Managing SQL Servers in multi-server environment via Central Management Server, Policy Management, Power Shell and Reporting Services - 1

  • Article

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:

  1. In SQL Server Management Studio, on the View menu, click Registered Servers.
  2. In Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
  3. In the New Server Registration dialog box, register the instance of SQL Server that you want to become the Central Management Server.
  4. In Registered Servers, right-click the Central Management Server, point to New, and then click New Server Group. Type a group name and description, and then click OK.
  5. In Registered Servers, right-click the Central Management Server group, and then click New Server Registration.
  6. In the New Server Registration dialog box, register one or more instances of SQL Server that you want to become members of the server group.

 

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.