Downgrade SQL Server Cluster from Enterprise to Standard

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.

Chrissy is a PowerShell MVP who has worked in IT for nearly 20 years, and currently serves as a Sr. Database Engineer in Belgium. Always an avid scripter, she attended the Monad session at Microsoft’s Professional Developers Conference in Los Angeles back in 2005 and has worked and played with PowerShell ever since. Chrissy is currently pursuing an MS in Systems Engineering at Regis University and helps maintain in her spare time. She holds a number of certifications, including those relating to SQL Server, SuSE Linux, SharePoint and network security. She recently became co-lead of the SQL PASS PowerShell Virtual Chapter. You can follow her on Twitter at @cl.

Posted in PowerShell, SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *