Darren Myher's Blog

Companies like mine that provide customers SQL Server access under a Service Provider License Agreement (SPLA) saw our costs go up almost 10,000% or more in 2013.  What used to cost under $20 per user per month for a SQL Enterprise license suddenly jumped to the equivalent of hundreds of dollars per user per month.  This required that we immediately downgrade dozens of customers from SQL Enterprise to SQL Standard.

Fortunately for me, we weren’t actually taking advantage of any SQL Enterprise-Edition specific datatypes so I didn’t have any structural database issues to deal with.  Of course Microsoft does NOT provide a way to gracefully downgrade an existing instance, so my steps had to include a complete un-install of SQL Server Enterprise Database Engine and Reporting Services, and re-install of those components.  I did not have Analysis services installed or that would have had to have been uninstalled as well.  (The other tools could remain – reducing the time for this process by about an hour).

If you read other blog posts about things to do first you’ll see things like checking the server’s settings for AWE, memory etc.  In my case I all really cared about were the following:

• SQL Users and their permissions
• Maintenance Plans
• SQL Server Agent Jobs
• Reporting Services databases and subscriptions – there were a lot of them.

For SQL Users, I created a script to re-create those by right-clicking each user and scripting create to a new window.

On this particular server, no specific securables were defined.  if you have securables defined, then you’ll also need to script those out.

SQL Auth users get assigned random passwords by this process.  If you know the passwords for those accounts, you’ll need to script them out.

I DID NOT TRY to restore the Enterprise Edition Master / Model / MSDB databases that would have allowed me to avoid this step.  I just figure that they are incompatible between editions so I didn’t even try.

Since SSMS doesn’t seem to include an easy way to script out maintenance plans, I used a 3rd party tool to do that step: Toad for SQL Server – formerly from Quest software, now part of the Dell family of products.  (By the way, if you haven’t moved to using Toad for SQL Server, I STONGLY recommend it.  It’s worth the license price in terms of the productivity you’ll gain.)

In Toad for SQL Server, you double-click the Maintenance Plan, then click the Export button:

Later, after you’ve restored your databases, you can create a “new” maintenance plan then click the “Import” button from the toolbar to import the file you previously exported.  All dimensions of the maintenance plan get re-created this way including the schedules.  This is a massive time saver.

In terms of SQL Server Reporting Services, here’s what I did:

• Open Reporting Services Configuration Manager, Encryption Keys, Click Backup:
  
• Export the key with a .snk extension to make it easier to restore later:
  
• Backup the ReportServer and ReportServerTempDB databases
• Stop Reporting Services
• Uninstall SQL Server Enterprise Edition
• RENAME THE DATABASES BEFORE YOU RUN SQL Standard setup or setup WILL FAIL
• Install SQL Server Standard Edition

At this point I backed up the new ReportingServer databases just in case I needed them.

• Restore the original Enterprise Edition ReportServer and ReportServerTempDB databases, choosing the option to overwrite the original.
• Open the Reporting Services Configuration Manager
• Restore the key you exported in the previous step.
• Try to start the reporting server and open the site.  If you get an error about rsReportServerNotActivated then restore the key again – you may have restored the key in the wrong order.
• If you get an error about The feature: "Scale-out deployment" is not supported in this edition of Reporting Services. (rsOperationNotSupported) then you need to run a command line tool to list the keys and delete the oldest ones:

Run this command to list the Keys:

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

Then run this command to delete all but the last key (which should be the one you restored):

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe -r 132b8974-1503-4682-ae12-a1d836e3526e

Substitute the keys from your first command – but remember to keep the last key.

When done, run the first command again

C:\Program Files\Microsoft SQL Server\110\Tools\Binn>RSKeyMgmt.exe –l

And make sure that only one key is listed.

When done, refresh your reporting services web site and it should load successfully with everything in tact – Data sources, reports, subscriptions, etc.