Add to social bookmarks:
4-page Case Study - Posted 3/17/2009
Views: 1791
Rate This Evidence:

Beth Israel Deaconess Medical Center

Major Hospital Enhances Auditing Infrastructure using SQL Server 2008

As a teaching hospital of Harvard Medical School, and the largest member of the CareGroup Healthcare System, Beth Israel Deaconess Medical Center needs an IT infrastructure with auditing capabilities to help it remain in compliance with regulations including those of the Health Insurance Portability and Accountability Act (HIPAA). The organization has more than 2 terabytes of information, including data warehouses, clinical applications, and other solutions hosted using Microsoft® SQL Server® database software. BIDMC is upgrading to Microsoft SQL Server 2008 Enterprise to take advantage of advanced auditing and transparent data encryption features in the latest version of SQL Server to help it meet HIPAA and other regulatory requirements. As early adopters, CareGroup IT professionals helped Microsoft create the new SQL Server 2008 Compliance Software Development Kit.


Beth Israel Deaconess Medical Center (BIDMC), a teaching hospital of Harvard Medical School, and a member of the CareGroup Healthcare System, has more than 5,000 employees including more than 800 full-time staff physicians, most of whom hold faculty appointments at Harvard Medical School. The medical center, which also employs 1,100 full-time registered nurses, cares for more than 40,000 admitted patients each year, and serves more than 470,000 outpatient visits.

CareGroup Healthcare System, of which BIDMC is the largest member, also includes Beth Israel Deaconess Hospital-Needham; New England Baptist Hospital, located in Boston; and Mount Auburn Hospital, in Cambridge, Massachusetts. CareGroup provides IT support for hospitals in the group, including hosting more than 3.5 million patient electronic medical records (EMRs) and multiple data warehouses for reporting and analytics. CareGroup hosts more than 2 terabytes of information stored on 460 databases on 30 instances of Microsoft® SQL Server® database software. The main clinical data is stored on an InterSystems Caché system, while SQL Server is used to support data warehouses, data marts, a spectrum of clinical applications, and auditing.

* SQL Server 2008 and the SQL Server 2008 Compliance SDK are significant resources for … governance, risk management, and compliance. Very cool technology. *

John D. Halamka, MD, MS
Chief Information Officer, CareGroup Health System, and Chief Information Officer and Dean for Technology, Harvard Medical School

Every healthcare provider in the United States is required by the Health Insurance Portability and Accountability Act (HIPAA) to safeguard the use and disclosure of a patient’s protected health information. Beyond HIPAA, BIDMC and CareGroup are always looking for ways to enhance data security. To help enhance security, and to help ensure HIPAA compliance, CareGroup and BIDMC needed an auditing solution that would work across terabytes of relational data stores.

“Auditing is a critical component of HIPAA compliance and ensuring patient privacy,” says John D. Halamka, MD, MS, Chief Information Officer of the CareGroup Health System, and Chief Information Officer and Dean for Technology at Harvard Medical School. “We currently have 1 billion rows of audit data from 146 mission-critical clinical applications. Our comprehensive audits of every clinical lookup yield 300,000 to 500,000 transactions per day. HIPAA requires an audit system to record who is looking up what, where and why. We need to keep these audit logs for 20 years.”


It was against this backdrop of compliance and auditing concerns that BIDMC began upgrading its database infrastructure to SQL Server 2008 Enterprise, from SQL Server 2005. CareGroup IT professionals were so impressed with the auditing and encryption features of SQL Server 2008, running on the Windows Server® 2008 Enterprise operating system, that they became early adopters of SQL Server 2008 and helped Microsoft create the SQL Server 2008 Compliance Software Development Kit (SDK).

The participation of CareGroup in creating the Compliance SDK, helped to provide real-world, step-by-step validation for the information. The Compliance SDK provides a guide to governance, risk management, and compliance (GRC) efforts.

BIDMIC had been very happy with its experiences using SQL Server 2005, but wanted to take advantage of new features in SQL Server 2008 Enterprise including advanced auditing and transparent encryption to help it meet HIPAA and other regulatory requirements.

The decision to upgrade early was easy because CareGroup and BIDMC have long been impressed with the enterprise-grade performance of SQL Server. “With electronic medical records, we can’t be down for even a second,” says Ayad Shammout, Lead Technical Database Administrator at CareGroup HealthCare System, and a major contributor to the Compliance SDK efforts. “Physicians and nurses need the ability to immediately access the EMR at all times, and SQL Server has proven itself able to meet these demands.”

BIDMC has upgraded several servers to SQL Server 2008, including the databases supporting its GRC solution, and anticipates having all of its SQL Server instances, which make up about 85 percent of the organization’s database landscape, upgraded to SQL Server 2008 by the end of 2009. In some cases the upgrades will be timed with acquisition of new hardware. BIDMC led the way in becoming the first CareGroup hospital to deploy a GRC auditing solution using new features in SQL Server 2008.

The Basics of GRC
Beyond healthcare and HIPAA, many organizations are affected by regulatory requirements such as complying with Sarbanes-Oxley or Payment Card Industry Data Security Standard (PCI DSS). And even companies that are not impacted by regulatory requirements need to reach compliance with their own organizational policies. All face the challenges of determining what needs to be protected, how it should be protected, and ensuring that protection is implemented.

Although called GRC, logically it might also be called RGC, as a program generally begins with risk management and the associated risk assessment, prioritization, and mitigation planning. Basics of GRC include:

  • Risk Management. Risk management begins with an assessment of each area of an organization to determine where risks may exist. BIDMC performed this on a department-by-department and application-by-application basis. After risks are identified, they are prioritized, and based on those priorities, an action plan is put into place to avoid or mitigate the risk.
  • Governance. Governance represents the actions taken to address the risks identified during the assessment. This is the step where policies, IT controls, best practices, systems, and training are put into place to mitigate risks. An important element of governance is creating policies to ensure that data integrity is preserved, including monitoring to ensure that sensitive data is not being inappropriately accessed or changed.
  • Compliance. Compliance is the validation that identified risks are being mitigated. Basics of compliance include developing the ability to show that policies have been developed and deployed to address identified risks, and being able to prove that policies have been in place and followed during the enforcement period. Auditing is an essential element of compliance, so compliance solutions should be designed to make it easy for auditors to validate compliance. Centralization of auditing systems helps to improve the efficiency of compliance auditing. These techniques can lower auditing costs and minimize disruption to daily operations.

“We need to safeguard access and audit to ensure compliance not just on the medical records level, but also on the server level,” says Shammout. “We need the ability to track who is making a change on the server, who is dropping a table, who is creating a new database, who changed a password, who is granting permissions. We need to capture all of this and store it in a master repository.”

The SQL Server 2008 Compliance SDK
The SQL Server 2008 Compliance SDK was created to help organizations meet their GRC needs regardless of the type of regulatory requirements with which they need to comply. The Compliance SDK helps organizations with a spectrum of operations, including:

  • Implementing IT controls with SQL Server 2008 
  • Securing the platform 
  • Controlling identity and separation of duties 
  • Encrypting database information 
  • Monitoring key access 
  • Auditing sensitive operations 
  • Using SQL Server 2008 Policy-Based Management to implement compliance practices 
  • Managing encryption keys 
  • Managing auditing 
  • Managing Policy-Based Management policies

For an in-depth look at the SQL Server 2008 Compliance SDK, please see the Microsoft white paper "Reaching Compliance".

* Reporting Services enables us to pull extremely helpful data from the audit repository. *

Ayad Shammout
Lead Technical Database Administrator, CareGroup HealthCare System

BIDMC used SQL Server 2008 Enterprise and the Compliance SDK to implement a federated audit system that consolidates all audit logs from multiple SQL Servers into one place. The organization uses a SQL Server 2008 Integration Services package executed every 15 minutes to fetch through the audit files from a centralized file location and upload the data to its SQL Server 2008 instance hosting an audit data repository to capture information from multiple levels, including:

  • Server Level. Login/Logout/Failed events, and server configuration changes
  • Database Level. Create/Alter/Drop database events
  • Object Level. Create/Alter/Drop object events
  • Data Level. Insert/Update/Delete and select events (Select events will be activated in the project’s phase 2.)

The organization uses SQL Server 2008 Reporting Services to query and view audited data, for example to see who made a specific change, who modified a table, or who performed an insert, update, or delete on a record.

“Part of our Phase 2 plans is to process all audit data using SQL Server 2008 Analysis Services, to create multidimensional cubes to analyze the collected data, and build reports and alerts based on thresholds,” says Halamka. “A threshold-based alert could be used, for example, to alert database administrators if the system is experiencing an abnormally high level of attempted logons, a feature that will be implemented as part of the project’s Phase 2.”


Upgrading to SQL Server 2008 Enterprise and using resources from the SQL Server 2008 Compliance SDK have helped BIDMC achieve the easier compliance auditing it needed to help ensure compliance with HIPAA and other regulations. BIDMC enjoys the efficiency of SQL Server 2008 Policy-Based Management, and the low system overhead required for SQL Server auditing. The organization is gaining tighter security using SQL Server 2008 Transparent Encryption, and gaining insights from reports generated with Reporting Services.

Easier Compliance Auditing
BIDMC has found compliance auditing easier to deploy and manage since upgrading to SQL Server 2008, and taking advantage of the new enhanced data auditing feature which provides comprehensive data auditing to help organizations monitor all events at the server and database levels, and scale-out auditing across the enterprise.

“Prior to upgrading to SQL Server 2008 we used a collection of third-party tools for auditing,” says Shammout. “The old solution was difficult to integrate and configure. Additionally the audit information was stored locally on the individual databases or applications, which made the audit process cumbersome. It lacked the kind of central repository we’ve been able to create with SQL Server.”

Development is easier working with the auditing features that are built into SQL Server 2008. “Our developers love having auditing tools built into SQL Server 2008,” Shammout says. “They don't have to worry about writing their own custom code to perform audits on their applications. They know that SQL Server is automatically capturing the information they need in the background.”

BIDMC is enjoying savings from its auditing efforts because it has been freed from third-party licensing costs and because its internal developers no longer have to create custom auditing solutions when creating applications. CareGroup plans to deploy the same solution to cover all of its operations. “Eliminating third-party licensing and freeing up our developers from creating custom audit solutions provides us with ongoing operational savings,” Shammout notes.

“SQL Server 2008 and the SQL Server 2008 Compliance SDK are significant resources for any organization seeking to tighten governance, risk management and compliance,” says Halamka. “This is very cool technology.”

Efficiency of Policy-Based Management
Policy-Based Management is a system for managing one or more instances of SQL Server 2008. When SQL Server policy administrators use Policy-Based Management, they use SQL Server Management Studio to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects.

Policy-Based Management permits the definition of categories that point to a set of policies that can be targeted at a database or database object such as a user, a table, or a stored procedure. Each Policy-Based Management policy can be monitored to ensure it is being complied with. A policy can be monitored on a schedule or when a change occurs in the target for which the policy was created.

The Compliance SDK has an extensive section on benefitting from Policy Based Management. For example, audit data collected from Policy-Based Management policies can help organizations establish baselines, from which exception alerts can be created.

“Aggregating audit information may show that on a daily basis you normally see 100,000 insert statements into a specific database,” says Shammout. “If that figure suddenly jumps to 1 million inserts a day, then either something is wrong or perhaps a new application has been added. Either way you want to find out why the value has changed. SQL Server 2008 Policy-Based Management is very effective for establishing policies, monitoring metrics, and applying best practices.”

Low System Overhead
New for SQL Server 2008, SQL Server Extended Events (Xevents) plays a role in enabling BIDMC to gain the audit functionality it requires while having very little impact on processing resources. The Xevents infrastructure is a lightweight mechanism that supports capturing, filtering and acting upon events generated by the server process.

“We’ve been extremely pleased with the minimal impact our auditing efforts have on processing power,” says Shammout. “For most instances we see less than a 1 percent impact on CPU usage. For extreme cases where we are gathering an unusual amount of audit information, we rarely see CPU usage go beyond 3 percent. This is important to us because we want to preserve our processing resources for the applications we are supporting.”

Tighter Security with Transparent Encryption
BIDMC plans to take advantage of the transparent encryption built into SQL Server 2008, which enables encryption of an entire database, data files, and log files, without the need for application changes. Some of the benefits of transparent data encryption include searching encrypted data using both range and fuzzy searches, and searching secure data from unauthorized users.

The organization is especially interested in using transparent encryption to protect backups. “We would very much like to use encryption for our database backup files,” says Shammout. “With the Transparent Encryption feature of SQL Server 2008, the database can be used to implement encryption without touching the front-end application.”

BIDMC has found encryption easier to work with since upgrading to SQL Server 2008. “We didn’t feel we could take full advantage of the encryption feature in earlier versions of SQL Server because it required modification on the application and on the client side, which was a big issue for us,” says Shammout. “With SQL Server 2008, encryption can be implemented in a seamless manner.”

Insights from Reporting
SQL Server 2008 Reporting Services is used to pull high-value reports from the central repository of audit information. The reports are accessed through a portal using Windows Server 2008 Active Directory® to implement role-based access. “Reporting Services provides a powerful reporting tool for examining what is happening with audit information,” Shammout says. “We are able to design reports that provide aggregate information and enable drilling down to whatever level of detail is desired. You can see who ran a specific query, at what time, and from what IP address. Reporting Services enables us to pull extremely helpful data from the audit repository.”

In summary, BIDMC has been able to enhance its GRC efforts, including HIPAA compliance, by upgrading to SQL Server 2008 Enterprise, and using resources from the SQL Server 2008 Compliance SDK that it helped Microsoft create.

Windows Server 2008, SQL Server 2008, and Visual Studio 2008
Windows Server 2008, SQL Server 2008, and Visual Studio 2008 provide a secure and trusted foundation for creating and running your most demanding applications. Combined, the products offer advanced security technology, developer support for the latest platforms, improved management and Web tools, flexible virtualization technology to optimize your infrastructure, and access to relevant information throughout your organization.

For more information about Windows Server 2008, go to:

For more information about SQL Server 2008, go to:

For more information about Visual Studio 2008, go to:

For More Information
For more information about Microsoft products and services, call the Microsoft Sales Information Center at (800) 426-9400. In Canada, call the Microsoft Canada Information Centre at (877) 568-2495. Customers who are deaf or hard-of-hearing can reach Microsoft text telephone (TTY/TDD) services at (800) 892-5234 in the United States or (905) 568-9641 in Canada. Outside the 50 United States and Canada, please contact your local Microsoft subsidiary. To access information using the World Wide Web, go to:

For more information about Beth Israel Deaconess Medical Center products and services, visit the Web site at:

This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Document published March 2009

Solution Overview

Organization Size: 8000 employees

Organization Profile

Beth Israel Deaconess Medical Center (BIDMC), a teaching hospital of Harvard Medical School, is the largest member of CareGroup Healthcare System. 

Business Situation

BIDMC needed a better auditing solution to help ensure compliance with the Health Insurance Portability and Accountability Act (HIPAA).


BIDMC is upgrading to Microsoft® SQL Server® 2008 Enterprise database software and using resources from the SQL Server 2008 Compliance Software Development Kit to enhance its auditing infrastructure used for ensuring compliance with HIPAA and other regulations.

  • Easier compliance auditing
  • Efficiency of Policy-Based Management
  • Low system overhead
  • Tighter security with transparent encryption
  • Insights from reporting

  • Intel-based servers

Software and Services
  • Microsoft SQL Server 2008
  • Windows Server 2008
  • Microsoft SQL Server Analysis Services
  • Microsoft SQL Server Report Server
  • Microsoft Active Directory Domain Services

Vertical Industries
Health Provider

Country / Region
United States

Business Needs
  • Cloud & Server Platform
  • Business Intelligence and Reporting