SQL Server 2005 Setup Checklist Part 2: After The Install

I’ve covered what needs to happen before you install SQL Server – now, let’s talk about what to do immediately after the setup finishes.

Install not just the service packs, but also the cumulative updates.

Starting with SQL Server 2005’s Service Pack 2, Microsoft releases hotfixes in cumulative packs. These updates do more than just fix bugs: they improve how SQL Server performs. These updates are free performance benefits – and who doesn’t like that?

To find the latest service packs and cumulative updates, check out the SQL Server Release Date Calendar at SQLServerPedia.  It’s got version numbers, build numbers, and download links for all versions of SQL Server in one place.

Double-check that Instant File Initialization is enabled.

Paul Randal wrote an excellent blog post on how to tell if instant initialization is enabled.  Follow the instructions in his post, and you’ll know for sure.  (While you’re there, subscribe to his blog – it’s chock full of SQL-y goodness.)

Best Practice: Move TempDB to its own drive.

By default, the TempDB files are put on the same drive as the SQL Server binaries. Even if the user chooses a custom install, TempDB still goes on the same drive as the other data files, and that’s not a good idea either. Instead, the TempDB data files should be on their own dedicated drive.

Fix this by first moving TempDB to its own drive.  In this example, I put the data file on the T drive and the log file on the L drive.  (Be aware that the directory paths must already exist.)

use master
go
alter database tempdb modify file (name=’tempdev’, filename=’T:\MSSQL\DATA\tempDB.MDF’, size = 1mb)
go
alter database tempdb modify file (name=’templog’, filename=’L:\MSSQL\LOGS\templog.LDF’, size = 1mb)
go

I only set a 1mb file size because SQL Server does something tricky: even though we’re telling it to use a different drive letter, it will look for this amount of free space on the drive TempDB currently uses! If SQL Server was installed on the server’s C drive, for example, and we try to create a 10gb TempDB file on a T: drive, that SQL command will fail if there isn’t 10gb of free space on the C drive. Yep, it’s a bug – get over it.

After this code runs, restart the SQL Server. That will create the new TempDB file on the new drive. Manually delete the old TempDB file on the original drive, because SQL Server doesn’t delete that itself.

Now that TempDB is on the right drive, expand it to the full size you want, and then create one additional TempDB data file per core. Here’s the code to create one additional TempDB data file – you can modify this for more files:

USE [master]
GO
ALTER DATABASE [tempdb] ADD FILE ( NAME = N’tempdev2′, FILENAME = N’T:\MSSQL\DATA\tempdev2.ndf’ , SIZE = 10GB , FILEGROWTH = 0)
GO

The data file creation should only take a couple of seconds – if it takes more than ten seconds, then instant file initialization isn’t configured correctly. We talked about this back in the pre-installation checklist, so go back and revisit that before you create the next TempDB file. Fix the security to allow for instant file initialization now – it has a huge performance impact on database growth.

Assuming that one file growth only took a couple of seconds, then go ahead and create the rest of the TempDB data files.

Notice that I don’t have filegrowth enabled.  You want to proactively create the TempDB files at their full sizes to avoid drive fragmentation.  If you have a dual-cpu quad-core server (8 cores total) and an 80-gb array for TempDB data, you would create eight 10gb files for TempDB.  That way, each file is contiguous, all laid out in one big chunk.  If you create them as smaller files and let them autogrow, then the disk will be fragmented all over the place because the files will be growing at random times.  Plus, you could end up with differently sized TempDB files if one of them happened to grow faster than the rest.  That’s why I pre-grow all of the TempDB files ahead of time and get them at exactly the right size.

Configure SQL Server memory for best practices.

Sounds easy, right? Go into SQL Server Management Studio, right-click on the server name and click Properties, go into Memory, and just configure it. There’s only a couple of fields – how hard could it be?

Oh, this screen is full of danger and pitfalls.

First, that tricky checkbox that says “Enable AWE”. Check that box if you’re using a 32-bit server with more than 4 gigs of memory.

Second, the minimum and maximum memory amounts are important, especially since we gave the SQL Server account the permission to lock its pages in memory. If other applications are running on this server, we need to specify how much memory we want SQL Server to take.

Ideally, no one would ever remote desktop into a SQL Server and run programs. Unfortunately, this happens, and we have to plan for it by leaving enough free memory for people to run things like SQL Server Management Studio. When I’m first building a server that isn’t running any other applications at all, I like to leave 10% of the memory free, or 4gb, whichever is larger. Then I monitor the free memory over the course of a month or two, and adjust it up or down during the next outage window.

If the server does multiple duties like act as a web server or application server, we have to be much more conservative with memory. Application owners never seem to know how much memory they’ll really use in production: SAP BW’s Netweaver, for example, tends to use anywhere from 10% to 50% of the memory on our production server, and it’s tough to predict. As a result, we have to leave the SQL Server’s memory allocation at just 50% of the available memory on the server.

The only way to know the right answer long term is to use Perfmon or a performance monitoring utility to watch the server’s free memory. I’ve written up a separate blog post on using Perfmon for SQL Server monitoring.

Set the Default Database Path

Even if you chose this during setup, we need to revisit it because SQL Server puts both the data files and the log files in the same directory. In SSMS, right-click on the server name and click Database Settings. The paths for the data files and log files can be configured from there.

Of course, this assumes that we have separate drives for the data and log files, which is the right way to go for performance purposes.

Tweak the model database.

This tip comes courtesy of reader John Langston.  Whenever a new database is created, SQL Server uses the “model” database as – well, as the model.  You can make changes to that database, and those changes will automatically happen to any new databases.  John writes:

I also like to go to model and change the recovery model from FULL since we use SIMPLE a lot, even in production and also change the datafile autogrowth setting from 1 MB.

Great tip!

Configure Database Mail with public & private profiles.

Database Mail is a pure SMTP solution that beats the daylights out of SQL 2000’s SQLmail. It doesn’t require Outlook to be installed on the database server, doesn’t need any MAPI code, and works with any company email server that can be accessed via SMTP.

There’s plenty of sites on the web that explain how to configure Database Mail, but I want to address something: be aware that developers can use Database Mail for things that SQL Server shouldn’t be doing. For example, they may decide to use Database Mail to send out mass emails to your end users or customers. There’s nothing technically wrong with that, but it increases the load on the database server and it sends all outgoing email with the SQL Server’s Database Mail account.

At our shops, we use internal emails like (servername)@ourcompany.com to identify which server is sending the database mail. Those email addresses make sense to us because we just need to know where the alerts are coming from – we would never hit Reply to a server-generated email.

However, if developers use SQL Server to send out emails directly to customers, those customers will indeed reply. I had a nasty problem where a couple of developers decided to purge old customer accounts, and they used SQL Server’s Database Mail to broadcast an announcement to those users. The email read something like, “You haven’t used your account in 30 days, so we’re deleting it. Please contact us for questions.” Of course a lot of customers got aggravated and sent some nastygram replies, which arrived in the IT team’s inboxes, who had no idea what was going on. After some confusion, we were able to track down the guilty party, but those emails never should have gone out from the IT staff.

Bottom line: if you decide to use Database Mail (and you should), consider setting up separate private and public email profiles. The public email profile used by the developers should be sent from the developer management team’s group email address – that way, they can address any replies themselves.

Configure SQL Server Agent’s failsafe operator.

After configuring Database Mail, create at least one SQL Server Agent operator. This operator’s email address should be a distribution list for the database administrator group. Even if the company only has one DBA, never use an individual person’s email address – use a distribution list instead. When the DBA goes on vacation or gets a job at another company (or heaven forbid, gets fired), it’s easier to add someone to a single distribution list instead of modifying operators on dozens or hundreds of servers.

Then right-click on the SQL Server Agent, configure the alerting system to use Database Mail, and set up that DBA group as the failsafe operator. That way if anything happens and SQL Server doesn’t know who to alert, it can alert the group.

Create default alerts for severities 16 through 25.

SQL Server’s alerting system has the ability to notify operators whenever major things break inside the database. These include running out of space in log files, backup failures, failed logins and other things DBAs just need to be aware of. Don’t rely on this as your only SQL Server monitoring system, because it only sends alerts when it’s too late to take proactive action, but still, it’s better than nothing.

The below script will set up an alert for severity 16. Copy this and repeat the same thing for 17-25, but change ‘Database Team’ to be the name of your default operator. Notice that @delay_between_responses is set to 60 – that means if it sends out an alert, it won’t repeat that same alert for 60 seconds. This is useful because when a database runs out of drive space, for example, all hell will break loose for a minute or two, and you don’t want hundreds of emails and pages per minute.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’Database Team’, @notification_method = 7
GO

Install the SQL Server 2005 Performance Dashboard Reports.

These are an insanely cool and free extension for SQL Server Management Studio.

• Download the SQL Server 2005 Performance Dashboard Reports
• Read about them on SQL-Server-Performance

You run the setup.exe on your personal workstation, and then you have to execute the setup.sql script on each server that you want to monitor. It only takes a few minutes, but the information that it gathers will help you manage your server better throughout its lifetime.

There are better performance tools out there – I’m a fan of Quest Spotlight – but if you’re just getting started, it’s hard to beat the free Performance Dashboard utility. SQL Server 2008 includes a new performance management data warehouse tool. I saw a preview of it at PASS 2007, and it’ll be something to watch for.

Set Up Maintenance Plans

This is where things start to get different on a shop-by-shop basis. Some places use native backups, some places use backup compression software, some places use index defrag software, and so on. I’ve written a lot of articles about my personal backup best practices, and one about why SQL native backups suck. (Hey, if I was politically correct, I’d be writing manuals instead of reviews.)

Benchmark It, and Find the Slowest Link

Before it goes into production, load test and stress test it to find where you’ll need to improve performance down the road.  Before it goes live, this is your one chance to really bang the daylights out of it without anybody complaining.

To help, I recommend the book Professional SQL Server 2005 Performance Tuning from WROX.  It does a great job of covering all the different performance areas – storage, memory, CPU, network – and showing you how to detect bottlenecks and remove them.

Want Performance Tips?  Check Out My Articles.

I’m sure my readers have plenty of opinions about what should be done right away after setup.  If you’ve got one, feel free to leave a comment here or contact me.