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 will 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)
• 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?