MySQL is a common repository server, and the cloud-based Azure SQL Database on Microsoft Azure is a powerful and affordable option, through this article, using the SQL Server migration Assistant, which is Here's a simple: SSMA) take a few simple steps to move your MySQL repository to Microsoft SQL Server or Azure SQL Database.
SQL Server move small help hand
SSMA supports multiple structures of repositories (Sybase, Oracle, MySQL) to quickly move to Azure SQL Database or Microsoft SQL Server. It will move the main steps of the repository, such as the transformation of the structure (SCHEMA), SQL statement conversion, transfer of the information form, and so on, to reduce the time and risk of moving from different repositories to Azure SQL Database or Microsoft SQL Server. SSMA currently offers several versions:
- Support for Oracle's Microsoft SQL Server to move small help hand (Version 6.0)
Microsoft SQL Server Migration Assistant v6.0 for Oracle
- Microsoft SQL Server support for MySQL to move small help hand (Version 6.0)
Microsoft SQL Server Migration Assistant v6.0 for MySQL
- Support for Sybase's Microsoft SQL Server to move small help hand (Version 6.0)
Microsoft SQL Server Migration Assistant v6.0 for Sybase
- Support for Microsoft SQL Server for Access to move small help hand (Version 6.0)
Microsoft SQL Server Migration Assistant v6.0 for Access
For installation steps, please refer to SQL Server migration Assistant Team's Blog
Move the MySQL repository to Microsoft SQL Server step
1. Download and install Microsoft SQL Server migration Assistant for MySQL
2. Open Microsoft SQL Server migration Assistant for MySQL
Then click File to add a new Repository object (object).
In the new object's conversation block, you will be asked to enter the name of the object and the version of Microsoft SQL Server or Azure SQL Database to which you want to move the MySQL repository. In this example, we chose to move the MySQL repository to Microsoft SQL Server Express Edition.
Select "OK", and the new repository object will be set up.
Note: If your current version of SQL Server is an older version (for example, SQL Server 2008), your library object Transfer option is not high enough for this version.
3. Establish MySQL Information Warehouse
Select "Connect to MySql" in the upper left corner
Input MySQL server name, link the message port, user name and password
- To connect MySQL, you also need to install MYSQL-CONNECTOR-ODBC (version 5.1 or above), if not previously downloaded, in the previous page will be reminded to download MySQL ODBC, you can go to http://dev.mysql.com/ downloads/connector/odbc/ Download Installation
- MYSQL-CONNECTOR-ODBC can't connect MySQL 4.0 with older versions of MySQL
After you have finished, click on the "Connect" button and then follow the image below. The MySQL Metadata Explorer on the top left shows the MySQL repository (world) we want to transfer, and the SQL Server migration Assistant has been successfully connected to MySQL at the bottom of the output.
On the right is the option to let us set the Type, Schema, and so on.
4. Connect to Microsoft SQL Server .
Select "Connect to SQL Server" in the top left corner
The server name of the SQL Server, the name of the target repository and the user account are then entered.
After entering, you will see the following warning messages.
There are several reasons why SQL Server Express R2 does not provide a SQL Agent, but this does not affect the result of the transition, which is chosen to continue.
If the repository you are entering does not exist in SQL Server, you will be prompted to create one.
As in sync 31, on the bottom toolbar, you can see that SQL Server Migration Assistant has been successfully connected to the target SQL Server
5. Conversion Structure Description (Convert Schema)
Currently SQL Server Migration Assistant has been connected to MySQL and SQL Server, and then we want to transfer the structure description, the linked fields bit, the linked fields bit type, the key (primary key) in the MySQL repository, the external keys (foreign Key) and other structures to be used in SQL Server.
Select the MySQL repository you want to switch to, and select the "Convert Schema" in the Upper toolbar.
After the conversion, we can see that SQL Server has the same Schema as the MySQL repository.
6. synchronization (Synchronize)
The steps above have already transformed the MySQL table and Schema to SQL Server. Next step, we'll use SSMA to synchronize SQL Server with the Repository object.
In the SQL Server repository, click the right mouse button and select "Synchronize with Database"
At the bottom of the output, you can see that the synchronization is complete.
7. will be MySQL the information is shifted to SQL Server
The last step is to transfer all the information in the MySQL repository to SQL Server.
Select "Migrate Data" on the tool list
After the transfer of the information, you can see the information transferred from the information Transfer report.
From the bottom, you can see that the information was successfully moved from the MySQL repository to the SQL sever
Move the MySQL repository to Azure SQL Database
The steps to move the MySQL repository to Azure SQL Database are quite similar to the ones described above, but there are some differences between building the repository object and establishing the connection.
1. Click File to add a new Repository object (object).
The difference between this and the above is that in the dialog box for creating objects, we want to select the transferred database "SQL Azure" (Azure SQL Database name)
When the Repository object is built and connected to the MySQL Database (step 3 above), we're going to build a link to the Azure SQL project.
2. First Create an azure SQL Database on Microsoft Azure.
Please refer to this form
3. Once azure SQL Database is established, we enter Azure Management page and select "SQL Library"
4. In this service, you can see all Azure SQL Database in the subscribed account.
After clicking on the transfer target, you can see the server name of Azure SQL database in the lower right corner of the dashboard, which is the name of the server to be entered in the next step to build the Ssma and Azure SQL database.
5. As in step 1, it has been told that SSMA is going to move the target for Azure SQL Database, and therefore the tool list also differs from the above.
Select "Connect to SQL Azure"
Here you need to enter step 4 of the server name, server account password, target information library name
After the build is complete, you can see in the left-hand SQL Azure Metadata Explorer window that SSMA has completed the connection with your Azure SQL Database.
6. After the Ssma and Azure SQL Database are built, the rest of the motion includes the following: The conversion structure description, synchronization, transfer information and so on are the same.
The following images have been successfully moved to Azure SQL Database
7. After finishing the last step, through the Microsoft Azure Management Web site, we can directly use the SQL Database Management Portal to manage the repository, before we need to get access to the repository.
Enter the management page of Azure SQL Database and select Manage at the bottom of the toolbar.
At this point, you will be asked if you want to add your current IP address to the firewall rules, select "Yes", and Azure will automatically add your IP to the rules, so that you can enter Azure SQL Database Management page.
You can also add your IP address to the IP range of the allowed access server through the Server Management page
8. Once you have administrative authority, you can use SQL Database Management Portal to import into the repository for management.
Input server user name and password (created earlier in the new step)
Through the Azure SQL database management interface, you can see that the MySQL repository Schema and information has been successfully transferred to Azure SQL database.
This article was originally published in "microsoft Azure Chinese blog"
How to move the MySQL repository to Microsoft SQL Server and Azure SQL Database