Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
<< hoy he aprendido a valorar la simplicidad de las cosas >>.
Migración de MS SQL Server a MySQL. Este es un problema típico para una organización que quiere migrar toda su base de datos que está en Microsoft SQL Server a MySQL. Ya sea por razones de amor a la complejidad, o por razones económicas (la licencia de MS SQL es muy cara). Al rebuscar en la web, no encontré información del proceso interno de migración, los conflictos de datos, problemas para trasladar el modelo físico, que porcentaje se lograba alcanzar en la migración al usar herramientas de solución. Al barajar entre las posibilidades escogí 3. 1. Migrar la base de datos manualmente 2. Usar una herramienta de migración 3. Implementar una solución en un Leng uaje de Prog ramación (xD no recomendado). Requerimientos: Tener ambos servidores corriendo. Paciencia. No mentar la madre a los creadores de las herramientas (Full Convert Enterprise Trial ahí te voy). Para que el artículo no sea teórico migraremos la base de datos “academia” a la base de dat os “academiaDestino”. La base de datos tiene el modelo físico siguiente.
…. ya tiene tablas (en total 9) y registros (Ej. Para la tabla alumno 51 registros).
http://soymiuniversidad.blogspot.com
1
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Es una base de datos totalmente funcional para una academia. Ej. Mostrar el número de ficha, nombre, apellido paterno y sexo de un alumno Son T rece recuérdalo. Servirá para comprobar si nuestra base de datos que pasamos sigue siendo usable .
La consulta es la siguiente -- Mostrar el número de ficha, nombre, apellido paterno y sexo de un alumno SELECT FICHA_MATRICULA.NRO_FICHA,ALUMNO.NOM_ALUMNO,ALUMNO.APE_PATERNO, ALUMNO.SEXO_ALUMNO FROM FICHA_MATRICULA INNER JOIN ALUMNO ON FICHA_MATRICULA.COD_ALUMNO=ALUMNO.COD_ALUMNO ORDER BY SEXO_ALUMNO El detalle esta a continuación…
http://soymiuniversidad.blogspot.com
2
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
<< esta será la prueba final al que someteremos a nuestra base de datos , cuatro varones y 9 mujeres matriculadas, recuérdalo >>
Primera solución (Migrar la base d e datos manualmen te, es una mejor s olución si s olo quieres migrar la estructura física de la base de datos, es válido para cualquier Gestor de base de datos que funcione sobre S QL). Si manualmente no estoy loco, es una tontería usar un programa si vas a migrar dos tablas, y encima solo de tipo “VARCHAR e INT”, es como si intentaras matar una mosca a cañonazos. Para implementar esta solución seguiremos los siguientes pasos a. Obtener el modelo físico de la base de datos b. Obtener el script SQL de creación de tablas correspondiente c. Transformar nuestro Script a SQL ANSI. d. Ejecutar los scripts e. Exportar archivos de data( *.csv ) Se puede resumir a dos. a. Reconstruir la estructura de las tablas en SQL ANSI ( Aceptado por MySQL) b. Ejecutar el script y migrar la data. Necesitas tener conocimientos sobre SQL ANSI, algo de modelamiento ER, específicamente el modelo físico (en tu modelador favorito, seguro que lo tienes por ahí). Si no lo tienes no es problema siempre podemos recuperarlo haciendo Ingeniería Inversa
http://soymiuniversidad.blogspot.com
3
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Comencemos a implementarlo. Necesitamos el servidor de MS SQL funcionando, abre el administrador corporativo, y selecciona la base de datos “academia”. Recuperando el modelo físico de las tablas, esto nos servirá como referencia, dale clic donde dice diagramas.
Anti clic en la región blanca (No hay elementos dis…), le haces clic en la primera opción “Nuevo diagrama de base de datos” y obtén un cuadro de dialogo similar a este.
Posteriormente seleccionaremos todas las tablas que queremos migrar, para este ejemplo solamente migraremos dos tablas, no quiero modificar mucho código en SQL. No olvides marcar la opción “Agregar automáticamente tablas relacionadas”
http://soymiuniversidad.blogspot.com
4
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Aceptamos y finalizamos el proceso, obtendremos los atributos para ambas tablas y sus relaciones.
Nota: La imagen que obtengas puede variar mucho. Al cerrar el diagrama, te pedirá confirmación si quieres guardar el diagrama o no, dale en sí.
Ponle un nombre al diagrama y acepta, ahora tienes el diagrama físico de la base de datos (solo para dos tablas relacionadas) http://soymiuniversidad.blogspot.com
5
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Hicimos esto para averiguar que atributo son la clave primaria, y cuales con claves foráneas. Saca un papel y anota estos valores. Nota: Es mucho mejor si lo imprimes. Abre el diagrama que creamos y a reconocer los atributos. Claves Primarias, observa la llavecita a su costado
Define la relación existente en ambas tablas el atributo COD_AL UMNO(Foreign Key) de la tabla FICHA_MATRICULA, es muy fácil de reconocer solamente fíjate en el símbolo que hay en la tabla parecido a infinito . Significa que el atributo referencia al atributo de mismo nombre de la tabla alumno. Prácticamente significa que no puedes matric ular a un alumno que no está registrado.
Obtener el script correspondiente de creación de ambas tablas, teniendo claro cuáles son las PK, y FK. http://soymiuniversidad.blogspot.com
6
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Seleccionamos
la
base
de
datos
y
abrimos
las
tablas
Seleccionamos la tabla ALUMNO y abrimos el menú contextual (clic en Generar secuencias de comandos SQL)
Seleccionamos las tablas de las cuales queremos obtener la estructura física
http://soymiuniversidad.blogspot.com
7
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Solamente marcamos la primera opcion Generar el comando CREATE para cada objeto
Aceptamos y guardamos el script http://soymiuniversidad.blogspot.com
8
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Abrimos el archivo con un editor de texto (que tenga la función de reemplazar)
http://soymiuniversidad.blogspot.com
9
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
En este caso es el script que genero fue el siguiente. CREATE TABLE [dbo].[ALUMNO] ( [COD_ALUMNO] [char] (10) NOT NULL , [APE_PATERNO] [varchar] (35) NULL , [APE_MATERNO] [varchar] (35) NULL , [NOM_ALUMNO] [varchar] (25) NULL , [FEC_NACIMIENTO] [datetime] NULL , [LUG_NACIMIENTO] [char] (25) NULL , [SEXO_ALUMNO] [bit] NULL , [DIR_ALUMNO] [varchar] (35) NULL , [TEL_ALUMNO] [varchar] (8) NULL , [OTROS_ESTUDIOS] [text] NULL , [DOC_ALUMNO] [varchar] (15) NULL , [FOTO_ALUMNO] [image] NULL ) GO CREATE TABLE [dbo].[FICHA_MATRICULA] ( [NRO_FICHA] [int] IDENTITY (1, 1) NOT NULL , [TURNO] [char] (1) NULL , [FECHA_FICHA] [datetime] NULL , [OBSERVACIONES] [text] NULL , [COD_ALUMNO] [char] (10) NULL , [COD_EMPLEADO] [int] NOT NULL , [COD_PERIODO] [char] (5) NULL , [COD_ESPECIALIDAD] [char] (5) NULL ) GO Procederemos a editar nuestro script de tal manera que se adapte a MySQL, ej. Para la tabla FICHA_MATRICULA(para quien ya conoce SQL ANSI y algo de MySQL, esta parte le resultara muy fácil): Modificando el código. Quitaremos el propietario de la tabla [dbo]. ( reemplazar [dbo]. Por „‟ ) Reemplazaremos los corchetes „[„ y „]‟ por „‟(por nada). GO será reemplazado por el punto y coma „ ; ‟ exigencias de MySQL
http://soymiuniversidad.blogspot.com
10
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Modificando los atributos [NRO_FICHA] [int] IDENTITY (1, 1) NOT NULL en la línea Nro. 18, para MS SQL significa que va generando un valor que se incrementa a partir del uno, para pasarlo a MySQL bastara buscar un equivalente en este caso será: NRO_FICHA INT NOT NULL AUTO_INCREMENT, autoincrement es una propiedad especial de MySQL que trabaja de manera similar Para la tabla A lumno no hay mucho problema solo debemos cambiar la línea Nro. 8 [SEXO_ALUMNO] [bit] NULL por SEXO_ALUMNO TINYINT(1) NULL. El tipo de datos bit, no está disponible en MySQL, asumo que deben ser problemas de patentes, así que usaremos un equivalente TINYI NT(1) entero pequeño de longitud uno, este tipo de dato, no trabaja de manera igual a bit. Porque aceptara valores del uno al 9, podríamos hacer un CHECK de tal manera que solo acepte cero o uno. Definiendo las claves primarias y foráneas Para la tabla AL UMNO, según el diagrama es el atributo COD_AL UMNO Su traducción a SQL seria agregar la línea PRIMARY KEY( COD_ALUMNO ) Para la tabla FICHA_MATRICULA, que tiene una clave primaria y foránea será PRIMARY KEY( NRO_FICHA ) FOREIGN KEY (COD_ALUMNO) REFERENCES ALUMNO (COD_ALUMNO) Nota importante: Si diseñaste bien tu base de datos, la PK no aceptara valores nulos, después de editarlo me quedo así, muestro con negrita las modificaciones. CREATE TABLE ALUMNO ( COD_ALUMNO CHAR(10) NOT NULL, APE_PATERNO VARCHAR(35) NULL, APE_MATERNO VARCHAR(35) NULL, NOM_ALUMNO VARCHAR(25) NULL, FEC_NACIMIENTO DATE NULL, LUG_NACIMIENTO CHAR(25) NULL, SEXO_ALUMNO TINYINT(1) NULL, DIR_ALUMNO VARCHAR(35) NULL, TEL_ALUMNO VARCHAR(8) NULL, OTROS_ESTUDIOS TEXT NULL, DOC_ALUMNO VARCHAR(15) NULL, FOTO_ALUMNO BLOB NULL, PRIMARY KEY(COD_ALUMNO) ); CREATE TABLE FICHA_MATRICULA ( NRO_FICHA INT NOT NULL AUTO_INCREMENT, TURNO CHAR(1) NULL, FECHA_FICHA DATE NULL, OBSERVACIONES TEXT NULL, COD_ALUMNO CHAR(10) NOT NULL, COD_ESPECIALIDAD CHAR(5) NOT NULL, COD_EMPLEADO INT NOT NULL, COD_PERIODO CHAR(5) NOT NULL, http://soymiuniversidad.blogspot.com
11
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
PRIMARY KEY(NRO_FICHA), FOREIGN KEY (COD_ALUMNO) REFERENCES ALUMN O (COD_ALUMNO) );
Al tener este script adaptado a las exigencias de MySQL, ejecutaremos este script en MySQL, previamente crearemos la base de datos.
Por cierto la GUI, que uso para MySQL se llama HeidiSQL, usa el que más te guste ahora solamente debemos ejecutar el script que acabamos de crear.
http://soymiuniversidad.blogspot.com
12
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Y ta tan, las tablas ya fueron creadas
Migrando la data. Hay muchas formas, pero la más sencilla es usar archivos separados por *.csv, si los archivos que usa Excel. Exportando la data para la tabla alumno, desde MSSQL. Accedemos a MS SQL, Seleccionamos la BD en cuestión Seleccionamos la tabla Escogemos la opción Exportar datos, creo que con un grafico se entenderá mejor.
http://soymiuniversidad.blogspot.com
13
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Nos saldra el clasico asistente de MS SQL Server, le damos clic a siguiente y escogeremos el origen(source) de datos “academia”
Seleccionando el destino (archivo de texto)
Escogemos un nombre de archivo, para la data que exportaremos (alumno.csv) http://soymiuniversidad.blogspot.com
14
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Ahora tenemos dos opciones, copiar toda la data o crear una consulta SQL para especificar que datos se copiaran (es completo este asistente) Como queremos toda la información…
http://soymiuniversidad.blogspot.com
15
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Formateamos la salida, escoge el que más te guste, yo me quede con este
Que me dará la siguiente estructura Atributo, atributo, atributo, atributo,… salto de línea Atributo, atributo, atributo, atributo,… salto de línea Finalizamos el proceso, notar que especifica cuantos datos se han copiado
Ya que tenemos ese archivo, exportamos a MySQL, sin embargo cuando checamos la data nos daremos con una sorpresa (desagradable por cierto).
http://soymiuniversidad.blogspot.com
16
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
El tipo de datos bit, fue exportando como True – False y no será compatible con el nuevo tinyint. Solución: Buscar la cadena True, False reemplazarla con 1 y 0 respectivamente. Debe quedar así
http://soymiuniversidad.blogspot.com
17
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Importando nuestro archivo alumno.csv a MySQL Abrimos nuestra GUI favorita para MySQL, seleccionamos la BD y la tabla de destino (alumno).
Seleccionamos el origen d el cual importaremos los datos
Clic en import, eso es tod o.
http://soymiuniversidad.blogspot.com
18
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Segunda solución (Migrar la base de datos usando una herramienta de migración). La solución en general es la siguiente. a. Seleccionar la base de datos fuente ( source ) para este caso MSSQL b. Seleccionar la base de datos destino, MySQL c. Seleccionar las tablas y data a copiar d. Clic en Convert, Start, Commit…. ( cada programa con su botón ) Aquí van los valores por defecto para los servidores (conviene saberlos). MySQL Host: localhost //nombre del servidor Puerto: 3306 User: root Password: „‟ Vacio, solo en algun os MS SQL Host: Nombre del servidor que pusiste, si escogiste a windows para que maneje el servidor el nombre será el nombre de usuario, para mi maquina es “THE_FLAKO” User: sa Password: „‟ Herramientas, me quede con estos dos… seguro que hay muchos mas Mssql2MySQL de Michael Kofler (open source) Full Convert Enterprise (de paga)
http://soymiuniversidad.blogspot.com
19
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Mssql2MySQL de Michael Kofler (open source) Por hoy (Domingo), me doy por vencido este día… el ganador es, mssql2mysql es open source, así que puedes descargar la última versión del programa 0.10, una herramienta recomendada en la página de MySQL, con las siguientes limitaciones ;
No No No No No
soporta soporta soporta soporta soporta
claves foranes ( foreign keys) procedimientos almacenados ( stored procedures) disparadores ( triggers ) Vistas ( views ) datos definidos por el usuario
Hace su trabajo muy bien, si tus tablas no tienen muchas relaciones, ni abusaste de los puntos descritos. Necesitas… Una GUI, para el programa abajo el link El conector ODBC de MySQL en su versión 3.51, (el programa es algo antiguo), aquí renegaras un poco, los de MySQL te pedirán tu valioso e-mail antes de de descargarlo, dáselo no reclames. Para motivos de esta práctica descargaremos la última GUI, mssql2mysql-gui2 es un proyecto en VB, luego de desempaquetarlo…
El autor nos ahorro el trabajo de compilarlo, es el archivo mas colorido mssql2mysql.exe (BYTE MULTIMEDIA) A simple vista no es la gran cosa, pero ya verás el potencial que tiene
http://soymiuniversidad.blogspot.com
20
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Le das clic en settings para configurar
Para el lado izquierdo MS SQL. MS SQL Host: Nombre del servidor Secure Login: Esta en Yes porque uso la misma cuenta del sistema para SQL Server, si usas una cuenta diferente ponla. Si no te da con ninguna el nombre del servidor está mal. Puedes probar “sa” como usuario, sin las comillas y clave en blanco (es el usuario por defecto para MS SQL Server) New database name El nombre de la nueva base de datos. (Si, si... no tiene sentido porque aun no hemos seleccionado el origen). Output to file?, esta opción la dejamos en no, porque no nos interesa Para el lado derecho MySQL. No necesitan muchas explicaciones, los parámetros Drop database?, te está preguntando si la base de datos existe lo apuñala a punta de n ulls o no (si la borramos o no). Unicode to blob, no se para que servirá creo que era un problema con MS SQL 2005. Le haces clic en Save Settings luego en Test Conection, si todo sale bien veras esto
http://soymiuniversidad.blogspot.com
21
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Ya terminamos de configurar, cierra esa ventana. Obtén una ventana igual a la de abajo.
Al hacer clic en Connect, te saldrá la lista de BD disponibles en el servidor de MS SQL -> seleccionas la base de datos que quieres en este caso “academia” -> selecciona las tablas que quieres pasar a MySQL, si hay muchas puedes usar el botón del Select All. Paso final (es el más fácil), clic a Start. Y estate atento a cualquier error que se produzca
http://soymiuniversidad.blogspot.com
22
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Verificando si se crearon y copiaron las tablas y datos.
Ya se nota, algo feo en esa primera captura que mal inicio tuvimos, problemas con el tipo de datos bit, que actualmente fue transformado a T YNYINT (4) y el (uno) que debería aparecer ahora es un (1), con los demás datos no tuvo problemas haber lo primero que se me ocurre es hacer una consulta.
http://soymiuniversidad.blogspot.com
23
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es UPDATE alumno SET SEXO_ALUMNO = 1 WHERE SEXO_AL UMNO = -1
Ahora la prueba final - MOSTRAR EL NOMBRE, APELLIDOS Y SEXO DE LOS ALUMNOS MATRICULADOS VARONES.
No me quejo, logramos un 95% de migración (el 5% son las claves foráneas), solo debemos recordar que las fechas, el tipo bit son algo difíciles de tratar… cuando dejaran libre a “bit”
http://soymiuniversidad.blogspot.com
24
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Full Convert Enterprise v3. Este es uno de los programas que mayor rendimiento tiene en cuanto a migración, soporta multitud de base de datos, si tienes un trabajo más serio comprarlo es muy buena opción para el ejemplo usaremos, una versión trial de 30 días perfectamente funcional, en realidad la trial “da rabia”, porque le pone un triste slogan ***TRIAL VERSION**** a algunos registros, pero en fin como en la vida real si quieres obtener su real rendimiento tienes que pagar por ello pero definitivamente en es el mejor. Escogí este programa, antes de DBConvert, MSSQL – To – MySQL, por que soporta mucho mejor las claves foráneas, y no tiene la jodida limitación de solamente cinco registros, diez registros (como voy a saber yo si puede pasar 10 millones de registros eh... serán burros estos). Pero la verdadera razón por la cual se gano su lugar fue porque permite retroalimentación, es decir podremos volver de M ySQL a MS SQL si algo sale mal, si no nos gusto…. Etc., Etc. La solución en general es la misma. El programa de por es muy intuitivo y los valores por defecto están a la perfección, ahora nuestro trabajo es adentraremos algo mas en sus opciones “avanzadas”. Nota. Para descargar el trial necesitas entregar tu correo electrónico real, ahí te llegara un código de activación (que difícil la hacen), instala, pon ese código y abre el programa (esto es deducible).
Crearemos una nueva sesión en Create New conversión, seleccionando el origen.
http://soymiuniversidad.blogspot.com
25
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Detalle de selección de destino, por cierto debemos crear previamente la base de datos la llamare “academiafullconvert”.
http://soymiuniversidad.blogspot.com
26
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Por defecto asume que queremos convertir todas las tablas, eso es lo queremos así que lo dejaremos, ahora veamos que puede realmente hacer le damos clic a Count Records, para que cuente los registros, fíjate que aparecen al lado del nombre de la tabla.
Seleccionamos la tabla ALUMNO, y le damos clic en Table Settings.
http://soymiuniversidad.blogspot.com
27
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Lo primero que n otamos es que ten emos absoluto c ontrol sobr e cómo se migraran los datos…. Desglosando ese grafico. Nombre de la tabla
Aquí te está diciendo, “Oe defin e la migración” La opción por defecto copia la estructura física de la tabla. La segunda opción Recreate data, seguro la usaremos cuando algo en nuestra migración no anduvo mal, borra toda la data que existe en la tabla de destino y la vuelve a crear, esta opción no recrea la estructura(la estructura física de la tabla ya existe). La tercera opción agrega datos al final.
Esta opción la necesitaremos si solamente queremos migrar parte de la data, en este caso solamente pasare a los alumnos que sean de Tacna.
Aquí observamos, como se pasaran los atributos de la tabla de origen (color amarillo) a la tabla de destino (color blanco). Hagamos unos cuantos pinitos. Tinyint unsigned no me parece buena opción como mis datos de fecha de nacimiento no registro hora lo pondré solamente de date. Recientemente en Perú como los números de celular se agregaron dos dígitos ahora quiero guardarlo con código de país incluido porque hay unos extranjeros… no hay problema en total la longitud de un teléfono normal tendrá 13 dígitos. Si algo sale mal cuando estamos editando, siempre nos que dara “Revert”.
http://soymiuniversidad.blogspot.com
28
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es Me quedo así.
Aceptamos y seguimos adelante.
Clic en Convert, por cierto si quieres que las tablas tengan un prefijo y otras cosillas no olvides la opción de Customize.
http://soymiuniversidad.blogspot.com
29
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Me salió un error... xD, que creías que iba a ser facil… no paso ni un solo registro a la tabla alumno Me olvide las comillas simples de la consulta xD, no importa les dije que escogí este programa porque podemos volver a editar los parámetros, guardar la sesión... pasar los datos de a poquito. Excusarnos que la migración está muy complicada que a lo mejor lo terminas en un semana, que le va a salir más caro, que algunos datos son incompatibles. Le doy clic en File -> Edit Project y al corregir el error, ta tan
http://soymiuniversidad.blogspot.com
30
Migración MS SQL - MySQL / CP IIS/ Roy Homar Quintana Alvit es
Tercera Propuesta I mplementar una solución en un Lenguaje de Programación No hay que tomar decisiones drásticas, para un problema sencillo… ahora en serio reflexionando esta solución es real, podrías usar un LP, conectarte a ambas base de datos, crear clases para cada tabla, obtener lo datos, trabajar con colecciones…. tratar el *.csv (archivo separado por comas ), llevarlo a un vector.. Pero crees que te hubiera explicado las herramientas anteriores si tuviera ganas de hacerlo?, en este caso esta es la peor de todas las soluciones, la más compleja, y honestamente si la primera solución era como matar un mosquito a cañonazos, esta sería como si hubieras traído el ejercito mundial.
Migracion MS SQL – MySQL Roy Homar Quintana A lvites Estudiante del quinto semestre de la Universidad Nacional Micaela Bastidas de Apurimac. http://soymiuniversidad.blogspot.com
http://soymiuniversidad.blogspot.com
31