SCOM 2012: How to move databases – specially when you did an upgrade from SCOM 2007 R2 before

If you want to prepare the upgrade from System Center Operations Manager 2012 RTM to SP1 you perhaps also need to move the database(s) to another SQL server because the old system is not supported anymore (SP1 only supports SQL servers installed on Windows Server 2008 R2 and above!).

In this case you will try to follow the given procedures from Microsoft:
How to move the Operational Database http://technet.microsoft.com/en-us/library/hh278848.aspx
How to move the Data Warehouse Database http://technet.microsoft.com/en-us/library/hh268492.aspx

The problem is, that the documentation is missing some steps. And if you move a SCOM 2012 database which was upgraded before from SCOM 2007 R2 you will have different table names. The documentation only references the table names which are created during a fresh SCOM 2012 installation.

Additional: If you have installed the databases in named instances and/or use different ports than the standard ports, then you also need to enter that.

Assuming you have the following setup:

New database server:
OperationsManager database: SQLServer1\SQL1
OperationsManager DW database, ReportServer database, ReportServerTempDB: SQLServer1\SQL2

Here is the list of additional steps or differences you can follow:

OMDB Step 6: Perform on all management servers.

Open up regedit and change the entry of the following keys to 
\\HKLM\SOFTWARE\Microsoft\System Center\2010\Common\Database\DatabaseServerName
Value: SQLServer1\SQL1

\\HKLM\SOFTWARE\Microsoft\System Center\2010\Common\Database\DataWarehouseDBServerName
Value: SQLServer1\SQL2

OMDB Step 7: Perform on all management servers.

Open up %ProgramFiles\System Center 2012\Operations Manager\Server\ConfigService.config.

Search and replace all entries of the old sql server name with the new server name incl. instance name!
There should be two entries: one within the “Cmdb” and one in the “ConfigStore” category.
Value: SQLServer1\SQL1

OMDB Step 8: Perform on OperationsManager database.

Change the entry of the field SQLServerName_6B1D1BE8_EBB4_B425_08DC_2385C5930B04 in the following table. If you upgraded from SCOM 2007 R2 before then use the SCOM 2007 R2 table name!

SCOM 2012 RTM: dbo. MT_Microsoft$SystemCenter$ManagementGroup
SCOM 2007 R2: dbo.MT_ManagementGroup

Value: SQLServer1\SQL1

OMDB Step9: Only talks about the server name. Please remember to enter the instance name!
Value: SQLServer1\SQL1

OMDB Step 13: The listed logins do not cover the server action account. Please also check the permissions for this account on the OperationsManager database! The account should have db_datareader, db_datawriter, db_dlladmin and dbmodule_users.

OMDB Step 14: Perform on OperationsManager database.

Do not paste all 4 lines into your query. Perform them separated; otherwise you will get the error “Incorrect syntax near ‘sp_configure’.
First execute:
sp_configure ‘show advanced options’, 1
reconfigure
Then execute:
sp_configure ‘clr enabled’, 1
reconfigure

DWDB Step 6: Perform on reporting server.

Open up regedit and change the entry of the following keys to 

\\HKLM\Software\Microsoft\System Center Operations Manager\12\Reporting\DWDBInstance
Value: SQLServer1\SQL2

\\HKLM\Software\Microsoft\Microsoft Operations Manager\3.0\Reporting\DWDBInstance
Value: SQLServer1\SQL2

DWDB Step 10: Perform on OperationsManager database.

Change the entry of the field MainDatabaseServerName_2C77AA48_DB0A_5D69_F8FF_20E48F3AED0F in the following table. If you upgraded from SCOM 2007 R2 before then use the SCOM 2007 R2 table name!

SCOM 2012 RTM: dbo. MT_Microsoft$SystemCenter$DataWarehouse
SCOM 2007 R2: dbo.MT_DataWarehouse

Value: SQLServer1\SQL2

DWDB Step11 and Step12: Both only talk about the server name. Please remember to enter the instance name!
Value: SQLServer1\SQL2

So, if you have followed all steps explained in the Microsoft procedures and also checked the additional steps/information here, then your move should be successful.

MSCE Private Cloud

Yes, I made it. I am now a Microsoft Certified Solution Expert – Private Cloud. Doesn’t that sound good?!

I attended a one week power workshop at Firebrand training and passed both exams (70-247 and 70-246).
It was a hard week and I am glad that it is over, but I learned a lot about Service Manager, VMM and DPM. Tools I only had seen in presentations and videos before.
Greetings to my fellow participants!

Special thanks to Firebrand, who helped me pass this certification. And particularly to our trainer Jens Gilges who really has a very broad Microsoft and Cisco knowledge and could answer all of our questions.

Firebrand has a nice competition at the moment where you can win training for your whole life.

Check here: http://www.firebrandtraining.de/FTFL/AEJ. (This links to the German web page, but they also have representations in a lot of other countries => check “Über Uns” and then “International”).

SCOM 2012: Specified cast is not valid error

I got an error message in SCOM 2012 (RTM) today when I tried to open up an existing Run As Account to distribute it to a new server. The error message was this: “System.InvalidCastException: Specified cast is not valid.” The error only appeared on one account. The distribution tab of the Run As Account was empty. It looks like the account is corrupted.

So what to do? I found this forum entry http://social.technet.microsoft.com/Forums/en-US/operationsmanagergeneral/thread/952fc370-c438-4d43-ba78-a89dac697fa4/ and followed the steps.
And it solved it! Great! Thanks for the solution!

So here are the steps:

1. Get the servernames which use this Run As Account – best would be if you have a group or view for it. I was lucky that I had a view, where I could get all server names through copy and past into Excel.
2. Create a new Run As Account with the same account and password as the one which is corrupted. You can name it as you like, because you can later rename it.
3. Distribute the account to all servers you have listed.
4. Assign the new Run As Account to all profiles where the old account is in. I simply edited the old entry and selected the new Run As Account. Therefore you do not need to select the class again, etc.
5. Now you can delete the old Run As Account. And if you do not want to change your documentation, then you also can rename the new Run As Account to the old name.

Orchestrator 2012: Check SCCM maintenance window and set SCOM maintenance mode

Everyone who uses System Center Configuration Manager 2012 and System Center Operations Manager 2012 knows the problem of setting the server into maintenance mode when patching or software deployment needs to take place.

With System Center Orchestrator 2012 you get the integration packs for both systems and the option to create a workflow for this task. My intetion for this was to use the maintenance windows which are defined on the collections. During this timeframe software updates and deployments can be performed on the servers incl. reboots. So it would be good to set the servers into maintenance mode in SCOM. I only focussed on general maintenance mode windows not OSD ones and non recurring windows.

Here is the summary of the workflow I have created:
The workflow runs every 2 minutes. It reads a text file on the runbook server with all collection ids it should check, then checks if the collection has a maintenance window defined, that will start within the next 10-15 minutes. If yes, then it gets the collection members in SCCM, gets the FQDN for the server and starts the maintenance mode in SCOM. If successful it writes a log file otherwise it tries again to set the maintenance mode with the Netbios name.

Diagram:

Most of the parts are standard activities, so I only describe the “Get Maintenance Window” activity, which runs a PowerShell script on the Runbook server. This activity needs to run with a user that has SCCM permissions, otherwise it will provide no result. It only will have output data, if the maintenance window will occur within the next 10-15 minutes. So the link to the Get Collection Members activity should have the following include entry: Pure Output from Get Maintenance Window matches pattern .+

Here is the command line for the Get Maintenance Window activity:

cmd.exe /c | c:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe –c “function WMI-DateStringToDate($time) {  [System.Management.ManagementDateTimeconverter]::ToDateTime($time);};$collsettings = ([WMIClass] ‘\\SCCM Server FQDN\root\SMS\site_SCCMSiteCode:SMS_CollectionSettings’).CreateInstance();if($collsettings -is [Object]){$collsettings.CollectionID = ‘Link to Line Text of previous activity’;$collsettings.get();$windows=$collsettings.ServiceWindows;if ($windows -is [Object]){$now=Get-Date;Foreach ($window in $windows){$Time=WMI-DateStringToDate($window.StartTime);if (($window.IsEnabled -eq $True) -and ($window.ServiceWindowType -eq ’1′) -and ($window.RecurrenceType -eq ’1′)){if (($now.AddMinutes(15).compareto($Time) -eq ’1′) -and ($now.AddMinutes(10).compareto($Time) -eq ‘-1′)){$Duration=$window.Duration+15;write-host ($Time.ToString(),$Duration) -separator ‘;’}}}}};”

Attention! The command line should not have line breaks! Otherwise it will not work within this activity.
For better readability I post the script here also with line breaks and comments:

