Rules to Better SQL Databases - Admin

​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) 
  • Minimizing resultset sizes and data over the wire​​​
Hold on a second! How would you like to view this content?
Just the title! A brief blurb! Gimme everything!


  1. ​DBAs - Do you measure Up-Time?

    ​If you measure up-time you can pro-actively inform your manager how successful you have been as a DBA. You can do this in 2 ways:

    Option 1: High Tech Solution - using System Center Operations Manager (SCOM)

    SCOM allows you to monitor and generate reports on the total uptime of your SQL Server and other service level exceptions. You need the following for these reports:

    1. System Center Operations Manager and SQL Server on the network when performing a network scan
    2. Microsoft System Center Management Pack for SQL Server
    ​Option 2: Low Tech Solution - using a recurring select as a heartbeat
    1. Run a query as a ping once every a 5 minutes something that takes about 2 seconds
    2. SELECT * FROM Orders Five times
    3. Log it with the time
    4. Graph - See uptime
    5. Graph See performance

  2. DBAs - Do you check your SQL Server is up-to-date?

    ​​​Most patches are for security. SQL Slammer showed that it's no good waiting a month before you decide to install a service pack. I would say wait one week and then install the service pack (maximum delay should be 2 weeks)

    • Manually check your server using @@​version

  3. ​DBAs - Do you design for database change?

    ​​Many developers are frightened of making a change to the existing database because they just don't know what applications are using it. This is especially a problem when you are dealing with databases that you did not create. Here are some approaches to this issue:

    1. You could run around the office and find some one and hope they know (unbelievably this seems this the most common method!)
    2. Trawl through source control, all network locations and all the source code around to check what connection strings are being used
    3. You can have a zsApplication table and manually populate with application it uses (Recommended). This can be populated with a run of a SQL profiler over a period of a week so all usage is captured.
      SQLDatabases_zsApplication.png
      Figure​: Add a zsApplication table to make applications that use it visible to all developers
    4. Keep a constantly running login Audit with a SQL Server Profiler Trace that saves to a table​ and make sure all applications have an application name in their connection string. This method is the most comprehensive option but is not recommended because you get a constant performance hit from SQL Profiler running.
      SQLDatabases_ProfileTraceForSecurity.jpg
      Figure: SQL Profiler can help you design for change with auditing of Login events by giving you a guide on what applications are connecting to your database
  4. ​DBAs - Do you script out all changes?

    ​Every time a change is made to your product's SQL Server Database, script out the change. You can use Enterprise Manager, VS.NET or Query Analyzer but every time you make changes you must save the change as a .sql script file so any alterations are scripted. Everything at SSW is usually done three times, once on Development, once on Staging and once on Production. Change control is one of the most important processes in ensuring a stable database system.

    Keep the scripts in a separate directory to any other scripts or files. This way you can always go back to them and find out what alterations you have made to the database in version xxx to find errors. If you have all the scripts you are able to rebuild the database from scratch. At SSW we name this folder SQLChangeScripts so as to not confuse it with other script folders.​​

    SQLChangeScripts.jpg
    Figure: A list of change SQL scripts, each file name is in the correct format

    The script file format should be: <version>_<description>.sql

    The <version> should be a number which is padded with leading zeros (0) on the right to firm 3 or 4 digits (however long we need).​​​​

  5. ​DBAs - Do you configure all your SQL Server Services to use a Domain Account rather than a local service account?

    Depending on which components you decide to install on your SQL Server, you may need to configure the following services​:

    • SQL Server
    • SQL Server Agent
    • SQL Server Reporting Services
    • SQL Server Integration Services
    • SQL Server Fulltext search
    • SQL Server Analysis Services

    In the service properties window for these services, ensure that the Service Startup Account is run as "This Account" and not as "Built-in Account". Otherwise, you won't get all the functionality by default such as the ability to use Replication, Linked Servers or connect to other machines.

    For security, you should not have this domain account​ in the Administrators group.​

    SQLDatabases_RunAsAccount_Bad.png
    Figure: Bad example - This service is using a built-in local service account​
    SQLDatabases_RunAsAccount.png
    ​Figure: Good example - Run as Account should use a domain account rather than a built-in account​
  6. ​DBAs - Do you name your SQL Server Domain Account as 'SQLServerMachineName' so it is easily identified when performing network operations?

    ​​When you create the domain accounts under which SQL Server services run, you should name this domain account as "SQLServerMachineName". 

    E.g. SSW2000\SQLServerDragon

    If one of the SQL Server services updates a file on the network, then you can then determine which server wrote to the file.​

  7. ​DBAs - Do you run SQL Server Services under non-Administrator accounts?

    ​​​​​​​​You should always run all SQL Server services with the lowest possible privileges allowed in case the account is compromised. SQL Server setup makes the whole process of granting privileges a whole lot easier because it automatically creates groups with all the necessary ​permissions for you!​

    SQLDatabases_RunAsAccount_GroupsCreated.png
    Figure: SQL Server now creates groups for all the SQL Server services with the bare minimum permissions for you

    ​If you are running any SQL Server Service in a user account that has administrator privileges a user that compromises the account could do anything that administrator could do - including playing around with the registry with procedures like xp_regdeletevalue. So, if you use an Administrator account, you're in effect giving away the keys to the house. Is this something you want to do?

  8. DBAs - Do you use Database Mail (not SQL Mail) in SQL 2005?

    ​SQL Server 2005 includes Database Mail, a replacement for SQL Mail. Database Mail solves many of the problems inherent in SQL Mail, including:

    • HTML messages are now natively supported - so there's no need to use 3rd party dlls anymore
    • There's no need for outlook or MAPI profiles on server - communication is directly with SMTP server
    • Multiple profiles and accounts are supported to specify multiple SMTP servers or different email infrastructure situations
    • SQL Server queues messages even when the external mailing process fails
    • High security - users and roles have to be granted permission to send mail
    • Logging and auditing
    • Attachment size regulations and file extension requirements can now be implemented​
    SQLDatabases_SQLMail.png
    F​​igure: Bad example - Using SQL Mail

    EXEC master.dbo.xp_smtp_sendmail
    @FROM = N'your@email.com',
    @FROM_NAME = N'Sophie Belle',
    @TO = 'recipient@email.com',
    @subject = 'Vendor List',
    @message = 'The list of vendors is attached.',
    @type = N'text/html',
    @server = N'mail.company.com.au'

    Figure: Bad example - Avoid using SQL Mail -  you need to have Outlook on the server and there is no built-in logging
    SQLDatabases_DBMail.png
    Figure: Good example -  Use Database Mail

    USE msdb
    Execute dbo.sp_send_dbmail
    @profile_name = 'UTS',
    @recipients = 'your@email.com,
    @body = 'The list of vendors is attached.',
    @query = 'USE AdventureWorks; SELECT VendorID, Name FROM Purchasing.Vendor',
    @subject = 'Vendor List',
    @attach_query_result_as_file = 1

    Figure: Good example - Use database mail for scalability, built-in logging and HTML capability

     
    For a more in-depth comparison of SQL Mail vs Database Mail, see this intro to Database Mail in SQL 2005

  9. DBAs - Do you turn on all the default alerts?

    SQL Alerts are valuable because they can alert administrators of imminent SQL Server failures. e.g. when the msdb log file is full. To enable, you should change the settings under SQL Server Agent.​

    ​SQL has no default alerts. You will have to create them, and I recommend that you add all the fatal level exceptions to alerts.
    SQLDatabases_DefaultAlerts2005.png
    Figure: SQL Alerts - We recommend that you add the fatal exceptions as alerts​​
  10. DBAs - Do you create your own alerts?

    ​In addition don't forget to add your own alerts, such as sending an alert for the exception "is outstanding amount > $15000". Many people​ are not aware of this functionality and write triggers or get 3rd party products to the same job.​​

  11. Backup - Do you setup a complete Maintenance Plan?

    It is surprising how many IT staff create backup plans and then leave it at that. To have a complete maintenance plan, you should also consider the following:

    1. Checking database integrity
    2. Shrinking Databases
    3. Reorganizing Indexes
    4. Rebuilding Indexes
    5. Updating Statistics
    6. Cleaning up old maintenance histories
    7. Performing automatic backups
    8. Backing up System databases
    9. Last but not least - you should regularly check that the maintenance plans have been running successfully. Otherwise, all your backup and maintenance efforts are pointless
    This can be found under Management | Maintenance Plans​ within the database tee in SQL Server.

    SQLDatabases_MaintenancePlan.png
    Figure​: SQL Server - ​A Complete Weekly Maintenance Plan
  12. Backup - Do you back up scripts?

    Scripts are an important component in the operation of any database. This is why you should back up all your scripts and historical schema snapshots - so you can track the scripts that have been run and those that need to be deployed to test and production databases. We typically store these in source control such as VSS or Team Foundation Server as a Visual Studio Database project. You should regularly generate full scripts of all objects changed, keeping the following points in mind:

    • Don't encrypt your database objects if you can avoid it - otherwise, they can't be scripted.

    ​Use:

      • ​Enterprise Manager Generate Scripts Wizard OR​
      • ​SQL DMO object model to script out the objects OR​

  13. Backup - Do you take Restoration seriously?

    Restoration is vital, we all agree. But rapid restoration with minimal down time is just as important. Run practice restorations on a regular basis, as you don't want to find out that your back-up doesn't restore when a problem has already occurred.
    This restoration process is so important that you should have a termination clause in an employee's contract if the restore doesn't work when something goes wrong.​
  14. Backup - Do you have a Restoration Standard?

    If the restoration process is not clear and too complicated, then it will cost you both time and money when you are already in a pressure situation. Make sure you have a step by step and comprehensive restore standard with screenshots for every step of the way so there are no nasty little surprises when your system goes down.​
  15. DBAs - Do you know all the log files?

    ​SQL Server stores vital error and performance information in several different logs. You should be aware of all of them:

    1. SQL Server Error Logs
      • Configure how many you want to keep
      • You should Back up your SQL Server error logs with your other scripts
      • Sp_cycle_errorlog
    2. SQL Server Agent Error Log
      • Recycles after every service restart
    3. Job History Logs
      • Agent properties, Job System tab
      • Probably too low by default
    4. DBMaint history logs
    5. (Event Viewer) - Issues​

  16. DBAs - Do you secure your server by changing the 'defaults'?

    1. ​Disable defaults
      • Disable Administrator and Rename it, then create a new "honeypot" Administrator account with no permissions.
      • Disable Guest on the SQL
      • Change Port 1433
      • Delete the sample databases - (AdventureWorks, Northwind and Pubs). These have a Public Role which is a security risk and allow Massive SQL Statements
    2. Other security issues
      • Use a service account with a strong password
      • Dont run SQL Server service as an administrator
      • Run in integrated security mode
      • Run on NTFS file system - Encrypt the data files​

  17. DBAs - Do you turn on security auditing?

    ​Configure login security auditing:​

    • Not on by default
    • Configure on the security tab of Server Properties in Enterprise Manager
    • Enable for Failure
    • View using the Windows Event Viewer​
    SQLDatabases_EnableAuditing.png
    Figure: Enable Auditing for SQL Server logins
    Note: You can turn on a trace for SQL DDL operations statements.​
  18. DBAs - Do you increase the Log Size of your Event Viewer?

    ​Change the defaults from 512KB and "Overwrite events older than 7 days" to 64000KB and Overwrite as needed. This will allow the users to view Security audits and errors much further into the past with a minimal increase in space - and it will never bloat your server.​

    SQLDatabases_EventLog_Bad.png
    Figure: Bad Example - Using a ridiculously small log size
    SQLDatabases_EventLog_Bad.png
    Figure: Good Example - Using a ​reasonable log size
  19. DBAs - Do you remove unnecessary permissions on databases?

    What goes for permissions to Windows objects also goes for SQL Server objects. Remove all permissions but the bare minimum required to operate your application.​​

  20. DBAs - Do you use Performance Alerts?

    ​Performance alerts work well for problems that need to be discovered before they occur.

    For example, one problem that you may encounter is database file growth. Since databases are set to grow to a certain percentage, you needed to configure an alert to let you know when my database would draw close to that threshold. you can configure a performance alert that fired off when it reached 80% of that threshold. Here is an example of what you can do:​

    ​​To configure an alert to trigger an application, perform the following steps:

    1. Start the Microsoft Management Console (MMC) Performance snap-in (Start, Programs, Administrative Tools, Performance).
    2. Expand Performance Logs and Alerts, and select Alerts.
    3. Right-click in the right pane, and select New Alert Settings.
    4. Enter a name for the setting that reflects what the alert will monitor, and click OK.
    5. On the General tab, add the counter the alert will monitor and specify the values that will trigger the action.
    6. On the Action tab, select the Run this Program checkbox.
    7. Click the Browse button, and select the name of the application you want to run.
    8. Click OK.
    performanceAlert.gif
    ​You have just configured an application to run in response to an alert. Unfortunately, because the program doesn't interact with the desktop, it runs in the background, visible only in Task Manager. To enable the program to run interactively, perform the following steps:
    1. Start the MMC Services snap-in (Start, Programs, Administrative Tools, Services).
    2. Right-click Performance Logs and Alerts, and select Properties.
    3. On the Log On tab, specify the "Local System account" and select the "Allow service to interact with desktop" checkbox.​
  21. DBAs - Do you make sure you use a consistent Collation server-wide?

    Collation is the combination of language and sort orders, and you typically don't notice it until you start running cross database queries.

    It would make development simpler if the whole world spoke one language but even if you are using English, you will still encounter collation issues if you are not careful. The most common issue is the dreaded 'Cannot resolve collation conflict for equal to operation' error when joining on columns that have different collation orders. Collation is a great feature for international companies, but if you are not consciously using it then you should have ALL the objects in ALL the databases on ALL the servers using a consistent collation.​​

    Flexibility with collation orders has increased a lot since SQL 7.0:

    • SQL 7: Back in SQL Server 7, you could only define the collation at the server level and, once it was set, you could not change it without rebuilding the master database.
    • SQL 2000: This added the ability to have Column level collation which allows you to set it at the database or column level.

    However, with this column-level flexibility come additional issues. It is ideal for those who only want the column name 'FirstName' to be represented in accent insensitive sort order. However, one of the side effects, if you are not taking notice of collation, is that you end up with many different collations on many different databases.

    We feel that the only time you need inconsitent collations is when you have a rogue 3rd Party application like Microsoft Great Plains that enforces its own collation.

    See these Knowledge Base articles for more information about the issues you will encounter when you have inconsistent collations:

    • Q211874 - Why do I get the error 'Cannot resolve collation conflict for equal to operation'?
    • The database collation differs from the SQL Server default collation because it was attached or created with a different collation order. This causes issues when you attempt to join tables in databases that have different collation orders. For example, if your tempdb database and Northwind each have a different collation you will get the following error 'Cannot resolve collation conflict for equal to operation' when you attempt to do a join between tables from these databases

    • Q711843 - How do I change the collation order in my SQL Server 2000 or 7.0 database?
    • There is no 'recommended' collation as different collations will be used in different countries but as a guideline, installations in the United States and installations that require compatibility with SQL Server 7 databases should use the SQL_Latin1_General_Cp1_CI_AS collation. Non-United States installations in English speaking countries should use the Latin1_General_CI_AS collation​​

    SQLDatabases_CollationRecommended.gif
    Figure​: Setting the collation in SQL 2005 Setup - Choose Case Insensitive(CI), Accent Sensitive (AS)
  22. DBAs - Do you avoid collation errors?

    ​​You don't want this error:

    "120_ClientInvoice_ClientIDRequired.sql...Column 'dbo.Client.ClientID' is not of same collation as referencing column 'ClientInvoice.ClientID' in foreig..."

    When you write a stored proc - it must work regardless of the users collation. When you are joining to a temp table - meaning you are joining 2 different databases (eg. Northwind and TempDB) they wont always have the same collation.​

    The reality is that you can't tell a user what collation to run their TempDB - we can only specify the collation Northwind should be (we don't even want to specify that - we want that to be their default (as per their server))​​.

    ​Here is what you need to do:​​​

    SELECT
    #ClientSummary.ClientID,
    DateOfLastReminder = MAX(ClientDiary.DateCreated),
    DaysSinceLastReminder = DATEDIFF(day,MAX(ClientDiary.DateCreated),getdate())
    INTO #RecentReminderList
    FROM
    ClientDiary INNER JOIN #ClientSummary
    ON ClientDiary.ClientID = #ClientSummary.ClientID COLLATE
    database_default
    WHERE
    ClientDiary.CategoryID LIKE 'DEBT-%'
    GROUP BY
    #ClientSummary.ClientID

  23. DBAs - Do you create new databases in the default data directory?

    When trying to create a database in SQL Server 2005 from an existing create script written for SQL Server 2000, we came across a problem. Our create script was trying to determine the path to save the database file (the path to the default data store) by using the sysdevices table in the Master database; however, the schema for the Master database had changed in 2005 and our script could no longer find the column it relied on to determine this path.

    Rather than creating a new script specific to 2005, we found that by removing the optional FILENAME attribute altogether, both SQL Server 2000 and 2005 were happy and the database files were saved into the default data directory which is what we were after.

    The moral of the story is - keep it simple.

    ​When using a create script to create a new database, let SQL Server determine the filename and path from its default settings. This will help make the script simpler, more flexible, and ready to use with utilities such as MS OSQL and SSW SQL Deploy.

    DECLARE @device_directory NVARCHAR(520)
    SELECT @device_directory = SUBSTRING(phyname, 1,
    CHARINDEX(N'master.mdf', LOWER(phyname)) - 1)
    FROM master.dbo.sysdevices
    WHERE (name = N'master')
    EXECUTE (N'
    CREATE DATABASE [DatabaseName]
    ON PRIMARY
    (
    NAME = N''[DatabaseName]'',
    FILENAME = N''' + @device_directory + N'[DatabaseName].mdf''
    )
    LOG ON
    (
    NAME = N''[DatabaseName]_log'',
    FILENAME = N''' + @device_directory + N'[DatabaseName].ldf''
    )
    COLLATE SQL_Latin1_General_CP1_CI_AS
    '
    )
    Go

    Figure: Bad Example - FILENAME Parameter used to specify database path

    ​CREATE DATABASE [DatabaseName]
    COLLATE SQL_Latin1_General_CP1_CI_AS
    Go

    Figure: Good Example - Generic CREATE DATABASE used​​
  24. DBAs - Do you know the compatibility issues between SQL Server 2000 and 2005?

    The SQL 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.​​

    IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ProcessTarget]') AND type in (N'P', N'PC'))
    drop procedure [dbo].[ProcessTarget]

    Figure: script only works on SQL 2005, because 'sys.objects' is only available in this version

    IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[ProcessTarget]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
    drop procedure [dbo].[ProcessTarget]

    Figure: script works on both SQL 2000 and SQL 2005​
  25. DBAs - Do you have a general Contact Detail table?

    It is common to have a Contact Detail table to store your contact information such as phone numbers. Below is an example of a Contact Detail table and its related tables. This is bad because the PartyPhone table is too specific for a phone number and you have to add a new table to save an email or other contact information if this is needed in the future.​

    ContactDetailTable_bad.png
    Figure: Bad Example - A too specific Contact Detail table

    We normally have a general Contact Detail table that includes all the different categories of phone numbers, whether it is shared or primary plus emails all in the same table.

    ContactDetailTable_good.png
    Figure: Good Example - A general Contact Detail table

    We use a Contact Detail Category table to store these categories.

    ContactDetailCategoryTable.png
    Figure: Good Example - Details of Contact Detail Category table
  26. DBAs - Do you know how to provide best database schema document?

    ​​​​​​​​You should not provide a database schema via several screen captures - it has little information about the details. A well-formatted Word document may be providing more details information, but it is not easy to maintain the document to keep it up-to-date. The best way is to automatically generate your document with a tool.

    We recommend and use Red-Gate SQL Doc to produce chm help files or html pages of the database schema. SQL Doc also allows you to run via the command line so you can include the generation in your build process to be automatically created.

    We have also have used other available tools in the past, such as Apex SQL Doc.​

    Alternatively, you can use SQL Management Studio to generate a Database diagram.
    1. Connect to your database using SQL Server Management Studio
    2. Create a new Database Diagram, by right-clicking Database Diagrams

    Figure: New Database Diagram

    3. A popup will appear. Shift-Click to select all the tables then click Add

    Figure: Selecting tables for diagram

    4. You will see tables populate behind the dialogue box, once complete click Close

    Figure: Tables populated

    5. Click off the tables in the diagram and Ctrl+A to Select all
    6. Right-Click one of the tables in the diagram and perform the following

    a. ​Select Table View | Standard from the menu

    b. ​​Select Autosize Selected Tables from the menu 

    Figure: Changing the database table diagram to Standard View and Autosize​

    7. Right-click the diagram background and select Show Relationship Labels

    Figure: Show Relationship Labels

    8. Move the tables around so that the Relationship Labels are clearly visible.

    Note: You will need to screenshot the diagram as using the copy to clipboard function removes the “Allow Nulls” checkmarks.

    Figure: Northwind Database Schema





  27. Performance Tuning - Do you make sure to clear SQL server cache when performing benchmark tests?

    When you are tuning SQL statements you tend to play in SQL management studio for a while. During this time SQL caches your query's and execution plans.
    All well and good but when you are trying to speed up an existing query that is taking some time then you may not be making a difference even though your execution times are way down.

    You really need to clear SQL's cache (or buffer) every time you test the speed of a query. This prevents the data and/or execution plans from being cached, thus corrupting the next test.

    To clear SQL Server's cache, run DBCC DROPCLEANBUFFERS, which clears all data from the cache. Then run DBCC FREEPROCCACHE, which clears the stored procedure cache.​

    ​​ClearSQLServerCache_BenchmarkTests.jpeg
    Figure: First call is after clearing the cache. The second one is without clearing the cache. (26 seconds vs 2 seconds)​