In-Depth

Be Prepared

Scout out potential trouble by listening to your servers’ sounds. The best plan, as always, is a current backup.

• By Rodney Landrum
• 03/01/2002

Many network administrators who have been in the game long enough tend to develop a motherly instinct when it comes overseeing their family of servers and workstations. They know when their machines are too hot, hungry (for more RAM), or sick. The sound a machine makes when it’s down and out is distinct. Sounds emanating from the server normally lead to a straightforward diagnosis, as there are so few moving parts: hard drive failure. The noise sometimes reminds me of a playing card in the spokes of a bicycle or an index finger in a moving CPU fan (though I shouldn’t admit to having done the latter). The symptoms aren’t always severe at first. Advanced operating systems like Windows NT/2000 can detect bad sectors on the drive and reroute the location accordingly. But when the worst-case scenario becomes reality, you’d better count on a long night.

Overconfidence
I wasn’t prepared for such an evening when I drove six hours to a client site to upgrade its SQL Server from 6.5 to 7.0. In fact, because I’d done at least 10 upgrades of the same kind, using the upgrade wizard provided in SQL Server 7.0, I was expecting to roll out early and spend a relaxing evening studying in the hotel room for an upcoming exam.

After arriving, I made my introductions to the IT staff members, with whom I’d only had phone contact previously. We shared a few moments of “Hey, that’s what you look like!” before I started getting ready for the upgrade. I think they could perceive my confidence, as we’d been planning this for several weeks. We chose a Thursday evening so I could be on site Friday morning in the event of a disaster.

The Dreaded Clicking Sound
I remember seeing the entrance to the server room from about 15 feet away. As I approached the entrance, I felt something odd, like a premonition of a long night without the opportunity even to stop for a slice of delivery pizza—but I shrugged it off.

The moment I placed my right foot in the doorway, however, I heard it: The repetitive click of a small, incapacitated spindle arm banging against the metal surface of a disk drive platter. I looked at the now inquisitive IT staff members, who’d placed their entire trust in me to make their jobs and lives easier. Their questioning looks said, “I wonder what he’s going to do about this?” I smiled and said something that apparently only I found humorous: “So, you guys did make good backups like I asked, right?”

The more I worked on the machine, the more noise it made and the slower it got. I had to act fast.

The sickly machine, naturally, turned out to be the SQL erver system I was there to upgrade. I pulled up to the machine in a painfully uncomfortable rolling office chair, a place I would occupy for the next several hours. I was miraculously able to log in and navigate the directory structure, though the machine was crawling. The first thing I discovered was that there was no configured RAID (Redundant Array of Inexpensive Disks), hardware, software or otherwise. They’d gone with the antithesis of RAID—SLED (Single Large Expensive Disk). The drive was partitioned with C: and D: drives. The master database was set up on C: and all the user databases on D:. SQL Server seemed to be running fine but I knew it was only a matter of time—potentially only minutes—before we might never boot again. The more I worked on the machine, the more noise it made and the slower it got. I had to act fast.

If at First You Don’t Succeed…
They’d purchased a new server that was going to be the recipient of the upgraded databases, once the conversion was finished on the now-crashing server. Both were running NT 4.0 with Service Pack 6. They’d already installed SQL 7.0 with SP2 on the new server.

I faced several alternatives. One option would be to attempt a machine-to-machine upgrade by connecting, via the network with the upgrade wizard, to the old SQL Server. Another course of action: Remove SQL 7.0 on the new server, install SQL 6.5, copy over the database files to the new server and perform a single-machine upgrade. Either option would require pulling very large amounts of data—a gig and change—from the ailing system. I decided to walk down the machine-to-machine upgrade path first. About 10 minutes into the process, when I was just starting to believe it would work, the old server hung. I waited for any sign of life, then decided to take the only option left and bounce the old server. Another small miracle occurred when it actually rebooted successfully and the SQL services started! So, scratch plan A and move to plan B, the single-machine upgrade.

I’d learned a trick when moving a full SQL Server directly to a new machine without having to restore databases individually, one of which I’d employed several times in the past. The procedure’s simple: Install the same version of SQL Server on another machine. Stop the SQL services on both machines. Copy all the SQL database and log files, like master.dat, into the same location from the source server to the destination server. If master.dat resided on C:\MSSQL\Data, then that’s where it has to go on the new server. The master database contains information about all the databases, users and logins on the server. With all the files in place, take the old server offline, give the new server the same name as the old box and change the IP address. Restart the SQL services on the new machine. If everything was done correctly, the new SQL Server would be identical to the old SQL Server. This was my new plan of attack.

Crash No. 2
I uninstalled SQL 7.0 and installed SQL 6.5 on the new server. It had been partitioned identically, so all that remained for me to do was move the data and log files. I connected to the dying server by mapping drives to the administrative shares on the C: and D: drives and began copying the files. In hindsight, I could have attempted to zip the files, but that would require even more HD activity. After another 45 minutes of copying, the old server hung again. Arggghhh!

Please Tell Me You Made the Backup
This time I opted to restore from backup tape. I’d made the network manager at the client site promise me he’d make backups of everything, including the raw data files, before I began the upgrade. He’d stopped the SQL services so the files wouldn’t be open and subsequently skipped during the backup process. I was able to pull the backed-up files from the tape and restore them on the new SQL server, which was now a SQL 6.5 machine. I started the services after powering down the old machine for the final time, and everything started successfully. All that remained was to reinstall SQL 7.0 and complete the upgrade process for all the databases. Thankfully, it went off without a hitch.

As it was nearly 3 a.m. before I finished, I headed back to the hotel. I was so geared up, I did actually study for a few minutes. The users showed up the next morning, rested and eager to experience the promised performance gains. I made sure they were all content and then headed home for a relaxing weekend, remembering to thank the real heroes who’d saved the day with one backup tape.

MTBF Means Just That
Every hard drive comes with an MTBF value. MTBF stands for Mean Time Between Failures and is measured in hours. Though today’s hard drives have values in the hundreds of thousands of hours, just knowing the number exists is food for thought.

Remember, though, that there are many resources and technologies out there to prevent hard-drive catastrophes, or to at least provide the minimum downtime. Thus, there are really no excuses for not protecting your data. Technologies like Intelli- Mirror, clustering, Remote Installation Services, disk imaging and single-disk recovery procedures that come with many backup applications offer varying levels of protection.

There are also companies that provide services to yank data off drives seemingly beyond repair. Many of these resources are expensive, however, and not all companies see the value in investing in them. Having seen my share of crashes, and with the plunging prices of hard drives, I’d recommend at the very least using the software mirroring available with Win2K in conjunction with a solid tape backup plan. In the end, the time and cost involved with rebuilding and restoring a server, especially if there’s significant data loss, would likely pay for the ultimate addition to my server family: a twin pair of quad Xeon, load-balanced, RAID 10, hot-swappable cluster servers with a solid tape backup plan.