param($SMSSiteCode, $SMSManagementServer, $COLLECTION_ID)
# convert WMI date to DateTime format
function WMI-DateStringToDate($time)
{ [System.Management.ManagementDateTimeconverter]::ToDateTime($time)} 
# get collection settings (incl. Maintenance Windows)
$collsettings= ([WMIClass] \\$SMSManagementServer\root\SMS\site_$($SmsSiteCode):SMS_CollectionSettings).CreateInstance() 
if($collsettings -is [Object])
{
$collsettings.CollectionID =$COLLECTION_ID
$collsettings.get()
$windows=$collsettings.ServiceWindows
if ($windows -is [Object])
{
$now=Get-Date
Foreach ($window in $windows)
{
$Time=WMI-DateStringToDate($window.StartTime)
# only check general maintenance and non recurring windows
if (($window.IsEnabled -eq$True) -and ($window.ServiceWindowType -eq’1′) -and ($window.RecurrenceType -eq’1′))
{
# check if starttime is within the next 10-15 min.
if (($now.AddMinutes(15).compareto($Time) -eq’1′) -and ($now.AddMinutes(10).compareto($Time) -eq‘-1′))
{
# add 15 min to duration as buffer
$duration=$window.Duration+15;
write-host ($Time.ToString(),$Duration) -Separator ‘;’
}
}
}
}
}

Another thing to mention: Please add an exclude to the link between “Get Collection Member” and “Get FQDN” for your Management Servers: Member Name from Get Collection Member equals SCOMMGServerName.
Then they will not be set into maintenance mode if they are members of the checked collections.

Orchestrator 2012: Reset SCOM 2012 monitor for closed alert

Everyone who works with System Center Operations Manager 2012 knows the problem of closed alerts where the monitor has not been reset first. The monitor will stay in the unhealthy state and no new alerts will be created anymore until the monitor gets reset.

You can create a scheduled task with a script on a management server or use Orchestrator for it. I found this blog which describes how to use the ”Monitor alert” activity and then run a script afterwards. http://blog.scomfaq.ch/2012/05/05/reset-monitor-using-scom-2012-and-orchestrator-a-must-have-runbook/
I like the “Monitor alert” activity but I would like to reduce the number of scripts which connect to the management group.

So I have created another runbook.

The first activity “Check every 5 min” triggers the runbook every 5 min. I think that is a good timeframe to check for closed alerts.

The next activity “Reset Monitor” runs on the Runbook server. It uses PowerShell and imports the SCOM 2012 module, so this must be installed on the Runbook Servers and the execution policy should be set to remotesigned.

Here are the details of the activity:

$Alertname=@();
$State=@();
$Displayname=@();
# Import Operations Manager Module and create Connection
Import-Module OperationsManager;
New-SCOMManagementGroupConnection %ManagementServerName%;
$alerts=get-scomalert -Criteria “Severity!=0 AND IsMonitorAlert=1 AND ResolutionState=255″| where {$_.LastModified -ge ((get-date).AddMinutes(-5)).ToUniversalTime()}
if ($alerts -is [object])
{
foreach ($alert in $alerts)
{
$monitoringobject = Get-SCOMClassinstance -id $alert.MonitoringObjectId
# Reset Monitor
If (($monitoringobject.HealthState -eq ‘Error’) -or ($monitoringobject.HealthState -eq ‘Warning’))
{
$monitoringobject.ResetMonitoringState()
$State+=$monitoringobject.HealthState
$Displayname+=$monitoringobject.displayname
$Alertname+=$alert.Name
}
}
}

The script gets all closed alerts from monitors with severity ‘Warning’ or ’Critical’ within the last 5 min and only resets the monitor if it is still in ‘Error’ or ‘Warning’ HealthState. You could use this script also for a scheduled task on a management server.

The published data is Alertname, State, Displayname, you could also publish other data, but that was what I needed for troubleshooting.

Orchestrator: Get FQDN activity

Sometimes you need to get the FQDN of a computer within a runbook for the following activity (example: SCOM – Start Maintenance Mode). Most activities provide only the Netbios name (example: Get Computer IP/Status).

I have a simple Run Program activity that utilizes PowerShell to get that information.

It runs on the computer for which you would like to get the FQDN.

Command:
cmd.exe /c | c:\Windows\system32\WindowsPowerShell\v1.0\powershell.exe –c “[System.Net.Dns]::GetHostEntry(‘%Netbios computername from previous activity%‘).hostname”

