Introduction
With the release of SQL Server 2008 SP2 and its subsequent cumulative updates, the clock is ticking on SQL Server 2008 SP1 (EOL 10/11/2011). Rolling out a service pack together with one of the more recent cumulative updates can prove to be a time-consuming task depending on how many instances you support. With this article, I hope to show everyone a different way to patch their SQL Server 2008 instances by using a single batch file to kick off the installations. Our time as DBAs is valuable, and we shouldn't have to click through the GUI for each and every SP and CU update we'll need to deploy this year.
Background Information
Patching has always been something that my organization tends to struggle with (and I'm sure we're not alone). As we all should know, it's important to stay on top of patches, especially the service packs, because Microsoft generally only supports the previous service pack for up to one year after the release of a new service pack. Therefore, one of the organizational goals for my employer for the year is to get these the major service pack for SQL Server 2008 rolled out to our environment of over 100 SQL Server 2008 instances.
In an effort to find ways to make the installation of these patches easier, I determined that we can install the patches from the command line, and rolled that methodology up into a full-blown process to reboot, patch with the service pack, reboot, patch with a cumulative update, and then reboot one last time to try and get us up to the most recent patch level available.
Patching from the Command Line
SQL Server 2008 service packs and cumulative updates provide the option of a command-line interface to perform the installation. In this section, I'd like to simply show how this can be accomplished, and I'll show you how this tip fits in with the Windows Tasks that I'll use to perform the patch installations. Below, you can see the commands to install SQL Server 2008 SP2 from the command line (x86 and x64 examples shown).
32-bit Windows: SQLServer2008SP2-KB2285068-x86-ENU.exe /allinstances /quiet
64-bit Windows: SQLServer2008SP2-KB2285068-x64-ENU.exe /allinstances /quiet
You may want to ensure that the account you are logged in as has sysadmin rights to the instance to avoid any potential security issues. I'm sure there are ways to give less privileges, but I have not tested this method out with anything less than sysadmin. From the commands shown above, the executables are the executables downloadable here, the "/allinstances" switch specifies that all SQL Server 2008 components should be upgraded, and the "/quiet" tells the installation to run without any user interaction. To note, the "/quietsimple" option is also available in place of "/quiet" and it would also not provide any user interaction, but it would display the progress window to see how far along the installation is. I am using the "/quiet" switch here since I'll be installing from a Windows Scheduled Task and won't have a need for the status window.
It's also important to note that the Service Pack executable can be replaced with any of the Cumulative Updates released. As you'll see in the scripts below and attached, I am utilizing a cumulative update executable as well (SP2 CU2 to be exact).
Schtasks.exe Introduction
Schtasks.exe is a windows command line utility that can be used to created windows scheduled tasks, and I will use this tool to schedule .bat files at system startup. More information about this tool can be found on TechNet by going here.
My shop uses a few combinations with regard to 2008 on Windows 2003 32/64bit and on Windows 2008 R2 (Note: Win 2008 R2 is only a 64-bit OS, we do not use Windows 2008 R1). For this article, I use schtasks on both Windows 2003 and Windows 2008 R2 to show the subtle difference with regard to the User Account Control (The UAC was introduced with Windows 2008 R1). The difference is with the RL (stands for run-level) parameter, and it is used by specifying "/RL HIGHEST" in the schtasks call in order to run the tasks with elevated rights in Windows 2008 R2.
Two small examples:
Windows 2003: schtasks /create /tn "Test_Task_Name" /tr "C:\temp\a_sample_batch_file.bat" /sc onstart /ru "DOMAIN\youradminaccount" /rp "youradminpassword"
Windows 2008 R2: schtasks /create /tn "Test_Task_Name" /tr "C:\temp\a_sample_batch_file.bat" /sc onstart /ru "DOMAIN\youradminaccount" /rp "youradminpassword" /RL HIGHEST
To break down the command:
- "/create" specifies that you would like to create a new Windows Scheduled Task.
- "/tn" stands for "task name" and specifies the name of the task.
- "/tr" stands for "task run" and specifies what you would like to run.
- "/sc" stands for schedule and "onstart" means run the task when Windows starts up.
- "/ru" stands for "run user" and will be the context that the task runs under.
- "/rp" stands for "run password" and will be the password of the user you specified for "/ru"
- In Windows 2008 and up, you'll need "/RL" which stands for "run level". For this, "HIGHEST" equates to "Run As Administrator".
For either OS, ensure that the account you set the tasks up to run under has the "Log on as a batch job" logon right on the server. This can be assigned through gpedit.msc. By default, administrators on the server are granted this right, but some shops may tweak this to only allow specific accounts to have this right. For either OS, you can go to Start > Run > gpedit.msc [Enter] > Windows Settings > Security Settings > Local Policies > User Rights Assignment > <Change "Log on as a batch job" to include your "/ru" account>.
Patching using Batch Files and the Windows Task Scheduler
Bringing the command line installation of patches as well as the schtasks.exe executable together, we can effectively patch our systems with in this sequence:
- Reboot System
- Install Base Service Pack
- Reboot System
- Install Cumulative Update
- Reboot System
I will provide two examples below:
- SQL Server 2008 SP2 CU2 patching on Windows 2003 32-bit. This illustrates using the process without the User Access Control (UAC) being in place.
- SQL Server 2008 SP2 CU2 patching on Windows 2008 R2 (64-bit only). This illustrates using the process with the User Access Control (UAC) being in place.
Example 1 - SQL 2008 SP2 CU2 on Windows 2003 (32-bit)
All of the batch files needed are provided in the attached SQL 2008_SP2_CU2_Win2003_32bit.zip file. To completely configure the directories for use, you'll need to complete the following:
- Extract the contents of the SQL2008_SP2_and_CU_2k3_32bit.zip file to C:\temp and you'll end up with the expected folder structure.
- Second, you'll need to download the Base SP2 32-bit executable (available here) and place it into the C:\temp\SQL2008_SP2_and_CU_2k3_32bit\BASE folder.
- Third, you'll need to request and download the SQL 2008 SP2 CU2 patch. To do so, visit this link, and click on "View and request hotfix downloads", agree to the terms and conditions, click on "Show hotfixes for all platforms and languages" and select the x86 file "SQLServer2008_PCU2_CU2_2467239_10_00_4272_x86", enter your email address, confirm the address, validate the image, and click on "Request hotfix". Follow the instructions in the e-mail to download the patch. After downloading the patch, place SQLServer2008-KB2467239-x86.exe into the C:\temp\SQL2008_SP2_and_CU_2k3_32bit\CU folder.
Now that the files have been extracted and everything is in place, let's take a look at how the batch files flow.
Batch File Sequence Explained
1. SP2_CU2_Install.bat kicks off the entire sequence of events by creating the Windows Task to install the base SP2 on the next system start, and then restarts the system. Change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL. For our shop, we grant our SQL Server service account sysadmin rights and put it in the Administrators group local to the server.
REM Create task to install SP2 when system boots up
schtasks /create /tn "SQL2008_SP2_Base" /tr "C:\temp\SQL2008_SP2_and_CU_2k3_32bit\BASE\install_SP2_BASE.bat" /sc onstart /ru "DOMAIN\youradminaccount" /rp "youradminpassword"
REM Reboot the host before kicking off the install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
This first batch file illustrates the creation of a Windows Scheduled Task using schtasks.
2. install_SP2_BASE.bat is kicked off at system startup after the reboot. This installs the base service pack, then schedules the CU install on the next system startup, and concludes by rebooting the system. Once again, change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL.
REM starting SQL SP2 base install.
"C:\temp\SQL2008_SP2_and_CU_2k3_32bit\BASE\SQLServer2008SP2-KB2285068-x86-ENU.exe" /allinstances /quiet
REM SQL SP2 install complete
REM create the task to install the cumulative update
schtasks /create /tn "SQL2008_SP2_CU" /tr ""C:\temp\SQL2008_SP2_and_CU_2k3_32bit\CU\install_SP2_CU.bat"" /sc onstart /ru DOMAIN\youradminaccount /rp youradminpassword
REM Reboot the host before kicking off the CU install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
The second batch file in the flow shows the call to the base SP executable.
3. install_SP2_CU.bat is kicked off when the system starts back up after the reboot following the base SP install. This installs the cumulative update for the service pack, schedules the cleanup task to run on the next system startup, and concludes by rebooting the system. Once again, change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL.
REM delete the scheduled task for the SP2 base install
schtasks /delete /tn "SQL2008_SP2_Base" /F
REM starting SQL SP2 cu install.
"C:\temp\SQL2008_SP2_and_CU_2k3_32bit\CU\SQLServer2008-KB2467239-x86.exe" /allinstances /quiet
REM SQL SP2 cu install complete
REM create the task to cleanup the old tasks
schtasks /create /tn "SQL2008_SP2_Cleanup" /tr "C:\temp\SQL2008_SP2_and_CU_2k3_32bit\Cleanup\cleanup_tasks.bat" /sc onstart /ru DOMAIN\youradminaccount /rp youradminpassword
REM Reboot the host before kicking off the install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
The third batch file shows the use of schtasks to delete a Windows Scheduled Task as well as shows the call to the cumulative update for the service pack. Notice that the call to the CU is the same syntax as the call to the service pack.
4. Finally, cleanup_tasks.bat file will kick off when the system comes back up. At this point, the patching is now complete and no additional reboots will take place. That batch file will attempt to remove any lingering Scheduled Tasks that weren't removed and clean up the C:\temp directory by removing the SQL2008_SP2_and_CU_2k3_32bit folder.
*Please Note: I have had mixed success with all files and tasks being cleaned up, so any tips here would be truly appreciated.
REM delete the scheduled task for the SP2 base install
@echo off
cls
schtasks /query > doh1
findstr /B /I "SQL2008_SP2_Base" doh1 >nul
if %errorlevel%==0 goto :disableBase
cls
schtasks /query > doh2
findstr /B /I "SQL2008_SP2_CU" doh2 >nul
if %errorlevel%==0 goto :disableCU
goto :deleteDirectories
:disableBase
schtasks /delete /tn "SQL2008_SP2_Base" /F
:disableCU
schtasks /delete /tn "SQL2008_SP2_CU" /F
:deleteDirectories
rmdir C:\temp\SQL2008_SP2_and_CU_2k3_32bit /s /q
schtasks /delete /tn "SQL2008_SP2_Cleanup" /F
Within the cleanup script, you'll see I am using schtasks to query the task list for the BASE and CU tasks and deleting them if they exist. I then call rmdir to remove the directory with all of the files in the C:\temp folder to finalize the patching steps.
Kicking off the Sequence
Before kicking off the patching sequence, I would recommend ensuring that you have at least 5GB available on your C:\ drive (or main Windows drive) to ensure the patching can run to completion. All other pre-patch considerations should also be addressed (backups, rollback, halting OLTP activity as best as possible, etc).Once all of the above files have been prepared with your accounts, and the executables have been put in place, simply executing SP2_CU2_Install.bat should be all that is required to get your system patched.
Example 2 - SQL 2008 SP2 CU2 on Windows 2008 R2 (64-bit)
All of the batch files needed are provided in the attached SQL 2008_SP2_CU2_Win2003_32bit.zip file. To completely configure the directories for use, you'll need to complete the following:
- Extract the contents of the SQL2008_SP2_and_CU_2k8_64bit.zip file to C:\temp and you'll end up with the expected folder structure.
- Second, you'll need to download the Base SP2 64-bit executable (available here) and place it into the C:\temp\SQL2008_SP2_and_CU_2k8_64bit\BASE folder.
- Third, you'll need to request and download the SQL 2008 SP2 CU2 patch. To do so, visit this link, and click on "View and request hotfix downloads", agree to the terms and conditions, click on "Show hotfixes for all platforms and languages" and select the x64 file "SQLServer2008_PCU2_CU2_2467239_10_00_4272_x64", enter your email address, confirm the address, validate the image, and click on "Request hotfix". Follow the instructions in the e-mail to download the patch. After downloading the patch, place SQLServer2008-KB2467239-x64.exe into the C:\temp\SQL2008_SP2_and_CU_2k8_64bit\CU folder.
Once the files have been extracted and everything is in place, we can then take a look at these files (very similar to the Win 2003 32-bit example shown above) and how they flow.
Batch File Sequence Explained
1. SP2_CU2_Install.bat kicks off the entire sequence of events by creating the Windows Task to install the base SP2 on the next system start, and then restarts the system. Change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL. For our shop, we grant our SQL Server service account sysadmin rights and put it in the Administrators group local to the server.
REM Create task to install SP4 when system boots up
schtasks /create /tn "SQL2008_SP2_Base" /tr "C:\temp\SQL2008_SP2_and_CU_2k8_64bit\BASE\install_SP2_BASE.bat" /sc onstart /ru "DOMAIN\youradminaccount" /rp "youradminpassword" /RL HIGHEST
REM Reboot the host before kicking off the install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
This first batch file illustrates the creation of a Windows Scheduled Task using schtasks. Different from the Windows 2003 schtasks call, you'll see "/RL HIGHEST" in the call on Windows 2008 R2, and this is because we need the task to run with elevated rights on the Win 2008 R2 operating system.
2. install_SP2_BASE.bat is kicked off at system startup after the reboot. This installs the base service pack, then schedules the CU install on the next system startup, and concludes by rebooting the system. Once again, change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL.
REM starting SQL SP2 base install.
"C:\temp\SQL2008_SP2_and_CU_2k8_64bit\BASE\SQLServer2008SP2-KB2285068-x64-ENU.exe" /allinstances /quiet
REM SQL SP2 install complete
REM create the task to install the cumulative update
schtasks /create /tn "SQL2008_SP2_CU" /tr ""C:\temp\SQL2008_SP2_and_CU_2k8_64bit\CU\install_SP2_CU.bat"" /sc onstart /ru
DOMAIN\youradminaccount /rp youradminpassword /RL HIGHEST
REM Reboot the host before kicking off the CU install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
The second batch file in the flow shows the call to the base SP executable, and once again the call to schtasks includes the "/RL HIGHEST" parameter which is different than the Win 2003 call.
3. install_SP2_CU.bat is kicked off when the system starts back up after the reboot following the base SP install. This installs the cumulative update for the service pack, schedules the cleanup task to run on the next system startup, and concludes by rebooting the system. Once again, change "DOMAIN\youradminaccount" and "youradminpassword" to your account and password that has access on the server and within SQL.
REM delete the scheduled task for the SP2 base install
schtasks /delete /tn "SQL2008_SP2_Base" /F
REM starting SQL SP2 cu install.
"C:\temp\SQL2008_SP2_and_CU_2k8_64bit\CU\SQLServer2008-KB2467239-x64.exe" /allinstances /quiet
REM SQL SP2 cu install complete
REM create the task to cleanup the old tasks
schtasks /create /tn "SQL2008_SP2_Cleanup" /tr "C:\temp\SQL2008_SP2_and_CU_2k8_64bit\Cleanup\cleanup_tasks.bat" /sc onstart
/ru DOMAIN\youradminaccount /rp youradminpassword /RL HIGHEST
REM Reboot the host before kicking off the install
shutdown.exe /r /f /c "DBA scheduled reboot" /t 30
The third batch file shows the use of schtasks to delete a Windows Scheduled Task as well as shows the call to the cumulative update for the service pack. Notice that the call to the CU is the same syntax as the call to the service pack.
4. Finally, cleanup_tasks.bat file will kick off when the system comes back up. At this point, the patching is now complete and no additional reboots will take place. That batch file will attempt to remove any lingering Scheduled Tasks that weren't removed and clean up the C:\temp directory by removing the SQL2008_SP2_and_CU_2k3_32bit folder.
*Please Note: I have had mixed success with all files and tasks being cleaned up, so any tips here would be truly appreciated.
REM delete the scheduled task for the SP2 base install
@echo off
cls
schtasks /query > doh1
findstr /B /I "SQL2008_SP2_Base" doh1 >nul
if %errorlevel%==0 goto :disableBase
cls
schtasks /query > doh2
findstr /B /I "SQL2008_SP2_CU" doh2 >nul
if %errorlevel%==0 goto :disableCU
goto :deleteDirectories
:disableBase
schtasks /delete /tn "SQL2008_SP2_Base" /F
:disableCU
schtasks /delete /tn "SQL2008_SP2_CU" /F
:deleteDirectories
rmdir C:\temp\SQL2008_SP2_and_CU_2k8_64bit /s /q
schtasks /delete /tn "SQL2008_SP2_Cleanup" /F
Within the cleanup script, you'll see I am using schtasks to query the task list for the BASE and CU tasks and deleting them if they exist. I then call rmdir to remove the directory with all of the files in the C:\temp folder to finalize the patching steps.
Kicking off the Sequence
Before kicking off the patching sequence, I would recommend ensuring that you have at least 5GB available on your C:\ drive (or main Windows drive) to ensure the patching can run to completion. All other pre-patch considerations should also be addressed (backups, rollback, halting OLTP activity as best as possible, etc). Once all of the above files have been prepared with your accounts, and the executables have been put in place, simply executing SP2_CU2_Install.bat with Elevated Rights (Right click > Run As Administrator)should be all that is required to get your system patched.
Some SQL Server 2008 Failover Clustering Considerations
- Because of how SQL 2008 handles patching in clusters, we are able to fully patch an inactive cluster node using the methodology described.
- Many of the clusters at my organization are active/passive in nature, so we can fully patch inactive nodes with the provided scripts with no impact on production traffic, and then simply failover into the patched node, and then proceed with executing the scripts on the un-patched, the now inactive node of the cluster.
- The scripts are built to update "allinstances", meaning if you have more than one instance on a cluster and use these scripts, both will be upgraded.
- For granular control, you can modify the scripts, but the GUI may be just as effective time-wise if you won't have repetition in the names of the instances or the complexity of your configuration.
Expanding on the Process
I only showed two variations here, SQL 2008 on Windows 2003 32-bit and Windows 2008 R2 (64-bit). You may also have SQL 2008 running on Windows 2003 64-bit or running Windows 2008 R1 32/64-bit. Altering the scripts to account for those changes should be fairly easy to do. Simply update the patch executables to the proper bit, and then ensure the schtasks parameters are correct for your operating system (/RL HIGHEST is needed for Win 2008/ Win2008 R2, and is not a valid parameter on Windows 2003).
You could easily schedule the main SQL 2008 SP2_CU2_Install.bat for scheduled implementation by creating a windows scheduled task for when to the installation off. You could also extend the automation to copy the files down locally at run time. I prefer to prep everything, and for our production installations, I like to kick them off manually. However, I hope this at least spawns some additional automation thoughts.
SQL Server 2005/2008 R2
SQL Server 2005 also offers similar command line options, and I believe you should have no problems getting batch files built to handle SQL Server 2005 SP4 which was released in January of this year. We'll all need to get that rolled out by January of next year to stay within MS expectations for extended support for that product.
SQL Server 2008 R2 most likely behaves the same as SQL Server 2008. Unfortunately, we don't have any SQL Server 2008 R2 rolled out as of yet.
Summary
With this article, I showed how it is possible to perform relatively unattended installations of the service packs and cumulative updates for SQL Server 2008. By using the command line options that are available to use on SQL Server 2008 hotfixes as well as the native Windows Task Scheduler command line utilize, schtasks.exe, we are able to run through an entire sequence of events to reboot, install a base service pack, reboot, install a cumulative update, and reboot a third time, without a lot of manual intervention.
For my organization, we have opted to handle each instance one at a time during scheduled maintenance windows, and these scripts have helped standardize the installations while reducing clicks, and improving DBA efficiency. We can kick off the installations and come back 45 minutes to an hour later and simply check the setup bootstrap logs (%install dir%\100\Setup Bootstrap\Log\) to validate success/failure, and then finally confirm the build number of all of the components updated.
I hope you enjoyed the article and got to see patching from a new angle, and I look forward to seeing your feedback.
References
Schtasks.exe (including the references to what has changed with windows 2008 R2)
http://technet.microsoft.com/en-us/library/cc725744%28WS.10%29.aspx
SQL Server 2008 Rolling Cluster Patching:
http://support.microsoft.com/kb/958734
SQL Server 2008 Service Pack LifeCycle:
http://support.microsoft.com/lifecycle/?p1=13165
"Log on as a batch job" - Security Article
http://technet.microsoft.com/en-us/library/cc938847.aspx