Advertisment

SQL Server UCP: Multi Instance Management

author-image
PCQ Bureau
New Update

LIn recent times, management at scale has been the mantra for many server products. The scale factor is — can I manage multiple instance of my server in the same way and at a centralized place? There are many products in the market that do this today and they have reached propositions where management of heterogeneous servers is also possible. SQL Server management studio (SSMS) so far has predominantly been a client tool application that managed a single instance. It has the registered server concept that gets little close to multi-server management, again as separate silos. In this article, we'll cover 'Utility Control Point (UCP)' that was delivered in SQL Server 2008 R2. Since there are many documentations and videos on the web related to this, this article will look at explaining the UCP around the factors that led to its incarnation.

Advertisment

Why and what is a Utility Control Point?

In any enterprise today, there are large numbers of databases (generally called departmental apps) which are not so sophisticated, and therefore require a less heavy but collective management entity. This means that the hardware remains underutilized as the organizations have larger number of applications that do not demand high sophisticated hardware like ERP or CRM. Utility Control Point shipped in SQL Server 2008 R2 gives a single point of reasoning in terms of instance and database application management. It provides an easy way to address the problems of underutilized hardware and overburdened administrators and the v1 scope would be targeted at the less sophisticated apps.

The biggest beast for overburdened administrators is the integrated experience for the application lifecycle management (ALM) (iterative process of develop, deploy and manage). This led to the birth of data-tier applications as a single composite entity representing an application unit which will be used to handshake across various stages of ALM. In simple terms, an example would be that, a DBA will not worry about the appropriate upgrade scripts in an ALM, but the DAC will dynamically generate the same. Similarly in the Utility Ccontrol Point, he can configure thresholds which will alert him appropriately about threshold violations.

So, to summarize, the SQL Server Utility, the central point of reasoning, models an organization's SQL Server-related entities in a unified view. Utility Explorer and SQL Server Utility viewpoints in (SSMS) provide administrators a holistic view of resource health through an instance of SQL Server that serves as a utility control point (UCP).

Advertisment

How is UCP different from competition?

UCP comes out of the box, is easy to setup, easy to use and it really acts as a centralized point of reasoning with great integrated experience for various personas. The picture below demonstrates the same. The UCP plays a vital role in the manage phase and provides a run-time management solution.

How do I get started?

Open SSMS that comes with enterprise edition of SQL Server 2008 R2. Go to the View menu and click on Utility Explorer and you will see a self-explanatory getting started page. You should be up and running an UCP in no time. Entities that can be viewed in the SQL Server UCP include:

-Instances of SQL Server

-Data-tier applications

-Database files

-Storage volumes

-File groups

The picture below shows how the UCP presents the CPU utilization and disk space utilization. The disk space utilization is aggregated through the storage hierarchy.

UCP, as a single point of reasoning, gives you pre-canned and configurable thresholds which help in great co-relational views. These views are both application centric and instance centric. This way, the DBA can do capacity planning, especially for storage and decide how many applications can exist on an instance without disrupting his or her throughput needs. Also, the UCP dashboards present a consistent visualization through appropriate drill downs. The picture below is a snapshot of the same. Dimensions are evaluated against the pre-defined or configured policies and the health state is an indication of how the dimensions are doing against the defined policy. Example — the user wants to configure his application (DAC) dimensions in such a way that beyond a threshold, his app is an over utilized state.

Summary

We just saw how SQL Server went about building the UCP in SQL Server 2008 R2. This explains a clear approach on their target audience in V1 and for sure there is a void in that space which the UCP addresses. We also saw a glimpse of how the UCP looks and what are its capabilities. Thus the DBAs, who can't afford expensive multi-server management tools in the market, can get rid of their great white boards, with this simple UCP experience.

Advertisment