12 Essential Steps After Installing SQL Server

Rolling out SQL Servers is a key task for DBAs.  While for some DBAs this can be a frequent exercise, others will have to face it one time or other throughout their career in an organisation.

Successfully planning an installation requires a fair bit of knowledge about SQL Server and a basic understanding of the operating system. Once the installation is complete, the system usually needs some changes to default configuration before production databases can be rolled out. In this article I list some common steps that I take after installing a SQL Server. This can be used as a “checklist” to ensure consistency for all the SQL Servers that you roll out.

Step1: Install the Service Pack, Hotfixes and Cumulative Updates

This is the first step you should take after a successful installation. You should always install the latest service pack available unless there is a clear business cause not to do so. At the time of this writing, SQL Server 2005 is in service pack level 3 and SQL Server 2008 has service pack 1. Service packs are freely downloadable from the Microsoft web site. If there are hotfixes or cumulative updates available after the last service pack has been released, you should apply them as well. This ensures that your server is ready with the latest version of the database software. Once all the patches are installed, make a backup of your system databases and reboot the server.

Step 2: Configure SQL Services

Although your planning phase should determine what SQL services you are going to install, there may be occasions when a service is rolled out but not needed immediately. Perhaps your business requires you to install a SQL Server and although reporting is not going to be part of it, management insists you install Reporting Services – “just in case”. If a service is not going to be needed immediately (or needed at all), disable it.

One example of a service that you can disable is the Browser service. This service is installed by default. If you are not running named instances or planning to install multiple instances in one machine, you can disable this service.  You can disable the Browser service from the SQL Server Configuration Manager.

Another service that you can disable is the SQL Server VSS Writer. Unless you are using applications that make use of the Windows Volume Shadow Copy infrastructure to backup SQL databases, you can disable this service safely. Note that SQL VSS Writer is not available from the Configuration Manager. You can access it from the Control Panel > Services applet (see below).

While non-essential services should be disabled, other services like the Agent are supposed to be running continuously. This is because scheduled backups, maintenance plans, replication and jobs are dependent on the Agent service. Similarly, if your server is going to execute Integration Service packages, ensure the SSIS service starts automatically every time the server boots up. You can configure the start-up property of a service from the SQL Server Configuration Manager.

One thing that is not available in the Configuration Manager is the recovery behaviour of SQL Services - i.e. what should the services do if they fail unexpectedly. To access this behaviour, you can bring up the service’s property from the Control Panel’s “Services” applet. The “Recovery” tab of the service property allows you to define what the computer should do in case the service fails once, twice or subsequent times. From here, you can specify that the service should start automatically if it fails.

The same type of behaviour can be set in the SQL Server Agent’s property page. Here, you can specify that SQL or Agent service should restart automatically if they stop unexpectedly.

Step 3: Configure Default Directories

Everything has its place in this world and when it comes to files and folders, SQL Server is no exception. A number of directories are created during installation - you have one directory for storing data and log files, another directory for replication, another for full text indexes, and yet another for backup files.

Where SQL places its files is determined mostly during the installation process. This happens because either you provide this information explicitly or SQL uses a default set of locations. The directory path information you provide during the setup should really be coming from your planning phase. Once the installation is complete, it is still possible to fine-tune some of these component locations.

If you have followed the best practice, your data files should be residing in a separate physical drive and directory.  The transaction log files of user databases should also be in a separate drive and folder. You can configure both the locations from the server’s properties:

Similar to the data and log directories, you can specify the default locations for:

a)    Backup files

b)    Replication files

c)    Full text index files

d)    Job output files

SQL Server creates the folders for these components in the same location where data files are placed. To ensure data files are given the maximum possible dedicated space as far as disk activity is concerned, I try to move these folders to a separate drive.

Unfortunately, you cannot use a GUI front-end to configure these alternate locations. The path information is saved as registry values and that means you either have to manually change registry entries or use an extended stored procedure like xp_regwrite.  Also, the location of the registry keys will be different between named instances. They will also vary depending on your version of SQL Server (2005 or 2008). Generally, you will be looking for a registry key called “Working Directory”.

The script below shows how I changed the default locations for a default instance of SQL Server 2005 installed in a Windows Server 2003 system:

Configuring_Default_Directories.sql

And of course, the paths you specify as registry key values must exist – that means you need to create (or move) the folders.

To ensure the system is aware of your changes, restart SQL services after making the changes.

Step 4: Configure Default Database Properties

We all know it – model database works as the “template” for new databases. If you want to set some common properties across all your user databases, you can set them in the model. A number of properties can be set here to take advantage of this approach:

a)    Recovery Model: perhaps you are installing SQL Server for a data warehouse that will host a number of data marts that would not require full recovery model. You can set the recovery model of the model database to “simple” mode in such cases.

b)    File Properties: by default, the data files are configured to grow by 1 MB and the log files by 10% of their size. This means that a large transaction log files will grow by a large proportion when it needs more space. For example, every time a 6 GB log file needs to grow, it will take up 600 MB of disk space in one gallop. For a write intensive database, the data file’s incremental growth by 1 MB can result in fragmentation. As a general rule of thumb, the log files should be quarter or half the size of the data files. If you have an idea about how big your data and log files are going to be and how they should grow, you can set up the properties in the model database. I generally set the growth increments in megabytes rather than leaving it as a percentage.

Another area where you can make use of the model database is filegroups. If you want your user tables to be created on secondary data files and leave the primary filegroups with system objects only, you can create secondary data files in the model database and assign them to a user defined filegroup and make that filegroup default.

c)    Database Options: depending on your requirements you can set up other database options in the model database. Examples of such options could be torn page detection, ANSI defaults etc.

d)    Custom Objects: if you have custom objects that need to be present in each user database, you can put them in the model database.