Downgrade SQL Server Cluster from Enterprise to Standard

Sep 8, 2014 · 3 min read  ·
Share on:

I'm currently working on a PowerShell/SQL set of scripts to ease SQL Server Migrations. A few of the scripts have been posted to Microsoft's ScriptCenter. Recently, one of the ScriptCenter visitors asked:

Can this script be used to backup all server configuration and restore at a later date. The use case I have is that I have a SQL 2012 Enterprise cluster and need to downgrade it to SQL 2012 Standard. A key requirement is to maintain the same IPs/SQL instance names/virtual names etc. Our method will be to uninstall and reinstall SQL and we need a reliable method to backup and then restore the databases/logins/server configuration.

My response got cut off, so I'm posting it here.

Start-SQLMigration.ps1 can backup key elements of the server configuration, but cannot restore databases from a server that is offline. I'm currently working on a separate script that would likely help in that scenario, but it will be a bit before it’s released.

Fortunately, I just tested the following in my lab and was successful in downgrading my Enterprise Edition cluster to Standard. Here are the steps I took:

  1. Took note at the exact version of SQL Server, down to the build number
  2. Made backups of all databases, including the system databases
  3. Stopped the SQL server role within the Failover Cluster Manager
  4. Once the disks went offline within Windows, took a storage-level snapshot of the shared disks. I named this snap “ent version”.
  5. Copied the system databases, and their logs (mdfs and ldfs) to backup directories just in case the snapshot didn’t work (one can never be too safe)
  6. Used VMware to snapshot both Windows nodes (alternatively, you can shut them down and snap them at the storage level)
  7. Uninstalled SQL Server from both nodes using the Remove Node from Cluster option. This will delete the system dbs, but not the user dbs.
  8. Deleted templog.ldf from my system directory (it was the only one that the uninstall didn’t get rid of)
  9. Reinstalled SQL Server Standard exactly as Enterprise was before on both nodes – down to the IP, service account, name, directory structure, filestream share etc.
  10. Patched both nodes of SQL Server until I was back at the same build
  11. Ensured the SQL role was stopped within the cluster and that disks were offline
  12. Snapshotted the storage again, naming this snap “std version”
  13. Restored storage back to “ent version”
  14. Started SQL role within the cluster
  15. Voila! The version was now SQL Standard and all of my dbs/logins/settings were just as they were.

Of course, you should not do this without testing it in your dev environment first.