As part of the Microsoft CSS group, we get to deal with a lot of production as well as non-production related issues. While we are troubleshooting such issues, we also end of dispelling some or the other myth that exists regarding SQL Server architecture.

So, this time when Sankar Reddy (Blog | Twitter), a SQL Server MVP, decided to host T-SQL Tuesday, a community blogging effort started by long time SQL Server MVP, Adam Machanic (Blog | Twitter), I decided to contribute a post on this month’s topic “Misconceptions in SQL Server”.

There are multiple posts by very eminent people in the SQL community that have debunked various SQL Server myths. If you are not a SQL Server specialist and buy these myths that have spread around in the community like wild fires, you would probably think that SQL Server and MS Access are the same. And as any good SQL Server DBA would know that this is not true! Since, T-SQL Tuesday is an effort to enhance and share community knowledge, I shall attempt to refute and clarify certain common myths that I deal with day-in and day-out during the course of my work with SQL CSS. My first series will be on

Series #1: SQL Server Services and Patching

It is alright to change the SQL Service account and password from the Service Manager snap-in
This is one of the most common misconceptions that people across the world have in this area. The SQL Server Configuration Manager has been designed to do the following (other than a host of other configuration activities):

1. Change SQL service account
2. Update the password of the SQL service account
3. Make configuration related changes like adding trace flags

If you have not used Configuration Manager to do any of the above and have not faced an issue, then you are lucky! But Lady Luck may not always be on your side! The Configuration Manager (available from SQL Server 2005 onwards) does a host of other activities when you make a change to your service account like granting the necessary permissions required on the Registry for the SQL service account etc. which the Services Manager doesn’t do. Even in SQL Server 2000, service account changes should be done through Enterprise Manager as it grants the necessary permissions required to use the Full-text feature. As new features get added to the product, a lot of considerations w.r.t. ACLs, Registry permissions, encryption keys, etc. need to be taken into account while modifying SQL Service accounts. The Configuration Manager was designed keeping these considerations in mind. So, this is not a new recommendation but has been around for more than a decade.

The other option that you have is to use WMI and the SQL Server namespaces to programmatically change the SQL service account. I had posted an example of this on Technet. If you want to modify Reporting Services service accounts, then it would be a bit more complex because you would need to manage the encryption keys etc.

Windows Cleanup Utility can be used to uninstall SQL Server
All I have to say to this is NOT a good idea if you still want to be the DBA, Windows Cleanup Utility shouldn’t be used unless and until Microsoft has published the same (via an official blog post or KB Article) to be used for a certain scenario. Windows Cleanup Utility is a generic tool that uses Windows Installation GUIDs from the registry to annihilate the product’s existence from your box! Since, we use Windows Installer to install and patch SQL Server (true for SQL Server 2005 to SQL Server 2008 R2), the SQL component registry entries are centrifugal to the SQL’s existence on the box. If these are not cleaned up by our uninstaller program correctly, then you can have a server which behaves very goofily the next time you run a SQL installation program. The worst case scenarios that I have seen are:

1. No further patching possible for existing instances of SQL on the box
2. No further new installations of SQL Server on the box

You wouldn’t want to land up in either situation. In such situations, you might even have to rebuild the box! Not pretty when you have multiple instances running on the server!

Different nodes can have different builds for the same SQL instance
SQL Server clusters are not supported in scenarios where different nodes belonging to the same clustered instance are on different builds. The reason behind this is that are be code differences in the SQL builds that we release. So if you failover to a lower build of the SQL Server, then you can expect to see some known issues surfacing which do not occur on the other node/nodes.

SQL Server setup will automatically patch the newly added node
If you have recently added a node to the SQL instance, then you should patch it immediately to the current build of the SQL clustered instance that you added the node to, before putting that node into production. Till SQL Server 2005, there was no option of slip-streaming such installations. You can use the steps mentioned in the following KB Article in case you run into issues while patching the newly added node.

SQL Server services don’t go offline while patching the SQL instance
Another common misconception that makes applications connecting to the SQL Server instance unhappy (read: application timeouts) if they are kept online during SQL setup. At a high level, I like to look at SQL Server setup as a two step process. The first step involves MSI (Windows Installer) actions, which are responsible for making the necessary registry changes, laying down the binaries and other Windows related configuration changes including .NET (if necessary). The second step is when the SQL Server instance is started up and configuration scripts (T-SQL) are executed to configure the SQL instance and make the necessary meta-date changes required for the system metadata residing in the SQL system databases. For the first step, the SQL instance needs to be offline as the system database files(like mssqlsystemresource) cannot be updated if the SQL instance has a lock on the files. When the SQL Service is started for configuring the instance, it is in single user mode with certain parameters, so that applications cannot connect to it during that time. It is always advisable to take a downtime during patching of SQL instances. Hot patching is still not available in SQL Server.

Deleting files from the C:\Windows\Installer folder will free up disk space
A mistake that I have seen so many people make, that I have lost count. Yes, it will free up your disk space but at a cost where it is possible that you might not be able to patch your existing applications which use Windows Installer for installation/patching. Surprisingly, this is a hidden folder and still people think it’s wise to delete files from that folder. We made it hidden for a reason!! As mentioned before in this blog post, that SQL installation uses Windows Installer. The files .MSI and .MSP) used during installation are cached in the Windows Installer folder. If you delete these files, then you will land up with various missing MSI/MSP files error. SQLSeverFAQ (Blog | Twitter), a SQL CSS MSDN blog, has multiple posts has step-by-step instructions when you run into such issues. Disclaimer: The steps mentioned in the posts are NOT for the faint-hearted! So, avoid Shift+Delete of such files!

Reference :
Part – 1 – SQL Server 2005 Patch Fails to install with an error Unable to install Windows Installer MSP File
SQL SERVER 2005 SP3 Install Fails with Error 1612- Unable to install Windows Installer MSP file

In case I have missed out any other common myth related to SQL Services and Setup, please feel free to leave a comment and I shall add it to the post. Leave your name and Twitter handle (if you have one) and I shall acknowledge the credit for the myth to you.