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 1: OS
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

General:
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