DP-300 Microsoft Azure Database – Recommend and test HA/DR strategies, and configure HA/DR

1. 94. configure replication – Transactional replication

Hello. And in this section we are going to return to HADR high availability disaster recovery. But we’re moving away from just the ones that are done in Azure and looking at HADR in the sense of hybrid deployments as well as Azure. I’m going to start off with replication and how to configure transactional replication. So you can push changes made in an Azure Mi or in a virtual machine to a SQL Server database on premises, or on an Azure virtual machine, to an Azure SQL database or an instance database in Azure SQL managed instance.

So this is useful for distributing changes to one or more databases, keeping several distributed databases synchronized. And it’s also a good way of migrating databases from on premises, for example, to another database by continuously publishing the changes. So you just set it up and allow the computer to do what it needs to do. So we have got this one computer here, this one database, and I want to publish it to multiple databases. So the components of a transactional replication are the publisher.

So we publish changes made on some tables, which in replication speaker called articles, and we send the updates to someone else, the distributor. This can be an Azure SQL Mi or an SQL Server instance, but it can’t be an Azure SQL database. If you want to publish changes from an Azure SQL database, you’d use something like Data Sync for that. So the publisher then goes to the distributor. This collects changes from publishers and distributes them to subscribers. Again, this can be an Azure SQL Mi or an SQL Server instance, perhaps on a virtual machine.

Now, it can be the same as your SQL Mi as the publisher, but on a different database. If it’s an SQL Server instance, then it needs to be the same or higher than the publisher version. And then the distributor distributes all of the changes to the subscribers. And there are two different types of subscribers. First of all, there is the Pull subscriber. So this can be an Azure SQL Mi or an SQL Server instance, but it needs to be of the same type as the distributor. So Pull is getting the changes, it’s making the content itself push subscriber that distributor pushes on to the subscribers. So it can be an Azure SQL database.

However, it only supports two particular mods, the transactional and snapshot mods that we’ll be looking at later. It could be the mi. This supports the standard transactional snapshot and bi directional. And it could be an Azure SQL Server instance on a virtual machine. So this needs to be more recent than the publisher, but it can be slightly early. But it must be more than two versions earlier. So we got the publisher leading to a distributor leaving two subscribers. So let’s look in a virtual machine how we can do this. So I go to the replication node, right, and click on Local publications and I select new publications. You’ll also notice there’s one for new subscriptions. So the first thing we need to do is say okay, this is the publisher, what is the distributor?

So you can use a different server as the distributor which must already be configured. Or you could say this computer will be its own distributor.

So after this we have to say well, are you going to have SQL Server Agent starting automatically? If not on a VM then this is not going to actually work until it is done. So you can see the big problem down here and you need to say no, I will start it manually, I will start it automatically. Now the next part is specifying a snapshot folder. So it needs to be a network path or a drive letter map to a network path. If you want to support pull subscriptions.

Otherwise as I’ve got it here, this will only support push subscriptions. Then we select a database and select the publication types. So we’ve got several different ones. We’ve got snapshot. Replication. So this distributes data at a specific moment in time and doesn’t monitor for any updates to the data. We’ve got transactional publication. This allows for changes to occur in near real time applied to the subscriber in the same order as they occurred to the publisher. So with both of these publication types you can have the subscribers being SQL Database, Mi or VM PeerToPeer Publications.

This allows for changes in near real time on multiple server instances. This is for Onprem and VMs and then we have merge publication data can be changed on both the publisher and the subscriber. So when connected to the network all roles which have changed between the publisher and the subscriber are synchronized. This is for on Prem, VM and Mi.

So I’m going to choose transactional publication for this example. Then we’ll go to Next and we select which articles, which objects we want to publish. So I’m going to publish this DBO my table. So click on next. We can filter it. If I click Add then we create an SQL statement which identifies which table rows subscribers will receive. So then we need to say how snapshots are going to be initialized.

So do you create a snapshot immediately or do we get it to run at various times or both? And you can say I want it to run at weekly, monthly and we’ve gone through these schedules before and you notice that it is just creating an escrow server agent job while doing all of this. Then we need to publish a snapshot agent and if necessary a log reader agent.

So I’m just going to have a look at any SQL Server Agent account. Not the best practice but I’m going to be doing that because it’s just a test environment. Then at the end of the wizard we can create the publication and or generate a script file for doing it and then specify a publication name. So this is going to be DBO my table and you can see the summary of what we have asked it to do here. So click Finish and it works. So we have success. And if you wanted to create a push subscription on another computer, we would go to local subscriptions, click on new subscription and we would find the publication.

We would find where the replication agents are running at the distributor for push subscriptions or at the subscriber for pull subscriptions. We would say, okay, where are our subscribers? So I’m going to put it into a different database. In this case database one, we would have the distribution agent security. So we need to set up login and passwords for connections by the replication agent.

So I’ll click on the dot over here and do exactly what I’ve done before. And then we can have a synchronization schedule. So run continuously or run on demand or you can define a schedule and then we can finish. We can select additional options for merge and transactional publications. So let’s click Finish and that this subscription is being created. So this is how we can configure replication. So we need a publisher which leads to a distributor which then can push or pull data onto subscribers. And this is how to configure replication.

2. 85, 86, 87. evaluate HADR for hybrid and Azure-specific deployments

