I have migrated my existing SQL server database to MySQL server database using MySQL workbench Migration Wizard. Because these are two different database servers, I want to ensure there is no data loss along with stored procedures, triggers, and views, I mean everything is intact. I tried using the MySQL workbench Compare Schema wizard but that only works for two MySQL databases. Please suggest a way to achieve it.
CodePudding user response：
First you should compare database schemas between SQL Server and MySQL to see if there is difference.
I don't think that field are missing, but perharps it needs data type adjustments, index adjestments, etc.
Second, once you fix database schema, you should verify imported datas, including:
- total row number for each tables
- Row contents
For both, the best is to write a verification script (PHP, node.js, python, etc.) that will list all tables of SQL Server, and for each table check row number, identity counter and then datas itself.
CodePudding user response：
For moving or copying the MS SQL database table's schema into MySQL, you must map data types, find NULL constraint, and determine the field that is set as a PRIMARY KEY.
This procedure does not support conversion of indexes, foreign keys, identity columns, unique or other table constraints, and character set.
MySQL supports all the important MS SQL data types. However, there are some SQL server data types that do not match with MySQL data types. Some of the major data types you’ll need to map MySQL with are as follows:
| SQL Server | MySQL |
|---|---|
| VARCHAR(max) | LONGTEXT |
| SQL_VARIANT | BLOB |
| IDENTITY | AUTO_INCREMENT |
| AUTO_INCREMENT | TEXT CHARACTER SET UTF8 |
| SMALLDATETIME | DATETIME |
| DATETIMEOFFSET | TIMESTAMP |
| MONEY | DECIMAL(19,4) |
| UNIQUEIDENTIFIER | BINARY(16) |
| SYSNAME | CHAR(256) |
Organizations may develop the need to migrate from MS SQL server to MySQL because of its rich feature-set, cross-platform and open source availability, and lower cost.
While migration from one database to another can be performed manually, it can be an extremely time-consuming and error-prone process.
A better alternative is to use specialized database converter software like Stellar Converter for Database, which is specially designed to help DBAs and developers automate the process of converting a database file format to another. The software converts table records and attributes from MS SQL to MySQL database quickly, while preserving database integrity.