Skip to main content

Case Study:

AlwaysOn HA Group Implementation

Dinosaur Bones

Industry: Science Museum

Project Type: Cloud Solutions

Tools Used: Microsoft’s newest offering in high-availability (HA) AlwaysOn Availability Groups for SQL

“This looks great! Thank you so much for implementing this for us, and writing so well about what needed to be done. I found this documentation very clearly written, concise and yet extremely thorough in what you needed to pass on to us bystanders for future reference.”

- IT Director

Project Overview

A large regional science museum needed to decommission its dedicated offsite disaster recovery (DR) site in order to leverage cloud-hosted DR services.

The client is a large, regional science museum in the Upper Midwest that is known throughout the world for its interactive exhibits, films and traveling exhibitions. The museum consistently explores and implements new technologies that help educate their visitors about the world of science.

The client decided to decommission its dedicated offsite disaster recovery (DR) site in order to leverage cloud-hosted DR services. The client did not have a robust solution in place for managing its enterprise data, or for designing database-level DR processes. The client was also encountering performance issues in its core production applications, leading to longer waits for visitors in line to buy tickets.

Strategy and Solution

SCS designed and developed state-of-the-industry database DR processes, including the implementation of cloud-hosted offsite database backups. SCS also designed and deployed Microsoft’s newest offering in high availability (HA), AlwaysOn Availability Groups for SQL. AlwaysOn is a great fit for the client. It allows for immediate, automated DR in the case of systems failure, while simultaneously improving core application performance by leveraging readable secondaries for read-only report requests, plus offloading of backup workloads.

AlwaysOn is a multidisciplinary technology that relies on coordinating Windows Server Failover Clustering (WSFC) expertise along with SQL Server database administration best practices. It takes an experienced consultant to implement this solution. SCS was chosen because of its track record of excellence and history of accomplishments with the client.

There were some custom features required in order to make the client’s core enterprise application, Tessitura, compatible with AlwaysOn. Tessitura is a legacy application that relies on SQL agent jobs to maintain application data integrity for multiple processes. SCS developed custom scripts, SQL agent alerting, and failover procedures to manage the enabling/disabling of read/write required jobs whenever AlwaysOn replica roles change from primary to secondary. SCS also created a custom process to synchronize database server logins across the replicas, since Tessitura relies on scripted collation changes. This meant that database containment was not an option in the near term.

SCS also designed and implemented a custom solution for managing database backup, index maintenance and integrity checks based on de facto industry standard scripts. The solution has cut backup AND index processing time by 50% and includes regular health check monitoring that was not performed before. In addition, the scripts can also backup and archive directly to Azure, Microsoft’s cloud service offering, for secure, redundant, cost-effective offsite storage.

The Results

The improvement in maintenance plans implemented in partnership with SCS has allowed the client to go from an hourly log backup strategy to log backups for every minute, keeping data loss and downtime to a minimum, especially on busy summer days when they can least afford an outage.

Detailed documentation was also provided to the client, which provided an overview of the Windows Server Failover Cluster (WSFC) architecture and the AlwaysOn Availability Group (AG) design and customization.

The SCS solution reduced backup AND index processing time by 50% while offering consistent health check monitoring. In addition, the client was able to go from an hourly log backup strategy to log backups for every minute.

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.