SQL Server (on-premises) con Azure Data Factory

Prerrequisitos para SQL Server y Data Factory

Cuando se habla de copia de datos entre SQL Server y Data Factory en Azure, es importante tener en cuenta que si el servidor SQL se encuentra dentro de una red privada (on-premises), debes disponer del software de integración de datos instalado en él (Self-hosted Integration Runtime) para conectarte a Data Factory.

Objetivo

La idea de esta publicación es generar una guía que permita configurar, de forma práctica, un enlace de comunicación entre dos nodos de un mismo sistema. Dicha conexión permitirá realizar una copia transparente de tablas SQL de un conjunto de variables desde un nodo local «A-LOCAL» hacia la base de datos SQL de gran capacidad del nodo remoto «B-REMOTO», utilizando la plataforma de Azure «Data Factory» y el componente «Self-hosted Integration Runtime«.

Configuración para SQL Server y Data Factory

Establece el ambiente básico de trabajo en Azure

Crea el grupo de recursos «Resource Group» a utilizar

• En primer lugar, presiona «Create a resource» en la página de inicio de Azure.

• A continuación, presiona el botón «Create» para iniciar la creación del grupo de recursos.

• Seguidamente, en el formulario mostrado, asigna los valores que apliquen y presiona el botón «Review + create» para revisar la creación del grupo de recursos.

• Finalmente, procede a presionar el botón «Create» para completar la creación del grupo de recursos indicado.

Crea el servicio o recurso «Data Factory» a utilizar

• Para iniciar esta tarea, presiona el enlace «Create a resource» en la página de inicio de Azure.

• Seguidamente, presiona el botón «Create» para iniciar la creación del servicio o recurso.

• Utiliza los valores mostrados y presiona el botón «Create» para continuar.

Crea el servicio o recurso «SQL Database» a utilizar

• Presiona el enlace «Create a resource» en la página de inicio de Azure.

• Luego, presiona el botón «Create» para iniciar la creación del servicio o recurso.

• En la pestaña «Basics», asigna los valores mostrados y presiona el enlace «Create new» para crear un nuevo servidor SQL.

• Asigna los valores que correspondan y presiona el botón «OK» para finalizar la creación del nuevo servidor SQL.

• Ahora, en la pestaña «Basics», verifica los valores asignados y presiona el botón «Next : Networking >» (si deseas modificar el modelo de base de datos que se configura por defecto, Standard S0, presiona «Configure database»).

• Seguidamente, en la pestaña «Networking», verifica las opciones escogidas y presiona el botón «Next : Additional settings >».

• A continuación, en la pestaña «Additional settings», verifica las opciones escogidas y presiona el botón «Next : Tags >».

• Luego, en la pestaña «Tags», presiona el botón «Next : Review + create >».

• Finalmente, en la pestaña «Review + create», observa un resumen de las selecciones realizadas y presiona el botón «Create >» para crear el recurso.

Crea tabla de ejemplo en base de datos Azure SQL

• Accede al enlace «SQL databases» para iniciar la gestión de la base de datos creada.

• A continuación, ingresa al enlace «system_db» para mayor detalle.

• Después, ve a «Query editor (preview)» para abrir el editor interno de solicitudes y consultas a la base de datos.

• Luego, ingresa las credenciales requeridas y presiona el botón «OK» para abrir el editor.

• A continuación, crea el esquema «sc_devvar» para la tabla de ejemplo (inicia el script con el botón «Ejecutar»).

• Posteriormente, crea la tabla de ejemplo «sc_devvar.tb_rv_zit_5511» (inicia el script con el botón «Ejecutar»).

• Finalmente, verifica que la tabla de ejemplo indicada haya sido creada con éxito.

Gestiona servicio o recurso «Data Factory»

Ejecuta el ambiente de desarrollo «Azure Data Factory»

• Presiona el enlace «Data factories» para iniciar la gestión del servicio o recurso creado.

• Presiona el enlace «df1» para mayor detalle.

• Con el recurso seleccionado, presiona «Overview» y luego «Author & Monitor» para iniciar el entorno de desarrollo.

• Presiona el botón «Author» para abrir el entorno de desarrollo.

Crea servicio enlazado «Linked service» o conexión para flujo de datos de entrada

• Ve a «Connections» para iniciar la creación del servicio indicado.

• Presiona «+ New» para agregar un nuevo servicio enlazado.

• Selecciona «Data store», escribe «sql» en el buscador, escoge «SQL Server» y presiona «Continue» para seguir.

• Asigna el nombre que corresponda y presiona «+New» para iniciar la creación del componente «Self-hosted Integration Runtime».

• Escoge «Self-Hosted» y luego presiona «Continue» para seguir.

