Introduction
Each release of Microsoft SQL Server has brought innovative manageability features that help reduce the total cost of ownership of your data services solution by automating administrative tasks, centralizing management, and enabling administrators to focus on higher value, strategic activities. Microsoft SQL Server 2008 builds on the enterprise class manageability capabilities of earlier releases, and introduces a comprehensive performance data collector, warehouse, reporting, and policy-based management solutions that give you unprecedented control over your SQL Server–based enterprise data services solution.
This paper introduces the manageability capabilities of SQL Server 2008 and describes how you can use them to:
· Automate maintenance, best practices, and configuration management to reduce administrative overhead.
· Analyze and optimize data services performance across the enterprise.
· Scale administrative operations easily and manage data services of all types throughout the organization.
Automating Management with SQL Server 2008
SQL Server 2008 reduces the amount of time a database administrator or operator needs to spend performing administrative functions through extensive, policy-based configuration management and automation of maintenance tasks.
Configuring SQL Server with Policy-Based Management
The Policy-based Framework provides the ability to define policies that apply to servers, databases, and other objects in your data environment. Well-defined policies can help administrators to control and manage change proactively within the data services environment. The framework delivers the following benefits:
· Management by Intent. The framework provides a logical view of the system configuration, which enables administrators to define the desired configuration of data services proactively instead of making changes in response to issues when they arise.
· Intelligent Monitoring. Policy-based Framework policies can monitor and prevent changes to the system that deviate from the desired configuration.
· Virtualized Management. With the framework, you can scale management across multiple servers, which makes it easy to enforce consistent configuration policies across the enterprise.
The Policy-based Framework uses the following objects when configuring policy management:
· Facets. Contain properties that expose options related to an area of policy management. For example, the Surface Area includes properties that relate to specific SQL Server features, such as Database Mail Enabled and CLR Integration Enabled.
· Conditions. Express the state of a facet. are based on a single facet, and can be used in one or more policies. For example, you could create a condition named Minimal Surface Area in which all of the properties in the Surface Area facet are assigned the value False.
· Policies. Contain a single condition that is enforced for one or more targets. For example, you could create a policy named Locked Down Server that assigns the Minimal Surface Area condition to the server.
· Categories. Contain one or more policies. Database owners can subscribe a database to one or more category. For example, you could create a category named Corporate DB Policies that contains a policy to enforce database object naming conventions and another policy to enforce a specific database compatibility level, and subscribe line of business databases to the category. By default, all databases implicitly subscribe to the default category. Additionally, categories can be marked as Active or Inactive at the server or database level, which enables administrators to control policy enforcement.
· Targets. Are entities, such as servers, databases, logins, tables, or other database objects to which policies are assigned. All targets in a SQL Server instance form a target hierarchy. Within a policy, a target set is defined when a set of filters are applied to the target hierarchy. For example, a target set can include all of the indexes owned by the Production schema.
Policy-based Framework Facets
Each framework facet represents an aspect of SQL Server management in which one or more related configuration options is defined. SQL Server 2008 includes a number of pre-defined facets, which you can use to define policies that enforce specific configuration option values. For example, SQL Server 2008 includes the Surface Area facet, shown in Figure 1.
Figure 1: Surface Area Facet
You can create conditions to define a desired configuration that is based on the settings in a facet, as shown in Figure 2.
Figure 2: Defining a condition
Finally, to define a policy, you simply specify the condition that you want to enforce, as shown in Figure 3.
Figure 3: Defining a policy
SQL Server 2008 provides a comprehensive set of facets that you can use to specify configuration settings and rules for your SQL Server 2008 implementation. Some examples of ways in which you can use the provided facets to define policies include:
· Using the Server facet to enforce specific server configuration settings such as the login authentication mode.
· Using the Surface Area facet to control which features are enabled, and reduce the surface area of the server.
· Using the Database facet to enforce specific database settings such as the compatibility level.
· Using the Multipart Name facet to enforce naming conventions for tables views, and other schema-bound database objects.
· Using a range of facets to enforce known best practices for your database solution, such as ensuring that data files are stored on a separate drive from log files.
Distributing Policies Across the Enterprise
The framework provides the capability to publish policy categories to additional SQL Servers in your environment. You can define policies on a SQL Server instance (known as the configuration server) and then replicate those policies to other servers (known as configuration targets). Any changes made to the policies or the categories on the configuration server (such as marking a particular category as inactive) are automatically propagated to all of the configuration targets, which significantly reduces the overhead that is associated with enforcing configuration policy compliance across the enterprise. In addition to the distribution of policies, you can execute queries against the set of configuration targets by using the Query Editor.
Applying Policies
When you define a policy, you select an execution mode for the policy, which will determine whether the policy is enforced proactively, on a scheduled basis, or manually through administrative intervention. The following four execution mode options are in the policy properties:
· On Demand. An administrator manually invokes policy checking.
· On Schedule, log out-of-compliance. A SQL Server Agent job periodically executes the policy and records any configuration settings that do not comply with active policies.
· Changes are attempted, prevent out-of-compliance. DDL triggers roll-back transactions that do not comply with active policies.
· Changes are attempted, log out-of-compliance. Event notification evaluates a policy when a relevant change occurs and records any configuration settings that do not comply with active policies.
Automated Maintenance
Through the SQL Server Agent service, SQL Server 2008 gives administrators the flexibility to provide an automated and proactive management and maintenance strategy for their database environment. The SQL Server Agent provides the following components to configure your automated environment:
· Jobs. A series of one or more steps to be completed. These steps can consist of Transact SQL statements, SQL Server Integration Services package, or command-line statements.
· Alerts. Preconfigured messages that are generated automatically when a particular event occurs on a SQL Server.
· Operators. Users defined with additional contact information that may include an e-mail address, pager number, or net send address. Alerts are sent to these operators when preconfigured events occur, or to notify them of success, failure, or completion of a job or job step.
· Schedules. Specifies when a job runs. A job can be associated with more than one schedule.
Multi-server jobs can be maintained at a centralized server and used to manage SQL Server instances across your organization.
Maintenance Plans
Maintenance plans enable administrators to quickly configure standard maintenance policies, such as regular database and transaction log backups, and index maintenance to be implemented on SQL Server instances. The Maintenance Plan Wizard provides a quick and user-friendly way to define maintenance plans for one or more databases. Similarly to jobs, multi-server maintenance plans can be managed from a centralized server in a master server/target server configuration. In addition to the Maintenance Plan Wizard, SQL Server Management Studio provides an extensive design surface with which you can define an enhanced workflow for maintenance plan tasks.
Programmatic Management Interfaces
You can customize and extend your management capabilities by creating applications and scripts to perform common administrative tasks. This approach can significantly simplify the operation and management of your data services by delegating management tasks to less experienced administrators and providing a simple utility that contains only the functionality that is required for those tasks.
SQLCMD
To facilitate multi-script automation through batch files, SQL Server 2008 includes SQLCMD. SQLCMD is a comprehensive command-line tool that you can use to run parameterized and multi-server scripts that perform administrative tasks. By incorporating SQLCMD operations in a batch file or script, you can achieve a high level of administrative automation without requiring operators to use complex management interfaces.
Server Management Objects
Server Management Objects (SMO) is an enhanced Application Programming Interface (API) that exposes the full management environment of SQL Server to developers. You can use SMO to build powerful custom applications to manage SQL Server–based solutions.
Optimizing Performance with SQL Server 2008
A significant aspect of data services management is to ensure that databases and applications provide the required level of performance. SQL Server 2008 provides various tools that you can use to collect and analyze performance data, and to optimize database performance to meet the requirements of your common data access workloads.
Data Collection and Monitoring with SQL Server Performance Data Collection
SQL Server 2008 provides Performance Data Collection that enables you to collect, analyze, troubleshoot, and store SQL Server diagnostics information. Performance Data Collection provides an end-to-end solution for performance monitoring that includes low overhead collection, centralized storage, and analytical reporting of performance data. You can use SQL Server Management Studio to manage collection tasks, such as enabling the data collector, starting a collection set, and viewing system collection set reports as a performance dashboard. You can also use system-stored procedures and the Performance Studio API to build your own performance management utilities based on Performance Data Collection.
Low Overhead Data Collection
Performance Data Collection provides a unified data collection infrastructure that consists of a data collector in each SQL Server instance that you want to monitor. The data collector is flexible and enables you to manage the scope of data collection to fit development, test, and production environments. You can easily collect both performance and general diagnostic data with the data collection framework.
The data collector infrastructure introduces the following new concepts and definitions:
· Data Provider. Sources of performance or diagnostic information that can include SQL Trace, Performance counters, and Transact-SQL queries (for example, to retrieve data from Distributed Management Views).
· Collector Type. A logical wrapper that provides the mechanism for collecting the data from the data provider.
· Collection Item. An instance of a collector type. When you create a collection item, you define the input properties and collection frequency for the item. A collection item cannot exist on its own.
· Collection Set. The basic unit of data collection. A collection set is a group of collection items that are defined and deployed on a SQL Server instance. Collection sets can run independently of each other.
· Collection Mode. The manner in which the data in a collection set is collected and stored. The collection mode can be set to cached or non-cached. The collection mode affects the type of jobs and schedules that exist for the collection set.
The data collector is extensible and supports the addition of new data providers.
Centralized Storage of Performance Data
When the data collector is configured, a relational database with the default name MDW is created as a management data warehouse in which to store the collected data. This database can reside on the same system as the data collector, or on a separate server. Objects in the management data warehouse are grouped into the following three preconfigured schemas, each with a different purpose:
· The Core schema includes tables and stored procedures for organizing and identifying the collected data.
· The Snapshot schema includes data tables, views, and other objects to support the data that is collected from the standard collector types.
· The Custom_Snapshot schema enables the creation of new data tables to support user-defined collection sets that are created from standard and extended collector types.
Analytical Reporting
Performance Data Collection helps you to analyze your collected data quickly with a robust set of preconfigured system collection sets, including Server Activity, Query Statistics, and Disk Usage. The Server Activity system collection set is the entry point for most monitoring and troubleshooting scenarios. A set of reports that is associated with each system collection set is published in SQL Server Management Studio, and you can use these reports as a performance dashboard to help you analyze the performance of your database systems as shown in Figure 4.
Figure 4: A Performance Data Collection report
Optimizing Database Performance
SQL Server 2008 provides a variety of well-integrated tools to optimize your database environment. These tools give operators and administrators the ability to collect statistics and other data so that they can provide optimal database environment performance in the enterprise.
SQL Server Profiler
SQL Server Profiler shows how SQL Server internally resolves queries by providing a graphical interface for the functionality provided through SQL Trace. With SQL Server Profiler, you can capture the activity generated by a workload on a SQL Server instance. The events that are captured and displayed can be defined by using predefined templates, and you can modify the system provided templates to create your own templates. You can save the captured workload to a table or to a file for future evaluation. You can immediately start a new trace from SQL Server Management Studio based on your connection context. For example, if you want to trace statement execution for a particular Query Editor session, the new Trace Query in SQL Server Profiler command immediately starts a trace that uses the query window SPID as a filter.
After you have collected a workload from your production system, you can replay the workload on a new test system to verify the performance with an actual workload. You can also correlate these workloads with System Monitor Performance Counter logs to view how the workload affects the system performance.
In SQL Server 2008, you can use SQL Server Profiler to capture activity on Microsoft SQL Server™ 2000, Microsoft SQL Server™ 2005, and SQL Server 2008 database engine instances, and on SQL Server 2005 and SQL Server 2008 Analysis Services instances.
Database Engine Tuning Advisor
You can use the Database Engine Tuning Advisor (DTA) to gain insight into the existing indexing and partitioning structure of your databases, and to get recommendations for how to improve database performance by creating appropriate indexing and partitioning structures. In addition to optimizing your indexing structure, DTA can recommend new physical data structures, including partitioning. DTA also offers you the ability to tune across multiple servers and limit the amount of time the tuning algorithms run. DTA is available as both a command-line and graphical utility. The command-line utility enables you to take advantage of advanced scripting options.
SQL Server Management Studio Reports
In addition to the performance dashboard reports provided with Performance Studio, SQL Server Management Studio includes a number of standard reports at both the server level and database level that you can use to view performance statistics and live activity. You can use these reports to troubleshoot performance problems that are caused by excessive resource usage or concurrency issues. Figure 5 shows the Memory Consumption standard report.
Figure 5: Memory Consumption standard report
SQL Server Management Pack
The SQL Server Management Pack for Microsoft System Center Operations Manager monitors the availability and performance of SQL Server 2008, and can issue alerts for configuration problems. The Management Pack collects Event Log alerts and provides associated knowledge articles that provide additional user details, possible causes, and suggested resolutions.
Scalable Management with SQL Server 2008
Today’s organizations need to store and manage ever-increasing volumes of data, and support a growing number of software applications and services that access that data. This trend has led to data services solutions that span the enterprise, which dramatically increases the workload for database administrators and other operational support personnel. Although server consolidation can greatly reduce the number of database servers to be managed, the variety of different kinds of data services, such as online transaction processing (OLTP) databases, online analytical processing (OLAP) databases, reporting solutions, extract, transform, and load (ETL) operations, and occasionally connected data solutions on laptops and mobile devices, means that data management functions must scale across the enterprise.
Centralized Data Services Administration
SQL Server Management Studio, shown in Figure 6, is a comprehensive, centralized management tool that enbles you to manage all of your SQL Server instances through a single interface. With SQL Server Management Studio, you can manage all of your database related services including SQL Server database engine, Analysis Services, Reporting Services, Integration Services and SQL Server Anywhere edition. Database administrators can use the extensive standard and customizable reports to provide a concise and graphical view of the state of the database environment.
Figure 6: SQL Server Management Studio
Integrated Configuration
SQL Server Configuration Manager, shown in Figure 7, enables system administrators to manage SQL Server services and network libraries from a single location. You can add the SQL Server Configuration Tool as a snap-in to the Microsoft Management Console (MMC). By creating a custom management console that is specifically designed to support their job functions, system administrators can save time by not having to open multiple tools. Additionally, the SQL Server configuration tool helps system administrators to manage portions of the services that relate to their functions, which relieves the database administrator to manage databases and database performance.
Figure 7: SQL Server Configuration Manager
Conclusion
SQL Server 2008 builds on the management capabilities of previous versions of SQL Server and provides a greater variety of tools and frameworks to help you manage your enterprise data environment. SQL Server 2008 provides the tools that help you to scale your data environment to meet the growing needs of your company and to optimize the performance of these databases across your enterprise. It also provides functionality to relieve database administrators of day-to-day maintenance tasks and reduces management overhead through automation.
