Upgraded

I’ve just upgraded all three of my sites (this one, my personal site, and my Pug site) to the latest version of WordPress. Keeping current on your software is one more way to prevent getting hacked.

My Updated SQL Server 2005 Install Checklist

If you’ve been following me lately (either my blog or Twitter), you know that I’m in the process of building up some new servers on a new SAN for a big database consolidation.

After the first server, I had a pretty good checklist. Brent Ozar has a pretty fantastic checklist that I stole a few items from.  This is my attempt at a more refined checklist. (of my own, not Brent’s)

In my new environment, each server is a VM. The physical box has 16 GB of RAM and the VM will get 8 GB. I have drives for the OS, SQL logs, SQL data, TempDB, and backups.

Here is my order of things:

• Install Windows 2003 (64 bit)
• Install Windows 2003 SP2
• Install our antivirus software
• Install SQL 2005
• Install SQL 2005 SP3
• Set permissions to permit Instant File Intialization
• Set permissions to permit locking pages in memory
• Install System Center Operations Manager  Agent
• Install Quest LiteSpeed
• Attach to SAN/Add VMWare virtual disks
• Create Directory Structure
  • SQL Logs
    • User
    • System
    • Error
  • SQL Data
    • User
    • System
• Move Master DB
• Move TempDB
• Move Model DB
• Move MSDB DB
• Resize TempDB (32 GB data, 16 GB logs), disable autogrowth
• Set default file locations
• Set CPU to boost SQL priority
• Set minimum memory to 6GB
• Set maximum memory to 7GB.
• Configure Database Mail

Disabling autogrowth on TempDB may be a little controversial. I’m okay with that. If TempDB is growing that much on our servers, which aren’t that big, I want to know what the heck is going on.

Okay, fellow DBAs, what did I forget, and what did I do wrong here? I think I did some good stuff here, but I doubt it’s perfect.

Moving System Files

Having built one database server in my new environment, the second one is an awful lot easier.

The following six lines of code saved me a ton of time this morning.

ALTER DATABASE [tempdb] MODIFY FILE (name = tempdev, FILENAME = ‘D:\TempDB\tempdb.mdf’)
ALTER DATABASE [tempdb] MODIFY FILE (name = templog, FILENAME = ‘D:\TempDB\templog.ldf’)
ALTER DATABASE [msdb] MODIFY FILE ( NAME = msdbdata , FILENAME = ‘G:\system\MSDBData.mdf’ )
ALTER DATABASE [msdb] MODIFY FILE ( NAME = msdblog , FILENAME = ‘e:\system\MSDBLog.ldf’ )
ALTER DATABASE [model] MODIFY FILE ( NAME = modeldev , FILENAME = ‘G:\system\model.mdf’ )
ALTER DATABASE [model] MODIFY FILE ( NAME = modellog , FILENAME = ‘e:\system\modellog.ldf’ )

I just can’t explain how big of a deal this is to me. In my current envrionment, every server was set up with a single C: drive. Some of these file systems are over a terabyte. Now we’re breaking things up, and I can only imagine the performance boost we’re going to see.

Server Builds

I’m building a new server. Actually, I’m building two. We just bought a new pair of SANS (one production, one DR) and are doing a consolidation project with our databases.

Each DB server will live on its own VMWare host. We’re virtualizing so that we can utilize VMWare’s clustering. It also gives us the flexibility to move things around. My two database servers will have identical configurations. One will server internal applications, and the other will host customer-facing applications.

Hardware wise, I’m using IBM HS22 blades with 32 GB of RAM connected to an EMC Clariion CX4-120SAN. Each server has five datastores, each corresponding a LUN on the SAN. Each virtual drive will live in its own storage group. I have a drive for:

• OS – Aps
• TempDB
• Log (LDF) Files
• Backups
• Data (MDF/NDF) files

My preference would have been to have the virtual machines use an iSCSI initiator to talk to the SAN, but I got overruled on that one. Still, this is a better configuration than I had before. (We had no SAN at all.)

Everything is 64 bit SQL 2005.  Again, I wanted to use SQL 2008, but that’s the next project.

After installing Windows and SQL, I started putting together a checklist of items to do next. Brent Ozar has a great checklist and has been a great reference. Here is my list:

1. System Center Operations Manager Agent
2. Antivirus protection
3. Quest’s LiteSpeed
4. Service Packs, Service Packs, Service Packs.
5. Set default file locations
6. Move master DB
7. Move TempDB
8. Resize TempDB
9. Move Model
10. Move MSDB
11. Load Testing