SQL Server Setup Checklist Part 1: Before The Install

Over the last few years, I’ve put together a list of best practices for installing Microsoft SQL Server 2005 and 2008. Other folks do more, but for small businesses, this is a good start.

I don’t show the exact point-and-click steps because my target audience (you smart folks) aren’t complete newbies. I know you folks understand HOW to do most of these things, but you take shortcuts because you don’t understand WHY you’re doing these things. There are plenty of places on the web where you can find examples of how to execute these steps (if not, email me and I’ll post ‘em) – instead, this series of articles will explain the reasoning behind the gruntwork.

Create at least one new Active Directory account for this SQL Server.

This is best practices, and a popular shortcut is to run all of the database servers under the same service account. I’ve run into two problems with this approach. First, I’ve had a sysadmin repeatedly fat-finger the service account password, thereby locking out the service account. When all of the SQL Servers suddenly run into service account problems, that’s a really ugly outage.

Second, there have been times when I’ve needed to give permissions to a specific SQL Server that other servers shouldn’t get. If xp_cmdshell is enabled, then DOS commands will be run with the SQL Server’s service account permissions. I hate giving any more permissions than necessary, so if a user needs to write to remote locations using xp_cmdshell, it’s nice to lock down the permissions on that one database server’s account instead of enabling every SQL Server to write to that remote location.

I say “at least one new account” because best practices dictates using separate accounts for the database service, fulltext service, agent, etc. I’ll slack a little there and share the same account sometimes, especially on development boxes.

Configure local security settings for the SQL Server 2005/2008 service account.

Go into Local Security Settings (click Start, Run, type SECPOL.MSC and hit enter) and click Local Policies, User Rights Assignment.

In the permission “Perform Volume Maintenance Tasks”, add your SQL Server service account (or a domain group if you use that). This enables SQL 2005′s Instant File Initialization, which lets SQL Server instantly grow data files without erasing the physical disk first. This is a huge performance gain for large databases like data warehouses.

In the permission “Lock pages in memory”, add your SQL Server service account or domain group. This lets SQL Server keep all of its data in physical memory instead of swapping it out to disk.

If the SQL Server uses SAN storage….

Update the HBA firmware. Downlevel HBA firmware has caused me all kinds of nasty problems, especially in clustered servers. Generally, these updates can’t be done online while the server accesses data, so it’s better to get the code up to date before the box goes into production. For HP servers, this firmware isn’t shown in the System Homepage: install Emulex HBAnyware on the server instead, and it will flash the HBAs inside of Windows without a reboot. HBAnyware is available in the HP Support site by searching for downloads for the HBA’s part number instead of the server’s part number. This is the only driver/firmware at HP that works this way.

Set up multipathing drivers. Sometimes this is done by the storage team, but the DBA should get involved enough to understand whether the multipathing is active/active or just failover.

Test the multipathing & failover. Start a huge file copy to each array, and do them all simultaneously. Go into the datacenter and pull one fiber cable out. Watch to make sure the file copy continues. Some SAN drivers will take 10-15 seconds to fail over, but the file copies should not be disrupted, period. If they’re disrupted, the multipathing didn’t work. Then plug the cable back in, and pull another cable. Again, the file copy should continue. Finally, while a file copy is running, ask the SAN admin to disable one of the SAN zones for the server – that way, the fiber cable will still be lit up, but the path to the storage will be gone. (This is a tougher failover method than just pulling the fiber.)

If the SQL Server uses iSCSI storage….

Set up teaming software. Database servers can’t rely on one single network connection for iSCSI any more than a fiber-connected SAN can rely on one single host bus adapter. Ideally, the two network cards will be connected to two different switches for redundancy, but at the very least, we need two network cards. The teaming method can be active/active (meaning 2 gigs of throughput for two teamed 1 gig nics) or active/passive.

