Migrating a SQL Server 2014 DB to OCI MySQL Database Service
In this tutorial I’m migrating a SQL Server 2014 DB to OCI MySQL Database Service.
And Why Migrate?
Well, you can think in some factors like Costs/Performance or just moving all your workloads to OCI and you don’t wanna leave it on a SQL Server installed on top of a Compute VM.
To our testes here, I’m using a small DB as a template, of course for huge/complex DB, this gonna need more attention to change some configuration/dependences and extra configuration manually. The idea is to show that’s possible to do, basically moving data from on side to other.
Datatypes Requiring Conversion
Be aware that you may need to do some mapping when you migrate from SQL Server to MySQL:
So, let’s start !
For this Lab you gonna need:
- SQL Server 2014 (my lab)
- MySQL Workbench 8
- OCI MySQL Database Service (MySQL Version: 8.0.22-u4-cloud)
- You must use the “sa” on SQL Server user or equivalent
- On MySQL created a user “oracle” as the “sa” for SQL Server
- Connectivity between the Workbench and both Databases (via VPN or Tunnel SSH)
Installing the MySQL Workbench is quite simple, just follow the instructions and start it.
Once you got it installed, let’s configure it:
1) Go to Database -> Migration Wizard
2) In the main Migration Wizard page, go to “Open ODBC Administrator”, in my case, I had to configure it from scratch.
3) Then, under System DSN tab, click em Add…
4) Give a name of your choice for the connector and complete the other fields, in my lab I’m using a bastion server as “jump server”, when this tutorial was created, OCI MySQL does not allow external IP’s, so, you can use a VPN if you like.
5) Select the connection parameter which better works for you. In our lab, I’m using the SQL Server authentication via login ID and password:
6) Select the Database that you want to migrate, in our lab, AdventureWorks2014, click Next.
7) In this step, if you don’t have to change anything, just keep Next.
8) The ODBC data source creation is completed. You can check the connectivity in the Test Data Source option.
9) Click on Test Data Source… Connection working well as expected.
10) So, Back to the main menu, click on Start Migration on the bottom page.
11) Select the Database System, Connection Method and DSN driver as below:
- Microsoft SQL Server
- ODBC Data Source
- DSN: the name you created the odbc driver before, sql2014 in this lab.
12) All completed, you can test your configuration clicking on Test Connection, if all good, click Next.
13) Same idea of the previous step, now, let’s configure your access to MySQL Database on OCI!
14) Test your connectivity with your user/password for the MySQL Database
15) So, all sorted, then click Next and you will see this validation page:
16) In this screen, select your Database to be migrated, you can migrate everything or be selective.
17) Once the analysis is done, click in Next.
18) On this Lab, we have Table Objects, View Objects and Routine Objects. We’ll only select the Table Objects because for the rest of the object we should check the corresponding MySQL equivalent code manually. Click Next if it’s okay for you.
19) In this step, the objects from the Source DB (SQL Server) are converted into MySQL compatible objects. Next.
20) The system found 47 warnings, that can be fixed before the migration (typemappings).
21) So, we can continue by selecting how we want to create the migrated schema in the target. We’ll use the default “Create schema in target RDBMS” option. Next.
22) Now we can check the results of the script execution on the Target MySQL DB.
23) So, let’s look into the MySQL Database on OCI, let’s check it out:
24) Now have the database structure, but we don’t have the data yet. Now, we’ll select how we want to copy the data in the MySQL Server. We’ll use the “Online copy of table data to target RDBMS” option. Next.
25) Data being copied via Bulk Data Transfer service.
26) And if everything goes well, you start to see your Data on the OCI MySQL DB….
Well, that’s it! Of course you will have to do all post-migration checks and etc, but, the intention here was to show that is something you can do… To be honest, I’m curious to see how would an application consume the data after migrated.
Some other following items will need special attention when migrating:
- Assemblies
- Types
- DDL and statement-based triggers (MySQL has row-based triggers)
- Proprietary SQL Server function calls
- Certain cases of dynamic T-SQL
In the same way, Synonyms and Security Roles will need a workaround as they cannot be directly migrated into MySQL.
