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
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.
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.
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
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.