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.
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?
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.
Can you explain what should have name and IP of the old standalone server, Cluster Name or SQL Server?
The SQL Server gets the name and IP address during the installation of SQL Server.
Thanks for answer.I have another question , on my standalone server i have a few presistent routes where its should be after migration?
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.
What about ODBC sources? I’ve got a few System DSN ( MySQL ODBC ) on standalone server.
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.)
Thank’s for all answers ,today i’m going to move my db to cluster 🙂
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?
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.
Why should i install MPIO? i have only one FC card in my server?
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.
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
Yep, that’s what you need to do.
Your English is fine. It’s better than a lot of people that live here.
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.
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.
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
Thank’s a lot.
When I connected storage to second node disks were offilne. When I wanted to bring one disk online node ask me to format disk?
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.
I am working under the assumption that you took backups before beginning this as a just in case protection.
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?
Yeah, you should be able to just add the machine to the cluster via the cluster manager.
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?
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.
I have sql server data backup every day.
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?
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.
How can i add durring instalation SP1 to SQL SErver 2008 becouse i have on node1 sp1 but i have installation version witout.
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.
New node is working thank you for your help.