SQL Server with Mr. Denny

Nov 23 2009   11:00AM GMT

Moving a standalone instance to a cluster while keeping the name and IP

Denny Cherry Denny Cherry Profile: Denny Cherry

A little while back I posted about how to move a standalone instance to a clustered instance using a different name and IP, but what do you do when you need to keep the same name and IP address?  This is a much more complex procedure to complete.

Before I start going over the procedure here are the names and IP addresses that I’ll be refercing throughout the rest of the post.

Standalone server name and IP: SQL04, 10.5.0.12

Clustered Servers names and IPs (public IP/Heartbeat IP):
Node1: SQL06A 10.5.0.17/192.168.1.1
Node2: SQL06B 10.5.0.18/192.168.1.2
Cluster Name: SQL06 10.5.0.19

The first thing that we need to remember if that we can’t even install SQL Server on the cluster until we take the old machine offline since we want to keep the same name and IP address.

If you have the standalone server on the SAN the entire process is much easier and quicker to handle the process because you can simply move the LUNs from the standalone server to the cluster. If your standalone server is not on the SAN, then you’ll either copy the database files from the old server to the cluster, or backup the database before shutting it down. I recommend stopping the SQL Server and copying the files. It will take a little bit longer, but you are guaranteed that you won’t loose a single transaction.

If you have to copy the database files from the old server to the cluster then do so.

At this point you’ll need to shutdown the old server. You’ll need to remove the computer object from the Windows domain, as the Windows 2008 Cluster will create a new computer object in the domain for the cluster resource. You’ll want your sysadmin to do an Active Directory backup so that if you need to rollback they can restore the computer object to active directory and turn the server back on quickly.

After you have deleted the computer object from the domain you’ll probably want to force replicaton between the domain controllers. When deleing the computer object from the domain, be sure to delete the computer object using a domain controler in the same site so that the replication of the deletion is quicker.

Once the replication has been completed you can begin the process of installing SQL Server. When you do the install you’ll be specifying the SQL04 name and the IP address that the old server was using. Once SQL has been installed you can move the system databases into place, then remove the user databases which have failed to load and then reattach the user databases.

Now, the question you may be asking is why would you want to keep the name and IPaddress the same? May enviroments grow very organically, and without a whole lot of documentation. You may find that it is easier to move the server keeping the name and IP than it is to try and find every connection string which looks at the name or IP address. Keeping the IP address makes life much easier when you have a firewall between the SQL Server and the web servers such as web servers in a DMZ. Modifying the firewall rules is usually pretty straight forward, but its just one more thing which can go wrong. Keeping the IP address the same means that the firewall rules don’t have to be modified which is one less thing which can go wrong.

These same techniques work weither your SQL Servers are physical or virtual servers.

Hopefully this will help you migrate your standalone systems to a cluster.

Denny

