Tag: SQL Server

Step-by-Step – Upgrading to SCOM 2016 from SCOM 2012 R2

So you’ve decided to take the jump and upgrade to System Center Operations Manager 2016? Assuming you are running a SCOM 2012R2 (UR11) environment, this post will be exactly what you need to have a successful SCOM 2012 R2 to SCOM 2016 upgrade. **Although the recommended path is to upgrade from UR9**

In my environment, here is the quick breakdown:

  • SCOM 2012 R2 environment has two Management Servers.
    • Handful of clients (Windows 2012R2) being monitored.
  • Both Management Servers live on a Windows 2012 R2 operating system.
    • Both Management Servers are running with UR 11 (Update Rollup) **Microsoft recommends upgrading the environment at latest version number minus one (latest – 1), so in this case, UR9 (UR10 was never issued for SCOM 2012R2)**.
  • The SQL environment is a SQL Server 2014 SP2 also running on Windows 2012 R2.
    • The Operations and Data Warehouse live on dedicated SQL instances, however reside on the same server.

Pre-Upgrade Tasks

Let’s get started!

First, we need to do some “Pre-Upgrade” Tasks. Follow these in order.

  1. Back up the Operations Manager Databases
  2. Review the Operations Manager Event Logs
  3. Cleanup the Database (ETL Table)
  4. Remove Agents from Pending Management
  5. Disable the Notification Subscriptions
  6. Stop the Services or Disable any Connectors
  7. Verify that the Operational Database Has More Than 50 Percent Free Space
  8. Back up the Operations Manager Databases

To Cleanup the ETL Tables, you will need to run the following script:

  • The following script will determine the number of rows that will (need) be deleted:

DECLARE @SubscriptionWatermark bigint = 0;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();

Select COUNT (*)
FROM EntityTransactionLog ETL with(nolock)
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL with(nolock) WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL with(nolock) WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND EntityTransactionLogId < @SubscriptionWatermark;


  • Now we can go ahead and clean up the ETL table running the script below:

DECLARE @RowCount int = 1;
DECLARE @BatchSize int = 100000;
DECLARE @SubscriptionWatermark bigint = 0;
DECLARE @LastErr int;

SELECT @SubscriptionWatermark = dbo.fn_GetEntityChangeLogGroomingWatermark();
WHILE(@RowCount > 0)
FROM EntityTransactionLog ETL
WHERE NOT EXISTS (SELECT 1 FROM EntityChangeLog ECL WHERE ECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND NOT EXISTS (SELECT 1 FROM RelatedEntityChangeLog RECL WHERE RECL.EntityTransactionLogId = ETL.EntityTransactionLogId)
AND ETL.EntityTransactionLogId < @SubscriptionWatermark;

SELECT @LastErr = @@ERROR, @RowCount = @@ROWCOUNT;



Great! Let this run, which may take a few minutes, or hours depending on your environment..

Now we are ready to get started with the upgrade. (Don’t get to backup your databases (Report Server, Operations, Data Warehouse!!)


Upgrading to SCOM 2016

After you have downloaded the RTM image file, as you may have noticed, it is not an ISO file. So let’s extract the file contents locally…


Before moving on with the install, I stopped the following SCOM services on all of the Management Servers:

  1. Microsoft Monitoring Agent (healthservice)
  2. System Center Data Access Service (OMSDK)
  3. System Center Management Configuration (cshost)

Now we can run the installer (Run As Administrator or, SCOM Data Access/SDK account)






Sweet! Although this was expected, since no new changes were required for Windows Server 2012 R2.


I cannot stress how useful it is to use dedicated service accounts. Here input your Data Access/SDK account.


Once quick review before we begin the Upgrade..


Let this run… For me, the upgrade took around 50 minutes for the first Management Server..


Sweet! All good. Remember to install the license key before the 120 days are up.

Let’s launch the console just to make sure we are in all working order.


Great! Now we will need to repeat the process for the second/other Management servers…

Once complete, let’s upgrade our client agents to SCOM 2016 (v 8.0.10918.0)


At this time we can make use of the SCOM 2016 features, and update our out-dated Management Packs. Remember this feature really only works for Microsoft based Management Packs, ie. SQL, Windows Server, Client OS, etc.



I hope this helped! For additional information, and or upgrading other items such as Gateways, ACS (Audit Collection Services), etc. Please visit Microsoft’s guides HERE.


Lastly, it is highly recommended to upgrade to SCOM 2016 Update Rollup 1 (UR1). For that guide, please visit this LINK.


Happy SCOM’ing 2016!


Configuring Memory Consumption for SQL Server 2012 R2

Naturally (by default) SQL will consume all the memory available within its environment. There are times some applications require a minimum of memory allocated to the SQL instance before you can proceed with the application install. Below is how you can configure the minimum and maximum memory allocation your SQL instance can consume.

For starters, I am going to assume you understand how much memory (RAM) is available to your SQL server, and you have done the math behind any other SQL instances that may be within your SQL server…

In my case, I am going to ensure my application will always have a minimum of 12GB of memory, and a maximum of 14GB. The server itself has 16GB, and I will leave 2GB for the OS.

Launch your SQL Server Manager > right-click on the named instance > select Properties > select the Memory tab > define your settings here.




That is it!