Lara Rubbelke : SQL Server 2008

SQL Server Separation of Duties Whitepaper Released

Lara Rubbelke — Fri, 06 May 2011 16:17:00 GMT

Hot off the virtual press, the SQL Server Separation of Duties Whitepaper is now available! SQL Server 2008 R2 provides the full breadth of tools to support restrictive compliance and security requirements. This whitepaper will associate the features...(read more)

You want to grant someone permissions to do WHAT?!?!

Lara Rubbelke — Mon, 24 Jan 2011 02:32:00 GMT

Have you ever heard of these types of requests? True story! I have had each of these and many more: A customer needed to grant a business user the rights to issue a KILL command – without giving them sysadmin or CONTROL SERVER. A customer wanted to grant...(read more)

Microsoft Assessment and Planning (MAP) Toolkit 5.0 Beta

Lara Rubbelke — Tue, 25 May 2010 03:19:00 GMT

Do you know where SQL Server is installed - everywhere it is installed? Do you really know where SQL Server is installed? Are you looking for a tool that will help you discover any rogue instances so you can better manage these instances? The Beta 2 for...(read more)

SQL Server 2008 R2 CTP Available

Lara Rubbelke — Mon, 10 Aug 2009 15:54:00 GMT

The first public CTP of SQL Server 2008 R2 is available for download. Read more about it at the Data Platform Insider blog: http://blogs.technet.com/dataplatforminsider/archive/2009/08/10/download-sql-server-2008-r2-august-ctp-today.aspx...(read more)

Automating SQL Server 2005/2000 Policy Evaluation

Lara Rubbelke — Sat, 13 Jun 2009 16:11:00 GMT

The Enterprise Policy Management Framework version 3.0, a new version of the framework to support policy automated policy evaluation for SQL Server 2000 and 2005, has been posted to codeplex.

For those who are not familiar with the tool, the Enterprise Policy Management Framework is a reporting solution on the state of the SQL Server enterprise against a desired state defined in a policy. The key capabilities are to extend Policy-Based Management to all SQL Server instances in the enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will automate a scheduled evaluation of a set of policies against a group of servers, and provide reports for DBAs to understand where they have instances and database objects which are not complying with an organization’s defined standards.

The new 3.0 release includes the following enhancements:

Supports nested server groups in the Central Management Server

The previous versions did not support Central Management Server groups that were nested in parent groups. This restriction has been removed and you may now design CMS groups to fit your organization, and leverage these groups for the EPM Framework.

A new parameterized PowerShell execution

The PowerShell script has been updated with parameters. This enhancement will significantly ease how you may deploy the solution, so you only have a single script to manage. The previous versions would have required multiple versions of the PowerShell script you were to design the execution strategy by server group and policy category.

Policy results are stored in a table format

The new version 3.0 will shred the policy result XML document to a PolicyHistoryDetail table during the evaluation. The previous version only stored the XML data and issued queries against XML results stored in a SQL Server table named PolicyHistory. This update will greatly improve performance during reporting and provides a better platform for the community to build customized views and reports. This could also improve storage – you can purge the data in the PolicyHistory table if you do not require the XML results.

New Report Parameters

Based on feedback from the community, the new version includes parameters in the reports to support filtering by Central Management Server group. This will be a very important criteria for large organizations who would like to focus on specific groups of instances.

Fixes to error reporting logic

Not much to say, other than the logic that identifies errors stored in the tables is fixed.

Updated Documentation

The documentation has been updated, and should be much easier to follow when setting up the framework.

The EPM Framework leverages the Central Management Server, PowerShell, Reporting Services 2008, and Policy-Based Management. You will need at least one instance of SQL Server 2008 and an instance of SQL Server 2008 Reporting Services to support the framework. I will dive deeper into installation and configuration of the framework in subsequent blogs.

Please let me know if you are using the framework, and if you have suggestions for future enhancements. I am going to be integrating SQL Server 2008 Policy History centralization into the framework in the next version.

Should I Compress My Indexes? New Compression Whitepaper!

Lara Rubbelke — Sun, 31 May 2009 15:27:00 GMT

A very well written study of the compression is now available: Data Compression - Strategy, Capacity Planning and Best Practices. This study provides guidance on best practices for designing, implementing and managing compression in your environment. I found this whitepaper to be full of useful guidance and scripts to monitor and manage compressed tables.

The paper starts with tips and tools to help decide what to compress. It then guides the reader through considerations for implementing compression and estimating necessary resources to complete compression. The final sections of the paper provide guidance and evidence on management topics – space management, performance management, partition management.

