{"id":3141,"date":"2016-11-21T13:18:25","date_gmt":"2016-11-21T09:18:25","guid":{"rendered":"https:\/\/rakhesh.com\/?p=3141"},"modified":"2016-11-21T13:18:25","modified_gmt":"2016-11-21T09:18:25","slug":"p2v-a-sql-cluster-by-breaking-the-cluster","status":"publish","type":"post","link":"https:\/\/rakhesh.com\/windows\/p2v-a-sql-cluster-by-breaking-the-cluster\/","title":{"rendered":"P2V a SQL cluster by breaking the cluster"},"content":{"rendered":"

Need to P2V a SQL cluster at work. Here’s screenshots of what I did in a test environment to see if an idea of mine would work.<\/p>\n

We have a 2 physical-nodes SQL cluster. The requirement was to convert this into a single virtual machine.<\/p>\n

P2V-ing a single server is easy. Use VMware Converter. But P2V-ing a cluster like this is tricky. You could P2V each node and end up with a cluster of 2 virtual-nodes but that wasn’t what we wanted. We didn’t want to deal with RDMs and such for the cluster, so we wanted to get rid of the cluster itself. VMware can provide HA if anything happens to the single node.<\/p>\n

My idea was to break the cluster and get one of the nodes of the cluster to assume the identity of the cluster. Have SQL running off that. Virtualize this single node. And since there’s no change as far as the outside world is concerned no one’s the wiser.<\/p>\n

Found a blog post<\/a> that pretty much does what I had in mind. Found one more<\/a> which was useful but didn’t really pertain to my situation. Have a look at the latter post if your DTC is on the Quorum drive (wasn’t so in my case)<\/em>.<\/p>\n

So here we go.<\/p>\n

1) Make the node that I want to retain as the active node of the cluster (so it was all the disks and databases). Then shutdown SQL server.<\/h3>\n

\"sqlshutdown\"<\/p>\n

2) Shutdown the cluster.<\/h3>\n

\"clustershutdown\"<\/p>\n

3) Remove the node we want to retain, from the cluster.<\/h3>\n

We can’t remove\/ evict the node via GUI as the cluster is offline. Nor can we remove the Failover Cluster feature from the node as it is still part of a cluster (even though the cluster is shutdown). So we need to do a bit or “surgery”. :)<\/p>\n

Open PowerShell and do the following:<\/p>\n

Import-Module FailoverClusters\r\nClear-ClusterNode \u2013Force<\/pre>\n
This simply clears any cluster related configuration<\/a> from the node. It is meant to be used on evicted nodes.<\/p>\n
Once that’s done remove the Failover Cluster feature and reboot the node. If you want to do this via PowerShell:<\/p>\n
Import-Module ServerManager\r\nRemove-WindowsFeature FailoverClustering\r\nRestart-Computer \u2013Force<\/pre>\n
4) Bring online the previously shared disks.<\/h3>\n
Once the node is up and running, open Disk Management and mark as online the shared disks that were previously part of the cluster.<\/p>\n
\"disksonline\"<\/p>\n
5) Change the IP and name of this node to that of the cluster.<\/h3>\n
Straight-forward. Add CNAME entries in DNS if required. Also, you will have to remove the cluster computer object from AD first before<\/em> <\/span>renaming this node to that name.<\/p>\n
6) Make some registry changes.<\/h3>\n
The SQL Server is still not running as it expects to be on a cluster. So make some registry changes.<\/p>\n
First go to HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\Setup<\/code> and open the entry called SQLCluster<\/code> and change its value from 1 to 0.<\/p>\n
Then take a backup (just in case; we don’t really need it) of the key called HKEY_LOCAL_MACHINE\\SOFTWARE\\Microsoft\\Microsoft SQL Server\\MSSQL10_50.MSSQLSERVER\\Cluster<\/code> and delete it.<\/p>\n
Note that MSSQL10_50.MSSQLSERVER<\/code> may vary depending on whether you have a different version of SQL than in my case.<\/p>\n
7) Start the SQL services and change their startup type to Automatic.<\/h3>\n
I had 3 services.<\/p>\n
Now your SQL server should be working.<\/p>\n
8) Restart the server – not needed, but I did so anyways.<\/h3>\n
Test?<\/h3>\n
If you are doing this in a test environment (like I was) and don’t have any SQL applications to test with, do the following.<\/p>\n
Right click the desktop on any computer (or the SQL server computer itself) and create a new text file. Then rename that to blah.udl<\/code>. The name doesn’t matter as long as the extension is .udl<\/code>. Double click on that to get a window like this:<\/p>\n
\"udl\"<\/p>\n
Now you can fill in the SQL server name and test it.<\/p>\n
One thing to keep in mind (if you are not a SQL person – I am not). The Windows NT Integrated security is what you need to use if you want to authenticate against the server with an AD account. It is tempting to select the “Use a specific user name …” option and put in an AD username\/ password there, but that won’t work. That option is for using SQL authentication.<\/p>\n
If you want to use a different AD account you will have to do a run as of the tool.<\/p>\n
Also, on a fresh install of SQL server SQL authentication is disabled by default. You can create SQL accounts but authentication will fail. To enable SQL authentication right click on the server in SQL Server Management Studio and go to Properties, then go to Security and enable SQL authentication.<\/p>\n
\"sqlauth\"<\/p>\n
That’s all!<\/p>\n
Now one can P2V this node.<\/p>\n","protected":false},"excerpt":{"rendered":"
Need to P2V a SQL cluster at work. Here’s screenshots of what I did in a test environment to see if an idea of mine would work. We have a 2 physical-nodes SQL cluster. The requirement was to convert this into a single virtual machine. P2V-ing a single server is easy. Use VMware Converter. But … Continue reading P2V a SQL cluster by breaking the cluster<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":true,"jetpack_social_options":{"image_generator_settings":{"template":"highway","enabled":false}}},"categories":[183,85],"tags":[559,640,641],"jetpack_publicize_connections":[],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"","_links":{"self":[{"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/posts\/3141"}],"collection":[{"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/comments?post=3141"}],"version-history":[{"count":0,"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/posts\/3141\/revisions"}],"wp:attachment":[{"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/media?parent=3141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/categories?post=3141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/rakhesh.com\/wp-json\/wp\/v2\/tags?post=3141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}