The A to Z of Best Practices for SQL Server

Written by Justin Hostettler-Davies on 31 January 2008

DBAs are employed purely to improve efficiency and accuracy for the user community, as well as saving time and more importantly money for the business. If the following good practices can be adhered to � everyone can win.
The business runs smoothly and the individual DBA can focus on more challenging IT projects on new technology etc instead of spending best part of his/her day babysitting SQL servers.

A - Administrators

Probably the most important factor in ensuring your SQL Server databases and servers are performing well, maintained correctly and continually monitored is the Database Administrator. As the DBA in your company, you are pivotal to the success of the overall SQL infrastructure. The data is effectively your responsibility. After all, you are the person everyone comes running to when things start to go wrong, the resolution � make sure they don�t go wrong in the first place! The rest of this alphabet, if followed, will ensure an easier role for the Database Administrator and ultimately a happier work life!

B - Backups

If a DBA is the most important part of maintaining a healthy SQL environment, then backups come a very close second! Data is what most organisations regard as their most important asset. Ensure all databases are backed up frequently (every night or every week depending on data involved).
You cannot restore data if you haven�t backed it up.
Create a solid backup strategy, mixing full backups, differential and transaction log backups where necessary. A standard strategy could be as follows:-
Daily � all system databases (full backup)
Daily � transaction log backups (every 30/60mins)
Daily � Differential backups
Weekly - / Bi-Weekly � Full backups of all user databases
But your own strategy will mainly come down to how much data loss is deemed �acceptable� by your organisation.As well as the database itself, I would recommend (from experience!) backing up all your DTS packages, scripting all your users and SQL jobs. Script all other objects within a database too just in case you need to quickly recreate them.
Also have your OS guys to backup the system partition after any configuration changes.
Scripts are an important component in the operation of any database; this is why I would recommend backing up all your scripts too.
If you�re going to choose only one of these good practices to implement, choose this one. You will have achieved your two primary objectives � the protection of the data and your own piece of mind! In short here are some what practices to avoid:-
Backing up directly to tape.
Backing up to the same physical drive as the databases.
Failing to check for corruption before backup.
Failing to check for successful backup (both the SQL Server Agent job and disk write). Also, failing to verify the backup (RESTORE VERIFYONLY).

C - Coding

As you�ve probably gathered from my other articles � I�m no programmer, but even writing SQL code has its own best practices. For instance whenever I�m reading any code, I always find it extremely useful if it has been �documented� � wherever appropriate use comments (someone else may inherit your scripts !)
Even novices like me know not to use things like SELECT * and to avoid wildcards, NULLs & BLOBs where possible. Use constraints rather than rules. It is also recommended to avoid cursors � I guess this is mainly because of the impact it could have on the performance of a server. Avoid the need for long running transactions and implement appropriate normalisation. SQL server provides you with the tools to optimise queries.
Badly designed code = Poor performing applications = Dissatisfied users = Hassle.

D - Documentation

I cannot stress more the importance of documentation. Not only does it help you and your colleagues during absences etc but it can act as a crucial tool in both your recovery process and ongoing monitoring & maintenance of SQL servers.
Everything needs to be documented � your backup strategy, restore procedures, disaster recovery plans, as well as aspects such as server maintenance and security information. You don�t want to be running around frantically looking for bits and pieces of information when an emergency occurs and chaos ensues.
We keep �runbooks�, a source of information containing records of recent work carried out on servers, backup history, future planned work/outages, contact details, change management and downtime records etc. Always think to yourselves, if you employed somebody new how quickly would it take them to understand and get to know your SQL environment? Plus bosses always like to see good documentation.
Updating your documentation is important though too; outdated, irrelevant documentation is simply a waste of time and could prove obstructive in times of emergency.Also ensure you maintain a record of all sa & other login passwords for all your servers.
Good documentation sets usually contain a database schema document too and sometimes an ERD (entity relationship diagram) could come in handy, especially if you have inherited the database.
Now, hopefully if your system goes down, you won�t be running around looking for any usernames or passwords and you shouldn�t need to fill holes in any existing documentation.

E - Encryption

SQL Server brings many encryption options.
It is not recommended to use NTFS data file encryption (EFS) and compression on your datafiles and logfiles.
Enable encrypted connections to your servers, and I would even consider only allowing encrypted connections through � it all adds to a more secure environment.When allowing SQL authentication, I would urge you to encrypt either the network layer with IPsec or the actual session with SSL.

F - Full

Full recovery model should be used on all productive servers, especially the mission-critical ones. Full-recovery allows you to perform transactional backups, allowing the recovery of more data, faster.
I would also recommend Full Backups wherever possible of both system databases and your user databases. Again this will aid the recovery process.
Be careful though running full backups frequently may impact on the performance of a server, so try to schedule these for within �quiet-times�.

G - Gatekeeper

A gatekeeper is a common simile used in SQL world, a gatekeeper is someone who manages change control. Change needs to be controlled and continually tracked.
A DBA should always be able to answer the question "what changes have been made recently?" Especially when things seem to go wrong, if you find time to document changes that have recently occurred on servers, you�re more likely to find answers.
We need to know very clearly what recent changes have occurred. Again I refer you back the �runbook� idea � non-DBA staff and on-call personnel will need to know where to find information and change history on a server.
A DBA should always be able to answer the question "what changes have been made recently?"

H - High Availability

Availability of data is very important to most organisations, whether you decide to implement basic log-shipping, or more complex technologies such as mirroring and clustering � it is good practice to have some kind of �HA�.
Again, if failure or corruption occurs, you�ll be able to quickly get your data to your users � sometimes without them ever realising there�s been a problem!
Go to http://www.gre-sqlserver-solutions.com/clustering.html for more information on clustering.
High-availability techniques should complement but NOT a replacement to backup & recovery.

I - Indexing

Indexes if created and maintained correctly can vastly improve query times and general database performance. But if not maintained and used properly could have an adverse effect.
As a rule of thumb every table should have at least have a clustered index. Be aware though you don�t have to create indexes for the sake of it � redundant, non-required indexes can cause detrimental effects of the timeliness of queries etc.
Too may indexes on general OLTP tables will affect performance.Unfortunately the query processor does not simply select the indexes it needs and ignore the rest (would be nice though!).
Where possible, try to create unique indexes, as SQL searches these quicker than non-unique indexes.
The most obvious opportunity for improving performance is creating an index where one was missing (SQL 2005 comes with dynamic views allowing you to monitor these - sys.dm_db_missing_index_details. This stores a list of columns the query processor has gathered since the last server restart which would assist in covering the queries)

J - Janitor

A major part of a DBA�s make-up is to frequently �clean-up� databases.
You need to �Update Statistics�; SQL Server provides two options to keep the statistics current �auto create statistics� and �auto update statistics�. I would recommend keeping these on! Or if not, for performance reasons then ensure you schedule in periodical manual updates.
Shrink periodically; many databases need to be shrunk in order to free up valuable disk space. Conversely, automatic shrinking may cause performance degradation, so schedule in manual shrinkages is my advice.
Also re-organise your indexes to avoid fragmentation, these can easily be configured in maintenance plans too. Regular rebuilding of indexes and defragmentation is recommended too.
DBCC CHECKDB - Ideally consistency check using CHECKDB should be run before performing online backups etc but in the real world I know this isn�t always possible. DBCC CHECKDB is a very time and resource consuming activity that will put a heavy workload on your system, so generally I would advise to avoid running during busy times.
This will help detect physical inconsistencies at an early stage and aid minimising the impact of potential corruption. Once a fortnight is adequate.Unwanted backup files � maintenance plans can be automatically configured to retain a certain amount of backup files and discard the rest.
Last but not least, you should be checking that your maintenance plans have actually been running successfully. Otherwise all your backup and maintenance efforts are pointless.

K - Knowledge

As mentioned at the start, a DBA is crucial to the wellbeing of your databases and surrounding environment.
I cannot emphasise enough, the importance of knowledge � technology changes rapidly and SQL Server is no different � new and previously unknown features appear on the horizon constantly � ensure you keep abreast of developments and known issues.
If you�re a manager, ensure your DB personnel are given every opportunity to continue learning; I shudder to think of all the functionality I haven�t touched with SQL Server yet. The more you know, the more useful you�ll be when disaster strikes!

L - Log Files

You may not even be aware of all the log files that SQL Server produces.SQL Server error logs � its good practice to back these logs up too with your other scripts etc. Could use them as evidence further down the line!
Sql agent logs � we have weekly jobs in place to truncate these logs � as they can grow quite large if your server rarely gets restarted etc.
Job history � you can configure notifications to alert you when a job fails and for what reason etc. Plus, if you run maintenance plans, they too have their own logs.OS event logs � use event viewer to monitor the servers� behaviour and highlight any issues.
As for the actual database transaction log, check DBCC SQLPERF (LOGSPACE) to measure log size and log space used to determine how much space is consumed by transaction log. This needs to be monitored frequently.

M - Monitoring Tools

There are numerous SQL server monitoring tools out there on the market � I discuss two of them in http://www.gre-sqlserver-solutions.com/MonitoringTools.html
Production database usage � how often is your database getting updated and therefore at what rate does it tend to grow? You can use these measurements to aid future capacity-planning etc
Disk space � this is a major bug bear of ours, we constantly have to monitor free disk space on our servers � a full hard disk would result in database failure. You may also have no room to back your data up if you do not continually monitor this. (Of course the resolution to this is a hefty SAN !)
Locking / blocking � monitoring locks etc can help prevent conflicts in user queries etc and enhance the overall performance of the server.Alerts & notifications � most of the tools allow you to configure automatic alerting to notify you of potential problem areas etc helping you to prevent, rather than cure.
Unplanned downtime depends on how quickly system failures are notified to DBAs and how soon they can start the recovery process.Application Performance � it may not be your primary concern but by monitoring the behaviour of your database activity you will also be able to pinpoint possible flaws in application design. That will please your developers! And besides third party applications may need to be upgraded in order to leverage new functionality you may have added to your SQL Server.
You may also use MOM (Microsoft Operations Manager) which can be very informative. If you don�t the standard SQL Server alerts are good tools for identifying things like tempdb filling up etc (see tempdb)
Note in SQL 2000 default alerts are disabled after install, so you�ll need to enable them and in SQL 2005 there are no default alerts, so you�ll need to create some.

N - Nulls

Using NULLs in your database can be a very bad idea. I would recommend never allowing NULL values. NULL values cause several problems and they often confuse front-end applications.
Allowing NULL values makes you work extra hard to get the kind of data you are looking for. From a related angle, allowing NULL values reduces your convictions about the data in your database. You can never quite be sure if a value exists or not. Will that make you feel safe and comfortable if you�re programming?
Bottom line: DO NOT ALLOW NULL VALUES unless absolutely necessary. You will only be making things harder for yourself. Of course, for some business rules, there will be legitimate reasons to have NULL values, just be cautious.

O - Optimisation

Optimisation is key. As mentioned ensure your datafiles and logfiles are separated and where possible use SAN technologies. When installing SQL, consider your performance by placing SQL Server on its own physical server separate from other applications and services.
And the usual advice of separating your datafiles and logfiles onto separate disk arrays.
Tools such as Profiler and Performance Monitoring are very effective if used in the correct manner. System tables provide interesting information too � use them.

P - Patching

Its good practice to keep an up-to-date inventory of all versions, patch sets etc of the SQL servers in your organisation.
Regularly check the Microsoft website for latest patch releases and security bulletins. I would also recommend maintaining your test systems � so they mimic your live systems, that way, any patches that do get released can be properly and thoroughly tested.

Q - Quick Fixes

Here are just a few quick and helpful stored procedures I run on a daily basis:-

To identify possible blocking - exec sp_who2
To cut over your SQL error log - exec sp_cycle_errorlog
To check free space on drives - exec master..xp_fixeddrives

R - Recovery Plan

Again, a vital piece of any SQL jigsaw is an effective recovery plan. Everyone needs one, as with documentation you need to ensure that a recovery plan is in place and ready for anyone (DBA or otherwise) to use if server rebuilds or data recovery is required.
Identify the processes and resources that are critical to the organisation. Develop realistic recovery objectives for them and then determine how you can achieve those objectives as simply and cost-effectively as possible. The wrong choice in recovery plan can have disastrous consequences.
Test out your recovery plans periodically too, as with any procedures & plans � they too can become outdated as your environment continues to change. Simulating disaster scenarios will help you to evaluate your plans capability � in order to be prepared for the real thing!
Always remember to test the full restore process. Restoration is vital, we all agree. But quicker restoration with minimal downtime is just as important. Hence the need for a good recovery plan.
The last thing your on-call DBA needs at 3am in the morning is a bombardment of nasty little surprises when he comes to restore from failure!

S - Security

Use the principle of least privilege. You should periodically review your security settings and who has access to your productive databases, what privileges they have compared to the privileges they know need etc.
Other security best practices include � only enabling required services and features, installation of firewalls and secure internet access, anti-virus software, as well as keeping abreast of the latest security information and patches. Security basics like strong passwords & authentication also apply to SQL Server. If you are using SQL/Windows security, then guard the SA password (and make sure it isn�t blank!).
The latest software downloads for SQL can be found at http://www.microsoft.com/sql/downloads/trial-software.mspx
Basically, everyone in the office needs to understand the importance of the security of the data

T - TempDB

If it�s being used heavily I would recommend separating it from your other datafiles and logfiles on its array. Microsoft recommends placing the tempdb on a fast I/O subsystem. In this case, try to use RAID 0 disks for tempdb database.
This will reduce I/O contention issues, and improve overall performance. As mentioned earlier, use Update Statistics to make sure SQL server knows the best way to self-tune SQL queries.
It is difficult to estimate the tempdb space requirement for an application, just try to avoid things like cursors and temporary tables wherever possible. Monitor tempdb via either Performance Monitor or through the new 2005 �sys.dm_db_file_space_usage� view.
Automatically growing tempdb can result in performance degradation; therefore you should set a reasonable size for tempdb database and a reasonable autogrow increment to avoid it from automatically growing too often.

U - User relations

They may be the root of much frustration, but your users are crucial to the way you administer your databases. They could be the main source of most of your problems, but without a good working relationship, those problems will become difficult to resolve.

V - Views

Using Views is always a good way to control permissions, you do not have to give access to base tables, views allow more control of what you allow your users to see. With the later versions of SQL Server too, view performance can be considerably increased by implementing indexes on them.

W - World Wide Web

The internet is jam-packed full of interesting SQL Server forums, blogs and website such as http://www.gre-sqlserver-solutions.com can provide a rich source of information.
We all run into problems when it comes to administering SQL Server and need help. I'm really glad that the web has so many resources for DBAs like me who have to solve so many different problems.
I don't know everything about SQL Server, but luckily, with so much content on the web, usually I can find the answer pretty quickly.

X - XML

With the current upsurge in handling web documents, XML is becoming a very popular way in which to store web page data and emails.
With SQL 2005 � the use of the XML data type comes strongly recommended! In fact the use of XML datatypes can significantly reduce the storage size and improve overall performance, enabling the data engine to use XML indexes etc

Y - Yukon & the next generation

SQL Server technology is continually being researched and new releases are constantly being made available, introducing new functionality and some potential business benefits for your organisation e.g.Reporting Services is being developed with each version new (http://www.gre-sqlserver-solutions.com/ReportingServices.html).
SQL Server has an Upgrade Advisor to assist you in the process. Beware though, newer versions may render some of your working practices ineffective and these will need to be revisited etc.
Always best to test out any new versions of SQL Server you�re looking to invest in, on a separate test environment � will make the eventual upgrade process less hassle.
Also beware of compatibility issues between SQL 2000 and SQL 2005, 2005 generated scripts are not compatible to SQL 2000, so use SQL 2000 to generate your scripts if you want to make your scripts work well on both versions!
Oh � and make sure your applications will work with newer versions of SQL Server too, you may be willing to move on with technology but your third-party suppliers may not.

Z - Zero Tolerance

As managers, team-leaders or even yourself as a DBA you should enforce zero-tolerance when it comes to the maintenance, security and well-being of your databases � following the practices in this article and I can assure you everyone will be happier in the long run.
You get paid to keep the database servers running and the data backed up and secure. Your bosses rely on you to ensure the organisation doesn�t effectively �crumble� beneath their feet!
If these good practices have not been followed by your predecessors for example, then now is the time to do something about it. Be assertive and most importantly do whatever it takes to keep your job!

To conclude:-

Best practices aren't one size fits all. You may be in an environment where best practices don't seem to fit. There are situations that don't always make it possible to implement all the practices I�ve discussed here. I�m just saying don�t disregard them.You may even have to deduce your own best practice to suit your particular needs.

If you would like one of our experts to evaluate and assist in the maintenance and configuration of your SQL Server environment please contact us.