Steps to Install a SQL Server 2008 Service Pack

How to Install a SQL Server 2008 Service Pack

In this article we will take a look at how to install a SQL Server 2008 Service Pack. It is the primary responsibility of a SQL Server Database Administrator to make sure all the SQL Servers within the organizations are applied with the latest Security Updates, Cumulative Updates and Services Packs which are released by Microsoft from time to time. In this article we will take a look at how to install a SQL Server 2008 Service Pack.

Important Note: It is highly recommended that database administrator should backup all User and System database along with Resource database before applying any Security Updates, Cumulative Updates or Service Packs. Resource database is a read only database which stores all the system objects which are included in SQL Server. This database is invisible in management studio and this was first introduced in SQL Server 2005. To know more about Resource database read the article titled Resource Database

Best Practices to follow before installing a SQL Server Service Pack

1.  Perform a full backup of all User, System and Resource database.

2. Note down of the important SQL Server Configuration Settings, Startup Parameters, Linked Servers, and Script out SQL Server Agent Jobs, Script out SQL Server Logins, Memory Utilization, CPU and Disk Utilization etc.

3. Create an appropriate Service Pack Deployment and Rollback Plan

4. Always install Service Packs first in Development environment and then test all applications which are using SQL Server.

5. Once everything looks good in development environment, then only install Service Pack in QA environment. Test all applications which are using SQL Server in QA environment and also test your rollback plan.

6. If everything look good in both Development and QA environment then plan to install Service Pack in a Production environment by communicating an appropriate downtime window to the stake holders and database/application users.

7. Once the Service Pack is installed successfully in a Production environment perform sanity checks to confirm all applications are working fine.

8. Reboot the SQL Server once Service Pack / Cumulative Updates or a Security Updates are applied successfully on the server.

9. If everything looks goods then release the Production environment for user activities and monitor the environment closely for a week or two to make sure there are no unusual spikes in CPU and Memory Utilizations.

Install SQL Server 2008 Service Pack

Let us now start with installing SQL Server 2008 Service Pack 2. You can follow the steps mentioned in this article to apply Service Packs on all Editions of SQL Server 2008. If you are using an RTM version of SQL Server 2008 then you can directly apply SQL Server 2008 Service Pack 2 as this has all the fixes of Service Pack 1.

1. Identify which Version and Edition of SQL Server you are using in your environment and then download the latest available SQL Server 2008 Service Pack from Microsoft website. 

2. Right click the downloaded SQL Server 2008 Service Pack Executable file and then select Run as Administrator option from the drop down as shown in the below snippet.

3. In the Welcome screen of SQL Server 2008 Service Pack, the Setup wizard will check whether all the prerequisites are met before the service pack installation begins. If you encounter any error then you need to resolve them before continuing with the service pack installation.

4. In the License Terms screen you must accept the Microsoft Software License Terms and then click Next to continue with the service pack installation.

5. In the Select Features Screen you can choose the database instance on which you want to install SQL Server 2008 Service Pack. If you want to install service pack only on a default instance then you need select MSSQLSERVER as shown in the below snippet. If you would like to install service pack on a named instance then you need to select the named instance to install service pack on the named instance. Click Next to continue with the service pack installation.

6. In the Check Files In Use screen it will check for services and applications which are using files that SQL Server 2008 Service Pack Setup will require. If there are any such services and applications which are using files required for the service pack setup then you need to stop the process or else you should restart the SQL Server before applying SQL Server Service Pack. Click Next to continue with the service pack installation.

7. In the Ready to Update screen you will be able to see a quick summary of all the features which will be updated with the Service Pack. Click Next to continue with the service pack installation.

8. In the Update Progress screen you will be able to see the progress of service pack installation

9. Once the Service Pack installation is complete you will be able to see the below screen. Click Next to complete with the service pack installation.

10. In the Complete screen you will be able to see a message Your SQL Server 2008 update operation has completed successfully. Click the Close button to complete the service pack installation.

Execute the below mentioned TSQL query to verify the Service Pack Installation.

SELECT
            SERVERPROPERTY('ProductVersion') AS ProductVersion,
            SERVERPROPERTY('ProductLevel') AS ProductLevel,
            SERVERPROPERTY('Edition') AS Edition,
            SERVERPROPERTY('ResourceLastUpdateDateTime') AS 'ResourceLastUpdateDateTime',
            SERVERPROPERTY('ResourceVersion') AS 'ResourceVersion' 
GO

It is highly recommended that you must backup all the System databases (Master, Model, MSDB, ReportServer, and ReportServerTempDB) including Resource database immediately after the successfully installation of SQL Server 2008 Service Pack or a Cumulative Update. It is also a good practice to reboot the SQL Server once the SQL Server Service Pack or a Cumulative Update installation is completed.

Conclusion

In this article you have seen how to apply a SQL Server 2008 Service Pack and the best practices which you need to follow before installing a service pack.

Important note: this will be the last service release of any kind for SQL Server 2008 R2 (there will be no cumulative updates for SP3). So if you are not moving to 2012 and were waiting for some sign to deploy fixes, this may be it.

My usual disclaimer: this update is NOT for SQL Server 2008 (or SQL Server 2012). Only apply to systems where SELECT @@VERSION returns 10.50.xxxx, where xxxx is < 6000.  

Reference:
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_Service_Pack_P1.html
http://www.mytechmantra.com/LearnSQLServer/Install_SQL_Server_2008_Service_Pack_P2.html