[Note from Pinal]: This is a 12th episode of Notes from the Fields series. When taking a vacation the biggest worry any DBA has it that in their absence what will happen to their database. They are often worried that something will go wrong with their server or some users will change something without their permission and knowledge. This keeps on them so much worried that even though they take vacations they keep on looking at the phone or email continuously. Here is a simple trick which DBA can implement and take their vacation without worrying about their database.
In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very common issue DBAs, and Developer faces in their career – how to be proactive and manage database policies before they are violated. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.
In this week’s tip from the field were going to cover Policy Based Management. Policy Based Management (PBM) allows you to import and build policies that could be enforced or checked to validate that your SQL Server Farm is compliant with best practices. Today, were going to show you how to import best practice policies. In a future tip, we will show you how you can leverage Central Management Server and Policy Based Management together to validate best practice settings against your SQL Server farm.
In order to use Policy Based Management you will need to enable it. In this case PBM is enabled, but this would how you would enable it incase it’s not already enabled on your instance.
For those of you who are familiar with SQL Server Best Practices Analyzer you will noticed that several of these checks have been included as policies that can be imported. We are going to take a look at how you can import some of these policies.
Policy Based Management can be accessed via SSMS under the management node for an instance as shown below. Right click on policies and select Import Policy.
Once you click on import you will see the following window. When you click on the ellipse next to “Files to import” you will see the folder by default is pointed to the folder that includes the default best practices shown below.
You will notice that there are several best practices policies. Today, we are going to import just the Database Auto Close policy.
Once we import the following policy we will see it inside the policies tree in SSMS.
You will notice that there is a red arrow on the Database Auto Close policy we just imported. This is because the policy evaluation isn’t scheduled. You can manually evaluate it or schedule a SQL Agent job to run on a schedule to evaluate the policy.
In this tip, were going to execute manually. This is done by right clicking on the policy and selecting evaluate. You will see that the “Database Auto Close” policy was evaluated for every user database on the instance.
If you enjoyed this tip from the field, checkout several other posts on Policy Based Management.
Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
