Category: Azure DB

How to Move an Existing Database to Azure SQL DB

When moving the enterprise to a cloud based solution such as Azure SQL Database, we will need to find a way to migrate existing database to the cloud.  I already completed a post on how to create an Azure SQL database.  This post will focus more on how to migrate an existing on-prem databases to an Azure SQL Database.  There a several options that you can use, use the “Deploy Database to Microsoft Azure Database…” option in SQL Server Management Studio, Data Migration Assistant tool or use the Azure Database Migration Service.  During this post I will cover all three options.

Deploy Database to Microsoft Azure Database…

After opening SSMS, move to the database you wish to move to Azure SQL Database and right click on it.  When you do, you will see this:

After clicking the first next in the wizard,  you will then be asked to provide some detailed information about what you are attempting to migrate to Azure SQL Database.

This information includes the following:

  • Server Connection – this is the Azure location you are attempting to migrate to
  • New Database Name – this will default to the name of the database you right clicked on, but can be changed in Azure SQL Database if you have a need to change it
  • Azure SQL Database Settings – these will not be active until you identify a Server Connection
      • Edition of SQL Server
      • Maximum Database size
      • Service Objective
  • Temporary File Name – This is the name and location of the .bacpac file.

What is a .bacpac file?  Since you may not have worked with this before, I thought I would take a few minutes to go over exactly what a .bacpac file is. It is considered a Data Tier Application that can be used for deploying databases from one location to another.  This file will contain both schema, object definitions and data.  There is also a .dacpac file type.  The only difference between the .bacpac and .dacpac is that the .bacpac is the only file type that also contains the data.

This is what the screen will look like.

When you click “Next”, you will have an opportunity to review all the settings before clicking “Finish”.  When you do, you will see the progress of the migration, step by step.

As you can see above, there are number of steps.  You can also see that there is a step named “Processing Table”.  This step will show each table as it is processed and migrated to Azure SQL Database.  Once the migration is completed, you will now see the database in both locations, the original SQL Server and in Azure SQL Database.

Data Migration Assistant

This is a relatively easy tool to use.  It really has two purposes.  One to assess an on-prem database to identify what challenges you might encounter when migrating at database to Azure and the other to actually complete the migration.

This is a tool the must be downloaded and installed before using.  To download it you can go here.  Download DMA.  Once downloaded and installed you will find it list as “Microsoft Data Migration Assistant” in the Start menu, not under the SQL Server items.

Below you will see the opening screen in the DMA.  As you can see there are two options, completed an assessment or do a migration.  By clicking the “+” on the left side you will have an option to chose what you would like to do.  Notice that there are also options to restart an assessment or open an assessment.  These are exactly what you might think they are.  When you complete an assessment you will be able to save the results or save the configurations of the assessment that you can restart if you like.

When you click the “+” sign you will then see a few options that you can chose from.  Here you will be able to chose to complete an assessment or a migration.


Both of which will require a project name, a source and target server type.

You will have several options for a target as you can see in the image below.  Since this blog post is about the migration of a database, we will focus on that target.

Once you pick you source and target, you will then be able to click the “Create” button.  Once you did, you will see this.  You will see three options, although only two are available at this time.  The two options that are available are Check Database Compatibility and Check Feature Parity.

Once you click “Next” you will then be asked to identify a source.  Since this post is about migrating an on-prem SQL Server database to Azure SQL Database, the source will be a local instance of SQL Server.  You will have to provide an authentication method and then the database(s) you would like to complete the assessment on.  Once you have provided all the information you can click the “Start Assessment” button.

Below is an example of the results of the assessment.  There were a few things that were expected, especially the Agent jobs not being supported in Azure SQL Database.

Fortunately Microsoft has also included links to each of the topics that will allow us to gather more information easily.

Make sure you look at both groups of items, SQL Server feature parity and the Compatibility issues.  In this case not compatibility issues were found.

Now that we have completed an assessment and addressed all the blocking issues, we can now use the same tool to perform the migration.  As expected you will also have to provide a project name, a source and a destination type.  This will also include the connection information for the target.

