In my last post I described the database design for my payroll poc project. For that tutorial I created the database and data with SQL Server 2017 that I have running on my local development machine. That's fine for initial development, but I need that database in Azure for the poc to work as I'd like. In this tutorial I'm going to use the Azure Data Migration Assistant (DMA) to get my database in Azure. The migration assistant is a small program that you download from Microsoft then install and run against your local environment or dev server. When you start up the assistant you'll be greeted with a splash screen with a small walk through create a new project. You can create either an assessment or a migration project. It is a good idea to do the assessment before you blindly try migrating you database. The assessment will check for features that are enabled on your database that may not be compatible with the destination server. When I ran the assessment it flagged that I was using Windows Auth and that it wouldn't work on Azure SQL. After you fix any issues you can create a new migration project that will move your schema, data or both to the new destination. I setup an Azure SQL Database in Azure portal already that I'll use as the destination for the migration. Let's walk through the whole process from start to finish. The Assessment ProjectAfter launching the DMA click on the plus sign to create a new project and select Assessment as the type. Give your project a name, and select the target server type (currently only SQL Server is the only supported source). I selected Azure SQL Database as the destination and clicked create. Next you will walk through all the things you want the assessment to check. The first step is select if you want for database compatibility, feature parity or both then click Next. Now you have to connect to the source server. This is the source server, not the specific database you want to migrate. My local computer name is 'alpha' and I have windows auth enabled on my sql server. Encrypt connection will be checked by default, but I had to disable it to connect to my local SQL Server. Click Connect. Next you will select individual databases to assess. After you add the source(s) you can will be given the option to Start Assessment, as well as add or remove any sources as needed. Once the assessment finishes running you will get a report of any issues that it found. If you selected both feature parity and compatibility you will see the radial buttons on the left to switch between reports. You can see that the report flagged that I have windows auth enabled and that it isn't supported in Azure SQL Database. Once you fix any issues that it found you are ready to move on to the actual migration. Creating the Migration ProjectStart by clicking on the plus sign to create a new project, only this time select Migration. You'll have the same options as assessment (name, source server, and destination) plus another option to select if you want to migration schema only, data only, or both schema and data. This is the first time I'm running the migration so I want both schema and data. Next you need to connect to the source server. Enter the same values for server, auth type, encrypt connection and trust server certificate that you did when you ran the assessment then click Connect. You should see the databases at the source you can migrate. Select the database you want to move and click Next. Now it is time to enter the connection information for the target server. If you are migration to Azure SQL you have to already have a database created, the DMA will not create a new database for you. For Azure SQL the server name should be {yourappname}.database.windows.net. Use SQL Server Auth and enter the username and password for the target server. Since you are leaving your local environment you will want to encrypt connection. Once you have entered everything click Connect. You'll see a list of databases just like when you were selecting the source. Select the database and click Next. Now you need to select any tables that are either new tables or have changed schema since the last deploy. If nothing has changed you can deselect any tables you don't need to migrate. You can click on each table name and see if there are issues that would prevent the migration. Once everything is set click on Generate SQL script. It's time to deploy the schema updates and new tables to the target server. You can check the generated script for any errors or copy the script and run it yourself from another tool, like SSMS. If you are good to deploy the changes click Deploy schema. After the schema has been deploy it is time to move the data. I already run the migration once so when I go to select tables I get the messages on my tables that the target database is not empty. If this is the first time this database has been created in the target you can select any/all the tables you want to move and then click Start data migration. Once you start the migration you'll be taken to a results page like the one shown below. Each table that you are migrated will be listed and the In-Progress, Successful, Warnings, Failed counters will update in real time as each table is migrated. If there were any issues with a specific table they will be shown here. That's it. You have migrated the schema and maybe data from the source server to the target server. After the migration is complete you can delete the assessment and migration projects if you wish. Final ThoughtsThe Data Migration Assistant is a great tool for quickly and easily moving a database from your local or development environment to Azure. I wouldn't use the DMA in a large production virtualized environment. In those cases I would use the Azure Database Migration Service (DMS), currently in preview. DMS requires a virtual network connection between Azure and your internal servers. Additionally, you have to install a virtual appliance in vCenter Server in order to allow Azure to discover any machines you want to migrate. Let me know your thoughts.
Happy Coding Sean Wernimont The Blind Squirrel Copyright 2015-2020
|
AuthorWelcome to The Blind Squirrel (because even a blind squirrel occasionally finds a nut). I'm a full-stack web and mobile developer that writes about tips and tricks that I've learned in Swift, C#, Azure, F# and more. Archives
April 2018
Categories
All
|