Tuesday, June 27, 2017

SCOM 2016 - Moving the Operations Database

There may come a time where you require to move your Operational database from one machine to another. In my case I have outgrown my existing SQL server and need to move it to a server that has more headroom. This is going to be the first of two segments where I will cover the migration of the Operations, Data Warehouse and Reporting databases to an alternate SQL server.

At a high level the process is fairly straight forward.
  1. Stop the Operations Manager services
  2. Create the backup of the Ops Database
  3. Restore the backup of the Ops Database on new hardware
  4. Update the registry and configuration files
  5. Update the Database tables
  6. Update credentials in SQL
  7. Start Operations Manager services
Depending on how many management servers you have and how large the database is this process could take a while and monitoring will be down during this time, so plan accordingly. Lets get started

Stop the Operations Manager services:
On ALL of your management servers go to services and stop the following:
  • System Center Data Access
  • Microsoft Monitoring Agent
  • System Center Management Configuration
Create the backup of the Ops Database:
On the SQL server that currently hosts the Operations database log into SQL Server Management Studio with an administrator account. Expand the SQL Server then Databases. Right Click on OperationsManager (assuming you left the name default, select the appropriate db if not) then Tasks > Back Up...

The Back Up Database Wizard will start. On the General page, In the Database field verify that OperationsManager is selected. Otherwise grab the pulldown and choose the correct database. Backup type is Full, Backup component is Database, Back up to Disk and select an appropriate location to save to (I left it default).

On the Media Options Page Select Back up to new media set, and erase all existing backup sets. Give it a name and description. For Reliability select Verify backup when finished and Click OK

Depending on how large the database is this could take a while. You will get a success message when finished.

Tip: If you want to save some time in the following segment, repeat this step for OperationsManagerDW, SCOMReports and SCOMReportsTempDB. 

Copy all four of the .bak files you just created from the old SQL server to the new SQL server and you are ready for the next step.

Restore the backups onto new hardware:
Once you have copied the .bak files to the new SQL server open SQL Server Management Studio (on the new machine) Right Click on Databases and Select Restore Database...

When the Restore Database Wizard starts you will need to select device since the destination server has no backup history information you will not be able to select Database. Click on the ... and navigate to the location of the OperationsManager.bak file created earlier. Since we are moving to a new server leave everything else default and Click OK

Success is good

Verify that the database exists and it is running

Update the Registry and Configuration Files:
The following steps will need to be performed on ALL management servers to ensure they will be able to connect to the new SQL server successfully.
  1. Run regedit on the management servers from an elevated command prompt. Navigate to HKEY Local Machine > Software > Microsoft > System Center > 2010 > Common > Database. Change DatabaseServerName to the ServerName\InstanceName,PortNumber of the new SQL server. If you left it default, the PortNumber should be 1433.
  2. Now navigate to HKEY Local Machine > Software > Microsoft > Microsoft Operations Manager > 3.0 > Setup. Change DatabaseServerName to the ServerName\InstanceName,PortNumber of the new SQL server.
  3. Next go to %ProgramFiles%\System Center 2016\Operations Manager\Server\ Open the ConfigService.config file in notepad and change the following:
    1. <Category Name="Cmdb"> Change ServerName value to ServerName\InstanceName of the new SQL server. ChangePortNumber to 1433
    2. <Category Name="ConfigStore"> Change ServerName value to ServerName\InstanceName of the new SQL server, Change PortNumber to 1433
Update the Database Tables:
Now go back to SQL Server Management Studio on the new SQL Server. In Object Explorer expand Databases > OperationsManager > Tables

Right Click on dbo.MT_Microsoft$SystemCenter$ManagementGroup and Edit Top 200 Rows. Scroll to the right and find the column SQLServerName. Change it to the new SQL ServerName\Instance,PortNumber
Right Click on dbo.MT_Microsoft$SystemCenter$OpsMgrDB$AppMonitoring and Edit Top 200 Rows. Scroll to the right and find the column MainDatabaseServerName. Change it to the new SQL ServerName\Instance,PortNumber


Update Credentials in SQL:
Collapse Databases and expand Security > Logins. Add the following accounts and associated user mappings (if the account did not exist previously, the restore should have set these permissions for you):
  1. SCOM Read
    1. apm_datareader
    2. apm_datawriter
    3. db_datareader
    4. dwsynch_users
  2. SCOM Write
    1. db_datareader
    2. db_datawriter
    3. db_ddladmin
    4. dbmodule_users
  3. SCOM DAS
    1. ConfigService
    2. db_accessadmin
    3. db_datareader
    4. db_datawriter
    5. db_ddladmin
    6. db_securityadmin
    7. sdk_users
    8. sql_dependency_subscriber
