In Cloud Consulting
On Prem To The Cloud: Migrating to Azure SQL (episode 7) - read the full article about cloud migration, Cloud Consulting and Data migration, Cloud infrastructure management from Azure DevOps on Qualified.One
Youtube Blogger
>> Hey, everybody. Welcome back to the DevOps Lab.
I am Damian being joined by Jay, and this week we are looking at the next part of our On Prem to the Cloud series, which is moving our database across to Platform-as-a-Service.
Dont miss it. Its pretty cool stuff.
[MUSIC] >> Hey, everybody. Welcome back to the DevOps Lab and another episode in our series of On Prem to the Cloud.
This week, Im joined by Jay Gordon who is going to show us how to move to Azure SQL. Hey, Jay, how are you? >> Hey, Damian, how are you? >> Good.
>> Awesome. Im so glad that were going to get to finish up this series.
I think we did a lot of really interesting things.
Weve learned how to do a lift and shift.
Weve learned about putting up gates.
We also learned about putting it all into code, moving our web apps, all that stuff, Damian.
I think today, were going to fit in one last important part that we can talk about which is actually moving our database to a Platform-as-a-Service.
>> Yeah, absolutely. The first thing would be, I guess, why do we want to move? I know weve hit this question every time we do an episode.
Whats the benefit? Why are we moving to Platform-as-a-Service for our hosted SQL? >> Ive thought about what it takes to actually manage a SQL Server or any database server for that matter.
What it really comes down to is updates and updates of your operating system, the actual version of SQL that youre running.
Then you also have to, if youre doing it on your own, youre going to have to make sure backups are taken care of, security.
All these big ideas that Platform-as-a-Service helps take care of for you, all on the back-end.
Thats what Azure is doing.
Azure is thinking about what it is you need to provide as far as a database and giving you those tools without having you need to manage all the things like version upgrades of SQL or having to ensure the backups are running.
None of that is stuff that we think is going to help us get our applications running.
All that is just extra work that we can avoid.
>> Yeah. Much like the other stuff, its taking all of those things that arent core to the application away from us, so we dont have to worry about them.
Thats really cool. Azure has more than one option for hosting a SQL; what options do we have? What can Azure do for us there? >> Sure. I love this image.
It is from our Microsoft Learn module about Azure SQL.
The first thing is running it on your own or Azure SQL virtual machines.
Thats basically giving you a VM with an installation of SQL Server, and its making you have to do all the big work anyhow.
Sure, you can use Azure Backup services to backup your full virtual machine.
But it really seems like its more work than you need.
Then theres managed instances, and this is really good for those lift and shift migrations.
You get a lot of the features associated.
Youre talking about being able to modify vCores.
Youre getting it so that you can actually add it to a VNet so that you can have a VPN or some private connection to the database.
Its a fully-managed service, but still giving you some of that underlying ability to make some changes.
But then were going over to what is finally the PaaS version or Database-as-a-Service, anyway you want to call it, Platform-as-a-Service.
Thats making sure that you have all those services for you, not automated.
It provides you with all the things that were talking about.
Its fully-managed, serverless, hyperscale storage up to a hundred terabytes.
Then we have elastic pools which allow you to do resource sharing, simplifies your performance.
Theres an AI bit that helps you do optimization of SQL Server.
Its all baked in there, Damian, to help you really build your applications without having to build a database server and then do all that big DBA work.
Lets have our DBAs actually manage our data instead of managing backups or scheduling version upgrades.
>> Yeah.
>> Theres one other thing that I really love too about Azure SQL, Damian, is that it is version-less.
When I say version-less, its not a specific version of SQL Server.
Its actually just based off 2019, but its not like were having to say, "Well, this version of Azure SQL, its connected to a instance of SQL 2016," or something like that.
You know what I mean? Its unnecessary for this particular situation in a Platform-as-a-Service database to really have that is a big, big idea.
Rather, were going to shift to the idea of version-less so that we can just consider where our data is going and how its getting there, and eventually, how were querying it.
>> Yeah, awesome. Speaking of, how do we get it across? I know when we did the web one, there was a migration tool to help you do that.
Is it the same story for SQL? Do we get some tools like that? >> Sure. Theres Azure Database Migration Service and this is a hosted migration servers within Azure.
It allows you to create this main migration service and then allows you to do this.
We can create a new migration project.
But you know what, Damian? I actually want to know a little bit about our database before we actually migrate it.
>> Yeah.
>> While this is a great service to have, we can go into the actual Azure Migrate Service.
These are two separate services, but Azure Migrate is going to let us go into SQL Server here on the left, and then its going to give us some assessment tool, so we can either do database migration, database assessment.
Were going to start with an assessment.
To start that assessment, we just go ahead and click "Assess".
Were going to download the Data Migration Assistant tool or DMA.
Thats basically just a MSI that you install on your Windows machine or anything like that.
Then we can create an assessment.
Lets take a look at that.
Im on my actual server that I want to do the migration from.
We can see Ive got a project name, where Im going from.
Our source is SQL Server.
Our target is going to be Azure SQL, and then we can say were going to just do a data only or schema, whichever we want.
Lets start with this assessment.
Lets go ahead and click "Create".
Well click "Next" here because were going to go ahead and start checking our database compatibility.
Ill click "Next" and as you can see, we can decide how we want to authenticate, whether its SQL Server Authentication AD or just standard Windows authentication.
Im going to use that, Ill click "Connect".
Now, Ive got my database here on local host.
What Im going to do is click "Add".
Now, its going to start our assessments.
Lets click "Start our assessment".
Its going to start going through, and its going to tell us what exactly is supported.
In this case, its not supporting EKM and Azure Key Vault integration.
Once weve finished our actual assessment, we can click "Upload to Azure", so we can select where on Azure.
Eventually, itll connect it.
Well connect via our authentication.
Once that completes, we can select our subscription and then our database migration account.
Once weve finished that, were going to get this assessed database in the Azure Migrate portal.
What we can do is actually click "Assessed databases instances", then well see right here, the database instance name we came from was local host 127001.
Our readiness status is 100 percent because we werent actually using key vault or anything there, it was just the databases on a VM.
Now, we look at our version that were working from, the number of databases that were actually going to migrate.
We can go in and itll actually do the assessment of the actual database itself.
We looked at the instance, now, we looked at the database itself.
Then it actually tells you how many migration blockers there are.
Now, here comes the cool part.
Weve made a decision of how were getting everything migrated and were going actually start doing it, were going to use Azure Database Migration Service.
Its super easy because its just pretty much point-and-click now.
We can click "New Migration Project" and well give it a name.
Well just call it db123mig.
Sure. Well say what our source server type is, its SQL Server, our target server type.
Were going to be moving into Azure SQL database, we have these three options, however, this is the one were going to be using.
We can move either a schema, we can create a project or our data.
Im just going to move our schema first.
So we can go ahead and click "Create and Run Activity".
What its going to do is ask me for the actual server, IP address or name.
If weve got it in BNS, we can use that.
Then we need our username.
Our username is Abel.
Thank you so much, Abel, for creating this SQL Server for me, it helped a lot.
Then Ill pop in my password, Im going to have to make sure I trust server certificate.
Then now, we get to go to the target.
Our target is this particular database right here.
Weve already created an Azure SQL Server.
Now, all we have to do is go ahead and provide it.
Were not going save Abels password.
Were going to put in here the actual target server name, were going to put in our username, and I made it pretty easy, I just use this.
Then were going to put in our password.
Im not telling you-all what my password is.
Now, we can select the database and the schema that were actually going to be migrating.
Now, its going to validate that I can log in to everything, were not saving my password either.
But thank you very much, Edge.
Then were going to go ahead and were going to select what our source database is, were going to go to our target database which is this sqldbjagord.
Then what were going to say is were going to go ahead and create our schema from the source server.
Well click "Summary", and then well click "Start Migration".
But hey, I forgot to give it a name.
You actually have to give this a name and so well just plug dbming123.
Dont worry about this validation error, it just keeps you honest.
Then well just click "Start Migration".
What well see here is weve started our migration, itll take a few minutes, and then itll start actually moving our schema over.
You can see its generating the script and then eventually, it will copy the script and run that query.
That query will eventually create our database, and then we can go ahead and migrate our data.
>> Awesome. When you did this, I noticed as well, it was asking you for the IP address for the virtual machine that the SQL Server was on and things like that.
I can imagine thered be a few people whose SQL Server leaves behind firewalls and all that kind of stuff.
I imagine you can probably change some firewall rules to get around that, but we had a migration tool on that SQL Server.
Is that an option for doing this as well? Is that an easy one? >> Absolutely. Lets take a look at it right here.
We looked at the assessment.
>> What a question that was.
>> Yeah. Right here, we have our migration option that we can select, and so we can do the same thing.
Were actually running this on the server, this case move the schema and the data.
Well click "Create", and now, were going to connect to our source.
In this case, were just going to pick local host.
Well use Windows authentication.
Because were already connected, we dont have to put in a username and password.
Right now, we can see our database is there, its selected.
Itll do an assessment if we want to, but weve already done in assessments we dont need to. Well click "Next".
Now, were going to decide where were actually going to send it.
Heres that SQL Server that I created, heres my username.
Were going to use SQL Server Authentication, so Im going to put in my password.
Then well make sure that these connection properties are done, well click "Connect".
Its going to collect the database that were going to be moving, well click "Next", itll prepare the source database schema.
Itll do exactly what we saw before, were going to bring over our stored procedures, our tables, we can even bring over our users if we want to.
Itll generate that SQL script just like Database Migration Service did on the other side.
Now, what we can do is deploy the schema.
We click "Deploy Schema", itll write all, creates the schema.
Then after that, lets click "Migrate Data".
Itll select all of our tables, will start data migration.
Now, itll go through the process, and we get this nice little view of everything moving over.
You see how fast that was, Damian? >> Yeah, thats pretty good.
>> How do I know everything moved over? Well, Azure Data Studio.
Really, really awesome tool.
Lets go ahead, lets refresh this, lets go into databases.
Heres our database right here.
Lets take a look at our tables. Here we go.
Heres food log entries, we can go ahead and we could query that if we want to.
Weve got everything that we were looking to get migrated over into this managed platform as a service.
No more writing automation code to build the server.
You dont have to do it manually, you can use things like arm, you can use services like Terraform.
You can use all these tools that are built to actually create your databases, you can use the portal, you can use the API, Azure CLI, create our database, and then use these tools to migrate everything over.
>> Thats awesome. Theres one last thing that I can think of which is the actual app itself.
Weve got the database over, weve got the app over, but we need to connect the two. I presume thats pretty easy.
>> Sure. Here in Azure SQL database for our specific instance that were talking about, we can go over here to connection strings.
Here, we have are our connection strings.
For .NET, all we have to do is just change our password here, we can use for JDBC, ODBC, PHP, Go.
Weve got all these examples of how to actually take this connection string.
Then what we do is go into our config file, drop this in, change the old one, and then restart IAS if we need to or whatever it is, and go ahead and serve our application.
>> Yeah, absolutely. Because web app is a service, we can just put those settings through the portal if we want or we can automate them the same way as before, and suddenly, got our database and we got our app all up in the Cloud as performance as a service, which is very cool.
Awesome. Thank you so much for showing me that, thats actually a really straightforward process, that one. Yeah.
>> Yeah. I got to mess around a little bit with Azure Migrate, I got to spend some time with that database Migration Service, got to learn a little bit more about those bits and pieces, they bring it all together.
Then when you actually see it all into one big service, you feel a lot less anxiety about the process of moving into your new service, and you can really just trust these tools to help you get it done.
>> Yeah, thats awesome. Weve pretty much migrated everything to the Cloud.
I know we still have an episode or two left in the series.
Im looking forward to seeing whats happening next.
But thanks so much, Jay.
>> Me too.
>> That was awesome.
Great demos. Thank you, everybody, for watching and well see you at the next DevOps Lab.
[MUSIC]
Azure DevOps: On Prem To The Cloud: Migrating to Azure SQL (episode 7) - Cloud Consulting