Using Policy Based Management for checking SQL Server Versions

By: Svetlana Golovko   |   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:

• SQL Server 2005 - SP4 (9.00.5000)
• SQL Server 2008 - SP2 (10.00.4000 or 10.50.4000 for SQL 2008 R2)
• SQL Server 2012 - SP1 (11.00.3000)

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…":
  
  
• 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):
  
  
  

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"

• Add the following 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…":
  
• 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"
  

  

• 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:

Click on the "View..." link for the failed server under "Details" column:

Next Steps

• Test and install the latest SQL Server Service Pack on the servers identified by policy evaluation.
• When the next Service Pack is available - update the policy with the new build number for this Service Pack.
• Read the tip about Central Management Server setup: "Execute SQL Server query on multiple servers at the same time".
• Read more tips on Policy Based Management.

About the author

DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

View all my tips

We Recommend

• More SQL Server DBA Tips...

Print

Tweet

Become a paid author

Comments and Feedback:

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 *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code