OMS SQL Assessment Solution

First things, first, what is the SQL Assessment Solution? OMS SQL Assessment Solution does exactly what it sounds like, it assesses a given SQL environment, providing a health check and risk assessment. The solution executes on a fixed (for now) interval monitoring and evaluates your SQL environment.

With the solution, it provides six focus areas, where it allows you and your SQL team(s) to understand where your environment may need attention either soon, or immediately. The focus areas provide recommendations based on Microsoft’s KB and Microsoft’s engineers across multiple environments, industries and scenarios. These recommendations are suggested in order to get your environment back in good standing.

Six Focus Areas:

  1. Security and Compliance
  2. Availability and Business Continuity
  3. Performance and Scalability
  4. Upgrade, Migration and Deployment
  5. Operations and Monitoring
  6. Change and Configuration Management

Each focus area will break down its recommendations based on a weighted system. The weighted system is based on three metrics: Impact, Probability and Effort.

Each metric can be broken down as follows:

  • The Impact of the issue on your organization if it does cause a problem. The higher the impact equates to a larger overall score for the recommendation.
  • The Probability that an issue identified will cause problems within the environment. The higher the probability equates to a larger overall score for the recommendation.
  • The Effort required to implement the suggested recommendation. A higher effort equates to a smaller overall score for the recommendation.

For example, if the “Schedule full database backups at least weekly,” is weighted with 4.0, this means after implementing the recommendations and satisfying the assessment, this will improve our SQL assessment score from 88% to 92%, an overall increase of 4%.

temp

Implementing the OMS Solution

To get the SQL Assessment Solution implemented, you will obviously need a SQL environment to monitor, and its Microsoft Monitoring Agent (MMA) either configured to OMS, or the agent/server a member of the OMS server group with SCOM.

Here are the steps you will need to follow to configure the SQL Run As account in the SCOM console:

Note, the Run As account you will be using, needs to be a member of the Local Administrators group on all of the Windows Servers hosting the SQL Server Instances.

  1. In SCOM, go to the Administrations tab
  2. Under the Run As Configuration, click Accounts
  3. Create the Run As Account, following through the Wizard, creating a Windows account
    1. Under Distribution Security, select More secure
  4. Go back to the Run As Configuration and click Profiles
  5. Search for the SQL Assessment Profile
  6. Assuming you are using SCOM 2012 R2 UR7, the profile name should be, “Micorsoft System Center Advisor SQL Assessment Run As Profile
  7. Right click and update its properties, and add the recently created Run As Account we just created in step 3
  8. Now you need to add the Run As account to the SQL database, and grant it the permissions it will need. Use the SQL code below, this will need to be executed on all SQL instances you are interested in incorporating into the SQL Assessment Solution. (I used this from the OMS documentation site; link can be found at the bottom of this blog)

---
    -- Replace "DOMAIN\UserName" with the actual user name being used as Run As Account (removing the quotes).
    USE master

    -- Create login for the user, comment this line if login is already created.
    CREATE LOGIN ["DOMAIN\UserName"] FROM WINDOWS

    -- Grant permissions to user.
    GRANT VIEW SERVER STATE TO ["DOMAIN\UserName"]
    GRANT VIEW ANY DEFINITION TO ["DOMAIN\UserName"]
    GRANT VIEW ANY DATABASE TO ["DOMAIN\UserName"]

    -- Add database user for all the databases on SQL Server Instance, this is required for connecting to individual databases.
    -- NOTE: This command must be run anytime new databases are added to SQL Server instances.
    EXEC sp_msforeachdb N'USE [?]; CREATE USER ["DOMAIN\UserName"] FOR LOGIN ["DOMAIN\UserName"];'

Once you have implemented the steps above, and assuming everything went successfully, soon, with OMS, you will see your SQL environment under the SQL Assessment Solution.

Hopefully there isn’t too much to fix. =)

SQL Assessment OMS

(more…)

Advertisements

Connecting Operations Management Suite (OMS) to SCOM

