How to move SQL Server from Standalone to Cluster environment

How to move SQL Server from Standalone to Cluster environment

Rate This
  • Comments 5

Few days back there was a question in our internal forum about moving SQL Server from Standalone to cluster environment. Based on my experience there are many ways to move standalone SQL Server to clustered SQL Server.

As you all know that SQL Server has two kind of databases

  • User databases.
  • System databases {master, model, tempdb, msdb and distribution(if replication configured)}.

User databases can be moved to new location using “with move” option of Restore Database command. For system database you can follow http://msdn.microsoft.com/en-us/library/ms345408.aspx.

The purpose of this blog is not to tell what is already available :) I have a little trick to make things faster. Here we go…

First lets understand what we are doing and what’s the reason behind it. Whenever we start SQL Server, it goes to registry to find out location of master.mdf, maslog.ldf and ErrorLog files. One all three are located, logging would start and master database would be opened to know location of other databases mdf and ldf files.

I’m sure this goes without saying – you need a full backup of all databases before attempting this. I can’t imagine this failing – that’s all the more reason to remember to take backups. You need backups most when you’re (over)confident.

Lets say you Standalone SQL Server having databases on D drive and you want to decommission the server. Below are the steps.

  1. Take Standalone machine out of network. Make sure there is no entry for host name or IP address anywhere in the network.
  2. Install SQL server in clustered environment and keep IP address and Virtual Server name same.
  3. While installing make sure that folder structure is same as standalone instance.
  4. Bring clustered SQL to same patch level as standalone SQL. This reason of this is that system databases can’t be reused across builds. To know the build you can open ERRORLOG and check highlighted part. (9.00.4053)
    • 2010-01-22 13:28:00.610 Server       Microsoft SQL Server 2005 - 9.00.4053.00 (X64)
          May 26 2009 14:13:01
          Copyright (c) 1988-2005 Microsoft Corporation
          Enterprise Edition (64-bit) on Windows NT 6.0 (Build 6001: Service Pack 1)

  5. One step 4 is completed, make sure that you can do failover.
  6. Take SQL Server offline, keep a backup of DATA folder to safe location (in case something goes wrong). I generally rename it to DATA_New.
  7. Now, take the DATA folder from Standalone server (Copy all folders which have mdf, ndf and ldf files) and put this in DATA folder on clustered SQL Server. Verify that all the files are at same location which was there on Standalone server.
  8. Bring SQL server online. If there are failures then start looking at Error Logs and troubleshoot further.

Happy Migration :)

DISCLAIMER: This postings is provided "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability.

Leave a Comment
  • Please add 5 and 7 and type the answer here:
  • Post
  • Hey,

    I don't thing this will work for your sys DBs. especially master.

    I have done this for stand alone instance (including system DBs :) ).

  • And why do you think so Steve? logically it should work if names are not changing (i did mention that in step 2)

    I have not tried personally and its only theory.

  • How to do the reverse?

    -- Switching from a cluster test environment to stand alone SQL 2008 servers?

    What are the steps needed?

  • ABCD,

    Same steps. Make sure the path of data drive is not getting changed.

  • if the dabatase path of standalone sql server is not the same with clustered sql? how to do ?

Page 1 of 1 (5 items)