Best Practices for SQL Server Decommission

In this post I would like to share some Best Practices about SQL Server Decommission:

1. Be aware of your Client IT Policy

The process of uninstalling / removing permanently a SQL Server Instance(s) goes beyond the technical scope. The stakeholders must be aware of what will happen and how their business will be impacted. Depending on the client’s IT policy, even minor changes on test envrionments must have a clear documentation and technical timeline of the events. This is usually done through a Change Management Tool. Some other factors to consider are:

  • Extra caution is required when removing a single SQL Server Instance on a Cluster environment with another Instances in production that will not be part of the decommission;
  • Make sure to understand if the Client is willing to decommission only the SQL Server product or the whole Windows Server infrastructure, and if another teams are involved;
  • Understand the big picture of why the SQL Server is being removed. Despite it may look obvious ( nowadays most of the times it is cost-saving related), does the client know for sure what he is doing? As the DBA, part of your duties should be a trusted advisor for the client in case you notice they are doing something wrong.

Example: You identify the client need only to remove a single user database instead of uninstalling everything (Consider the non-technical business users) because they do not the difference between a SQL Server instance name, User database name, or even DNS name which they use to connect remotely.

2. Backups

This is a common quote from many SQL Server books: “If you don’t have backups, then you do not have a database at all”. This applies to Decommission scenarios as well.  Chances of the client requesting back the disposed databases might be minimal. Let’s imagine they are of 1% only.  Can you think of the consequences of telling the client their MSSQL is gone for good and and can no longer be restored due to lack of backups? This could cost your Job.

Ensure they are complete before uninstalling MSSQL. If for any reason you are unable to guarantee 100% of backups, it is better to suspend than to move forward to avoid pressure from above. Having the client to pay for some more time for that server running (assuming the decommission is cost-saving related) is preferable over the harsh task of bringing bad news later.

3. Collect useful data

Another tip is to collect useful data that can be helpful in the event of a rebuild. This type of information is commonly used on the client technical documentations (inventory), but it is worth to run some T-SQL scripts to capture actual information from the server prior the change. Such as: Configuration data (Data and Transaction Log locations, Service Pack /Cumulative Update level) are just a few examples.

4. Always use the standard removal method

Microsoft has specific procedures about uninstalling SQL Server Instances from versions 2000 to 2012, either through Standard setup (Add/Remove Programs) and manual ones, where you may need to deal with the Registry Hive. Be careful with this second option and always back up your registry prior changing anything, as it can damage the whole Operating System if something goes wrong there. If the standard removal setup is failing, consider reviewing the Setup Bootstrap Logs and identify what is causing the error and troubleshoot from there on.

These are 4 simple tips. Feel free to add your own comments or any questions you may have about this subject.

Best Regards!

Cluster Quorum Registries pointing to the wrong SQL Server Disk

This situation happened in a Windows Server 2003 SP2 environment on a Clustered Microsoft SQL Server 2005 SP4 instance.

First off, this article helps to understand a little bit more about what is the Quorum resource on a Failover Clustering environment and it’s primary functions.

http://msdn.microsoft.com/en-us/library/windows/desktop/aa371819%28v=vs.85%29.aspx

The SQL Server Instance was not starting and the following error was recorded at the windows application log:

Could not open error log file ‘K:\i2_SystemDb\Microsoft SQL Server\MSSQL.2\MSSQL\LOG\ERRORLOG’. Operating system error = 32(The process cannot access the file because it is being used by another process.).

However, it was normally starting through the Services console (services.msc) from outside the Cluster Administrator. This was a strong indicator that something was definetly wrong with some of the cluster settings.

Since the ERRORLOG file was in use by another process, it was obviously another SQL instance on the cluster. In this case, it was the named one. From SQL Server Configuration Manager it was determined that both  existing instances, the default (MSSQLSERVER) and the named, were pointing to the exact same data drive and files.

Image

-dR:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\master.mdf;-eR:\Microsoft SQL Server\MSSQL.1\MSSQL\LOG\ERRORLOG;-lR:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\mastlog.ldf

-d (Master data file path), -e (ERRORLOG file path), -l (Master log data file path)

The registries also showed this same setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer\Parameters

Image

But, for this scenario, changing the registry directly on this above screen would not work, as the Cluster configurations for the SQL paths resides also in another registry – for the Quorum disk.

This KB article from Microsoft has the solution. The method 1 was used.

http://support.microsoft.com/kb/953504

Image

Image

Note that there is one GUID(unique identifier)  entry for each Cluster resource, such as SQL Fulltext, Volume manager,DTC,Network name, to mention some.

Image

The 000000004 is the checkpoint file name. With these details in hand, the KB steps 5 to 9 (for method 1) must be followed so you can update the registries accordingly (SQLArg keys) with the correct drives path and SQL will be able to start with success.

How can I track what could have caused this?

To reach out the reason when this situation occurs, your starting point should be looking at recent changes to all related resources from the Cluster environment, specially to drives and quorum configuration.

Thank you for viewing this post. Should you have any concern, question or suggestions, feel free to comment.

SQL Server 2008 R2 SP2 Patch error – Missing MSI Files

This situation happened in a Windows 2008 R2 Enterprise X64 environment, when attempting to apply the Service Pack 2 R2 on a Clustered Microsoft SQL Server 2008 R2 Instance.

Missing files scenario may occur when your Windows Server environment have gone under some of these circumstances ( examples):

  • Intentional deletion of install files from C\Windows\Installer folder
  • Security breach and malicious attempt to harm the system
  • A batch program running periodically to clear old files to reclaim disk space (this was my case)

There are 2 types of files used by MSSQL Setup: MSI (Windows installer database file) and MSP(Windows Installer Patch Files). Whenever some of these are missing (are there are many), setup will throw an error or if not, will inform some is missing and give the user a chance to re-enter the correct folder where it is located.

The errors are:

TITLE: SQL Server Setup failure. The cached MSI file ‘C:\Windows\Installer\3bee658c.msi’ is missing. Its original file is ‘sql_fulltext.msi’ and it was installed for product ‘SQL Server 2008 R2 SP2 Full text search’ from ‘F:\MSSQLInstall\SQL2008_R2_x64\x64\setup\’, version ‘10.52.4000.0’, language ‘ENU’.  To resolve this problem, recover the missing file from the installation media and start setup again.

Image

This one prevents the Service Pack setup to start

Image

This other is an example that occurs after the patching has already started.

Fortunately there is  KB article from Microsoft address the resolution steps.

http://support.microsoft.com/kb/969052

I have opted for the script solution, which turned out to be successful after some attempts. Note that after the steps followed and some folders were recreated, the Service Pack 2 R2 appliance still accused some missing files, but even so the patching terminated with success.

The script is named “FindSQLInstalls.vbs”. It will go through all SQL related setup and patches installed and point out what is missing, with the fix instructions.

Image

The above picture illustrates the importance of having the MSP and MSI files in the windows installer cache. They are required for upcoming patches and hotfixes. Full text with this explanation is listed in the Microsoft KB.