The migration scope has three options.

      • Schema and data
      • Schema only
      • Data only

You just need to decide which is best for what you attempting to accomplish.  In this case, we will pick Schema and Data.

Once you provide all the information, you will be able to click the “Create” button. As part of the migration, there are six steps.  These are list in the image below.

Selecting a source if the obvious starting point for a migration.  Once you do, you will then have an opportunity to pick which database you would like to migrate.

It this is a new database in Azure SQL Database, make sure to click the “Create a new Azure SQL Database…” link.

When you do, make sure you click the Connect button so the list of databases refreshes.

The next step will allow you to identify which objects you would like to migrate.

In this case there are still a few issues that will need to be address, most due to encryption, so I will not migrate those objects.  The next step is to generate the scripts.  Once the scripts are generated, you can save it or copy it.  Note below you the DMA tool will also identify items with assessment issues.

At this point the database has been created in Azure SQL Database and all the objects have been created.  However, we have not reached the point of migrating the data yet.

In order to migrate that data, all we have to do is click the “Migrate Data” button.

We will now have the option to decide which tables we would like to have the data migrated as well.  As you can see below, we can see the tables listed.  This list include the number of rows and whether or not the table is ready to move or not.

Once you have confirmed the information, click the “Start Data Migration” button.  What I like about this part of the tool is that it tells me the progress of each table and the status of each migration of data.

Now were are done. Not only have the objects been migrated, but the data as well.

Both of these migration options take place on your computer, one with SSMS and the other the downloadable Data Migration Assistance.  In my opinion, I think they are both pretty easy to work with.  The third option is done in the Azure portal.  There is a server called Azure Data Migration Service.  There are a number of required steps to utilize this service.

Azure Data Migration Services

As the name implies, this is a service in the Azure Portal.  When looking at all the services, the Azure Database Migration Services can be found either by doing a search or looking in the Databases group.

Once you click the Azure Database Migration Service, you will see what is below.  However, if you already have one of this service type running, you will not have to go through this step again.

For this method, you will need to provide a service name, a location(region) as well as a Virtual Network.  Yes, you will need to create a virtual network. This will include the creation of a virtual NIC.

If you already have the service created you can go to the All Resources list in the portal and click that service. Once you do, you will see this.

The next step is to click the “New Migration Project” button.  At this point you will pick a data source and a target.

The options for Type of Activity are:

    • Schema Only migration
    • Offline data Migration – this is to be used for source databases that will NOT be changed during the migration.
    • Online data migration – For systems that will be modified during the migration
    • Create project only

If you chose the Online Data Migration type, Azure with provide you with a list of rules that must be followed in order for the migration to be successful.

Also, if you chose Online Data Migration, it will require a higher level of SKU.  You must have Premium, which I currently do not have.  I received this warning what I tried to use Online Data Migration.

Given this I changed the type of activity to Offline Data Migration.  You will have to complete several steps to be successful in the migration. These steps are fairly basic and expected.  Identify a source, a target, map to target database, configure migration options and a summary.

There are a number of network and security steps that must be completed prior to utilizing this service.  This is a good description of what is required, click here.

As expected the source and the target require the usual information. What is a bit different is the third step, mapping target databases.  Below is a screenshot of the mapping step that is on the above Microsoft site. As you can see below, you simple map the source databases that you would like to migrate to a destination database in your Azure SQL database portal.

The settings step is simple telling the service what tables you would like to migrate.  Below is also from Microsoft’s site.

Finally, once you have provided all the required information and you can then begin the migration.  Once you do, Azure will provide progress status that will look like this, also from Microsoft’s site.

It is always good to have options.  In this case we have three different options.  Two you can run from your computer and one that must be done in the Azure portal.  Personally, I like the options of running the migration through an on-prem computer.  They appear to be less involved and I don’t need to involve other teams, such as the network or security team.

When migrating databases to Azure SQL Database, it is important to complete an assessment on the database first using the Data Migration Assistant.  This will allow you to identify what your obstacles might be.  You can then address them before starting the migration.

Thank for stopping by my blog and I hope you learned something.

Creating an Azure SQL Database