Assuming you are now equipped SCOM 2012 R2 UR7 the following procedure below to link your SCOM environment to your OMS workspace should be almost identical. If you are running on SCOM 2012 R2 UR6, then the steps below are similar, however please note, Microsoft was still referring to OMS as Operational Insights. With the UR7 update, the name was changed to Operations Management Suite.

For starters, I am going to assume you have an OMS workspace created and an Azure subscription. I am also going to assume you have a SCOM 2012 R2 (UR6 or higher) and some servers within your SCOM environment.

  • To begin, you will need to launch the Operations Manager console and go to the Administration tab/pane.
  • Within the Administration settings, select the Operations Management Suite (or Operational Insights on UR6).
  • Expand Operations Management Suite, and select connection.
  • Select Configure Operations Management Suite and follow the wizard instructions.
  • After you have successfully paired SCOM with OMS you can now add servers to your OMS Computer Group. Select Add a Computer/Group.

1 (3)

  • Within the Search, find the servers you want to add to the OMS group

2 (3)

  • Select the servers/computers, and hit OK
  • Once the servers have been added, you will now be able to see them within the Managed Computers, under the Operations Management Suite node

3 (3)

  • Now if you go back to the Operations Management Suite web portal, and select Connected Sources, under your settings, you should not only see your SCOM Management Group name, but also see the servers we have now added to the OMS group via SCOM.

4 (3)

What is Operations Management Suite (OMS)?

By now, many are wondering what the heck is OMS? This past May, Microsoft introduced Operations Management Suite or more commonly known as OMS. OMS is a cloud based management solution, that allows you to collect and mine big data and perform solutions such as analytics, automation, capacity planning and much, much more! The solutions offered today offer a vast insight into your environments.
OMS uses a cloud-first approach that allows you to manage your data centre and/or environments with simple and meaningful dashboards. Regardless if your servers reside within Azure or AWS, or On-Premises, or are Windows Servers or Linux, OMS couldn’t care less, OMS can still manage your environment(s).


Building dashboards with previous System Center tools was never easy, definitely not that intuitive, and very cumbersome. Well, this is where OMS shines!


In a nut shell, if your server has access to the Internet (or has a SCOM environment) linking OMS to the environment is essentially a, “next, next, finish” approach.

8 (1)

It seems OMS is adding solutions every other week, or at least tweaking the current solutions already implemented. This is very exciting, as Microsoft is continuously improving their product(s).

OMS as of today is able to offer some of the following solutions/scenarios:

  • Log Analytics
  • Automation
  • Capacity Planning
  • Security and Audit
  • Availability
  • Change Tracking
  • SQL Assessment
  • AD Assessment
  • Azure Site Backup & Recovery
  • Network Analytics
  • and, more!

Next post, I will show you how to get OMS and your environment chatting.

Step-by-Step – SCOM 2012 R2 Update Rollup 7 (UR7) Install Procedure

My personal notes,  UR7 has a lot of security fixes, and it is highly recommended to upgrade your lab/Dev environments first before upgrading your Production environment(s). The step by step procedures below are the steps I took and in no way shape or form do I accept responsibility for any data loss, and/or issues within your environment. It is advised to always take a backup of your SQL databases and/or snapshots of your SCOM environment(s). Please take these notes as suggestions. Always refer to Microsoft’s KB (posted above) for full documentation steps.

Here are the key updates for UR7 (source Microsoft):

Issues that are fixed in this update rollup can be found here, https://support.microsoft.com/kb/3064919

Once you are ready to begin your upgrade, it is recommend you do the following server/roles in the order below:

  1. Install the update rollup package on the following server infrastructure:
  • Management server or servers
  • Gateway servers
  • Web console server role computers
  • Operations console role computers
  1. Apply SQL scripts.
  2. Manually import the management packs.
  3. Apply the agent update to manually installed agents, or push the installation from the Pending view in the Operations console.

