Sometimes, all a DBA needs, to help with day-to-day work, is a checklist of best-practices and dos and don’ts. It provides a handy reminder.

We asked Brad to provide one…


This is the full text for Brad McGehee's SQL Server DBA Best Practices poster, which can be downloaded from the speech-bubble at the head of the article.

Contents
* General DBA Best Practices
o Best Practices for Becoming an Exceptional SQL Server DBA
o Day-to-Day
o Installation
o Upgrading
o Security
o Job Maintenance
o SQL Server Configuration Settings
o Database Settings
o Replication
* High Availability Best Practices
o General High Availability
o Disaster Recovery
o Backup
o Clustering
o SQL Server Mirroring
o Log Shipping
* Performance Tuning Best Practices
o Performance Monitoring
o Hardware Performance Tuning
o Indexing
o SQL Server 2008 Compression Best Practices
o SQL Server 2008 Data Collector Best Practices
o Best Practices for Resource Governor
* Application Design and Coding Best Practices
o Database Design
o Queries and Stored Procedures
o Transact-SQL
o SQL Server CLR
o XML
* SQL Server Component Best Practices
o SSIS
o Reporting Services
o Analysis Services
o Service Broker



General DBA Best Practices

Best Practices for Becoming an Exceptional SQL Server DBA
1. Join (or start) a local SQL Server users group (chapters.sqlpass.org )
2. Join SQL PASS (www.sqlpass.org)
3. Attend at least one professional conference each year.
4. Attend at least one training session each year.
5. Read at least four books on SQL Server each year.
6. Read the free e-book, How to Become an Exceptional DBA
7. Learn everything you can about your job, especially those areas that nobody else likes or wants to master.
8. Volunteer at your work for new and challenging tasks that will make you known throughout your organization.
9. Install SQL Server on a laptop or desktop computer at home and practice learning new aspects of SQL Server, especially SQL Server 2008.
10. Participate in SQL Server forums (asking and answering questions)
1. www.SQLServerCentral.com
2. www.SQL-Server-Performance.Com
3. www.microsoft.com/communities/newsgroups/en-us/default.aspx
11. Become a Microsoft Certified IT Professional
1. Database Administrator (http://www.microsoft.com/learning/mcp/mcitp/dbadmin/default.mspx)
2. Database Developer (http://www.microsoft.com/learning/mcp/mcitp/dbdev/default.mspx)
3. Business Intelligence Developer (http://www.microsoft.com/learning/mcp/mcitp/bid/default.mspx)


Day-to-Day
1. Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
2. Verify that all scheduled jobs have run successfully.
3. Confirm that backups have been made and successfully saved to a secure location.
4. Monitor disk space to ensure your SQL Servers won’t run out of disk space. For best performance, all disks should have 20% or more of free space.
5. Throughout the day, periodically monitor your server’s performance. Use System Monitor, Profiler, DMVs, or the SQL Server 2008 Performance Data Collector
6. Use Management Studio or Profiler to help monitor and identify blocking issues.
7. Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
8. Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as needed.
9. Take some time to learn something new as a DBA to further your professional development.


Installation
1. Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
2. If possible, install and configure all of your SQL Server instances consistently, following an agreed upon organization standard. Optionally use SQL Server 2008 Policy-based Management to enforce standards.
3. Don't install SQL Server services you don't use, such as Microsoft Reporting Services or Analysis Services, if you won’t be using them.
4. For best performance of SQL Server running under Windows, turn off any operating system services that aren't needed.
5. For optimum SQL Server performance, dedicate your physical servers to only running a single instance of SQL Server, no other applications.
6. For best I/O performance, locate the database files (.mdf) and log files (.ldf) on separate volumns on your server to isolate potentially conflicting reads and writes.
7. If tempdb will be used heavily, also put it on its own separate array. In addition, pre-size tempdb to a size that will meet your server’s needs without having the need to autogrow. Divide the tempdb database into multiple files so that the number of files is equal to 50% to 100% of the number of CPU cores in your servers. Each physical file must be the same size.
8. Do not install SQL Server on a domain controller.
9. Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.

Upgrading
1. Run the Upgrade Advisor on any database you intend to upgrade to identify potential problems.
2. Before performing an upgrade to SQL Server, thoroughly test your application in a test environment to ensure compatibility. Make any necessary changes before performing the upgrade.
3. Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
4. While upgrading in place can work well, it is less risky to upgrade to new hardware with a fresh install of the OS and SQL Server.
5. If you upgrade from a previous version of SQL Server, you should update all of the statistics in all your databases using UPDATE STATISTICS. This is because statistics are not automatically updated during the upgrade process. In addition, run DBCC UPDATEUSAGE on all databases to correct any incorrect row or page counts.

Security
1. Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.
2. Only install required network libraries and network protocols on your SQL Server instances.
3. Minimize the number of sysadmins allowed to access SQL Server.
4. As a DBA, log on with sysadmin privileges only when needed. Create separate accounts for DBAs to access SQL Server when sysadmin privileges are not needed.
5. Assign the SA account a very obscure password, and never use it to log onto SQL Server. Use a Windows Authentication account to access SQL Server as a sysadmin instead.
6. When assigning permissions to users, only give them the minimum permissions they need to perform their jobs.
7. Use stored procedures or views to allow users to access data instead of letting them directly access tables.
8. When possible, use Windows Authentication logins instead of SQL Server logins.
9. Use strong passwords for all SQL Server login accounts.
10. Don’t grant permissions to the public database role.
11. Remove user login IDs who no longer need access to SQL Server.
12. Remove the guest user account from each user database.
13. Disable cross database ownership chaining if not required.
14. Never grant permission to the xp_cmdshell to non-sysadmins.
15. Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
16. Avoid creating network shares on any SQL Server.
17. Turn on login auditing so you can see who has succeeded, and failed, to login. In SQL Server 2008, you can use SQL Server Audit instead.
18. Don’t use the SA account, or login IDs who are members of the Sysadmin group, as accounts used to access SQL Server from applications.
19. Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
20. Remove the BUILTIN/Administrators group to prevent local server administrators from being able to access SQL Server. Before you do this on a clustered SQL Server, check Books Online for more information.
21. Run each separate SQL Server service under a different Windows domain account.
22. Only give SQL Server service accounts the minimum rights and permissions needed to run the service. In most cases, local administrator rights are not required, and domain administrator rights are never needed. SQL Server setup will automatically configure service accounts with the necessary permissions for them to run correctly, you don’t have to do anything, with one possible exception. You may want to give the SQL Server service account SE_MANAGE_VOLUME_NAME rights so that instant file initialization can be used for SQL Server 2005/2008. This is only required if the SQL Server service account is not a member of the local administrator’s group.
23. When using distributed queries, use linked servers instead of remote servers.
24. Do not browse the web from a SQL Server.
25. Instead of installing virus/antispyware protection on a SQL Server, perform scans from a remote server during a part of the day when user activity is less.
26. Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
27. Encrypt all SQL Server backups with a third-party backup tool, such as SQL Backup Pro. If you have SQL Server 2008, Enterprise Edition, you can use Transparent Data Encryption to ensure encrypted backups.
28. Only enable C2 auditing or Common Criteria compliance if required.
29. SQL Server 2008 includes a new built-in auditing tool called SQL Server Audit. It can be used to audit virtually any user activity. Keep the number of activities and objects you audit to a minimum to reduce performance overhead.
30. Consider running a SQL Server security scanner against your SQL servers to identify security holes.
31. Consider adding a certificate your SQL Server instances and enable SSL or IPSEC for connections to clients.
32. If using SQL Server 2005/2008, enable password policy checking.
33. If running SQL Server 2008, Enterprise Edition, consider implementing Transparent Data Encryption to help protect data stored on disk.


Job Maintenance
1. Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
2. When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
3. Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
4. If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
5. Periodically (daily or weekly) run a database rebuild or reorganize job on all the indexes on all the tables in all your database. This will rebuild the indexes so that the data is no longer logically fragmented. Fragmented data can cause SQL Server to perform work, slowing down SQL Server's performance. Rebuilding or reorganizing tables will also update column statistics.
6. As often as you take full backups, which is probably once a day, run DBCC CHECKDB on your databases to verify database integrity.
7. Avoid running most DBCC commands during busy times of the day. These commands are often I/O intensive and can reduce performance of the SQL Server, negatively affecting users.
8. Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.

SQL Server Configuration Settings
1. SQL Server configuration settings should remain at their default settings. Any changes to these settings should only be made by an experienced DBA who understands the pros and cons of making changes.
2. If you are using the 32-bit version of SQL Server, and if you are using 4 GB or more of RAM, ensure that you have all the AWE settings correctly set.


Database Settings
1. Database settings should generally be kept at their default values. Ensure that the following settings are set for overall best performance: These are default settings:
* Auto Create Statistics: On
* Auto Update Statistics: On
* Auto Shrink: Off
* Page Verify: Checksum
2. Don’t rely on AUTOGROWTH to automatically manage the size of your databases. Instead, proactively monitor and alter database size as circumstances dictate. Only use AUTOGROWTH to deal with unexpected growth.

Replication
1. Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
2. Ideally, publishers, distributors, and subscribers should be on separate physical hardware.
3. Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
4. Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed.
5. Use default replication settings, unless you can ensure that a non-default setting will actually improve replication performance or other issues. Be sure that you test all changes to ensure that they are as effective as you expect.
6. Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
7. Periodically, validate data between publishers and subscribers.
8. Regularly monitor replication processes and jobs to ensure they are working.
9. Regularly monitor replication performance, and performance tune as necessary.
10. Add alerts to all replication jobs so you are notified of any job failures.

Full article...