In this video we’re going to evaluate H ADR so high Availability and Disaster Recovery. So one possibility is that you could have Database Mirroring. So you could have, for instance, a database, a principle it would be called. It could be on an onprem network, it could be elsewhere and it can be mirrored in the cloud. Now, you don’t actually have to have a database if you don’t want to. You could back up and restore using Azure Blog storage. You could have Azure Site recovery.

That is a service which can replicate workloads running on physical and virtual machines from a primary site to a secondary location. So if your primary goes down, then it could fail over to a secondary location. You can replicate and fail over SQL Server to Azure using Azure storage. So, Data Mirroring, you would need one partner running in an Azure Virtual Machine and the other one on premises. For cross site disaster recovery, you would use server certificates to do the authentication. There’d be no Virtual private network connection required.

However, if you wanted to do this with the same Active Directory, so we’re talking about Windows Server Active Directory Domain, then you would need a Virtual Private network connection because they’ll need to be in the same Active Directory domain. Now SQL Server database mirroring is not supported for SQL Server 2008 or SQL Server 2008 r two on an Azure VM. And ideally you’ll be looking at a VM running at least SQL Server 2012. Now, another possibility is Log Shipping. So with Log Shipping you would have one server running on an Azure Virtual Machine and another running on site for cross site disaster recovery. So Log Shipping, it means that you have the logs and they get shipped from your primary to your secondary.

So it’s just like having a lot of transaction log backups, except they are restored automatically. Now it depends on Windows file sharing. So a VPN. A virtual private network connection is required. And you probably should also install a Replica domain controller at your disaster recovery site. That way if something were to go down, you’d instantly be able to go back up with a Replica domain. So the secondary database would be stored on a secondary server. It could be active, or it could be in warm standby mode, so it could become active fairly swiftly.

Now, the two ha DRS that are mentioned prominently in the DP 300 requirements are availability groups and Windows server failover cluster. Now, the availability groups require a failover cluster, so we could have a primary Replica going to a secondary Replica. And we’ll also see that there’s some sort of thing called a file share witness which checks on all of the Replicas and sees if they’re still up. And each of these will then have a vote as to whether each of them is up. And if majority say a particular replica is not up, well, it’s going to be deemed down and so maybe Failover might be needed, maybe notification that a particular secondary replica is down might be needed and so forth.

You can have up to two to nine SQL Server instances, so they can be on premises data center, but with a domain controller you can also have them going up to a secondary replica. So you can have up to two to nine SQL Server instances on VMs for instance, or VMs and on premises data is on the primary sent out to the secondary. Now, you can use something called Synchronous Commit for the secondary replica in an on premises network. So what is that? It means that transactions which go in on the primary are not committed on the primary until they’re committed on the secondary.

So it’s only when they’re committed saved on the secondary they can be committed saved on the primary. Other asynchronous is also available, especially if you’ve got geographically spread secondaries or for the lowest latency. So you need a domain controller as it requires an active directory domain. So this does allow for disaster recovery. If something happens to the primary replica, one of the secondary replicas can take over. If you are using a hybrid, in other words, on premises and in the cloud, then you will need a VPN connection for the entire failover cluster using a multisubnet failover cluster.

So for disaster recovery purposes, you would also need a replica domain controller at the disaster recovery site as well. Now, availability groups are also available if you just have it on Azure. So in the forthcoming videos we will be setting up a Windows Server failover cluster and a domain controller on Azure and being able to do it all entirely within Azure. But you can also do it in a hybrid situation as well. We will be setting up a failover cluster instance with a failover group. So this allows for high availability. It’s designed to protect against network card or disk failure.

Now, there are five different configurations as your shared disks for Windows Server 2019. So you can attach a managed disk to multiple virtual machines at the same time. Storage space is direct. You might also see that called S two S for storage area network for Windows Server 2016 or later, and Premium file share for Windows Server 2012 or later. It needs SSD, a solid state drive for the Lore latency. There’s also third party solutions, there’s also Azure Express route. There’s plenty of other things that we can do for Ha Dr, but these are some of the main ones.

So in the forthcoming videos, what we’re going to do is create an always on availability group. We’re going to start by creating virtual machines and then connecting them all together to a domain. So we’re going to create two virtual machines which are going to be SQL Server and then a third virtual machine which is going to be the domain controller. And then we’re going to connect all of the machines together through this domain controller. We’re then going to create a failover cluster. We’re going to check SQL Server installation, and we’re going to prepare for the always on availability group.

We’re going to configure quorum options for the WSFC. That’s the Windows Server failover cluster, and then we’re going to create an always on availability group. So I don’t necessarily recommend that you do this in practice, because you’ll just be creating virtual machines that you’ve done before. You’re going to connect them to a domain as well. And the DP 300 exam is not really about connecting VMs to a domain.

So you can just watch me do it, and then you can see me create a failover cluster, prepare for always on, create quorum options, and then create an always on availability group together with a listener. So in the next video, I’m going to create some virtual machines.

And the major thing about these virtual machines is I’m going to be creating them in the same network so they have a chance of communicating with each other, so they have similar TCP IP addresses, Internet Protocol addresses. Let’s create these virtual machines and connecting them to a domain in the next two videos. And if you want to skip the two videos and go straight into how we can create a failover cluster, that’s absolutely fine. I’ll see you in whichever video you choose to join me in Next.