Playing around with your live SQL Server cluster is always something to be extra careful with. Especially when you have to remove some old storage from your Windows Cluster group (Service/Application). Today I had a scenario where I had to move the system databases and folders from one drive to another. So the process is as usual – change the physical file paths for model, msdb (and tempdb in my case), take the SQL Server down, copy the files to the new location (system folders like Log, FTData and database files of msdb and model), edit the SQL Server service from the SQL Server Configuration Manager and then simply boot the SQL Server. Sounds like a fairly easy job. But it has some lessons to learn – let’s explain a bit further the order –
1. Changing the system databases physical file location (MSDB, Model and tempdb) – following this link will guide you through the process. No surprises here.
2. Changing the location of the master database and ERROR log – again use the above link, just note that it is better to have the SQL Server instance down while operating.
3. Take down (if still not) you SQL Server instance. Copy the system folder Fix folder permissions – make sure you fix all permissions and inheritance on NTFS level for the copied. (Depending on your installation you might need to move the DATA, Log, FTData, Repldata folders).
4. Change registry values for the FTData folder – open the registry editor and edit the following path – HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance id>\Setup\FullTextDefaultPath
5. Change the output of your SQL Server Agent error log – here is a great article describing in details how to do it.
6. Start your SQL Server and cross your fingers everything is alright. Personally I am changing/renaming the old location of everything I moved, so I am certain that the SQL Server instance is still not using it for something.
7. At this point you should be ready to go and remove the old drive from the SQL Server cluster group. Now here is a great deal of attention you need to pay! First check this article on how to remove an unused drive from a WS 2008 Cluster. Now here is the deal – you need to take the resource offline before doing anything. Otherwise, you are getting ALL YOUR RESOURCES MOVED TO Available Storage group. Nice, huh? I actually had several more drives to remove, mounted to the root drive for SQL Server, so I took the first one, made sure it is not used (it had no files on it whatsoever) and just tried Remove from the GUI. Imagine my surprise, when an elementary enough (from my perspective) action of removing a disk made my group empty! So this disk was dependent on the root drive, which in terms was a dependency of the SQL Server, which in terms is depending on the IP and VCN (in short – everything is connected in a logical dependency chain). On top of that, the GUI asked if I am really sure if I want to delete it and if I am – then it will remove the dependencies and remove the resource from my group. Well…it did not! So, I have my SQL cluster group sitting there empty. I tried the suggestion from the above article to move the root disk back to my group, however, due to the large dependency chain, there was no simple way of doing it – it simply failed with error “Details: Error code 0x800700b7 Cannot create a file when that file already exists”.
Taking a step back on this error and looking at the logic of things, I decided that I need to move the SQL Server service resource back to the group. Well, that was not an easy task, because the resource itself is not available in the user interface. I used Get-ClusterResource (reference here) to see the exact naming of the SQL Server instance resource and then, using Move-ClusterResource (reference here) I moved the SQL Server resource to my SQL group, which in its turn took all its dependent resources with it. Voila! Everything was back in its place and the SQL Server was up and running. I will definitely make sure to have some lessons before doing anything similar in the future!