Implementing High Availability and Workload Balancing Using SQL Server AlwaysOn Availability Groups

Industry: Software and Data Services

Project Type: Power BI and Analytics

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

Easy-To-Use Tools

Collaborate seamlessly across devices from wherever you may be - whether you're in office, on the go, or making last-minute changes before your next meeting.

From start-to-finish, the design and strategy team provide all of the guidance and expertise necessary to build a high-conversion website.

Without rigid templates, you can build the exact type of website necessary to hit your website performance goals.

Project Overview

This client was a software and data services organization servicing the automotive recycling industry. They revolutionized the automotive recycling industry 80 years ago, and since then, the company’s innovative yard management, e-commerce, data, and sales and inventory solutions have been powering the industry. With a legacy of innovation, a forward-thinking culture and industry-leading employees, this company creates solutions that help auto recyclers be more productive and profitable.

The client needed to upgrade its core data services platform, EDEN, to more accurately and effectively synchronize data with external entities. This included expanding their parts catalog and providing critical high-availability and load-balancing capabilities for eLink, their custom solution for real-time inventory integration with eBay.

At the time, the process for synchronizing inventory between EDEN and eLink was causing resource consumption spikes and performance issues. While a rewrite of the EDEN API was planned for future release, the performance issues in the data platform required a more immediate resolution.

The upgraded EDEN platform also needed a method for synchronizing data with the existing legacy model, while application dependencies were gradually removed after the initial implementation.

Strategy and Solution

SCS designed a new platform, EDEN 3.0, incorporating AlwaysOn Availability Groups as part of the solution. AlwaysOn provides Enterprise-grade high availability and load balancing by creating readable, real-time transaction synchronized secondaries. With EDEN 3.0, the readonly API calls could now be rerouted to the AlwaysOn secondary via the listener service, while the eLink batch process executed against the writeable primary. The read-only API calls would then return data from the snapshot isolation level secondary instance with no locking issues during processing. The only code changes required for this implementation were appending ApplicationIntent = READONLY to the API connection string.

SCS also designed a migration strategy for the existing environment that eliminated the need for bulk migration and EDI and instead leveraged distributed database views. The distributed views allowed for T-SQL statements to be simultaneously executed against the new and legacy platforms with minimal performance impact. Data inserts and transfers were then handled logically in the view definition, rather than using triggers to route changes to the appropriate legacy or EDEN 3.0 model. The query statements issued from the EDEN client, PowerLink, then required no refactoring as a result of this architecture design, with the source views being completely transparent to the calling applications.

The Results

The client now has a modern data platform that they can utilize to build out their next generation of SaaS products. The added value of an expanded product catalog, along with 24/7 high availability for customers throughout North America assures their leadership in the marketplace for years to come.

In addition, SCS worked across the entire organization to ensure that the data platform specifications met requirements from the Product team, as well as implementation standards from the Project Management, Enterprise IT Operations, Enterprise Architecture, and Development teams. SCS shared many best practices around SQL Server database administration and multi-tenancy models that were new to the company, but quickly adopted and championed by both the DBAs and Enterprise Architect.

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.

Get Started