Once you have downloaded the rollup files, I like to extract and only keep the language I need, in this case, ENU (English). You will need to install these with Administrative rights, I like to use PowerShell as Local Administrator. It really does frustrate me, as there is no indication that the rollup installed correctly, (other than looking at the file version number change via File Explorer).

1 (2)

2 (2)

Once the rollups are installed, you will now need to apply the SQL scripts. First update the Data Warehouse, then followed by the OpsMgr DB.

The scripts can be found here, “C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\SQL Script for Update Rollups\”

Please note, the user executing these scripts needs to have read and write permissions to the database(s).

3 (2)

5 (2)

4 (2)

Once you have successfully executed the SQL scripts, you will now need to import the updated Management Packs. These MPs can be found here, “C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\Management Packs for Update Rollups\”.

You will need to import the following MPs, please see below:

6 (1)

Once the MPs have been imported, you should now go back to your Pending Management view, under the Administrations pane, and update all servers.

7 (1)

And that is that! You are now on the latest System Center release for SCOM 2012 R2.

(more…)

Management Pack Backup Automation

Backup, backup, backup. It is never a bad idea to be safe and back up your data, or in this case your Management Packs. I recently created an automated, scheduled task that runs every Monday morning that backs up all the Management Packs within the environment. Please note, all sealed and un-sealed Management Packs will be backed up in a un-sealed format.

The following PowerShell code I have used:


$a = get-date
$a = $a.ToString("yyyy-MM-dd")
$rootMS = gc env:computername

Import-Module OperationsManager
New-SCOMManagementGroupConnection -ComputerName $rootMS

$path = New-Item -ItemType directory -Path "\\somepath\MPBackup\$a"

Get-SCOMManagementPack | Export-SCOMManagementPack -Path $path.FullName

Maintenance Mode History with SQL

Unfortunately SCOM 2012R2 does not have a native report and/or view that allows you quickly view the maintenance history on a specific server or servers. This handy SQL query I have used many times over to get the history of a given server or servers to find out when the machine entered MM (Maintenance Mode). Using the query below, run against the OperationsManager (or Data Warehouse) DB, and specify the server(s) you are interested with the date range:

---
USE OperationsManagerDW
SELECT ManagedEntity.DisplayName, MaintenanceModeHistory.*
FROM ManagedEntity WITH (NOLOCK)
INNER JOIN
MaintenanceMode ON ManagedEntity.ManagedEntityRowId = MaintenanceMode.ManagedEntityRowId
INNER JOIN
MaintenanceModeHistory ON MaintenanceMode.MaintenanceModeRowId = MaintenanceModeHistory.MaintenanceModeRowId

WHERE DisplayName Like 'server%.domain.net' AND ScheduledEndDateTime BETWEEN 'fromDateRange' AND 'toDateRange'

Wintel Gray Agents Runbook Automation

This Orchestrator Runbook, “SCOM2012R2_Check_HealthService” is setup to capture a “Health Service Heartbeat Failure” for Windows machines, and restart the HealthService and/or delete the corrupted HealthService cache folder and restart the service.

The Runbook will capture the alert from SCOM, once captured, it will wait 60 seconds, it will then ping the machine, and if the ping is successful then it will then wait for 180 seconds, then check to see if the HealthService on the machine is running. If the ping is unsuccessful, it will send an email indicating the machine is actually offline.

If the HealthService is running, then it is possibly a corrupted cache folder. It will then stop the HealthService, delete the cache folder, and restart the service.

If the HealthService is not running, it will then start the service.

In both events, an email will be sent out as an information alert, to indicate that the Runbook resolved the issue.

1

Details of Configuration

Monitor Alert Properties:

2

Link from Monitor Alert to Run Program:

3

Link from Run Program to Get HealthService Status:

4

Link from Get HealthService Status.

If Not running:

5

 Start HealthService Properties:

6

Since the Stop HealthService Properties are almost the same as Start HealthService, we have omitted this.

Delete Folder Properties

This pertains to SCOM 2012R2. There is a duplicate run book with the same configuration that checks against the old folder structure:

7

8