locked
Confused about changing SQL Servers.... RRS feed

  • Question

  • After reading http://technet.microsoft.com/en-us/library/cc512725(v=office.12).aspx, I'm confused. The article includes instructions on A) moving databases to a server with a different name and B) moving databases to an identically named server. But in the case of A it says, "The new database server must use the same version of Windows Server and SQL Server as the old database server." In the case of B it says that the other version can be a newer version, but the name must be the same.

    Neither scenario works for us. Our old SQL Server is running older versions of Windows and SQL, and we want to move SharePoint to more recent versions, so A won't work. However, we're not going to turn off the old box, because there are other applications that still need it. So we can't create a new SQL Server with the same name, meaning B also won't work.

    Any recommendations?


    Brandon Murray | MCTS | SharePoint Administrator

    Thursday, March 1, 2012 8:45 PM

Answers

  • I'd rather not rely on a trick, especially one that could potentially cause a lot of confusion. In our case, we've decided to copy our physical SQL server to VMware, so that SP and SQL won't even know anything has changed. Then we can improve our virtual hardware and look into upgrading the SQL box later if necessary.


    Brandon Murray | MCTS | SharePoint Administrator

    Friday, March 9, 2012 3:13 PM

All replies

  • Here is a method that uses a SQL Alias to trick your SharePoint farm into thinking your SQL Server hasn't changed. This method provides some confusion as in SharePoint it says your SQL Server is your old server it's not. Document it for the future administrators. ;)

    For this example, assume the following servers:

    • SPSRV01 - Your SharePoint server (let's assume one for this example, but you may have more)
    • SQLOLD - Your old SQL Server. Let's assume the default instance
    • SQLNEW - Your new SQL Server. Let's assume the default instance

    1. Create a SQL Alias on SPSRV01. Create the alias with the name SQLOLD. Server address SQLOLD. At this point you should still be able to access your SharePoint sites. 
    2. Stop all the SharePoint (Windows) services running on SPSRV01.
    3. Change the alias to point to the server address SQLNEW
    4. Shut down your SharePoint servers to prevent them accessing SQL Server
    5. Copy (backup/restore, don't move) the databases from SQLOLD to SQLNEW (edit: I should clarify you should be copying ALL of your farm's databases. Content, services, even the configuration database.)
    6. Stop the Database Engine service on SQLOLD
    7. Turn on your SharePoint servers. At this point you should be able to access your SharePoint sites



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




    • Edited by Jason Warren Friday, March 2, 2012 7:49 PM added note about copying all databases
    • Marked as answer by star.warsModerator Friday, March 9, 2012 1:32 AM
    • Unmarked as answer by Brandon Murray Friday, March 9, 2012 3:08 PM
    Friday, March 2, 2012 12:22 AM
  • I'd rather not rely on a trick, especially one that could potentially cause a lot of confusion. In our case, we've decided to copy our physical SQL server to VMware, so that SP and SQL won't even know anything has changed. Then we can improve our virtual hardware and look into upgrading the SQL box later if necessary.


    Brandon Murray | MCTS | SharePoint Administrator

    Friday, March 9, 2012 3:13 PM
  • Hi Brandon,

    Dude where have you been this is not trick, if you think about it for a second, every SQL Cluster in the world uses a SQL Alias as the cluster address. We never use the actual ServerName even if there is just one SQL Server in DEV, even if its the same system hosting SQL and SharePoint. Always use Aliases whether its for a cluster or for a single server. It will make your life much easier, the next time you have to upgrade or move servers its simple.

    Also, this is a SharePoint Best Practice because we inherit this particulkar Best Practice from the SQL Team.

    Example you set up an Alias SharePointDB01 for a single server, later you want to add a failover cluster, you just change the ServerName of the Alias and now SharePointDB01 is a Cluster. This works with every Micrsosoft product and I dont know how DBAs would live without is and meet there SLAs.

    Note: Step 1 is not necessary, I think Jason is using it as an example to help you understand SQL Aliases

    -Ivan 


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

    Wednesday, April 4, 2012 10:12 AM
  • Example you set up an Alias SharePointDB01 for a single server, later you want to add a failover cluster, you just change the ServerName of the Alias and now SharePointDB01 is a Cluster. This works with every Micrsosoft product and I dont know how DBAs would live without is and meet there SLAs.

    Note: Step 1 is not necessary, I think Jason is using it as an example to help you understand SQL Aliases

    Step 1 is necessary as the farm has already been created using the SQL Server's server name. As you point out the best practice is to create the alias before creating the farm, however in this scenario it is not possible.


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

    Wednesday, April 4, 2012 12:48 PM