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

trace log

Trace flag -1
This trace flag sets trace flags for all client connections, rather than for a single client connection. Is used only when setting trace flags using DBCC TRACEON and DBCC TRACEOFF. The setting of the Trace flag -1 is not visible with DBCC TRACESTATUS command, but work without problems. This trace flag was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

Trace flag 1204
This trace flag returns the type of locks participating in the deadlock and the current command affected. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

Trace flag 1205
This trace flag returns more detailed information about the command being executed at the time of a deadlock. This trace flag was documented in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

Trace flag 1807
You cannot create a database file on a mapped or UNC network location. This opportunity is generally unsupported under SQL Server 7.0 and SQL Server 2000. You can bypass this by turn on trace flag 1807.

Trace flag 3604
One of the most used trace flag. Trace flag 3604 sends trace output to the client. This trace flag is used only when setting trace flags with DBCC TRACEON and DBCC TRACEOFF. Trace flag 3604 was documented in SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

Trace flag 3605
In comparison with Trace flag 3604, this trace flag sends trace output to the error log. Trace flag 3605 was documented in SQL Server 6.5 Books Online and in SQL Server 7.0 Books Online, but was not documented in SQL Server 2000.

Trace flag 3608
This trace flag skips automatic recovery (at startup) for all databases except the master database. Trace flag 3608 was documented in SQL Server 6.5 Books Online, but was not documented in SQL Server 7.0 and SQL Server 2000.

Trace flag 4022
If turns on, then automatically started procedures will be bypassed. This trace flag described in CREATE PROCEDURE statement in the SQL Server Books Online.

Trace flag 8202
This trace flag used to replicate UPDATE as DELETE/INSERT pair. Let me to describe. UPDATE commands at the publisher can be run as an "on-page DELETE/INSERT" or a "full DELETE/INSERT". If the UPDATE command is run as an "on-page DELETE/INSERT," the Logreader send UDPATE command to the subscriber, If the UPDATE command is run as a "full DELETE/INSERT," the Logreader send UPDATE as DELETE/INSERT Pair. If you turn on trace flag 8202, then UPDATE commands at the publisher will be always send to the subscriber as DELETE/INSERT pair.

Trace flags

To enable these trace flags, run the following commands: (you must be a member of the SQL Server system administrators' role to run the DBCC command)

DBCC TRACEON(1204, -1)
DBCC TRACEON(1205, -1)
DBCC TRACEON(3605, -1)

The 1204 flag returns the types of locks participating in the deadlock encountered.

The 1205 flag returns more detailed information regarding the statements being run at the time of the deadlock.

The 3605 flag sends the trace information to the error log, which the SQLDiag tool will later output to a text file.

The "-1" parameter to the command tells SQL Server to apply the trace flag to all connections to the server.

----------

Disables the specified trace flags.

DBCC TRACEOFF

---------

Displays the status of trace flags.

DBCC TRACESTATUS

*******************************************

DBCC TRACEON(1204, -1)
DBCC TRACEON(1205, -1)
DBCC TRACEON(3605, -1)

DBCC TRACESTATUS

DBCC TRaceOff(1204,1205,3605,-1)------Sql Server 2000

-------------

DBCC TRACEON(1204, -1)
DBCC TRACEON(1222, -1)
DBCC TRACEON(3605, -1)

DBCC TRaceOff(1204,1222,3605,-1)------Sql Server 2005

If you’re running SQL 2005, you should be using 1222 instead of 1204 unless you have deep-seated masochistic tendencies. Alternatives to 1222:

If you are using SQL 2000 or SQL 7.0, you’ll have no choice but to fall back on the older -T1204.
There’s a “Deadlock graph” Profiler trace event that provides the same info as -T1222. Feel free to use this instead of -T1222 if you’re on SQL 2005.

But don’t waste your time with the “Lock:Deadlock” and “Lock:Deadlock Chain” trace events that are in SQL 2000, as they provide an unacceptably incomplete picture of the deadlock.