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
Comments
Post a Comment