icrosoft SQL Server is a great database for Web sites, especially Web sites deployed on NT and using Microsoft’s IIS server. It’s easy to install, relatively cheap, and interacts well with IIS. In addition, having all your tools from one vendor helps simplify obtaining support.

Although SQL Server is easy to use, it is also a sophisticated product that has requires maintenance and tuning to obtain the most from it. The trick in using SQL Server properly is to recognize these requirements and plan for them from the start of the project and not wait till issues arise in production. Here’s a checklist of items to cover to ensure that your project runs as smoothly as possible.

1. Check your Hardware and Software Configuration
As a “techie” I’m as enamoured by hardware as anyone. However, when defining the hardware requirements for SQL Server it is important to weigh where most performance gains can be realized. In many cases, too much emphasis is placed on having multiple CPUs and large amounts of memory without paying enough attention to the configuration of the disks. In general, the more disks and controllers you have, the faster your I/O will be. For a good view of how to increase hardware performance, take a look at the Microsoft SQL Web Site under TPC benchmarks. You can find diagrams of the systems used to produce the benchmarks. Some very respectable numbers were produced on systems with just one CPU.

When SQL Server is installed, it uses a default configuration that is suitable for the most minimal requirements. As an example, only sixteen megabytes of ram is allocated to it. The TEMPDB database is set to just 2 megabytes. Have someone knowledgeable look over your configuration and set it up according to your environment.

2. Create a Backup and Recovery Plan
Database backups are not done like file backups. There are specific commands that need to be executed that create a backup of the database. Make sure that you have someone responsible for running the backup and testing it periodically to ensure that it can be restored. This sounds obvious but I’ve seen it overlooked too many times. One client I worked with was using a very large service provider to host his site. He insisted that they were backing up his database for him. As it turned out, they had never done so and left all the database administration tasks to their clients. You should also be aware that under version 6.5 (seven will change this) performance on your database drops significantly while a database dump is being performed. Plan on scheduling it for your least busy hours.

It is important to have a written plan that will cover all contingencies. How will spare hardware be obtained in the event of a crash? Who will be responsible for keeping track of the backup tapes? Creating a plan before a crisis occurs will allow you to deal with the problem in a calm and efficient manner.

3. Create a Schedule for Maintenance
SQL Server needs several maintenance commands to be executed periodically. These commands are vital both for performance and to ensure that the database has not suffered any corruption.

For example, the command UPDATE STATISTICS tells SQL Server to sample the data in your tables. It uses this information to decide which indexes to use during the execution of SQL statements. The schedule for running this command depends on how much data is being inserted or updated in your system. If this command is not executed periodically, performance will begin to drop dramatically.

DBCC (database consistency check command) commands serve the same purpose as chkdsk does on a hard drive. It performs an internal check to make sure that the database is not corrupt. It doesn’t have to be run as often as update statistics. However, it should be executed at least once a month. Depending on the size of the database it can take several hours to execute. In addition some of them require no updates to be happening at the time.

4. Design Your Schema Correctly
Web sites seem to change constantly. Just try to find the same item at the Microsoft site twice in a row. However, a database schema can not be changed as easily as a Web page. You also can’t design a schema on the fly. A successful design process includes clearly defining what the information to be stored means and how it is to be used. A well-designed schema will be easy to work with and maintain. A hasty and ill-designed schema will be difficult or impossible to work with and probably will not be able to support the functional requirements.

5. A Word on Power Searches
A frequent feature on Web sites is the ability to do a power search of the data stored on that site. Just remember that the more “powerful” a search–for example, the more tables and rows that the query touches–the slower the response time will be. A query that touches every table in your database and every row will be slow no matter how well you’ve written your code. Try to limit queries to what is actually useful.You can also divide a power search into several smaller and less all-encompassing searches.

6. Make Sure You Use the Proper Indexes
Proper indexing is critical to performance. Two decisions must be made when deciding on indexes for a table: which columns to index, and which index should be clustered. A clustered index essentially orders the data in the table according to the index. Queries that use a clustered index can get a terrific performance boost. However, by definition, you can only have one clustered index per table.

7. Secure Your Server
Don’t, and I say again, don’t have your scripts execute under the sa id. The sa id has total control over the SQL Server environment. Instead, you should create an id that has the minimal rights necessary to get the job done and use that id for your scripts. Also remember to change the sa password immediately after installation (upon installation it has no password).

8. Obtain the Services of a Database Administrator
Even after the system is in production it is a good idea to have someone monitor performance, check the results of the maintenance routines and backups, and in general ensure that every thing is running smoothly. This does not mean that you have to hire someone full time for your system. Your host provider might be willing to provide these services for a fee. However, you want to make sure you have someone on hand before you have a sudden need for him.