Microsoft SQL DWH using DellEMC iCDM

Recently I got engaged with one of our customer who was trying to implement data warehouse using the Microsoft SQL 2017. But they were not able to implement the same into the production environment even after successful POC and testing. When we got engaged with customer DBA teams we used DellEMC’s iCDM functionality (with XtremIO X2 system) in some unique way to help them not just solve their problem but exceeding business SLA expectations. This blog covers the details around problem and solution along with learning.

Problem Statement

Customer has several mission-critical MS SQL 2016 databases which are running in 24×7 operation. These databases are supporting their business critical OLTP application.

Customer’s planned DWH approach

Business teams were pushing application and DBA teams to create singular reporting platform which will create reporting on data from multiple MSSQL source databases.

For this project DBA teams built MS SQL 2017 platform which will consolidate data from 3 source MS SQL databases. They created the test setup for feature testing before production roll-out, which worked as per their expectations. But when they implemented this feature in production DBA team realized that this is generating massive load on their source databases and causing performance issues for their business applications, which was completely unacceptable to business teams.

This forced DBA teams to stop fetching data from the network and restore the data from last backup instead. This was used as workaround because this meant that data in reporting server was more than 30 hours old, which was making reporting irrelevant.

DellEMC Solution

When DellEMC team got involved we started with collecting DB environment data collection and business expectation understanding. Once we understood the pain points and business requirement we mentioned to DBAs that traditional way of data management techniques won’t help them. We also mentioned that since they are exiting DellEMC customer they already have the solution with them, which they never implemented.

Every DellEMC All Flash storage solutions includes bundled copy data management software called Appsync.

On high level AppSync allows application and database administrators to create application workflows. These workflows allows them to create on demand or scheduled protection or re-purpose copies of databases. We also mentioned that using this software you can create your copies of source MS SQL 2016 DB copies and mount it to target MS SQL 2017 DB server, scheduled or on-demand. These workflows are based on DellEMC storage in back-end and hence can be executed during even the busiest times.

As usual customer DBA teams didn’t believe us and that’s where it comes to next section of this blog – Demonstration! 🙂

Demonstration of the Appsync

To demonstrate the functionality of DellEMC iCDM we installed the Appsync server on of the Windows server VM in customer environment. Below is the high level architecture

DellEMC’s proposed solution approach for DWH project

Once Appsync was installed we configured the same to communicate with source and target SQL servers and also DellEMC XtremIO storage. This allows Appsync to discover running databases and create the end to end database mapping.

Post initial configuration we created the SQL re-purpose copy schedule to create the MS SQL database copy every 6 hours. This schedule was then applied to all the 3 source databases.

Once the copy of multiple source database was created we used Appsync to mount the same into target MSSQL 2017 server. In our testing we mounted 3 source copies on single target MSSQL 2017 instance. Entire mounting operation completed within couple of minutes. This process allowed customer to save multiple days and help achieve business SLA.

Apart from solving their most critical problem in hand this solution helped them solve couple of major production issues

  • Database RPO – We reduced production DB RPO to 2 hours instead of 30+ hours
  • Agent-free backup – iCDM helped customer to mount DB production copy on off-host backup server. This helped them completely eliminate backup load from production server.

If you’re facing similar challenges in your DB environment then highly recommended to use iCDM approach instead of traditional methods.

I have created another blog post to elaborate more on iCDM and advantages of the same. You can go through the same using below link.

Databases and Integrated Copy Data Management (iCDM) with DellEMC

Below are few reference documents for further reading on iCDM

Dell EMC AppSync Datasheet

Best Practices for running SQL Server on DellEMC XtremIO X2

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s