As the industry moves more and more activity to the cloud, I thought it would be a good idea to put together a post on how to create an Azure SQL database.  There are a ton of advantages to using an Azure SQL database.  One of the biggest is that I do not have to purchase a SQL Server license for a single database.  This is my first post on any Azure product.  I am hoping to have more in the future.

First of all, lets go over what Azure is.  Azure is a collection of many different cloud based technologies that Microsoft offers.  To see the services that are being offered, once in your Azure portal just click the “All Services” option on the left side.

This will bring up many, if not all the services that you can utilize.  Just remember to be careful to not do more than needed on a production server.  It is very easy to take advantage of many of the great features of Azure. If you don’t monitor the cost, you could potentially have a very large bill at the end of the month.

Once you click on the services option you will see something similar to what you see below.

The red rectangle identifies where the SEARCH options can be found in Azure.  The green box is where the groups of services can be found, while the blue box represents the service.  There are many services to choose from.  You just need to identify what you need to pick the appropriate services.

When searching the services you can enter a value into the Search box and Azure will search as you type. If you select a group on the left and then do a search, it will only search that group. However, when you do searching in a group and the query returns no results and there a items in other categories you will see this message.  This is simply telling you that there is nothing in the current category that matches your search criteria, how ever there are other services that do.

Since this post is about Azure SQL Database, we will focus on that service.  There are several ways to get to the point of creating a database, this is one of them.  The blue box below is the group of services and the green box is the service, in this case, SQL databases.

Microsoft defines “SQL Database” as “Azure SQL Database is a general-purpose relational database-as-a-service (DBaaS) based on the latest stable version of Microsoft SQL Server Database Engine“.  Notice that the definition states that it is based on the latest stable version of SQL Server.  This means no SQL Server upgrades or patches, Microsoft takes care of those.

Once you click on “SQL Databases” Azure will take you to this page.  If there already SQL Databases, you will see them listed here.

Before we move on, I would also like to point out that there are several methods to reach this page.  Most notably is by using the menu item on the left.  Then you will need to create the database, you can click the “SQL Databases” option.  This will take you to the part of the portal that will allow you to create a database, as you see in the image above.

Let’s take a tour of what we are seeing.  Your interface should look like below.

This first thing I would like to point out is the Subscription.  Below you can see that the name of the subscription I am using is Pay-As-You-Go.  This is the default name you will see when you first create the subscription.  You can rename it if you like.  Be cautious when adding items to Azure, if you are not careful, you could end up with an unexpectedly large bill.  Just be aware of the potential cost before creating items in Azure.

You also have the ability to change the columns that you see in the list.  You simple click “Edit Columns”.  You can also click “Refresh” to see new items if they do not appear.

Once you click “Edit Column” you will see the form below.  This is very similar to most products that have the ability to change the columns in a list.  As you can see below you can move columns back and forth.

The list will identify all the SQL databases you currently have in the subscription.  As you can see, I currently do not have any databases.

Regions

Before going into how to create an Azure SQL Database, I thought I would take a moment to talk about what a Region is.  A region is nothing more than a geographic location of where you want you resources to reside.  It is also important to note that not all services are available in all regions.  Here is the link to Microsoft’s page on Regions.  Ideally you would like to have the resources in the closest region.  As part of the database creation process, you will need to place a few resources in regions.

Here is a map and a link of the current regions:

Creating the Database

Now that we have gone over how to get to the point where we can create a database, let’s create one.

When you first click the “Create Database” button you will see this.

The first bit of information you will need is which subscription you will be adding the database to.  In my case, I just have the one so Azure has defaulted to that one.

The next item is the Resource Group.  Let’s take a moment to get to know exactly what a resource group is.

Microsoft defines as Resource Group as:

“A container that holds related resources for an Azure solution. The resource group includes those resources that you want to manage as a group. You decide how to allocate resources to resource groups based on what makes the most sense for your organization”

Here a few rules around Resource Groups:

  • A resource can only exist in one Resource Group.
  • A resource can be moved to another Resource Group at any time
  • A resource in one group can interact with a resource in another group
  • A Resource Group can contain resources from different regions
  • Ideally, the Resource Group and the resources should be placed in the same region

