none
Move all WSS 3.0 databases to new SQL server

    Frage

  • I'm currently running a WSS 3.0 farm. 

    • My Web server: Server 2003 R2, 32-bit
    • My Existing DB server: Server 2003 R2 (64-bit), SQL 2005 (64-bit).
    • My new DB server: Server 2008 R2 (64-bit), SQL 2008 (64-bit)

    My goal is to migrate the sharepoint databases to the new DB server so I can decommission the existing DB server.   Both the old and the new DB servers are currently running in production.  I have no immediate plans to move/change the web server. At some future date (probably this summer) I do intend to upgrade my site collection to SharePoint 2010 Foundation, but that's not going to be a part of this project.

    I've located a number of articles explaining the process for sharepoint moves, migrations, etc, but none of them clearly seems to be aimed at my scenario.  Since I'm changing both the OS and the SQL version, it seems I cannot simply backup all DBs, restore them to the new DB server, and update the web application.  This makes sense to me, since I know the Sharepoint Central Administration also stores the farm configuration in a DB (and that DB has to be moved/recreated also) , but I'm not sure what steps to take in what order to accomplish my goal.  Kind of a chicken/egg problem.

    Can anyone point me to the correct articles, or maybe a blog posting that addresses this specific scenario of keeping the web server, but moving to a new DB server with a new name, running both a newer OS and newer SQL versions?

    Thanks in advance for all replies!

    BJ

    Freitag, 2. März 2012 18:03

