SQL Server 2017: HA and DR on Linux
Articles,  Blog

SQL Server 2017: HA and DR on Linux


[MUSIC] Hi, I’m Mihaela Blendea, a program
manager on the SQL Server team. In this session, I’m going to cover
some of the high availability and disaster recovery solutions we
plan for SQL Server 2017 on Linux. As stated since our
first public preview we released back in November 2016,
our goal for SQL Server on Linux is to enable all
spectrum of high availability and disaster recovery solutions
available on Windows today. CTP1 release included backup,
restore, and shared disk failover clustering. And with CTP1.3, we released always-on availability
groups capabilities in all flavors. Log shipping is being
released as well. We are going to focus, in this
session, on always-on availability groups as a mission-critical HADR
solution for SQL Server on Linux. So let’s go through a series of
scenarios that availability groups are enabling in SQL Server 2017. To start with, let’s assume you are an organization
with an all-Linux infrastructure. You are building
a mission-critical application. And now, with SQL Server being
available on the new platform, you are considering using
SQL Server on Linux. To enable such scenarios, we are bringing always-on
availability groups on Linux. This feature is now available on all
Linux distributions SQL Server 2017 supports, that have
Enterprise Linux, Ubuntu and SUSE Linux Enterprise Server. Also, all capabilities that make
availability groups a flexible, integrated, and efficient HADR solution
are available on Linux as well. We have multi-database failover,
transparent failover using floating API resource, multiple
sync/async secondary replicas. Manual and automatic failover
with no data loss, and read-only secondary replicas. Automatic setting database
level health monitoring, and failover trigger, as well as
disaster recovery configurations, are also available. As you know on Windows, always-on
depends on Windows server failover clustering for distributed metadata
storage, failure detection, and failover orchestration. On Linux, we are enabling
availability groups to integrate natively with Linux clustering
technologies like Pacemaker. You can add a previously-configured
availability group as a resource to
a Pacemaker cluster. And old orchestration regarding
monitoring failure detection and failover is taken care of. We are going to see
later in the demo, how to configure this
high availability setup. Next, let’s focus on
another scenario. You’re a customer that has a SaaS
application, like a website, which uses a catalog database with
a high number of read transactions. Previously, you had to set up
a cluster to offload read workloads for the application to
deliver secondary replicas. Now, you can create a group
of replicated databases, and leverage the fastest replication
technology for SQL Server. And no cluster set up is required. Note, these new capabilities
are available for SQL Server running on both
Windows and Linux platforms. Also, this is not
an HA configuration, as there is no orchestration for
automatic monitoring and failover. If you do need HA, make sure
you integrate with the cluster technology, like Windows Server
failover clustering on Windows, or Pacemaker on Linux. Lastly, let’s consider
the example of an ISV with a solution on Windows. And now,
with SQL Server available on Linux, you want to certify
the solution on Linux as well. Or the example of an Enterprise, moving SQL Server workloads
to a Linux infrastructure. But due to business requirements
related to availability as of late, the migration must be live
with minimum downtime. Using SQL Server 2017, you can
migrate SQL Sever work loads, cross platform, by setting up
a distributed availability group, with primary on Windows,
and secondary on Linux. We do not recommend running in this
configuration in a steady state, as there is no cluster manager for
cross-platform orchestration. But it is the fastest solution for a cross-platform migration
with minimum downtime. Now, let’s see a demo on how to
create an availability group for high availability
configuration on Linux, and how to add it as a resource
to a Pacemaker cluster. So, let’s see how we complete
an availability group on Linux, and how we can add it to a Pacemaker
cluster as a resource. We’re gonna start configuring
the availability group using SSMS. So just go into
the Using Availability Group Wizard. Gonna go to SSMS, and create an external cluster
type availability group. This value, External, for
the cluster-type option, specifies that the user’s intention
is to use the availability group for an HA configuration. And the cluster manager
is an external entity. In this case it will be
a Pacemaker cluster. So let’s go through the setup. We just have to specify the name
of the availability group, add a database to it. And this is the primary replica
that I’m already connected to. I’m just gonna add
a secondary replica, and making synchronous commit. I already created an end
point because I’m using a certificate authentication. So I already have the end points,
and nothing else is required. And also, I’m gonna using automatic seeding,
now it’s available on Linux as well. And I think this was
required by the setup. And in just a few seconds,
I have everything configured. So, going to the Object Explorer,
I can see that I have the primary, now secondary, and the database is
added to the availability group. And on the secondary side, I should
have the database already synced. And synchronized, right? So now,
let’s add a resource in the cluster. How did you have a cluster set up? Let’s see, it stutters. So it’s a two node cluster. Both nodes are online, but
no resources are yet configured. SQL Server as source agent for Pacemaker is included in
the MS SQL Server HA package, which I already installed on both
the nodes, so just to double-check. Okay. I already have it installed. Now, all we have to do is to run the
pcsresource create command to create the resource for the availability
group that we just created. So using pcsresource create,
we just gave the name. Any name for
the resource itself, but we have to specify
the type of the resource. Of course, the availability
group name, we called it AG1. As well as a series of parameters,
that will tell the source how many copies,
how many replicas we had created. How many can be online at once,
on a certain node. And also, we’re gonna tell the cluster that this is
a master/slave type of resource. We need to map the ability group,
primary, secondary. Now, I have two replicas. Okay, and
using the same pca status command, I should see now,
the resource is created. And we can see that the master
is on agvm1 corresponding to the primary, and
the secondary is on agvm2. Now, we can add a second
resource to the cluster, which is the floating IP. This is the unique IP address that
we’ll use to connect to the primary. So, we’ll have to add
constraints to the resource, to collocate always
with the primary. And this is gonna be used for transparent re-connection after,
in case of failover. So to create a floating IPD source, we use the same pcs
resource create command. The same thing,
we’ll have to give it a name. Specify the type of the resource. And the only required parameter
is the actual IP that is going to be used. And as I was mentioning,
we need to add a constraint. To collocate,
The virtual IP resource, and the master of ag_cluster,
always on the same node. Looks like I have,
I misspelled something. I’m missing a C. Okay. And we can also add
another constraint, to make sure that upon failover, the
virtual IP resource is not starting before the primary on
the failover target node. So we can say,
we can add another constraint. And we can say that we have, the
cluster will have to first promote that master of the ag_cluster,
and then start the virtual IP. Okay. Just double-checking that
everything is healthy. And you can see the virtual
IP is added, and is definitely collocated on
the same node with the primary. Now lets try to perform
a manual failover. For that we’re going to use
a PCS at a source move command. And we going to say that the master
will have to move to the other node. And using the PCS status, we can see
that now, virtual IP first stopped. We’re seeing that the primary
on agvm1 was demoted, and we have two secondaries now. And soon we should see
that the primary move to agvm2, and of course virtual IP
started on the same node as well. Hope you found that useful. We are looking forward to your
feedback on these capabilities. Thank you for watching. [MUSIC]

Leave a Reply

Your email address will not be published. Required fields are marked *