How to install SQL Server 2005 SP3

Applies to: Microsoft SQL Server 2005Download SQL Server 2005 SP3.

Please download SQL Server 2005 SP3 from here.

Risk Analysis.

- After SQL Server 2005 SP3 has been applied, it cannot be removed without uninstalling SQL Server 2005 completely.- Test it on a test environment.- Contact vendors of third-party software using your database server and get their approval.

Requirements.- Use windows authentication instead of SQL Server Authentication when applying SP3.-

The windows account used to run the SP3 setup program must have administrator privileges on the computer where SP3 will be installed.-

Disk space needed for the installation:

Permanent
50 MB - SQL Server installation directory1 GB - System driveAt least 500 KB of free space on system databases (better if you set autogrow on)

Temporary
600 MB - Temp Directory334 MB - SP3 package. Remove it after applying SP3

Pre-installation tasks.-

Backup system databases.-

Backup user databases.-

Back up your Analysis Services databases, configuration file, and repository.-

Pause mirroring.- Enable named pipes on your SQL Server 2005 server.-

Disable DDL Triggers.-

Verify that SQL Server 2005 Service Accounts are not disabled.-

Resources that have dependencies on SQL Server resources must be removed or taken offline prior applying SP3 .-

Stop all applications and services that make connections to the instance being upgraded. You cannot stop services in a failover cluster environment.
In case of a failover cluster environment, configure Microsoft Distributed Transaction Coordinator on the cluster.

Also, verify the following services are running on both nodes:
- Task Scheduler

- Com+ System Application Service

- Cryptographic Services

- Remote Registry Service

- ServerInstallation.

In case of a failover cluster environment, please consider the following:
- Do not stop the cluster service during installation or before applying SP3.

- Do not stop any running processes before running SP3 Setup.

Normal (non-clustered) installation:

- To install SQL Server 2005 SP3, run the self-extracting package file. Run SP3 Setup.

- After applying SP3, install cumulative update package 1 for SQL Server 2005 Service Pack 3

Installing SP3 on a failover cluster environment:

- Run SP3 Setup on the active node.

- After applying SP3, install cumulative update package 1 for SQL Server 2005 Service Pack 3 on the active node.

Post-installation Tasks.

- Restart each node of the cluster.

- Restart services that have dependencies on SQL Server.

- Verify proper operation of client applications.

- Backup system databases after successful installation.

Following key points should be considered while designing storage system for consolidated SQL Server environment.

RAID 1/0 (or RAID 10) gives excellent random read/write performance. RAID 10 is ideal for OLTP environment with lots of small random read/writes. This RAID type also provides good fault tolerance since it can survive the failure of up to half of the disks, provided one disk in each mirror image pair survives. Use RAID 10 if more than 30 percent of the IO is small random writes and if budget permits. RAID 10 is recommended for OLTP Data, Transaction Log, and Tempdb data volumes.

RAID 5 gives excellent read performance, especially large sequential I/O. But it provides lower random write performance. It also delivers flower fault tolerance than RAID 10 since it can tolerate only one drive failure per RAID 5 LUN. Also, in the event of a drive failure, the time for the storage system to rebuild the content of the failed drive is longer than RAID 10. RAID 5 costs less for the same storage capacity compared to RAID 10. RAID 5 is ideal for Backup volumes.

SQL Server log files are accessed sequentially and are write intensive. Since RAID 5 requires 4 I/O per write (write data, validate data, write parity, and validate parity), placing the log files on RAID 5 array greatly increases the possibility for an I/O bottleneck. Placing the data file and log files on the same drive spindles creates contention for the drive heads between the sequential movement required for the log writes versus the random drive head movement required to read and write data. Because of this, the log file should not be placed on the same set of spindles as the data files. The log files should be placed on a RAID 1+0 arrays.

SQL Server 2005 makes greater use of tempdb than SQL Server 2000. For high performance applications, tempdb should be placed on a dedicated RAID 1+0 array and sized large enough so it does not auto grow. TempDB has a large number of objects being created all the time. For an object to be created, space must be allocated to it. Space allocation is determined by looking at some of the internal system pages (the GAM, and SGAM). In the end, it is these pages that start to have significant contention (with just one file) in a VERY active TempDB. To minimize that contention you should create multiple files. The guideline is to create as many numbers of files as the number of processor cores on the system. So, for example if you have dual core 8 way processor, you should have 16 files for your tempdb database.

Backups should be made to drives other than the ones used for data and log not only to separate out I/Os, but physically separate drives means that even if the data or log disks fail, the backup drive will most likely still exist. Writing the backup file to the same set of disks means that the data file is being read from and the backup file is written to all using the same I/O pool. It also puts available space at risk since backups can fill up drives quickly without a proper retention policy to manage them.

All versions of SQL Server write in 8k pages and the read ahead is 64k. The recommendation is to format any disks which may be used for SQL Server data using a 64k block size. Using defaults for NTFS (which is not 64K) will hamper SQL Server performance. Even if there is the potential that a disk may be used for SQL Server data, format it with a 64k block size.

SQL Server on non-optimized and shared SAN where many different applications and I/O profiles access the same spindles can result in performance issues. Sections of the storage should be reserved for high performance SQL Server requirements and carved up at the time of deployment of the particular SQL Server solution. These storage allocations should not come from the general storage pool.

Sector alignment. Sector alignment can increase the I/O performance of SQL Server by up to 20%. If sector alignment is required, this will be done via the command line utility DISKPART in Windows Server 2003 prior to formatting the disks with NTFS.

NTFS Cluster size

Format D: /FS:NTFS /A:64K

Clear Putty's Cache

1. Open the registry (regedit)

2. Go to HKEY_CURRENT_USER\Software\SimonTatham\PuTTY\SshHostKeys

There you should see Putty’s cache of host keys.The ‘name’ column tells you which key is for which server.

For example, it will have the format of @: [rsa2@22:172.16.117.159]

3. Delete the rows that you need and presto!

Cumulative update package 7 for SQL Server 2005 Service Pack 3

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

SQL_Server_2005_SP3_Cumulative_Update_7_SQLWriter

SQL_Server_2005_SP3_Cumulative_Update_7_XMO -XML Management Object

SQL_Server_2005_SP3_Cumulative_Update_7_RS -Reporting Services

SQL_Server_2005_SP3_Cumulative_Update_7_SNAC -SQL Native Client

SQL_Server_2005_SP3_Cumulative_Update_7

Reserved Resource Database in SQL Server 2005

TSQL Query to determine the version of Resource Database

SELECT SERVERPROPERTY('ResourceVersion')

TSQL Query to determine when the Resource Database was last backed up

SELECT SERVERPROPERTY('ResourceLastUpdateDateTime');

GO

SQL Server Release Date Calendar

http://sqlserverpedia.com/wiki/SQL_Server_Release_Date_Calendar