Sunday, 23 June 2013

SQL Server Setup Considerations

The notes below were collated from a couple of SQL Server 2008R2 deployments on Windows 2008R2 (one was on Dell EqualLogic storage, the other on NetApp.)

Storage Considerations

Storage Allocation (Suggested):
LUN 2: SQL Server Data (User Databases)
LUN 3: SQL Server Logs (Transaction Logs)
LUN 4: SQL Server TempDB (DB & Logs)

Optional Drives:
LUN 5: SQL Server Backups
LUN 6: SnapInfo (if using NetApp SnapManager for SQL - SMSQL)

Storage Configuration (Suggested Drive Letters):
C:\ = Operating System / Page File
D:\ = Default Instance Data disk
L:\ = Default Instance Log file disk
T:\ = Default Instance TempDB disk

Optional Drives:
Z:\ = Default Instance Backup disk
S:\ = SnapInfo (for NetApp SMSQL)

Note 1: The 4 system databases are master, model, msdb & tempdb (in the above design we would have the master, model and msdb system databases and their transaction logs on the OS drive.)
Note 2: Placing databases on one LUN and transaction logs on another LUN, gives improved SQL performance by separating the random I/O patterns of user databases, from the sequential I/O patterns of the logs.
Note 3: The SnapInfo directory contains all backup set metadata. By default, the directory name is SMSQL_SnapInfo. When NetApp SMSQL performs a backup, a new backup set subdirectory is created under the SnapInfo directory.

Other Suggestions

The Model database can be configured with settings which will be used for all new databases.
TempDB best practices are to have 1 TempDB file per Processor Core.
Maintenance Plans - Check integrity of all databases and indexes @ Sunday 00:00

Database Default Settings:
Page_Verify = Checksum
Auto_Shrink = OFF
Owner = SA

Policy based management - enabled policies evaluated daily @ 00:00:
Data and Log File Location = Checks to ensure that Data and Log Files are not in the same location
Database Auto Close = Checks to see that Auto_Close is not enabled on databases
Database Auto Shrink = Checks to see that Auto_Shrink is not enabled on databases
Database Page Verification = Checks that Page_Verify is set to Checksum on all databases
Surface Area Configuration for Database Engine 2008 Features = Checks that Database mail is enabled

Further Reading (aimed at NetApp SMSQL deployments)

 ‘Microsoft SQL Server and NetApp SnapManager for SQL Server on NetApp Storage Best Practices Guide’ - February 2013 | TR-4003

SnapDrive for Windows documentation:

SnapManager for Microsoft SQL Server documentation:

Data ONTAP 8 documentation:

Technet - Microsoft SQL Server:

‘NetApp Disaster Recovery Solution for Microsoft SQL Server’ - January 2010 | TR-3604

‘Microsoft SQL Server Relational Engine: Storage Fundamentals for NetApp Storage’ - January 2011 | TR-3693

Image: Design Example 3 (of 5) from p28 - Separate System DBs, TempDB, User DBs, User Logs, SnapInfo

No comments:

Post a Comment