SQL Server Data Tools (SSDT) – Detección de diferencias

*Actualizado a SQL Server 2014 y Visual Studio 2013

Continuando con esta serie de artículos sobre SQL Server Data Tools (SSDT), hablaremos sobre las detecciones de diferencias que se encuentra en el grupo de desarrollo conectado.

Detección de Diferencias

• Drift detection

La base de datos es un objetivo en constante movimiento. Después de la implementación, es bastante común que un DBA ajuste la estructura o aplique parches en la base de datos de producción, por ejemplo la adición de índices en tablas particulares para mejorar el rendimiento de las consultas.

Cuando los ambientes se encuentran fuera de sincronía, la base de datos está en un estado diferente del que usted y su aplicación espera que sea y esas diferencias deben ser identificadas y reconciliadas.

Muchas veces no preguntamos:

• “¿Quién puso las tablas en mi base de datos?”
• “¿Quién cambió esta definición de la vista?”
• “¿Por qué este chico esta en db_owner?”
• “¿Dónde ha ido mi procedimiento almacenado?”

En otras palabras el drift de base de datos puede ser descrito como objetos que aparecen, objetos que se retiran u objetos que se modifican en las bases de datos de producción o cuando se realizan cambios en la base de datos que no están incluidos en el proyecto.

Drift de base de datos en SSDT

Si utiliza SSDT para gestionar la base de datos, entonces probablemente cuenta el código fuente en sus proyectos SSDT y por lo tanto cualquier cosa que aparece en sus bases de datos que no es en el código fuente se considerarían drift de base de datos.

Con el fin de detectar drift de base de datos utilizando SSDT debemos asegurarnos que la base de datos esté registrada como Data-Tier Application (Aplicación de capa de datos). Esto se puede hacer cuando se publica el proyecto de base de datos (es decir dacpac) seleccionando “Registro como una aplicación de capa de datos”.

En el siguiente ejemplo se realizaron las siguientes operaciones:

• Se publicó la base de datos AdventureWorks 2014 en una instancia de pruebas o de producción. Ver Figura 1.
• Se agregó la tabla Test en el esquema dbo en la instancia de pruebas o de producción.
• Se eliminó el procedimiento almacenado uspUpdateEmployeeLogin desde la instancia de pruebas o de producción
• Se modificó los campos  AddressLine1 y AddressLine2 de la tabla Person.Address de nvarchar(60) a nvarchar(100) en la instancia de pruebas o de producción
• Se publicó nuevamente la base de datos en una instancia de pruebas o de producción. Ver Figura 1.
• En el wizard de publicación se seleccionaron las opciones de “Register as a Data-tier Application” y “Block publish when database has drifted from registered version”, y finalmente click en el botón “Publish”. Ver Figura 2.
• Se verifica el resultado de la publicación en la ventana de Data Tools Operations y seleccionar la opción de ver reporte. Ver Figura 3.

Figura 1. – Opción de Publicar en el proyecto de Base de Datos en Visual Studio 2013.

Figura 2. – Wizard de publicación de base de datos.

Figura 3. – Data tools operations.

Una vez damos click en la opción de ver el reporte vemos el siguiente XML donde podemos observar los objetos que se adicionaron, eliminaron y los que se modificaron fuera del proyecto de base de datos.

<?xml version=“1.0“ encoding=“utf-8“?>

<DriftReport xmlns=“http://schemas.microsoft.com/sqlserver/dac/DriftReport/2012/02“>

  <Additions>

    <Object Name=“[Test]“ Parent=“[dbo]“ Type=“SqlTable“ />

  </Additions>

  <Removals>

    <Object Name=“[uspUpdateEmployeeLogin]“ Parent=“[HumanResources]“ Type=“SqlProcedure“ />

    <ExtendedProperty HostName=“[uspUpdateEmployeeLogin]“ HostParent=“[HumanResources]“ HostType=“SqlProcedure“ Count=“1“ />

    <ExtendedProperty HostName=“[@BusinessEntityID]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[@OrganizationNode]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[@LoginID]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[@JobTitle]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[@HireDate]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[@CurrentFlag]“ HostParent=“[HumanResources].[uspUpdateEmployeeLogin]“ HostType=“SqlSubroutineParameter“ Count=“1“ />

    <ExtendedProperty HostName=“[DF_Address_ModifiedDate]“ HostParent=“[Person].[Address]“ HostType=“SqlDefaultConstraint“ Count=“1“ />

    <ExtendedProperty HostName=“[DF_Address_rowguid]“ HostParent=“[Person].[Address]“ HostType=“SqlDefaultConstraint“ Count=“1“ />

  </Removals>

  <Modifications>

    <Object Name=“[AddressLine1]“ Parent=“[Person].[Address]“ Type=“SqlSimpleColumn“ />

    <Object Name=“[AddressLine2]“ Parent=“[Person].[Address]“ Type=“SqlSimpleColumn“ />

    <Object Name=“[Address]“ Parent=“[Person]“ Type=“SqlTable“ />

  </Modifications>

