How to downgrade SQL Standard Edition to SQL Express - running SharePoint

How to downgrade SQL Standard Edition to SQL Express - running SharePoint

Sometimes mistakes get made, imagine checking an SQL Express installation and finding SQL Standard Edition installed. 

To make this harder to find, the SQL Standard instance was named SQLExpress in Services.

Run a SQL @@version to get the truth.

Overview

We'll use a Hyperv replica at the DR site for preparing the recipient server, a clone of production. You could also export the production server and configure as a new virtual machine.

This example SharePoint 2013 installation with SQL2008R2 Standard

Process fist run - without impacting production (takes about an hour)

  1. Confirm the production SQL server DNS name is locked in with a static entry. This is to prevent overwrite when starting up your recipient server.
  2. On the recipient new server:
  3. Configure the recipient server as a new virtual machine on a different subnet from production to prevent name clash.
  4. Start the recipient server, set an IP address skip the gateway address, confirm SQL is running, then make a note of the file locations for the database master files.
  5. Make a note of the running SQL version / patch level 
  6. Stop SQL service on the recipient server and set to disabled.
  7. Rename the database master files folder.
  8. Uninstall SQL from add remove programs.
  9. Install SQL Express with a matching version and patch to the same level as the SQL Standard.
  10. Complete the installation and restart, confirm SQL is running (with no user databases).
  11. Stop SQL service.
  12. COPY back from the renamed SQL folder and overwrite the  new SQL files (which will have the same folder location as the original had).
  13. Start SQL service.
  14. All going well SQL will start with the original databases running, permissions intact and accessible with the original SQL service name.
(If you accidentally deleted then copied in the SQL files you will need to add permission for the SQL service to modify the SQL files.

For migration

Now that the technique is perfected...
We implement a change freeze for production, a couple of hours should do it.
And copy out the database files to the new server.

The Hyper-v way to do this (15-minutes)

  1. Use a replica of the production server at the DR site
  2. Replication Start Test Failover, but don't bother starting the virtual machine.
  3. Open the test failover server properties and copy the hard disk path and filename of the drive which contains your database files.
  4. Open the recipient new server properties and Add a new hard disk with the path you have copied.
  5. In the recipient server, open disk properties and initialise the new disk.
  6. Stop the SQL service
  7. Use File Explorer now to Copy the database files from the new drive (most recent replica of production) over the top of the SQL files (our testing files from earlier).
  8. Start the SQL service
  9. Check event logs for clean startup

Into production (15-minutes or longer depending on bandwidth from DR to prod)

  1. Replicate our new server back to the production site.
  2. Shutdown the old Production server
  3. Failover the new server and start in production, set the IP address and gateway. Might as well reverse the replication at the same time to have DR ready to go.
  4. Job done

Notes

Testing SharePoint from the local host at DR will need host file entries for the urls pointing to the DR server, and a registry key to permit localhost access: Ref: https://support.microsoft.com/en-us/kb/896861 
  1. Click Start, click Run, type regedit, and then click OK.
  2. In Registry Editor, locate and then click the following registry key:
  3. HKEY_LOCAL_MACHINESYSTEMCurrentControlSetControlLsa
  4. Right-click Lsa, point to New, and then click DWORD Value.
  5. Type DisableLoopbackCheck, and then press ENTER.
  6. Right-click DisableLoopbackCheck, and then click Modify.
  7. In the Value data box, type 1, and then click OK.
 

Comments

Popular posts from this blog

Server Manager Refresh completed with one or more warning

Event ID 122 Access to Drivers on Windows Update Blocked by Policy - Fix Here

root\cimv2\TerminalServices namespace is marked with the RequiresEncryption flag - Temporary Solution