Chapter 2: Multi-Server Administration

Over the years, an increasing number of organizations have turned to Microsoft SQL Server because it embodies the Microsoft Data Platform vision to help organizations manage any data, at any place, and at any time. The biggest challenges organizations face with this increase of SQL Server installations have been in management.

This chapter is taken from book "Introducing Microsoft SQL Server 2008 R2" by Ross Mistry and Stacia Misner published for Microsoft Press.

Over the years, an increasing number of organizations have turned to Microsoft SQL Server because it embodies the Microsoft Data Platform vision to help organizations manage any data, at any place, and at any time. The biggest challenges organizations face with this increase of SQL Server installations have been in management.

With the release of Microsoft SQL Server 2008 came two new manageability features, Policy-Based Management and the Data Collector, which drastically changed how database administrators managed SQL Server instances. With Policy-Based Management, database administrators can centrally create and enforce polices on targets such as SQL Server instances, databases, and tables. The Data Collector helps integrate the collection, analysis, troubleshooting, and persistence of SQL Server diagnostic information. When introduced, both manageability features were a great enhancement to SQL Server 2008. However, database administrators and organizations still lacked manageability tools to help effectively manage a multi-server environment, understand resource utilization, and enhance collaboration between development and IT departments.

SQL Server 2008 R2 addresses concerns about multi-server management with the introduction of a new manageability feature, the SQL Server Utility. The SQL Server Utility enhances the multi-server administration experience by helping database administrators proactively manage database environments efficiently at scale, through centralized visibility into resource utilization. The utility also provides improved capabilities to help organizations maximize the value of consolidation efforts and ensure the streamlined development and deployment of data-driven applications.

The SQL Server Utility

The SQL Server Utility is a breakthrough manageability feature included with SQL Server 2008 R2 that allows database administrators to centrally monitor and manage database applications and SQL Server instances, all from a single management interface. This interface, known as a Utility Control Point (UCP), is the central reasoning point in the SQL Server Utility. It forms a collection of managed instances with a repository for performance data and management policies. After data is collected from managed instances, Utility Explorer and SQL Server Utility dashboard and viewpoints in SQL Server Management Studio (SSMS) provide administrators with a view of SQL Server resource health through policy evaluation and analysis of trending instances and applications throughout the enterprise.

The following entities can be viewed in the SQL Server Utility:

• Instances of SQL Server
• Data-tier applications
• Database files
• Volumes

Figure 2-1 shows one possible configuration using the SQL Server Utility, which includes a UCP, many managed instances, and a workstation running SSMS for managing the utility and viewing the dashboard and viewpoints. The UCP stores configuration and collection information in both the UMDW and msdb databases.

FIGURE 2-1 A SQL Server Utility Control Point (UCP) and managed instances

REAL WORLD

Many organizations that participate in the Microsoft SQL Server early adopter program are currently either evaluating SQL Server 2008 R2 or already using it in their production infrastructure. The consensus is that organizations should design a SQL Server Utility solution that factors in a SQL Server Utility with every deployment. The SQL Server Utility allows you to increase visibility and control, optimize resources, and improve overall efficiencies within your SQL Server infrastructure.

SQL Server Utility Key Concepts

Although many database administrators may be eager to implement a UCP and start proactively monitoring their SQL Server environment, it is beneficial to take a few minutes and become familiar with the new terminology and components that make up the SQL Server Utility.

