As a DBA, one of the more stressful tasks that occasionally lands on your plate is having to decommission a SQL Server. Maybe the hardware is being retired, or databases are being consolidated onto fewer servers. Whatever the reason, it’s a delicate process that requires careful planning and execution to avoid data loss or business disruption.

I still remember the first time I had to decommission a server early in my career. I was so paranoid about missing a step and messing something up! Since then, I’ve unfortunately had to do it many more times. But that experience motivated me to put together a thorough checklist to follow. I’ll share that checklist with you here, along with some tips I’ve learned along the way.

Step 1: Assess server usage and plan migration

The first step is to figure out what databases and applications are running on the SQL Server to be decommissioned. Work with business users to understand which are still critical and need to be migrated to another server vs. which are no longer used and can be archived or deleted.

For the databases that need to be kept alive, plan out where they will be moved to. Ensure the new server has sufficient capacity for the additional workload. Work out a migration timeline and schedule with the application teams.

Step 2: Notify users of the decommission

Give business users and application teams plenty of lead time about the impending server decommission. Communicate the planned timeline clearly. This gives them time to wrap up any final data loads or reporting runs on the old server.

I recommend sending the initial notification at least 2 months in advance, with regular reminders leading up to the decommission date. In my experience, there’s often one or two business users who only speak up about their crucial process running on the server at the last minute!

Step 3: Migrate databases and applications

Follow your migration plan to copy over the databases to their new server. Be sure to backup the databases first! Work with the application teams to point their apps to the new database location and verify everything is connecting and running properly.

For very large databases, you may need to use special techniques to migrate them efficiently:

• Use backup and restore for databases under 1 TB
• For larger databases, consider using log shipping to create a copy on the new server and keep it synced
• Tools like the SQL Server Import and Export Wizard or BCP can help move data in batches
• Alternatively, set up transactional replication to sync the data across servers

-- Perform a manual failover
ALTER DATABASE YourDatabaseName SET PARTNER FAILOVER;

It’s a good idea to keep the databases on the old server around for a week or two after the migration, in case something was missed and you need to refer back to them. But after that, be sure to delete them so there aren’t multiple copies of the data floating around.

Step 4: Perform a final backup

Before shutting down the SQL Server for good, perform one final full backup of all the databases. Copy these to a secure location (or two!) and store them according to your company’s data retention policies. You never know when you might need to pull some old data out of cold storage.

Step 5: Retire the SQL Server

The final step is to retire the server itself. Be sure to:

• Uninstall SQL Server and any other software
• Wipe the drives
• Remove the server from Active Directory and DNS
• Shut it down and unplug it from the network
• Dispose of or repurpose the hardware asset following your company’s policies

And that’s it! With careful planning and by following these steps, you can safely and smoothly decommission a SQL Server, even those hosting very large databases. The keys are communication, preparation, and having a checklist so you don’t miss a critical step.

I hope this has been helpful!