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:
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:
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:
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)
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:
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:
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.
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:
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.
Restoration is vital, we all agree. But rapid restoration with minimal downtime 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.
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.
SQL Server stores vital error and performance information in several different logs. You should be aware of all of them:
Configure login security auditing:
Change the defaults from 20480KB 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.
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.
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: