Recently I wrote myself a PowerShell unattended AKA silent installation script. I had thoroughly tested this script on clustered installations and stand-a-lone named instances but my tests for stand-a-lone default instances fell short.
I will share the details of my PowerShell unattended install script in my next post but for now we’ll see what happens when you get a NULL value for your instance name passed into the strings that make up your database directories.
What Happened
So, with my command line installation the $InstanceName variable was NULL. As far as the setup.exe option /INSTANCENAME= that was not a problem. A NULL value automatically assumes default instance and the installation will move forward. Now, I was using that same variable in the naming convention for my installation directories so my file path related options looked like this:
INSTALLSQLDATADIR=”C:\Databases\MSSQL10_50.\SQLSystem”
SQLUSERDBDIR=”C:\Databases\MSSQL10_50.\MSSQL\Data”
SQLUSERDBLOGDIR=”C:\Databases\MSSQL10_50.\MSSQL\Logs”
SQLTEMPDBDIR=”C:\Databases\MSSQL10_50.\MSSQL\Data”
SQLBACKUPDIR=”C:\DBBackups\MSSQL10_50.\MSSQL\Backup”
This also was acceptable for the installation, my 2008 R2 instance installed completely fine without any problems at all. Then I tried to install SP1 and the world went crazy. My first indication of a problem was in the error messages from the upgrade failing. The messages were extremely vague and not of much use right away so I went to the system logs as soon as I noticed that my instance would not start.
Every time I started my instance it would be up for a short period of time in script upgrade mode, which is a status where no one can log in but the instance is trying to run scripts on the databases to complete the upgrade. Shortly after beginning to run the scripts the instance would fail with this message in the Windows>Application logs.
Script level upgrade for database ‘master’ failed because upgrade step ‘sqlagent100_msdb_upgrade.sql’ encountered error 15240, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.
As it states, “If the error happened during upgrade of the ‘master’ database, it will prevent the entire SQL Server instance from starting,” my failure was within the master database. I tried to do some craziness with modifying the upgrade scripts but I’m here to tell you not to even try. I wasted a lot of time and was not successful.
What I did find out was that the upgrade scripts didn’t mesh with my folder structure. It could be because the folder name ended in a period, I’m not sure, but the moral of the story was that I now needed to relocate (rename the folder) my instance directories. I also wanted to do this so that it could match the naming convention that I meant for it to be. I could have simply re-installed the instance but user databases were loaded onto it in between my initial install and the upgrade so I was trying to centralize the work effort on myself and not other developers since it was my own fault.
How to fix the system database paths
First thing I did was shutdown the services that were still running. I want to rename these folders regardless of whether the service is currently operational or not. Then I went ahead and renamed the folders that I wanted changed. From there I use the Configuration Manager to get as much of this work done as I can through the GUI. I show this because there are multiple locations for some of these paths in the registry and I didn’t want to miss any so I put my faith in Microsoft for those changes.
The services I had installed are as shown above. None of these have editable binary paths via the Configuration Manager but SQL Server, SQL Server Browser, and SQL Agent all have Dump directories which are relative to the root database path so we will want to update those.
Once you’ve updated your dump directories it is important to update the locations within the start up parameters for SQL Server. You will have to alter the location of your master.mdf, master.ldf, and the ERRORLOG. These are seen below with the -d, -l, and -e switches.
With the new folder names and new start up parameters your master database will load but the instance will fail to start because all of your other system database locations are stored within master. Since you can’t get the instance to start without these databases loaded you will need to change these locations somehow. Assuming you don’t have outstanding upgrade scripts (like I did in this situation) you can append -T3608 to your SQL Server start up string which will initiate trace flag 3608 and prevent any database besides master from starting. This will allow you to log in to master and run ALTER DATABASE <db_name> MODIFY FILE commands on the other system databases. Even with this complete you aren’t completely out of the woods so stay tuned for how to get your SQL Agent to start.
In my case I had outstanding upgrade scripts that needed to run. With that being said trace flag 3608 would allow my instance to start but I could never log in to run the ALTER DATABASE commands because the upgrade scripts would not complete and therefore not relinquish control of the instance. Once I hit this roadblock I realized that I needed to get the master database rebuilt while still allowing this upgrade to finish. This is when I dived into the registry.
I intend upon using the installation media to rebuild my system databases but I want to make sure that nothing gets put in the wrong spot. So I manually edit the following registry keys to match my new directory path.
HKLM>SOFTWARE>Microsoft>Microsoft SQL Server>MSSQL10_50.MSSQLSERVER>MSSQLServer>BackupDirectory
HKLM>SOFTWARE>Microsoft>Microsoft SQL Server>MSSQL10_50.MSSQLSERVER>MSSQLServer>DefaultData
HKLM>SOFTWARE>Microsoft>Microsoft SQL Server>MSSQL10_50.MSSQLSERVER>MSSQLServer>DefaultLog
Next I executed the below command in PowerShell (works just fine in command prompt if you omit the ‘.\’ at the beginning) in the root directory of my installation media.
.\setup.exe /QUIET /ACTION=REBUILDDATABASE
/INSTANCENAME=MSSQLSERVER
/SQLSYSADMINACCOUNTS=”MyAccount” /SAPWD=”MyPW”
/SQLCOLLATION=”CollationName”
The SAPWD parameter is required if your are in Mixed Mode Authentication and the SQLCOLLATION parameter is optional in case you want to rebuild your databases with a different collation then was originally installed.
Once this command ran, my databases were rebuilt and my upgrade scripts began running as soon as I started the instance. A few minutes later I had my instance back but, as I mentioned above, the SQL Agent was still broken. So I jumped back into the registry and updated these two values.
HKLM>SOFTWARE>Microsoft>Microsoft SQL Server>MSSQL10_50.MSSQLSERVER>SQLServerAgent>WorkingDirectory
HKLM>SYSTEM>CurrentControlSet>Services>SQLSERVERAGENT>ImagePath
Once those are updated your SQL Agent should start up as normal and you have your instance under control. If you are still having issues with your SQL Agent, check out this post by pradeepadiga.
Lesson Learned
I felt like I was done at this point but then I had one more problem. I had user databases that were previously attached to this instance that were no longer attached because the master database was rebuilt. From here I just reattached the user databases and had to re-add a few logins to get things back to normal. With brand new system databases you would need to re-create any logins, credentials, proxies, agent jobs, SSIS packages and anything else that resides in the system databases. This is why I would have gone with the ALTER DATABASE <my_db> MODIFY FILE approach that I mentioned above if I had that as an option.
Truly Complete
The last thing I did before moving on to my next task was fix the bug in my unattended install script.
Leave a Reply