The major goal of this book is to list and describe best practices for the administra-tion of SQL Server systems. Knowing best practices is one thing, but ensuring they’re implemented, and remain implemented, is an entirely different matter.
Suppose you’ve accepted a position as the new DBA for a company with thou-sands of server instances spread across production, test, and development environ-ments, each of which were installed and configured by various DBAs and developers with different preferences and knowledge of best practices. If you’re asked to per-form an audit of all servers for compliance with best practices, how will you go about it? If you’re handy with scripting technologies such as SQL Server Management Objects (SMOs) and PowerShell, that will certainly help, but other than that, you’re facing a significant and time-consuming challenge. When you finally complete the exercise, how can you be confident that none of the servers have changed configuration
In this chapter, we’ll cover
■ Enterprise DBA challenges
■ Policy-based management
■ Central management servers
■ PowerShell and ExecuteWQL()
since you started inspecting them? Like painting a bridge, by the time you finished you’d be due to start all over again!
Recent versions of SQL Server ship with good out-of-the-box settings and self-man-aging features, and together with well-documented and well-managed policies, ensur-ing enterprise-wide consistency in SQL Server configuration is somewhat easier, but it’s still a challenge.
SQL Server 2008 introduces a new feature called policy-based management, and it’s arguably the single most significant new feature for the DBA. In this chapter, we’ll dis-cuss some of the challenges facing DBAs in enterprise environments and how policy-based management can be used in assisting with such challenges. We’ll also take a look at combining the power of policy-based management with central management servers and PowerShell.
8.1 Server management challenges
Enterprise DBAs—those who manage complex environments with a mix of database products and versions—face a number of challenges. In addition to keeping many sys-tems up and running, they need to provide a smooth path for the implementation of changes to production databases. Before looking at policy-based management in depth, let’s spend some time covering the typical enterprise environment and the tasks faced by an enterprise DBA.
8.1.1 Enterprise environments
In a typical enterprise environment (see figure 8.1 for a simple example), one of the first things that comes to mind is the number of production server instances requiring support. A large number of servers are usually accompanied by a mix of product ver-sions, and possibly even other DBMS products such as Oracle or MySQL. In such envi-ronments, some degree of specialization usually exists, and DBAs are often grouped into areas of product expertise.
In addition to the production server instances, test and development servers, some or all of which may be provided using virtualization products, exist for the purposes of developing and testing new databases or making changes to existing production sys-tems. Accompanying such systems are various development tools and change manage-ment processes to ensure changes are version controlled and implemanage-mented in accordance with an appropriate deployment process.
In environments with critical systems, dedicated operations staff are usually on hand 24/7, with DBAs typically on call on a rotation basis. Products such as Systems Center Operations Manager (SCOM) are typically used for monitoring disk space and event logs in an attempt to identify and solve problems before they manifest them-selves as production outages.
In complex environments such as these, successful database administration must overcome a range of challenges, some of which are presented next.
149 Server management challenges
Figure 8.1 A typical enterprise environment consists of development, test, and production servers
8.1.2 Enterprise DBA challenges
Let’s take a look at some of the tasks facing DBAs in administering an enterprise envi-ronment:
Production systems should be secured with the least privilege principle. This is often in contrast with development environments in which changes originate.
When developed code reaches test and production systems, certain functions often fail as a result of the security differences between environments. DBAs must therefore coordinate environment configuration across the enterprise, particularly settings that may cause certain functions to fail.
Databases in production environments (should) use the full recovery model along with regular transaction log backups. In development and test environ-ments that don’t perform transaction log backups, the full recovery model may cause the transaction log to consume all available disk space. DBAs must match environments with backup profiles and recovery model settings.
In sites where different DBAs manage different environments—for example, development, test, and production—systems must be in place to ensure that both common and environment-specific settings are applied where appropri-ate. Where many DBAs are involved, each of whom has his or her own prefer-ences and skills, this becomes a difficult and time-consuming process.
Production
Test Development
C2 audit trace, full login auditing, regular
In sites with a range of DBMS products that require support, it’s often the case that the requirement for a broad range of skills prevents expertise in any one area, making correct and consistent configuration even more difficult.
In poorly configured environments, the time taken to troubleshoot highly visi-ble production provisi-blems often prevents important proactive maintenance required for ongoing environment performance, security, and stability.
Without strong proactive maintenance routines, mismanagement is a real danger, pre-senting a number of organizational risks.
8.1.3 The risks of mismanagement
Even with the best intentions and a good grasp of best practices, the sheer size of some deployments creates a challenging environment for even the most experienced DBA. Poorly configured servers pose a number of risks:
Security weak points
Unexpected performance problems due to different configuration settings between environments
Scripts working in one environment but failing in another, again due to config-uration differences
Without the use of third-party or custom-developed tools, ensuring consistent server configuration across the enterprise is a difficult and time-consuming process. This process often requires manual inspection or development of PowerShell and/or SMO scripts, a skill possessed by only a small percentage of DBAs.
Discovering incorrect configurations is more often than not a result of investigat-ing script failures, poor performance, or worse, a security breach. Such a process is commonly known as exception-based management. What’s needed is a way of defining and applying standard configurations to groups of server instances, and either pre-venting or alerting on deviations from the standard. This is typically called intent-based management, and as you’ve probably guessed, that’s exactly what we can now achieve using the new policy-based management feature in SQL Server 2008.
Figure 8.2 Policy-based management is found in SQL Server Management Studio under the Management node.
151 Policy-based management terms
Figure 8.2 shows the location of policy-based management in SQL Server Management Studio along with the new Data Collection and Resource Governor features we’ll cover in later chapters.
Before looking at the details of policy-based management, let’s cover some of the terms used.
8.2 Policy-based management terms
You can think of policy-based management as Active Directory for SQL Server. Active Directory is used in simplifying the process of administering thousands of domain users and computers. In a similar manner, policy-based management is the tool of choice in ensuring consistent SQL Server configuration, and like Active Directory, its value is magnified in environments with large numbers of server instances.
There are several new terms used when discussing policy-based management: tar-gets, facets, conditions, and policies. Let’s look at each in turn.
8.2.1 Targets
A target is the entity managed by a policy. Depending on the policy, targets may be SQL Server instances, databases, tables, and so forth. In the example in figure 8.3, the tar-get chosen for a table name policy is every table in every database.
Figure 8.3 When creating a policy, you choose a target. In this example, the target for Table Name Policy is “Every Table in Every Database.”
8.2.2 Facets
A facet is the name given to a group of configurable properties that are appropriate for a certain number of targets. For example, as shown in figure 8.4, the Surface Area
Configuration facet, applicable to the Server target, contains properties such as Data-baseMailEnabled, CLRIntegrationEnabled and XPCmdShellEnabled.
8.2.3 Conditions
A condition is created to specify the required state of one or more facet properties.
Continuing our surface area configuration example, the condition shown in figure 8.5 contains the required state of ten properties belonging to the Surface Area Con-figuration facet.
Figure 8.4 Facets, such as Surface Area Configuration, contain a number of properties that can be used in defining policy conditions.
Figure 8.5 A condition contains the required value of one or more facet properties.
153 Policies in action
8.2.4 Policies
Putting it all together, a policy contains a condition, a target, and an evaluation mode, which defines how the policy conditions will be enforced. Evaluation modes, some of which are only available for certain facets, are as follows:
On Change–Prevent—This mode ensures policy violations are prevented through the use of DDL triggers that roll back changes that violate policy. The mecha-nism used for the rollback (DDL trigger) limits the situations in which this eval-uation mode can be used.
On Change – Log Only—This mode logs violations when a change occurs that vio-lates an enabled policy. Corresponding alerts can then be set up as appropriate.
On Schedule—Using SQL Agent jobs, the On Schedule evaluation mode will periodically check policy compliance, and log violations if appropriate. This mode is useful in reducing the performance impact of a large number of enabled policies.
On Demand—This evaluation mode is used when creating ad hoc checks. The policies are created as disabled and, as such, have no performance impact on a running instance.
With these terms in mind, let’s take a look at the process of importing, creating, and evaluating policies.
8.3 Policies in action
SQL Server 2008 ships with a number of predefined policies that can be imported and evaluated. These policies encapsulate best practices such as those for securing the sur-face area of a SQL instance. In addition to importing these policies, new policies can be created and exported to file for later use on other server instances.
In this section, we’ll start off by importing an existing policy and looking at the var-ious evaluation options. We’ll then walk through the process of creating a new policy from scratch and exporting it to file.
8.3.1 Importing policies from file
In SQL Server 2005 and earlier, tools such as Best Practices Analyzer and Baseline Security Analyzer were used to periodically check a SQL Server instance for adherence to various best practices. In SQL Server 2008, policy-based management can be used to import predefined policies that encapsulate best practice settings.
Once imported, depending on the evaluation mode, the policies remain in place, actively checking, preventing, and/or logging violations. As such, they’re a stronger, more active version of previous-generation tools such as Best Practices Analyzer, and can be customized to suit a particular environment’s requirements.
Importing an existing policy is straightforward. In SQL Server Management Studio simply right-click the Policies menu under Policy Management, choose Import Policy, and specify the location of the policy definition file. SQL Server 2008 ships with a
number of predefined policies that can be imported. These policies are located in C:\Program Files\Microsoft SQL Server\100\Tools\Policies.
In this directory (or the equivalent installation directory) are three subdirectories containing polices for the Database Engine, together with Reporting Services and Analysis Services. The policies for Reporting and Analysis Services are limited to sur-face area configuration checks, and the Database Engine directory contains approxi-mately 50 policies covering a wide variety of best practices. Here are some examples of best practices addressed by the supplied policies:
Backup files must be on separate devices from the database files.
Data and log files should be on separate drives.
The default trace should be enabled.
Max Degree of Parallelism should be less than 8.
No unexpected system failures should be detected.
Backups should be performed frequently.
No I/O delay messages should be detected.
One of the nice things about the supplied policies is that some of them can be used with previous versions of SQL Server. For example, the File Growth for SQL Server 2000 policy can be used to check for the existence of SQL Server 2000 databases larger than 1GB whose AutoGrowth property is percentage based rather than a fixed size.
Although policies can be defined and executed against versions of SQL Server prior to 2008, there are some restrictions, and we’ll cover these (and some workarounds) later in this chapter.
In the example shown in figure 8.6, we’ll import the supplied Surface Area Con-figuration for Database Engine 2008 Features.
Once the file is selected, the only other option we need to specify is Policy State. By default, the policy state is preserved on import—that is, if the policy is enabled in the
Figure 8.6 You can import existing policies to check SQL instances for compliance based on predefined configuration files.
155 Policies in action
definition file, it will be enabled on import. Alternatively, we can explicitly enable or disable the policy as part of the import process.
Now that we’ve imported a policy, let’s look at the process of evaluating it.
8.3.2 Evaluating policies
One of the most powerful features of policy-based management is the variety of ways in which checks and violations can be defined and managed at an individual policy level.
In the previous section we covered the four evaluation modes: On Change – Pre-vent, On Change – Log Only, On Schedule, and On Demand. Let’s take a look at an example of each of these methods, starting with On Demand.
ON DEMAND
When you create a policy using the On Demand evaluation mode, the policy is created in a disabled state. You can then use it in an ad hoc manner as required by right-clicking the policy and choosing Evaluate. Let’s do this for the Surface Area Configuration pol-icy we imported earlier. Figure 8.7 shows the evaluation results of this polpol-icy. In this example, the evaluation failed because the target server has Database Mail enabled.
In addition to clicking View to see the details of the evaluation, you can click Apply, which will reconfigure the server to be compliant with the policy.
ON CHANGE – PREVENT
You may wish to enforce certain policies so that violations are prevented from occur-ring. Unfortunately, this is only possible for a certain class of conditions, specifically those able to be rolled back with DDL triggers.
As an example, figure 8.8 contains the error message returned when a table create statement violates a table name condition specifying that tables must be created with a tbl_ prefix.
Figure 8.7 You can manually evaluate a policy by right-clicking it and choosing Evaluate.
ON CHANGE – LOG ONLY
Like On Change – Prevent, On Change – Log Only actively monitors for policy viola-tions, but rather than roll back the violation, it logs the violation to the SQL Server log. Regardless of the evaluation mode, all policy failures are logged,1 enabling cus-tom policy failure alerts to be set up, a process we’ll cover in chapter 14. Figure 8.9 shows such a policy failure error in the SQL Server log.
Figure 8.9 All policy violations are recorded in the SQL Server log.
ON SCHEDULE
The On Schedule evaluation mode lets you enable policies to be checked on a sched-uled basis. This mode ensures that the overhead of active policy checking doesn’t impact performance. When you choose this mode, the policy creator selects a sched-ule, which creates SQL Agent jobs to run the scheduled policy checks.
Now that we’ve looked at the process of importing policies and covered the evalua-tion modes, let’s walk through the process of creating a new policy to check database properties such as AutoClose and AutoShrink.
1 Error numbers 34050 through 34053 are reserved for policy failures.
Figure 8.8 The On Change – Prevent evaluation mode will actively prevent changes that violate policy conditions.
157 Policies in action
8.3.3 Creating a database properties policy
The first step in creating a new policy is to right-click Policies under Policy Manage-ment and choose New Policy. You then enter a policy name and either choose an exist-ing condition or create a new condition.
In the example shown in figure 8.10, we’ll create a policy called Database Proper-ties Policy and create a new condition. We’ll use the Database facet and specify that both AutoClose and AutoShrink should be false.
After clicking OK, we’re returned to the policy definition screen shown in figure 8.11.
Here we select Every Database for the condition’s target. For the evaluation mode, we’ll choose On Schedule and create a new schedule for Nightly 11PM.
Figure 8.11 When you’re creating a policy, after choosing a condition, you select the condition target and evaluation mode.
Figure 8.10 After selecting a condition’s facet, we build the expression.
At this point, the policy is created and will run according to the defined schedule. One of the really useful features of the new SQL Server Management Studio is that it’s aware of policy failures. As shown in figure 8.12, any server and/or database that has failed a policy will be marked with a red cross icon. In this example, the AdventureWorks2008 database is set to AutoShrink and AutoClose, contrary to the policy we just created.
To correct the policy failure, rather than manually setting these two database prop-erties we can simply right-click the database and choose Polices > Evaluate to view the policy failure, and then click Apply to force the server’s properties to comply with the policy conditions.
Once created, policies can be easily exported, and doing so enables a number of important management functions.
8.3.4 Exporting policies
Policies can be exported in one of two ways. First, you can simply right-click an exist-ing policy and choose Export Policy. The resultexist-ing dialog box allows you to select a location in which to save the XML-based policy file.
The other method is based on an instance facet. By right-clicking on a registered SQL Server instance, you can choose Facets. The View Facets window allows you to view the instance properties on a facet-by-facet basis, but more important, you can choose the option Export Current State as Policy.
Figure 8.13 shows the Server Performance facet of the BNE-SQL-PR-01\SALES instance. By clicking the Export Current State as Policy button, we’re able to create a new policy file based on the Server Performance properties of this server.
The importance of this function can’t be overstated; essentially, we’re able to
The importance of this function can’t be overstated; essentially, we’re able to