I am certain that those who are considering compression will find these concepts, scripts and tools to support the full lifecycle of compression. I know that I will keep this on my short list of favorite whitepapers!

Achieving PCI Compliance Resources

Lara Rubbelke — Sun, 26 Apr 2009 14:36:56 GMT

This past week I was delivering some events for customers on supporting Mission Critical databases with SQL Server 2008. During the compliance conversation I mentioned that there were a couple of new resources available related specifically to PCI compliance.

Parente Randolph published a whitepaper on how to leverage SQL Server 2008 features to meet PCI compliance.

Furthermore, these auditors presented a TechNet webcast on the same subject.

I would also highly recommend the SQL Server 2008 Compliance Guide which will complement the above resources with technical implementation examples.

Another EKM Vendor Announced Support

Lara Rubbelke — Thu, 23 Apr 2009 03:05:00 GMT

This week Thales announced that their nCipher product line now integrates with SQL Server 2008 EKM. This announcement follows SafeNet as our second vendor to support EKM.

Standardized SQL Server 2008 Audit

Lara Rubbelke — Tue, 21 Apr 2009 04:24:00 GMT

Isn’t life grand when you have a standard approach? That is typically my frame of mind, which is one reason why I advocate for a standard approach to designing, deploying and managing your SQL Server 2008 audits. I have developed the attached standardized scripts which should get you started on your standardized approach.

The script SetupAudit_Shell is a single SQLCMD script that will create the Audit, the Server Audit Specification, and a Database Audit Specification in every user database (except Model and TempDB). The script is written to send the audit data to a file destination. You will need to alter this part of the statement if you prefer to send your audit data to the Windows Security or Application log. The Server Audit Specification and the Database Audit Specification statements do not specify the audit action groups – you will need to supply these based on what you want to audit.

The script SetupDataAccessAudit_Shell is a script to alter an existing Database Audit Specification with table level audit actions. This will apply the exceptions to your standard audit when compliance goals require you audit DML activity from tables which store sensitive data.

The remaining scripts (DisableAudit_Shell, EnableAudit_Shell, RemoveAudit_Shell) are scripts to help manage the audits which are deployed with the defined standards.

Enterprise Policy Management: Updates, New Whitepaper, and Upcoming Webcast

Lara Rubbelke — Wed, 04 Mar 2009 15:21:00 GMT

The next version of the Enterprise Policy Management Framework has been published to CodePlex. The new version includes error trapping and error reporting, and a few bug fixes. Please be sure to download the documentation as well, which will help you understand how to use the PowerShell variables to scale the execution in your environment.

A new whitepaper has been published outlining the intent of the Enterprise Policy Management Framework. A big thank you to Tom Davidson for all of the hard work writing the paper.

Finally, anyone who is interested in an introduction to Policy-Based Management and a demonstration of the EPM Framework in action can attend my TechNet webcast next Thursday, March 12: TechNet Webcast: Governing Your Enterprise with Policy-Based Management (Level 300). This webcast is best suited for those of you who…:

are currently frustrated with managing a SQL Server environment that is inconsistently deployed
want to reduce the time you spend developing and running scripts to determine issues in business and regulatory compliance
simply want to see a better way to manage groups of servers to better scale your time

Join in the webcast, it is sure to be fun and enlightening! In short, you will walk away with an understanding of Policy-Based Management, how to use the SQL Server 2008 Policy-Based Management framework to support down-version (SQL Server 2000 and 2005) policy evaluation, and how to implement the EPM framework (see picture below) for an understanding of policy compliance across the enterprise.

Policy-Based Management Configuration Policy

Lara Rubbelke — Sun, 01 Mar 2009 03:15:00 GMT

I have been working on some new materials for an upcoming webcast on leveraging Policy-Based Management using the Enterprise Policy Management Framework. As I was working through some sample policies, I noticed that my SQL Server 2008 instances were not consistently configured for history retention and to log on success. Well, I thought this was a good thing to put in a policy, as policies allow me to easily manage configuration settings for my instance. Much to my surprise, SQL Server 2008 does not currently have a Policy Based Management Configuration facet! I logged a suggestion to connect.microsoft.com (https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=419574), and hopefully we will see a new facet in the future. Please feel free to vote if you think this would be a helpful addition.

In the meantime, we still need to manage our PBM configuraton in our SQL Server 2008 installations. So I developed a policy which will execute some scripts and report the state of our environments. I cannot force configuration to my policy when conditions are defined with scripts, but at least I have a scalable solution to report on those instances which do not conform.

