When it comes to deploying new versions of SQL Server, we never do in-place upgrades. Management just feels there’s too much risk involved and if something goes wrong, it’s too hard to roll back to the older version. So we build a test system, copy the databases over to it, test the software against that server and, if all goes well, we get the green light to move the production data to the new version. Then we do the whole process again – build a new server, copy the database over, point all the production applications to the new server, and let it rip.

This provides a nice level of safety. If something goes horribly wrong during the conversion, we always have

the old system available for us to fail back to. When things go well, we also end up with old SQL Servers laying around unused. And users are hoarders. No one likes to throw things out. Everyone is afraid of some day in the future when they might say “Oh, I need that information that was on that old server…” (Email admins have it worse – people always think they may need that old email, so they save everything. Is there really any relevant, useful information in those emails from 1999?)

So we’ve got the test server we built plus the old server that is no longer being used, just sitting there, gathering dust. If you’ve got automated maintenance routines, they are probably still running, generating database backup files, performing index maintenance, consuming power, heating up your server room, etc. All for nothing. If you have any confidential data in those databases, they also represent a security risk. If no one is using those servers anymore, they probably aren’t getting the latest security patches or being monitored too closely. They are a breach of privacy lawsuit waiting to happen. Or a corporate espionage attack vector. They also count against your software licenses.

Shut them down. If they aren’t actively being used, get rid of them. It’s hard. As I said before, people are hoarders. People also dislike change. Even after a new server has been in use for weeks or months, they may not want to give up the security of knowing that old server is still hanging around. Usually by that time, trying to go back to using the old server would be more work than fixing whatever was wrong with the new one, but it doesn’t matter. People want their security blankets.

I have a plan. Whenever a new server is brought online that replaces an old server, I give the migration team a couple of weeks with both the old and new SQL Servers running. Often, they will want to go back and compare things between the old and new systems. There is always something that forgets to get moved, etc. Everyone gets a grace period, but after a couple of weeks, I start to pester people with weekly emails asking if I can shut the old server down.

When I finally get the OK, I don’t take the server down immediately. Instead, I set all the databases to Offline and wait another 1 or 2 weeks. (Reminders in your favorite calendar program really help with tracking all these time-frames.) That usually generates errors from some process that someone forgot about that is trying to access the old server. Or there’s that one remote user that didn’t check his email and forgot to point to the new server. By setting the databases to offline, I can flush out these cases and, should there be a case where they truly need to connect to the old server, I can bring the database back online with a couple mouse clicks.

After those 1 or 2 weeks have passed, I will finally decommission the server. I have a checklist I go through for this process to make sure nothing is missed and that it is always done in a consistent manner:

1. Take final backups of all databases, including the system databases (which store login and job info) and archive them off to a secure location. I store my backups in folders named for the version of SQL Server that created the backups and subfolders named for the server the backups are from.
2. Delete any old maintenance backup files that are in your normal backup location.
3. Remove the server from any automated monitoring you might have set up.
4. Stop the various SQL Server services and set them to Disabled – just to prevent them from starting up if the server is rebooted or brought back online for some reason.
5. Update any documentation that might reference this server – server lists, wiki entries, DR instructions, etc.
6. Notify the rest of your IT team that the server can be decommissioned. This will allow them to dispose of old hardware, or if the server was virtual, reallocate those host resources to other VMs.

And perhaps the most important benefit of shutting off servers – when it’s shut down, that’s one less server you have to worry about!