Microsoft SQL Server 2008 R2: Managing the Core Database Engine

Exclusive offer: get 50% off this eBook here
Microsoft SQL Server 2008 R2 Administration Cookbook

Microsoft SQL Server 2008 R2 Administration Cookbook — Save 50%

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook

$32.99    $16.50
by Satya Shyam K Jayanty | June 2011 | Cookbooks Enterprise Articles Microsoft

The core database engine is the main platform for managing all of the business data for your organization—both archived and current—in a relational database management system. Managing the Core Database Engine, enables the user to produce a resilient data platform, which is possible with new features of SQL Server 2008 R2 such as Utility Control point, multi-server management, and implementing central management feature enhancements.

In this article by Satya Shyam K Jayanty, author of Microsoft SQL Server 2008 R2 Administration Cookbook, we will cover:

  • Implementing Central Management feature enhancements
  • Designing Multi-server management from SQL Server 2008 R2
  • Managing Utility Control Point data warehouse database

 

Microsoft SQL Server 2008 R2 Administration Cookbook

Microsoft SQL Server 2008 R2 Administration Cookbook

Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook

        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

Implementing Central Management feature enhancements

Central Management Server (CMS) is a feature that enables the DBA to administer multiple servers by designating a set of server groups, which maintain the connection information for one or more instances of SQL Server. In this recipe, we will go through the implementation strategy for the CMS feature in your SQL Server data platform. Before we continue into the recipe, it is essential for you to understand the core functionality of this feature, which uses the multiple features from the SQL Server core database engine:

  • SQL Server system repository
  • Manage the SQL Server farm from one location
  • Easy reporting about system state, configuration, and performance

Getting ready

As per the feature characteristics described previously, there are three important components in the system. These components are:

  • Storage
  • Execution
  • Reporting

Storage is used to store the system repository, Execution manages the SQL Server instances, and Reporting shows the statistical information.

The CMS feature implementation is a dual-process (setup and configuration) making use of SQL Server components. The prerequisite of services is to install SQL Server 2008 R2 and Reporting Services. To support the extensive features of the core database engine Enterprise Edition, it is recommended that the CMS be a single point of resource to manage the multiple instances of SQL Server. However, for cost purposes, you can still make use of the Standard Edition.

CMS is introduced in SQL Server 2008, as earlier versions of SQL Server cannot be designated as a Central Management Server. The main characteristic of CMS is to execute TSQL and Policy-based management policies at the same time against these designated server groups.

How to do it...

To get started, we will need to complete the following steps to implement the central management server feature to the existing SQL Server data platform:

  1. SQL Server Management Studio is the main tool to designate the CMS feature. Open 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 will be 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 will be members of this server group, see the following screenshot:

  7. After you have registered a server, the Central Management Server (CMS) will be able to execute queries against all servers in the group at the same time.

    TSQL statements and Policy-based management policies can be executed at the same time against the server groups; CMS maintains the connection information for one of more instances of SQL Server. It is essential to consider the effective permissions on the servers in the server groups, which might vary; hence, it is best to use Windows Authentication in this case.

  8. To enable the single-point of resource to execute a single query on multiple CMS servers, it is easier to access the registration properties of a group of servers. Using that specific server group, there are multiple options of new query, object explorer, evaluate policies, and import policies.

    The CMS feature is a powerful manageability feature and is easy to set up. However, it is potentially dangerous to implement a policy that may not be suitable for a set of server groups.

  9. Therefore, it is best to represent the CMS query editor with a specific color that can easily catch our eye to avoid any 'unintentional execution of queries or policies'. To enable such a representation follow these steps:
    1. Open SQL Server Management Studio, on the Tools menu, click on Options.
    2. In Options, select Editor Tab and Status Bar.
    3. Select the Status Bar Layout and Colors section from the right-hand side of the options window.
    4. In the following screenshot, we can see that we have selected the Pink color, which highlights that extra care is needed before executing any queries, as shown in the next screenshot:

