SCOM 2012 Disaster Recovery SQL Server Restore

Intro

Recently I had the need to perform a test of my SCOM 2012 SQL Disaster Recovery Database Restore Process. This setup has to be an exact copy of previous SQL configuration that occurred before. The following steps are what I followed to get the SCOM back online successfully. Please note that this is for a single SQL server instance. If you are doing this on SQL Cluster there are additional Steps that are required to get the cluster to look the same. However once the SQL Cluster is setup, the database restores should be the same process.

  1. New clean Windows 2008 R2 server build same server name as original server.
  2. New SQL 2008 R2 SP2 installed with same instance name as original server .
  3. Create dummy “Operations Manager” Database (AKA: Operational Database).
    1. Restore”Operations Manager” database from backups.
  4. Create dummy “Operations Manager Data Warehouse” database.
    1. Restore “Operations Manager Data Warehouse” database from backups.
  5. Restore ReportServer and ReportServerTempDB databases.
  6. Restore “master” database.
  7. Restart server.
  8. Install SCOM Agents
  9. Install Operations Manager 2012 Reporting.
  10. Test connection to SQL server from SCOM Management server.

Detailed Process

  1. Take a newly setup Windows 2008 R2 server that has all the updates that need to be applied.
    1. Needs to have same Server/DNS Name and IP(IP can be different as long as all DNS is updated appropriately, however I wouldn’t change it if possible.) as original Database Server.
  2. Install SQL 2008 R2.
    1. Use the same instance name that original SQL server had.
    2. Be sure to use the same user account assignment that was used in original SQL server. There are a couple steps where you configure accounts for services and SQL Server Administrators. NOTE: You will need to know the Passwords for the Service Accounts.
    3. Reboot
  3. Apply SP2 Update to SQL Server.
    1. Reboot
  4. Connect SQL Server Management Studio to the SQL Instance.
    1. Restore Report Server Databases
      1. Right Click on ReportServer$INSTANCENAME, select Tasks -> Restore -> Database
        1. Be sure to use the correct backup of your “ReportServer” Database.
        2. Be sure to restore your Encryption Keys for Reporting services.
      2. Right Click on ReportServer$INSTANCENAMETempDB, select Tasks -> Restore -> Database
        1. Be sure to use the correct backup of your ReportServerTEMPDB database.
    2. Restore Operations Manager
      1. Right click databases and select “New Database”.
      2. Make sure database name  matches the original exactly. They must be the same.
      3. Restore Operational Database. (http://technet.microsoft.com/en-us/library/hh278847.aspx)
    3. Restore Operations Manager Data Warehouse
      1. Right click databases and select “New Database”.
      2. Make sure database name  matches the original exactly. They must be the same.
      3. Restore Data Warehosue Database (http://technet.microsoft.com/en-us/library/hh278847.aspx)
  5. Close SQL Server Management Studio.
  6. Restore “Master” Database – NOTE: To do this you will have to restore is in single user mode.
    1. Open “SQL Server Configuration Manager” (Start -> All Programs->Microsoft SQL Server 2008 R2 -> Configuration Tools -> SQL Server Configuration Manager)
    2. In the left pane click on SQL Server Services. In the right pain you will see a list of running SQL services. Stop all services.
    3. Right click on “SQL Server(INSTANCENAME)” and select properties.
    4. On the “Advanced” Tab select the field labeled “Startup Parameters”
      1. You will add a “-m;” to the begging of the string and click Apply or Ok. Example: -m;-de:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf 
        1. Please note that there will be additional strings past this that should not be altered. It will list the other databases.
    5. Start the “SQL Server(INSTANCENAME)” service.
    6. Open a command prompt and run “sqlcmd -S SERVERNAME\INSTANCENAME”
      1. NOTE: -S is case sensitive and stands for server. (See: http://msdn.microsoft.com/en-us/library/ms180944.aspx and “sqlcmd /?” for more details.)
    7. Run following command
      1. RESTORE DATABASE master FROM DISK = 'C:\BackupLocation\master.bak' WITH REPLACE;
      2. GO
    8. Once restore is completed you will get a message stating how many rows were affected. This is an indicator that it completed successfully.
    9. Go back to “SQL Server Configuration Manager” -> SQL Server Services -> and stop all services.
    10. Right click on “SQL Server(INSTANCENAME)” and select properties.
      1. On the “Advanced” Tab select the field labeled “Startup Parameters” and remove the “-m;” that was put in before. Select Apply or Ok.
  7. Reboot SQL Server
  8. At this point I like to connect to SQL Server Management Studio to make sure that all the data looks the way it should.
    1. I check things like Security -> Logins to make sure they are all there. Also like to check the server properties to make sure that they are set correctly, I pay special attention to Memory ,CPU and Permissions configurations: right click on SERVERNAME\INSTANCENAME ->Properties
  9. Install SCOM 2012 Agent and apply all necessary Updates, CU’s, Service Packs.
  10. Install Ops Manger Reporting and apply all necessary Updates, CU’s, Service Packs.
    1. Be sure that the SCOM Management Server is turned on before you start.
    2. If you are doing this for a Sandbox / LAB environment that does not have outside internet access (is completely isolated) then you will need to perform Method 3 and 4 of these workarounds. You will know you need to do it when the Reporting Services not start. – http://support.microsoft.com/kb/2745448
    3. You may also have to run the the RESETSRS.EXE tool from the Installation Media “SupportTools” folder.  It will prompt you for an account and password. This should be a one of the SQL Server Administrator accounts. You may have to change this back to you Data Reader Account after its completed by opening “Reporting Services Configuration Manger -> Database-> “Change Credentials” Button under Current Report Server Database Credential .
      1. NOTE: You WILL have to restore / import the Report Server Encryption Key to get all of your reports back.
        1. Do you have a backup of these Encryption keys? If not you really should back them up.
      2. NOTE: You MAY have to restore your DataWareHouse Database again after doing this. ( I say may because I did the encryption keys last and it resolved the issue. This wont hurt anything if you do.)
      3. NOTE: You MAY also want to restore your ReportServer Database as well. ( I say may because I did the encryption keys last and it resolved the issue. This wont hurt anything if you do.)
  11. Reboot SQL Server Again
  12. Log into SCOM Management server load up the Management Console and make sure everything works.

Conclusion

Assuming that your Management server works well, you should have a fully functional SCOM environment again. I hope this helps save some time and frustration for others.

Best of Luck!

Advertisements

~ by Josh on May 31, 2013.

2 Responses to “SCOM 2012 Disaster Recovery SQL Server Restore”

  1. Don’t forget to enable the SQL Broker on the databases after the restore. If the broker is not enabled on the DB, then agent push functionality will fail.

    I am running a SQL AG, and found this out the hard way. Once in an AG, the DB cannot be dropped to single-user mode. The AG must be destroyed, the DB ALTERed, and then the AG re-built.

  2. Thanks JP this is fantastic note. I will be sure to update the post to include the warning.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: