My recipe for a basic SQL install

Posted on by

When I install SQL server on a fresh install, I chose to select different locations (drives) for the Data vs. the Log directories. Even in a virtualized environment this can be beneficial. If (for instance) you want to move the logs to a higher performance storage array, if the logs exist in a different VMDK than the data, the migration is a simple storage VMotion.

I always create 3 drives;

  • I: Drive (Install) Small
  • S: Drive (SQL Data) Large
  • L: Drive (SQL Logs) Depends on your application/db config

In the install wizard, I take the defaults for most. I only change a few options.

During the first instance install (at the feature selection screen), I select the I: drive for both the Shared Feature directories. Each additional instance these fields will be grey.

In the instance configuration screen, for the default instance I set the server name as the instance ID. I also select the I: drive (installs drive) for the Instance root directory (I:\Program Files\Microsoft SQL Server\).  The instance root directory is where SQL installer will place the binaries. Any additional instance I create I type the name of the server plus the instance name as the Instance ID. So, if you server name was SQLSERVER1, and your instance name was SQL1, the Instance ID would be SQLSERVER1SQL1.

I always select Automatic Start on the SQL Server Agent in the Server Configuration Screen.

In the Database Engine Configuration screen, I always select Domain Admins as server administrators. You can select anything you want here. These users will have SA access. I always select Mixed mode installations. I never know when I will need SQL authentication. Set the SA password to something strong!

The next tab over in the Engine Configuration screen is called Data Directories. I always select the following (don’t copy and paste here, these directories differ based upon your configuration);

  • Data root directory: S:\Program Files\Microsoft SQL Server\
  • User database directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • User database log directory: L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Temp DB directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Temp DB log directory: L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data
  • Backup Directory: S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Backup

Again, this assumes your server name is SQLSERVER1, and your instance name is SQL1

Next, next, next…

After the installation completes you are ready to move the system databases. There is probably 1000 ways to do this, some may be faster. I do it the same way every time and it works for me. Your current layout should be as follows;

  • Master DB: S Drive
  • Master Log: S: Drive
  • Model DB: S Drive
  • Model Log: S Drive
  • MSDB DB: S Drive
  • MSDB Log: S Drive
  • TempDB DB: S Drive
  • TempDB Log: L Drive
  • Binaries: I Drive

Here is how I move the rest of the system database log files to the L drive.

**Important**
Make sure that “NT SERVICE\MSSQL$XXX” (replace XXX with the SQL instance name — use “NT SERVICE\MSSQLSERVER” for the default instance) has access to the log folder before you move the logs!!
 

Launch the SQL server Configuration manager, and then select SQL Server Services. Find the SQL server instance you just installed and double click it (It should be labeled “SQL Server(Instance Name)”, so for the sample I have been using, it would be “SQL Server (MSSQLSERVER)” for the default instance, or “SQL Server (SQL1)” for the SQL1 instance). Select the Startup Parameters tab, and locate the line that starts with;

-lS:\Program Files\Microsoft SQL Server..

Change it to read;

-lL:\Program Files\ Microsoft SQL Server...

Obviously I trancated there, but you get the point. Click “Update”. That parameter “-l” (dash L) tells SQL server where to find the Master DB log LDF file. We want to point it to the L drive.

Once you set that, right click the instance and select stop. Let it stop the agent too. Once its stopped, move the file (you might need to enable file extensions in Windows explorer);

S:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\ mastlog.ldf

To;

 L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\Data

Start the instance back up (you can leave the agent down). Verify (through SQL Management Studio) by getting the properties on the db, and clicking the files tab and looking at the Path column.

For the Model, and MSDB, you have to run some SQL. The procedure is posted on this MSDN article.
http://msdn.microsoft.com/en-us/library/ms345408%28v=sql.90%29.aspx
They recommend to use the TSQL query;

ALTER DATABASE database_name MODIFY FILE (NAME = logical_name , FILENAME = 'new_path\os_file_name')

So, for our instance;

Use Master
ALTER DATABASE Model MODIFY FILE (NAME = modellog , FILENAME = 'L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\modellog.ldf')
ALTER DATABASE msdb MODIFY FILE (NAME = MSDBLog , FILENAME = 'L:\Program Files\Microsoft SQL Server\ MSSQL11. SQLSERVER1SQL1\MSSQL\DATA\MSDBLog.ldf')

Execute that in SQL Studio, then back in configuration manager, stop the SQL instance. Move the log files (modellog.ldf, and MSDBLog.ldf) from;

S:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

To;

L:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

Now you can safely start SQL server and SQL agent. Again, verify your work by checking the properties on each database. While you are in there, change autogrowth file size to 10 percent on every database. Some DBA’s would explode into a ball of fire if they read that… It’s just my preference…

The lat thing I always do is change the default locations for Data, Logs and Backups. Right click on the server in SSMS (SQL Server Management Studio) and click Properties. Then click on “Database Settings”. Set the default location for Data to be;

S:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\DATA

And set the default for the logs to be;

L:\Program Files\Microsoft SQL Server\MSSQL11.SRVHDQDB2SDE\MSSQL\Data

Congratulations, you now have a fresh installation of SQL!

Cris.