Nowadays, one of my trendy topics @my customers is multi server management. I will make a few blog posts regarding on this topic. Firstly, even though it is very basic I will start with Central Management Server and Policy Based Management, and then merge them with Reporting Services. I will mainly refer http://epmframework.codeplex.com/ during these series. After going through with those topics I am planning to go with my custom PowerShell scripts that I use to manage SQL Servers effectively in a multi- server environment.
Let’s start with Central Management Server which is a feature coming with SQL 2008. http://technet.microsoft.com/en-us/library/bb934126(v=sql.100).aspx (But don’t worry if you still have SQL servers older than 2008. You can still register them under a central management server whose version is 2008. )
CMS is mainly used for running a query or a policy on more than one SQL Server instances at the same time. Registering is very straightforward in fact. Please just follow the steps below:
After registering your central management server, you can register a group (for ex you can group according to versions) or you can directly register a SQL instance under your central management server. I have chosen the 2nd option and skipped 4 define above. As a result you should have sth like the one below:
BE CAREFUL that only Windows authentication is permitted.
And now, let’s talk about Policy Based Management and then combine these two together. I am not going to go into detail with Policy Based Management Server, you can review the link for details. http://technet.microsoft.com/en-us/library/bb510667.aspx
Let me give you an example,
Our policy: A database has a full backup since yesterday.
If you evaluate this policy against a SQL instance, if you have a database that you have full backup older than yesterday, then the policy will be evaluated.
You can check many things from data-file location to your db options with policies. You can write your own policies even with your custom script, however not to go away from our main topic I will work with standard policies coming with SQL Server installation. When you expand Management branch on SSMS, you have a branch called “Policy Management”. To be able to use standard policies we have to import first. Please review the screen shot below:
After importing the policy, we can now evaluate it and see the results.
So far so good, right? But what if I have dozens of SQL instances and I want to evaluate a policy on all SQL instances at the same time?
Now, it is time to combine CMS and Policy Based Management together:
Just right click Registered Servers > Central Management Server > ISILEFE02 and then click import policies:
And import “Last Successful Backup Date” policy:
And here you go, just evaluate it and see your results on all SQL instances under your Central Management Server!
OK so far so good but still not perfect. Why not?
We can view the results by run time, what if we collected the results in a database regularly by a scheduled agent job and view the results via reports. It would be great, right?
And it is not that much work, please review my next blog post in this series.