SQL Server : Deployment Guide

SQL Server : Deployment Guide

  1. Use mount points for storage and an anchor lun for the mount points. This will simplify storage management.
  2. Split logs and database files; lots of SAN storage vendors will say this isnt necessary now, but keep them seperate. This will protect you in the even of database file growth beyond normal tolerances.
  3. Storage must be aligned, if operating system is earlier than Windows 2003 this must be manually done when partitioning the LUNs, Windows 2008+ automatically aligns disks:
    1. If the disk you are aligning is already blank (raw), proceed to Step 3. If the disk contains data, backup the disk before proceeding.
    2. Delete all partitions on the disk.
    3. Open a command prompt, and execute Diskpart.exe.
    4. At the Diskpart command prompt, type List Disk and press Enter. If the disk you want to align does not appear in the list make sure that it exists and is accessible using the Disk Management snap-in.
    5. At the Diskpart command prompt, type Select Disk X, where X is the number of the disk as shown in the output of the List Disk command. Diskpart should return a message that indicates that Disk X is the selected disk.
    6. At the Diskpart command prompt, type Create Partition Primary Align=X, where X is either 32 or 64, depending on the recommendation from your storage vendor. If your storage vendor does not have any specific recommendations, it is recommended that you use 64. W 2008 / Vista use an offset of 1024.
    7. At the Diskpart command prompt, type Assign Letter=. For example, to assign letter Z to the disk, type Assign Letter=Z.
    8. Once the drive letter is assigned, type exit to exit out of the Diskpart utility.
    9. Use the Disk Management snap-in or the Windows Format command to format the partition as an NTFS-formatted partition.
  1. All data and log LUNs should be formatted with 64K NTFS allocation unit size
  2. If server has more than 8 CPU’s (or cores) you must configure MAXDOP to a value of 8, when less than 8 CPUs are present use a value of 0-X where X is the number of CPU’s. For OLTP scenarios use a MAXDOP calue of 1.
  3. Confirm SQL collation setting is correct (if upgrading/migrating from an existing platform) using SELECT SERVERPROPERTY(‘Collation’) AS ‘Collation’
  4. TempDB should be configured to have approx 4 data files, execute the code below, modify for each additional data file:
        ALTER DATABASE [tempdb]
        ADD FILE (
            NAME = N’tempdev_1′,
            FILENAME = N’E:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Data\tempdb_1.ndf’ ,
            SIZE = 8MB ,
            FILEGROWTH = 10% )

  5. Ensure you use setspn to configure the ServicePrincipalName for and SQL service accounts, setspn –A MSSQLSvc/[server fqdn]:[port | instance name] “[service account]” – anything in RED should be changed:
    1. For an instance running on port 1188 example: setspn –A MSSQLSvc/DB1.domain.local:1188domlocal\svc_dbe
    2. For a named instance, in this example ‘I1’, you should also run: setspn –A MSSQLSvc/DB1.domain.local:I1domlocal\svc_dbe
  6. MSDTC should have its own resource group
  7. MSDTC should be configured with the following permissions (more information here: http://technet.microsoft.com/en-us/library/cc753510%28WS.10%29.aspx):
    1. Open Component Services > My Cmoputer > Distributed Transaction Coordinator > Clustered DTCs
    2. Enable Network DTC Access (But do not select Allow Remote Clients/Allow Remote Administartion)
    3. Enable Transaction Manager Communication (Allow Inbound, Allow Outbound and select incoming Caler Authentication Required)
    4. Select SNA LU 6.2 Transactions (Ensure that Enable XA Transactions is not selected)
    5. In Windows Firewall configure an exception for DTC
  8. For SQL Server 2008 ‘Page Verify’ should be set to ‘Checksum’
  9. Configure a maximum memory size (see below)
  10. When managing [ermissions ensure that the local Administartors group is not a member of the sysadmin role
  11. The SQL service account should have th following permissions:
  • Perform Volume Maintenence Tasks (allows for faster restores as database files are not pre-zeroed)
  • Lock Pages in Memory
  • Logon as a Service

Additional Notes/Setup Instructions

SQL Server Memory Configuration

Suggested maximum memory allocations (http://www.sqlservercentral.com/blogs/glennberry/archive/2009/10/29/suggested-max-memory-settings-for-sql-server-2005_2F00_2008.aspx):

Physical RAM                        MaxServerMem Setting
2GB                                           1500
4GB                                           3200
6GB                                           4800
8GB                                           6400
12GB                                         10000
16GB                                         13500
24GB                                         21500
32GB                                         29000
48GB                                         44000
64GB                                         60000
72GB                                         68000
96GB                                         92000
128GB                                       124000

Configuring a maximum meory size for an SQL instance

EXEC  sp_configure‘max server memory (MB)’,2048;

Disk Alignment

To verify disk alignment execute the command: diskpar –I 2 where 2 is the number of the disk you wish to check the alignment.

The important setting here is the StartingOffset – this value should divide cleanly by the NTFS cluster size implemented on the disk.

Ensure that Partition Offset ÷ Stripe Unit Size is a whole number (you will need to consult the storage configuration to identify stripe size).

Ensure that Stripe_Unit_Size ÷ File_Allocation_Unit_Size is also a whole number.


Configure MAXDOP

sp_configure ‘show advanced options’, 1;
sp_configure ‘max degree of parallelism’, 8;