• The SQL Server Utility This represents an organization's SQL Server-related entities in a unified view. The SQL Server Utility supports actions such as specifying resource utilization policies that track the utilization requirements of an organization. Leveraging Utility Explorer and SQL Server Utility viewpoints in SSMS can give you a holistic view of SQL Server resource health.
• The Utility Control Point (UCP) The UCP provides the central reasoning point for the SQL Server Utility by using SSMS to organize and monitor SQL Server resource health. The UCP collects configuration and performance information from managed instances of SQL Server every 15 minutes. Information is stored in the Utility Management Data Warehouse (UMDW) on the UCP. SQL Server performance data is then compared to policies to help identify resource bottlenecks and consolidation opportunities.
• The Utility Management Data Warehouse (UMDW) The UMDW is a relational database used to store data collected by managed instances of SQL Server. The UMDW database is automatically created on a SQL Server instance when the UCP is created. Its name is sysutility_mdw, and it utilizes the Simple Recovery model. By default, the collection upload frequency is set to every 15 minutes, and the data retention period is set to 1 year.
• The Utility Explorer user interface A component of SSMS, this interface provides a hierarchical tree view for managing and controlling the SQL Server Utility. Its uses include connecting to a utility, creating a UCP, enrolling instances, deploying data-tier applications, and viewing utilization reports affiliated with managed instances and data-tier applications. You launch Utility Explorer from SSMS by selecting View and then choosing Utility Explorer.
• The Utility Explorer dashboard and list views These provide a summary and detailed presentations of resource health and configuration details for managed instances of SQL Server, deployed data-tier applications, and host resources such as CPU utilization, file space utilization, and volume space utilization. This allows superior insight into resource utilization and policy violations and helps identify consolidation opportunities, maximizes the value of hardware investments, and maintains healthy systems. The utility dashboard is depicted in Figure 2-2.

FIGURE 2-2 The SQL Server Utility dashboard

UCP Prerequisites

As with other SQL Server components and features, the deployment of a SQL Server UCP must meet the following specific prerequisites and requirements:

• The SQL Server version running the UCP must be SQL Server 2008 R2 or higher. (SQL Server 2008 R2 is also referred to as version 10.5.)
• The SQL Server 2008 R2 edition must be Datacenter, Enterprise, Evaluation, or Developer.
• The SQL Server system running the UCP must reside within a Windows Active Directory domain.
• The underlying operating system must be Windows Server 2003, Windows Server 2008, or Windows Server 2008 R2. If Windows Server 2003 is used, the SQL Server Agent service account must be a member of the Performance Monitor User group.
• It is recommended that the collation settings affiliated with the Database Engine instance hosting the UCP be case-insensitive.

NOTE The Database Engine instance is the only component that can be managed by a UCP. Other components, such as Analysis Services and Reporting Services, are not supported.

After all these prerequisites are met, you can deploy the UCP. However, before installing the UCP, it is beneficial to size the UMDW accordingly and understand the maximum capacity specifications associated with a UCP.

UCP Sizing and Maximum Capacity Specifications

The wealth of information captured during capacity planning sessions can help an organization better understand its environment and make informed decisions when designing the UCP implementation. In the case of the SQL Server Utility, it is helpful to know that each SQL Server UCP can manage and monitor up to 100 computers and up to 200 SQL Server Database Engine instances. Both computers and instances can be either physical or virtual. Additional UCPs should be provisioned if there is a need to monitor more computers and instances.

Disk space consumption is another area you should look at in capacity planning. For instance, the disk space consumed within the UMDW is approximately 2 GB of data per year for each managed instance of SQL Server , whereas the disk space used by the msdb database on the UCP instance is approximately 20 MB per managed instance of SQL Server. Last, a SQL Server UCP can support up to a total of 1,000 user databases.

Creating a UCP

The UCP is relatively easy to set up and configure. You can deploy it either by using the Create Utility Control Point Wizard in SSMS or by leveraging Windows PowerShell scripts. The high-level steps for creating a UCP include specifying the instance of SQL Server in which the UCP will be created, choosing the account to run the utility control set, ensuring that the instance is validated and passes the conditions test, reviewing the selections made, and finalizing the UCP deployment.

Although the setup is fairly straightforward, the following conditions must be met to successfully deploy a UCP:

