Learn more about SQL Server tools

solving sql server problems for millions of dbas and developers since 2006
join MSSQLTips for free SQL Server tips














































Using Policy Based Management for checking SQL Server Versions

MSSQLTips author Svetlana Golovko By:   |   Read Comments (3)   |   Related Tips: More > Policy Based Management
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


Last Update: 2/4/2013


About the author
MSSQLTips author Svetlana Golovko
Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips
Related Resources


print tip Print  
Become a paid author




Recommended For You








Learn more about SQL Server tools
Comments and Feedback:
Monday, February 04, 2013 - 6:03:13 PM - David Read The Tip

Svetlana,

 

I enjoyed the article and worked through setting it up. What would be nice to see is being able to evaluate SQL 2008 and SQL 2008 R2 by themseleves as they are at different levels. Can you check the level using '10.00' and '10.50'. That would ge great.

 

David


Tuesday, February 05, 2013 - 10:45:15 AM - Svetlana Golovko Read The Tip

Hi David,

This is a good point. Thank you!

It's pretty easy to add another version. To add SQL Server 2008 SP3 to the check you can change the expression to this:

ExecuteSql('Numeric', 'SELECT CASE
WHEN LEFT(CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20)), CHARINDEX(''.'', CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20))) + 2) = ''9.00'' THEN 5000
WHEN LEFT(CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20)), CHARINDEX(''.'', CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20))) + 2) = ''10.00'' THEN 5500
WHEN LEFT(CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20)), CHARINDEX(''.'', CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20))) + 2) = ''10.50'' THEN 4000
WHEN LEFT(CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20)), CHARINDEX(''.'', CAST(SERVERPROPERTY (''ProductVersion'') as VARCHAR(20))) + 2) = ''11.0.'' THEN 3000
END')


Tuesday, March 19, 2013 - 6:04:25 PM - Sumon Basu Read The Tip

Svetlana,

Thanks for your post. I tried to implement the policy in my environment, where maximum of the instances are named. In some of the servers I am getting an error "cannot configure property..... [ExecuteSql statement]..... because the result is not deterministic". I tried to fix the issue, but was unable to do the same. Can you please help.

Thanks & Regards,

Sumon



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates

Signup for our newsletter


Comments
*Enter Code refresh code


 
Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.