Test the multipathing. In my experience, I’ve usually seen active/passive on a per-array basis – meaning, if you have two different iSCSI drive letters, then the teaming or multipathing drivers will put each drive on its own network card. The EMC and LeftHand multipathing appears to do this by default. Start multiple simultaneous drive copies and go into Task Manager, in the Network tab. Look at the bandwidth used by each network card. If a network card is sitting idle, then you’re leaving performance on the table. Now is the time to tweak the multipathing software and ask questions of the vendor – it’s easier to troubleshoot file copy performance than it is to troubleshoot SQL Server performance.

Test the failover. As with the fiber cable testing, start multiple simultaneous file copies to/from the network drives and pull one network cable out. If the file copy fails (if Windows throws an error) then SQL would have crashed. Tweak the teaming software until it can fail over seamlessly, and ideally it should fail over back and forth and go back to higher bandwidth levels as the networks come back online.

Use DISKPART to create aligned partitions.

If you’re using Windows 2008 or newer, you can skip this step.  Microsoft KB article 929491 covers some of this in technical detail, but the Microsoft Exchange Team blog does a much better job of explaining why disk partition alignment is important for performance. Use this command in Diskpart.exe:

CREATE PARTITION PRIMARY ALIGN=1024

The 1024 number will work with every major SAN out there. Gurus can use smaller numbers when they know a specific SAN very well, but small numbers only save you a few hundred kilobytes in the entire disk. Be safe, be sure, and use 1024, and you won’t get burned if the underlying SAN structure changes, like with today’s virtual storage.

Install the Windows components for SNMP and WMI.

These management tools can be found in Add/Remove Programs, Add/Remove Windows Components, Management. They need to be installed before the SQL Server install starts because SQL will only create the monitoring mechanisms if SNMP & WMI are already installed.

For 32-bit servers, configure AWE and PAE.

One of my favorite sites, SQL-Server-Performance.com, has the most straightforward explanation of how to configure these obscure settings in boot.ini:

• 4GB RAM: /3GB (AWE support is not used)
• 8GB RAM: /3GB /PAE
• 16GB RAM: /3GB /PAE
• 16GB + RAM: /PAE

You can read more about AWE and PAE at SQL-Server-Performance, who does a better job of explaining this than I ever could.

Set the page file on the C drive to 2GB.

When a SQL Server is configured correctly, it doesn’t use the page file for memory.  We’ll configure it correctly in a later step.  In theory, we don’t need a page file at all, but there’s a catch.

For advanced troubleshooting, there has to be a page file on the C drive big enough to do a minidump. This isn’t a strict requirement, but if SQL Server ever crashes hard, that dump file will be invaluable. I have actually had this happen, and I’ve even been able to reproduce the crash on several different servers – we even did it at the Microsoft Technology Center in Chicago. It was some stunningly bad code coupled with an ugly database, and I got a lot of laughs out of that. Anyway, the point was we needed a dump to troubleshoot it.

Configure antivirus software to skip LDF/MDF/NDF files.

SQL Server stores its data in files with LDF, MDF and NDF extensions.  These files should not be scanned by antivirus software.  Depending on your antivirus configuration, you may need to exclude these files network-wide at the central antivirus management server.

This isn’t just a performance issue; it’s an outage issue.  I’ve seen cases at boot-up time when the antivirus software grabbed a lock on a particular ldf/mdf/ndf file for virus scanning before the SQL Server service started up.  The database was not attached since SQL Server couldn’t open the file, and due to the huge file size, the antivirus software spent several minutes before releasing the file.  During a server startup, this type of thing isn’t obvious to the sysadmin – they just see a problem with a database and go into panic mode.  Avoid panic: fix the antivirus before installing SQL Server.

Time to Start the Install

But you can’t just run the setup.exe and go into production – oh no. In the next article, I’ll cover my non-default installation settings and the things I do after the install to make the server as reliable as possible for the long haul.

The Next Steps: After The Install.