DP-300 Microsoft Azure Database – Evaluate a strategy for migrating to Azure

1. 15, 16. evaluate requirements and strategies for the migration

In this video, we’re going to evaluate requirements for migration of data onto Azure. And we’re going to evaluate offline and online migration strategies. So the requirements you have to look at what workloads you intend to migrate, the actual resource requirements, in other words, hard drive space, compute, size of processing power. Do you need a version of SQL Server or Kind? You use the latest version. Remember, as your SQL Database, azure SQL managed instance will be using the latest stable version or a variant of that. Now if you need a specific version, then you will need a virtual machine. And if so, you need to decide what version of Windows Server is needed. Now, when migrating, what about downtime allowances? Have you allowed for any downtime at all? If not, you will need to do an online migration and that will reduce your options.

Are there dependencies between databases? Remember Azure SQL database can’t do cross database queries. Are there dependencies between databases and applications? What are your security requirements? Backup and restore requirements? Have you got some current limitations? What limitations do you anticipate in future? And what about the location for your data storage? Do you have something like the GDPR, the General Data Protection Act? Maybe the California Consumer Privacy Act or similar requirements? So you can see on the screen that there are five different programs apps you can use, and they have different requirements, different uses. So do you need to lift and shift SQL Server to a virtual machine? If so, you should use Azure migrate.

It can also discover and assess your SQL data estate at scale. In other words, across your data center. You can get Azure SQL deployment recommendations, target sizing, and monthly estimates. You need to migrate NonSQL objects. So we’re talking things like access DB two MySQL Oracle Sapase databases to either SQL Server or Azure. SQL? If so, then the SQL Server Migration Assistant or SSMA should be what you’re looking at. So if you’re not lifting and shifting, if you’re not migrating non SQL objects, then you’re migrating SQL Server objects to SQL Database or SQL Managed instance.

So if so, do you need to migrate and upgrade SQL Server? If so, have a look at the data. Migration Assistant, or DMA. It can help migrate to Azure SQL Database or to SQL Server on a virtual machine or to another on Prem Server. It can also discover and assess SQL data estate and recommend performance and reliability improvements for your target environment. It will detect combatability issues between your current database and the target version of SQL Server or Azure SQL.

As you know, Azure SQL database is not fully featured. You can move your schema, your data, and your uncontained objects. Now, do you need to compare the workloads between the Salt and target SQL Server? If so, then have a look at the Database experimentation. Assistant DEA You can capture the workload of a source SQL Server environment and identify compatibility issues. Now, do you need to migrate open source databases such as MySQL PostgreSQL MariaDB? If so, have a look at the Azure Database migration Service DMs. You can have minimal downtime, which is especially good for large migrations. And you can do this online using the premium pricing tier. So for this you would need to allow outbound port four four three. That’s the same one that’s used for Https. So secure. Waiver you can also might need to have 1434.

That’s UDP that’s quite often needed for SQL Server. You then need to enable the TCP IP protocol that’s usually not enabled by default. You then create an Azure SQL Database instance. So for that you need a server level firewall rule on your server to allow the access to the DMs, the Database Migration Service, and then on the target database you would need Control Database permissions. So you need a firewall on your computer or your server, control Server permissions on your server, and then Control Database permissions on the target database. Now this doesn’t actually initiate any backups. It uses existing full and log backups. It doesn’t use differential, it uses full and log backups. Now can you do it online or not? Well, it depends.

While you’re migrating from or to so you can see if you’re migrating from SQL Server to Azure SQL, then it can be online. If you are going to a managed instance, but it’s offline in other cases. If you’re going from MongoDB, then it can be online or offline. You’ll be going to Cosmos DB. In that case, if you’re going from MySQL to the Azure database for MySQL, then it’s an offline one time migration. And if you’re going from an on prem postgradu SQL to the Azure Database for PostgreSQL, then it would be an online migration.

So it would be a continuous sync organization. So here we’ve got five different strategies and you can see there is a bit of overlap sometimes. So it really depends on what more accurately defines what you’re want to do. Do you want to lift and shift SQL Server? Do you want to migrate non SQL objects? Do you want to migrate or upgrade SQL Server? Do you want to compare workloads or do you want to migrate databases, open source databases, either offline or online? If so, these are the things that you can be using.

2. 19, 20. Azure Migrate

