Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Monitor Your SQL Server Linked Server Configurations with Policy Based Management


By:   |   Last Updated: 2010-01-20   |   Comments (1)   |   Related Tips: More > Policy Based Management

Problem

You need to quickly ascertain if any linked servers for your SQL Servers have had recent configuration changes. In this tip we cover the step by step process for setting up a policy to check for when there have been any linked server changes to your servers.

Solution

SQL Server 2008 introduced Policy Based Management (PBM) which you can use to determine which linked servers have had recent configuration changes.  Below is the step by step process to set this up. 

If you are not familiar with Policy Based Management (PBM) you can review these articles:


PBM contains a facet named 'Linked Server', and this facet has a property named 'DateLastModified'. You can use this property to build your condition and policy. Right click on policies and select 'New policy...'

In the name field enter in 'Linked Server Modified Check Policy', then click on the arrow in the Check condition dropdown and select 'New condition...':

In the name field enter 'Linked Server Modified Check Condition'. For the Facet select 'Linked Server', then click on the 'Field' dropdown. Select 'DateLastModified', set the operator to be less than or equal to, and then click on the far right ellipses.

In this screen you will want to enter in the following statement:

DateAdd('day', -7, GetDate())

Click 'OK' twice, you should see the following:

Your policy is created, next up is to evaluate, right click on the policy and select evaluate:


Result

Here is a screenshot of a sample result set returned by the policy.


Next Steps


Last Updated: 2010-01-20


next webcast button


next tip button



About the author
MSSQLTips author Thomas LaRock Thomas LaRock is a Head Geek at SolarWinds and a Microsoft Certified Master, Microsoft Data Platform MVP, VMware vExpert, and a former Microsoft Certified Trainer with over 20 years’ experience.

View all my tips
Related Resources




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Wednesday, January 20, 2010 - 12:34:52 PM - SankarReddy Back To Top

Its a good one introducing the PBM. Another way to look at this data is running the query below using Multiple Server Query Execution.

SELECT nameproductdata_sourcemodify_date FROM sys.servers

Sankar Reddy | SankarReddy.com


Learn more about SQL Server tools