Antworten

  • Hi Bryan,

    Below is the TechNet article you can follow to accomplish your task. You would follow the section "Move all databases to a different database server" - please note there is this confusing line in this article "The new database server must use the same version of Windows Server and SQL Server as the old database server" - not sure why? I tried multiple time following this article to move successfually to a different version of SQL and OS. So I know it works. Tried providing feedback about this line - but did not see any response :-)

    http://technet.microsoft.com/en-us/library/cc512725%28v=office.12%29.aspx

    Hope this helps and good luck!


    BlueSky2010
    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Sonntag, 4. März 2012 03:48
  • Hi,

    The following steps will allow you to remove YourExistingDBServer from Production after you have moved your SharePointDBs to the new server

    1. From YourWebServer Shutdown the SharePoint Services
    2. From your YourExistingDBServer Backup all SharePointDbs
    3. Copy the Backups from YourExistingDBServer and Restore them to YourNewDBServer
    4. Recreate the permissions from YourExistingDBServer on YourNewDBServer
    5. From YourWebServer
      1. Open your Host file location windows\systems32\drivers\etc
      2. Enter the IPAddressof YourExistingDBServer, then tab and enter the name of YourNewDBServer
      3. Save the Host File
      4. Open a command prompt and type cliconfg
      5. Click the AliasTaband then Click Add
      6. Choose TCPIPas the NetworkLibrary
      7. Enter YourExistingDBServer in the ServerAlias field
      8. Enter YourNewDBServer in the ServerName field
      9. Uncheck Dynamically determine port and leave 1433 and click OK
      10. Start the SharePointServices
    6. Run iiseset

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.

    Montag, 5. März 2012 15:20
  • I made a post yesterday about this, my suggestion there may help be applicable to you as well.

    My suggestion is to create a SQL Alias (if you don't have one already), move the databases, and retarget the alias to the new location. If you don't already have a SQL Alias, then the alias will unfortunately have to be named the same as your existing SQL Server (or instance name). This may be confusing but it is the simplest, and easiest to implement approach.

    Basically you trick SharePoint into thinking your databases haven't moved.


    Jason Warren
    Infrastructure Specialist
    Habañero Consulting Group
    www.habaneros.com/blog

    Freitag, 2. März 2012 19:47

Alle Antworten

  • I made a post yesterday about this, my suggestion there may help be applicable to you as well.

    My suggestion is to create a SQL Alias (if you don't have one already), move the databases, and retarget the alias to the new location. If you don't already have a SQL Alias, then the alias will unfortunately have to be named the same as your existing SQL Server (or instance name). This may be confusing but it is the simplest, and easiest to implement approach.

    Basically you trick SharePoint into thinking your databases haven't moved.


    Jason Warren
    Infrastructure Specialist
    Habañero Consulting Group
    www.habaneros.com/blog

    Freitag, 2. März 2012 19:47
  • Hi Bryan,

    Below is the TechNet article you can follow to accomplish your task. You would follow the section "Move all databases to a different database server" - please note there is this confusing line in this article "The new database server must use the same version of Windows Server and SQL Server as the old database server" - not sure why? I tried multiple time following this article to move successfually to a different version of SQL and OS. So I know it works. Tried providing feedback about this line - but did not see any response :-)

    http://technet.microsoft.com/en-us/library/cc512725%28v=office.12%29.aspx

    Hope this helps and good luck!


    BlueSky2010
    Please help and appreciate others by using these features: "Propose As Answer", "Vote As Helpful" and "Mark As Answer"

    Sonntag, 4. März 2012 03:48
  • Hi,

    The following steps will allow you to remove YourExistingDBServer from Production after you have moved your SharePointDBs to the new server

    1. From YourWebServer Shutdown the SharePoint Services
    2. From your YourExistingDBServer Backup all SharePointDbs
    3. Copy the Backups from YourExistingDBServer and Restore them to YourNewDBServer
    4. Recreate the permissions from YourExistingDBServer on YourNewDBServer
    5. From YourWebServer
      1. Open your Host file location windows\systems32\drivers\etc
      2. Enter the IPAddressof YourExistingDBServer, then tab and enter the name of YourNewDBServer
      3. Save the Host File
      4. Open a command prompt and type cliconfg
      5. Click the AliasTaband then Click Add
      6. Choose TCPIPas the NetworkLibrary
      7. Enter YourExistingDBServer in the ServerAlias field
      8. Enter YourNewDBServer in the ServerName field
      9. Uncheck Dynamically determine port and leave 1433 and click OK
      10. Start the SharePointServices
    6. Run iiseset

    -Ivan


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.

    Montag, 5. März 2012 15:20
  • Thanks to Jason, BlueSky, and Ivan for the replies.  I'm inclined to stay away from using an alias (for now) because my existing IIS server does host sites other than SharePoint, and the DBs used by those sites have already been moved to the new SQL server.  IIUC this would run up against the caveat about aliases, that an alias applies to an entire (web) server.

    It sounds like at some point, it would make sense and ease my future life to use an alias for my SharePoint sites, but (also iiuc) I'd need to split off my SharePoint sites from the shared IIS box to their own dedicated web server.  Also included on my road map for the near future is to replace my SBS 2003 R2 server with SBS 2011; when I do that, I'm thinking I may want to consider letting SBS 2011 once again host the IIS portion of my farm, while keeping the DBs on my existing SQL server. (Which would bring me full-circle, since my site collection started on SBS 2003 with WSS 2.0!)  Thoughts on this?  Lots of moving parts and goals to consider here. 

    Anyway, I digress somewhat - back to the matter at hand, namely getting my DBs moved....

    BlueSky, I'm hopeful I can use your method to move my DBs successfully - currently reading thru the steps to understand what I'll be doing. One question for you (or anyone) - I'm not familiar with SSPs as discussed in the document - is this applicable only to OSS 2007?  I'm only running WSS 3.0.

    Can anyone else comment on the admonition by MS that the OS and SQL versions cannot be changed when using that method, and why BlueSky has been able to successfully carry it off?  I don't want his success contingent upon a factor or variable which might be different in my environment and would cause it to fail for me, where it succeeded for BlueSky.

    I'm not clear what the OS version under SQL has to do with anything other than the bit-ness of it, and since I'm already 64-bit SQL on old and new servers, maybe the OS version change is a non-issue?  It appears SQL 2008 is supported for WSS 3.0, so I'm digging to see if something about the change of SQL version from 2005 to 2008 is an issue. Can anyone elaborate about all this?

    Thanks again - this group has been invaluable to me and I hope to move this forward soon, so all replies are welcome and appreciated!





    • Bearbeitet Bryan-L Montag, 5. März 2012 22:51
    Montag, 5. März 2012 21:59
  • SSPs are only applicable to OSS 2007, so you won't need to worry about that in your environment.

    In regard to the statement that the new database server must use the same OS and version of SQL, I will be investigating this further and may submit a request to change that statement. In my experience, you do not necessarily need to have the same version of the OS and SQL. However you cannot 'downgrade' - like going from SQL Server 2008 to SQL Server 2005. Even if the exact build of SQL is not the same, as long as the new server is at a higher build, restoring the database to the new server will automatically update the databases to the SQL server version. In your case, restoring a SQL 2005 database to a SQL 2008 instance will also automatically upgrade a database to 2008, and then you can still use Central Admin to re-attach those databases to your web application.

    With this said though, I would still highly recommend you setup a test environment if you can and try moving the databases there before doing this in your live/production environment.


    Regards,

    Jerad Plesuk
    Technical Support | SharePoint Technologies | Microsoft Corporation
    -----------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights


    Donnerstag, 8. März 2012 20:27
  • Jerad,

    Thanks.  All my servers are virtualized, and I'd like to try this in a test environment first. However I'm not sure how to test this scenario properly. If this move is successful in my production environment, it will mean:

    • I moved the DBs from OLDSQL\INSTANCE to NEWSQL\INSTANCE within the farm
    • I updated/reconfigured my existing Application Server within the farm as needed to reference the new DB server

    I think I could set up a TESTSQL\INSTANCE without too much trouble, but how would I set up a "test" application server?  If I did, I'd be testing a different scenario than the one I'm contemplating. Instead of testing the scenario above, I'd be testing a move to an entirely new farm, both DB and application servers.


    Freitag, 9. März 2012 22:32
  • With a virtualized environment, it may be easiest for you to make copies of your VMs, set them up to run on their own internal/separate network, install the second instance of SQL somewhere in that environment just like you would in Production, and then attempt the database move process in that separate environment. However, keep in mind that you will need a DC for this environment to handle account names and manage DNS.

    However, you can also use the NEWSQL instance you have setup, but then you will need to bring up the 'test' App and WFE servers by installing SharePoint on them, and then running PSConfig, pointing it to the NEWSQL instance. At that point you could go through the test process of moving the databases from the NEWSQL instance to the TESTSQL instance.


    Regards,

    Jerad Plesuk
    Technical Support | SharePoint Technologies | Microsoft Corporation
    -----------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights


    Montag, 12. März 2012 21:39
  • Thanks again Jerad.

    I take regular image backups of these servers (including a DC), so it was simple (if a bit time consuming) to restore those images to new VMs and boot them in the VM Only Network.  For the NEWSQL server, I'm not yet doing image backups (only SQL backups), so I'm simply building another NEWSQL server to use for this test, and that got me to thinking...

    ...if I build this NEWSQL *test* server using the same name as the in-production NEWSQL server, and my test in the isolated environment is ultimately successful, will that open up any shortcuts that'll allow me to move more quickly into production?  Can I take any of my work from the test Application or DB servers and simply move them to the production server(s) of the same name?  

    Unless the answer is an unqualified "Yes, it'll be pretty simple" then I'll simply stick with my plan of doing the move in my test environment, documenting a successful experience, and then executing those same steps in production. But I thought it would be worth asking.

    Thanks once again,

    Bryan

    Mittwoch, 14. März 2012 20:12
  • The main thing is that while you are performing your work in teh test environment, I assume users will be using and making changes to the product environment. So really, the databases may change (however slightly) which means you can't be 100% sure that whatever work you do from the test environment will accurate trying to move it back to production (or hooking production into it). So with that said, I would recommend you just stick with your currnet plan of doing the move in the test environment and then noting what you encounter for preparation in production.


    Regards,

    Jerad Plesuk
    Technical Support | SharePoint Technologies | Microsoft Corporation
    -----------------------------------------------------------------------------------------
    This posting is provided "AS IS" with no warranties, and confers no rights


    Freitag, 23. März 2012 19:58