Setting up high availability for Microsoft SQL Server Integration Services in Scale Out deployment on Amazon EC2

In this blog post, we’ll dive into two options to set up high availability for Microsoft SQL Server Integration Services (SSIS) in a Scale Out deployment model to help you figure out the best way to achieve SSIS high availability for mission-critical workloads.

Introduction

When you are deploying mission-critical workloads using Microsoft SQL Server databases, it’s important to make sure that all the components are highly available—including SSIS Scale Out deployment.

Many customers choose to run SQL Server databases on Amazon Elastic Compute Cloud (Amazon EC2) because it’s scalable, flexible, and provides high performance. To provide SQL Server high availability, customers either use SQL Server Always On availability groups (AG) or SQL Server Always On Failover Cluster Instances (FCI) with Amazon FSx for Windows File Server or Amazon FSx for NetApp ONTAP shared storage across multiple Availability Zones (AZ).

To maintain high availability for mission-critical workloads, it’s crucial to ensure that all components, not just SQL Server, remain highly available. SSIS is often a crucial part of data extraction and transformation solutions. Now, the good news is that you can also use high availability solutions for SSIS in Scale Out deployment when you’re deploying on Amazon EC2. There are two options for setting up high availability in this scenario:

You can set up an SSIS Scale Out Master node with the SQL Server FCI using Amazon FSx.
Alternatively, you can set up an SSIS Scale Out Master node with the Always On availability group.

What is Scale Out SSIS deployment?

Microsoft introduced the SSIS Scale Out feature in SQL Server 2017, which allows scaling of SSIS horizontally. This means that this feature provides better performance and concurrent execution of SSIS packages across multiple Scale Out Worker nodes. To ensure high availability and disaster recovery for an SSIS Scale Out deployment, it is important to deploy the Scale Out Master service and SSISDB database with high availability. This provides redundancy and failover capabilities for the Scale Out Master node.

Prerequisites

An Amazon Virtual Private Cloud (Amazon VPC) with two Availability Zones (AZ) in the same AWS Region
Security groups configured to secure the traffic between the instances deployed in the Amazon VPC
Active Directory
Microsoft Windows Failover cluster
Microsoft SQL Server 2017 or later deployed in the Always On availability group or SQL Server FCI with Amazon FSx for NetApp ONTAP or Amazon FSx for Windows File Server with multi-AZ setup

You can use AWS Launch Wizard to set up the SQL Server Always On FCI cluster or the SQL Server Always On availability group. AWS Launch Wizard is a tool that offers a guided way of sizing, configuring, and deploying SQL Server high availability solutions without requiring manual provisioning of individual AWS resources.

Solution Overview

Option 1: High availability for SSIS using SQL Server FCI and Amazon FSx

Let’s look at the first option on how to set up SSIS high availability using the SQL Server FCI. SQL Server FCI is a part of the SQL Server Always On offering that uses the Windows Server Failover Clustering (WSFC) functionality to provide high availability at the SQL Server instance level.

To set up the SQL Server FCI on Amazon EC2 using Amazon FSx for Windows as shared storage, you’ll need to install it across Amazon EC2 WSFC nodes in one or multiple AZs. You can refer to this blog post for more details about the installation steps. If you want to deploy using Amazon FSx for NetApp ONTAP, you can see more details about installation steps in this blog post.

By setting up the SSIS catalog database (SSISDB) and SSIS Master Scale Out service as part of the FCI setup, you can ensure high availability for SSIS. This means that if one node fails, another one will take over automatically, allowing you to keep your SSIS service running without interruption. Figure 1 shows this architectural pattern using Amazon FSx for Windows.

Figure 1. SSIS scale out master HA with SQL Server always on failover cluster

Steps for configuring SSIS Scale Out deployment in FCI

Figure 2 shows SSISDB catalog DB created on the FSx for Windows file System in the SQL Server Always on FCI.

Figure 1. SSIS scale out master HA with SQL Server always on failover cluster

Add SSIS to Always on Failover Cluster

Connect to Failover Cluster Manager and bring the SSIS Scale Out Master service online using the Failover Cluster Manager.

Figure 3. Bring the service online in Windows Failover cluster manger tool

With these configuration steps, you can achieve high availability of SSIS using the SQL Server FCI. Your SSIS packages will run from the primary node of the cluster, and in the event of a failure, the Scale Out master service will fail over to the secondary node. Your SSIS packages will continue to run from the active cluster node.

Option 2: High availability for SSIS using Always On availability group

Now let’s look at the second option on how to set up SSIS high availability using the SQL Server Always On availability group. The SQL Server Always On Availability group consists of a primary replica and one or more secondary replicas that are maintained through the SQL Server log-based data movement. You can see more details about the SQL Server Always On availability group installation steps in this blog post.

As with the SQL Server FCI, you can configure the SSIS catalog database (SSISDB) and SSIS Master Scale Out service in an Always On availability group configuration to ensure high availability for SSIS. Figure 4 provides a high-level architecture showing SQL Server Always On cluster nodes replicating synchronously between Availability Zones.

Figure 4. High-level architecture showing SQL Server Always on cluster nodes replicating synchronously between Availability Zones.

Steps for configuring SSIS Scale Out deployment in Always On availability group

With the SQL Server FCI, the SQL Server Agent jobs are made available to the secondary server after failover as the whole instance fails over. With Always On availability group, you have to copy the SQL Server Agent jobs from the primary SQL Server node to the secondary node. Please refer to this blog post for details.

Alternatively, if you are using SQL Server 2022, you can use a contained availability group. A contained availability group allows management of objects like the SQL Server user, logins, SQL Agent jobs, and more. This will remove the need to manually replicate these objects across nodes in the availability group.

With these configuration steps, you can achieve high availability of SSIS in Scale Out deployment using the SQL Server Always On availability group. Your SSIS packages will run from the primary node of the cluster, and, in the event of a failure, the Scale Out master will be available on the secondary node to continue serving requests.

Conclusion

In this blog post, we showed you how to set up high availability for SSIS for the Scale Out deployment model using the Always On availability group and the SQL Server FCI deployments using Amazon FSx for Windows File Server. In both methods, we used SSIS Scale Out Master node setup on the primary SQL Server node and configured the secondary node for high availability. We showed you the steps to set up high availability for SSIS packages and achieve consistency without data loss. You can use either of the above methods to set up high availability for your SQL Server Integration Services.

Check the best practices document to learn more about the most effective ways for working with SQL Server on Amazon EC2

AWS can help you assess how your company can get the most out of cloud. Join the millions of AWS customers that trust us to migrate and modernize their most important applications in the cloud. To learn more on modernizing Windows Server or SQL Server, visit Windows on AWS. Contact us to start your migration journey today.

Microsoft Workloads on AWS