Migration
Tomorrow, my company moves into our new facility. It’s about 1.5 miles down the road from our old facilities. We were in two different buildings about 100 yards apart. Now, we’re moving into a single building.
We’re a technology company who makes networking software and quite a bit of the hardware that runs our software .We also make management software. On top of that, we do final assembly and testing in-house for the majority of our products. That means our IT group had two server rooms, engineering had two test labs, customer support had a test lab, and manufacturing had a major facility. For all intents and purposes, we had six data centers. That’s a whole heck of a lot of networking. In our new facility, that is consolidated down to four. The engineering “lab” is massive. Our cabling contractor tells us they probably ran 150 miles of CAT6 cable in the new facility.
Tomorrow’s move meant we migrated our data center Friday night. It was bumpy but successful.
Six months ago, we bought a pair of EMC ClarIIon CX-120 SANs and a pair of IBM H series blade centers with HS22 blades. My coworker and I have spent the past six months consolidating servers, virtualizing servers, and migrating systems to new virtual machines. About 80% of our corporate systems are virtual now. I have to say that it made this weekend’s migration much easier. Well, almost.
In working with the people from EMC, they noticed that we were replicating the LUNs that contained my backup files as well as my tempdb files. This was causing a lot of replication traffic between the two SANs. While they were sitting next to each other, this was fine. But going across a 100 megabit connection, they thought it might be too much. They suggested that we not replicate those LUNs. Foolishly, I agreed. After the migration (which was bumpy itself due to a few self-inflicted issues), the virtual machines that run all four of my primary database servers failed to start. I saw my career flash before my eyes.
Fortunately, EMC sent us an amazing engineer who actually made sure the SANs were completely in sync before moving. All he had to do was present the LUN as a VMWare storage group and my servers came up. The process took about 90 minutes longer than we had expected, but it was still quite successful. And now we have a better idea what to expect when we move our DR to a colo facility next month.
One of the blessings of this move is that we got a new alarm system. And that means my only remaining SQL 2000 instance got retired and replaced with a brand new SQL 2008 instance. That made me happy.
New Project: Aviation Statistics
Most technology professionals have a lab or test environment where they can work and play. Unfortunately, I’m not one of them. Sure, I have a test environment, but it’s only for my ERP system. And my company is really particular about software licensing. I could never set up a test environment that wasn’t explicitly related to work. And since we’re so touchy at proprietary data, it makes blogging about work data incredibly difficult.
Until today.
Thanks to another member of the SQL Server community, I got my hands on an MSDN membership. That gives me access to some software licenses from MIcrosoft. And that means I can start building my own test lab. And that’s exactly what I started doing tonight.
I have a reasonably powerful desktop that is running VMWare ESXi (free!), and my first virtual machine was built to play with SQL 2008 R2. If you read my personal blog, you know that I’m a bit of an aviation nut. So to have some good data to test with, I’ve downloaded a lot of data from the Bureau of Transportation Statistics.
In the coming weeks, I’ll have a few posts about the data, what I’m doing with it, and what I’m learning by playing with it. So far, I have about 20 million rows in a single table that’s the raw import I got from BTS. Once I have a clean import, I’ll make that data file available to anybody who wants it. It’ll be my little contribution to the SQL Server community. It’s some good data for testing.
Of course, I got sucked into a project, and I’m up past my bedtime. At least I was doing something fun.
Free Lunch – Without Lunch
The old adage is that there is no free lunch. Every once in a while, a free event comes up that’s a great use of time at no cost. Today was one such example.
My friend Brent Ozar presented a virtual training class sponsored by Quest Software. Today’s topic was “Performance Tuning & Troubleshooting with DMVs” and I found it pretty fantastic.
If you keep your eyes open, you can get some really great free training out there. They’re not always sit down training in a proper classroom. Sometimes they’re seminars or virtual events. My current boss seems to think that if it’s not led by an instructor in a classroom, it’s not worthwhile. I will say that I get more out of talking to and listening to other DBAs, learning from their experience.
Sure, these events are usually paid for by a sponsor, and you have to listen to the sales pitch. It’s well worth it. My only complaint is that Quest didn’t provide lunch today. They should have provided the bacon. Since I was sitting in my own office, I guess I can handle buying my own lunch.
Looking for a Security Solution
We have some pretty strict security policies in my company. That often means coming up with creative solutions to problems. Hopefully someone in the DBA community can help me.
We have a company that does some outside work for us. We have a database server at their location. We need to allow the database server to talk to our DB server in a network DMZ. We’d use a linked server from the remote site to talk to our DMZ server. I’m okay setting up the username/password and the firewall ports. The problem is that we need to have the traffic between the two servers encrypted.
To further complicate this, we don’t want all of the traffic on these servers encrypted, just the traffic that goes across the internet.
I’m completely befuddled on how to do this properly. There is a very good chance that I’m making this harder than it needs to be, and I’d be thrilled if someone gave me a simple solution.
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
- SQL Logs
- 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:
- System Center Operations Manager Agent
- Antivirus protection
- Quest’s LiteSpeed
- Service Packs, Service Packs, Service Packs.
- Set default file locations
- Move master DB
- Move TempDB
- Resize TempDB
- Move Model
- Move MSDB
- Load Testing
Why Database Names Matter
There is a reason users shouldn’t be allowed to name their own databases. Actually, there are probably several thousand reasons. I have a new one for you–It makes my life more difficult.
Lets say you work for a company called Acme. And lets say your manufacturing people created an in-house Microsoft Access application with a SQL database called AcmeDatabase. And lets say that your support organization created a database on a different server called Acme that’s part of their CRM system. On top of that, the ERP system (yet again on a different server) prefixes all four of its databases with Acme_. As the DBA, you’d never permit this to happen unless you inherit this mess.
Welcome to my world and the mess I inherited. I have my company name (or some form of it) in countless databases. Lets make this just a little more interesting, though. We’re going to be consolidating database servers in the near future and potentially putting all of these databases on the same server.
These are databases that could potentially end up living on the same server:
- AcmeDatabase
- Acme
- Acme_App
- Acme_Data
- Acme_Forms
Which one s are ERP, CRM, or Manufacturing?
When we consolidate database servers, we’re also going to be renaming some of these databases. My application owners won’t be too happy about this. They’re going to have to get over that. I inherited this mess, and I’m going to make it right.
Rookie Mistakes: Named Instance
I hate rookie mistakes. I hate them even more when I’m the one making them.
My coworker was installing Microsoft OCS Server, and he said that he needed a SQL instance, and he told me what the instance needed to be named. Since we already had a SQL server dedicated to this project, I created a named instance on said server.
In retrospect, I should have looked a little more closely instead of just assuming I needed to create a named instance. After I looked at the screen that asked for the SQL instance, I knew that we could use the default instance and not have to go down the named instance path.
Fortunately, we’re still in the test phase with virtual servers and the servers we’re using will be replaced with physical machines. That means I’ll be able to go back and get it right the next time around.
That’s why we have test environments, right? I’d much rather make a rookie mistake in a test environment than in production.