The link above to Microsoft has the rules listed.

Now that we have gone over regions and Resource Groups, it is time to create the database.  The first thing we need to do it give the database a name.  Just as in an On-Prem SQL Server, there are rules around how you name your database.  What is nice is that Azure will pop up and tell you if you are violating one of the rules.  Just as with an On-Prem instance, the rules are pretty straight forward.

If you violate one of the rules, Azure will tell you. Just as seen below.

Now that we have provided a name for the database, the next step is to assign it to a server.

If you have not created a server yet, you can simply click the “Create New” link.  A server for Azure SQL Database is a logical container for your SQL Databases and provides a centralized point for administration.  Here is a link to Microsoft’s documentation on Server.

When creating a server, you will also need to create an account that will be used as the administrator account.  You will also need to provide a password and a region.  When providing a name, the name cannot be the same as the database. Notice that under the server name, you will see, “.database.windows.net”.  This will be important information you will need when connecting to your database with SQL Server Management Studio.

Once you have click the “Select” button and filled out all the information, it may look like you are done, but you are not.  We need to provide additional information.  By clicking on the “Additional Setting” item towards the bottom we will see the what else we need to provide.

The first item we need to address is the Data Source.  Here you have three options.

  • Blank
  • Restore from Backup
  • Sample

There is only one option at this point for the sample database, AdventureWorksLT.  If you chose None, this is simply a blank database and you will need to review the Collation to see if it appropriate for your needs.

The next item you will look to look at are the tags.  Tags are not required, but can be useful when grouping your databases.  For example, if you have a system that uses more than one database, you can take them with the name of the system.  Just a note, keep in mind cross database queries are not allowed in Azure SQL Database.

Now that we have provided all the required information it is time to review before creating the database. You will want to pay close attention to the Product Details section.  Notice that it has an estimated cost per month.

The rest of the options are pretty straight forward, however should be carefully be reviewed before creating the database.

Once you click “Create” you will see this.  If you look in the upper right corner of the image, you will see something that looks kind of like a bell.  This is the notifications and you will see the blue line under it move from the left to the right while the deployment is in progress.

Once the deployment is complete, Azure will tell you as seen below.

Now that we have created the database, we can confirm it by returning to the list we talked about earlier.  Just click the SQL Database menu item on the left and you will be returned here.

Notice that our new database is listed.

I would also like to point out the Tags. You will see this list along the top.

If you click the drop down arrow you will see this and the tags that was created. By clicking the box next to Adventwork, the list will only show items that have a tag of Adventwork.

Connecting to the Database

Although this post was mostly about how to create an Azure SQL Database, I would also like to go over how to connect to your newly created database. If you click the database name in the list, you will be taken to a page that will list all the important information about your database.  This includes the subscription name, database name, status, location as well as a number of other usefully bits of information.

However, in order to connect there is one thing we need to copy, that is the Server Name.  In my case, blogdbsvr.database.windows.net.  If you move your mouse just to the right of it, you will be able to copy it to the clipboard.

Once on the clipboard, it is time to open SSMS. You will connect just as if you were connecting to an On-Prem SQL Server using SQL Authentication.

Once you enter a user name and password, you click Connect.  This is where you will begin to see a few differences.  You will be asked to login into your Azure account as well as a few screens about your IP address.

Once connected you will see a few significant differences from an On-Prem instance of SQL Server. Notice that the SQL Server agent is no longer present. As a DBA, this is a huge difference because so much of our on going maintenance is dependent on the Agent.  No worries there, Microsoft has provide a number of ways for us to schedule maintenance jobs.

Below you will see many more differences.

Listing all the differences is outside the purpose of the post, however there is a lot of really good information available.  I may cover some of these differences in future blog posts.  Once you are in SSMS, you can do many of the same things you have done with an on-prem instance.  Including creating objects, writing queries and working with security.  Please keep in mind that there are some differences on how security is set up, however that is for another time.

Thanks for reading the long post on how to create an Azure SQL Database.  This is the longest blog post I have done so far.  Hopefully you found it useful.