</DriftReport>

Drift de base de datos en SSMS

Para detectar drift de base de datos utilizando SQL Server Management Studio debemos seguir los siguientes pasos:

• Seleccionamos la base de datos donde realizamos los cambios hechos en el ejemplo de SSDT (Adición, Modificación, Eliminación) y damos derecho, seleccionamos la opción de “Task” y posteriormente seleccionamos la opción de “Upgrade Data-tier Application”. Ver Figura 4.
• Inicia el wizard de “Upgrade Data-tier Application” y damos click en el botón Siguiente. Ver Figura 5.
• Seleccionamos el archivo con extención .dacpac y damos click en el botón Siguiente. Ver Figura 6.
• El Wizard de “Upgrade Data-tier Application” inicia el proceso de detección de cambios. Ver Figura 7.
• Finaliza el proceso de detección de cambios el DAC desplegado y la base de datos y damos click en el botón guardar reporte. Ver Figura 8.
• Finalmente vemos el XML con el resultado de la detección de cambios. Ver Figura 8.

Figura 4. – Opción de Upgrade Data-tier Application en SSMS.

Figura 5. – Wizard Udgrade a data-tier application en SSMS.

Figura 6. – Selección del archivo con extensión .dacpac.

Figura 7. – Inicio proceso de detección de cambios.

Figura 8. – Finalización proceso de la detección de cambios entre el DAC desplegado y la base de datos.

Figura 9. – Reporte en XML generado del proceso de detección de cambios entre el DAC desplegado y la base de datos.

Generar un reporte de drift de base de datos desde linea de comandos

El informe de drift se puede generar con la herramienta de sqlpackage.exe de línea de comandos. Para ello es necesario definir:

• La acción del reporte de drift
• Un servidor y base de datos de destino
• Un archivo de salida

> SqlPackage.exe / A: DriftReport / TSN:“(LocalDB) ProjectsV12” / TDN:”AdventureWorks2014” /op:DriftReportAW2012.xml

Figura 10. – Generación del reporte del Drift desde linea de comandos.

• Schema comparison – Comparación de esquemas

La función de comparación de esquemas compara objetos entre el proyecto de base de datos de Visual Studio, un archivo Data-tier Application (DAC) o una base de datos (Servidor o LocalDB). Ver Figura 14.

Se presenta una lista de las diferencias, y se puede aplicar los cambios si lo desea. Para iniciar el proceso de comparación de esquemas podemos hacerlo de las siguientes formas:

• En el Solution Explorer o Exporador de Soluciones damos click derecho sobre el proyecto de base de datos y seleccionamos la opción de comparar esquema. Ver Figura 11.
• En el SQL Server Object Explorer o Explorador de Objetos de SQL Server podemos dar click derecho en un proyecto de base de datos o en una base de datos (Servidor / LocalDB) para que nos aparezca la opción de comparar esquema. Ver Figura 12.

Figura 11. – Opción de comparar esquema en el Explorador de Soluciones.

Figura 12. – Opción de comparar esquema en el Explorador de Objetos SQL Server.

Una vez seleccionamos la opción de comparar esquema Visual Studio nos muestra la ventana para comparar esquemas. Ver Figura 13.

Figura 13. – Ventana de comparación de esquemas.

Lo primero que debemos hacer es seleccionar los dos esquemas que queremos comparar. En este ejemplo primero seleccione el proyecto de base de datos AdventureWorks2014 y posteriormente seleccione la base de datos servidor de AdventureWorks2014-2 que desplegamos en el ejemplo del drift de base de datos. Ver Figura 14.

Figura 14 – venta de selección de esquemas a comparar.

Una vez hemos seleccionado los esquemas que queremos comparar damos click en el botón de comparar y posteriormente se muestra una lista con las diferencias entre los dos esquemas. En este ejemplo veremos diferencias de adición, modificación y eliminación de objetos. Ver Figuras 15, 16 y 17.

Figura 15 – Diferencias de eliminación de objetos.

Figura 16 – Diferencias de modificación de objetos.

Figura 17 – Diferencias de adición de objetos.

Adicionalmente la ventana de comparar esquema nos permite visualizar las diferencias por acción, por esquemas definidos en la base de datos o por tipo de objeto. Ver Figura 18, 19 y 20.

Figura 18 – Diferencias organizadas por acción.

Figura 19 – Diferencias organizadas por esquemas definidos en la base de datos.

Figura 20 – Diferencias organizadas por tipo de objeto.

Una vez analizadas las diferencias entre los esquemas podemos actualizar alguno de los dos esquemas para que estos queden iguales simplemente dando click en el botón Update.

Más artículos:

Introducción

• SQL Server Data Tools (SSDT) – Introducción

Desarrollo Conectado

• SQL Server Data Tools (SSDT) – Experiencia familiar de Usuario

Saludos,

Compartir: