Compliance (DW)---a Technical Reference Guide for Designing Mission-Critical DW Solutions

  • Article

Want more guides like this one? Go to Technical Reference Guides for Designing Mission-Critical Solutions.

Key Concepts

Many of the tier-one applications that customers deploy are required to follow government or business regulations. For example, in the U.S.A., healthcare organizations are required to follow Health Insurance Portability and Accountability Act (HIPAA) and HealthAct regulations, while financial organizations are required to follow payment card industry (PCI) and Sarbanes-Oxley (SOX) regulations. Failing to comply with these regulations can be very expensive. At a minimum, each failure to comply incurs financial penalties. For example, with the HealthAct regulations, each perceived breach of patient medical data incurs punitive penalties. Large-scale compliance failures result in negative publicity and the loss of trust in a company. For example, 15,500 Northern California Kaiser patients had their medical records breached in December 2009 causing unpleasant publicity.

Data warehouse applications fall under unique implementation guidelines in terms of how a data warehouse is used for analytical and reporting purposes. Since a data warehouse will often contain more than just raw data, such as a "complete picture" of a patient or a customer account for example, it is imperative that information be carefully protected.

Best Practices

The following resources provide some general information about compliance. (Note that the full URLs for the hyperlinked text are provided in the Appendix at the end of this document.)

  • The white paper, Reaching Compliance: SQL Server 2008 Compliance1, provides comprehensive guidance for governance, risk management, and compliance (GRC). The white paper also provides policy guidance and information about the application of IT features and sample scripts to reach these goals..

  • The Enterprise Policy Management Framework2 (EPM) is a CodePlex project that provides an end-to-end working framework for using Microsoft SQL Server Policy-Based Management features to reach compliance goals. A key contribution of the EPM is that it allows the inclusion of SQL Server 2000 and 2005 servers into the framework.

  • Centralized Auditing Framework 3 is a CodePlex project that provides an end-to-end working framework for using SQL Server’s XEvents-based Auditing feature to reach compliance goals.

  • FIPS standards are developed jointly by the National Institute of Standards and Technology (NIST) in the U.S.A. and the Communications Security Establishment (CSE) in Canada. The Microsoft Support article, “Instructions for using SQL Server 2008 in FIPS 140-2-compliant4,” discusses Federal Information Processing Standard (FIPS) 140-2 instructions and describes how to use SQL Server 2008 in FIPS 140-2 compliant mode.

  • The Microsoft Software Developer Network (MSDN) Webcast, "Addressing Compliance with SQL Server 20085,” provides details on compliance concepts, including identity management, data protection, separation of duties, auditing and reporting, and policy-based management using SQL Server 2008.

  • Consider using partitioned tables for large extraction, transformation, and load (ETL) operations. Loading data to an empty partition, creating the indexes, and switching the partition into the table can be faster than normal insert operations by orders of magnitude. For more information, see the following articles:

    Corporate Integrity is a strategy advisory firm that researches governance, risk, and compliance issues. Some customers have mentioned that they find the research documentation listed on their site, “Written Research6,” useful.

Case Studies and References

Industry standards compliance examples and implementation guidelines include:

Questions and Considerations

This section provides questions and issues to consider when working with your customers.

  • Understand the government regulations and compliance requirements by working with external partners to develop best practices and technical guidance associated with the regulatory guidance. In particular, seek to establish the requirements for the country/region in which the organization is operating.

  • Understand which data points are required for full auditing across different regulations.

  • Understand the security and encryption requirements across different regulations (for example, private/public key at the field level, and column-based encryption).

  • Understand how different organizations manage their data and comply with their own internal policies, which are often a superset of the standard regulations.

  • Most importantly, understand the minimal working set of IT features required to meet all of these regulations and policies. If you use fewer technologies, the solution is potentially simpler.

Appendix

Following are the full URLs for the hyperlinked text.

1 "Reaching Compliance: SQL Server 2008 Compliance Guide,"http://sqlcat.com/whitepapers/archive/2008/11/15/reaching-compliance-sql-server-2008-compliance-guide.aspx

2 "Enterprise Policy Management Framework," https://www.codeplex.com/EPMFramework

3 "Centralized Auditing Framework," http://sqlcat.codeplex.com/wikipage?title=sqlauditcentral&referringTitle=Home

4 "Instructions for using SQL Server 2008 in FIPS 140-2-compliant mode," https://support.microsoft.com/kb/955720

5 "MSDN Webcast: Addressing Compliance with SQL Server 2008," http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?EventID=1032389193&EventCategory=5&culture=en-US&CountryCode=US

6 "Written Research" (at the Corporate Integrity website), http://www.corp-integrity.com/written-research

7 "ParenteBeard: Deploying SQL Server 2008 Based on Payment Card Industry Data Security Standards (PCI DSS)," http://www.parentebeard.com/Uploads/Files/Deploying_SQL_Server_2008_Based_on_PCI_DSS.PDF

8 "TechNet Webcast: SQL Server 2008 Capabilities for Meeting PCI Compliance Needs," http://msevents.microsoft.com/CUI/WebCastEventDetails.aspx?culture=en-US&EventID=1032404174&CountryCode=US

9 "Beth Israel Deaconess Medical Center: Major Hospital Enhances Auditing Infrastructure using SQL Server 2008,” https://www.microsoft.com/canada/casestudies/Case_Study_Detail.aspx?casestudyid=4000003892

10 "TechNet Webcast: Supporting HIPAA Compliance with SQL Server 2008," http://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032441700&Culture=en-US

11 "Jefferson Wells: Supporting HIPAA Compliance with Microsoft SQL Server 2008," http://www.jeffersonwells.com/mssql2008hipaa