Virtualisation for the DBA part 2 – SQL Server Management
Given the almost mandatory drive to server virtualisation in data centres, what do you need to worry about and what’s in SQL Server to help you?
The biggest worry is probably trying to manage everything, as typically many organisations simply create a virtual machine for every physical server they have and them lump them onto a fewer number of newer more powerful servers. so you still have as many instances to look after as before.
SQL Server 2008 introduced 3 new tools to help manage multiple instances of SQL Server:
Central management Servers allow you to register servers and collect them into groups to use with T-SQL command and the other tools below:
Policy Based Management (PBM) allows you to define a facet of SQL Server you want to manage, establish what condition you want it in, and then create a policy to encapsulate this and whether you or not you want to simply report on how things are now or to enforce that policy on the target servers (note not all conditions and facets can be enforced). For example you could write a policy that check to see that all of your databases were backed up over the last week, or setup a policy to only allow stored procedures to be created if they have a usp_ prefix to annoy your developers. a final thing to note is that the best practice analyser in SQL Server 2008 is simply a set of polices which you can install, and a final final note is that
Powershell. Powershell is built into Windows Server , Windows , exchange and SQL Server 2008 It’s batch files on steroids or more accurately a .net based scripting language. One of my standard demos is to use this to invoke a policy across a group of servers defined in a text file, put the results into another SQL Server table, and have a custom report to show the results. There is a good example of what is possible on Codeplex – the Enterprise Policy Management Framework..
The other good thing about what these tools can do is that you only need one installation of SQL Server 2008 to manage the other earlier version of SQL Server you may have, without having to use any other tools from Microsoft or anyone else.