Table of Contents
1.
General DBA Best Practices:
·
Day to Day activities.
·
Installation
·
Upgrading
·
Security
·
Job Maintenance
·
Database Settings
·
Replication
2.
High Availability Best Practices
·
General High Availability
·
Disaster Recovery
·
Backup
·
Clustering
·
SQL Server 2005 Mirroring:
·
Log Shipping
3.
Performance Tuning Best Practices
·
Performance Monitoring
The SQL Server Database
system from Microsoft
Corporation has gained popularity over the last
years, and it is believed to be one of the few surviving relational database systems in this marketplace (the others being Oracle and DB2 from
IBM).
General DBA Best Practices:
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.
5. Throughout
the day, periodically monitor performance using both System Monitor and
Profiler.
6. Use
Enterprise Manager/Management Studio to 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 emailed to
you. Take actions as needed.
9. Run the SQL
Server Best Practices Analyzer on each of your server’s instances on a periodic
basis.
10. 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.
3. Don’t install SQL Server services you don’t use, such as
Microsoft Full-Text Search, Notification Services, or Analysis Services.
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, you want
to 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 arrays 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.
8. Do not install SQL Server on a domain
controller.
9. Be sure that SQL Server is installed on an
NTFS partition.
10.
Don’t use NTFS data file encryption
(EFS) and compression on SQL Server database and log files.
Upgrading
1. Run the Upgrade Advisor before upgrading. Make any
necessary changes before performing the upgrade.
2. Perform a test upgrade of your test SQL Servers before
you upgrade your production servers. And don’t forget to test your applications
with the new version also.
3. Before you upgrade, be sure you have a plan in place to
fall back to in case the upgrade is problematic.
4. Don’t upgrade SQL Server clusters in place. Instead,
rebuild them on new hardware.
5. If you upgrade from a previous version of SQL Server,
you should update all of the statistics in all your databases using
either UPDATE STATISTICS or sp_ updatestats. This is
because statistics are not automatically updated during the upgrade process.
Security:
1. Ensure the physical security of each SQL Server,
preventing any unauthorized users to physically accessing 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. Give users the least amount of permissions they need to
perform their job.
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. Remove sample databases from all production SQL Server
instances.
16. Use Windows Global Groups, or SQL Server Roles to
manage groups of users that need similar permissions.
17. Avoid creating network shares on any SQL Server.
18. Turn on login auditing so you can see who has
succeeded, and failed, to login.
19. 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.
20. Ensure that your SQL Servers are behind a firewall and
are not exposed directly to the Internet.
21. 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.
22. Run each separate SQL Server service under a different
Windows domain account.
23. 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.
24. When using distributed queries, use linked servers
instead of remote servers.
25. Do not browse the web from a SQLServer.
26. Instead of installing virus protection on a SQL Server,
perform virus scans from a remote server during a part of the day when user
activity is less.
27. Add operating system and SQL Server service packs and
hot fixes soon after they are released and tested, as they often include
security enhancements.
28. Encrypt all SQL Server backups with a third-party
backup tool, such as SQL Backup Pro.
29. Only enable C2 auditing or Common Criteria compliance
if required.
30. Consider running a SQL Server security scanner against
your SQL servers to identify security holes.
31. Consider adding a certificate to your SQL Server
instances and enable SSL or IPSEC for connections to clients.
32. If using SQL Server 2005, enable password policy checking.
33. If using SQL Server 2005, implement database
encryption to protect confidential data.
34. If using SQL Server 2005, don’t use the SQL Server
Surface Area Configuration tool to unlock features you don’t absolutely need.
35. If using SQL Server 2005 and you create endpoints,
only grant CONNECT permissions to the logins that need access to them.
Explicitly deny CONNECT permissions to endpoints that are not needed by users.
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, weekly, or monthly) perform a
database reorganization 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 unnecessary data reads, slowing down
SQL Server’s performance. Reindexing tables will also update column statistics.
6. Don’t reindex your tables when your database is in
active production, as it can lock resources and cause your users performance
problems. Reindexing should be scheduled during
Downtimes, or during light use of the databases.
7. At least every two weeks, run DBCC CHECKDB on all your
databases to verify database integrity.
8. 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.
9. If you rarely restart the mssql server service, you may
find that the current SQL Server log gets very large and takes a long time to
load and view. You can truncate (essentially create a new
log) the current server log by running DBCC ERRORLOG. Set
this up as a weekly job.
10. Script all jobs and store these scripts in a secure area
so they can be used if you need to rebuild the servers.
Database
Settings:
1. Unless you know exactly what you are doing and have
already performed impartial experiments that prove that making SQL Server
configuration changes helps you in your particular environment, do not change
any of the SQL Server configuration settings.
2. In almost all cases, leave the “autocreate statistics”
and “auto update statistics” options on for all user databases.
3. In most cases, the settings for the “maximum server memory”
and the “minimum server memory” should be left to their default values. This is
because the default values allow SQL Server
To dynamically allocate memory in the server for the best
overall optimum performance. If you use AWE memory, then this recommendation is
to be ignored, and maximum memory needs
to be set manually.
4. Many databases need to be shrunk periodically in order
to free up disk space as older data is deleted from the database. But don’t be
tempted to use the “auto shrink” database option, as it can waste SQL Server
resources unnecessarily. Instead, shrink databases manually.
5. 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.
High Availability Best Practices:
General High
Availability
1. Physically protect your SQL Servers from unauthorized
users.
2. Physically document all of your SQL Server instances.
Incorporate effective change management.
3. Always use a RAIDed array or SAN for storing your data.
4. Use SQL Server clustering, database mirroring, or log
shipping to provide extra fault tolerance.
5. Replication is not an effective means to protect your
data.
6. Ensure that your entire IT infrastructure is redundant.
It is only as strong as its weakest link.
7. Always use server-class hardware, and standardize on
the same hardware as much as possible.
8. Use hardware and software monitoring tools so you can
quickly become aware of when problems first arise.
9. After testing, apply all new service packs and hot
fixes to the OS and SQL Server.
10. Cross-train staff so that there are multiple people
who are able to deal with virtually any problem or issue.
Disaster
Recovery:
1. You must create a disaster recovery plan and include
every detail you will need to rebuild your servers.
2. As your SQL Servers change over time, don’t forget to
update your disaster recovery plan.
3. 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.
4. Fully test your disaster recovery plan at least once a
year.
5. 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.
Backup:
1. All production databases should be set to use the full
recovery model. This way, you can create transaction log backups on a periodic
basis.
2. Whenever possible, perform a daily full backup of all
system and user databases.
3. For all production databases, perform regular transaction
log backups, at least once an hour.
4. Perform full backups during periods of low user activity
in order to minimize the impact of backups on users.
5. Periodically test backups to ensure that they are good
and can be restored.
6. Backup first to disk, then move to tape or some other
form of backup media.
7. Store backups offsite.
8. If using SQL Server 2005 encryption, be sure to backup
the service master key, database master keys, and certificates.
9. 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.
10. 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.
Clustering:
1. Detailed planning is critical to the success of every
SQL Server cluster installation. Fully plan the install before performing the actual
install.
2. 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.
3. Run only a single instance of SQL Server per node.
Whether you have two or eight nodes in your cluster, leave one node as a
failover node.
4. 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.
5. All cluster hardware must be on the Microsoft Windows
Clustering Hardware Compatibility List, and certified to work together as part
of a cluster.
6. 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.
7. 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.
8. Each node of a cluster should have identical hardware,
drivers, software, and configuration settings.
9. Fiber channel shared arrays are preferred over SCSI,
and Fiber channel has to be used if you include more than two nodes in your
cluster.
10. The Quorum drive must be on its own fault-tolerant,
dedicated, logical drive.
11. Once the cluster has been installed, test it
thoroughly for every possible failure scenario.
12. Do not run antivirus or antispyware on a SQL Server
cluster.
13. If you need to reconfigure any Windows or SQL Server
clustering configuration options, such as IP addresses or virtual names, you
will need to uninstall clustering and then reinstall it.
14. 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.
15. Once you have a stable SQL Server Cluster running, be
very leery about making any changes to it, whatsoever.
SQL Server
2005 Mirroring:
1. The principal database and the mirror database should
be on separate physical hardware, and ideally, in different physical locations.
2. The witness server should be on separate physical
hardware, and be on a separate network (best if at a third location).
3. 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.
4. Use high availability mode whenever possible, and high
performance mode only when required.
5. The hardware, along with the OS and SQL Server
configuration, should be identical (at least very similar) between
the two servers.
6. While a fast connection is not required between mirrored
servers, the faster the connection, and the better quality the connection, the
better.
7. 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.
8. Thoroughly test database mirroring before putting it
into production.
9. Monitor database mirroring daily to ensure that it is
working properly, and is meeting performance goals.
10. Develop a formal operational and recovery procedure
(and document) to support mirroring. Periodically test the failover process to
ensure that it works.
Log Shipping:
1. 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.
2. If you take advantage of SQL Server 2000 or 2005 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.
3. Monitor log shipping daily to ensure that it is working
successfully.
4. Learn what you need to know to fix shipping if synchronization
is lost between the production and backup databases.
5. Document, and test your server recovery plan, so you
will be ready in case of a server failure.
Performance Tuning Best Practices
Performance
Monitoring
1. Regularly monitor your SQL Servers for blocked
transactions.
2. Regularly monitor system performance using System
Monitor. Use System Monitor for both real-time analysis and for historical/baseline
analysis.
3. 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.
4. 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.
5. Perform performance monitoring from a computer that is
not the SQL Server you are monitoring. Run monitoring tools on a separate
desktop or server.
