March 11, 2016 at 12:42 am
Hi
We need to move MS SQL Cluster to Standalone SQL Server, what are the steps we need to follow & how to move same instance name with virtual name. appreciate if someone can give the correct guidance to do this.
Thanks
Harshana
March 11, 2016 at 12:37 pm
Are you building the new "Standalone SQL Server" and migrating to it?
How much data do you have to move and what is an acceptable amount of downtime?
March 11, 2016 at 6:43 pm
Move LUNS and attach to the new stand alone instance.
March 13, 2016 at 5:35 pm
Harshana-455711 (3/11/2016)
HiWe need to move MS SQL Cluster to Standalone SQL Server, what are the steps we need to follow & how to move same instance name with virtual name. appreciate if someone can give the correct guidance to do this.
Thanks
Harshana
Do you plan to destroy the exisiting WSFC and re use the hardware or do you have a new standalone replacement server?
muth_51 (3/11/2016)
Move LUNS and attach to the new stand alone instance.
Probably the worst advice that can be given here.
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
March 15, 2016 at 6:14 am
Hey Harshana,
I actually do something similar to this all the time when upgrading our clusters from Windows 2008 R2/SQL 2008 R2 to Windows 2012 R2/SQL 2012 or 2014.
There is a slight difference if you are using a default or named instance. If you are using a named instance then build your standalone server with a blank install of SQL and use the exact same instance name as on the cluster. If it's a default instance then just install the new SQL instance as default.
It's important to mind the drive letters between the cluster and the standalone server. If you use the same drive letters for your system, data, log and temp drives then it makes life easier. If you didn't use the same drive letters and files will be moved then I find it easiest to run ALTER statements pointing files to their new location PRIOR to shutting down the clustered instance. Don't forget about the tempdb file placement!
After you shut down the cluster instance, rename the virtual name for the cluster instance to something else, I always just add -O to the name. You can just go to properties for the virtual name and change it. Sometimes it errors out when you apply but if the FCM shows the name as different then it worked even with the error. I do it this way so the SPNs for SQL get changed and the new instance can build its own SPNs.
If you can, before moving drives, run snapshots if they are on a SAN. Always better to be safe than sorry.
Move the drives to the new server and attach. I move them one at a time so I can keep track of drives and letters.
Once your files are where they need to be on the new server, you can start SQL.
A few caveats here. If you relocated the system files then hopefully you ran ALTER statements for them. Before starting SQL you have to modify the registry to change all the system pointers to the new drive location. Remember that master is referenced from the SQL Connection and not from where your ALTER statements pointed them to.
After SQL is up and running then rename the server to the same name you had prior as the cluster virtual name and restart the server. It should build its SPNs on start as long as you have full admin rights in Acive Directory.
You can move instances this way between clusters and into and out of clusters. You can upgrade instances this way easily too on new hardware. I like to use BareTail to monitor the SQL error log during startup so I can catch anything going wrong. It's a nice, free tool that follows the tail end of log files and opens large ones really quickly. And once you open your error log and don't close it out in the application, it will always open to the SQL error log. No more having to figure out where that thing is.
Hope this helps. I would be happy to answer any follow up questions you have.
Jon
March 15, 2016 at 1:09 pm
Hi Jon
Thanks for reply,
Yes, I am going build new Standalone SQL Server & want to migrate to it, I have 20GB one Database & can Take Maximum 2 Hours Downtime
Thanks
Harshana
March 15, 2016 at 1:35 pm
I bet you can do a full backup and restore in under 2 hours, run a dry-run to verify.
You'd just need to take the old server down, rename the new server and run sp_dropserver / sp_addserver after the restore.
HTH,
Jon
March 15, 2016 at 3:03 pm
Jon is right, If you just have one 20 GB database to migrate, then just backup and restore the database. Or just copy it over to new instance instead of moving drives and attach to new instance. Of course if you do it this way then you have to configure the instance with security and maintenance plans and jobs, etc.. If you migrate the system databases over from the existing instance then all of that comes over with the instance.
Our instances have 200 to 300 databases each so I have to move drives as I don't have the luxury of backup/restore of databases to new instances.
Also, try and use the same account to run your SQL services as on the cluster. If you use a different account then some things won't work correctly like linked servers. They will have to be dropped and added back or encryption between them doesn't work.
Jon
March 15, 2016 at 11:49 pm
Hi Jon.Morisi
Thanks a lot for your reply & Following the steps what I am going to do, appreciate if you could validate below steps are correct or not.
Steps
1. Create new Standalone SQL Server with name instance using cluster virtual name
2. Take backup from cluster SQL server
3. Shout down both cluster nodes
4. Transfer backup to new standalone SQL Server
5. Change computer name & ip address using cluster virtual name & virtual ip
6. Restore Backup to new Standalone Server
Thanks
Harshana
March 16, 2016 at 3:31 am
Have you considered the 'do-nothing' approach. (Well, almost do nothing...)
Evict node 2 from your cluster, then shut it down and decommission it. You are now running on a single-node cluster that cannot fail over.
In this scenario you do not need to make any changes to SQL Server. If you are planning to keep with your current version of SQL Server this is definitely what I would consider. When the time comes to upgrade to a new SQL Server version you do this on a new Windows instance.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply