SQL Server Database Migration using Azure Arc Explained | Data Exposed: MVP Edition

0:09 Hi, I'm Aokana and welcome to Data Exposed

0:13 MVP edition and May the Fourth be with you.

0:15 Today I'm joined once again by Obi-Wan Goi.

0:19 Uh thanks so much for coming back this year.

0:21 How's it going?

0:22 Uh it is going.

0:23 Thank you so much for having me back.

0:25 We we love this edition of Star Wars, right?

0:28 Yeah.

0:28 Uh we are here to talk about migrations though

0:31 because we are as you can see behind me I'm

0:34 on the planet Hoth and we know the empire is coming.

0:37 So we have data and SQL databases that we have to get out of this this system

0:42 otherwise the rebel or the uh uh empire is going to get access to it.

0:46 So we have to migrate it and so we're

0:48 going to use Azure Arc to do that migration.

0:51 Okay great.

0:52 And you're going to use Azure Arc because you're in a time crunch.

0:55 It's easy.

0:56 what what are some of the reasons you might be using Azure Arc?

0:59 So, Azure Arc uh as you know Microsoft has made great strides in Azure Arc

1:04 and we can connect our on-remise or my my data center here on Hoth to the cloud.

1:10 So, we can easily migrate SQL databases to the cloud

1:14 with a couple clicks of the button in the portal.

1:16 Uh and so it makes it super easy uh to set up and configure and to quickly

1:20 migrate uh off off a planet where

1:22 the the uh empire is going to come and commandeer.

1:26 Amazing.

1:26 Hopefully not too many of our viewers are also facing this predicament.

1:30 But at least for you, I'm happy to see that Azure Arc is here to help.

1:34 So let's uh let's take a look at the scenario.

1:37 Okay, we will jump over to I have a machine that has uh it's a virtual

1:43 machine in a colo here on Hoth uh that has already been connected to ARC.

1:48 So, we have already done the initial stages

1:50 of uh and setting it up is relatively straightforward.

1:52 You can actually go to the Azure portal uh

1:55 create an an arc gateway and then download a PowerShell

1:58 script to run on your servers uh in your own

2:00 data centers and get it connected to Azure.

2:04 Uh and then once that machine is connected,

2:06 you actually get a really nice overview.

2:08 Um this is actually the uh SQL instance,

2:14 if we actually go over to the actual ARC machine.

2:18 So the actual ARC machine will actually tell

2:19 you a lot of information about the VM itself,

2:21 such as the operating system, what version,

2:24 uh we can see that it's VMware right here.

2:26 Um and we can do a lot of things with this.

2:29 One of the really cool things that I really like is that it

2:32 actually will tell me what SQL Server I am running on my local instance.

2:36 So, we can see that we have the Azure extension for SQL Server already running.

2:40 We can see that I am running SQL Server 2022 Developer Edition.

2:44 Uh, and I'm going to want to migrate this out of my my data center

2:49 that is going to be crushed by the Empire here shortly um to a managed instance.

2:54 I think the managed instance is going to be a great

2:56 spot for my data and we're going to migrate it.

3:00 I can click into this SQL server uh virtual

3:04 machine blade and actually do more more things here too.

3:08 I get more information such as the licensing type

3:11 uh I'm provisioned all this all this good stuff.

3:14 I can see that I've got databases on this uh uh virtual

3:18 machine and I have a database called rebel inventory and this database

3:22 has crucial information to the rebel forces as we fight the empire

3:26 um for the for the the cause of the good versus evil.

3:30 So I want to make sure that we migrate this database to MI.

3:35 So I can click in here

3:37 and just a question from my side.

3:38 So you're like right now you're in the Azure portal.

3:41 I just want to be super clear like these are

3:42 databases running in that VMware instance not in Azure at all.

3:47 We just done like some metadata to see what databases are there.

3:51 You are absolutely correct.

3:52 So this virtual machine is not

3:54 even physically located within the Microsoft ecosystem.

3:57 It is running on a virtual machine that's backed

3:59 by uh VMware in the data center here on Hoth.

4:04 So until you connect it to Azure via the ARC

4:06 process uh it is it's on basically on premises.

4:10 is that's where it's living.

4:11 So, uh we install an agent.

4:14 That agent has a communication stream

4:15 to the Microsoft uh data plane control plane.

4:18 And this is how we get all that information out of here.

4:21 Got it.

4:22 Um but you can see that I've got all this really cool database information too.

4:25 I can see that I've got some settings.

4:27 Uh I don't have autoshrink enabled, which is really good.

4:30 I'm autocreating statistics, auto updating statistics.

4:33 So, right here, I don't even have to go to like management studio on premises.

4:38 I can see it all right here in a central pane of glass here in the Azure portal.

4:43 Nice.

4:43 Really, really, really cool.

4:44 I can uh and there is a new preview thing called coming with backups.

4:48 But if I go back a blade and scroll down,

4:53 there's a really cool database migration uh option that comes with Azure Arc,

4:59 especially with SQL Server.

5:00 And we can migrate that database pretty

5:03 pretty seamless in a couple different ways.

5:05 Uh we can use MILink which is a little bit more um complex to set

5:10 up because we need to have either an express route or a VPN connection.

5:14 Um or we can use log replay service which is basically

5:17 log shipping behind the scenes and it works really really well.

5:21 Uh I did an assessment already and we

5:24 can look at that assessment and that assessment

5:26 will will basically give you guidance on hey

5:30 what platform in in Azure should you go to?

5:33 Uh this one recommended Azure SQL database,

5:36 but we're actually going to go to MI.

5:38 We're going to tell you a price.

5:40 Um it can you can actually create a managed instance here already if you want,

5:43 but I've already done that just for time.

5:47 Um as well as some a bunch of other information, right?

5:49 So it gives you a lot of good useful information

5:51 when you want to think about moving to the cloud.

5:54 Um and you're already in the Microsoft ecosystem.

5:59 So, I'm going to go back a blade.

6:03 Uh, I've already created a MI.

6:05 I created a uh MI-Cloud city-01, right?

6:10 Because we want to go to the cloud and cloud city, I think, is perfect.

6:12 Lando Kersian will make sure to take care of our our database for us.

6:17 So, I've already selected the target there.

6:20 Uh, I'm going to click on migrate data.

6:23 This is where you can select your migration method.

6:25 We can do mi link like I said or we can use log replay services.

6:30 Log replay services utilizes Azure blob storage.

6:34 So I have a blob storage account already running uh

6:37 where my back my databases are being backed up to.

6:40 Um so I've already configured that.

6:42 I've run a full I've done some

6:44 transaction log backups and a couple differentials.

6:47 So I'm going to go ahead and select that.

6:51 We can see that the Azure Azure Arc agent has

6:53 already identified that I've got a couple databases ready to migrate.

6:56 I could migrate both Adventure Works in 2019 Adventure Works 2019

7:00 and my Rebel Inventory at the same time if I wanted to.

7:03 In my case, I Adventure Works I was just playing around with.

7:07 So, uh I don't care if the Empire gets that.

7:09 So, I'm just going to migrate the Rebel inventory database.

7:12 I can change the name if I wanted to.

7:15 I'm going to I'm going to leave it as is.

7:20 My location, I am in the east US because

7:24 I want to make sure to migrate my database

7:26 as well away from the Hoth system uh which

7:29 is located uh in another part of the solar system.

7:34 There is my resource group, my storage account,

7:37 my container and then my directory uh is where the uh backups are located.

7:44 It's going to do some validation here and we

7:46 can see that it has all the permissions.

7:48 Uh it is worth noting that the managed instance that I'm

7:51 going to has to have uh blob uh reader access.

7:57 Okay.

7:56 So I granted its managed identity not only storage

7:59 contributor but also reader uh so they can actually

8:02 read the storage account container to to read

8:05 the backups and then be able to successfully restore them.

8:09 And remember this is basically log shipping.

8:12 So this process is going to reach into that blob storage account,

8:16 identify all the appropriate backups, put them in order,

8:19 restore the full backup,

8:21 and then any differential and then any

8:22 subsequent sub subsequent I cannot talk today.

8:28 You're under a lot of pressure.

8:29 The empire is Yes, I know.

8:30 I know.

8:31 The the empire is coming.

8:33 I'm trying to get this done.

8:34 uh it'll restore the differential in any

8:37 subsequent transaction log backups and get

8:40 to a no recovery state and then we can cut over the data the migration.

8:46 So while this is running for someone who like maybe isn't super

8:49 familiar with log shipping like myself um like let's say like maybe

8:53 you are already backing up your backups into the Rebel backups preparing

8:57 for this day like is there anything special you need to do here?

9:01 And the second question is once you start this quick start migration,

9:05 are you now offline for the workload?

9:09 Oh, that's a great question.

9:10 Those are all great questions.

9:11 So the first question was if you're already backing up

9:14 your databases and you should be as any good DBA should be.

9:17 Uh you would have to at least make sure

9:19 that those backups reside in an Azure blob storage account.

9:24 Okay.

9:23 Uh starting with SQL Server 2016 or higher I think might have been 2012.

9:28 one of the for quite a while you've been able to actually

9:31 write backups directly to a blob storage account if you wanted.

9:35 Um or you can set up a uh you know

9:38 a copy process or some sort of file migration process

9:42 where you pick up your local backups on premises and put

9:44 them into a blob storage account and then restore them.

9:48 Uh this what was the second question?

9:51 Yeah, the second question is sorry I should have asked them initiated.

9:55 The second question uh was once you

9:57 hit that start migration button like should I

10:00 assume that like everything is offline for the ripple

10:03 scenario they can't make any changes now

10:06 great question and the answer is no it is not offline so because

10:10 the log replay services and even if you were to use the mi link process

10:14 in this uh through arc uh the database will remain online on premises until such

10:20 point where you want to cut over

10:22 and then change your application uh connection strings,

10:26 right?

10:26 So because because because the log replay services only utilizes backups,

10:32 you can continue to use your on-remise database up until that point.

10:35 Uh backups will continue to occur and then this service will continue just

10:39 to replay those or restore those backups until such time where you want to say,

10:44 "Yep, I'm ready to cut over." There's

10:46 a button in the migration that you can say,

10:48 "Yep, cut over." the process will actually finalize any

10:52 last restores it needs and then bring the database

10:55 online and then at that point that migration is

10:58 complete and you can't do anything else with it.

11:00 Got it.

11:00 So over the course of I don't know maybe it's minutes

11:03 that you're down would you say or it kind of depends.

11:07 It all depends on your migration method and what else you need to migrate.

11:11 Uh it also will depend on the size of your backups.

11:14 If you are trying to move a very large database and your backups are very large,

11:18 it might take longer.

11:20 Uh we use uh log u log shipping to do migrations uh quite often.

11:26 And usually we can get those migrations

11:28 from a database perspective down to 15 minutes or less.

11:32 Quite often a lot faster.

11:35 Um and so here you can actually monitor the the migration.

11:39 So we can see that my source database is Rebel Inventory.

11:42 I am in a restoring state.

11:44 Um, we can see some metadata.

11:48 We've, it actually detected 25 different backup files

11:52 that has been occurring since I created the database.

11:55 Um, it skipped a couple files because it's going to do the full backup,

12:00 a differential, and the most recent uh or the subsequent transaction log.

12:05 So, I'm guessing that there's uh there was four seven files total.

12:08 It restored four.

12:09 It's got three more to go.

12:11 Got it.

12:11 So it's not entirely ready for cut over.

12:13 Now we're down to one.

12:16 I can.

12:17 So the service is smart enough to Yep.

12:20 understand like which backups to restore and which

12:22 diff and if more get added all that.

12:25 Yep.

12:25 So as more files get added uh because I

12:28 haven't changed you you'll notice that I have not gone

12:30 on gone into the source uh SQL instance and changed

12:34 the backup schedule or adjusted anything around that nature.

12:39 We can actually see the database right here.

12:42 Oops.

12:44 So, there's my Rebel inventory.

12:46 There's all my my important table that I need

12:49 for my for my ongoing battles with the the in the Empire.

12:54 I've got backups already scheduled.

12:56 I see.

12:57 But I haven't changed any of these.

12:59 Right.

13:00 So when I go back to the migration, I've got one file left,

13:06 but as backups occur, so I can actually I can actually kick this off.

13:13 So I'm going to manually force a backup.

13:16 That was done.

13:22 So it should eventually refresh and pick that new file out.

13:28 Yep.

13:28 Yeah.

13:28 And then once it's picked up, I can and it's ready this this complete

13:32 cut over button should should become alive

13:35 and then I can finish that migration and actually complete the cut over.

13:40 Nice.

13:40 Awesome.

13:41 So it has this safeguard that it doesn't let

13:42 you complete it if you know it's not safe basically.

13:47 Yep.

13:47 And so at this point I just got to wait until

13:50 until the service sees that the backup has uh actually happened.

13:55 So I think the migration might be done.

13:57 So if we go check monitor migrations,

14:00 we can double check our and we can see here

14:03 in the status the migration status is now ready for cut over.

14:08 Nice.

14:08 Uh so we've restored I don't know if you remember before

14:10 but that number was seven I think but it's now eight.

14:12 So we detected 26 files when originally it was 25.

14:16 So it saw that new backup uh and got it restored.

14:19 And so we can complete the cut over.

14:24 It's going to want to make sure.

14:25 So, there's a safe another safety net here that I want

14:28 to make sure that there's no additional log backups to be uh restored.

14:32 Uh I'm going to go ahead and say, "Yep, I'm done.

14:34 I'm ready to make that decision.

14:36 I'm going to cut over." Now,

14:40 if I go look at my managed instances, there's my database.

14:50 It is currently in a restoring state.

14:51 This portal takes a little bit extra time for it to catch up,

14:55 but eventually when that database comes online, this will actually be online.

14:59 Um, and then it'll we get all the benefits of h that's funny.

15:05 We get all the benefits of, you know, managed instance platform as a service.

15:09 The backups will start to happen.

15:11 We get all the uh this is a nextgen MI

15:14 so we get all the performance features of the nextG platform.

15:17 Uh, and my database will be successfully migrated.

15:20 Awesome.

15:20 Cool.

15:20 Well, thanks so much, Oban Gino.

15:22 I learned a lot and I think you got this just in the nick of time.

15:26 I'm hearing and and seeing some alerts

15:28 about uh the Empire quickly approaching you all.

15:31 So, I hope you're able to safely evacuate or prepare for battle.

15:36 I'm going to get out of here.

15:38 All right.

15:39 Well, thanks so much.

15:39 I I learned a bunch.

15:40 I'm sure our viewers did as well.

15:41 Um viewers, if you're watching this episode,

15:43 we'll put some links in the description for you to learn more.

15:45 Let us know what you think about our May

15:47 the 4th episode and if you've tried out the new

15:50 migration experience in Azure Arc and we hope

15:52 to see you next year on Data Exposed MVP edition.

15:55 May the fourth be with you.