Skip to main content

Case Study:

A Highly Available and Highly Performing Transactional and Reporting Environment

Code on a computer screen

Industry: Medical Manufacturing

Company Size: 500+ Employees

Project Type: Power BI and Analytics

Tools Used: SQL Server High Availability Groups, SQL Server Replication, SQL Server Reporting Services, Windows Clustering

Project Overview

A Midwest-based medical device company, that serves tens of thousands of patients, needed a seamless data disaster recovery solution, ensuring zero downtime. The company partnered with Superior Consulting Services to create a Highly Available and Highly Performing Transactional and Reporting Environment. The medical device company had a number of SQL servers in a data warehouse in the Midwest. They needed to be able to flip to “active” in a backup warehouse without data or time loss.

Project Goals

  • Maintain 100% availability of data.
  • Disaster recovery with zero interruption.
  • Seamless maintenance without interruption.
  • A test reporting instance that automatically updates with production reports.
  • A separate analytics environment from the transactional environment.

Strategy and Solution

Strategy: Superior Consulting Services (SCS) provided a database administrator and infrastructure consultant to implement the project. The two resources worked alongside our client’s team to develop an analytical database environment for reporting and dashboards. SQL Server Replication was used to keep the data in sync between the two environments. SCS built a high availability solution, enabling the environments to failover automatically should there be a failure at the client’s primary location. The client needed a test reporting environment, with the same reports as production, but used a test database as the data source. SCS built a routine to migrate the reports from production to test on a nightly basis and change the data connections to the test database after the migration was complete.

Testing: The solution was tested in many different ways. The data in the analytical environment was compared to the transactional environment on a nightly basis to ensure all transactions had been replicated. The high availability solution failed over several times to ensure that the failover process worked successfully and the data was available after failover. Failover was planned to take place when the SQL Server is patched quarterly.

What work did SCS perform?

  • SCS installed SQL Server 2019 Enterprise Edition on four database instances and configured the servers utilizing best practices. The existing transactional environment was then migrated to one of the four database instances.

  • SCS installed and configured a Power BI server in the client’s reporting environment and migrated existing SQL reports to the new reporting environment.

  • SCS configured SQL Server Availability Groups in both the transactional and analytical environments to provide failover capability between the two pair of servers.

  • Replication was then built and configured to send transactions to the analytical environment from the transactional environment.

  • All solutions were clearly documented and provided to the client.

The Results

SCS successfully helped implement a seamless transition from a single central data center to a distributed data warehouse. The environment and its data are highly available and high performing for transactions, reporting, and disaster recovery. To conclude the project, SCS and its medical device client performed multiple test failovers with success. The client continually adds reports and dashboards to the Power BI server regularly and reports are optimized for maximum performance.

  • 100% data availability

  • Seamless database syncing without downtime

  • Recovery without interruptio

Improve Your Performance and Profitability Today

SCS uses best-in-class tools to deliver database, reporting and data platform and analytics solutions. We partner with your team to develop custom solutions.