CareGroup Healthcare System is responsible for protecting the privacy and integrity of 2 terabytes of patient information and related data. Based in Boston, CareGroup is the corporate parent of Beth Israel Deaconess Medical Center, a teaching hospital of Harvard Medical School, and three other area hospitals. CareGroup hosts its data on 390 databases on 30 instances of Microsoft® SQL Server™ 2005. The organization is updating databases to SQL Server 2008 to take advantage of new features including advanced auditing and transparent encryption to help it meet HIPAA and other regulatory requirements. CareGroup is using Policy-based Management, new for SQL Server 2008, to enforce policy and schema across its operations, and is centralizing reporting using SQL Server 2008 Reporting Services accessed through a portal created using Microsoft Office SharePoint® Server 2007.
Based in Boston, CareGroup Healthcare System is the corporate parent of four area hospitals including the prominent Beth Israel Deaconess Medical Center, a teaching hospital of Harvard Medical School. Rounding out the CareGroup family are Beth Israel Deaconess Hospital-Needham; New England Baptist Hospital, located in Boston; and Mount Auburn Hospital, in Cambridge, Massachusetts. Hospitals of the CareGroup Healthcare System support more than 400,000 clinical encounters per year.
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 about 2 terabytes of information stored on 390 databases on 30 instances of Microsoft® SQL Server™ 2005 database software.
The organization has been impressed with SQL Server, and appreciated the new features and performance enhancements when it migrated from earlier versions of SQL Server to SQL Server 2005. “With electronic medical records, we can’t be down for even a second,” says Ayad Shammout, Lead Technical Database Administrator at CareGroup HealthCare System. “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.”
Because of regulatory requirements and the inherent need for keeping patient information private, CareGroup is always looking for ways to enhance data security. When CareGroup saw the new auditing and security features of SQL Server 2008, as well as enhanced management features, the company decided to begin upgrading its databases even before the product was formally released, in order to better protect its information.
||With SQL Server 2008 we have transparent encryption, so we can easily enforce the encryption of the information in the database itself without making any changes on the application side.
Lead Technical Database Administrator, CareGroup HealthCare System
CareGroup is upgrading all of its SQL Server instances to SQL Server 2008. The first two to be upgraded were the Patient Billing database and the Lab Results database. “Both of these are important databases,” Shammout says. “The Patient Billing database is queried constantly and used for heavy reporting.”
In upgrading all of its SQL Server relational databases to SQL Server 2008, CareGroup will be taking advantage of a number of new features, including:
- Advanced Data Auditing. SQL Server 2008 provides comprehensive data auditing to help CareGroup monitor all events at the server and database levels, and scale-out auditing across the enterprise—extremely important in the heavily regulated healthcare field.
- Transparent Data Encryption. SQL Server 2008 includes transparent data encryption that enables CareGroup to encrypt data in an entire database without the need for application code changes.
- Policy-based Management. New for SQL Server 2008, Policy-based Management helps CareGroup set and enforce compliance with policies for system configuration—for its internal and external database developers.
- Performance Data Collection. SQL Server 2008 provides Performance Studio, an integrated framework that CareGroup is using to replace an internally developed solution to collect, analyze, troubleshoot, and store SQL Server diagnostics information.
- Resource Governor. SQL Server 2008 enables organizations to provide a consistent and predictable response to end users with the introduction of Resource Governor. CareGroup is using Resource Governor to define resource limits and priorities for different workloads, and to help ensure resources can’t be unduly impacted by poorly constructed queries or other unusual workloads.
- Reporting Services. CareGroup is deploying SQL Server 2008 Reporting Services accessed through a portal created using Microsoft Office SharePoint® Server 2007 to create a unified reporting solution.
Upgrading to SQL Server 2008 is giving CareGroup the enhanced auditing capability and transparent encryption it needed to better protect its data and to meet HIPAA and other regulatory requirements. CareGroup is also benefiting from easier system monitoring with Policy-based Management, performance data collection using Performance Studio, consistent performance using Resource Governor, and consolidated reporting.
Enhanced Privacy with Data Auditing
Protecting the privacy and security of patient information is a concern to healthcare organizations around the world. The need for protecting such information in the United States was underscored by the U.S. Congress in 1996 with passage of the Health Insurance Portability and Accountability Act (HIPAA), which includes the Privacy Rule that requires safeguards for the use and disclosure of a patient’s protected health information.
“Auditing is a very important part of HIPAA regulations,” says Shammout. “Prior to SQL Server 2008 we had to supplement our auditing capability by integrating third-party auditing applications. Previously we used the native auditing capabilities built into SQL Server 2005, but we found this capability didn’t meet all of our needs.”
Shammout is impressed with the auditing tools built into SQL Server 2008.
“The enhanced auditing of SQL Server 2008 enables us to track all changes to tables and other data elements in our system,” says Shammout. “We can track all inserts, deletions, and other actions to who and when, and store all of this in a SQL Server repository for reporting against. From an administrative point we have complete transparency. And if a patient wants to know who viewed their records and when, all of that information will be preserved and instantly available.”
||We’re using the SQL Server 2008 Policy-based Management right now to enforce our existing naming conventions and other best practices to bring about much needed standardization throughout our operations.
Lead Technical Database Administrator, CareGroup HealthCare System
With enhanced auditing built into SQL Server 2008, CareGroup is freed from the time and licensing investments of integrating a third-party solution. “It is good to not have to worry about system integration,” says Shammout. “And because we already own a SQL Server license, we don’t have to pay additional licensing costs.”
Tighter Security with Transparent Encryption
CareGroup is taking 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 transparent encryption built into SQL Server 2008 helps CareGroup comply with HIPAA data encryption requirements and address overall concerns for data privacy. SQL Server 2008 delivers an excellent solution to this growing need, by supporting third-party key management and hardware security module products.
“A lot of the information we handle is considered sensitive data, so we want to make sure it's encrypted,” says Shammout. “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. With SQL Server 2008 we have transparent encryption, so we can easily enforce the encryption of the information in the database itself without making any changes on the application side.”
Easier System Monitoring with Policy-based Management
CareGroup is using the Policy-based Management feature of SQL Server 2008 to enforce policy and schema across its database infrastructure, including on work performed for the group by vendors.
Using SQL Server 2008 Management Studio, CareGroup creates policies that manage hosted entities including instances of SQL Server, databases, and other SQL Server objects. Database administrators can use Policy-based Management to select one or more managed targets and explicitly check that the targets comply with a specific policy, and force the targets not in compliance to comply with the policy.
“We’re using the SQL Server 2008 Policy-based Management right now to enforce our existing naming conventions and other best practices to bring about much needed standardization throughout our operations,” says Shammout. “For instance we have a vendor who uses just two characters to name tables—AA, AB, AC and such—which makes it quite difficult for anyone else to know anything about the contents of the tables. By enforcing something as basic as using TBL as the prefix for a table name (TBL+Name) and VW, to begin a view name, and SP to begin a stored procedure name, we can instantly provide greater clarity for what is happening. With Policy-based Management we can enforce our own best practices.”
Shammout notes that Policy-based Management will also be used to ensure that prescribed schemas are followed. “With Policy-based Management we can ensure that objects are created using the proper schema, so they don't interfere with other schemas,” says Shammout. “This should result in cleaner structure and a more controlled design.”
Performance Data Collection
Shammout is extremely happy to be using SQL Server 2008 Performance Studio for performance data collection. It is clear that Shammout needed such a solution, as he spent the last few years creating his own custom solution to fill what he saw as a void.
“I spent the last four years building my own centralized solution for collecting and storing performance data from across our environment,” says Shammout. “My
application collected everything from top-level server information properties, to database information properties, to tables, users, and what level of access each user had. With hundreds of databases and thousands of users, I built a centralized solution to collect all these details and stored them in a central repository to report against.”
With SQL Server 2008, Shammout collects the same data and more using Performance Studio to gather the performance data he wants, making it easier to collect, analyze, troubleshoot, and store SQL Server diagnostics information.
“Data collection is now built into SQL Server 2008 so I don’t have to continually update my own application and write scripts from scratch to collect the information a database administrator needs to keep operations up and optimally running,” says Shammout. “We use the new SQL Server data collection on our other Windows®-based servers as well. Every morning we get an e-mail with total disk space on each server as well as the free space. We set alerts using our own thresholds such as a server running with less than 10 percent free space. This helps us to stay ahead of potential problems.”
Consistent Performance with Resource Governor
CareGroup is using the Resource Governor feature of SQL Server 2008 to protect its operations from poorly constructed queries and other sources of unusual workloads.
“We have thousands of users and you can’t expect all of them to be sophisticated database users,” says Don Wood, Manager of Information Resource Management, at CareGroup HealthCare System. “In the past some of our users, who lacked the experience to properly design a query, would sometimes join tables and run ad hoc queries that could maximize CPU usage so everybody else suffered degraded performance.”
With SQL Server 2008, users might still create poorly designed queries, but the results won’t be nearly so dramatic.
“The Resource Governor in SQL Server 2008 gives us the ability to limit poorly designed ad-hoc queries so that they can’t consume more than 20 percent or 30 percent of the CPU,” says Wood. “This means that others will be able to continue doing their jobs without being negatively impacted. We’ve wanted something like this for years.”
CareGroup is using SQL Server 2008 Reporting Services, accessed through a portal created using Microsoft Office SharePoint Server 2007 to consolidate a reporting environment that currently includes internally developed ASP pages and widespread user reliance on various versions of Microsoft Office Access™ data-tracking software.
||The Resource Governor in SQL Server 2008 gives us the ability to limit poorly designed ad-hoc queries so that they can’t consume more than 20 percent or 30 percent of the CPU.
Manager of Information Resource Management, CareGroup HealthCare System
“We have hundreds of Microsoft Office Access users and they like the product, but we want to standardize on Reporting Services,” says Shammout. “The Report Builder feature of Reporting Services simplifies report creation and will enable our users to create their own custom reports and ad hoc queries, while Reporting Services optimizes how they are run against the database.”
In summary, upgrading to SQL Server 2008 helped CareGroup Healthcare System gain the advanced data auditing and transparent data encryption it needs to meet HIPAA and other regulatory requirements. The organization is experiencing easier systems administration using SQL Server 2008 features including Policy-based Management, Performance Studio, and the Resource Governor. And reporting will be consolidated using Reporting Services.
Microsoft SQL Server 2008
SQL Server 2008 is a secure, reliable and scalable platform for business critical applications with transparent encryption, comprehensive auditing and enhanced mirroring, while reducing the time and cost of managing data with enterprise wide policies. SQL Server simplifies development of data driven applications with integration of Visual Studio and .NET Framework, support for any type of data, and consistent storage from the device to the data center. SQL Server delivers the right information to all users with advanced visualizations, powerful reports and integrated analysis.
For more information about SQL Server 2008, go to: www.microsoft.com/sqlserver
This case study is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.
Document published November 2007