Now, let us evaluate how to create a CMS server group, evaluate policies, and execute queries on multiple servers.

  1. Open SQL Server Management Studio; on the View menu, click on Registered Servers, expand Database Engine, right-click Central Management Servers, point to New, and then click Central Management Servers.
  2. In the New Server Registration dialog box, register the instance of SQL Server that will be the Central Management Server.
  3. In Registered Servers, right-click on Central Management Server. Point to New and click on New Server Group. Now type a group name and description, for instance, UAT or Production and click OK. See the following screenshot:

    Microsoft SQL Server 2008 R2 Administration tutorial

  4. To register the servers to these individual server groups, in Registered Servers right-click on Central Management Server Group and then click New Server Registration.
  5. In the New Server Registration dialog box, register one or multiple instances that are applicable to the relevant server groups, such as Development or Production.

As a best practice, it is ideal to create individual groups within CMS to enable a single-query execution against multiple servers as in the following screenshot:

Microsoft SQL Server 2008 R2 Administration tutorial

SQL Server 2008 R2 provides a 'Policy Based Management framework' that consists of multiple sets of policy files that you can import as best practice policies. You can then evaluate the policies against a target set that includes instances, instance objects, databases, or database objects. You can evaluate policies manually, set policies to evaluate a target set according to a schedule, or set policies to evaluate a target set according to an event.

To evaluate a policy complete the following steps:

  1. To evaluate the policy against multiple configuration targets in the SQL Server Management Studio, click on the View menu to select Registered Servers and expand the Central Management Server.
  2. Right-click on the Production server group and click on Evaluate Policies.
  3. The Evaluate Policies screen will be presented as shown in the next screenshot.
  4. Click on Source to Choose Source.
  5. The relevant best practices policies are stored as XML files under %:\Program Files\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033 directory, which is shown in the next screenshot:

    For this recipe, we have chosen to select the policy from SQL Server 2008 R2 Best Practices Analyzer that is downloaded from http://www.microsoft.com/downloads/en/details.aspx?FamilyID=0fd439d7-4bff-4df7-a52f-9a1be8725591 and installed on the server.

Let us evaluate the policy of Backup and Data File Location against the production instances. This policy will perform the steps to:

  • Check if the database and the backups are on separate backup devices. If they are on the same backup device, and the device that contains the database fails, your backups will be unavailable.
  • Putting the data and backups on separate devices optimizes the I/O performance for both the production use of the database and writing the backups, as shown in the following screenshot. (This is one of the Best Practices of Maintenance policies from Microsoft SQL Server 2008 R2 Best Practices Analyzer.)

The ease of executing a single query against multiple-servers at the same time is one of my favorite manageability features. The results that are returned with this query can be combined into a single results pane, or in a separate results pane, which can include additional columns for the server name and the login that is used by the query on each server. To accomplish this task, let us execute a query to obtain the Product Version, Product Level, Edition, and EngineEdition that are registered against the Production server group.

  1. In SQL Server Management Studio, on the View menu, click Registered Servers.
  2. Expand a Central Management Server, right-click a server group, point to Connect, and then click New Query.
  3. In Query Editor, execute the following TSQL statement:

    SELECT
    SERVERPROPERTY('ProductVersion') AS ProductVersion,
    SERVERPROPERTY('ProductLevel') AS ProductLevel,
    SERVERPROPERTY('Edition') AS Edition,
    SERVERPROPERTY('EngineEdition') AS EngineEdition;
    GO

    The results are displayed as follows:

  4. From the preceding screenshot, the results with a pink bar at the bottom state that this is a Production group. To change the multi-server results options, in the Management Studio, on the Tools menu, click Options.
  5. Expand the Query Results, expand SQL Server, and then click Multi-server Results.
  6. On the Multi-server Results page, specify the option settings that you want, and then click OK.

We now should have successfully completed the CMS feature implementation into the SQL Server data platform.

How it works...

The SQL Server instance that is designated as a Central Management Server maintains server groups, which maintain the connection information for one or more instances of SQL Server. CMS and all subsidiary servers on the network are registered using Windows Authentication only; in case local server groups are registered using Windows Authentication and SQL Server Authentication.

The storage for centralized management server groups, connection information, and authentication details are available from themsdb system database in:

  • dbo.sysmanagement_shared_registered_servers_internal
  • dbo.sysmanagement_shared_server_groups_internal system tables
  • dbo.sysmanagement_shared_registered_servers
  • dbo.sysmanagement_shared_server_groups system views

The user and permissions information on the registered servers may vary based on the user permission that uses Windows Authentication connection. For instance, in our recipe, on the dbia-ssqa\SQL2K8 instance, the login is a member of the SYSADMIN fixed server role and on the DBIA-SSQA\SSQA instance, the login is a member of the DBCREATOR fixed server role. In this case, we may not be able to run certain SYSADMIN privileged statements on DBIA-SSQA\SSQA instance.

There's more...

Using SQL Server 2008 R2 Management Studio with CMS, we can manage the lower SQL Server version instances, such as 2005 (90), 2000 (80), and 7.0 (70), which is truly a central management of instances.

Microsoft SQL Server 2008 R2 Administration Cookbook Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook
Published: May 2011
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

Designing Multi-server management from SQL Server 2008 R2

Taking advantage of the two new manageability features—Policy Based Management and Data Collector of SQL Server 2008 which are independent of each other—the multi-server management addresses problems by providing further solutions to the DBAs. The lack of manageability tools to manage multiple instances by understanding resource utilization and enhancing effective reporting tools is plugged in using multi-server management from SQL Server 2008 R2.

This recipe is intended to design the multi-server management component using SQL Server 2008 R2 for data-tier applications (DAC). By operating the SQL Server Management Studio or Visual Studio with the help of Utility Explorer (UE), we can design the Multi-Server Management (MSM) component. The MSM provides several ways to view summary and detailed data about the health state of managed instances of SQL Server. An additional capability of UE enables the interface to view and manage policy definitions that includes objects, data, and policies managed by the SQL Server utility.

Getting ready

The new feature of SQL Server 2008 R2—Utility Control Point (UCP)—allows DBAs to enroll the SQL Server instances across their Enterprise network into a centralized multi-server management point. Once the instances are enrolled, the default capacity policies of utilization across the instances or applications are set. It is essential to check that you are using an SQL Server 2008 R2 instance to register the UCP to design the multi-server management feature.

The inter-operability with Visual Studio 2010 introduces the facility to create data-tier applications using the project template. The internal components of Visual Studio 2010 distribute several database projects and templates to enable the users to develop the integral part of applications for data-tier applications. These capabilities of Visual Studio 2010 and SQL Server 2008 R2 reduce the trial and error associated with deployments streamlining SQL Server consolidation initiatives; the control point also provides broad control over the hardware utilization of the database environment. Using the most common applications, such as SQL Server Management Studio (SSMS) and Visual Studio, the MSM component can be deployed. SQL Server 2008 R2 SSMS includes a new explorer called Utility Control Point explorer and interoperability with Visual Studio (VS) tool project type, which will help developers to write database applications, capture deployment intent, and produce the data-tier application as a single unit of deployment. In this recipe, we will go through the implementation of the MSM feature with the help of tools, such as VS and SSMS by the developers and DBAs.

How to do it...

To get started, let us go through the steps to implement the MSM feature using Visual Studio tool that will deploy data-tier application components, which are important for database applications deployment:

  1. Open the Visual Studio programs from Start | programs | Visual Studio 2010 program group.
  2. Click File, click New, and then click Project. Under Installed Templates, expand the Database node, and then click on the SQL Server node.
  3. In the template pane, click SQL Server Data-tier Application. Then, type the project name, location, and solution name in the appropriate text boxes at the bottom of the form.
  4. To use source control for database code, select the Add source control check box. After all selections are entered, click OK to create the project.
  5. The steps are represented in the following screenshot:

    Visual Studio 2010 delivers several database projects and templates that enable users to develop the data portion of their applications in Visual Studio Professional, Visual Studio Premium, and Visual Studio Ultimate editions. To deploy DAC packages, you need to download the templates using online templates.

  6. After all selections are entered, click OK to create the project.
  7. We will use the Scripts pane as shown in the next screenshot from Solution Explorer to manage the database or server.

    Microsoft SQL Server 2008 R2 Administration tutorial

  8. Open the Data-tier Application Wizard.
  9. Right-click on the project node, and then click Import Data-tier Application (refer to the next screenshot.)

    Microsoft SQL Server 2008 R2 Administration tutorial

  10. In the Specify Import Options page, click Import from an existing instance of SQL Server.
  11. Then, type a value in Connection string for the SQL Server instance, or click Edit to configure a connection.
  12. After that, choose a database from the Database name list. When you are done, click Next.
  13. The Summary page lists all database objects that are supported by a DAC.
  14. If any objects are not supported, the import cannot be performed successfully. If all objects are supported, click Next.
  15. The Import Objects page tracks progress while the wizard creates the SQL scripts for the database objects and loads them into the project.
  16. The final step is to click Finish to close the wizard.

We will now go through the deployment using SQL Server Management Studio tool to deploy data-tier application components:

  1. Start the Import Data-tier Application Wizard.
  2. Right-click the project node and then click Import Data-tier Application.
  3. In the Specify Import Options page, click Import from a data-tier application package and then click Browse to select the DAC package file; click Next to continue.
  4. The final confirmation of the list of database objects in the DAC package is shown in the Summary page.
  5. The Import Objects page tracks progress as the wizard creates the SQL scripts for the database objects and loads them into the project.
  6. Click Finish to close the wizard when the page completes.

    After a data-tier application project has been created, users can add SQL Server database objects as well as instance objects (such as logins) to the project. Objects can be added by using built-in templates or by writing Transact-SQL code directly in the editor, without the use of templates.

The final screenshot of UCP will be as follows:

Once the steps are completed, the unified view of MSM is visible on SSMS from the Utility Explorer Content on the right-hand side. The unified view is the representation of a successful implementation of MSM to manage data-tier application deployment.

How it works...

The Data-tier Application (DAC) component helps to adopt a single unit of deployment method to streamline all the consolidation of SQL Server instances into a unified view.

In this recipe, we used the Visual Studio 2010 template to build the data-tier application that will stand as multi-server management. The different types of templates and projects are as follows:

  • Database Project: Database project templates are available for SQL Server 2005 and SQL Server 2008. You can use these templates to define databases for missioncritical and business-critical applications. All objects implemented by the respective SQL Server version are supported in database projects.
  • Server Project: Server project templates are available for SQL Server 2005 and SQL Server 2008. You can use these templates to define server-level objects as well as modifications to the SQL Server master database. Examples of server-level objects are logins and custom error messages.
  • Data-tier Application: Data-tier application (DAC) projects are useful for implementing departmental applications (or other simple applications). Using this method the DAC package will be operated as a single unit of deployment throughout the development, test, and production lifecycle of an application.

After Visual Studio 2010 is installed, the data-tier applications process can be initiated using create a new data-tier application project. Once the project is available, the new database server objects can be authored, to initialize the project with existing schema. We can employ one of the two techniques to populate the project with database and server objects for existing databases and applications. More explicitly, we can import a data-tier application from an existing database, or we can load schema objects from a DAC package file.

Once the extraction of a DAC package is completed the Extract Data-tier Application Wizard can be used. Similar to Visual Studio 2010, restriction importing from a DAC package with SSMS can be done only once, to initialize a new data-tier application project.

Microsoft SQL Server 2008 R2 Administration Cookbook Over 70 practical recipes for administering a high-performance SQL Server 2008 R2 system with this book and eBook
Published: May 2011
eBook Price: $32.99
Book Price: $54.99
See more
Select your format and quantity:
        Read more about this book      

(For more resources on Microsoft SQL Server, see here.)

Managing the Utility Control Point data warehouse database

Utility Control Point (UCP) is one of the new management features in SQL Server 2008 R2. UCP enables you to manage and monitor multiple instances of SQL Server. Data collected by UCP managed instances of SQL Server are stored in the Utility Management Data Warehouse (UMDW) database called sysutility_mdw.

To generate data for UMDW, it is essential to create a UCP on an instance of SQL Server. In this recipe, we will use the SSMS to create the Utility Management Data Warehouse (UMDW). The steps are wizard-driven to specify a SQL instance—where the UCP is created—and enable the utility collection set to periodically collect and store in UMDW.

How to do it...

