Using Policy Based Management for checking SQL Server Versions

Problem

Brady Upton explains in his tip how to create a SharePoint KPI that will display SQL Server versions. In this tip we will show how to check SQL Server versions on multiple instances using Policy Based Management.

Solution

To get a list of the different versions of SQL Server, we can use SqlSecurity.com to find SQL Server build numbers for the latest Service Packs. Then we can create a policy that compares current SQL Servers’ versions with the latest Service Pack build. To evaluate the policy against all SQL Servers you can use Central Management Server or Registered Servers in SQL Server Management Studio (SSMS). In this tip we will evaluate the created policy against SQL Server 2005, SQL Server 2008 and SQL Server 2012.

For more info on Policy Based Management please review this tip: Using Policy Based Management in SQL Server 2008.

For this policy I am going to check the following versions and builds:

Create Conditions

We will create two conditions. One to use in the policy for server restriction (SQL Server 2005 or higher) and another one for actually checking the version and comparing it to the latest Service Pack build number.

Create the condition that will be used for the server restriction:

  • In SSMS go to the Management > Policy Management > Conditions
  • Right click “Conditions”, then click “New Condition…”:
    New Condition
  • Enter the name for the condition: “SQL Server 2005 or later”
  • Select “Server” facet
  • Select @VersionMajor field under “Expression” and add an expression to check if the version is higher than 9 (which is SQL Server 2005 or later):

    Condition 1

Create the condition that will be used in the policy to check the SQL Server version:

  • In SSMS go to the Management > Policy Management > Conditions
  • Right click “Conditions”, then click “New Condition…”
  • Enter the name for the condition: “SQL Server version check”
  • Select “Server” facet
  • Select the @BuildNumber field under “Expression”
Condition 2
  • Add the following expression:
     
    Condition expression

    ExecuteSql(‘Numeric’, ‘SELECT CASE
    WHEN LEFT(CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20)), CHARINDEX(”.”, CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20))) – 1) = ”10” THEN 4000
    WHEN LEFT(CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20)), CHARINDEX(”.”, CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20))) – 1) = ”9” THEN 5000
    WHEN LEFT(CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20)), CHARINDEX(”.”, CAST(SERVERPROPERTY (”ProductVersion”) as VARCHAR(20))) – 1) = ”11” THEN 3000
    END’)

  • Click “OK”
    Note: The expression currently checks if the following Service Packs installed: SQL Server 2005 SP 4, SQL Server 2008 R2 SP2, SQL Server 2012 SP1. This condition will have to be updated with the latest service pack build when the new Service Pack is released.
  • Click “OK” to save the condition.

Create Policy

  • In SSMS go to the Management > Policy Management > Policies:
  • Right click “Policies”, then click “New Policy…”:
    New Policy
  • Enter the name for the policy: “SQL Server Service Pack”
  • Select condition “SQL Server Version check” under “Check Condition”
  • Select condition “SQL Server 2005 or later” under “Server Restriction”

    Policy

  • Click “OK” to save the policy.

Evaluate the policy

You can use the Registered Servers in SSMS to evaluate the policy. Refer to this tip for more information.

You can also evaluate the policy using the Central Management Server. Refer to my previous tip for the details.

Review the results after you evaluated the policy:

Review results

Click on the “View…” link for the failed server under “Details” column:

Details

Next Steps

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *