Policy Management Tool / Framework for SQL Server

In today's world, where regulations are increasing day by day like SOX, HIPPA, Sarbanes-Oxley, etc and are becoming a key factor to execute projects, compliance is becoming a big consideration. In terms of SQL Server, Microsoft has already placed reasonable efforts in the direction of compliance. More about compliance can be checked on the Compliance Portal.

Codeplex is a treasure house, and the more you explore it the more you become richer. One more framework in the direction of compliance is Enterprise Policy Management Framework. Though I have not had a chance to evaluate this framework, but this is one my evaluation list now followed by DTLoggedExec tool. If the tool / framework really lives upto what is claims (as mentioned in the below which is excerpt of the documentation of this framework), it has a potential similar what was .NET Framework 1.0 when it got released. Regulation specific customized wrappers can be built upon this policy-based management tool which can be used as templates for respective regulatory environments.

The Enterprise Policy Management Framework is a reporting solution on the state of the enterprise against a desired state defined in a policy. Extend Policy-Based Management to all SQL Server instances in the enterprise. Centralize and report on the policy evaluation results.

The Enterprise Policy Management Framework (EPM) is a solution to extend SQL Server 2008 Policy-Based Management to all versions of SQL Server in an enterprise, including SQL Server 2000 and SQL Server 2005. The EPM Framework will report the state of specified SQL Server instances against policies that define the defined intent, desired configuration, and deployment standards.

When the Enterprise Policy Management Framework (EPM) is implemented, policies will be evaluated against specified instances of SQL Server through PowerShell. This solution will require at least one instance of SQL Server 2008. The PowerShell script will run from this instance through a SQL Server Agent job or manually through the PowerShell interface. The PowerShell script will capture the policy evaluation output and insert the output to a SQL Server table. SQL Server 2008 Reporting Services reports will deliver information from the centralized table.

This solution requires the following components are configured in your environment. All SQL Server 2008 requirements listed below may be executed from and managed on the same instance:

• SQL Server 2008 instance to store policies
• SQL Server 2008 instance to act as the Central Management Server
• SQL Server 2008 instance to execute the PowerShell script
• SQL Server management database and policy history table to archive policy evaluation results
• SQL Server 2008 Reporting Services to render and deliver policy history reports

General Distribution Release (GDR) for Microsoft Visual Studio Team System 2008 (VSTS) Database Edition : Formerly known as Power Tools or Data Dude

In November 2008, the General Distribution Release (GDR) for Microsoft Visual Studio Team System 2008 (VSTS) Database Edition was released. The GDR installs on top of the initial release of VSTS 2008 Database Edition, but it is more than a minor version upgrade. The GDR adds support for SQL Server 2008, incorporates improvements to existing features, includes many new features and extensibility points, and incorporates features that were previously released as power tools.

New features in GDR includes support for offline schema development, tools that support new processes that you can use when you develop a database schema, and features that support database administration.

In addition to these process improvements, VSTS Database Edition GDR also provides the following capabilities:

• Interpretation and evaluation of your project's schema and interdependencies. Offline processing enables developers to catch syntax and reference errors prior to deployment.
• Refactoring—By using VSTS Database Edition, you can change the name of an object (such as a table or column), and that change will update all references to the new name.
• Automated differencing engine—When you deploy a project, it generates a Transact-SQL (T-SQL) script that contains only the necessary changes to make the target database match the source.
• Database unit testing—You can use a designer that enables development of T-SQL-oriented tests to exercise and verify your schema prior to checking in your code.
• Test data generation—You can use this tool to generate pseudo-random realistic test data that can be used when you run unit tests.

The points mentioned above about GDR are excerpt from an article titled "Introducing New Features In The VSTS Database Edition GDR" by Jamie Laflen and Barclay Hill. This article is published in the March 2009 issue of MSDN Magazine.

Internals of MERGE Statement in SQL Server 2008 : MERGE statement behaviour or impact on triggers

The MERGE statement combines the four separate DML statements (SELECT, INSERT, UPDATE, and DELETE) involved in a merge operation. The actual internal implementation of MERGE is the very same as the distinct DML statements it encapsulates. This means that upgrading the existing installations and then using MERGE statement with the newly created or existing deliverables wont break any code.

All AFTER and INSTEAD OF triggers that have already been defined in existing tables or updateable views continue to fire when those tables or updatable views are designated as the target of a MERGE statement. For example, the WHEN NOT MATCHED THEN...INSERT clause fires any insert triggers defined for the target; similarly, the WHEN MATCHED THEN...DELETE clause fires delete triggers. The end result is that triggers are fired just the same as they would be using separate statements instead of MERGE. Existing business logic, constraints, and rules all continue to function as they did before.

The MERGE statement also supports the same OUTPUT clause introduced in SQL Server 2005 for the INSERT, UPDATE, and DELETE statements. This clause returns change information from each row affected by DML operations in the same INSERTED and DELETED pseudo-tables exposed by triggers. Being able to capture this information in the OUTPUT clause is a much better choice than capturing it in triggers, since triggers introduce nondeterministic behavior—that is, you cannot guarantee that multiple triggers on the same table will consistently fire in the same order every time, and that is often the cause of subtle bugs that are very difficult to track down.

In addition to INSERTED and DELETED pseudo-table columns, a new virtual column named $action has been introduced for OUTPUT when used with the MERGE statement. The $action column will return one of the three string values—'INSERT', 'UPDATE', or 'DELETE'—depending on the action taken for each row.

References: Microsoft Press Books

New features in SSIS 2008 that can Improve SSIS 2008 Performance over SSIS 2005 comparatively

An integral part of any BI system is the data warehouse — a central repository of data that is regularly refreshed from the source systems. The new data is transferred at regular intervals (often nightly) by extract, transform, and load (ETL) processes.

Typically the data in the data warehouse is structured as a star schema or snowflake schema or a hybrid between the two. No matter which structure is chosen, after the new data has been loaded into the data warehouse, many BI systems copy subsets of the data to function-specific data marts where the data is typically structured as a multi-dimensional OLAP cube.

Below are some of the points and new features of SQL Server 2008, using which I feel that, efforts made on SSIS performance tuning and optimization (in SSIS 2005 post development) would be reduced to a major level. Also this rich feature set of SQL Server 2008 should help reduce development time and improve performance compared to SSIS / SQL Server 2005.

1) Feasiblility Study: Develop a Proof Of Concept

Before venturing with any new feature that has been introduced in a new product or product version, it's always a good design practice to develop a POC of the new feature in alignment with the technical requirements.

Deciding upon a Proof of Concept project is an excellent way to gain support and influence people. This might involve the creation of an OLAP cube and the use of visualization software for one (or several) business units. The project can be used to show business people what they can expect from the new system and also to train the BI team. Choose a project with a small and well-defined scope that is relatively easy to achieve. Performing a Proof of Concept requires an investment of time, effort, and money but, by limiting the scope, you limit the expenditure and ensure a rapid return on the investment.

2) Analyze OLTP Source System : Use data profiling to examine the distribution of the data in the source systems

The new Data Profiling task in Integration Services can be used to initially understand the nature of the source data for design purposes. However, the profiles it produces can also be used to apply business rules to data as part of the transformation process. Suppose, for example, the business rule says that the data from a particular source is acceptable only if the number of nulls does not exceed 1%. The profiles produced by a Data Profiling task can be used to apply this rule.

You can use the information you gather by using the Data Profiler to define appropriate data transformation rules to ensure that your data warehouse contains “clean” data after ETL, which leads to more accurate and trusted analytical results. The Data Profiler is a data flow task in which you can define the profile information you need.

Eight data profiles are available; five of these analyze individual columns:

· Column Null Ratio
· Column Value Distribution
· Column Length Distribution
· Column Statistics
· Column Pattern

Three analyze either multiple columns or relationships between tables and columns:

· Candidate Key
· Functional Dependency
· Value Inclusion

Multiple data profiles for several columns or column combinations can be computed with one Data Profiling task and the output can be directed to an XML file or package variable. The former is the best option for ETL design work.

Note that Data Profiling tasks profile SQL Server tables; data in other locations must be loaded into staging tables before it can be profiled.

3) Use MERGE statement for dealing with Slowly Changing Dimensions : During the extract phase you often need multiple Data Manipulation Language (DML) queries in order to perform one logical movement of the deltas into the relevant table. This is particularly true when you have to deal with slowly changing dimensions. SQL Server 2008 allows you to combine these multiple queries into one MERGE statement.

The MERGE statement performs insert, update, or delete operations on a target table based on the results of a join with a source table.

The MERGE statement provides three types of WHEN clauses:

· WHEN MATCHED enables you to UPDATE or DELETE the given row in the target table when the source and target rows match some criteria or criterion.
· WHEN NOT MATCHED [BY TARGET] enables you to INSERT a row into the target when it exists in the source but not in the target.
· WHEN NOT MATCHED BY SOURCE enables you to UPDATE or DELETE the given row in the target table when it exists in the target but not in the source.

You can specify a search condition with each of the WHEN clauses to choose which type of DML operation should be performed on the row.

The OUTPUT clause for the MERGE statement includes a new virtual column called $action that you can use to identify the DML action that was performed on each row.

4) Use Change Data Capture to simplify extract process.

SQL Server 2008 has a new data tracking feature that is of particular benefit in data warehousing. The Change Data Capture process tracks changes to user tables and collects them into a relational format. A typical use would be to track changes in an operational database for later inclusion in the warehouse.

The capture process collects change data from the database’s transaction log and inserts it into a change table. Metadata about each transaction is also inserted into a metadata table so that changes can be ordered with regard to time. This enables the identification of, for instance, the type of change made to each row, and which column or columns changed in an updated row. It is also possible to request all rows that changed between two time/dates.

5) Use new Lookup Transformation

In SQL Server 2008, the Lookup transformation supports two connection types when connecting to the reference dataset: the Cache connection manager and the OLE DB connection manager. The reference dataset can be a cache file, an existing table or view, a new table, or the result of an SQL query.

Reference data is usually cached for efficiency and now a dataflow can be used to populate the cache. Many potential sources can be used as reference data: Excel, XML, text, Web services—anything within reach of an ADO.Net provider. In SQL Server 2005, the cache could only be populated by an SQL query and a Lookup could only take data from specific OLE /DB connections. The new Cache Transform component populates a cache defined by the Cache connection manager.

The cache no longer needs to be reloaded each time it is used: this removes the speed penalty incurred by reloading from a relational source. If a reference dataset is used by two pipelines in a single package, the cache can be saved to permanent file storage as well as to virtual memory so it is available to multiple Lookups within one package. Furthermore the cache file format is optimized for speed and its size is unrestricted.

The miss-cache feature is also new. When running directly against the dataset, a Lookup component can add to the cache any key values from the source where there is no matching value in the reference dataset. So if Lookup has once determined that the reference set does not contain, for example, the value 885, it does not waste time inspecting the reference set for that value if it appears again in the source data. Under certain conditions this feature can produce a performance improvement of 40%.

Finally there is now a ‘Lookup no match output’ to which ‘miss-cache’ rows can be directed instead of going to the error output.

References: Data warehouse Best Practices - MSDN, BOL

Free Tool to read SQL Server 2005 / 2008 Transaction Log files

Say you need to provide a solution to log and show all Create, read, update and delete (CRUD) transactions for auditing, this is more a mandatory for SOX & HIPPA regulations.

Using the standard SQL Server methods which is an undocumented statement DBCC LOGINFO will get information, but the problem is it cannot be interpreted in simple terms as per the requirement. Hence the rise of third party tools in this area is evident. In order to enable to read SQL Server transaction log files and provide an in-house tool to show the CRUD transactions on a set of databases in a customized manner is a demanding task for a DBA (now a days). The other aspect is to use Server side trace (Profiler) to track the statements on the database (which will impact the performance if the server is already stressed out for resources)

As per the note above using undocumented DBCC command you will get:
DBCC LOG([,{01234}])

• 0 - Basic Log Information (default)
• 1 - Lengthy Info
• 2 - Very Length Info
• 3 - Detailed
• 4 - Full
  

Example:
DBCC log (, 4)

And it displays the following transaction log information:

• Current LSN
• Operation (string starts with LOP_XXXXX)
• Context (string starts with LCX_XXXX)
• Transaction ID
• Tag Bits (Hex)
• Log Record Fixed Length (int)
• Log Record Length (int)
• Previous LSN
• Flag Bits (Hex)
• Description (string)
• Log Record (Hex)
  

As it is undocumented on the Microsoft articles its very hard to interpret what lies beneath those HEX values, therefore using third party tools such as APEX-SQL Log Reader and Lumigent's Log Explorer are more helpful to such users that are needed to cater the management needs. But these all tools comes for a heavy price costing dollars in four digits.

Now comes the climax of the entire article, a tool is available which can read and display contents of transaction log files in a very informative manner. The tool is called Internals Viewer for SQL Server. This tool has three main features / sub-tools: Allocation Map, Page Viewer, and Transaction Log Viewer. Also this tool integrates with SSMS and works for both SQL Server 2005 and SQL Server 2008. This tool might not be as capable as the ones listed above, but the functionality it provides that too for free is unmatched with any other tools in my knowledge.

This tool can be a very handy and economic debugger in case of a SSIS Load package failure, especially in a parent-child load package architecture. In such an architecture, tens / hunderds of SSIS child packages are executed SSIS by parent / master packages, and the transaction handling becomes so complex, that architects carelessly / helplessly amends point-in-time database recovery as the failure recovery option or end up buying one of the tools mentioned above to rollback specific transactions to recover the damaged relational datawarehouse. This also give a good hint / example of how important transaction handling remains in an ETL / SSIS project.

As per the documentation of this tool, it offers the below mentioned features:

Integration with SSMS (SQL Server Management Studio) 2005 and 2008:

• The application is installed as a SSMS add-in
• Internals information integrated into the Object Explorer
• Transaction Log viewer integrated into the Query Results

Allocation Map:

• Displays the physical layout of tables and indexes
• Displays PFS status
• Overlay pages in the Buffer Pool

Page Viewer:

• Displays Data pages including forwarding records and sparse columns
• Displays Index pages
• Displays allocation pages (IAM, GAM, SGAM, DCM, and BCM pages)
• Displays pages with SQL Server 2008 row and page compression

Below are some of the screen-prints of this tool:

References: sqlserver-qa.net