Episode III - Post - Install configuration

After installing SQL Server, its important to give the service account permissions to "Perform Volume Maintenance Tasks".  This allows databases to grow with zero initialization.  It doesn't work on log files, but it can shave minutes or even hours off of database expansion tasks!

To do so, open the local group policy editor on the server and navigate to Computer Configuration -->Windows Settings -->Security Settings-->Local Policies-->User Rights Assignment.  Then in the right hand side, double click on "Perform Volume Maintenance Tasks" and add the service account to the list. 

Then just restart the MSSQL service

Episode II - System Databases

After installing and service packing SQL Server (I'm using SQL 2005), I had to move the system databases off of their default disks.  Again, this is to make sure Windows will have as little disk I/O contention as possible.

For tempDB, the solution is pretty simple.  You can use ALTER DATABASE to move the database, then restart the SQL services.  The code to move it is simple:

USE master;
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = tempdev, FILENAME = 'E:\SQLData\tempdb.mdf');
GO
ALTER DATABASE tempdb 
MODIFY FILE (NAME = templog, FILENAME = 'F:\SQLLog\templog.ldf');
GO 

After the SQL services are restarted, tempDB will be in its new home.

Moving master is a bit trickier.  First, open the SQL Server Configuration Manager (Start-->All Programs-->Microsoft SQL Server 2005-->Configuration Tools-->SQL Server Configuration Manager)

Highlight the SQL Server 2005 Services node on the left pane, then double click the SQL Server (MSSQLSERVER) item in the right pane.  

Scroll down to the Startup Parameters section and modify the switches as needed














Now stop the SQL services and move the master.mdf and mastlog.ldf files to the locations specified above.

At the command line, start the SQL service in recovery mode with NET START MSSQLSERVER /f /T3608.

In SQL studio, execute the following query, making sure to use the correct path for the new locations of the master database files

ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=data, FILENAME= 'new_path_of_master\mssqlsystemresource.mdf');
GO
ALTER DATABASE mssqlsystemresource 
    MODIFY FILE (NAME=log, FILENAME= 'new_path_of_master\mssqlsystemresource.ldf');
GO

Now move the mssqlsystemresource.mdf and mssqlsystemresource.ldf files to their new homes, set the Resource database to read only ALTER DATABASE mssqlsystemresource SET READ_ONLY;

Finally, restart the MSSQL service and verify that the files were moved where they should be.

Hardware Setup - Step 1 - Disks

In any SQL Server installation, the main bottleneck is going to be disk I/O.  If the hard drives are not configured correctly, there will be contention for disk I/O.  If indexes are not created and maintained appropriately, there will be more disk I/O than is necessary.  If queries are not written properly, rather than being cached, they'll have to be performed on disk. 

In order to reduce contention for disk I/O, it is ideal to segregate the major functions on a SQL server.  This must be done at the controller level.  Windows will handle I/O to separate disks much more efficiently than it will handle I/O to partitions on the same disk. 

In my new server, there are a total of 17 hard disks.  6 of them are 15K RPM drives at 146 GB each.  8 of them are 10K RPM drives at 300GB each, and the remaining 3 are behemoth 1TB drives, but spin at only 7.2K RPM.  They are configured like this:





By segregating the different components in this way, I/O will be optimized.  Windows can read and write to the page file on Disk 0, can read and write to the system databases on Disk 1, can read and write the transaction logs on Disk 2, can read and write the databases on Disk 3, and finally can write the backups to Disk 4 all simultaneously.

Episode I

Hello and welcome to my humble SQL Server blog.  I'm building my n'th SQL server from the ground up, and will be documenting the process here, as well as explaining the configuration decisions I've made throughout.