In this video we’re going to start looking at some of the tools that I mentioned in the previous video. I’m going to start with Azure migrate. So you can see, you can get to it in the Azure Portal. Now, when we’re talking about Azure Migrate, do be careful of the terminology. There is the Azure Migrate hub which includes things like the Data Migration Assistant and the Azure Database migration Service and other things. However, the Azure Migrate itself has got two tools.

First of all, discovery and assessment. So you can see here discover, Assess and Migrate servers, databases and web Apps. So we can start creating our own project and specify things like where it is. So my migrate project and I’ll give it a new rule exhaust group as you migrate. So with this you’ll be able to discover and assess on premises servers running on VMware HyperV so that’s all virtualization and also physical servers in preparation for the migration to Azure. You’ll also be able to migrate servers so you can migrate VM wires, VMs HyperV, VMs physical servers, buffer virtualized servers and Public cloud VMs to Azure. So you can see that we’ve got these assessment tools.

So I can click Discover to start and we’ve got migration tools for your machine. So click on Discover to start and you get questions like are your servers virtualized? Nope. And then you’ve got some methodology of actually going through your onpremises or cloud environment. So just going back to here, you can also see we have got discovered for the server migration which gives us the same thing at the end. So these are the two tools of Azure Migrate, which is separate from the Azure Migrate hub which is the overall name for migration, which includes things like Azure Data Box.

So Azure databox products can help move large amounts of offline data to Azure. Basically, they send you the box. This is a storage device with 80 terabytes or so of usage and then it can be transported back to Microsoft and they can load it up themselves to Azure. But these are tools within the wider Azure migrate hub. But for the Azure Migrate tools themselves, you have got server migration and you have got discovery and assessment. So this is Azure migrate.

3. 19, 20. SQL Server Migration Assistant (SSMA)

Now in this video we’re going to talk about the SQL Server Migration Assistant, and you can see we’ve gone out of the portal here because this is a separate downloadable program. So. It’s a tool designed to automate database migration from Microsoft Access DB two MySQL Oracle and SAP ASE. So you can see that there is a different download for each one of these migration sources. So it’s not just really the one program, it’s a series of programs. And you can go to any version of SQL Server since 2012. But more interestingly for this course azure SQL Database and Azure SQL managed instance. Now if I were to click on any of these, you can see that there is an awful lot of documentation that is available for each of these programs. So you needn’t worry about whether there is any documentation.

There’s an awful lot. If we just go back, you can see down here we have got the downloads. So rather than having you watch all of the downloads, the download actually is quite small. It’s about eight megabytes, but quite a server file that needs to be downloaded, a fever program of about 400 megabytes. So rather than actually just watch me download it, I’ve also downloaded separately. And then just see what happens when you install it. So you can see that there are six steps here.

First of all we create a project and then we add access data files to the project, select the object, the tables, the views, et cetera, for migration, and then connect to SQL Server or Geo SQL Database where we can then link the migrated tables, which is optional, and then migrate the data. So the first thing would therefore be creating a project. So that’s very easy to do.

And you can see what I’m migrating to. So I’ll migrate to an SQL database here on Azure, then it’s going to ask me to add databases. Well, I just so happen to have a database here, we’re not actually going to migrate it, so it doesn’t really matter what’s contained inside. Then we can select what we want to migrate. So we’ve got a series of queries and tables. This database actually is empty, so we’re not going to be migrating anything. And then we connect to the Azure SQL database.

So this is exactly the same dialog box that you might get if you were hopping up a database in SSMS, SQL Server Management studio. So once you’ve entered that server name, database and so forth, then you can continue. So there are my details and it’s not actually allowing me to connect just at the moment because I would have to set up a firewall. So what I would do, instead of actually using either the Windows as you’re management portal or the Executive store procedure SP set firewall rule, I’ll just open it up in SSMS which would have the same effect.

So having done that, you can now choose to link your database so you can see what happens if you choose to do that. So linking modifies your access database. So instead of using the data in Access, it then uses the data in the SQL Server database. So you’ve got the one source and then you can see the final step. Convert selected objects, log into the target database, and migrate data for selected objects. So I’m just going to finish it there. So this is SQL Server migration Assistant. So you can see this is the Access version, but there are lots of other versions.

So four or four versions. So we’ve got versions for DB two MySQL Oracle and Sapace. So the SQL Server Migration Assistant should not be confused with the Data Migration Assistant, which is for migrating SQL Server itself. So it can be a bit confusing. The thing that says SQL Server Migration Assistant is not for migrating from SQL Server, but the one that doesn’t have SQL Server is for migrating SQL Server.

