locked
Copying SharePoint databases from single SQL server to clustered SQL servers, is it possible? RRS feed

  • Question

  • Dears

    I am going to install SharePoint on load balancing servers - two front web servers, clustered database and on query server.

    Since ordering the required hardware for clustering may take long time, my company decides to install SharePoint databases on single SQL server, and later on the databases will be restored on the clustered database server, any SharePoint configuration related to database will use DNS name not the IP address.

    My question , in order to copy SharePoint database from single SQL server to clustered SQL servers ,Can we take backup from SharePoint databases (Content , Shared services ,Search …) from single  SQL server to be restored later on clustered SQL server? And then change DNS record to refer to the clustered IP address without any modification on SharePoint settings?

    Thanks 

     

    Monday, November 1, 2010 6:16 AM

Answers

  • Hi,

    Yes, by using either A DNS Alias or a Local / Connection Alias and ensure the User Accounts have the same Server Role and User Mappings as in the existing SQL Server.

    Reference:  http://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx, http://technet.microsoft.com/en-us/library/ff945791.aspx

     

    -Ivan

     

     


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    • Marked as answer by Lily Wu Thursday, November 11, 2010 8:01 AM
    Monday, November 1, 2010 8:35 AM
  • A few approaches for you to consider

     

    1) Build the new SQL cluster with exactly the same host name assigned to the cluster as your current single server (will obviously require you to take the old database server off the network/rename it when you bring the new cluster up.

    If you have your data on LUNs you can then just re-attach the LUNs to the cluster.

    2) Build the new SQL cluster with a new host name, and use SQL Alias on the front end servers (start > run > cliconfg.exe on the WFE servers) and point e.g. DBSERVER01 to DBSERVER02)

    Then restore your databases to the new cluster and bring up the services on the WFE.

    Note if the DB has been reffered to in SharePoint with a FQDN you will need to use an FQDN in the SQL Alias.

    Both of these methods are the supported microsoft methods, I would suggest the SQL alias route personally as its easy just to remove the alias if something goes wrong and point your WFEs back at the databases on the existing SQL Server.

    Technet documentation on this method here: here

    This can also be done with minimal downtime.


    Conrad Goodman MCITP SA / MCTS: WSS3.0 + MOSS2007
    • Proposed as answer by Conrad Goodman Wednesday, November 3, 2010 8:34 AM
    • Marked as answer by Lily Wu Thursday, November 11, 2010 8:01 AM
    Tuesday, November 2, 2010 10:39 AM

All replies

  • Hi,

    Yes, by using either A DNS Alias or a Local / Connection Alias and ensure the User Accounts have the same Server Role and User Mappings as in the existing SQL Server.

    Reference:  http://blogs.msdn.com/b/sql_protocols/archive/2007/01/07/connection-alias.aspx, http://technet.microsoft.com/en-us/library/ff945791.aspx

     

    -Ivan

     

     


    Ivan Sanders My LinkedIn Profile, My Blog, @iasanders.
    • Marked as answer by Lily Wu Thursday, November 11, 2010 8:01 AM
    Monday, November 1, 2010 8:35 AM
  • Approcah to this migration depends on how big it is, how much downtime/maintenance window you have, do you have full-time SQL DBA's.

    You can migrate all DB's from old to new server (including the system db's and user db's) which will make sure that the permission are correct. Also make sure that the disk structure and file locations should be same as old servers for restore to work.

     

    • Marked as answer by Lily Wu Thursday, November 11, 2010 8:01 AM
    • Edited by Mike Walsh FIN Thursday, December 9, 2010 5:46 PM Sig removed. Do NOT ask people to mark your posts.
    • Unmarked as answer by Mike Walsh FIN Thursday, December 9, 2010 5:46 PM
    Tuesday, November 2, 2010 4:04 AM
  • A few approaches for you to consider

     

    1) Build the new SQL cluster with exactly the same host name assigned to the cluster as your current single server (will obviously require you to take the old database server off the network/rename it when you bring the new cluster up.

    If you have your data on LUNs you can then just re-attach the LUNs to the cluster.

    2) Build the new SQL cluster with a new host name, and use SQL Alias on the front end servers (start > run > cliconfg.exe on the WFE servers) and point e.g. DBSERVER01 to DBSERVER02)

    Then restore your databases to the new cluster and bring up the services on the WFE.

    Note if the DB has been reffered to in SharePoint with a FQDN you will need to use an FQDN in the SQL Alias.

    Both of these methods are the supported microsoft methods, I would suggest the SQL alias route personally as its easy just to remove the alias if something goes wrong and point your WFEs back at the databases on the existing SQL Server.

    Technet documentation on this method here: here

    This can also be done with minimal downtime.


    Conrad Goodman MCITP SA / MCTS: WSS3.0 + MOSS2007
    • Proposed as answer by Conrad Goodman Wednesday, November 3, 2010 8:34 AM
    • Marked as answer by Lily Wu Thursday, November 11, 2010 8:01 AM
    Tuesday, November 2, 2010 10:39 AM