DP-300 Microsoft Azure Database – Configure Azure SQL Database resource for scale and performance Part 5

9. 14. set up SQL Data Sync

In this video we’re going to see how we can set up SQL data Sync. Sync. Short for Synchronization. So let’s suppose that we’ve got these three databases. So it could be that we have a couple of databases which are Azure SQL database and a number database which is an onprem database, or it could be in a virtual machine. Now, what Azure SQL Data Sync allows you to do is synchronize data across multiple databases. Now in order to do that, your tables need to have a primary key and you can’t change this primary key.

So if for instance, you have a row with a primary key of 200 and you want to change it to 300, then you would have to delete the Raw and add in the Raw and say this is a primary key of 300. It’s not a huge restriction. Additionally, it doesn’t work with Azure SQL managed instances. So you’ve got to have Azure SQL Database or the VM or on Prem version. So what we need to do is define one of these Azure SQL databases as the Hub database. So let’s choose this one. So that’s the Hub database. So the others therefore will be member databases.

So these are either Azure SQL databases or On Prem or VM, so not managed instances. And the idea is that if anything changes in any one of these databases, it gets replicated. So if it changes in the Hub, then it goes out to the member databases. If it changes in a member database and it goes back to the Hub, which then goes out to all of the other member databases. So we’ve got bidirectional changes here, so we can have changes in anyone. Of course, there should be some sort of conflict resolution. If I change something in both a Hub and a member database, then we need to say, well, the Hub wins or the member wins. Now how can we set it up? Well, we need a database which is going to keep track of everything, and we call this a sync metadata database.

So this is going to be an Azure SQL database which is going to be in the same region as the Hub database. Now ideally, and Microsoft recommends it should be an empty database. So in other words, it’s one that you can create at the time of setting up the SQL data sync. There will be tables created and there will be a frequent workload run. So we’re nearly there in terms of visualizing what it is. Just one additional thing for any on Prem VMs, then we need in addition to the Sync Hub, which is automatically set up here, we need a program called a Sync Agent. So that’s the way of basically doing this arrow. So getting information in and getting information out. And here you can see that you can download the data sync Agent for your on premises databases here.

So I’ll just download it very small download ten megabytes. So let’s run this agree to the terms need to be able to put in a username and password that can reach SQL Data Sync Service. So it needs to have network access. So I’ll do that now and click Next and then we can install it and in just a few seconds it installs. Now if you get error messages saying that you don’t have sufficient permissions to be able to install it, then make sure your username and your password are correct. So your username, I’ve used my computer name and then a slash, a backslash and then my account.

So I’m on Windows Ten, I have a Microsoft account. So this is my email address, the password, that is the password for the Microsoft account and not your Pin that you would enter. So you may enter a four digit number to open up your computer. It’s not that. So it is your Microsoft account. So for that you can check whether you’ve got the right password by going to your account Microsoft. com and signing in there, where you’d sign in with your email address in this case and your password, which is not 3579, but is something a bit more complicated.

You can find your email address if you’re using Windows Ten by going to the Task Manager and clicking on Users at the top. You can find your computer name by going to your File Explorer and going to my Computer or Computer or this PC, right, and clicking on going to Properties. So I see my device name just here. So that is how you can install Microsoft SQL Data sync Agent. But that only needs to be used if you have an onprem or a SQL Server on a virtual machine. It doesn’t need to be set up for Azure SQL database.

So now let’s go into Azure itself and go into the portal and we’ll go into our SQL database. So not the server, but the database. And if we go near the bottom of this first page, we can see data management sync to other databases. So I’ll click that. So we don’t have any sync groups and we don’t have any sync agents. So I’m going to go to a new sync group. So create a sync group on the Hub database. So this database here, that is your Hub database. It needs to be in the same region as the sync metadata database. So we’re going to give it a sync name.

So this is a sync group name. We can use an existing database. So there is my server, or I can create a new database. And if I clicked a new database, then we’ll get these configurations including the configure database. So I’ll just leave it at that. So I’ll just put in my metadata database name, select my server, select my pricing tier. Fine, just leave it as that. Click okay.

Now automatic sync. So in other words, does it actually do an automatic sync or manual sync. So you can see that there’s a frequency that you can also get it to synchronize. I’m just going to leave that off and then the conflict resolution. So what happens if a Hub database changes a record, a row and a member database changes this as well? So who wins? I’m going to say the Hub wins.

You can say if there’s a private connection as well. So you can see it is now submitting that it has put it into a JSON, into an Arm template and is now deploying it. So I’m just going to pause this video until it’s deployed. So after a few minutes you can see that the deployment succeeded and that the sync group name was created. So I need to refresh this. And here we can see this sync group name, the database sync group, which has got one database at the moment, which is my DP 300. So here I can add an Azure database or I can add an on premises database.

So if I was to add an on premises database, then I next have to choose a sync gateway. So I’ll just create a new agent. So you can see I need to download the client sync agent. I need the agent name. So this is something get that to make up. So this is my sync agent name. And I will create and generate a key. You can see it’s processing. And here you can see an agent key has been generated. Use this key in the installed client sync agent to register this agent.

So I’m going to copy it, copy the clipboard. Then I open up on my on prem, the Microsoft SQL data sync. So I’m going to submit the agent key name and that opens up this dialog box. And there is my agent key and then the login and password used by the local agent to connect to the sync metadata database. So this is your existing username and password for the member database. And then you can test the connection, make sure you got the firewall all set up and everything else. Now if you want to add additional databases as your databases, this is why you do it.

So add an Azure database and it needs to be existing. So you may notice that there are no options to create a new database. You’ve also got sync directions. So does it sync from the Hub to the Hub? In other words from the member? Or both ways bi directional. And then you need to put in your existing username and password for the member database and then say whether it uses a private link or not. And then when that’s done, you can go to the tables.

So I’ll go to a particular database and you can select the tables to sync. And tables without primary key are not supported. So that might be why I can’t see anything in here. So you can see there’s a bit of work that needs to be done to be able to set this up. But once you set it up, then you will have databases which have been synchronized.

So to do this, we go to your Hub database, we go to sync to other databases, we add a new sync group, we add any sync clients that might be necessary for your onprem, and then we can add any tables that you want to synchronize. And then by the end, you will have a Hub database synchronizing with these member databases, which can be Azure, SQL Database, on Onprem, or VM member database.

• Category: Uncategorized