cutover

Question

Hi,
I am moving sql server instance from cluster to vm. I already installed sql server, restored user databases, moved logins, jobs etc.

I have one question for cutover. I will take last transactin log backup of the databases and restore on vm.

After that I have two options:
1. stop sql server instance (services) on cluster
or
2. take resource group (cluster admin) offline.

Then test application with new server and leave it there. Original instance on cluster should not be available. What is best approach or any other suggestion used during cutover?

Thanks

Answer 1

Have you tried using DNS alias? Create a DNS alias that points to the IP address of the new SQL Server instance running on the VM. This will allow you to have both cluster and VM to be online while you perform your testing. You can also just change the IP address of the DNS entry for the virtual server name to point to the IP address of the VM. Just remember to revert back the change after the testing and document it for the final cutover. The beauty of DNS aliases is that it abstracts changes such as server upgrade, hardware migration, etc. - you don`t need to change your application at all since the DNS alias will redirect the requests to the appropriate IP address.

It is recommended, however, to switch the databases on your cluster to read-only so that no transactions will be allowed while you are doing your testing.

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 2

Have you tested your application connectivity to new SQL in VM? Have you checked all SQL Jobs are working in new SQL Server in VM and producing exactly same output. If you have tested all this then your are good to go. I would suggest you to create DNS alias for your server so that if you need to move your server or database then you can point the DNS alias to new location.

---

Regards,

Basit A. Farooq (MSC Computing, MCITP SQL Server 2005 & 2008, MCDBA SQL Server 2000)

http://basitaalishan.com

Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.

Answer 3

Thanks for reply.

We have named instances on cluster. We created same named instances on new vms. Instance name is same but server name is different. So with DNS alisa or CNAME there will be no issue for named instances? Say old/instance1 new/instance1 will work for DNS Alias or Cname? Any one have experience?

Thanks

Answer 4

Generally, there are no issues with named instances because DNS aliases (and DNS in general) only look at the IP that corresponds to a particular server or host name (and vice versa.) Unless, of course, you already have issues with your overall DNS infrastructure like caching, forwarding, etc. In some environments that I've worked with, DNS aliases are what they use across the board. This helps in DR, server migration, hardware upgrades, etc. without having to modify anything on the application. For example,

Hostname for production is PROD and instance name is INSTANCE. IP of the hostname is 192.168.0.22. Your SQL Server instance name is PROD\INSTANCE and DNS will always point to the appropriate IP address assigned to the host. Let's say your VM's name is VM-PROD with an IP address of 192.168.0.23 and a named instance INSTANCE. To access the SQL Server instance on the VM, you will use VM-PROD\INSTANCE. DNS will redirect client access to VM-PROD using 192.168.0.23.  When you proceed with the cutover, if you change the IP address of PROD from 192.168.0.22 to 192.168.0.23, all client requests will be redirected to VM-PROD. You can change the original IP address of PROD to something else until you are ready to decommission the server. As long as the name is properly resolved with the correct IP address, you are good to go. After the environment has been stabilized, you can proceed to decommission the old server. This, of course, is only applicable if your instance name is the same on both the old and new environment, for example, INSTANCE on old and INSTANCE on new. DNS only takes care of the hostname-to-IP resolution, it has nothing to do with SQL Server

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 5

Thanks Edwin for your detailed reply.

During cutover, we will take that resource group offline and will check application connectivity with new server (dns redirect). If everything is ok, then later on we can remove that sql instance from cluster. I also want to keep the old instance for sometime just for rollback. What are the steps for cutover from cluster instance to vm with dns redirect?

Thanks

Answer 6

As I've mentioned, you do not need to take the cluster resource group offline during the cutover. By simply modifying the DNS entry for the virtual server name to point to the IP of the VM, you've basically redirected all client access to the VM. This is your final cutover step. You might also want to switch the databases on the cluster to read-only so that no modifications are made after the cutover. If you want to rollback, just change the IP address of the DNS entry back to the original virtual IP address and switch the databases to read-write. Although, you may need to restore your databases from the VM to the cluster if you want to consider the transactions between the cutover period and the rollback period. If you take the cluster resource group offline, you can't access your databases for comparison - checking if both have the same configuration, properties, etc. - after the cutover. Plus, taking it offline and bringing it back online may overwrite the change you made on the DNS entry. If you want to access both the VM and the clustered instance, just use IP addresses in your SQL Server Management Studio instead of hostnames/virtual server name in the format 192.168.0.22\INSTANCE

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 7

Thanks Edwin.

Since we have named instance on cluster, is there any issue for cname setup. Since cname will will redirect traffice from one server to other, how it will take care of named instance? Are there any special consideration or procedure to create cname for named sql server instance on that server? My concern is that sharepoint should not have any issue to connect to named instance that is on cluster when it will be redirected to vm.

Thanks

Answer 8

As I mentioned, DNS only takes care of the name-to-IP resolution. It does not know anything about instance names. However, if you have the same instance name on both the cluster and the VM, you should not have an issue. You are only changing the IP address of the original virtual server name to point to the IP of the VM. Applications, and including SharePoint, only looks at the instance name - whether default or named - and port number to connect to the database server.  What happens to the hostname/virtual server name-to-IP resolution is transparent to the application. That is the job of the DNS server. That is why I mentioned that the instance name between the cluster and the VM should be the same.

If the concept is still vague and you need immediate assistance on how to perform this task, my contact details are on my profile

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn

Answer 9

Thanks Edwin, now concept is clear. Thanks for your help.

Answer 10

Hi Edwin,

I moved maintennace plans and related jobs to new sever for sharepoint databases on sql server. I changed connection string to new server in maintenance plan. When I try to start the job manually i.e. update statistics on all user database job failed after some time may be on some database. Job run as sa owner. Maintenance plans are also failing after migration. 

Thanks

Answer 11

Do you have the complete error message on why the maintenance plans failed?

---

Edwin Sarmiento SQL Server MVP
Blog | Twitter | LinkedIn