A centralized, unified view of my SQL Server 2008 R2 entities, please?

When you have a lot of SQL Server instances, the job to manage them efficiently gets harder. Things like new databases needing more space, backups and other maintenance jobs to be set up, cpus running hot etc.

If your not using some homemade, or 3rd party tool, it tends to get more and more “sisyfos-work” alike. (Sisyfos, you know the greek fellow who got the quite hard punishment to having to push a quite big rock uphills, just so find when he’s finished, that the stone rolled back down there – infinite loop kind of way). Microsoft has finally done some work to try to leverage that problem. Making you see what is happening and how the instances are configured. Letting you handle many instances in a single way. Seing all in one place. Let’s dig into that a bit.

First we need to learn som new lingo:

Managed Server Group – a group of SQL Server instances handled through dashboard viewpoints in a Control Point – Nice to have a single point of view into the environment. Still a bit hard to overview it though, when the instances go a bit over the hundreds, but it’s a start.
Utility Control Point – The one of your SQL Server instances that holds the focal point, the collection of utilization and configuration data.
The Utility Explorer – The tool where you manage your server group and where you look at your control point/managed group. A Data-tier Application (DAC) – The DAC is a project template, a container that defines and packages database schema and deployment requirements

Let’s elaborate a bit further on each subjects: The managed groups are layered:

– The data-tier applications managed by the organization.
– SQL Server instances used by the organization.
– HW – the hardware resources used, disks, RAM etc

The utility control point have it’s own schema, jobs, polices and management data warehouse to handle this. And it’s easily created with a “Create Control Point”-wizard. Then you can enroll your instances with the “Enroll Instance”-wizard. This starts a managed server group collection set up, to be collected every 15 minutes to the UCP. Or you can manage a greater batch load more efficiently with powershell. Finally you can watch your pie charts, sliding gauges and other graphic clickable representations and inspect the resource health of your armada in terms of over- or underutilized cpu and space. The DAC captures your application schema (stored procedures, functions, tables, views etc) from within Mgm studio, from an existing application as well as from a new built one into a single file, single unit of deployment, which accelerates changes, upgrades, and deployments. It also handles versioning, so you can have an applications lifecycle under control. You use endpoint names to not tieing the objects to specific instances and at deploy time you can specify what a specific endpoint actual servername is for example, letting you move the code between different resources seamlessly. It handles SQL Server 2000, SQL Server 2005, and SQL Server 2008 to SQL Server 2008 R2 objects, including server level as logins.

Hey, it can even create those new version files from say a 2000 source to a 2008 R2 destination. You can further deploy from 2008 R2 to the cloud, as can the developers from Visual Studio 2010. Did I mention that it is a wizard for DAC as well, of course. Now I only need an assignment on the top shops in amount of installed instances so I can test the limits a bit further. A short summary With your familiar tools, and with help from the new wizards described in this post, the DAC, the control point, the managed server group and the utility explorer you now can gain control of the big installations where you have loads of instances swimming around to support your application pools.

/Jonas Bergström