• You must have administrator privileges on the instance of SQL Server.
• The instance of SQL Server must be SQL Server 2008 R2 or higher.
• The SQL Server edition must support UCP creation.
• The instance of SQL Server cannot be enrolled with any other UCP.
• The instance of SQL Server cannot already be a UCP.
• There cannot be a database named sysutility_mdw on the specified instance of SQL Server.
• The collection sets on the specified instance of SQL Server must be stopped.
• The SQL Server Agent service on the specified instance must be started and configured to start automatically.
• The SQL Server Agent proxy account cannot be a built-in account such as Network Service.
• The SQL Server Agent proxy account must be a valid Windows domain account on the specified instance.

Creating a UCP by Using SSMS

It is important to understand how to effectively use the Create Utility Control Point Wizard in SSMS to create a SQL Server UCP. Follow these steps when using SSMS:

1. In SSMS, connect to the SQL Server 2008 R2 Database Engine instance in which the UCP will be created.
2. Launch the Utility Explorer by selecting View and then selecting Utility Explorer.
3. On the Getting Started tab, click the Create A Utility Control Point (UCP) link or click the Create Utility Control Point icon on the Utility Explorer toolbar.
4. The Create Utility Control Point Wizard is now invoked. Review the introduction message, and then click Next to begin the UCP creation process. If you want, you can select the Do Not Show This Page Again check box.
5. On the Specify The Instance Of SQL Server page, click the Connect button to specify the instance of SQL Server in which the new UCP will be created, and then click Connect in the Connect To Server dialog box.
6. Specify a name for the UCP, as illustrated in Figure 2-3, and then click Next to continue.
   
   
   
   FIGURE 2-3 The Specify The Instance Of SQL Server page
   
   NOTE Using a meaningful name is beneficial and easier to remember, especially when you plan on implementing more than one UCP within your SQL Server infrastructure. For example, to easily distinguish between multiple UCPs you might name the UCP that manages the production servers "Production Utility" and the UCP for Test Servers "Test Utility." When connected to the UCP, users will be able to distinguish between the different control points in Utility Explorer.
7. On the Utility Collection Set Account page, there are two options available for identifying the account that will run the utility collection set. The first option is a Windows domain account, and the second option is the SQL Server Agent service account. Note that the SQL Server Agent service account can only be used if the SQL Server Agent service account is leveraging a Windows domain account. For security purposes, it is recommended that you use a Windows domain account with low privileges. Indicate that the Windows domain account will be used as the SQL Server Agent proxy account for the utility collection set, and then click Next to continue.
8. On the next page, the SQL Server instance is compared against a series of prerequisites before the UCP is created. Failed conditions are displayed in a validation report. Correct all issues, and then click the Rerun Validation button to verify the changes against the validation rules. To save a copy of the validation report for future reference, click Save Report, and then specify a location for the file. To continue, click Next. 
   
   NOTE As mentioned in the prerequisite steps before these instructions, SQL Server Agent is, by default, not configured to start automatically during the installation of SQL Server 2008 R2. Use the SQL Server Configuration Manager tool to configure the SQL Server Agent service to start automatically on the specified instance.
9. Review the options and settings selected on the Summary Of UCP Creation page, and click Next to begin the installation.
10. The Utility Control Point Creation page communicates the steps and report status affiliated with the creation of a UCP. The steps involve preparing the SQL Server instance for UCP creation, creating the UMDW, initializing the UMDW, and configuring the SQL Server Utility collection set. Review each step for success and completeness. If you wish, save a report on the creation of the UCP operation. Next, click Save Report and choose a location for the file. Click Finish to close the Create Utility Control Point Wizard.

Creating a UCP by Using Windows PowerShell

Windows PowerShell can be used instead of SSMS to create a UCP. The following syntax (available in the article "How To: Enroll an Instance of SQL Server (SQL Server Utility)," online at http://msdn.microsoft.com/en-us/library/ee210563(SQL.105).aspx), illustrates how to create a UCP with Windows PowerShell. You will need to change the elements inside the quotes to reflect your own desired arguments.

NOTE When working with Windows Server 2008 R2, you can launch Windows PowerShell by clicking the Windows PowerShell icon on the Start Menu taskbar. For more information on SQL Server and Windows PowerShell, see "SQL Server PowerShell Overview" at http://msdn.microsoft.com/en-us/library/cc281954.aspx.

$UtilityInstance = new-object –Type Microsoft.SqlServer.Management.Smo.Server
"ComputerName\UCP-Name";
$SqlStoreConnection = new-object –Type
Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection
$UtilityInstance.ConnectionContext.SqlConnectionObject;
$Utility =
[Microsoft.SqlServer.Management.Utility.Utility]::CreateUtility("Utility",
$SqlStoreConnection, "ProxyAccount", "ProxyAccountPassword");

UCP Post-Installation Steps

When the Create Utility Control Point Wizard is closed, the Utility Explorer is invoked, and you are automatically connected to the newly created UCP. The UCP is automatically enrolled as a managed instance. The data collection process also commences immediately. The dashboards, status icons, and utilization graphs associated with the SQL Server Utility display meaningful information after the data is successfully uploaded.

NOTE Do not become alarmed if no data is displayed in the dashboard and viewpoints in the Utility Explorer Content pane; it can take up to 45 minutes for data to appear at first. All subsequent uploads generally occur every 15 minutes.

A beneficial post-installation task is to confirm the successful creation of the UMDW. This can be done by using Object Explorer to verify that the sysutility_mdw database exists on the SQL Server instance. At this point, you can modify database settings-such as the initial size of the database, autogrowth settings, and file placement-based on the capacity planning exercises discussed in the "UCP Sizing and Maximum Capacity Specifications" section earlier in this chapter.

Enrolling SQL Server Instances

After you have established a UCP, the next task is to enroll an instance or instances of SQL Server into a SQL Server Control Point. Similar to deploying a Utility Control Point, this task is accomplished by using the Enroll Instance Wizard in SSMS or by leveraging Windows Power-Shell. The high-level steps affiliated with enrolling instances into the SQL Server UCP include choosing the UCP to utilize, specifying the instance of SQL Server to enroll, selecting the account to run the utility collection set, reviewing prerequisite validation results, and reviewing your selections. The enrollment process then begins by preparing the instance for enrollment. The cache directory is created for the collected data, and then the instance is enrolled into the designated UCP.

IMPORTANT A UCP created on SQL Server 2008 R2 Enterprise can have a maximum of 25 managed instances of SQL Server. If more than 25 managed instances are required, then you must utilize SQL Server 2008 R2 Datacenter.

Managed Instance Enrollment Prerequisites

As with many of the other tasks in this chapter, certain conditions must be satisfied to successfully enroll an instance:

• You must have administrator privileges on the instance of SQL Server.
• The instance of SQL Server must be SQL Server 2008 R2 or higher.
• The SQL Server edition must support instance enrollment.
• The instance of SQL Server cannot be enrolled with any other UCP.
• The instance of SQL Server cannot already be a UCP.
• The instance of SQL Server must have the utility collection set installed.
• The collection sets on the specified instance of SQL Server must be stopped.
• The SQL Server Agent service on the specified instance must be started and configured to start automatically.
• The SQL Server Agent proxy account cannot be a built-in account such as Network Service.
• The SQL Server Agent proxy account must be a valid Windows domain account on the specified instance.

Enrolling SQL Server Instances by Using SSMS

The following steps should be followed when enrolling a SQL Server instance via SSMS:

1. In Utility Explorer, connect to the desired SQL Server Utility (for example, Production Utility), expand the UCP, and then select Managed Instances.
2. Right-click the Managed Instances node, and select Enroll Instance.
3. The Enroll Instance Wizard is launched. Review the introduction message, and then click Next to begin the enrollment process. If you want, you can select the Do Not Show This Page Again check box.
4. On the Specify The Instance Of SQL Server page, click the Connect button to specify the instance of SQL Server to enroll in the UCP.
5. Supply the SQL Server instance name, and then click Connect in the Connect To Server dialog box.
6. Click Next to proceed. The Utility Collection Set Account page is invoked.
7. There are two options available for specifying an account to run the utility collection set. The first option is a Windows domain account, and the second option is the SQL Server Agent service account. You can use the SQL Server Agent service account only if the SQL Server Agent service account is leveraging a Windows domain account. For security purposes, it is recommended that you use a Windows domain account with low privileges. Specify the Windows domain account to be used as the SQL Server Agent proxy account for the utility collection set, and then click Next to continue.
8. As shown in Figure 2-4, a series of conditions will be evaluated against the SQL Server instance to ensure that it passes all of the prerequisites before the instance is enrolled. If there are any failures preventing the enrollment of the SQL Server instance, correct them and then click Rerun Validation. To save the validation report, click Save Report and specify a location for the file. Click Next to continue.
   
   
   
   FIGURE 2-4 The SQL Server Instance Validation screen
9. Review the Summary Of Instance Enrollment page, and then click Next to enroll your instance of SQL Server.
10. The following actions will be automatically completed on the Enrollment Of SQL Server Instance page: the instance will be prepared for enrollment, the cache directory for the collected data will be created, and the instance will be enrolled. Review the results, and click Finish to finalize the enrollment process.
11. Repeat the steps to enroll additional instances.

Enrolling SQL Server Instances by Using Windows PowerShell

Windows PowerShell can also be used to enroll instances. In fact, scripting may be the way to go if there is a need to enroll a large number of instances into a SQL Server UCP. Let's say you need to enroll 200 instances, for example. Using the Enroll Instance Wizard in SSMS can be very time consuming, because the wizard is a manual process in which you can enroll only one instance at a time. In contrast, you can enroll 200 instances with a single script by using Windows PowerShell. The following syntax illustrates how to create a UCP by using Windows PowerShell. Change the elements in the quotes to match your environment.

$UtilityInstance = new-object -Type Microsoft.SqlServer.Management.Smo.Server
"ComputerName\UCP-Name";
$SqlStoreConnection = new-object –Type
Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection
$UtilityInstance.ConnectionContext.SqlConnectionObject;
$Utility =
[Microsoft.SqlServer.Management.Utility.Utility]::Connect($SqlStoreConnection);
$Instance = new-object -Type Microsoft.SqlServer.Management.Smo.Server
"ComputerName\ManagedInstanceName";
$InstanceConnection = new-object –Type
Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection
$Instance.ConnectionContext.SqlConnectionObject;
$ManagedInstance = $Utility.EnrollInstance($InstanceConnection, "ProxyAccount",
"ProxyPassword");

The Managed Instances Dashboard

After you have enrolled all of your instances associated with a UCP, you can review the Managed Instances dashboard, as illustrated in Figure 2-5, to gain quick insight into the health and utilization of all of your managed instances. The Managed Instances dashboard is covered in Chapter 5, "Consolidation and Monitoring."

FIGURE 2-5 The Managed Instances dashboard

Managing Utility Administration Settings

After you are connected to a UCP, use the Utility Administration node in the Utility Explorer navigation pane to view and configure global policy settings, security settings, and data warehouse settings across the SQL Server Utility. The configuration tabs affiliated with the Utility Administration node are the Policy, Security, and Data Warehouse tabs. The following sections explore the Utility Administration settings available within each tab. You must first connect to a SQL Server UCP before modifying settings.

Connecting to a UCP

Before managing or configuring UCP settings, a database administrator must connect to a UCP by means of Utility Explorer in SSMS. Use the following procedure to connect to a UCP:

1. Launch SSMS and connect to an instance of SQL Server.
2. Select View and then Utility Explorer.
3. On the Utility Explorer toolbar, click the Connect To Utility icon.
4. In the Connect To Server dialog box, specify a UCP instance, and then click Connect.
5. After you are connected, you can deploy data-tier applications, manage instances, and configure global settings.

NOTE It is not possible to connect to more than one UCP at the same time. Therefore, before attempting to connect to an additional UCP, click the Disconnect From Utility icon on the Utility Explorer toolbar to disconnect from the currently connected UCP.

The Policy Tab

You use the Policy tab to view or modify global monitoring settings. Changes on this tab are effective across the SQL Server Utility. You can view the Policy tab by connecting to a UCP through Utility Explorer and then selecting Utility Administration. Select the Policy tab in the Utility Explorer Content pane. Policies are broken down into three sections: Global Policies For Data-Tier Applications, Global Policies For Managed Instances, and Volatile Resource Policy Evaluation. To expand the list of values for these options, click the arrow next to the policy name or click the policy title.

Global Policies For Data-Tier Applications

Use the first section on the Policy tab, Global Polices For Data-Tier Applications, to view or configure global utilization policies for data-tier applications. You can set underutilization or overutilization policy thresholds for data-tier applications by specifying a percentage in the controls on the right side of each policy description. For example, it is possible to configure underutilized and overutilized settings for CPU utilization and file space utilization for data files and logs. Click the Apply button to save changes, or click the Discard or Restore Default buttons as needed. By default, the overutilized threshold is 70 percent, and the underutilized threshold is 0 percent.

Global Policies For Managed Instances

Global Policies For Managed Instances is the next section on the Policy tab. Here you can set global SQL Server managed instance application monitoring policies for the SQL Server Utility. As illustrated in Figure 2-6, you can set underutilization and overutilization thresholds to manage numerous issues, including processor capacity, file space, and storage volume space.

FIGURE 2-6 Modifying global policies for managed instances

Volatile Resource Policy Evaluation

The final section on the Policy tab is Volatile Resource Policy Evaluation. This section, displayed in Figure 2-7, provides strategies to minimize unnecessary reporting noise and unwanted violation reporting in the SQL Server Utility. You can choose how frequently the CPU utilization policies can be in violation before reporting the CPU as overutilized. The default evaluation period for processor overutilization is 1 hour; 6 hours, 12 hours, 1 day, and 1 week can also be selected. The default percentage of data points that must be in violation before a CPU is reported as being overutilized is 20 percent. The options range from 0 percent to 100 percent.

FIGURE 2-7 Volatile resource policy evaluation

The next set of configurable elements allows you to determine how frequently CPU utilization polices should be in violation before the CPU is reported as being underutilized. The default evaluation period for processor underutilization is 1 week. Options range from 1 day to 1 month. The default percentage of data points that must be in violation before a CPU is reported as being underutilized is 90 percent. You can choose between 0 percent and 100 percent.

To change policies, use the slider controls to the right of the policy descriptions, and then click Apply. You can also restore default values or discard changes by clicking the buttons at the bottom of the display pane. 

REAL WORLD

Let's say you configure the CPU overutilization polices by setting the Evaluate SQL Server Utility Polices Over This Moving Time Window setting to 12 hours and the Percent Of SQL Server Utility Polices In Violation During The Time Window Before CPU Is Reported As Overutilized setting to 30 percent. Over 12 hours, there will be 48 policy evaluations . Fourteen of these must be in violation before the CPU is marked as over utilized.

The Security Tab

From a security and authorization perspective, there are two security roles associated with a UCP. The first role is the Utility Administrator, and the second role is the Utility Reader. The Utility Administrator is ultimately the "superuser" who has the ability to manage any setting or view any dashboard or viewpoint associated with the UCP. For example, a Utility Administrator can enroll instances, manage settings in the Utility Administration node, and much more. The second security role is the Utility Reader, which has rights to connect to the SQL Server Utility, observe all viewpoints in Utility Explorer, and view settings on the Utility Administration node in Utility Explorer.

You can use the Security tab in the Utility Administration node of Utility Explorer to view and provide Utility Reader privileges to a SQL Server login. By default, logins that have sysadmin privileges on the instance running the UCP automatically have full administrative privileges over the UCP. A database administrator must use a combination of both Object Explorer and the Security Tab in Utility Administration to add or modify login settings affiliated
with the UCP.

For example, the following steps grant a new user the Utility Administrator role by creating a new SQL Server login that uses Windows Authentication:

1. Open Object Explorer in SSMS, and expand the folder of the server instance that is running the UCP in which you want to create the new login.
2. Right-click the Security folder, point to New, and then select Login.
3. On the General page of the Login dialog box, enter the name of a Windows user in the Login Name box.
4. Select Windows Authentication.
5. On the Server Roles page, select the check box for the sysadmin role.
6. Click OK.

By default, this user is now a Utility Administrator, because he or she has been granted the sysadmin role.

The next example will grant a standard SQL Server user the Utility Reader read-only privileges for the SQL Server Utility dashboard and viewpoints.

1. Open Object Explorer in SSMS, and expand the folder of the server instance that is running the UCP in which you want to create the new login. For this example,SQL2K8R2-01\test2 will be used.
   
   MORE INFO Review the article "CREATE LOGIN (Transact-SQL)" at the following link for a refresher on how to create a login in SQL Server: http://technet.microsoft.com/en-us/library/ms189751.aspx.
2. Right-click the Security folder, point to New, and then select Login.
3. On the General page, enter the name of a Windows user in the Login Name box.
4. Select Windows Authentication.
5. Click OK.
   
   NOTE Unlike in the previous example, do not assign this user the sysadmin role on the Server Role page. If you do, the user will automatically become a Utility Administrator and not a Utility Reader on the UCP.
6. In Utility Explorer, connect to the UCP instance in which you created the login (SQL2K8R2-01\Test2).
7. Select the Utility Administration node, and then select the Security tab in the Utility Explorer Content pane.
8. Next to the newly created user (SQL2K8R2-01\Test2), as shown in Figure 2-8, grant the Utility Reader privilege, and then click Apply.

FIGURE 2-8 Configuring read-only privileges for the SQL Server Utility

REAL WORLD

Many organizations have large teams managing their SQL Server infrastructures because they have hundreds of SQL Server instances within their environment. Let's say you wanted to grant 50 users the read-only privilege for the SQL Server Utility dashboard and viewpoints. It would be very impractical to grant every single database administrator the read-only privilege. Therefore, if you have many database administrators and you want to grant them the read-only role for the SQL Server Utility within your environment, you can take advantage of a Role Based Access model to streamline the process. For example, you can create a security group within your Active Directory domain called Utility Readers and then add all the desired database administrators and Windows administrator accounts into this group. Then in SSMS, you create a new login and select the Active Directory security group called Utility Readers. The final step involves adding the Utility Reader role to the Utility Reader security group on the Security tab in the Utility Administration node within Utility Explorer. By following these steps, you provide access to all of your database administrators in a fraction of the time. In addition, the use of RBA makes it quite easier to manage the ongoing maintenance of security of the SQL Server Utility.

The Data Warehouse Tab

You view and modify the data retention period for utilization information collected for managed instances of SQL Server on the Data Warehouse tab in the Utility Administration node in Utility Explorer. In addition, the UMDW Database Name and Collection Set Upload Frequency elements can be viewed; however, they cannot be modified in this version of SQL Server 2008 R2. There are plans to allow these settings to be modified in future versions of SQL Server.

The following steps illustrate how to modify the data retention period for the UMDW:

1. Launch SSMS and connect to a UCP through Utility Explorer.
2. Select the Utility Administration node in Utility Explorer.
3. Click the Data Warehouse tab in the Utility Explorer Content pane.
4. In the Utility Explorer Content pane, select the desired data retention period for the UMDW, as displayed in Figure 2-9. The options are 1 month, 3 months, 6 months, 1 year, or 2 years.
   
   
   
   FIGURE 2-9 Configuring the data retention period
5. Click the Apply button to save the changes. Alternatively, click the Discard Changes or Restore Defaults buttons as needed.