{"id":36,"date":"2015-04-24T20:04:15","date_gmt":"2015-04-24T20:04:15","guid":{"rendered":"http:\/\/www.wellcode.de\/home\/?page_id=36"},"modified":"2021-11-05T01:21:03","modified_gmt":"2021-11-05T01:21:03","slug":"migrate-mysql-to-sql-server","status":"publish","type":"page","link":"https:\/\/wellcode.de\/home\/index.php\/en\/migrate-mysql-to-sql-server\/","title":{"rendered":"Migrate MySQL To SQL Server"},"content":{"rendered":"\n<p>This page&nbsp;illustrates the strongness of the wellcode dbms tool and shows how to&nbsp;migrate a MySQL database to MS SQL Server including indexes and constraints.&nbsp;The Example uses the dbms tool to generate and create the schema and a part of WellFrame library to dynamically&nbsp;import data.<\/p>\n\n\n\n<ol class=\"wp-block-list\"><li><strong><strong>Conditions to start<\/strong><\/strong>\n<ul>\n<li>MySQL ODBC Driver or MySQL.Net&nbsp;are installed on your machine<\/li>\n<li>An empty database on MS SQL Server is created and is ready for import.<\/li>\n<\/ul>\n<\/li><li><b>Start the migration<\/b>\n<ul>\n<li>Download the migrate tool example from <a href=\"\/downloads\/DbmsConverter.zip\">here<\/a><\/li>\n<li><span style=\"line-height: 1.6471;\">run the executable and enter the connection strings for <\/span>\n<ul>\n<li><span style=\"line-height: 1.6471;\">MySQL database and <\/span><\/li>\n<li><span style=\"line-height: 1.6471;\"><span style=\"line-height: 1.6471;\">the destination MS SQL Server databases.<\/span><\/span><\/li>\n<\/ul>\n<\/li>\n<li>Test the connections and start the migration<\/li>\n<\/ul>\n<\/li><\/ol>\n\n\n\n<p>To download the example project including all dependencies click <a href=\"\/downloads\/DbmsConverter.zip\">here<\/a>. The Visual Studio project uses .Net Framework 4.5.<\/p>\n\n\n\n<p>Please keep in mind, that the odbc driver has to be the same binary bit number as the programm it self. If you use 64bit driver, you must run the 46bit copy&nbsp;of import tool. To import from a 32bit mysql driver use the 32bit executables (see bin64 and bin32 directories in the example).<\/p>\n\n\n\n<p>You can avoid using the ODBC Driver by changing the source code and replacing &#8220;<em>OdbConnection<\/em>&#8221; with the .Net Connection&nbsp;of MySQL Driver &#8220;<em>MySqlConnection<\/em>&#8220;.<\/p>\n\n\n\n<figure class=\"wp-block-image\"><a href=\"\/home\/wp-content\/uploads\/DbmsConverterDialog.png\"><img loading=\"lazy\" decoding=\"async\" width=\"595\" height=\"505\" src=\"\/home\/wp-content\/uploads\/DbmsConverterDialog.png\" alt=\"DbmsConverterDialog\" class=\"wp-image-97\"\/><\/a><\/figure>\n\n\n\n<p>Here is&nbsp;the migrating source code for illustration:<\/p>\n\n\n\n<pre class=\"wp-block-preformatted lang:c# decode:true\">using Wellcode.Dbms;\nusing System;\nusing System.Collections.Generic;\nusing System.Data;\nusing System.Data.Odbc;\nusing System.Data.SqlClient;\nusing System.Linq;\nusing System.Text;\nusing System.Threading.Tasks;\nusing Wellcode.Basics;\nusing Wellcode.Frame;\nusing System.Data.Common;\n\nnamespace Wellcode.DbmsConverter\n{\n    public class MySQLToMSSQLExporter\n    {\n        public delegate void ActionEventHandler(MySQLToMSSQLExporter sender, RdbmsActionEventArgs e);\n        public event ActionEventHandler Action;\n\n        public string MySQLConnectionString = \"DSN=mysql\";\n        public bool MySQLOverODBC = true;\n        public bool MySQLOverNet = false;\n        public bool SchemaOnly = false;\n        public string MsConnectionString = \"\";\n        public string SchemaFileName = \"mysql-schema.xml\";\n\n        protected DynManager Manager = new DynManager();\n\n        public MySQLToMSSQLExporter(string mySqlCn, string msCn)\n        {\n            this.MySQLConnectionString = mySqlCn;\n            this.MsConnectionString = msCn;\n        }\n        \n        public bool Migrate(ref string errorString)\n        {\n            try\n            {\n                var my = new OdbcConnector(this.MySQLConnectionString);\n                var ms = new DbConnector(this.MsConnectionString, TransactionLevel.NoTransaction);\n\n                \/\/ create the basic schema definition from mysql database \n                \/\/ (blank table\/columns definitions)\n                FireAction(\"Creating Schema ...\");\n                var mydbms = new MySQLSchemaReader().CreateSchema(my, true);\n\n                \/\/ apply schema to destination ms sql database\n                FireAction(\"transfer schema to the destination database ...\");\n                var controller = new RdbmsController();\n                controller.Model = mydbms;\n                \/\/controller.SetSchemaFile(this.SchemaFileName);\n                controller.ConnectionString = this.MsConnectionString;\n                controller.Action += controller_Action;\n                controller.Upgrade(UpgradeMode.Upgrade);\n\n                FactoryDbms.SetStandardModel(mydbms); \/\/ prevent reading dbms model from database\n\n                \/\/ import all tables with columns\n                for (int j = 0; false == this.SchemaOnly &amp;&amp; j &lt; mydbms.Tables.Count &amp;&amp; mydbms.Tables[j].Columns.Count &gt; 0; j++)\n                {\n                    var table = mydbms.Tables[j];\n\n                    FireAction(\"Transfer Data to table \" + table.Name + \" ...\");\n\n                    \/\/ delete all existing contents\n                    var cmd = ms.CreateCommand(\"DELETE FROM \" + table.Name);\n                    cmd.ExecuteNonQuery();\n\n                    \/\/ use the entity model to import data\n                    DynFactory t = new DynFactory(this.Manager);\n                    t.IsChangeLogEnabled = false;\n                    t.TableName = table.Name;\n\n                    \/\/ dummy primary key\n                    t.Fields = new string[1];\n                    t.Fields[0] = table.Columns[0].Name;\n                    \n                    var ds = my.GetResult(\"SELECT * FROM \" + table.Name);\n                    var cnt = ds.Rows.Count;\n                    var num = 0;\n                    foreach (DataRow r in ds.Rows)\n                    {\n                        var entity = new WebEntity();\n                        entity.AquireEntityManager += entity_AquireEntityManager;\n                        for (int i = 0; i &lt; ds.Columns.Count; i++)\n                        {\n                            var o = r[i];\n                            if (DBNull.Value.Equals(o))\n                                o = null;\n                            entity.Add(ds.Columns[i].ColumnName, o, ds.Columns[i].DataType);\n                        }\n\n                        \/\/ write to ms sql database and protocoll errors\n                        try\n                        {\n                            t.SaveEntity(ms, entity);\n                        }\n                        catch (Exception e)\n                        {\n                            FireAction(\"Error: \" + e.ToString());\n                        }\n                        num++;\n                        if (num % 100 == 0)\n                        {\n                            FireAction(string.Format(\"{0}: {1} \/ {2} imported...\\r\\n{3} von {4} tables imported\", table.Name, num, cnt, j, mydbms.Tables.Count));\n                        }\n                    }\n                }\n\n                FireAction(\"Reset the assigned model for future usage.\");\n                FactoryDbms.ResetModel();\n\n                FireAction(\"Import done.\");\n\n                \/\/ create foreigh keys now and rest of dbms structure now\n                FireAction(\"create foreign keys after import data ....\");\n\n                mydbms = new MySQLSchemaReader().CreateSchema(my, false);\n                controller.Model = mydbms;\n                controller.Action -= controller_Action;\n                controller.Upgrade(UpgradeMode.Upgrade);\n\n                return true;\n            }\n            catch (Exception ex)\n            {\n                errorString = ex.ToString();\n            }\n            return false;\n        }\n\n        void entity_AquireEntityManager(WebEntity sender, EntityManagerEventArgs e)\n        {\n            e.Manager = this.Manager;\n        }\n\n        void controller_Action(RdbmsController sender, RdbmsActionEventArgs e)\n        {\n            if (e.Action == ActionMode.Init)\n                e.Wrapper.UpgradeForeignKeys = false;\n        }\n\n        void FireAction(string action)\n        {\n            if (this.Action == null)\n                return;\n\n            var e = new RdbmsActionEventArgs();\n            e.Comments = action;\n            this.Action(this, e);\n        }\n\n\n        public bool TestMySQLConnectionString(ref string errorString)\n        {\n            return this.TestConnectionString&lt;OdbcConnection&gt;(this.MySQLConnectionString, ref errorString);\n        }\n\n        public bool TestMsConnectionString(ref string errorString)\n        {\n            return this.TestConnectionString&lt;SqlConnection&gt;(this.MsConnectionString, ref errorString);\n        }\n\n        protected bool TestConnectionString&lt;Vendor&gt;(string connStr, ref string errorString)\n            where Vendor : DbConnection, new() \n        {\n            try\n            {\n                var cn = new Vendor();\n                cn.ConnectionString = connStr;\n                cn.Open();\n                cn.Close();\n\n                return true;\n            }\n            catch (Exception ex)\n            {\n                errorString = ex.ToString();\n            }\n            return false;\n        }\n    }\n\n    public class MySQLSchemaReader\n    {\n        \/\/\/ &lt;summary&gt;\n        \/\/\/ \n        \/\/\/ &lt;\/summary&gt;\n        \/\/\/ &lt;param name=\"my\"&gt;&lt;\/param&gt;\n        \/\/\/ &lt;param name=\"basicSchema\"&gt;Creates schema without indexes, constraints, identities, etc.&lt;\/param&gt;\n        \/\/\/ &lt;returns&gt;&lt;\/returns&gt;\n        public DatabaseModel CreateSchema(OdbcConnector my, bool basicSchema)\n        {\n            \/\/ the datamodel of mySQL database\n            var mydbms = new Wellcode.Dbms.DatabaseModel();\n\n            var myDbs = my.GetResult(\"SELECT database() AS SCHEMANAME\");\n            var myDbName = myDbs.Rows[0][0].ToString();\n\n            var tables = my.GetResult(\"SHOW TABLES\");\n            foreach (DataRow row in tables.Rows)\n            {\n                var table = new Wellcode.Dbms.Table();\n                table.Name = (string)row[0];\n                mydbms.Tables.Add(table);\n\n                \/\/ add columns to the table\n                var columns = my.GetResult(\"SHOW COLUMNS FROM \" + table.Name);\n                foreach (DataRow myCol in columns.Rows)\n                {\n                    var msCol = new Wellcode.Dbms.Column();\n\n                    msCol.Name = (string)myCol[\"Field\"];\n                    SetColumnType((string)myCol[\"Type\"], msCol);\n                    msCol.IS_NULLABLE = myCol[\"Null\"].ToString();\n\n                    \/\/ default value\n                    var defvalue = myCol[\"Default\"].ToString();\n                    if (false == basicSchema &amp;&amp; !string.IsNullOrEmpty(defvalue) &amp;&amp; Zeichen.Ne(defvalue, \"NULL\"))\n                    {\n                        msCol.COLUMN_DEFAULT = \"'\" + defvalue + \"'\";\n                        msCol.DEFAULT_CONSTRAINT_NAME = \"DF_\" + table.Name + \"_\" + msCol.Name;\n                    }\n\n                    \/\/ identity field\n                    if (false == basicSchema &amp;&amp; Zeichen.Eq(myCol[\"Extra\"].ToString(), \"auto_increment\"))\n                    {\n                        msCol.IS_IDENTITY = true;\n                        msCol.INCREMENT_VALUE = 1;\n                        msCol.SEED_VALUE = 1;\n                        msCol.LAST_VALUE = 1;\n\n                        \/\/ auto-inkrement doesn't have default values\n                        msCol.COLUMN_DEFAULT = null;\n                        msCol.DEFAULT_CONSTRAINT_NAME = null;\n                    }\n                    table.Columns.Add(msCol);\n                }\n\n                \/\/ when creatring basic data, ignore rest\n                if (basicSchema)\n                {\n                    continue;\n                }\n\n                \/\/ add indexes to table\n                var indexes = my.GetResult(\"SHOW INDEXES FROM \" + table.Name);\n                foreach (DataRow myIdx in indexes.Rows)\n                {\n                    var myKey = myIdx[\"Key_name\"].ToString();\n\n                    var idx = table.GetConstraint(myKey);\n\n                    var isPrimary = false;\n                    if (idx == null &amp;&amp; Zeichen.Eq(myKey, \"PRIMARY\"))\n                    {\n                        if (table.PrimaryKey == null)\n                        {\n                            table.PrimaryKey = new Wellcode.Dbms.TableConstraint();\n                            table.PrimaryKey.Name = \"PK_\" + table.Name;\n                        }\n                        idx = table.PrimaryKey;\n                        isPrimary = true;\n                    }\n                    else if (idx == null)\n                    {\n                        idx = new Wellcode.Dbms.Index();\n                        idx.Name = myKey;\n                    }\n                    var col = new Wellcode.Dbms.ConstraintColumn();\n                    col.Name = myIdx[\"Column_name\"].ToString();\n                    idx.Columns.Add(col);\n\n                    if (isPrimary) \/\/ make sure, that primary key fields are not null\n                    {\n                        var tcol = table.GetColumn(col.Name);\n                        if (tcol != null)\n                            tcol.IS_NULLABLE = \"NO\";\n                    }\n                }\n\n                \/\/ foreign constraints\n                var myForeigns = my.GetResult(string.Format(@\"\n                    select ku.constraint_schema, ku.constraint_name, ku.TABLE_NAME, ku.COLUMN_NAME\n                         , ku.REFERENCED_TABLE_NAME, ku.REFERENCED_COLUMN_NAME, cs.DELETE_RULE, cs.UPDATE_RULE\n                      from information_schema.key_column_usage ku\n                      left join information_schema.referential_constraints cs  \n                        on ku.table_name = cs.table_name \n                       and ku.constraint_name = cs.constraint_name\n                       and ku.constraint_schema = cs.constraint_schema\n                     WHERE ku.REFERENCED_TABLE_NAME IS NOT NULL \n                       AND ku.TABLE_NAME = '{0}'\n                       AND ku.constraint_schema = '{1}'\", table.Name, myDbName));\n                foreach (DataRow myForeign in myForeigns.Rows)\n                {\n                    var name = myForeign[\"CONSTRAINT_NAME\"].ToString();\n                    var reftable = myForeign[\"REFERENCED_TABLE_NAME\"].ToString();\n                    if (string.IsNullOrEmpty(reftable)) \/\/ it's an index\n                        continue;\n\n                    var msForeign = (ForeignKey)table.GetConstraint(name);\n                    if (msForeign == null)\n                    {\n                        msForeign = new Wellcode.Dbms.ForeignKey()\n                        {\n                            Name = name,\n                            ForeignTable = reftable,\n                            DELETE_RULE = myForeign[\"DELETE_RULE\"].ToString(),\n                            UPDATE_RULE = myForeign[\"UPDATE_RULE\"].ToString(),\n                        };\n                        table.ForeignKeys.Add(msForeign);\n                    }\n                    msForeign.Columns.Add(new Wellcode.Dbms.ConstraintColumn()\n                    {\n                        Name = myForeign[\"COLUMN_NAME\"].ToString(),\n                    });\n                }\n            }\n\n            return mydbms;\n        }\n\n        private void SetColumnType(string type, Wellcode.Dbms.Column msCol)\n        {\n            int len = 0;\n\n            \/\/ type in format \"type(len)\"\n            if (type.IndexOf(\"(\") &gt; 0)\n            {\n                var arr = type.Split(new char[] { '(', ')' });\n                len = Convert.ToInt32(arr[1]);\n                type = arr[0];\n            }\n\n            var rettype = type;\n\n            if (Zeichen.Eq(type, \"decimal\"))\n                rettype = \"money\";\n\n            else if (Zeichen.Eq(type, \"tinytext\"))\n            {\n                rettype = \"varchar\";\n                len = 255;\n            }\n\n            else if (Zeichen.Eq(type, \"text\"))\n            {\n                rettype = \"varchar\";\n                len = -1;\n            }\n\n            else if (Zeichen.Eq(type, \"MEDIUMTEXT\"))\n            {\n                rettype = \"varchar\";\n                len = -1;\n            }\n\n            else if (Zeichen.Eq(type, \"longtext\"))\n            {\n                rettype = \"varchar\";\n                len = -1;\n            }\n\n            if (Zeichen.Ne(rettype, \"char\") &amp;&amp; Zeichen.Ne(rettype, \"varchar\"))\n                len = 0;\n\n            msCol.CHARACTER_MAXIMUM_LENGTH = len;\n            msCol.DATA_TYPE = rettype;\n        }\n    }\n\n    public class OdbcConnector\n    {\n        public OdbcConnector(string connstr)\n        {\n            this.CreateConnection(connstr);\n        }\n\n        OdbcConnection Connection;\n\n        public OdbcConnection CreateConnection(string connstr)\n        {\n            this.Connection = new OdbcConnection(connstr);\n            this.Connection.Open();\n            return this.Connection;\n        }\n\n        public OdbcCommand CreateCommand(string sql)\n        {\n            var item = this.Connection.CreateCommand();\n            item.CommandText = sql;\n            return item;\n        }\n\n        public DataTable GetResult(string sql)\n        {\n            var ds = new DataTable();\n            var adapter = new OdbcDataAdapter(this.CreateCommand(sql));\n            adapter.Fill(ds);\n            return ds;\n        }\n    }\n\n    public class DynFactory\n        : WebEntityFactory&lt;WebEntity, WebEntityList&lt;WebEntity&gt;&gt;\n    {\n        public DynFactory(DynManager manager)\n        {\n            this.Manager = manager;\n        }\n\n        public override WebEntity CreateEntity()\n        {\n            throw new NotImplementedException();\n        }\n    }\n\n    public class DynManager\n        : EntityManager\n    {\n\n    }\n}\n<\/pre>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>Limitations<\/strong><br>This is just an example of how things can work. Not all the mysql data types are mapped to the data types of ms sql server.<\/li><\/ul>\n","protected":false},"excerpt":{"rendered":"<p>This page&nbsp;illustrates the strongness of the wellcode dbms tool and shows how to&nbsp;migrate a MySQL database to MS SQL Server including indexes and constraints.&nbsp;The Example uses the dbms tool to generate and create the schema and a part of WellFrame library to dynamically&nbsp;import data. Conditions to start MySQL ODBC Driver or MySQL.Net&nbsp;are installed on your &hellip; <a href=\"https:\/\/wellcode.de\/home\/index.php\/en\/migrate-mysql-to-sql-server\/\" class=\"more-link\">Continue reading <span class=\"screen-reader-text\">Migrate MySQL To SQL Server<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"open","ping_status":"open","template":"","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"class_list":["post-36","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/36","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/comments?post=36"}],"version-history":[{"count":22,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/36\/revisions"}],"predecessor-version":[{"id":1681,"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/pages\/36\/revisions\/1681"}],"wp:attachment":[{"href":"https:\/\/wellcode.de\/home\/index.php\/wp-json\/wp\/v2\/media?parent=36"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}