Moving SCOM 2012 R2 Data Warehouse Database to New SQL Server Cluster

This is the second of two posts in regards to moving Operations Manager 2012 R2 Databases.  The first post about moving the OperationsManager database can be found here.  I won’t rehash any of that information but will instead just get right into the How To portion.  The TechNet article for moving the SCOM DataWarehouse Database can be found here.

  1. Stop the following services on OPSMGR01 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  2. Stop the following services on OPSMGR02 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  3. On OLDSQLServer, use SQL Server Management Studio to create a full backup of the data warehouse database. The default name is OperationsManagerDW. We recommend that you also back up the associated master database.
    1. Copy backup file to \\SQLCLUSTERNODE1\SHARE
  4. Open SQL Management Studio on SQLCLUSTERNODE1 and connect to SQLCLUSTER\SCOMDW Instance
    1. Restore OperationsManagerDW Database using SQL
  5. Backup registry on OPSMGR01
  6. Backup registry on OPSMGR02
  7. Update Registry on OPSMGR01
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseWarehouseDBServerName: SQLCLUSTER\SCOMDW
      2. DatabaseName: OperationsManagerDW
  8. Update Registry on OPSMGR02
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseWarehouseDBServerName: SQLCLUSTER\SCOMDW
      2. DatabaseName: OperationsManagerDW
  9. Login to the server you are using for reporting off of the SCOM Data Warehouse
    1. HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center Operations Manager\3.0\Reporting,\ DWDBInstance double-click the name and change the value to the hostname of the SQL Server-based computer now hosting the operations manager DW database, and then click OK to save your change.
      1. In my case that would be SQLCLUSTER\SCOMDW
    1. Start the System Center Data Access Service on OPSMGR01 associated with the reporting server. This is needed to access the reports page
    2. On reporting server, change the connection strings.
      1. Open a browser and go to the reporting webpage
      2. Click Show Details and then click Data Warehouse Main. Change the Connection String to contain the new data warehouse server name, and then click Apply. SQLCLUSTER\SCOMDW.
      3. Test the connection
      4. Click Application monitoring, and then click .NET monitoring.
      5. Click AppMonitoringSource.
      6. On the AppMonitoringSource page, click Properties and change Connection string to contain SQLCLUSTER\SCOMDW, and then click Apply.
      7. Test the connection
      8. Close the browser.
    3. On SQLCLUSTERNODE1 update the OperationsManager database table. (Note that this is NOT the DataWarehouse instance!)
      1. Open SQL Server Management Studio. SQLCLUSTER\SCOM
      2. Expand Databases, OperationsManager, and Tables.
      3. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse, and then click Edit Top 200 Rows.
      4. Change the value in the MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F column to SQLCLUSTER\SCOMDW
    4. Update the OperationsManager database for Application Performance Monitoring functionality.
      1. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring, and then click Edit Top 200 Rows.
      2. Change the value in the MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A column to SQLCLUSTER\SCOMDW
      3. Do the same for the following tables.
      4. Right-click dbo. MT_Microsoft$SystemCenter$DataWarehouse$AppMonitoring_Log and then click Edit Top 200 Rows. Change the value of column Post_MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A to SQLCLUSTER\SCOMDW
      5. Right-click dbo.MT_Microsoft$SystemCenter$DataWarehouse_Log and then click Edit Top 200 Rows. Change the value of column.Pre_MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F TO SQLCLUSTER\SCOMDW.
      6. Close SQL Server Management Studio.
    5. On SQLCLUSTERNODE1, update the member database.
      1. Open SQL Server Management Studio and connect to SQLCLUSTER\SCOMDW instance.
      2. Expand Databases, OperationsManagerDW, and Tables.
      3. Right-click dbo.MemberDatabase, and then click Edit Top 200 Rows.
      4. Change the value in the ServerName column to reflect the name of the new SQL Server, SQLCLUSTER\SCOMDW
      5. Close SQL Server Management Studio.
    6. Update security credentials on SQLCLUSTER\SCOMDW
      1. All of my security credentials carried over and I had no issues.  However, your environment may be completely different.  Definitely make sure to read this section in the TechNet documentation
    7. Stop SQL Services relating to SCOM on OLDSQLServer
    8. Open PowerShell.  Type Write-Host “Bye Kitty” to sacrifice a kitten
    9. Restart OPSMGR01 and OSPMGR02 (this was to ensure registry setting changes were picked up)
    10. Start the following services on OPSMGR01 and change their startup type to Automatic
      1. System Center Data Access
      2. System Center Management Configuration
    11. Start the following services on OPSMGR02 and change their startup type to Automatic
      1. System Center Data Access
      2. System Center Management Configuration
    12. Spam refresh OperationsManager event log on OPSMGR01.  Hopefully you see lots of informational messages and no errors about connections to the SQL Server.

