Shulou(Shulou.com)05/31 Report--

This article shows you how to migrate SQL Server database to Azure SQL, the content is concise and easy to understand, it will definitely brighten your eyes. I hope you can get something through the detailed introduction of this article.

The version of Azure SQL

Azure SQL Database is a SQL service (PaaS) provided by Microsoft. The latest version is called Azure SQL Database V12, but Microsoft still provides database services through SQL Server 2014:

The first database server in the figure above is a locally installed SQL Server 2014, and the second and third are Azure SQL Database on the cloud. We can see very clearly that their versions are the same.

But don't assume that the database provided by Azure SQL Database is the same as the local installation version. There are a lot of differences between them, which is especially important when migrating existing databases.

Because it provides online services, Azure SQL Database can quickly release new features, as can be seen in the constantly updated MSDN documents. MS also strongly recommends that we use the latest version of the tool when dealing with Azure SQL Database. At the beginning of using SSMS (SQL Server Management Studio) in SQL Server 2014, the author found that the information displayed did not match the Azure portal after connecting to Azure SQL, and the problem disappeared after installing the latest version of SSMS.

Let's get down to business and take a look at what tools are needed in the process of migration, how to operate them, and what to pay attention to. It is particularly emphasized here that the old database is generally in use, so never do all kinds of experiments on the real database. All of the author's previous experiments were done on the test library created by restoring backup files.

Migration essentials Analysis of creating Azure SQL Server in the Cloud

Azure SQL Database runs in Azure SQL Server, so we need to create Azure SQL Server on Azure first. The operation process is relatively simple, just add SQL Server (logical server) to the Azure, please pay attention to select the appropriate area (this will affect the access speed).

Allow local access to Azure SQL Server

After the Azure SQL Server is created, let's test the connection through SSMS. When we enter the correct address and user information, a prompt box pops up:

It prompts us that the current IP cannot access the database server on Azure and lets me log in as an Azure account and create a firewall rule.

In fact, this is a security measure provided by Azure, which allows you to explicitly specify which IP addresses or IP segments can access Azure SQL Server.

At this point, we have two solutions:

1. Click "Sign in" in the dialog box and log in with your Azure account; then click "OK" when you have finished setting the firewall rules and SSMS has logged in to Azure SQL Server. This approach is generally used for development and testing and can only add the IP currently used by the client.

two。 A more general method is to log in to Azure portal, enter the configuration interface of Azure SQL Server, and add rules to the firewall. Similarly, you can add a single IP or one IP address range at a time:

Compatibility processing

As there are many versions of MS SQL Server, and there are differences between the cloud version and the local version, whether the migration can be successful depends on whether the compatibility between databases can be found and solved.

The following will introduce the compatibility problems encountered by the author in detail.

The user set in the compatibility processing details database does not exist

The compatibility check report shows the following information:

Error SQL71564: Error validating element [xxxx]: The element [xxxx] has been orphaned from its login and cannot be deployed.

Where xxxx is the user name set in the database.

The reason for this error is that the user is defined in the local SQL Server. Once the user's information is used in the database and the database is migrated to the cloud, the corresponding user's definition cannot be found, so the local user's information needs to be removed.

Don't worry about database access, because after the migration, you can use the Azure SQL Server account you just created to access the database. Of course, you can also create a separate access account for a database. For more information, please see MSDN.

Extended Property is not supported

The compatibility check report shows the following information:

One or more unsupported elements were found in the schema used as part of a data package.Error SQL71564: The element Extended Property: [dbo]. [xxxx]. [Ms _ Description] is not supported when used as part of a data package (.bacpac file).

Where xxxx is the name of a table in the database.

This can be troublesome, do not support Extended Property! This feature is used in several places in the author's database. What shall I do? I have to check the program over and over again. In the end, it was found that this feature was not used in the program, as if someone had just used it to make some instructions at that time. Fortunately, the final conclusion can be removed.

Create clustered index

The compatibility check report shows the following information:

One or more unsupported elements were found in the schema used as part of a data package.Error SQL71564: Table Table: [dbo]. [xxxx] does not have a clustered index. Clustered indexes are required for inserting data in this version of SQL Server.

Where xxxx is the name of a table in the database.

You need to create a clustered index for the table, which is no small matter, because any change to the table may affect the program logic. What should I do? Friends on the Internet have long had a more reliable solution, which is to add a column to the table for clustered index, so that the columns in the original table remain unchanged:

ALTER TABLE [xxxx] ADDRowId int NOT NULL IDENTITY (1,1) PRIMARY KEY CLUSTEREDGO other

There are some other points, mainly related to business, which will not be repeated here. I feel that most of the problems have different solutions on the Internet, and the key is to solve the problem in a way that is acceptable to our business.

Next, write all the changes to the database as a script file, and in the formal migration, execute the script file directly on the official library.

Migration process

MS provides different tools for compatibility checking, migration, and so on. We all use SSMS (SQL Server Management Studio) here.

Let's take a look at the specific steps.

Right-click the database to be migrated in SSMS, and select "Deploy Database to Microsoft Azure SQL Database …" in Tasks. .

Click "next" in the wizard that opens to enter the "Deployment Settings" interface.

First of all, you need to set the connection address and connection account of Azure SQL Server:

Next, set the database name and resource configuration after migration:

Notice that Azure SQL Database settings,MS divides the resources used by the database into three different categories: Basic, Standard, and Premium. There are different rates in each category, which simply means that you have to pay more if you want to use more and better resources. Of course, on the other hand, if I don't use a lot of resources, a little money will be enough!

We find that the last line in the figure above requires us to specify a storage path for the * .bacpac file. * .bacpac file is an intermediate file generated during migration. When the compatibility check is passed, all the contents in the database are exported to this file. From this information, we can see that no matter which migration method is used, the core operation is two steps: first generate a * .bacpac file from the local database, and then restore an Azure SQL Database from the * .bacpac file.

Click "Next" to display the details of the configuration, and then start the compatibility check next. If there are no compatibility issues, the migration operation is performed.

There were some compatibility issues with my database, so an error report was displayed and the migration operation was terminated:

Click the link in the "Result" column to see the detailed report. We have already introduced the compatibility issue, directly execute our script file to deal with the compatibility issue, and then try again!

There is no error prompt for this execution, in fact, the migration process has already begun in the background. What is more inconvenient is that there is no detailed progress prompt for this process, so we can only wait patiently. My empirical data is that the migration of 8G libraries takes about 8-12 hours. Of course, this has a lot to do with the bandwidth of your connection to Azure.

The above content is how to migrate SQL Server database to Azure SQL. Have you learned any knowledge or skills? If you want to learn more skills or enrich your knowledge reserve, you are welcome to follow the industry information channel.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.