• Establece el nombre del servicio y luego presiona «Create».

• Elige la opción «Manual Setup» y presiona «Download and install integration runtime».

• Presiona «Download» para iniciar la descarga del software «Azure Data Factory Integration Runtime».

• Marca las casillas indicadas (las versiones elegidas son las más recientes al momento de la publicación) y presiona «Next» para proceder.

• Instala el archivo «.msi» descargado como usuario administrador en el nodo «A-LOCAL» y presiona «Next».

• Presiona «Install» y acepta las opciones mostradas por defecto para continuar.

• Asegúrate que el nodo «A-LOCAL» no entre en ningún modo de suspensión de energía durante la operación del servicio.

• Una vez completado el proceso, presiona «Finish» para finalizar la instalación.

• Observa el lugar donde se debe realizar el registro de la credencial requerida (Authentication Key).

• De vuelta al portal en Azure, copia la llave número 1 (Key 1) y presiona el botón «Apply».

• Inserta el contenido de la llave copiada y presiona «Register» para continuar con el registro en la aplicación de escritorio.

• Presiona «Finish» para completar el proceso.

• Verifica que la ventana muestre el mensaje «Integration Runtime (Self-hosted) node has been registered successfully» y presiona «Close» para cerrar el administrador de configuración.

• De vuelta al portal en Azure, verifica que el formulario tenga los valores adecuados.

• Presiona «Test connection» y espera que se muestre el mensaje «Connection successful». Seguidamente, presiona el botón «Create» para finalizar la creación del servicio.

• Verifica que el servicio indicado ha sido creado con éxito.

Crea servicio enlazado «Linked service» o conexión para flujo de datos de salida

• Presiona el botón «+ New» para agregar un nuevo servicio enlazado.

• Selecciona la pestaña «Data store», escribe «sql» en el buscador, escoge «Azure SQL Database» y presiona «Continue» para seguir.

• Verifica que el formulario para el nuevo servicio tenga los valores adecuados.

• Presiona el botón «Test connection», espera el mensaje «Connection successful» y luego, selecciona «Create».

• Verifica que el servicio indicado ha sido creado con éxito.

Crea conjunto de datos «Datasets» de ejemplo para entrada

• En la página de inicio de Azure Data Factory, ve a la categoría «Datasets» y presiona el botón «…» para desplegar otras acciones.

• Presiona la opción «New dataset» del menú contextual para iniciar la creación del conjunto de datos indicados.

• Escribe «sql» en el buscador, escoge «SQL Server» y presiona «Continue» para seguir.

• Verifica que el formulario para el nuevo conjunto de datos tenga los valores adecuados y se asocie con el servicio enlazado de entrada. Presiona el botón «OK» para seguir el proceso.

• Verifica que el conjunto de datos de ejemplo para entrada se haya creado con éxito.

• En la pestaña «Connection», verifica el servicio enlazado y la tabla de datos de entrada.

• Presiona el botón «Test connection» y espera que se muestre el mensaje «Connection successful».

Crea conjunto de datos «Datasets» de ejemplo para salida

• Presiona la opción «New dataset» del menú contextual para iniciar la creación del conjunto de datos indicados.

• Escribe «sql» en el buscador, escoge «Azure SQL Database» y presiona «Continue» para seguir.

• Verifica que el formulario para el nuevo conjunto de datos tenga los valores adecuados y se asocie con el servicio enlazado de salida. Presiona el botón «OK» para seguir el proceso.

• Verifica que el conjunto de datos de ejemplo para salida se haya creado con éxito.

• Presiona el botón «Test connection» y espera que se muestre el mensaje «Connection successful».

Publica los cambios en el ambiente de desarrollo

• Observa el botón «Publish all» en la parte superior de panel, con un número indicando los cambios pendientes. Presiónalo para continuar.

• Presiona el botón «Publish» para finalizar la publicación de todos los cambios pendientes.

Crea tarea de procesamiento «Pipeline» para copia de datos

• En la página de inicio de Azure Data Factory, ve a la categoría «Pipelines», presiona el botón «…» y selecciona «New pipeline».

• En la sección «Activities», arrastra la actividad «Copy data» y suéltala en el panel principal.

• Ahora, en la pestaña «General», ajusta el nombre de la tarea de procesamiento a uno más descriptivo.

• Después, en la pestaña «Source», verifica que se haya seleccionado el conjunto de datos de entrada en el campo «Source dataset».

• A continuación, en la pestaña «Sink», verifica que se haya seleccionado el conjunto de datos de salida en el campo «Sink dataset».

• Observa el botón «Publish all» en la parte superior de panel, con un número indicando los cambios pendientes. Presiónalo para continuar.

