Simplifying migrations to Azure Database for PostgreSQL

Simplifying migrations to Azure Database for PostgreSQL


(lively music) Hello and welcome. Please enjoy this short readiness video on Azure Database for PostgreSQL. With us now, we have Gary Hope. Hi, Gary, how you doing today? Hi Sanjay, good to be here. Of course great to talk to you every time I talk to you. So it’s all good. So can you please tell us, what are you gonna talk about today? So today I’m gonna talk about migrating databases to Azure database for Postgres. And before we get started, what’s your role at Vegas, what do you do? So I’m the technical product manager for Azure database for Postgres. Yeah. Alright, that’s exciting. So let’s get started. So, when we think about Azure Database for Postgres, we are primarily moving workloads that are living in customers data centers. That’s the primary place for the work that’s come from. And as Postgres has become increasingly popular for enterprise workloads, enabling those migrations to be super smooth and easy for our customers has been very important for us. Of course. So let’s get started. Why would they I mean, of course, Omar talked about all the fun things, but what are the benefits? So I think Omar talked about a lot of those things, but we’ll quickly recap, customers are really wanting to take advantage of the platform as a service capability that we’ve got the embedded intelligence we’re building into that platform. But continuing to leverage that database that they love this Postgres database that they love, leveraging the extensions that they use, based on that open source community that supports them. I see, okay. And then obviously, we’ve got the extensions that enable us to do upper scale situs that was always back quite a bit. So you know, migrations are they sound interesting, but then when they actually started do migration, they were pretty hard to do. So we’ve tried to make that as simple as possible. What we’ve got is an Azure database migration guide. And this is not just about the scenario we’re talking about here today. But all migration scenarios, whether you want them to migrate your sequel databases up into Azure, or your Postgres databases that we’re talking about today, or MySQL, or whatever that is. So common tools for all migrations like that. So a common set of tools, but also detailed instructions on how to do it. So it’s a step by step guide. If you go to the Azure database migration guide, you will see a step by step guide in terms of how to do those migrations. And we’re gonna work through one of those scenarios today for Postgres. You’re gonna show live demo? We’ll show live demo, we’ll get that to you. So let’s talk a little bit about what that migration actually looks like. So you have a database that’s running in your data center. And we mustn’t forget these databases are not just databases, they have applications attached to them. Yes, of course. So you’re gonna wanna migrate your application, and then you’re gonna wanna migrate your database up to Azure to take advantage of this scenario. It’s crazy. We have a new Azure migrate portal experience, where we can enable all of those. So that data migration scenario that I spoke about earlier, it’s just a piece of your migration. You wanna maybe be able to do all of that, including your applications as well. As we enable all of that through the Azure migrate portal. But let’s dive into the details of the actual Postgres mode. There are two options, right? Yep, we have two options, we have an option that is gonna enable you to pick up your database once, do a dump of that database into standardized dump file format, which in essence, is just a script of your database. And you can pick that up and run it in Azure database for Postgres. So that then creates a copy of your database. But while you’re doing that dump, While you’re doing that load process and obviously If we’re doing from On-premises to the cloud, while you’re transferring that data, your database’s down until you get the new one up and running. So that’s the one option. It is useful for some scenarios, it’s useful for testing in a lot of cases. But also for super small database, that can be a very quick and easy way to do this. But we also have Azure database migration services, which allows us to do an online migration. It also enables offline migrations but today, we’ll be talking about that online migration, where you want your database to be running all the time, while you’re doing that migration in the back end and then having this short amount of downtime to be able to cut over from one database to the other. Oh, this is like an example of a retail store, customers are always there online or in person stores. Exactly. If you gonna hold on for two days, we taking it down while we migrate. It’s not gonna be a great experience. So the demo I’m gonna show you today is really the step by step process. If we have an application that’s connected to an On-premises database, what we’re going to want to do is migrate the data as part of an initial load. Now there’s a first step to that process of that initial migration. And that is that we need to set this environment up with the prerequisites to be able to take that data in. And the way that we do that is with a schema migration. In the same way, we would do a dump and a load. For the schema migration, we just do a dump of the schema and a load of that schema. And when we’re migrating from Postgres to Postgres, that process is super simple. You just dump the schema, we get a copy of that schema, we reload it into the environment. And I’ll show you some scripts on how to do that as part of the demos. So the schema or the data as well? Just the schema. So your data staying in your On-premises database, we’ve now created a copy of the schema. So basically, this database that’s in Azure, now looks the same without the data. And then we use an initial load as part of the data migration services. So we establish pipeline, we load that data. And then after that we enable new transactions to continuously flow into the database. So as your retail example that you picked up now– New customers in the store now. Yeah, new customers in the store. Those transactions continue on the old database, but the data that they’re creating is written into the new database that you’re gonna switch over to. At some point, we need to do this cut over. So we do need to stop data coming into the database in the store in your On-premises deployment. When you stop that data, we need to redirect the client to the new server once the last transaction has caught up. So this process is riding the data in the background, needs to catch up and then we will cut over the actual connection to the the new database. That’s the process end to end of migrating application and its connections to that new database. Without losing that those few transactions that came into the loop that– You lose no transactions The only thing is you have the short amount of downtime, while you reconfigure your connection strings for the application to pointed at the new database. Awesome. Let’s see it. So let’s jump into this demo, and let me show you what we’ve got going here. I have a VM set up with here you go, a local Postgres instance, on the platform. And what tool are we showing, Azure Data Studio? So this is Azure Data Studio, it is got the Postgres extension installed in it. So this is Native Postgres Support in Azure Data Studio. That’s awesome. So here I have a local host running on this machine. It has a sample database. In here we have some tables. There’s a scheduling app, that’s basically a bunch of tasks that are running on here. If I have a look at the tasks that are in here, you can see there’s a whole bunch of tasks in here, If I go and have a look at how many, there’s 161 of them. And we wanna migrate that while we continue to operate on this database to the new database. So one of the things we’re gonna do is we’re gonna simulate data arriving. And I’m gonna run this data load application at a simple power shell script. And I’ll give you a little bit of a view of what’s in there. Is every thing new customized by instant new products? Correct. We do a clean up just to get rid of all the excess records that were in there for this test. So there’s a bunch of deletes that happen, and then we inserting row by row. So now if we go back and have a look you’re gonna see that We deleted the whole lot up front, but now as as the new ones come in, we’re gonna see more dual rows arrive in. Right? So what we’re gonna want to do now is migrate this application across to our new Postgres as Postgres database on Azure. So here, I have the same setup, if I go back to this database here, the same setup. But you can see there are no tables in this because I haven’t done that initial schema migrations. So let’s do that initial schema migration again, of a simple script to do that migration and I’ll run this and basically, it’ll export the schema and load the schema into this Azure database. Now, if you have a look, we have a table, but we have no data.
It’s empty. Because as I said, we migrated the schema, the only thing that we moved over was that schema for that database. So basically, on the left hand side, the server’s local host is On-prem and this one is in the cloud? Correct. And it’s continue to work. The important thing that we need to remember is that we’re wanting that database to continue to work. So let’s go and have a look at actually doing the migration. So if I go across to my Azure data migration services, what I’ve done–
How do you go there? Like portal at Azure.com? This is Azure Portal, there’s a CLR interface for it as well. So if you wanting to interact with it via the CLR, you can actually do all of this configuration via CLR. In this case, I’m using the portal just so people can get an experience of visual experience– So when people go to portal at azure.com, what do they type over there to get here? Azure data migration services, you add a new resource, azure data migration services. That’s in essence what I’ve done. It will allow you to create a new project. And when you create that new project, all you’re gonna do is specify your source and destination databases for that new project. And then you will have configuration that looks like this. Inside here, what we’re now gonna do is we’re actually gonna create an activity. That activity is gonna be that pipeline that enables us to do that database migration live. So let’s do an online data migration. I’m gonna specify my source database. As I mentioned, it’s a Postgres in a VM. So this scenario is… You have to provide it with a password. They’ll do a bit of validation in the background to make sure that it can connect to that, that instance. There’s a password Probably filled. So there’s we connected to our source database. Now we’re gonna connect to the target database. This is the database that we’ve now set up in Azure that’s got a got a schema in it, but no data. So that’ll go again, make sure it can connect, do some validation. And then it allows us to choose the database that we want. We’re gonna just do that sample database today. It gives us some options to set up some configurations that are not important ready. This is around limiting the size of your LOBs and how many processes you want to do in parallel. How many tables you have to link. Especially those big one in size.
Then you mark right here. In our scenario, we’ve only got that one table, so it doesn’t matter. But if you’ve got a real database in production, you’re gonna have a lot of tables in there. I’m gonna call this Sample DB migrate. And we’re gonna run that migration. So now what’s gonna happen, is it’s gonna go back to that database, do all the validation against those connections that we’ve got, identify the tables, and build up a migration plan or pipeline for this migration to to run into end. And here, you can see it’s initializing the pipeline. What it does is it takes a couple of seconds. But while it does this, I wanna show you what we’ve done when we ran those scripts that we were talking about earlier, I just ran them. I never showed you actually what’s inside them. So the schema migration basically does a pg dump. So that’s a utility that we have with Postgres, that allows you to dump the database. We specify a parameter that says it needs to be the schema only. Not the data set.
Not the data set. And then we do an import using just a straight PSequel command, we rerun that script that it generates as part of that dump. And that’ll go create all the objects inside there. So that’s the one script that we grant. And then the other one that we we’ve done is we’ve got the Create Load. And this crate load is just a loop that runs through and creates a record. We saw the results of it. But you can see, it’s just super simple things. So let’s go back to VM. Over here we have, it is now running. What we’ll see is that there was a full load that was completed. Because we’ve had that load running in the background. There’s been a fair amount of data generated. Just in a little bit of time, we have 355 rows, and we have an incremental sink. So after it did that full load, we’ve got 46 rows applied. And if we go back to our database itself, and we Have a look here, what we’ll see is we’ve got 446 rows. It should be pretty close to what we’ve got in our source database now, 455. So there’s a little bit of lag as it tries to migrate that data, there will be bandwidth restrictions If you’re migrating from On-premises, it can take a little bit of time. So what we’re gonna wanna do is stop that process that’s generating the data. We’re now ready to cut over. So we’re gonna stop the process that’s creating the data. And we are going to finalize the cut over. The moment has arrived.
The moment has arrived. So when you start this cut over, it’ll tell you that there’s Pending Changes and it’ll slowly bring those pending the changes down to zero. So it will allow us to update that to a point to continue to update in the background to a point where we’re ready to cut over Within a few seconds it should update. And once it’s finished that, that migration, it’s going to enable you to disconnect from the On-premises database. So what’s happening in here doesn’t matter. You see, it’s gone down to zero, we’re gonna click Apply. And now once this is applied, the only remaining step that you have is to reconfigure the connection strings and get over there. Oh, wow. That’s exciting. That’s how simple it is to migrate databases across to Azure. And the application will now connect to the new database. And that will reconfigure the connection strings, the applications are on on the new database. So much easier than what I used to do before. So this is awesome. So I’ve shared some resources for customers wanting to do this. We’ve got some great tutorials update, you can go through step by step and achieve exactly the same thing in exactly the same way. Fantastic. Thank you so much Gary for your time here. Thank you Sanjay for having me. Great. Thanks for watching this short Azure Readiness video.

Leave a Reply

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