To verify a successful move of the Operations Manager Data Warehouse Database:

  1. Verify that you can successfully run a report from the console.
  2. Ensure that the health state of all management servers in the management group are Healthy.
  3. If the health state of any management server is Critical, open Health Explorer, expand Availability – <server name>, and then continue to expand until you can navigate to Data Warehouse SQL RS Deployed Management Pack List Request State. Check the associated events to determine if there is an issue accessing the data warehouse database.
  4. Check operating system events:
    1. Open the operating system’s Event viewer. Navigate to Event Viewer, and then to Operations Manager.
    2. In the Operations Manager pane, search for events with a Source of Health Service Module and a Category of Data Warehouse.
  1. The move was successful if event number 31570, 31558, or 31554 exists.
  2. There is an issue accessing the data warehouse database if event numbers 31563, 31551, 31569, or 31552 exists.
  3. Check events in Operations Manager:
    1. In the Operations console, select Monitoring.
    2. Navigate to Monitoring, Operations Manager, Health Service Module Events, and then to Performance Data Source Module Events.
    3. Search the Performance Data Source Module Events pane for events with a Date and Time that is later than the move.

 

Moving SCOM 2012 R2 Database to New SQL Server Cluster

When our System Center environment (VMM, Orchestrator, SCOM, DPM) was originally setup all the databases were installed on a single SQL Server VM (but in separate instances).  For a variety of reasons I made the decision that our System Center databases needed to at least be in a cluster configuration.  Luckily I stumbled across this excellent guide by MVP Paul Keely about configuring a SQL 2012 Cluster with Always On to be used for System Center databases.  I am not going to cover any of that here, this post is just going to be about how to move the Operations Manager database once the new cluster is built.

First, I need to go on a rant about just plain poor timing on my part.  If you don’t care to read it, skip to the next paragraph.  There is an article published by Microsoft that talks about how to move the Operational Database.  If you look at that article you will notice that it was last updated on January 19th.  That’s awesome.  You know what’s not awesome?  I first attempted to move the Operational Database on January 17th.  It failed miserably.  Why?  Because the article I was working off of didn’t include a second registry key that needed to be modified.  That’s 6 hours of my  life I will never get back.