The attached policy defines the PBM management settings. I typically like to change the PBM Management properties such that the policy history is only retained for 14 days and successful evaluations are logged. If you care to look at these setting in Management Studio, right click on Policy Management to select properties.

When LogOnSuccess is set to false (default) successful evaluations are not logged. Setting LogOnSuccess to true will log all policy evaluation results. You will want to log on success if you decide to centralize policy history and perform enterprise management reporting. Without the log on success, you will only have failed evaluations. This makes it difficult to determine if a policy is currently in a failed state or if it was in a failed state in the past but is currently in compliance with your specifications.
HistoryRetentionInDays will determine how many days of policy history will be saved in the policy history tables in MSDB.

The attached policy is not configured to run on a schedule. You may want to change this setting prior to importing the policy to your SQL Server 2008 instances.

Evaluating Policies On Demand Through PowerShell

Lara Rubbelke — Thu, 19 Jun 2008 04:22:00 GMT

In my previous blog I reviewed the different policy evaluation modes available in SQL Server 2008. In this next installment I will detail how to initiate a policy on demand using PowerShell.

PowerShell integration with SQL Server 2008 is getting more press lately. In past blogs I noted a few resources to help learn PowerShell, and since that time additional resources (http://blogs.technet.com/industry_insiders/pages/powershell-in-sql-server-2008.aspx and http://blogs.msdn.com/mwories/archive/2008/06/14/SQL2008_5F00_Powershell.aspx to call out a few) continue to surface. This blog series is focused on Policy Evaluation, so I will be specifically looking at the new PowerShell cmdlet available in SQL Server 2008 to support an on demand evaluation of policies.

The cmdlet Invoke-PolicyEvaluation will allow you to specify a policy (or set of policies) to be evaluated against a target server. The general syntax is as follows:

Invoke-PolicyEvaluation [-Policy [(<string[]> | <Policy> | <FileInfo>)]] -TargetServerName <(string | ISfcConnection)> [-TargetExpression <string>] [-OutputXml] [-AdHocPolicyExecutionMode <AdHocPolicyExecutionMode>]

To open the SQL Server PowerShell provider through Management Studio, right-click on the Server and select Start PowerShell.

Note: You can also add the SQL Server PowerShell provider into the main PowerShell. Open PowerShell and run the following commands to load the SQL Server snap-ins. These are documented incorrectly in SQL Server 2008 RC0 BOL.

add-pssnapin SqlServerCmdletSnapin100

add-pssnapin SqlServerProviderSnapin100

Once you are in PowerShell, we can execute our commands to evaluate a policy. I encourage you to issue the following command, which will return a very detailed report on the cmdlet:

Get-Help Invoke-PolicyEvaluation -Detailed

A very simple example will assume that you have the SQL Server 2008 sample policies installed in the default location. On my laptop, they are located at "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033".

#Set the working location to the file with our sample policies

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml" -TargetServer "MyServerName"

Executing these commands will return the output on the console with the results of the policy evaluation. Note that these results are NOT stored in the MSDB database (as other evaluation modes will do).

If you would like to evaluate multiple policies, they are passed as a comma separated list as follows:

#Set the working location to the file with our sample policies

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml", "Database Auto Shrink" -TargetServer "MyServerName"

The Invoke-PolicyEvaluation cmdlet can accept inputs from a pipe. The following example will loop through and evaluate each policy stored in the file against the server.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

gci | Invoke-PolicyEvaluation -TargetServer "MyServerName"

By default, the -AdHocPolicyExecutionMode parameter is "Check", which will simply evaluate the policy and report back through the console or through an XML output file. The "Configure" option will reconfigure properties that failed compliance so they comply with the policy, as long as they are deterministic and settable.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml", "Database Auto Shrink" -TargetServer "MyServerName" -AdHocPolicyExecutionMode "Configure"

The final parameter I want to point out is the -OutputXml, which will produce an XML report. As mentioned above, the policy evaluation results are not stored in the MSDB database. Using this parameter, you can produce an output file which will be very useful when you want to review the information at a later time.

sl "C:\Program Files (x86)\Microsoft SQL Server\100\Tools\Policies\DatabaseEngine\1033"

Invoke-PolicyEvaluation -Policy "Database Auto Close.xml" -TargetServer "MyServerName" -OutputXml >"Report.xml"

So, yes you can evaluate a policy on demand through PowerShell. You can create a SQL Server Agent Job with a step that executes your PowerShell script (new in 2008).

I can hear what many of you are thinking - "<insert your name here>, why would I need to use this feature if I can simply create a policy on SQL Server 2008 and let the policy manage the server and have the output stored in MSDB?" In my opinion, the policy should be on the server when your server is SQL Server 2008. The server should be managed by the policy whenever possible. However... What if you want to evaluate a policy against a SQL Server 2000 or 2005 instance? Aha! Now you have a real use case for invoking policy evaluation through PowerShell.

This is just a brief overview of some of the ways to use this cmdlet, but as with all PowerShell and SQL implementations there are multiple ways to put these into action. In the next parts of the series, I will explore central management servers, data collectors, and examples on how to extend this cmdlet for an enterprise solution.

Policy Evaluation on a Single SQL Server 2008 Instance

Lara Rubbelke — Fri, 13 Jun 2008 17:52:00 GMT

SQL Server 2008 Books Online and the Policy Based Management (PBM) blog have documented how to create a policy. Please review these valuable resources if you are looking for an introduction to creating policies in SQL Server 2008. Through the remainder of this series, I will be focusing on the different methods of evaluating a policy against a target (server, database, database object). This first part will focus on policy evaluation on a single SQL Server 2008 instance. As we journey through the series, I will deliver methods to evaluate policies across multiple servers and against down versions of SQL Server (2000 and 2005).

As a brief introduction, SQL Server 2008 includes a new Policy Based Management (PBM) framework. This new framework allows you to specify intent in a policy, store the policy on a server and have the server managed by that policy (note: a policy may also be stored in a file and I will touch on this later in the series). Intent may be defined as a server configuration, database configuration, connectivity requirements, security requirements, business rules, naming conventions, and more. A policy will identify the targets (servers, databases, database objects) that must be managed by this intent and how they are managed by the intent. How the target is managed by the policy is dictated by the evaluation mode specified on the policy.

I think it is very important that we set expectations: Policy Based Management provides a level of prevention and enforcement, but the current version should be more strongly considered a framework to report and in some cases prevent and enforce compliance. This is a tool to more easily manage deployed servers, report on the state of SQL Server, and maintain consistency and compliance to your business and regulatory requirements.

SQL Server 2008 policies can be evaluated, or invoked, in a number of ways on a single instance. Not all evaluation modes are available to all policies. The facet will determine the evaluation modes available for your policy. I have pointed out the Policy Based Management blog in the past, and please spend a few minutes to review the blog on Facets. This blog is particularly enlightening as it points directly to the relationship between each facet and the supported evaluation modes. The team has developed a chart (hint: print this out and keep it nearby) which will be very useful when you are designing policies.

The following are available evaluation mode options when you save the policy on a SQL Server 2008 instance:

On Schedule

On Schedule will create a SQL Server agent job to execute the policy at scheduled intervals. This option is available for all policies created for the database engine. The results of the policy evaluation are available by viewing the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB outlined here.

Consider the following scenario: Many organizations are challenged with maintaining a consistent deployment of SQL Server. The process to build a new SQL Server may specify the appropriate settings, but over time these settings may be inadvertently updated. A DBA may change an AWE setting, alter configuration settings, or alter a backup schedule - any of which can increase the time managing the server and identifying the cause of an issue. As a result the organization either spends energy managing these configuration settings (manually reviewing periodically or building a heavily architected process to manage this process) or the enterprise has many different deployed builds in production (which leads to issues when standardizing the management of the database tier).

A policy can be deployed on these servers with an evaluation mode On Schedule. The schedule may run once per day (for example) to determine the status of the deployed instance. This mode will provide an ideal method for automating the evaluation of deployment standards and reporting on instances which violate these standards.

Check on Change: Log

Check on Change: Log will write output to the event log and to MSDB when an action is executed against one of the properties that are controlled by the policy.

For example, you may have a policy that requires xp_cmdshell is disabled. If a DBA were to enable xp_cmdshell an event is logged indicating that the policy has been violated. These logs may be viewed in the SQL Server event log, Windows NT Application Log, or through the policy views in MSDB.

Rounding out this picture, consider an alerting mechanism for violated policies that are critical. In some cases, you may want to be immediately notified of a violation to your policy. You may set up an alert triggered by these event logs through SQL Server or System Center which will send an email to a manager or lead administrator that an action was executed against your server that violates the policy.

Check on Change: Prevent

Check on Change: Prevent will disallow any changes that violate the policy. When a policy is created with this option, a DDL trigger is generated on the server. If you have multiple policies which are set to Check on Change:Prevent, they will all use the same DDL trigger.

As an example, you may have a business rule that requires all stored procedures in a particular application database follow a consistent naming convention. A policy is set up with this requirement and enforced on the server with the Check on Change: Prevent evaluation mode. When a developer issues a CREATE PROCEDURE statement, the server DDL trigger is started. SQL Server will evaluate the procedure against the conditions specified in the policy. If the procedure violates the condition, the CREATE PROCEDURE statement is rolled back and an error is returned to the developer. The policy will also write an event to the SQL Server event log, Windows NT Application log and the policy views in MSDB.

On Demand

On Demand is an option where you manually run the policy. Policies which are stored on SQL Server 2008 and evaluated On Demand are disabled and cannot be enabled. This option is available for all policies stored on a SQL Server 2008 instance or stored as an XML file. The results of policies executed On Demand are not written to the SQL Server Event Log, but are available in the policy views in MSDB.

Evaluating a policy On Demand on a single instance can include multiple flavors. The most obvious is manually running the policy in SQL Server Management Studio, as in the following examples.

Right-click on any policy stored in SQL Server 2008 and select Evaluate to manually evaluate the policy.
Right click the Policies folder in SQL Server Management Studio (SQL Server Instance>Management>Policy Based Management>Policies) and select Evaluate. The Evaluate Policies dialog will appear. By default, the policies stored on the SQL Server 2008 instance are available and you can check which policies you would like to evaluate. If the policies are stored as XML files, select the Browse(...) button. Navigate to the folder where the policy xml files are stored and select one or more policies (hold the shift key to select more than one policy).

In my next post in this series I will detail one of the lesser known (but very cool and useful) methods of evaluating a policy on demand.

SQL Server 2008 Policy Based Management Resources

Lara Rubbelke — Thu, 12 Jun 2008 20:17:00 GMT

I will be releasing a series of blogs related to Policy Based Management and PowerShell in SQL Server 2008. As a precursor to these blogs, I wanted to be sure that everyone is aware of the many great resources available online.

Policy-Based Management Blog

The SQL Server Manageability Team has started a new blog related to Policy-Based Management. This site will provide some great learning content for the terminology and development of policies. http://blogs.msdn.com/sqlpbm/

SQL Server 2008 Feature Pack

The following site will provide a number of additional features that you can download and use with SQL Server 2008. Of particular interest is the download for the Microsoft SQL Server 2008 Policies, which are policies that were developed for all of the best practices represented in previous versions of the Microsoft SQL Server Best Practice Analyzer. I find these policies to be great tools to learn about the different ways to develop, deploy and evaluate policies. http://www.microsoft.com/downloads/details.aspx?FamilyId=089A9DAD-E2DF-43E9-9CD8-C06320520B40&displaylang=en

SQL Server 2008 RC0 Books Online

SQL Server 2008 RC0 SQL Server Books Online can be downloaded and installed on your desktop (without negatively impacting other versions of Books Online). The documentation contains many great learning resources for Policy Based Management and PowerShell (and loads of other stuff). http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en

If you are reticent to installing Books Online on your desktop, you can access the entire contents of BOL online: http://msdn.microsoft.com/en-us/library/bb543165(SQL.100).aspx

Watch for some very interesting material coming soon...

Where did Activity Monitor go?

Lara Rubbelke — Mon, 09 Jun 2008 20:39:00 GMT

When you begin working with RC0, you may find yourself searching for Activity Monitor. I personally spent about 10 minutes looking for Activity Monitor when I started working with RC0. Luckily, I happen to work at Microsoft and can directly email people when I have these types of questions (well, lucky for me but perhaps not lucky for them:-))

In the past, you could only find Activity Monitor by opening the Management folder in SQL Server Management Studio. This is no longer the place to find Activity Monitor. Here are the two new ways to launch Activity Monitor:

1. Open Management Studio. Select the icon on the toolbar that is the furthest right (It looks like a chart with green and blue lines).

2. In Object Explorer, right click on the server and select Activity Monitor. Please note that the Activity Monitor is not available in the context menu unless you select the server. You cannot launch the Activity Monitor when you select a database, another folder, etc.

I am certain many of you are questioning why there was a change in location for the Activity Monitor. I was told that the intention was to make it "more discoverable". This provided me a great deal of humor as I spent 10 minutes looking for it and ended up asking the product team.

Happy monitoring!