So imagine this is now just the target. So it’s a Migration Assistant to SQL Server. You don’t need that for something that already is in SQL Server. Then you just need to migrate the data. So this actually converts the objects, the tables of views, etc. Into SQL Server, which can be Azure SQL Database or Azure SQL managed instance, as well as onprem and virtual machine versions.

4. 19, 20. Data Migration Assistant (DMA)

In this video we’re going to look at the Data Migration Assistant. So this allows you to go from SQL Server into SQL Server or Azure SQL Database. Now you’ll notice that Microsoft says for large migration in terms of number and size of database, then they recommend you use the Azure Database Migration Service because that can migrate databases at scale. So here you can see some of the capabilities. So you can assess an On Prem SQL Server for migration onto Azure SQL databases, you can discover issues that could affect an upgrade, you can discover new features in the target SQL Server platform and you can migrate an on Prem to a more recent on Prem or on an issue virtual machine.

So let’s have a look at how we can do this. So first of all, you can download the latest tool from the Microsoft Download Center. So I have previously done this. So here is the DMA, the data migration assistant. So you can see get started. It’s just a little plus here and I say this is a new assessment. So I’m going to call this assessment. So the assessment type, I’m going to be looking at a database engine as opposed to SSIs Integration Services, and the source is SQL Server and the destination is an Azure SQL Database. You can use Managed Instance as well. Or Azure Virtual Machine or an on Prem.

So let’s click right and you can see what you can look for. You can look for database compatibility, so you can look for migration blocking issues. So this looks for features which are supported or unsupported or maybe partially supported that may block migration and it will give some recommendations for what to do. You can also look at the features, see what features and functions that your applications may rely on. And you can also coming soon, it says benefit from new features. So see what will be available that you don’t already have. So click next.

So I need to put in myself, well, I’m going to use my local server name and I’m going to trust the server certificate. So click Connect and I’m going to go for a particular database. And you can also put in an extended events or Traces if you so wish to assess those as well. So start assessment and in just a few seconds, because this is a very small database, you can see some unsupported features.

So SQL Server agent jobs are not available. So we know this about Azure SQL Database, it doesn’t use agent jobs. So if I needed that, then I might need to go onto the managed instance. So it doesn’t support some other things like Windows authentication, and there may be some features which are partially supported so that’s the SQL Server feature parity. And then we have a look for compatibility issues. So this is of interest if you’re going from say, SQL Server 2012 to 2019, or as your SQL Database which is always based on the latest version.

So that is the assessment. And you can see, you can save, export, restart and delete these assessments. And you can also upgrade it to the Azure Migrate hub. So now let’s have a look at migration. So again, I’m going to give this a project name. I’m going from an on Prem SQL Server to SQL Database in Azure. And I can say what I want to migrate. The schema and the data, the schema and the data only. So the schema, we’re talking about schema, we’re talking about things such as if I expand this one, the tables, the store procedures, basically everything that can contain data or can be quick to migrate. So we’ll be generating a script for this. So I’m going to create this new project.

So again, I’m going to connect to my source server. So I must have control server permission on the source. So let’s say I was going for this one, there’s a reason why this one won’t actually happen. And I can assess the database before migration. So that gets me back to the assessment. So I’m not going to do that. So now I’m going to a particular server. So this is my Azure server. And here we go. And again, I need Control Database permissions on the target database. So I need Control Server on the server, the source and Control Database on the target.

So if I was to pick my Azure SQL database, you can see we’re running into a problem. And that problem is that the collation is not the same. So the collation how it sorts. So here is the sort of code that you would need to change the collection. So now if you notice, I have the collections now being the same for this database, one database. So I’m going to now go back and select the 74 61 database so that has the same schema. It then says, okay, what do you want to export? So these are tables and store procedures. So my schema objects. So it’s not going to export the data at this time.

And here you can see it would create the tables so I can deploy this schema. So there it’s run. And next I’ll be migrating the table. So it’s run this script on my database. And if I go into my database on Azure, you can see we now have these additional tables. So next I can then migrate the data, I can select what data I want to migrate. So I’m just going to migrate a very small amount of data and then start the migration process. And you can see it only took 7 seconds. And if I go back in, because it’s a very small amount of data, and I say select Star from its table department, DBO Table department, we’ll get to the five roles that I’ve got in my source data.