32  Comments on this Post

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • AndrewNKeller
    Alright, here's the situation I'm in. I have a two-node clustered SQL 2008 with the one default instance. When we installed an additional named instance on top of this, it was installed as a stand-alone instead of a clustered install. We want to convert this to a clustered installation, but I don't think that's possible. Since all the databases (ok, it's just one) are all in their correct locations on the SAN drives, I'm thinking that I just delete/uninstall the old instance and re-install it as the new clustered instance. They only had the one user, so I don't need to worry about scripting those out, I can just manually recreate it, and the full server name+instance name will remain the same and be seamless to the end user. Then I can add the second node in and be done. That sound about right?
    0 pointsBadges:
    report
  • Denny Cherry
    You'll need to uninstall the named instance, and reinstall as a clustered instance. The hostnameinstancename will be different as each clustered instance has its own hostname that you have to use. In this case your new instance name would be clustername2instancename.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Hi, Can you explain what should have name and IP of the old standalone server, Cluster Name or SQL Server?
    0 pointsBadges:
    report
  • Denny Cherry
    Ssierocki334, The SQL Server gets the name and IP address during the installation of SQL Server. Denny
    66,185 pointsBadges:
    report
  • Ssierocki334
    Thanks for answer.I have another question , on my standalone server i have a few presistent routes where its should be after migration?
    0 pointsBadges:
    report
  • Denny Cherry
    No problem. If you've got static network routes setup on your server those will sill be there after reinstalling SQL Server. You'll need to manually add those to the other server in the cluster as well. I recommend always putting static networking routes into the network routers instead of the servers so that they always apply to everyone.
    66,185 pointsBadges:
    report
  • Ssierocki334
    What about ODBC sources? I've got a few System DSN ( MySQL ODBC ) on standalone server.
    0 pointsBadges:
    report
  • Denny Cherry
    Any ODBC data sources would need to be copied to the other node of the cluster manually. They aren't copied over via Windows clustering. (Which is one of the reasons that I'd typically don't recommend using ODBC data sources.)
    66,185 pointsBadges:
    report
  • Ssierocki334
    Thank's for all answers ,today i'm going to move my db to cluster :-)
    0 pointsBadges:
    report
  • Ssierocki334
    Hi, now I've got one node SQLCLUSTER. I'm going to add second node. Before installing feature "Fail Over Culstering" on that node, disks from my HP MSA2000 FC Storage should be "online" or "ofline" in Disk Managment?
    0 pointsBadges:
    report
  • Denny Cherry
    You don't want to present the disks to the new machine until you are ready to add the new machine to the cluster. I'd get failover clustering and MPIO installed first, then just before you go through the wizard to add the machine to the cluster present the disks, and rescan the SCSI bus.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Why should i install MPIO? i have only one FC card in my server?
    0 pointsBadges:
    report
  • Denny Cherry
    If that single HBA is connected to a single port on the storage array without going through a network switch then you don't need MPIO. If however the HBA is connected to a switch, and that switch has multiple cables connecting it to the storage array then you need MPIO as you can have multiple paths between the server and the storage.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Ok, so i don't need MPIO. Before Adding a node2 to cluster 1. Configure OS( Win2008 ) on node2 like on node1 ServicePackc etc. 2. Install FailOver Clustering feature 3. Connect storage to node2 and bring disks online 4. Add node through the wizard 5. Add SqlServer to an existing node is that ok ? sorry for my english i'm from poland
    0 pointsBadges:
    report
  • Denny Cherry
    Yep, that's what you need to do. Your English is fine. It's better than a lot of people that live here.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Thank's for your answers. How it is posible that two server/nodes are connected to storage throught one LUN and there is no data corruption? i'm afraid of losing data when i'll be adding second node.
    0 pointsBadges:
    report
  • Denny Cherry
    The Windows Clustering service manages this for you. The clustering services ensures that only one of the notes will be talking to the disks at a time, which is why you want to present the disks then immediately add the node to the cluster.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Thank's a lot when i was moving sqlserver2008 to cluster I used this script to move logins,passwords and sids http://sqlgeek.pl/2010/10/18/pl-sql-server-migracja-loginw/ maybe it will be usefull for you, its for sql2005 and higher and it's better then MS KB918992
    0 pointsBadges:
    report
  • Ssierocki334
    Thank's a lot.
    0 pointsBadges:
    report
  • Ssierocki334
    When I connected storage to second node disks were offilne. When I wanted to bring one disk online node ask me to format disk?
    0 pointsBadges:
    report
  • Denny Cherry
    You do NOT want to do that. You will loose all the data if you do. Try adding the machine to the cluster as is. It should be able to add the machine and get the disks sorted out.
    66,185 pointsBadges:
    report
  • Denny Cherry
    I am working under the assumption that you took backups before beginning this as a just in case protection.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Ok, I didn't lost data ( quickly shuttind down second node ). Now i've got storage connected to second node and three disks ( quorum, dtc, data ) are offline in Disk Managment. Can I now add second node to cluster?
    0 pointsBadges:
    report
  • Denny Cherry
    Yeah, you should be able to just add the machine to the cluster via the cluster manager.
    66,185 pointsBadges:
    report
  • Ssierocki334
    Ok, I have to wait for backup :-) I did not have Quorum and DTC disk backuped. Now I'm doing backup this disks by Windows Server Backup is it correct? I think that Windows Server Backup won't destroy quorum and dtc disk during backup?
    0 pointsBadges:
    report
  • Denny Cherry
    The quorum and DTC disks aren't the most important things in the world to backup. They can be rebuilt easily enough if there's a problem with them. It's the SQL databases and other data volumes which are the things to really worry about. The Windows Server backup tool will work just fine.
    66,185 pointsBadges:
    report
  • Ssierocki334
    I have sql server data backup every day.
    0 pointsBadges:
    report
  • Ssierocki334
    I've go some warrnings about validation disk. Disks in cluster are now online, during adding new node disks in cluster should be online or offline?
    0 pointsBadges:
    report
  • Denny Cherry
    The warnings are find. As long as you get through the wizard and the node is added to the cluster that's the important thing. Once you've got SQL Server installed on the new node you can test failover to see how well it's working.
    66,185 pointsBadges:
    report
  • Ssierocki334
    How can i add durring instalation SP1 to SQL SErver 2008 becouse i have on node1 sp1 but i have installation version witout.
    0 pointsBadges:
    report
  • Denny Cherry
    You can either slipstream the service pack onto the origional install disk, or you can just install the RTM on the new node, then install SP1 on the new node.
    66,185 pointsBadges:
    report
  • Ssierocki334
    New node is working thank you for your help.
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to: