SSW Rules to Better SQL Server Databases

Home > SSW Standards > SSW Rules > Rules To Better SQL Server Databases

 

Frequently when I'm reviewing a client's SQL Server database design, the in-house developers tell me that "it's a bit of a dog's breakfast." 10 different developers have been working on it for a few years so a lot of the basic defaults have not been changed. Here are some of the typical things that all SQL Server DBAs and Database developers should know. These rules are above and beyond the most basic textbook recommendations of:

• Ensuring your databases are Normalized and in 3rd Normal Form
• Making sure you have primary keys, foreign keys and simple indexes to improve performance
• Making sure you Back up regularly
• Basic Naming conventions (see some of our object naming conventions here)
• Minimizing resultset sizes and data over the wire

Now I don't see a purpose in rewriting applications that work. But - I do see the value in making an application standardised. We all KNOW that standards are a good thing, it's time we were all brave enough to be strict. Here are the standards that we follow...

Do you agree with them all? Are we missing some? Email us your tips, thoughts or arguments. Let us know what you think.

*** Update *** Instead of manually implementing these rules, use SSW SQL Auditor to automatically implement many of them for you. Download it and give it a go.

SSW's Rules to Better SQL Server Databases 1 - 99 General and DBA Rules Do you measure up-time? Do you check your SQL Server is up-to-date? Do you design for change? Do you script out all changes? Do you configure all your SQL Server Services to use a Domain Account rather than a local service account? Do you name your SQL Server Domain Account as 'SQLServerMachineName' so it is easily identified when performing network operations? Do your SQL Server Services run under non-Administrator accounts? Do you use Database Mail (not SQL Mail) in SQL 2005? Do you turn on all the default alerts? Have you created your own alerts? Backup - Do you setup a complete Maintenance Plan? Backup - Do you backup scripts? Backup - Do you take Restoration seriously? Backup - Do you have a Restoration Standard? Are you aware of all the log files? Do you secure your server by changing the 'defaults'? Do you turn on security auditing? Do you increase the Log Size of your Event Viewer? Do you remove unnecessary permissions on databases? Do you use Performance Alerts? Do you make sure you use a consistent Collation server-wide? Do you create new databases in the default data directory? Are you aware of compatibility issues between SQL Server 2000 and 2005? Do you have a general Contact Detail table? Do you know how to provide best database schema document? Do you avoid collation errors? 100 - 199 Data Rules Don't allow NULLs in text fields Don't allow NULLs in number fields if it has the same meaning as zero Don't start data in character columns with empty line Don't start data in character columns with spaces Use Identities in SQL Server (but don't use Autonumbers in Access) Don't delete records - just flag them as deleted Make sure you have valid date data in your database DateTime fields must be converted to universal time Do you use 3rd party audit tools to audit data changes?