Building SQL Server Standards and PowerShell Coding Standards - Professional SQL Server 2008 Administration with Windows PowerShell - page 327

❑ Never run a transaction in SQL server directly. Run everything from application unless you are

a DBA. Use transactions only for administrative work in SQL Server directly.

❑ Access tables in the same order in stored procedures, triggers, and development code consis-

tently to avoid deadlocks.

Data Protection Standards and
Best Practices
The following guidelines outline best practices for data protection standards:

❑ Create a separate maintenance plan for backing up the system databases. Do not mix user

database backups with system database backups.

❑ It is not necessary to backup the tempdb because it is rebuilt each time SQL Server starts.

❑ Keep a script of the functional database schema in a secure location on the network. This comes

in handy if you need to know the structure of the database in production or you need to recover
a database that does not have any backup left (although you should not allow yourself to be in
such a situation).

❑

Use Windows Authentication, rather than SQL Server Authentication.

❑ Windows Authentication provides additional security and is recommended.

❑ Because of the security risk, do not use the SQL Server extended stored procedure

xp_cmdshell .

Backup Policy
Typically, a full database backup should be scheduled for every database at 11:00 PM, and transactional
log backup should be scheduled for every 15 minutes. For companies that are not active on weekends, for
a VLDB, full backup should be scheduled every Saturday at 11:00 PM, and a differential backup should be
scheduled for every night at 11:00 PM except on Saturday and Sunday. Transactional log backup should
be scheduled for every 15 minutes.

Tape backup of all the local backups should also be scheduled. Though this is not a DBA's job, you should
keep track of the tape backups and ensure that database backups are going to the tapes.

SQL Server Production Standards
Follow your company's change management process. Do not implement a code onto the production
server just because you have permission. Remember Murphy's law: '' If anything can go wrong, it
will .''

High Availability and Disaster Recovery
In the production environment, wherever possible, try to have a cluster server. If that's not possible, at
least attach the SAN drive for data files like .mdf and .ndf , and log files like .ldf .