In the following activity you only need to use the Pure Output from “Get FQDN”, which is now the FQDN of the computer.

You can also use the Run .Net  Script activity, that runs on the Runbook server.  

Then you only need to select PowerShell as the script language and enter $FQDN=[System.Net.Dns]::GetHostEntry(‘%Netbios computername from previous activity%‘).hostname as script. Publish the variable FQDN in Published Data and you can use this variable in the next activity.

SCOM 2012: Alert history grooming fix available

I have updated my blog about the alert history grooming bug in System Center Operations Manager 2012 with the information how this can be fixed.
You can find it here.

SCOM: Disable Active Directory integration on an agent with PowerShell

Some companies use Active Directory integration for agent assignement in System Center Operations Manager. In some circumstances it can be that you have to remove the Active Directory integration from the agent (example: do not use AD integratrion on domain controllers or Exchange servers), perhaps if you have used software distribution without different options for special server classes or if you want to get rid of AD integration.

I have written a PowerShell script, that can be run on an agent to remove the AD integration and reenter the management group(s) as manual.

$object=New-Object-ComObject‘AgentConfigManager.MgmtSvcCfg’;
if ($object-is [Object])
{
#only change agent if active directory integration is enabled
if($object.GetActiveDirectoryIntegrationEnabled())
{
#get all ad integrated management groups
$MGs=$object.GetManagementGroups() | where {$_.IsManagementGroupFromActiveDirectory -eq $True};
$object.DisableActiveDirectoryIntegration();
$object.ReloadConfiguration();
Foreach($MG in $MGs)
{
$object.AddManagementGroup($MG.managementGroupName,$MG.ManagementServer,$MG.managementServerPort);
}
}
}
& net stop healthservice
& net start healthservice

SCOM2007/2012: Momadadmin change

The companies which use AD integration for assigning agents to the correct servers in SCOM use the tool momadadmin.exe which is provided with the Operations Manager source files  to prepare active directory for agent assignement.

There is a small change in how the commandline needs to look like compared between SCOM 2007 R2 and SCOM 2012 RTM.

SCOM 2007 R2:

Usage: MomADAdmin ManagementGroupName MOMAdminSecurityGroup {RootManagementServe
r | RunAsAccount} Domain
Also look at: System Center Operations Manager 2007 Unleashed page 387.

SCOM 2012:

Usage: MomADAdmin ManagementGroupName MOMAdminSecurityGroup RunAsAccount Domain
Also look at: http://technet.microsoft.com/en-us/library/hh212738.aspx

A lot people perhaps used the root management server in the past to assign permissions to the OperationsManager folder in AD. In SCOM 2012 you can only enter a RunAsAccount to assign permissions to the OperationsManager folder in AD. So select the appropriate account – if you do not use the server action account then enter the account which you have defined in the “Active Directory Based Agent Assignment Account” profile.

If you upgrade from SCOM 2007 R2 to SCOM 2012 RTM then remember to check the permissions in the OperationsManager folder in AD or recreate the folder with momadadmin so that correct permissions are set.

Orchestrator 2012: Undo Runbook Checkout

I recently had an issue with a Runbook, that I checked out in th Runbook Designer. The problem was that I had to reboot the machine where the Runbook Designer was running on. The result was, that the Runbook Designer lost the current session and I could not see the Runbook anymore in the designer after the restart.

It was a really long Runbook and I didn’t wanted to recreate it. So how can I undo the checkout now without having the Runbook in the Runbook Designer?

I checked directly in the Orchestrator database.

SELECT [UniqueID]
      ,[Name]
      ,[CheckOutUser]
      ,[CheckOutTime]
      ,[CheckOutLocation]
     FROM [Orchestrator].[dbo].[POLICIES]
  where Name  = ‘Check server access’

Here is the result:

UniqueID	Name	CheckOutUser	CheckOutTime	CheckOutLocation
A6541640-14AD-4AE5-86F5-2C3416152E35	Check server access	S-1-5-21-57989841-1960408961-725345543-2108	00:02.0	abcdefg

You can see that the fields ChechOutUser, CheckOutTime and CheckOutLocation have entries. CheckedIn Runbooks have the value NULL in these fields.

So I opened up the table with Edit mode and replaced the entries in the three CheckOut* fields with NULL.

Now I reloaded the Runbook Designer and my missing Runbook was there again.