[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]