Uso de Microsoft SQL Server Integration Services en Amazon RDS para SQL Server

Por Luis Gerardo Baeza, Sumit Ahluwalia, Chinni Bolapati, and Garry Singh

Ahora puede configurar Microsoft SQL Server Integration Services (SSIS) en Amazon Relational Database Service (RDS) for SQL Server. SSIS funciona en instancias de base de datos Single-AZ y Multi-AZ para las ediciones Standard y Enterprise utilizando las versiones principales de SQL Server 2016 o 2017.

Anteriormente, podía usar RDS para SQL Server como fuente de destino para SSIS, pero no podía usar SSIS en el mismo servidor que la base de datos de RDS para SQL Server. Aunque todavía es posible usar SSIS en Amazon Elastic Compute Cloud (Amazon EC2), y es posible que tenga que hacerlo para algunos casos de uso, la integración directa con RDS para SQL Server proporciona una mejor experiencia y una mejor rentabilidad al tenerlo en la misma instancia RDS de SQL Server. Si actualmente está ejecutando SSIS en Amazon EC2, ahora puede ahorrar costos ejecutando SSIS directamente en la misma instancia de base de datos RDS que su base de datos de SQL Server.

Esta publicación describe cómo configurar y usar SSIS en RDS para instancias de base de datos de SQL Server, se explica el proceso de implementación de un proyecto SSIS y la programación de su ejecución mediante el SQL Server Agent.

Prerequisitos para SSIS

Para configurar SSIS en Amazon RDS para SQL Server, debe cumplir los siguientes requisitos:

• La instancia de RDS para SQL Server debe ser SQL Server 2016 Standard o Enterprise Edition (13.00.5426.0.v1 o superior) o SQL Server 2017 Standard o Enterprise Edition (14.00.3223.3.v1 o superior).
• La instancia debe estar unida a AWS Managed Microsoft Active Directory para habilitar la autenticación de Windows. Para conocer cómo hacerlo, consulte Configuración de la autenticación de Windows.
• Debe habilitar CLR en la instancia de RDS modificando el grupo de parámetros de la instancia. Para conocer cómo hacerlo, consulte Modificación de parámetros de un grupo de parámetros de base de datos y Modificación de una instancia de base de datos de Amazon RDS.
• Debe habilitar la integración de Amazon Simple Storage Service (Amazon S3) para transferir paquetes SSIS y archivos de salida entre la instancia y el bucket de S3. Para obtener instrucciones, consulte Integración de una instancia de base de datos de Amazon RDS para SQL Server con Amazon S3.

Para obtener más información sobre las características de SSIS compatibles, consulte Limitaciones y recomendaciones para SSIS.

Configuración de SSIS

SSIS se activa en una instancia de RDS a través de un grupo de opciones. Para obtener más información, consulte Trabajar con grupos de opciones. Puede crear o utilizar un grupo de opciones existente para SQL Server según la edición de su instancia de base de datos RDS.

1. En la consola de Amazon RDS, elija el option group que desea configurar.
2. Elija Add option.

Figura 1

3. En Option name, elija SSIS.

Figura 2

Configuración de permisos SSIS para el usuario autenticado de Windows

Después de agregar la opción SSIS, se crea una base de datos con el nombre SSISDB . Para configurar sus permisos SSIS, complete los siguientes pasos:

1. Inicie SQL Server Management Studio (SSMS).
2. Conéctese al motor de la base de datos de SQL Server como usuario maestro mediante la autenticación de SQL Server.

Verá el catálogo SSISDB , como se muestra en la siguiente captura de pantalla.

Figura 3

El usuario maestro también tiene permiso para conceder los roles ssis_admin y ssis_logreader a cualquier otro usuario. Debido a que SSIS necesita un usuario autenticado de Windows para trabajar con el catálogo de SSIS, prepare al usuario autenticado de Windows completando los pasos de las siguientes secciones.

Conceder el rol ssis_admin y ssis_logreader al usuario del dominio

Para conceder el rol al usuario del dominio, complete los siguientes pasos:

1. Elija Security en el explorador de objetos en SSMS.
2. Elija Logins.
3. Elija el inicio de sesión [<domain>\<user>] de la lista.
4. Elija User Mappings.
5. Para Map, seleccione SSIDB.
6. Para Database role membership, seleccione ssis_admin y ssis_logreader.

Figura 4

7. Elija OK.

Conceder los permisos necesarios para ejecutar paquetes SSIS

Para ejecutar el paquete SSIS con el SQL Server Agent, el usuario autenticado de Windows debe tener ciertos permisos. Para conceder los permisos requeridos al usuario autenticado de Windows, use SSMS para ejecutar la siguiente consulta:

