Category: Cloud

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.