MS SQL Server

Quick Setup Guide

Well, of course you can just insert an MS SQL Server setup disk in the CD drive of your server and click "OK" and "Next" a few times. No doubt you'll end up with a working MS SQL Server. However, if you're planning on running multiple databases, or need to provide databases for multiple applications from different vendors, or you care about backups and recoverability of your databases, or you don't like software that dumps all of its executables, logs, backups and data in c:\Program Files\, then you might want to do some minimal customization during setup.
Been there, done that, took notes :

Partitions and Disks

It goes without saying that you do not want your data in Program Files. Ideally, you'll store the database files on a separate partition, away from the operating system and program files. On a decent server, you've already set this up, and probably have separate partitions for operating system, programs, and all the rest.

For a database server, you might consider using yet another partition / disk / LUN for backups and logs. If your data disk fails, you'll need the backup files AND the transaction logs to recover, so you don't want those on that same partition that just failed. A partition for backups allows for (fast) backup to disk; the backup files can be copied to tape later, eg as part of a daily file backup.

Having your rdbms using several disks for different operations might also increase disk I/O. For real database work, you'd also look in to optimizing the filesystem. Choosing a filesystem is not an option under Windows (NTFS, and no discussion, but for large, heavy load databases there might be benefit in adjusting the sector size, and other tweaks. We won't go there.

So, think something like

Windows or Domain User Account

By default, SQL Server will run under the local SYSTEM or Administrator Account, but an ordinary user account is sufficient so it is advisable to create a useraccount specifically for MS SQL server to run with. Give it a good strong password that never expires.

Setting up MS SQL Server

What follows has been tested with MS SQL Server 2000 SP3, but is probably generic enough to be applicable to other versions.

Choose for a custom install so you'd be prompted to change settings such as instance name and file locations

Instance Name

Choosing an instance name instead of accepting the default 'MSSQL' allows you to apply a naming convention should you, in the near or far future, need to setup additional instances to accommodate multiple unrelated applications, possibly from different vendors, each with their own specific requirements. So you could and up with multiple instances, such as

Note that the prefix MSSQL$ is added automatically to the instance name you provide. Even if you plan to start with only 1 instance, you might want to start a systematic naming scheme, eg with instance01. The names of the instances will be needed in database connection strings, and will be used in names of subdirectories where your files end up (see further), so it helps to keep things organized.

Service Account

Indicate that you want MS SQL to run under the dedicated user account you created for this purpose

set file locations

During setup, you can indicate separate paths for program files and data files.

Even when you indicate that the program files should be put in your custom directory (D:\...), some program files will still end up in C:\Program Files\Microsoft SQL Server, and C:\Program File\Common Files. You can probably avoid that by changing the registry settings for Program Files and Common Files, but that will affect any software that was installed earlier before this change.

Setup adds "MSSQL$name_of_instance" to both your data and program files path, and sets the filesystem ACL's for the service account user (Full Control) on the directories where the database files

database admin account

You can chose to use Windows authentication for the database administrator and thus implement a single-sign-on, but this has the downside that your databases may become completely inaccessible should the windows (or domain) authentication fail (like, your domain controller has crashed beyond repair and you need to migrate to a new domain). You can avoid that sort of headache by setting up an 'sa' account for database administration.

Creating databases

Databases are created through SQL Server : Enterprise Manager : instance_name : databases -> New database

You're prompted for a database name, and the wizard will suggest a file name and a file location based on the path you set during setup :

eg database name = dbGIS
default path = E:\Database\Microsoft_SQL_Server\MSSQL$INSTANCE01\Data\dbGIS_Data.mdf
* E:\Database\Microsoft_SQL_Server is the data file location defined during setup
* INSTANCE01 is the instance name defined during setup
* dbGIS is the database name defined during New Database creation

The location for "Transaction logs" defaults to a similar path (eg ...\MSSQL$INSTANCE01\Logs\dbGIS_Log.ldf) but it's advisable to keep transaction logs (and backups) in separate partitions or disks, so you'll have to change this by hand, i.e.
set path for transaction logs to

note that you need to create those directories in advance, and allow the mssql service account to traverse the patch and write to the destination, eg

once this is setup, you can reuse those directories for other databases Logs


Backups are configured in MSSQL Enterprise Manager : instance_name : Management Maintenance Plan : New Maintenance plan -> include backup in maintenance plan

Change the proposed backup path to one that matches your layout, like
+ create subdirectory for each database (checkbox)

The path F:\DB_Backup\MicrosofSQL_Server\MSSQL$INSTANCE01\Backup needs to exist, and you need to allow the service account adequate access. Refer to Transaction Log directories.

Maintenance plans can be scheduled, and backup to file can be copied to tape / other disks for offline backups. It is also possible to define a backup to tape (directly).

An alternative to backups via Maintenance Plans : in stead of a maintenance plan, specify a "Backup" in Enterprise Manager:Maintenance:Backup.
parameters and options are identical are similar to those in Maintenance Plan backups, but need to be configured separately.

Koen Noens
September 2008