The following steps are essential to manage required data for the UMDW database that will be generated by using the Utility Explorer wizard to validate the SQL Server instances:

  1. In Management Studio, on the Object Explorer, click Utility Explorer to create the Utility Control Point (UCP).
  2. The Introductory screen of the UCP wizard is presented as follows:

  3. The next screen is to specify the instance of SQL Server where the new Utility Control Point will be configured.
  4. This requires two values to be passed on, SQL Server Instance Name and UCP Name (circled text in the following screenshot):

  5. The next screen will be the UCP Collection Set Account that is essential to ensure that the utility collection has the required privileges to operate within a single Windows domain or across a domain with two-way trust relationships.

  6. Also the SQL Server service account on UCP and all managed instances must have read permission to users in the Active Directory.

    To specify the account to collect data, you can choose the SQL Server Agent service account, but for security precautions it is recommended to propose a new account.

  7. The next screen is the SQL Server Instance Validation screen that will run through 10 rules to validate (as shown in the next screenshot, which is processing these rules).
  8. If there are any errors, click on the result that will present the reason for failure.
  9. As soon as we fix the error, come back to the instance validation screen to Rerun validation.
  10. Once the validation rules are successful, you can choose to save the report by clicking on Save Report, which will save information in HTML format.
  11. Click Next to proceed.

We should now have completed the process to manage a UMDW database that is formed once all the results of SQL Server instance validation process completes as Success.

How it works...

From the previous screenshot, if you look at the option, there cannot be a database named sysutility_mdw on the specified instance UMDW (Utility Management Data Warehouse) data file is sysutility_mdw. All the performance data and policies are stored in this database that helps to identify resource use bottlenecks and consolidation opportunities.

The UMDW name will be Sysutility_mdw_<GUID>_Data and SQL Server agent service will handle the data load collection frequency every 15 minutes.

The configuration settings mentioned earlier, such as UMDW name and collection set upload frequency are not configurable in this release of SQL Server 2008 R2.

Whereas, the directory where UMDW files is stored are configurable. The default directory for UMDW is <System drive>:\Program Files\Microsoft SQL Server\MSSQL10_50.<UCP_Name>\MSSQL\Data\, where <System drive> is normally the C:\ drive. The log file, UMDW_<GUID>_LOG, is located in the same directory. Based on your environment security policy standards for database files location, you can choose to modify the UMDW (sysutility_mdw) file location using detach/attach method or ALTER DATABASE statement to a different directory other than specified (default) previously.

Continuing from the steps to create the UCP wizard, the next screenshot is the summary of UCP:

Once the UCP is configured successfully, the Utility Explorer presents the following screenshot.

The Managed Instances node is presented as a tree view to access list view data and details tabs at the bottom of the content pane providing data for CPU and storage volume utilization, as well as access to policy definitions and property details for individual managed instances of SQL Server in the SQL Server Utility. Within the Managed Instances the default maximum values for instance processor utilization/computer process utilization/file space utilization is 70 percent that produces a trend of resource usage and the default minimum value for instance processor utilization/computer process utilization/file space is 0 percent.

Summary

In this article we covered the new features of SQL Server 2008 R2 such as Utility Control point, multi-server management, and implementing central management feature enhancements.


Further resources on this subject:


About the Author :


Satya Shyam K Jayanty

Satya Shyam K Jayanty is an Independent consultant working as a Principal Architect and Director for D Bi A Solutions Limited based in the UK. He has a Master's degree in Economics and Commerce from Osmania University, Hyderabad (India) and an MBA – Systems from the University of the West of England. Satya started his career in the IT industry in 1992, and he has been working with SQL Server (from version 4.2) for more than 15 years and has been an accomplished Microsoft SQL Server MVP for the last six years. Satya is a frequent speaker and SME volunteer at Microsoft Tech-Ed, SQL PASS, SQL Bits, Scottish Area SQL Server user group. He maintains a blog at http://www.sqlserver-qa.net and http://www.sql-server-performance.com.

Books From Packt


Microsoft SQL Server 2008 High Availability
Microsoft SQL Server 2008 High Availability

Microsoft SQL Server 2008 R2 Master Data Services: RAW
Microsoft SQL Server 2008 R2 Master Data Services: RAW

Learning SQL Server 2008 Reporting Services
Learning SQL Server 2008 Reporting Services

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services
Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

Microsoft Data Protection Manager 2010
Microsoft Data Protection Manager 2010

.NET Compact Framework 3.5 Data Driven Applications
.NET Compact Framework 3.5 Data Driven Applications

Software Testing using Visual Studio 2010
Software Testing using Visual Studio 2010

MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook: RAW
MDX with Microsoft SQL Server 2008 R2 Analysis Services Cookbook: RAW


No votes yet

Post new comment

CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
S
A
8
P
n
7
Enter the code without spaces and pay attention to upper/lower case.
Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software