Run the following command on the new SQL instance were the OperationsManager db is now running
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO

Then run the following SQL query
SELECT is_broker_enabled FROM sys.databases WHERE name='OperationsManager'
If the result of is_broker_enabled = 1 skip the following commands and go to the final step
If the result of is_broker_enabled = 0 run the following commands
ALTER DATABASE OperationsManager SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE OperationsManager SET ENABLE_BROKER
ALTER DATABASE OperationsManager SET MULTI_USER
Run the first command again and verify you are set to is_broker_enabled = 1, if not, rerun the commands

Start the Operations Manager Services:
On ALL of your management servers go to services and start the following:
  • System Center Data Access
  • Microsoft Monitoring Agent
  • System Center Management Configuration
Give it a minute for the Data Access service to initialize and open up the console. If everything was done properly you shouldn't get any errors. If you do then review the changes you made, specifically the areas where ServerName\Instance,PortNumber are involved. A typo in any of these places can prevent you from reconnecting to the database.

In the next segment we will move the Data Warehouse database


More to come!

If you like this blog give it a g+1




Contributing Documentation:
Create a Full Database Backup
Restore a Database Backup
How to configure Operations Manager to communicate with SQL Server

Wednesday, June 21, 2017

SCOM 2016 - Enable Proxy on all Agents

So if you are setting up a new instance of SCOM 2016 and have started getting Agent proxy not enabled alerts. You are not alone.

By default SCOM does not configure all new agents to act as proxy's for other agents. This is an odd thing that Microsoft for some reason has not corrected in the last four versions. Fortunately there is an easy way to fix this.

Open up PowerShellISE on your SCOM server and run the following command:
Get-SCOMAgent | where {$_.ProxyingEnabled.Value -eq $False}

This will give you a list of all the servers that do not have Proxy enabled in your environment.

To enable existing agents to run as proxy run the following:
Get-SCOMAgent | where {$_.ProxyingEnabled.Value -eq $False}|Enable-SCOMAgentProxy

If you run the first command again you should come back with no results. Change the $False to $True and it will give you a list of all the agents that are Proxy Enabled (should be all of them).

One final step. There is an easy way to enable proxy by default so you never have to set this again. Be sure to add the FQDN of your management server on the second line.
add-pssnapin “Microsoft.EnterpriseManagement.OperationsManager.Client”
new-managementGroupConnection -ConnectionString:FQDN of your management server
set-location “OperationsManagerMonitoring::”
Set-DefaultSetting -Name HealthService\ProxyingEnabled -Value True

Now every machine that gets added to the management group will be Proxy Enabled by default.


More to come!

If you like this blog give it a g+1

Monday, June 5, 2017

SQL 2014 SP2 - Configuring SSRS for SCOM 2016

Writing this as an update to a post I did a while back SQL 2008 R2 - Configuring SSRS for SCOM 2012. This post supersedes that one and should be used instead. There are a few changes in the process and it has been streamlined a bit.

Open SQL Server Reporting Services Configuration Manager. You will be prompted to connect to a server. Click Connect

On the Report Server Status page make sure that the status is Started. If it is not, start it and Apply.

On the Service Account page change the radio button to Use Another Account: Enter the SCOM Read account and password. Click Apply

In the Web Service URL page we can change the Virtual Directory (or leave it default). Click Apply. If you are successful the Report Server Web Services URLs link will become active.

Click on the link and you should see something similar to this. If you don't, repeat the previous step.

For Database, Click Change Database.

When the Report Server Database Configuration Wizard starts Select Create a new report server database and Click Next

Make sure your SQL server name is correct and Click Test Connection.

Assuming you have the name correct and the user account has access it should be successful. Click OK and then Click Next

You can change the report name or leave it default but I recommend giving it a clear name so you know what it is later. Click Next

Again, use the SCOM read account and Click Next

If you are happy with the settings Click Finish

Looks good! Click Finish

Click Apply

For Report Manager URL you can change the virtual directory or leave it default. Click Apply.

The URL link should become active. Click on it and you should see something similar to this.

For Email settings you can add your relay information or skip it if you are not ready to configure this yet. Click Apply

Execution account should also be SCOM read. Click Apply

We aren't using encryption keys so you can skip that. On Scale-Out Deployment you need to make sure that the Status is Joined. If not you will need to double check the execution account information to make sure it is correct.

More to come!

If you like this blog give it a g+1