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

Post a Comment

Popular posts from this blog

Server Manager Refresh completed with one or more warning

The system cannot access one or more event logs because of insufficient rights The Problem: Configuration refresh message. The system cannot access one or more event logs because of insufficient rights, file corruption, or other reasons. For more information, see the Operation channel in the ServerManager-ManagementProvider error log on the target server. Problem occurred on Server 2012 servers that have been upgraded to Server 2012R2 servers. Servers are also clustered. The Solution: Following the event logs leads to a few registry keys. You can eliminate the messages by: 1. Export the following registry keys (for safety) 2. Delete the following keys: HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WINEVT\Channels\Microsoft-Windows-DxpTaskRingtone/Analytic HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WINEVT\Channels\Microsoft-Windows-IME-Roaming/Analytic HKLM\SOFTWARE\Microsoft\Windows\CurrentVersion\WINEVT\Channels\Microsoft-Windows-IME-SCDICCOMPILER/Analytic HKLM\SOFTWARE\Mic
Read more

Shrewsoft VPN client - can't open Access Manager

Shrewsoft VPN client - can't open Access Manager Problem Access Manager is not visible Occurred after adjusting using dual monitors. Reinstall does not fix the issue. The Fix Use regedit to rename or delete the following key: HKEY_CURRENT_USER\Software\ShrewSoft\vpn\user Had a look around the Shrewsoft support site - nothing useful in there to fix this problem.
Read more

Hyper-V could not replicate changes for virtual machine as replication is suspended on the Replica server

Image
EVENT ID 32088 for all VMs After running out of disk space on the Replica host server, all VMs stopped replicating.  Replication cannot simply be started by right clicking on the source VM and selecting Resume Replication. No console error message just the event log event 32088.  I considered removing replication and re-replicating but this seemed an awful lot of work for simple problem. Tried stopping the Hyper-V service on the replica host and restarting, but this also did not work. THE FIX 1. Go to the destination replica server, right click each VM and select Resume Replication.  2. Now you can go to the source server, right click each VM and select Resume Replication. For some strange reason this works. Just trying one end or the other will fail without a console error message (but shows in the event log).
Read more