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%.

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. =)

(more…)