MS SQL Server DBA Checklist - General DBA Best Practices
-- Join (or start) a local SQL Server users group
-- Attend at least one professional conference each year.
-- Attend at least one training session each year.
-- Read at least four books on SQL Server each year.
-- Read the free e-book, How to Become an Exceptional DBA
-- Learn everything you can about your job, especially those areas that nobody else likes or wants to master.
-- Volunteer at your work for new and challenging tasks that will make you known throughout your organization.
-- Install SQL Server on a laptop or desktop computer at home and practice learning new aspects of SQL Serve.
-- Participate in SQL Server forums (asking and a
MS SQL Server DBA Checklist - General - Day-to-Day
-- Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
-- Verify that all scheduled jobs have run successfully.
-- Confirm that backups have been made and successfully saved to a secure location.
-- 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.
-- Throughout the day, periodically monitor your server’s performance. Use System Monitor, Profiler, DMVs, or the SQL Server 2008 Performance Data Collector
-- Use Management Studio or Profiler to help monitor and identify blocking issues.
-- Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
-- Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as needed.
-- Take some time to learn something new as a DBA to further your professional development.
MS SQL Server DBA Checklist - General - Installation
-- Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
-- 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.
--. 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. -- For best performance of SQL Server running under Windows, turn off any operating system services that aren't needed.
-- For optimum SQL Server performance, dedicate your physical servers to only running a single instance of SQL Server, no other applications.
-- 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.
-- 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.
-- Do not install SQL Server on a domain controller.
-- Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.
MS SQL Server DBA Checklist - General - Upgrading
-- Run the Upgrade Advisor on any database you intend to upgrade to identify potential problems.
-- 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.
-- Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
-- 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.
-- 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.
MS SQL Server DBA Checklist - General - Security
-- Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.
-- Only install required network libraries and network protocols on your SQL Server instances.
-- Minimize the number of sysadmins allowed to access SQL Server.
-- 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.
-- 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.
-- When assigning permissions to users, only give them the minimum permissions they need to perform their jobs.
-- Use stored procedures or views to allow users to access data instead of letting them directly access tables.
-- When possible, use Windows Authentication logins instead of SQL Server logins.
-- Use strong passwords for all SQL Server login accounts.
-- Don’t grant permissions to the public database role.
-- Remove user login IDs who no longer need access to SQL Server.
-- Remove the guest user account from each user database.
-- Disable cross database ownership chaining if not required.
-- Never grant permission to the xp_cmdshell to non-sysadmins.
-- Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
-- Avoid creating network shares on any SQL Server.
-- 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. -- 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. -- Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
-- 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.
-- Run each separate SQL Server service under a different Windows domain account.
-- 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.
-- When using distributed queries, use linked servers instead of remote servers.
-- Do not browse the web from a SQL Server.
-- 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.
-- Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
-- 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.
-- Only enable C2 auditing or Common Criteria compliance if required.
-- 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.
-- Consider running a SQL Server security scanner against your SQL servers to identify security holes.
-- If using SQL Server 2005/2008, enable password policy checking.
-- If running SQL Server 2008, Enterprise Edition, consider implementing Transparent Data Encryption to help protect data stored on disk.
MS SQL Server DBA Checklist - General - Job Maintenance
-- Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
-- When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
-- Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
-- If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
-- 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.
-- . As often as you take full backups, which is probably once a day, run DBCC CHECKDB on your databases to verify database integrity.
-- 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.
-- Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.
MS SQL Server DBA Checklist - General - SQL Server Configuration Settings
-- 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.
-- 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.
MS SQL Server DBA Checklist - General - Database Settings
-- 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
-- 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.
MS SQL Server DBA Checklist - General - Replication
-- Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
-- Ideally, publishers, distributors, and subscribers should be on separate physical hardware.
-- Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
-- Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed.
-- 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.
-- Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
-- Periodically, validate data between publishers and subscribers.
-- Regularly monitor replication processes and jobs to ensure they are working.
-- Regularly monitor replication performance, and performance tune as necessary.
-- Add alerts to all replication jobs so you are notified of any job failures.
MS SQL Server DBA Checklist - High Availability Best Practices
-- Physically protect your SQL Servers from unauthorized users.
-- Physically document all of your SQL Server instances. Incorporate effective change management.
-- Always use a RAIDed array or SAN for storing your data.
-- Use SQL Server clustering, database mirroring, or log shipping to provide extra fault tolerance.
-- Replication is not an effective means to protect your data.
-- Ensure that your entire IT infrastructure is redundant. It is only as strong as its weakest link.
-- Always use server-class hardware, and standardize on the same hardware as much as possible.
-- Use hardware and software monitoring tools so you can quickly become aware of when problems first arise.
-- After testing, apply all new service packs and hot fixes to the OS and SQL Server.
-- Cross-train staff so that there are multiple people who are able to deal with virtually any problem or issue.
MS SQL Server DBA Checklist - High Availability - Disaster Recovery
-- You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
-- As your SQL Servers change over time, don’t forget to update your disaster recovery plan.
--. Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not assume a DBA will be rebuilding the servers.
-- Fully test your disaster recovery plan at least once a year.
-- Re-read all the best practice just mentioned. I’m not kidding. Remember, as DBAs, we are guardians of the organization’s data. This is a huge responsibility.
MS SQL Server DBA Checklist - High Availability - Backup
-- All production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.
-- Whenever possible, perform a daily full backup of all system and user databases.
-- For all production databases, perform regular transaction log backups, at least once an hour.
-- Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
-- Periodically test backups to ensure that they are good and can be restored.
-- Backup first to disk, then move to tape or some other form of backup media.
-- Store backups offsite.
-- If using SQL Server column encryption, or SQL Server 2008 Transparent Data Encryption, be sure to backup the service master key, database master keys, and certificates.
-- If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third-party backup program, such as SQL Backup Pro. SQL Server 2008, Enterprise Edition, includes backup compression.
-- Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency.
MS SQL Server DBA Checklist - High Availability - Clustering
-- Detailed planning is critical to the success of every SQL Server cluster installation. Fully plan the install before performing the actual install.
-- An expensive cluster is of little value if the supporting infrastructure is not also fault tolerant. For example, don’t forget power redundancy, network redundancy, etc.
-- Run only a single instance of SQL Server per node. Whether you have two or eight nodes in your cluster, leave at least one node as a failover node.
-- Cluster nodes must not be domain controllers, and all nodes must belong in the same domain and should have access to two or more domain controllers.
-- Since clustering is not designed to protect data (only SQL Server instances), the shared storage device used by the cluster must incorporate fault tolerant technology. Consider log shipping or mirroring to further protect your production databases.
-- When initially installing Windows and SQL Server Clustering, be sure that all drivers and software are up-to-date, including the latest service packs or hot fixes.
-- Each node of a cluster should have identical hardware, drivers, software, and configuration settings.
-- Once the cluster has been installed, test it thoroughly for every possible failure scenario.
-- Do not run antivirus or antispyware on a SQL Server cluster.
-- Monitor active production clusters on a daily basis, looking for any potential problems. Periodically test failover on production servers to ensure all is working well.
-- Once you have a stable SQL Server Cluster running, be very leery about making any changes to it, whatsoever.
MS SQL Server DBA Checklist - High Availability - SQL Server Mirroring
-- The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations.
-- The witness server should be on separate physical hardware, and be on a separate network (best if at a third location).
-- Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored.
-- Use high availability mode whenever possible, and high performance mode only when required.
-- While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better.
-- You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead caused by the mirroring process itself.
-- Thoroughly test database mirroring before putting it into production.
-- Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
-- Develop a formal operational and recovery procedure (and document) to support mirroring. Periodically test the failover process to ensure that it works.
MS SQL Server DBA Checklist - High Availability - Log Shipping
-- If you don’t currently employ clustering or database mirroring for your SQL Servers because of cost, consider employing log shipping to help boost your high availability. It provides reasonably high availability at low cost.
-- If you take advantage of SQL Server log shipping capability, you will want to keep the log shipping monitoring service on a SQL Server of its own, not on the source or destination servers participating in log shipping. Not only is this important for fault tolerance, but because the log shipping monitoring service incurs overhead that can affect the performance of the source and destination servers.
-- Monitor log shipping daily to ensure that is working successfully.
-- Learn what you need to know to fix log shipping if synchronization is lost between the production and backup databases.
-- Document, and test your server recovery plan, so you will be ready in case of a server failure.
MS SQL Server DBA Checklist - Performance Tuning - Performance Monitoring
-- Regularly monitor system performance using System Monitor or similar tool.
-- If running SQL Server 2005, SP2 or later, install the free SQL Server Performance Dashboard. It can be used for real-time monitoring and performance troubleshooting.
-- If running under SQL Server 2008, consider using the Performance Data Collector to collect, store, and analyze performance data.
-- Regularly monitor activity using Profiler. Be sure that traces are taken during the busiest times of the day so you get a more representative trace of what is going on in each server. When running the Profiler, do not collect more data than you need to collect.
-- Perform performance monitoring from a computer that is not the SQL Server you are monitoring. Run monitoring tools on a separate desktop or server.
MS SQL Server DBA Checklist - Performance Tuning - Hardware Performance Tuning
-- Although heavy-duty hardware can help SQL Server's performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. Before getting faster hardware, be sure you have thoroughly tuned your applications, Transact-SQL, and database indexing.
-- In many cases, adding RAM to a server is the cheapest and fastest way to boost hardware performance of a SQL Server. But before adding more RAM to a SQL Server, ensure first that it will be used by SQL Server. Adding more RAM doesn’t mean that SQL Server will always use it. If the current Buffer Hit Cache Ratio is consistently above 99% and you have well more than 100 MB of Available RAM, your server may not benefit from adding additional RAM.
-- If your SQL Server’s total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
-- Don't run any applications on your server other than SQL Server, with the exception of necessary utilities.
-- If your SQL Server database experiences mostly reads, then a RAID 5 array offers good protection and adequate performance. If your SQL Server database is mostly writes, then use a RAID 10 array for best protection and performance.
-- The more spindles you have in an array, the faster disk I/O will be.
-- Ensure that all hardware is running the latest, approved drivers.
-- If you are storing your databases and log files on a SAN, the System Monitor performance counters for disk I/O are unreliable. Instead, use vendor-supplied tools to monitor disk performance.
MS SQL Server DBA Checklist - Performance Tuning - Indexing
-- Periodically, run the Database Engine Tuning Advisor against current Profiler traces to identify potentially missing indexes.
-- Remove indexes that are never used.
-- Don’t accidentally create redundant indexes.
--. As a rule of thumb, every table should have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column, or some other column where the value is increasing — and is unique. In many cases, the primary key is the ideal column for a clustered index.
-- Since you can only create one clustered index per table, take extra time to carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an educated guess as to which query (the most common one run against the table, perhaps) is the most critical, and if this query will benefit most from having a clustered index.
-- If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don't want to add non-clustered indexes to columns that aren't at least 95% unique.
-- Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
-- If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type).
-- Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
-- Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by queries run against the table.
-- When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn't have to look any further.
-- If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have appropriate indexes.
-- Don’t automatically accept the default value of 100 for the fill factor for your indexes. It may or may not best meet your needs. A high fill factor is good for seldom changed data, but highly modified data needs a lower fill factor to reduce page splitting.
-- Don't over index your OLTP tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of indexes (for SELECTs) and the ideal number to minimize the overhead that occurs with indexes during data modifications.
-- If you know that your application will be performing the same query over and over on the same table, consider creating a non-clustered covering index, or a non-clustered index with included columns, on the table. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance. A non-clustered index with included columns is similar to a covering index, except that the extra columns you add to cover the query are not part of the index.
MS SQL Server DBA Checklist - SQL Server 2008 Compression
-- SQL Server 2008, Enterprise Edtition, offers both row and column compression, which can be used to reduce the amount of space data takes up on disk and in the buffer pool, potentially boosting performance.
-- Compression requires the use of additional CPU cycles to implement. Because of this, if your server currently has a CPU bottleneck, then compression should probably be avoided.
-- Compression works best for tables that experience mostly reads. Tables that experience a large number of DML activities should probably not be compressed.
-- Don’t use compression on tables subject to heavy inserts, such as bulk inserts.
MS SQL Server DBA Checklist - SQL Server 2008 Data Collector
-- The SQL Server 2008 Data Collector can be used to gather, store, and analyze many different types of SQL Server performance activities.
-- Using the SQL Server 2008 Data Collector imposes some performance overhead, and because of this, you may want to minimize the amount of data that is collected by it.
-- While the Management Data Warehouse can exist on each instance of SQL Server, it is recommended that a dedicated SQL Server be used to store the Management Data Warehouse used to store the collected data.
MS SQL Server DBA Checklist - Best Practices for Resource Governor
-- The SQL Server 2008 Resource Governor can be used to restrict how much CPU and memory resources are allocated to a particular user connection, allowing the DBA to give resource preference to some connections over other connections.
-- If you use the Resource Governor, ensure that the DAC is turned on, as it is the only way to troubleshoot and fix a bad Resource Governor configuration.
-- Classifier user-defined function should be well tested and optimized. If written incorrectly, a classifier function can cause connections to slow down, time out, and even create an infinite loop that locks up SQL Server.
-- Monitor Resource Governor after implementation to confirm appropriate use. If you are not careful, you can introduce query performance problems that didn’t exist before using Resource Governor. For example, if memory is limited too much, it can cause a suboptimal execution plan to be created and executed, hurting performance.
-- Test your Resource Governor implementation on test system before implementing it on production
-- Join (or start) a local SQL Server users group
-- Attend at least one professional conference each year.
-- Attend at least one training session each year.
-- Read at least four books on SQL Server each year.
-- Read the free e-book, How to Become an Exceptional DBA
-- Learn everything you can about your job, especially those areas that nobody else likes or wants to master.
-- Volunteer at your work for new and challenging tasks that will make you known throughout your organization.
-- Install SQL Server on a laptop or desktop computer at home and practice learning new aspects of SQL Serve.
-- Participate in SQL Server forums (asking and a
MS SQL Server DBA Checklist - General - Day-to-Day
-- Check OS Event Logs, SQL Server Logs, and Security Logs for unusual events.
-- Verify that all scheduled jobs have run successfully.
-- Confirm that backups have been made and successfully saved to a secure location.
-- 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.
-- Throughout the day, periodically monitor your server’s performance. Use System Monitor, Profiler, DMVs, or the SQL Server 2008 Performance Data Collector
-- Use Management Studio or Profiler to help monitor and identify blocking issues.
-- Keep a log of any changes you make to servers, including documentation of any performance issues you identify and correct.
-- Create SQL Server alerts to notify you of potential problems, and have them e-mailed to you. Take action as needed.
-- Take some time to learn something new as a DBA to further your professional development.
MS SQL Server DBA Checklist - General - Installation
-- Always fully document installs so that your SQL Server instances can easily be reproduced in an emergency.
-- 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.
--. 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. -- For best performance of SQL Server running under Windows, turn off any operating system services that aren't needed.
-- For optimum SQL Server performance, dedicate your physical servers to only running a single instance of SQL Server, no other applications.
-- 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.
-- 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.
-- Do not install SQL Server on a domain controller.
-- Don’t use NTFS data file encryption (EFS) and compression on SQL Server database and log files.
MS SQL Server DBA Checklist - General - Upgrading
-- Run the Upgrade Advisor on any database you intend to upgrade to identify potential problems.
-- 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.
-- Before you upgrade, be sure you have a plan in place to fall back to in case the upgrade is problematic.
-- 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.
-- 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.
MS SQL Server DBA Checklist - General - Security
-- Ensure the physical security of each SQL Server, preventing any unauthorized users from physically access your servers.
-- Only install required network libraries and network protocols on your SQL Server instances.
-- Minimize the number of sysadmins allowed to access SQL Server.
-- 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.
-- 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.
-- When assigning permissions to users, only give them the minimum permissions they need to perform their jobs.
-- Use stored procedures or views to allow users to access data instead of letting them directly access tables.
-- When possible, use Windows Authentication logins instead of SQL Server logins.
-- Use strong passwords for all SQL Server login accounts.
-- Don’t grant permissions to the public database role.
-- Remove user login IDs who no longer need access to SQL Server.
-- Remove the guest user account from each user database.
-- Disable cross database ownership chaining if not required.
-- Never grant permission to the xp_cmdshell to non-sysadmins.
-- Use Windows Global Groups, or SQL Server Roles to manage groups of users that need similar permissions.
-- Avoid creating network shares on any SQL Server.
-- 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. -- 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. -- Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
-- 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.
-- Run each separate SQL Server service under a different Windows domain account.
-- 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.
-- When using distributed queries, use linked servers instead of remote servers.
-- Do not browse the web from a SQL Server.
-- 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.
-- Add operating system and SQL Server service packs and hot fixes soon after they are released and tested, as they often include security enhancements.
-- 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.
-- Only enable C2 auditing or Common Criteria compliance if required.
-- 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.
-- Consider running a SQL Server security scanner against your SQL servers to identify security holes.
-- If using SQL Server 2005/2008, enable password policy checking.
-- If running SQL Server 2008, Enterprise Edition, consider implementing Transparent Data Encryption to help protect data stored on disk.
MS SQL Server DBA Checklist - General - Job Maintenance
-- Avoid overlapping jobs on the same SQL Server instance. Ideally, each job should run separately at different times.
-- When creating jobs, be sure to include error trapping, log job activity, and set up alerts so you know instantly when a job fails.
-- Create a special SQL Server login account whose sole purpose is to run jobs, and assign it to all jobs.
-- If your jobs include Transact-SQL code, ensure that it is optimized to run efficiently.
-- 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.
-- . As often as you take full backups, which is probably once a day, run DBCC CHECKDB on your databases to verify database integrity.
-- 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.
-- Script all jobs and store these scripts in a secure area so they can be used if you need to rebuild the servers.
MS SQL Server DBA Checklist - General - SQL Server Configuration Settings
-- 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.
-- 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.
MS SQL Server DBA Checklist - General - Database Settings
-- 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
-- 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.
MS SQL Server DBA Checklist - General - Replication
-- Replication needs should be clearly defined before creating a replication topology. Successful replication can be difficult and requires much pre-planning.
-- Ideally, publishers, distributors, and subscribers should be on separate physical hardware.
-- Create, document, and test a backup and restore strategy. Restoring replicated databases can be complex and requires much planning and practice.
-- Script the replication topology as part of your disaster recovery plan so you can easily recreate your replication topology if needed.
-- 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.
-- Fully understand the implications of adding or dropping articles, changing publication properties, and changing schema on published databases, before making any of these changes.
-- Periodically, validate data between publishers and subscribers.
-- Regularly monitor replication processes and jobs to ensure they are working.
-- Regularly monitor replication performance, and performance tune as necessary.
-- Add alerts to all replication jobs so you are notified of any job failures.
MS SQL Server DBA Checklist - High Availability Best Practices
-- Physically protect your SQL Servers from unauthorized users.
-- Physically document all of your SQL Server instances. Incorporate effective change management.
-- Always use a RAIDed array or SAN for storing your data.
-- Use SQL Server clustering, database mirroring, or log shipping to provide extra fault tolerance.
-- Replication is not an effective means to protect your data.
-- Ensure that your entire IT infrastructure is redundant. It is only as strong as its weakest link.
-- Always use server-class hardware, and standardize on the same hardware as much as possible.
-- Use hardware and software monitoring tools so you can quickly become aware of when problems first arise.
-- After testing, apply all new service packs and hot fixes to the OS and SQL Server.
-- Cross-train staff so that there are multiple people who are able to deal with virtually any problem or issue.
MS SQL Server DBA Checklist - High Availability - Disaster Recovery
-- You must create a disaster recovery plan and include every detail you will need to rebuild your servers.
-- As your SQL Servers change over time, don’t forget to update your disaster recovery plan.
--. Write the disaster recovery plan so that any computer literate person will be able to read and follow it. Do not assume a DBA will be rebuilding the servers.
-- Fully test your disaster recovery plan at least once a year.
-- Re-read all the best practice just mentioned. I’m not kidding. Remember, as DBAs, we are guardians of the organization’s data. This is a huge responsibility.
MS SQL Server DBA Checklist - High Availability - Backup
-- All production databases should be set to use the full recovery model. This way, you can create transaction log backups on a periodic basis.
-- Whenever possible, perform a daily full backup of all system and user databases.
-- For all production databases, perform regular transaction log backups, at least once an hour.
-- Perform full backups during periods of low user activity in order to minimize the impact of backups on users.
-- Periodically test backups to ensure that they are good and can be restored.
-- Backup first to disk, then move to tape or some other form of backup media.
-- Store backups offsite.
-- If using SQL Server column encryption, or SQL Server 2008 Transparent Data Encryption, be sure to backup the service master key, database master keys, and certificates.
-- If you find that backup times take longer than your backup window, or if backup file sizes are taking up too much space on your storage device, consider a third-party backup program, such as SQL Backup Pro. SQL Server 2008, Enterprise Edition, includes backup compression.
-- Document, step-by-step, the process to restore system and user databases onto the same, or a different server. You don’t want to be looking this information up during an emergency.
MS SQL Server DBA Checklist - High Availability - Clustering
-- Detailed planning is critical to the success of every SQL Server cluster installation. Fully plan the install before performing the actual install.
-- An expensive cluster is of little value if the supporting infrastructure is not also fault tolerant. For example, don’t forget power redundancy, network redundancy, etc.
-- Run only a single instance of SQL Server per node. Whether you have two or eight nodes in your cluster, leave at least one node as a failover node.
-- Cluster nodes must not be domain controllers, and all nodes must belong in the same domain and should have access to two or more domain controllers.
-- Since clustering is not designed to protect data (only SQL Server instances), the shared storage device used by the cluster must incorporate fault tolerant technology. Consider log shipping or mirroring to further protect your production databases.
-- When initially installing Windows and SQL Server Clustering, be sure that all drivers and software are up-to-date, including the latest service packs or hot fixes.
-- Each node of a cluster should have identical hardware, drivers, software, and configuration settings.
-- Once the cluster has been installed, test it thoroughly for every possible failure scenario.
-- Do not run antivirus or antispyware on a SQL Server cluster.
-- Monitor active production clusters on a daily basis, looking for any potential problems. Periodically test failover on production servers to ensure all is working well.
-- Once you have a stable SQL Server Cluster running, be very leery about making any changes to it, whatsoever.
MS SQL Server DBA Checklist - High Availability - SQL Server Mirroring
-- The principal database and the mirror database should be on separate physical hardware, and ideally, in different physical locations.
-- The witness server should be on separate physical hardware, and be on a separate network (best if at a third location).
-- Initial database mirroring setup should be done during less busy times, as the setup process can negatively affect performance of the production database being mirrored.
-- Use high availability mode whenever possible, and high performance mode only when required.
-- While a fast connection is not required between mirrored servers, the faster the connection, and the better quality the connection, the better.
-- You will want to optimize the performance of the mirrored database as much as possible to reduce the overhead caused by the mirroring process itself.
-- Thoroughly test database mirroring before putting it into production.
-- Monitor database mirroring daily to ensure that it is working properly, and is meeting performance goals.
-- Develop a formal operational and recovery procedure (and document) to support mirroring. Periodically test the failover process to ensure that it works.
MS SQL Server DBA Checklist - High Availability - Log Shipping
-- If you don’t currently employ clustering or database mirroring for your SQL Servers because of cost, consider employing log shipping to help boost your high availability. It provides reasonably high availability at low cost.
-- If you take advantage of SQL Server log shipping capability, you will want to keep the log shipping monitoring service on a SQL Server of its own, not on the source or destination servers participating in log shipping. Not only is this important for fault tolerance, but because the log shipping monitoring service incurs overhead that can affect the performance of the source and destination servers.
-- Monitor log shipping daily to ensure that is working successfully.
-- Learn what you need to know to fix log shipping if synchronization is lost between the production and backup databases.
-- Document, and test your server recovery plan, so you will be ready in case of a server failure.
MS SQL Server DBA Checklist - Performance Tuning - Performance Monitoring
-- Regularly monitor system performance using System Monitor or similar tool.
-- If running SQL Server 2005, SP2 or later, install the free SQL Server Performance Dashboard. It can be used for real-time monitoring and performance troubleshooting.
-- If running under SQL Server 2008, consider using the Performance Data Collector to collect, store, and analyze performance data.
-- Regularly monitor activity using Profiler. Be sure that traces are taken during the busiest times of the day so you get a more representative trace of what is going on in each server. When running the Profiler, do not collect more data than you need to collect.
-- Perform performance monitoring from a computer that is not the SQL Server you are monitoring. Run monitoring tools on a separate desktop or server.
MS SQL Server DBA Checklist - Performance Tuning - Hardware Performance Tuning
-- Although heavy-duty hardware can help SQL Server's performance, application and database design can play a greater part in overall performance than hardware. Keep this in mind, as throwing good money after bad on server hardware does not always fix SQL Server performance problems. Before getting faster hardware, be sure you have thoroughly tuned your applications, Transact-SQL, and database indexing.
-- In many cases, adding RAM to a server is the cheapest and fastest way to boost hardware performance of a SQL Server. But before adding more RAM to a SQL Server, ensure first that it will be used by SQL Server. Adding more RAM doesn’t mean that SQL Server will always use it. If the current Buffer Hit Cache Ratio is consistently above 99% and you have well more than 100 MB of Available RAM, your server may not benefit from adding additional RAM.
-- If your SQL Server’s total CPU utilization is consistently above 80% or more, you need more CPUs, faster CPUs, or you need to find a way to reduce the load on the current server.
-- Don't run any applications on your server other than SQL Server, with the exception of necessary utilities.
-- If your SQL Server database experiences mostly reads, then a RAID 5 array offers good protection and adequate performance. If your SQL Server database is mostly writes, then use a RAID 10 array for best protection and performance.
-- The more spindles you have in an array, the faster disk I/O will be.
-- Ensure that all hardware is running the latest, approved drivers.
-- If you are storing your databases and log files on a SAN, the System Monitor performance counters for disk I/O are unreliable. Instead, use vendor-supplied tools to monitor disk performance.
MS SQL Server DBA Checklist - Performance Tuning - Indexing
-- Periodically, run the Database Engine Tuning Advisor against current Profiler traces to identify potentially missing indexes.
-- Remove indexes that are never used.
-- Don’t accidentally create redundant indexes.
--. As a rule of thumb, every table should have at least a clustered index. Generally, but not always, the clustered index should be on a column that monotonically increases — such as an identity column, or some other column where the value is increasing — and is unique. In many cases, the primary key is the ideal column for a clustered index.
-- Since you can only create one clustered index per table, take extra time to carefully consider how it will be used. Consider the type of queries that will be used against the table, and make an educated guess as to which query (the most common one run against the table, perhaps) is the most critical, and if this query will benefit most from having a clustered index.
-- If a column in a table is not at least 95% unique, then most likely the query optimizer will not use a non-clustered index based on that column. Because of this, you generally don't want to add non-clustered indexes to columns that aren't at least 95% unique.
-- Keep the "width" of your indexes as narrow as possible. This reduces the size of the index and reduces the number of disk I/O reads required to read the index, boosting performance.
-- If possible, avoid adding a clustered index to a GUID column (uniqueidentifier data type).
-- Indexes should be considered on all columns that are frequently accessed by the JOIN, WHERE, ORDER BY, GROUP BY, TOP, and DISTINCT clauses.
-- Don't automatically add indexes on a table because it seems like the right thing to do. Only add indexes if you know that they will be used by queries run against the table.
-- When creating indexes, try to make them unique indexes if at all possible. SQL Server can often search through a unique index faster than a non-unique index because in a unique index, each row is unique, and once the needed record is found, SQL Server doesn't have to look any further.
-- If you perform regular joins between two or more tables in your queries, performance will be optimized if each of the joined columns have appropriate indexes.
-- Don’t automatically accept the default value of 100 for the fill factor for your indexes. It may or may not best meet your needs. A high fill factor is good for seldom changed data, but highly modified data needs a lower fill factor to reduce page splitting.
-- Don't over index your OLTP tables, as every index you add increases the time it takes to perform INSERTS, UPDATES, and DELETES. There is a fine line between having the ideal number of indexes (for SELECTs) and the ideal number to minimize the overhead that occurs with indexes during data modifications.
-- If you know that your application will be performing the same query over and over on the same table, consider creating a non-clustered covering index, or a non-clustered index with included columns, on the table. A covering index, which is a form of a composite index, includes all of the columns referenced in SELECT, JOIN, and WHERE clauses of a query. Because of this, the index contains the data you are looking for and SQL Server doesn't have to look up the actual data in the table, reducing logical and/or physical I/O, and boosting performance. A non-clustered index with included columns is similar to a covering index, except that the extra columns you add to cover the query are not part of the index.
MS SQL Server DBA Checklist - SQL Server 2008 Compression
-- SQL Server 2008, Enterprise Edtition, offers both row and column compression, which can be used to reduce the amount of space data takes up on disk and in the buffer pool, potentially boosting performance.
-- Compression requires the use of additional CPU cycles to implement. Because of this, if your server currently has a CPU bottleneck, then compression should probably be avoided.
-- Compression works best for tables that experience mostly reads. Tables that experience a large number of DML activities should probably not be compressed.
-- Don’t use compression on tables subject to heavy inserts, such as bulk inserts.
MS SQL Server DBA Checklist - SQL Server 2008 Data Collector
-- The SQL Server 2008 Data Collector can be used to gather, store, and analyze many different types of SQL Server performance activities.
-- Using the SQL Server 2008 Data Collector imposes some performance overhead, and because of this, you may want to minimize the amount of data that is collected by it.
-- While the Management Data Warehouse can exist on each instance of SQL Server, it is recommended that a dedicated SQL Server be used to store the Management Data Warehouse used to store the collected data.
MS SQL Server DBA Checklist - Best Practices for Resource Governor
-- The SQL Server 2008 Resource Governor can be used to restrict how much CPU and memory resources are allocated to a particular user connection, allowing the DBA to give resource preference to some connections over other connections.
-- If you use the Resource Governor, ensure that the DAC is turned on, as it is the only way to troubleshoot and fix a bad Resource Governor configuration.
-- Classifier user-defined function should be well tested and optimized. If written incorrectly, a classifier function can cause connections to slow down, time out, and even create an infinite loop that locks up SQL Server.
-- Monitor Resource Governor after implementation to confirm appropriate use. If you are not careful, you can introduce query performance problems that didn’t exist before using Resource Governor. For example, if memory is limited too much, it can cause a suboptimal execution plan to be created and executed, hurting performance.
-- Test your Resource Governor implementation on test system before implementing it on production
No comments:
Post a Comment