SQL Server 2005 Setup Checklist Part 1: Before The Install

Yes, SQL Server 2008 is almost here, but judging by the server inventory at my new employer, there’s still plenty of people installing SQL Server 2005 for the first time. Over the last few years, I’ve put together a list of things I do during the installation process. 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 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 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 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.

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 1.5x physical memory.

I’ve been through two separate Microsoft SQL Server health checks done by very, very intelligent MS guys, and I got two different opinions about page file size. We did a lot of escalation at Microsoft, and I got the best answer from J.C. Armand, a great SQL resource.

For advanced troubleshooting, there has to be a page file on the C drive big enough to do a memory dump. This isn’t a strict requirement, but if SQL Server ever crashes hard, that memory dump 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 memory dump to troubleshoot it, and the only way we could get that was a giant C drive page file.

The page file doesn’t have to be 1.5x memory in order to do that, but the consensus among the experienced DBAs was to start the server at a high page file size, then watch the utilization carefully over time and scale the page file size down as necessary.

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.

Ready for the Next Steps?

Read the next article - SQL Server 2005 Setup Checklist Part 2: After The Install.