So this is an overview of the data migration assistant so it allows you to go from your database engine of SQL Server to Azure SQL Database managed instance and an onprem SQL Server or Virtual Machine version. However, please note what Microsoft says. If you’ve got large migrations, we recommend you use the Azure Database Migration service, which we’ll be looking at in the next video.

5. 19, 20. Azure Database Migration Service (DMS)

In this video we’re going to look at the Azure database migration service elsewhere. It’s called the Migration Services. So it’s a fully managed service to enable seamless migrations to Azure data platforms with minimal downtime, so it can allow for online migrations. Now, I should point out that this is a constantly expanding service. So you can see for instance, since using the Azure data studio is now in preview, so what we’re going to do is we’re going to go into the portal of Azure and I’m going to look for migration. And here we can see Azure database migration Services and I’m going to create a new service. So we need a subscription, we’ve got that. We need a resource group. I’m going to call this the Migration Service. We need a name. So my migration service, we need a location. So plenty of locations to choose from. You can choose a service mod.

Now this says where the worker? Where the thing that is actually going to do the work is going to be hosted. Is it going to be in the cloud or is it going to be on premises? Well, I’m going to say it’s the cloud. Now this is probably the most important part of this, the pricing tier. So you can see the number of vCores that you can have for this migration service. And at the standard you can choose from one, two or four VCOs. So you can click, how can I choose the right VCOs? And there’s an article, it’s not very informative to be honest, because the price is zero, zero. So it doesn’t really matter how many vCores you choose to do. I go up to the maximum value. But are you happy with the standard?

Let’s go for the premium. And you can see the premium is vCores and it is free for the first six months. Now you can use it for a total of a year and you can create two services per subscriptions, DMs services, database migration Services. Now why is the standard and premium? The standard tier supports offline migrations. Only the premium supports both offline and online. So as I say, that is probably the most important part. So I’ll just go through the rest of it. You can see we need to put something in networking. So I need a new virtual network name. So I’m going to call this migration VN and create So you can see it is now deploying. So I’m going to pause the video until it has finished deploying. So now you can see the deployment has succeeded and I can now create my first migration project. So I’m on premium with four V codes which allows me to do offline. And online, the standard only allows me to do offline. So let’s click on New Migration Project.

So here is my migration project. So I’m going from SQL Server and you can see the various options that I’ve got to a particular target server so if I was going from postgres, then my target would be Postgres, same for MySQL, same for MongoDB, that would go to Cosmos DB. And I can choose to have the Schema only migration. So that includes the empty tables, the data migration, or I can create a project only so that allows me just to create the project and execute it later. And you can see before you start a migration, we recommend using the DMA, which we had a look at in the previous video. So we need to create a target. Azure SQL database.

We use DMA to access, we’ve done that, we can apply Fixes done that. And we deploy the Schema using the DMA. Don’t know that. So create and run activity. So you can see here I’ve got to enter my SQL Server instance name. So this would need an FQDN, a fully qualified domain name or an IP address. And then following that you then go through each of these other steps. So you select the databases, you select the target. You would map to target databases. So this will be mapping to new databases. Unless you’ve got a database with the same name, you would be configuring migration settings and selecting tables to be affected. And then you would have a summary and you would enter an activity name for the migration. You could then click on Start Migration to run it and monitor the migration from there. And then once complete, you would verify that the target database has been migrated. So this is the Azure Database migration service.

And as you saw, it is for putting SQL Server or Pulsegrassql MySQL or MongoDB onto the Azure databases. Now there are of course other migration strategies. You could use BCP, the Bolt Copy program that can be used for connecting from an On Prem or SQL Server on a virtual machine to Azure SQL. You could use the command bulk insert, so you could log data from Azure Blob storage. You could use SSIs SQL Server Integration Services packages, so that could be used for ETL extract, transform and load. And you can use Spark or Azure data factory.

So I just want to point out, even though we’re concentrating on these specific Azure migrations in this course, there are other ways of having a migration strategy. So these are various ways to implement an offline or online migration strategy. If you’re doing it online, then make sure that you choose the premium pricing tier, which is about a dollar for every 3 hours and allows online continuous migration and offline migration and faster speeds. But you can also have it free for six months. So this is how you can migrate your data onto Azure.

• Category: Uncategorized