In my environment I have two Operations Manager Management servers, in this article they are named OPSMGR01 and OPSMGR02.  When I wrote up my plan for this move I duplicated some sections that needed to be done on both servers and I kept that the same in this article.  For the SQL Server name, I used the name of the new SQL Cluster as SQLCLUSTER followed by the instance name.  If you are moving the databases to a single server you would just use the server name followed by the instance name.

  1. Stop the following services on OPSMGR01 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  2. Stop the following services on OPSMGR02 and change their type to Manual.  Otherwise they will restart and cause you problems.
    1. System Center Data Access
    2. System Center Management Configuration
  3. Create full backup of OperationsManager Database on OLDSQLServer using SQL
    1. Copy backup file to \\SQLCLUSTERNODE1\SHARE
  4. Open SQL Management Studio on SQLCLUSTERNODE1 and connect to SQLCLUSTER\SCOM Instance
    1. Restore OperationsManager Database using SQL
  5. Delete backup file from \\SOMEFILESHARE (Needed the space for the massive SCOM Data Warehouse Database backup that was coming next)
  6. Backup Registry on OPSMGR01
  7. Backup Registry on OPSMGR02
  8. Update Registry on OPSMGR01
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseName: SQLCLUSTER\SCOM
      2. DatabaseServerName: OperationsManager
    4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database and repeat step 3. (This was the aforementioned missing step)
  9. Update Registry on OPSMGR02
    1. Regedit
    2. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Setup
    3. For each of the following keys, double-click the name, change the value to the hostname of the SQL Server-based computer now hosting the operational database, and then click OK to save your changes.
      1. DatabaseName: SQLCLUSTER\SCOM
      2. DatabaseServerName: OperationsManager
    4. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\System Center\2010\Common\Database and repeat step 3.
  10. Edit the following file on OPSMGR01
    1. Browse to %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config
    2. Backup existing file (copy and rename to .old and place somewhere safe)
    3. Open Notepad as Administrator and open the file in Step 1.
    4. In the <Category> tags named “Cmdb” and “ConfigStore”, change the value for ServerName to the name of the new SQL server.
    5. SQLCLUSTER\SCOM
    6. Save changes and close
  11. Edit the following file on OPSMGR02
    1. Browse to %ProgramFiles%\System Center 2012\Operations Manager\Server\ConfigService.config
    2. Backup existing file (copy and rename to .old and place somewhere safe)
    3. Open Notepad as Administrator and open the file in Step 1.
    4. In the <Category> tags named “Cmdb” and “ConfigStore”, change the value for ServerName to the name of the new SQL server.
    5. SQLCLUSTER\SCOM
    6. Save changes and close
  12. Update the operational database with the new database server name on SQLCLUSTERNODE1
    1. Open SQL Server Management Studio. Connect to SQLCLUSTER\SCOM instance.
    2. Expand Databases, OperationsManager, and Tables.
    3. Right-click dbo.MT_Microsoft$SystemCenter$ManagementGroup, and then click Edit Top 200 Rows.
    4. Change the value in the SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 column to SQLCLUSTER\SCOM
      1. The GUID part in your database isn’t going to match the one above.  It’s ok!
    5. Save the change.
  13. On SQLCLUSTERNODE1, update the operational database with the new database server name to specify the location of the Application Performance Monitoring tables
    1. Open SQL Server Management Studio. Connect to SQLCLUSTER\SCOM instance
    2. Expand Databases, OperationsManager, and Tables.
    3. Right-click dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring, and then click Edit Top 200 Rows.
    4. Change the value in the MainDatabaseServerName_5C00C79B_6B71_6EEE_4ADE_80C11F84527A column to SC-SQLCLUS-SCOM\SCOM
      1. The GUID part in your database isn’t going to match the one above.  It’s ok!
    5. Save the change.
  14. Update security credentials on SQLCLUSTER\SCOM
    1. All of my security credentials carried over and I had no issues.  However, your environment may be completely different.  Definitely make sure to read this section in the TechNet documentation
  15. Execute these SQL commands on new OperationsManager database instance:
    1. sp_configure ‘show advanced options’,1
    2. reconfigure
    3. sp_configure ‘clr enabled’,1
    4. reconfigure
  16. Run the following SQL query (If you added the OperationsManager database to AlwaysOn before this step you can skip this step. I ran through all of this and added it to AlwaysOn once I knew for sure it was working on the new SQLCLUSTER):
    1. SELECT is_broker_enabled FROM sys.databases WHERE name=’OperationsManager’
      1. If the result of this query was an is_broker_enabled value of 1, skip this step. Otherwise, run the following SQL queries:
    2. ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
    3. ALTER DATABASE OperationsManager SET ENABLE_BROKER
    4. ALTER DATABASE OperationsManager SET MULTI_USER
  17. Stop SQL Services relating to SCOM on OLDSQLServer
  18. Open PowerShell.  Type Write-Host “Bye Kitty” to sacrifice a kitten
  19. Restart OPSMGR01 and OSPMGR02 (this was to ensure registry setting changes were picked up)
  20. Start the following services on OPSMGR01 and change their startup type to Automatic
    1. System Center Data Access
    2. System Center Management Configuration
  21. Start the following services on OPSMGR02 and change their startup type to Automatic
    1. System Center Data Access
    2. System Center Management Configuration
  22. Spam refresh OperationsManager event log on OPSMGR01.  Hopefully you see lots of informational messages and no errors about connections to the SQL Server.
  23. Start OperationsManager console.  Check stuff

That’s it!

Failed To Connect To URL ods.systemcenteradvisor.com

I signed up for and was able to get into the Preview of the new System Center Advisor. One problem I immediately ran into was that one of my SCOM Management Servers (I have 2) refused to connect to the advisor service. The Operations Manager event log was filled with these errors:

Health Service HTTP module in management group “MGMT_GROUP_NAME” failed to connect to url (https://ods.systemcenteradvisor.com/ProtectionStatusDataService.svc/PostDataItems). The service can be offline or not deployed to this DNS name.

Health Service HTTP module in management group “MGMT_GROUP_NAME” failed to connect to url (https://ods.systemcenteradvisor.com/UpdatesRequiredDataService.svc/PostDataItems). The service can be offline or not deployed to this DNS name.

Health Service HTTP module in management group “MGMT_GROUP_NAME” timed out connecting to url (https://ods.systemcenteradvisor.com/GenericEventDataService.svc/PostDataItems).

There were also a lot of warnings like this one:
A subscriber data source in management group NFM has posted items to the workflow, but has not received a response in 940 minutes. Data will be queued to disk until a response has been received. This indicates a performance or functional problem with the workflow.
Workflow Id : Microsoft.SystemCenter.CollectGenericEventDataToCloud
Instance : MGMTSERVER.DOMAIN.COM
Instance Id : {08B287B1-8AF8-C7DE-2AF9-C91877D3F0E5}

I reached out to Daniele Muscetta on Twitter, and after working with him and other people on the team over last week and a half or so we finally figured out what the problem was. We had checked all the possible ways to set a proxy to see if something was stuck there, and finally ran a network capture to see what was going on. After analyzing this and looking at it from their side they realized that the MGMT server which was not working was trying to connect using SSL 3.0. Which led to some further investigation.

Here is what the settings looked like on the working MGMT Server:02

And here is what they looked like on the non-working MGMT Server:01

Quite a bit of difference there. In the interest of time I deleted all the registry settings on the non-working MGMT server so that the keys matched those on the working MGMT server. After I did that and rebooted the server, everything has been working great! As to why it was set that way, I wish I knew. That particular server was built before my time here.

A further explanation of what they believe the issue was is below.

I believe this was the issue:

[HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\SecurityProviders\SCHANNEL\Hashes\SHA]
“Enabled”=dword:00000000

Having that set disables all SHA-1 based cipher suites. On server 2012 by default that would leave you with SHA-2 based suites as well as the older and weaker MD5. Both SSL labs and our services don’t support MD5 since it is considered to be insecure. I found a KB article stating that having SSL 2.0 and TLS 1.2 both enabled were incompatible and cause TLS 1.2 to not work. In that case since SHA-2 based cipher suites was added with TLS 1.2 that would leave you unable to connect. I wonder why someone in the past specifically turned off SHA-1 and enabled SSL 2.0. SSL 2.0 is known to be insecure. There must have been some site only supporting SSL 2.0 and MD5, and broken SHA-1 support that they wanted the server to be able to talk to.

Error 3000 – Unable to Register to Advisor Service

I was able to get into the current Limited Preview of the System Center Advisor service. Everything went great with the initial setup and Configuration except for registering Operations Manager with the Advisor Service. No matter what I did, I kept getting Error 3000: Unable to Register to Advisor Service. Please contact the system administrator.

If you Google this error message, you get pointed to this article by Stefan Roth. That article states that the issue is a time sync problem. I went through all the steps, and still no luck. I reached out on Twitter to Daniele Muscetta and we did some initial troubleshooting because he believed the issue was with a Proxy server (which turned out to be correct).

The catch was that we don’t use a Proxy server (anymore). We did last year when I was trying out System Center Advisor, but I ended up uninstalling it and removing all the management packs. After a bunch of initial troubleshooting, we were kind of stuck and setup a meeting to run a diagnostics tool. And that’s when I stumbled onto something magical.

While using the command add-on in PowerShell searching for Override related cmdlets for SCOM, I happened to notice there was a cmdlet called Get-SCAdvisorProxy. I run this command, and sure enough, it has the name of our old proxy server in it! There is also a command Set-SCAdvisorProxy. I ran the command below to set the Proxy to nothing.

Once that was done, I was able to register with the Advisor service and everything is looking great!

PowerShell Desired State Configuration (DSC) Journey – Day 21

I have written 20 articles about what I have learned about DSC so far.  At this point I am familiar enough with the technology that I have started to think about how I am going to demo this technology in my organization.  I could demo the basic features that are available without going into anything in depth, but I want this demo to be a complete and total WOW reaction.  I have spent some time this week figuring out how I want to proceed with the demo and this is what I have decided.

We use System Center Virtual Machine Manager to deploy VM’s.  I want to demonstrate using VMM and DSC the following:

  • Deploy a “farm” consisting of 4 Web Servers and 1 SQL Server.  This may require me to configure VMM Service Provisioning.
  • Use VMM Templates (including possibly a service template) to do the initial building of the VM, naming it, and joining it to the domain.
  • After that I want it to automatically configure its Local Configuration Manager for settings I have decided on
  • This includes reaching out to a Pull server and getting the appropriate Configuration
  • Applying that Configuration
  • Breaking the servers, and them fixing themselves based off of their DSC Configuration

Too ambitious?  Not ambitious enough?  Any high level steps I missing?

All that being said, I am going to continue to post about anything relating to this process (even if it’s VMM specific) under the DSC Learning Journey on Twitter, as eventually it will all tie in together.

PowerShell – Find Hyper-V Dynamic Disks

Operations Manager – Dynamic Group Using Regular Expression

I have a bunch of machines that are all named using the same syntax that I would like to create a dynamic group for.  The computer names all look like AA-DEV-AA where the ending AA is always the individual developers initials.  In my case my computer name would be AA-DEV-JB.  I created the dynamic group using the regular expression below.

The ?i: ensures that the expression is not case sensitive.  The $ at the end is because there are some machines that are named things like AA-DEV-SQL which I don’t want to include in the group.

Use Start-Sleep to Delay PowerShell Script Execution in Operations Manager Recovery Task

I had an issue where I needed a monitor in Operations Manager to start a service when it crashed.  Which is easy enough to do (you can find all kinds of examples on it), except that in my case the service wasn’t just stopping immediately.  It is a custom homegrown application that detected a dropped connection, wrote an event to the event log (which Operations Manager was watching), and then killed the service.  The problem is that it would write the event, and then attempt to stop the service, which took several seconds to stop.  In that interval Operations Manager would pick up the event, try to start the service, determine that it wasn’t stopped, and fail.  So, I needed to figure out a way to “delay” the recovery task.  I couldn’t find a way to do this natively in the recovery task, so I did it in PowerShell.  The PowerShell “script” itself was a simple two lines.

This forced the script to wait 30 seconds to run, and then started the service.  Here is what the recovery task in SCOM looks like.

Capture

Update Custom Property Field in VMM with the Last Update Date

We recently added four custom fields to Virtual Machine Manager (VMM) to help us keep track of application owners, server owners, when in our patch schedule servers were being updated, and the last time a server was updated.  In order to keep the field for last update current, I wrote a script to get the date the server was last updated, compare it to the value currently in the field, and if the new date was later than the value in the custom property, to change it.  Now there are certainly some issues with this script that need improving.  The main issue being that if there are multiple updates with different update days, it doesn’t pick out each unique one, or the latest one, and so updates the property in VMM multiple times.  I have this script running as a scheduled task on the VMM server once a week and it is working great.

Examples: Using Get-VM and Where-Object

I had two cases last week where I needed to get a certain subset of VM’s and specified properties from Virtual Machine Manager.  The first scenario involved SQL servers, which due to our server naming convention was fairly easy.

The next scenario involved using a list of VM’s that was provided in a text file.  I initially struggled with this but eventually got it worked out using the two lines below.  You could also do it in one line but that gets kind of messy.