(SQL)

USE [msdb]

GO

CREATE USER [mydomain\user_name] FOR LOGIN [mydomain\user_name]

GRANT EXEC ON msdb.dbo.rds_msbi_task TO [mydomain\user_name] with grant option

GRANT SELECT ON msdb.dbo.rds_fn_task_status TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_cancel_task TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_download_from_s3 TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_upload_to_s3 TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_delete_from_filesystem TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_gather_file_details TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_add_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_update_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_grant_login_to_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_revoke_login_from_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_delete_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_enum_login_for_proxy TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.sp_enum_proxy_for_subsystem TO [mydomain\user_name] with grant option

GRANT EXEC ON msdb.dbo.rds_sqlagent_proxy TO [mydomain\user_name] WITH GRANT OPTION

ALTER ROLE [SQLAgentUserRole] ADD MEMBER [mydomain\user_name]

GO

USE [master]

GO

GRANT ALTER ANY CREDENTIAL TO [mydomain\user_name]

GO

Implementar un proyecto SSIS

Para implementar el paquete SSIS, debe mover sus archivos ISPAC a la instancia de base de datos y ejecutar el procedimiento almacenado SSAS. No se admite la implementación del proyecto directamente en una instancia de base de datos RDS. Complete los siguientes pasos:

1. Cree un nuevo proyecto de Integration Services en SQL Server Data Tools (o abra un proyecto que haya creado anteriormente

Figura 5

2.  En la pestaña Solution Explorer , elija la solución y elija Properties.

3.  Elija Common Properties.

4.  En Project Protection Level, elija Do not save sensitive data.

5.  Elija OK.

Figura 6

6.  Elija OK nuevamente.

7.  Es posible que aparezca una ventana emergente con un mensaje de que aún necesita cambiar la propiedad ProtectionLevel  de los paquetes ya existentes en este proyecto.

8.  Elija OK.

Figura 7

9.  Para cambiar el nivel de protección de su paquete existente, complete los siguientes pasos:

• • a. Choose the package in the Solution Explorer.
  • b. En la sección Control Flow, elija cualquier posición vacía (clic derecho).
  • c. Elija Properties.
  • d. Para ProtectionLevel, elija DontSaveSensitive.

Figura 8

10. Repita este procedimiento para todos los paquetes existentes presentes en este proyecto.
11. En la pestaña Solution Explorer, cree su proyecto.

Cuando crea el proyecto de Integration Services, SQL Server Data Tools genera el archivo en la carpeta \Bin\Development del proyecto. Para la implementación, necesita el archivo <project name>.ispac.

12. Cargue el archivo <project name>.ispac al bucket de S3 en el que se habilitó la integración con Amazon S3.
13. Abra SSMS, conéctese a su instancia de RDS para SQL Server como un usuario autenticado de Windows y ejecute el siguiente procedimiento almacenado para descargar el archivo de proyecto desde el bucket de S3 a la carpeta local D:\S3 en la instancia de RDS:

(SQL)

exec msdb.dbo.rds_download_from_s3

@s3_arn_of_file='arn:aws:s3:::sample-s3-bucket/testProject.ispac' ,

@rds_file_path='D:\S3\testProject.ispac' ,

@overwrite_file=1

14. Para conocer el estado del procedimiento almacenado, use el siguiente código:

(SQL)

SELECT * FROM dbo.rds_fn_task_status(NULL,task_id)

El valor task_id es el ID de la tarea. Si ingresa 0 para task_id, se muestran todas las tareas. Espere a que el ciclo de vida de la tarea (lifecycle)  se muestre como SUCCESS  antes de avanzar al siguiente paso. La columna task_info también proporciona información adicional sobre el estado de la tarea.

15. Elija el Catálogo SSISDB (clic derecho) y cree una carpeta; por ejemplo, sample-ssis-folder.
16. Llame al procedimiento almacenado SSIS_DEPLOY_PROJECT para implementar el proyecto en SSISDB. Vea el siguiente código:

(SQL)

exec msdb.dbo.rds_msbi_task

@task_type='SSIS_DEPLOY_PROJECT',

@file_path='d:\S3\testProject.ispac',

@folder_name='sample-ssis-folder',

@project_name='testProject';

17. Después de que el estatus de la tarea SSIS_DEPLOY_PROJECT cambia a SUCCESS, puede ver el proyecto implementado en el catálogo SSIS en SSMS.

Ejecución de un paquete SSIS

Después de implementar el proyecto SSIS en el catálogo SSIS, puede ejecutar paquetes directamente desde SSMS (ejecuciones únicas) o programarlos mediante el Agente SQL Server. Debe utilizar un inicio de sesión autenticado de Windows para ejecutar paquetes SSIS.

Configurar administradores de conexión de base de datos para proyectos SSIS

Para las conexiones de bases de datos locales, puede utilizar autenticación de SQL. Si desea utilizar la autenticación de Windows, utilice DB_instance_name.fully_qualified_domain_name  como el nombre del servidor de la cadena de conexión. Por ejemplo, myssisinstance.corp-ad.example.com, donde myssisinstance  es el nombre de la instancia de base de datos y corp-ad.example.com es el FQDN.

Para conexiones remotas, utilice siempre la autenticación de SQL.

Configurar un trabajo de agente para ejecutar el paquete SSIS

Para programar la ejecución de un paquete SSIS en el Agente SQL Server, complete los siguientes pasos:

1. Inicie sesión como usuario autenticado de Windows.
2. Cree una credencial de SQLServer que utilice para ejecutar el paquete SSIS. Vea el siguiente código:

(SQL)

USE [master]

GO

CREATE CREDENTIAL [SSIS_Credential] WITH IDENTITY = N'mydomain\user_name', SECRET = N'mysecret'

GO

Reemplace 'mydomain\user_name' con el usuario de su dominio y 'mysecret' con la contraseña de su usuario de dominio.

3. Cree el proxy SSIS, conceda acceso al subsistema SSIS y conceda permiso sobre el proxy a su usuario de dominio. Vea el siguiente código:

(SQL)

USE [msdb]

GO

EXEC msdb.dbo.sp_add_proxy @proxy_name=N'SSIS_Proxy',@credential_name=N'SSIS_Credential',@description=N''

EXEC msdb.dbo.sp_grant_login_to_proxy @proxy_name=N'SSIS_Proxy',@login_name=N'mydomain\user_name’

EXEC msdb.dbo.rds_sqlagent_proxy @task_type='GRANT_SUBSYSTEM_ACCESS',@proxy_name='SSIS_Proxy',@proxy_subsystem='SSIS'

GO

Reemplace 'mydomain\user_name' con su usuario de dominio.

4. Agregue un trabajo del Agente SQL Server para la ejecución del paquete, utilice el usuario del dominio como propietario del trabajo.

Figura 9

5. Agregue un paso que ejecute un paquete SSIS, asegurándose de lo siguiente:

• • a. Modifique el paso para ejecutar (Run as) como el proxy creado anteriormente.
  • b. Utilice DB_instance_name.fully_qualified_domain_name como servidor. Por ejemplo, myssisinstance.corp-ad.example.com, donde myssisinstance es el nombre de la instancia de base de datos y corp-ad.example.com es el FQDN.
  • c. Especifique el paquete que le gustaría ejecutar en este paso.

Figura 10

Conclusiones

Esta publicación mostró cómo configurar y usar SSIS en instancias de base de datos de SQL Server 2016 y 2017 nuevas y existentes que se ejecutan en Amazon RDS. También aprendió sobre la compatibilidad, las dependencias y las limitaciones de ejecutar SSIS para RDS SQL Server. Con la disponibilidad de SSIS en RDS, puede ejecutar SSIS en RDS nuevo o existente para SQL Server sin tener que alojar una instancia separada de SSIS en Amazon EC2 o localmente. Pruebe SSIS en RDS para SQL Server hoy y comparta sus pensamientos y preguntas en los comentarios.

Autores

Sumit Ahluwalia es Ingeniero de Desarrollo de Software en Amazon Web Services. Trabaja con el equipo de Amazon RDS, centrándose en motores de bases de datos comerciales y SQL Server. Le gusta trabajar en desafíos técnicos en Amazon RDS y le apasiona aprender y compartir conocimientos con sus compañeros de equipo y clientes de AWS.

Chinni Bolapati es Ingeniero de Bases de Datos Senior en Amazon Web Services. Trabaja en el equipo de Amazon RDS, enfocándose en motores de bases de datos comerciales, SQL Server y Oracle.

Garry Singh es Arquitecto de Soluciones en Amazon Web Services. Trabaja con los clientes de AWS para proporcionar orientación y asistencia técnica para ayudarlos a lograr el mejor resultado para las cargas de trabajo de Microsoft en AWS.

Luis Gerardo Baeza es Arquitecto de Soluciones en Amazon Web Services AWS y tiene 10 años de experiencia en transformación de procesos, arquitectura empresarial, adopción de metodologías ágiles e integración de tecnología cloud. Luis ha ayudado a empresas del sector salud, financiero y educativo en México y Chile.