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
Want to develop your SQL Server Database with SSW? Check SSW's Databases Consulting page.
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:
- System Center Operations Manager and SQL Server on the network when performing a network scan
- Microsoft System Center Management Pack for SQL Server
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
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:
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.
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.
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.
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!
SQL Server includes Database Mail (it was a new feature released back in 2005 as a replacement for SQL Mail). Database Mail is a great feature as it allows:
- HTML messages natively supported - so there's no need to use 3rd party dlls anymore
- Communication direct with SMTP server - There's no need for outlook or MAPI profiles on server
- Multiple profiles and accounts 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
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.
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.
- DBAs - Do you measure Up-Time?
- DBAs - Do you check your SQL Server is up-to-date?
- DBAs - Do you design for database change?
- DBAs - Do you script out all changes?
- DBAs - Do you configure all your SQL Server Services to use a Domain Account rather than a local service account?
- DBAs - Do you name your SQL Server Domain Account as 'SQLServerMachineName' so it is easily identified when performing network operations?
- DBAs - Do you run SQL Server Services under non-Administrator accounts?
- DBAs - Do you use Database Mail (not SQL Mail)?
- DBAs - Do you turn on all the default alerts?
- DBAs - Do you create your own alerts?
- Backup - Do you set up a complete Maintenance Plan?
- Backup - Do you back up scripts?
- Backup - Do you take Restoration seriously?
- Backup - Do you have a Restoration Standard?
- DBAs - Do you know all the log files?
- DBAs - Do you secure your server by changing the 'defaults'?
- DBAs - Do you turn on security auditing?
- DBAs - Do you increase the Log Size of your Event Viewer?
- DBAs - Do you remove unnecessary permissions on databases?
- DBAs - Do you use Performance Alerts?
- DBAs - Do you make sure you use a consistent Collation server-wide?
- DBAs - Do you avoid collation errors?
- DBAs - Do you create new databases in the default data directory?
- DBAs - Do you know the compatibility issues between SQL Server 2000 and 2005?
- Performance Tuning - Do you make sure to clear SQL server cache when performing benchmark tests?