SQL Server performance: http://wp.me/p15Zft-8h
SQL Server Video archive: https://technet.microsoft.com/en-us/dn912438
T-SQL reference: https://technet.microsoft.com/en-us/library/ms189826(v=sql.90).aspx
SQL performance and troubleshooting: http://sqlnexus.codeplex.com/
Microsoft companion (MOC): http://www.microsoft.com/en-us/learning/companion-moc.aspx
Tips and tricks:
PowerShell: import-module SQLPS
Placement of tempdb in a dedicated disk (Raid 1) , same for log files (RAID 1 or 10) and database files (RAID 5). Also dedicated disk for OS and dedicated disk for SQL server binaries.
Do a dbcc checkdb before each database backup
Use Buffer pool extension
Enable security: create logins, server roles, then for db: create users, database roles, database perms
Privileged the Microsoft service accounts (MSA) to run the SQL services.
Enable SQL audit
Enable DML triggers (enable logons trigger)
Use SQL profile (but heavy in terms of performance). Else prefer to use (T-SQL) SQL trace (light footprint if well-designed).
Design a backup and restore strategy:
- To backup: backup full + backup differential + backup transaction log + backup tail_log
- To restore: restore first the full (with norecovery) + the last differential (with norecovery) + the latest transaction log (with recovery option) and eventually the latest Tail_log (if possible)
- don’t forget to backup the tail log before to start a restore sequence
- preferably use “backup device” which contains the full,differential,logs. Then you can backup the “backup device” using the OS backup software (Windows backup, Tivoli SM, Veritas Netbackup…)
Define maintenance plans:
– separate the maintenance plans to backup the system databases from the other databases (include also the check database integrity “dbcc checkdb” before each backup sequence)
– separate the maintenance plans to backup a Application Database from a maintenance plan to check only the Database health: check database integrity, reorganize indexes, update statistics.