• Finalmente, presiona el botón «Publish» para completar la publicación de todos los cambios pendientes.

Ejecuta tarea de procesamiento «Pipeline» para copia de datos

• Al inicio, presiona el botón «Debug» para probar y depurar la tarea de procesamiento configurada.

• Seguidamente, en la pestaña «Output», verifica que se muestre el mensaje «Succeeded» (ejecutada con éxito) para la tarea de copia de datos indicada.

Verifica copia de datos en base de datos Azure SQL

• En la página de inicio del portal, presiona el enlace «SQL databases» para iniciar la verificación de la escritura en la base de datos indicada.

• Presiona «system_db» para mayor detalle.

• Abre el editor presionando el enlace «Query editor (preview)».

• Valida el usuario y presiona «OK» para seguir.

• Ejecuta el script mostrado y verifica en la ventana de resultados que la escritura de datos fue realizada con éxito.

Configura copia periódica de datos

Crea habilitador de evento

• En el ambiente de trabajo «Data Factory», ubica el botón «Add trigger» para iniciar la configuración del evento que activa la copia periódica de datos desde el nodo «A-LOCAL».

• Presiona el botón «Add trigger» y selecciona «New/Edit».

• En la ventana «Add triggers», presiona el botón «+ New» para agregar un nuevo habilitador de copia de datos.

• En la ventana «New trigger», verifica que el formulario tenga los valores adecuados (ajústalos según se requiera). Presiona el botón «OK» para guardar los cambios.

• Observa que el nuevo habilitador de copia de datos no requiere parámetro alguno. Presiona el botón «OK» para continuar (al cerrar esta ventana, no olvides publicar los nuevos cambios para que se pueda activar la tarea de procesamiento indicada).

Usa esquema de copia con instrucción TRUNCATE TABLE

• En el panel izquierdo, selecciona la tarea de procesamiento indicada.

• En el panel central, selecciona la actividad «Copy data» indicada.

• Luego, en la pestaña «Source», ajusta a las opciones indicadas.

• En la pestaña «Sink», ajusta a las opciones indicadas e inserta el script SQL que aplique según los requerimientos.

• Observa el botón «Publish all» en la parte superior de panel, con un número indicando los cambios pendientes. Presiónalo para continuar y espera que se complete la publicación.

• Presiona el botón «Publish» para finalizar la publicación de todos los cambios pendientes.

• En el panel izquierdo, presiona el botón «Monitor» para verificar la ejecución de cada copia periódica de datos.

• Observa todas las copias de datos realizadas con éxito y hechas en un intervalo de tiempo determinado.

• Ve al editor SQL, ejecuta el script asociado a «SELECT *» y verifica en la ventana de resultados que la copia periódica de datos fue realizada con éxito.

Usa esquema de copia con instrucciones UPDATE/INSERT en procedimiento almacenado

• Para iniciar, presiona el enlace «SQL databases» para iniciar la gestión de la base de datos.

• Seguidamente, presiona el enlace «system_db» para mayor detalle.

• Ahora, ejecuta el editor con «Query editor (preview)».

• Ingresa con los permisos solicitados.

• Crea el tipo de tabla «sc_devvar.tb_rv_zit_5511_type» (inicia el script con el botón «Ejecutar»).

• Crea el procedimiento almacenado «dbo.sp_ups_tb_rv_zit_5511» (ejecuta el script con el botón «Ejecutar»).

• De vuelta a «Data Factory» y en el panel izquierdo, selecciona la tarea de procesamiento indicada.

• En el panel central, selecciona la actividad «Copy data» indicada.

• A continuación, en la pestaña «Source», ajusta a las opciones indicadas.

• En la pestaña «Sink», ajusta a las opciones indicadas y presiona el botón «Import parameter» para completar la carga de los campos «Table type» y «Table type parameter name».

• Observa el botón «Publish all» en la parte superior de panel, con un número indicando los cambios pendientes. Presiónalo para continuar, espera que se complete la publicación y confirma cuando sea necesario.

• Una vez habilitado el evento en el tiempo fijado, ve al editor SQL, ejecuta el script mostrado y verifica en la ventana de resultados que la copia periódica de datos fue realizada con éxito.

Referencias

• Partes del vídeo «Azure Data Factory Tutorial | Introduction to ETL in Azure«.
• Partes del vídeo «Azure Data Factory Self-hosted Integration Runtime Tutorial | Connect to private on-premises network«.
• Publicación «Copy SQL Server Data in Azure Data Factory«.
• Publicación «Upsert to Azure SQL DB with Azure Data Factory«.
• Partes del vídeo «Upsert to Azure SQL DB with Azure Data Factory«.

Para otros artículos como éste en español, presiona aquí. Para verlos en inglés, presiona aquí.