5 Critical DBA Best Practices

Create and maintain a standardized environment— If it’s all at possible you should strive to standardize your SQL Server configurations. You should keep your server and VM configurations as similar as possible. You should also endeavor to use the same Windows Server OS and SQL Server versions and editions. Likewise, you should try to keep all of your database maintenance plans and SQL Agent jobs the same. Maintaining a standardized environment will reduce operational complexity and reduce the chance for errors.

Keep your database servers dedicated to SQL Server— Your SQL Server instances should always run on a dedicated server. No other applications like file or print services should be running on the same system as your SQL Server instances. Likewise, it’s best to avoid running multiple SQL Server instances on the same server. While there are exceptions to this, these days it’s typically a better idea to run different instances in separate VMs. You should also be sure to disable any unnecessary Windows Server and SQL Server services.

Follow the least privilege security principle— Only give out the security permissions that each user and administrator needs to have. Don’t give developers administrative permissions.  Don’t use the SA account to run your services or perform operations. Microsoft recommends that you run your SQL Server services using a domain account. Always be sure to give the SA account a strong password. Make sure that all application use domain accounts to access the server and that they don’t use administrative accounts.

Manage your data and log files— In almost all cases, AUTOGROW should be enabled on your data and log files. Likewise, AUTOSHRINK should be turned off. Turning off AUTOSHRINK can help you avoid any grow-shrink cycles. When you create your data and log files you should plan to create then with enough space to minimize AUTOGROW events. Your MDF data files should be located on different disks than your LDF log files.

Implement and test your backup and restore plans— If you need to be able you restore your data to a given point in time you need to be sure that you are using the bulk or full recovery models for your databases. Typically, you should plan to perform full backups daily on all system and user production databases. Most businesses also perform multiple log file backups at frequent intervals throughout the day.  If a database uses the bulk or full recovery model, you must back up the transaction log to maintain its size. The recommended best practice is to create a SQL Agent job or Maintenance Plan to automate your backup processes.  Your backup jobs should use the RESTORE WITH VERIFYONLY to verify backup integrity. You should also be sure to regularly perform test database restores with your database backups to ensure that they work.