Cómo construir un diccionario de Datos SQL Server “inteligente”

En este artículo, “¿Qué es un diccionario de datos y por qué quisiera construir uno?” un diccionario de datos fue descrito y comparado a otras alternativas para documentar, auditar y versionar una base de datos. Este artículo también describió la diferencia entre un diccionario de datos “Tonto” y uno “Inteligente”.

Ahora que le hemos abierto el apetito, este artículo explicará cómo crear un diccionario de datos inteligente usando exportaciones de cambios de esquema XML desde ApexSQL Diff.

El diccionario de Datos puede ser usado después para varias cosas, como consultar para ver el historial completo de cambios de objetos, o crear exportaciones agregadas mostrando estadísticas de cambios en la base de datos, que serán cubiertas en un artículo separado.

Requerimientos

  • SQL Server 2005 y superiores.
  • ApexSQL Diff edición Profesional (todas estas tareas requieren la Interfaz de Línea de Comandos, CLI). Version 2016.02.0383 or higher is required
  • Seguridad integrada para la conectividad de la base de datos. Para la autenticación SQL Server, el script puede ser modificado para usar credenciales encriptadas almacenadas en el proyecto en sí mismo. Aprenda más acerca de las diferentes opciones para administrar credenciales de bases de datos aqui
  • Un archivo de configuración (config.xml), que provea el nombre del SQL Server y la Base de Datos donde el diccionario de Datos está localizado, y que también provea el SQL Server y el nombre de la Base de Datos cuyos cambios estamos rastreando. El archivo de configuración debe ser localizado en el mismo directorio que el script PowerShell.

Configuración

Configurando la línea base

El primer paso en el proceso de crear un diccionario de Datos es configurar las fuentes de datos. Para crear un diccionario de Datos “inteligente”, nuestro diccionario debe ser “consciente de cambios”, de modo que sólo escriba cambios (objetos nuevos, actualizados y eliminados). Para hacer esto, necesitaremos establecer (y reiniciar periódicamente) la línea base y compararla a nuestra base de datos real.

Para lograr esto, usaremos las instantáneas de esquema de ApexSQL, un archivo ligero y propietario que contiene un esquema completo de base de datos. Las instantáneas pueden ser creadas directamente desde ApexSQL Diff y pueden ser usadas por ApexSQL Diff para comparar una base de datos para producir una exportación de diferencias, lo cual estaremos usando más adelante en el artículo.

Crearemos una instantánea inicial y la grabaremos en la ruta Raíz (como se describió en el archivo de Configuración) usando ApexSQL Diff. Todo esto será hecho por el script de PowerShell, el cual administrará las instantáneas de línea base por nosotros.

Esta instantánea servirá como nuestra línea base inicial. Cada vez que cambios son descubiertos entre la instantánea de la línea base y la base de datos en la que estamos trabajando, reemplazaremos nuestra instantánea de línea base con una nueva versión, de modo que pueda ser usada como línea base en la siguiente comparación para revisar cualquier cambio.

  • Día 1: Instantánea de línea base = Base de datos (sin cambios)
  • Día 2: Instantánea de línea base = Base de datos (sin cambios)
  • Día 3: Instantánea de línea base ˛< Base de datos (cambios)

    • Escriba una nueva versión de la base de datos (cambios sólo al diccionario de datos)
    • Cree una nueva línea base usando la nueva versión de la base de datos

  • Día 4: Instantánea de línea base versión 2 = Nueva versión de la base de datos (sin cambios)
  • etc

Crear el repositorio del diccionario de datos

La siguiente parte es la creación del repositorio, el cual existirá como una tabla de base de datos en SQl Server. El repositorio lleno con los datos desde la exportación XML se volverá nuestro diccionario de datos.

Así que, creemos una base de datos que será usada como repositorio. Decidimos llamarlo “DataDictionary”. Luego, crearemos la tabla del mismo nombre para almacenar los datos en la información del diccionario de Datos (el script para esta tabla puede ser encontrado en el Apéndice A).

El siguiente paso es crear un procedimiento almacenado que será usado para llenar el diccionario de datos. El procedimiento extraerá los datos desde la exportación de diferencias de esquema exportada en XML, creará una tabla XML temporal con los datos desde la exportación XML, luego los datos serán extraídos desde la tabla XML a la tabla DataDictionary que creamos antes.

El procedimiento almacenado FillDataDictionary es creado donde está definido que se cree una tabla temporal XML, con todos los datos desde la exportación XML. Luego, todos los datos desde la tabla temporal XML son decodificados y colocados en la tabla DataDictionary. Una vez que esto es hecho, la tabla temporal es eliminada (el script para este procedimiento puede ser encontrado en el Apéndice B).

Ajustes de configuración

Ahora que nuestra infraestructura de diccionario de datos ha sido creada, necesitaremos establecer los ajustes de configuración para determinar:

  • El SQL Server y el nombre de la base de datos cuyos cambios vamos a rastrear.
  • El SQL Server y el nombre de la base de datos de repositorio para el Diccionario de Datos.

Crearemos esto como un archivo XML llamado config.xml y lo pondremos en el mismo directorio que el script PowerShell. El script PowerShell abrirá ese archivo de configuración y lo leerá para obtener la información de configuración necesaria para ejecutar exitosamente el trabajo de carga del diccionario de Dataos, cada vez que corre.

Ejecución

Una vez que el repositorio de la base de datos, la tabla DataDictionary y FillDataDictionary son creados y usted ha editado el archivo de configuración, está listo para seguir adelante. El script PowerShell que creamos puede ser simplemente ejecutado para crear el primer lote de registros en nuestro diccionario de datos.

El script puede ser programado para correr sin supervisión, cada noche a las 12:00 AM por ejemplo, para asegurar que su diccionario de datos es continuamente actualizado.

  1. Descargue los archivos requeridos para crear el diccionario de datos.
  2. Edite el archivo config.xml para (vea un ejemplo en el Apéndice C – Archivo de configuración)

    1. Especificar el nombre y el nombre del servidor de la Base de Datos para la que desea crear el diccionario.
    2. Especifique el nombre y el nombre del servidor del repositorio de DataDictionary.
  3. Corra el script SQL adjunto [create_data_dictionary_schema.sql] para crear la base de datos del repositorio, la tabla y cargar el procedimiento almacenado en el SQL Server que usted especifique.
  4. Correr el script PowerShell para capturar el estado inicial de su base de datos.

Prueba

Luego, haga un cambio a un objeto en la base de datos, añada un nuevo objeto, etc.

Ahora corra el script PowerShell de nuevo. Usted debería ver un registro por cada cambio cuando usted consulta su base de datos de diccionario de datos.

Para consultar su diccionario de datos, use este SQL:

SELECT *
FROM [DataDictionary].[dbo].[DataDictionary]

Programando para una ejecución sin supervisión

Una vez que todo esté corriendo bien, programe un trabajo para correr este trabajo sin supervisión. Vea este enlace para información acerca de cómo hacer esto: Cómo automatizar y programar una ejecución CLI con Trabajos de SQL Server

En el siguiente artículo en esta serie, mostraremos cómo aprovechar su diccionario de datos creado para auditar, controlar versiones y agregar reportes acerca de datos transaccionales.

Descargas

Por favor descargue los scripts asociados con este artículo en nuestro repositorio GitHub

Por favor contáctenos para cualquier problema o pregunta con los scripts.

Apéndice A – el script de creación de repositorio

-- Create database [DataDictionary]
IF (NOT EXISTS(SELECT * FROM sys.databases WHERE name='DataDictionary'))
BEGIN
	CREATE DATABASE [DataDictionary]
END
GO

USE [DataDictionary]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- Create table [dbo].[DataDictionary]
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID (N'[dbo].[DataDictionary]'))
BEGIN
	CREATE TABLE DataDictionary
	(
		ID int primary key identity(1,1),
		ChangeDate [datetime],
		[Server] [varchar](128),
		[Database] [varchar](128), 
		ObjectType [varchar](50) ,
		Name [varchar](128) ,
		Owner [varchar](128) ,
		ObjectID [int],
		DiffCode [bit] , 
		DiffType [char](2) ,
		DiffANSI [varchar](5) ,
		DiffAssembly [varchar](5) ,
		DiffAssemblyClass [varchar](5) ,
		DiffAssemblyMethod [varchar](5) ,
		DiffBaseType [varchar](5) ,
		DiffBody [varchar](5) ,
		DiffBoundDefault [varchar](5) ,
		DiffBoundDefaults [varchar](5) ,
		DiffBoundRule [varchar](5) ,
		DiffBoundRules [varchar](5) ,
		DiffChangeTracking [varchar](5) ,
		DiffCheckConstraints [varchar](5) ,
		DiffCLRName [varchar](5) ,
		DiffColumnOrder [varchar](5) ,
		DiffColumns [varchar](5) ,
		DiffDataspace [varchar](5) ,
		DiffDefaultConstraints [varchar](5) ,
		DiffDefaultSchema [varchar](5) ,
		DiffDurability [varchar](5) ,
		DiffExtendedProperties [varchar](5) ,
		DiffFiles [varchar](5) ,
		DiffForeignKeys [varchar](5) ,
		DiffFulltextIndex [varchar](5) ,
		DiffIdentities [varchar](5) ,
		DiffIndexes [varchar](5) ,
		DiffLockEscalation [varchar](5) ,
		DiffManifestFile [varchar](5) ,
		DiffMemoryOptimized [varchar](5) ,
		DiffNullable [varchar](5) ,
		DiffOwner [varchar](5) ,
		DiffParameters [varchar](5) ,
		DiffPermissions [varchar](5) ,
		DiffPermissionSet [varchar](5) ,
		DiffPrimaryKey [varchar](5) ,
		DiffReturnType [varchar](5) ,
		DiffScale [varchar](5) ,
		DiffSize [varchar](5) ,
		DiffStatistics [varchar](5) ,
		DiffUnique [varchar](5) ,
		DiffUserLogin [varchar](5) ,
		DiffXMLColumnSet [varchar](5) ,
		DiffXMLIndexes [varchar](5) ,
		DDL [nvarchar] (max)
	)
END
GO

Apéndice B – el script de procedimiento almacenado para cargar datos

-- Create stored procedure [dbo].[FillDataDictionary]
CREATE PROCEDURE [dbo].[FillDataDictionary] @xmlLocation VARCHAR(150)
AS
BEGIN
	DECLARE @COMMAND NVARCHAR(MAX)

	SET @COMMAND = N'SELECT CONVERT(XML, BulkColumn) AS XMLData
	INTO ##XMLwithOpenXML
	FROM OPENROWSET(BULK ''' + @xmlLocation + ''', SINGLE_BLOB) AS x';

	EXEC sp_executesql @COMMAND

	DECLARE @XML AS XML
		,@hDoc AS INT
		,@SQL NVARCHAR(MAX)

	SELECT @XML = XMLData
	FROM ##XMLwithOpenXML

	EXEC sp_xml_preparedocument @hDoc OUTPUT
		,@XML

	DROP TABLE ##XMLwithOpenXML

	INSERT INTO DataDictionary
	SELECT GETDATE() AS ChangeDate
		,[Server]
		,[Database]
		,[ObjectType]
		,[Name]
		,[Owner]
		,[ObjectID]
		,[DiffCode]
		,[DiffType]
		,[DiffANSI]
		,[DiffAssembly]
		,[DiffAssemblyClass]
		,[DiffAssemblyMethod]
		,[DiffBaseType]
		,[DiffBody]
		,[DiffBoundDefault]
		,[DiffBoundDefaults]
		,[DiffBoundRule]
		,[DiffBoundRules]
		,[DiffChangeTracking]
		,[DiffCheckConstraints]
		,[DiffCLRName]
		,[DiffColumnOrder]
		,[DiffColumns]
		,[DiffDataspace]
		,[DiffDefaultConstraints]
		,[DiffDefaultSchema]
		,[DiffDurability]
		,[DiffExtendedProperties]
		,[DiffFiles]
		,[DiffForeignKeys]
		,[DiffFulltextIndex]
		,[DiffIdentities]
		,[DiffIndexes]
		,[DiffLockEscalation]
		,[DiffManifestFile]
		,[DiffMemoryOptimized]
		,[DiffNullable]
		,[DiffOwner]
		,[DiffParameters]
		,[DiffPermissions]
		,[DiffPermissionSet]
		,[DiffPrimaryKey]
		,[DiffReturnType]
		,[DiffScale]
		,[DiffSize]
		,[DiffStatistics]
		,[DiffUnique]
		,[DiffUserLogin]
		,[DiffXMLColumnSet]
		,[DiffXMLIndexes]
		,[DDL]
	FROM OPENXML(@hDoc, 'root/*/*')
WITH(
	ObjectType [varchar](50) '@mp:localname'
	,[Server] [varchar](50) '../../Server1'
	,[Database] [varchar](50) '../../Database1'
	,NAME [varchar](50) 'Name'
	,OWNER [varchar](50) 'Owner1'
	,ObjectID [int] 'ObjectID1'
	,DiffCode [bit] 'Diff_Code'
	,DiffType [char](2) 'DiffType'
	,DiffANSI [varchar](5) 'DiffANSI'
	,DiffAssembly [varchar](5) 'DiffAssembly'
	,DiffAssemblyClass [varchar](5) 'DiffAssemblyclass'
	,DiffAssemblyMethod [varchar](5) 'DiffAssemblymethod'
	,DiffBaseType [varchar](5) 'DiffBasetype'
	,DiffBody [varchar](5) 'DiffBody'
	,DiffBoundDefault [varchar](5) 'DiffBounddefault'
	,DiffBoundDefaults [varchar](5) 'DiffBounddefaults'
	,DiffBoundRule [varchar](5) 'DiffBoundrule'
	,DiffBoundRules [varchar](5) 'DiffBoundrules'
	,DiffChangeTracking [varchar](5) 'DiffChangetracking'
	,DiffCheckConstraints [varchar](5) 'DiffCheckconstraints'
	,DiffCLRName [varchar](5) 'DiffCLRname'
	,DiffColumnOrder [varchar](5) 'DiffColumnorder'
	,DiffColumns [varchar](5) 'DiffColumns'
	,DiffDataspace [varchar](5) 'DiffDataspace'
	,DiffDefaultConstraints [varchar](5) 'DiffDefaultconstraints'
	,DiffDefaultSchema [varchar](5) 'DiffDefaultschema'
	,DiffDurability [varchar](5) 'DiffDurability'
	,DiffExtendedProperties [varchar](5) 'DiffExtendedproperties'
	,DiffFiles [varchar](5) 'DiffFiles'
	,DiffForeignKeys [varchar](5) 'DiffForeignkeys'
	,DiffFulltextIndex [varchar](5) 'DiffFulltextindex'
	,DiffIdentities [varchar](5) 'DiffIdentities'
	,DiffIndexes [varchar](5) 'DiffIndexes'
	,DiffLockEscalation [varchar](5) 'DiffLockescalation'
	,DiffManifestFile [varchar](5) 'DiffManifestfile'
	,DiffMemoryOptimized [varchar](5) 'DiffMemoryoptimized'
	,DiffNullable [varchar](5) 'DiffNullable'
	,DiffOwner [varchar](5) 'DiffOwner'
	,DiffParameters [varchar](5) 'DiffParameters'
	,DiffPermissions [varchar](5) 'DiffPermissions'
	,DiffPermissionSet [varchar](5) 'DiffPermissionset'
	,DiffPrimaryKey [varchar](5) 'DiffPrimarykey'
	,DiffReturnType [varchar](5) 'DiffReturntype'
	,DiffScale [varchar](5) 'DiffScale'
	,DiffSize [varchar](5) 'DiffSize'
	,DiffStatistics [varchar](5) 'DiffStatistics'
	,DiffUnique [varchar](5) 'DiffUnique'
	,DiffUserLogin [varchar](5) 'DiffUserlogin'
	,DiffXMLColumnSet [varchar](5) 'DiffXMLcolumnset'
	,DiffXMLIndexes [varchar](5) 'DiffXMLindexes'
	,DDL [nvarchar](max) 'SourceDDL'
	) EXEC sp_xml_removedocument @hDoc END

Apéndice C – archivo de configuración

<config>
        <! —- Server name where the target database is placed -->
        <Server>(local)</Server>
        <! —-Target database name which changes will be tracked --> 
        <Database>AdventureWorks2014</Database>
	<! —-Server name where the data dictionary repository will be placed -->
	<DataDictionaryServer>(local)</DataDictionaryServer>
        <!—-Name of the data dictionary repository (database) -->
	<DataDictionaryDatabaseName>DataDictionary</DataDictionaryDatabaseName>
</config>

Apéndice D – el script PowerShell

#find the Snapshot file which has the highest value for the "created date" parameter
function FindSnapshotByDate($folder)
{
    #find all files whose name ends with .axsnp
    $Files = Get-ChildItem -Path $folder -Filter "*.axsnp"
    if ($Files.Length -eq 0)
    {
        #if no such file is found, then that means that there isn't any snapshot previously created
        return $null
    }
    
    $mostRecentFile = $Files | Sort-Object -Property "CreationTime" -Descending | Select-Object -First 1
    return $mostRecentFile.FullName
}

#check the existance of Exports, Logs or Snapshot folders, creates it if it is not created and returns the path
function CheckAndCreateFolder($rootFolder, [switch]$Exports, [switch]$Baselines, [switch]$Logs)
{
    $location = $rootFolder

    #set the location based on the used switch
    if($Exports -eq $true)
    {
        $location += "\Exports"
    }
    if($Baselines -eq $true)
    {
        $location += "\Snapshots"
    }
    if($Logs -eq $true)
    {
        $location += "\Logs"
    }
    
    #create the folder if it doesn't exist and return its path
    if(-not (Test-Path $location))
    { mkdir $location -Force:$true -Confirm:$false | Out-Null }

    return $location
}

#insert schema difference records into the datadictionary table
function InsertRecordsToDataDictionaryDatabase($dataDictionaryServer, $dataDictionaryDbName, $xmlExportFullPath)
{
    $SqlConnection = New-Object System.Data.SqlClient.SqlConnection
   
    $SqlConnection.ConnectionString = "Server=$dataDictionaryServer;Initial catalog=$dataDictionaryDbName;Trusted_Connection=True;"

    try
    {
       $SqlConnection.Open()
       $SqlCommand = $SqlConnection.CreateCommand()
       $SqlCommand.CommandText = "EXEC [dbo].[FillDataDictionary] '$xmlExportFullPath'"

       $SqlCommand.ExecuteNonQuery() | out-null
    }
    catch
    {
        Write-Host "FillDataDictionary could not be executed`r`nException: $_"
    }
}

#################################################################################                                        

#read and parse config.xml file from root folder
[xml]$configFile = Get-Content config.xml

$server               = $configFile.config.Server
$database             = $configFile.config.Database
$dataDictionaryServer = $configFile.config.DataDictionaryServer
$dataDictionaryDbName = $configFile.config.DataDictionaryDatabaseName
$rootFolder           = $PSScriptRoot

#check if ApexSQLDiff is installed
$DiffInstallPath = Get-ItemPropertyValue -Path "HKLM:\SOFTWARE\Microsoft\Windows\CurrentVersion\Uninstall\ApexSQL Diff*" -Name "InstallLocation"
if(-not $DiffInstallPath)
{
    #ApexSQL Diff installation not found. Please install ApexSQL Diff to continue
}

$diffLocation      = $DiffInstallPath + "ApexSQLDiff.com"
  
$snapshotsLocation = CheckAndCreateFolder $rootFolder -Baselines
$exportLocation    = CheckAndCreateFolder $rootFolder -Exports
$logLocation       = CheckAndCreateFolder $rootFolder -Logs

$today          = (Get-Date -Format "MMddyyyy")
$latestSnapshot = FindSnapshotByDate $snapshotsLocation

$snapshotName = "SchemaSnapshot_$today.axsnp"
$logName      = "SnapshotLog_$today.txt"
$xml          = "SchemaDifferenceExport_$today.xml"

$initialCompare          = "/s1:""$server"" /d1:""$database"" /s2:""$server"" /d2:""$database"" /ot:x /xeo:e is /on:""$exportLocation\$xml"" /f /v"
$compareSettingsSnapshot = "/s1:""$server"" /d1:""$database"" /sn2:""$latestSnapshot"" /out:""$logLocation\$logName"" /rece /f /v"
$exportSettingsSnapshot  = "/s1:""$server"" /d1:""$database"" /sn2:""$snapshotsLocation\$snapshotName"" /export /f /v"
$diffExportXMLparams     = "/s1:""$server"" /d1:""$database"" /sn2:""$latestSnapshot"" /ot:x /xeo:d s t is /on:""$exportLocation\$xml"" /f /v" 

#if no previous snapshot found, create snapshot for current state and skip the rest
if($latestSnapshot -eq $null)  
{
     #put initial state of current database in datadictionary
     (Invoke-Expression ("& `"" + $diffLocation +"`" " +$initialCompare))
     PutRecordsToDataDictionaryDatabase $dataDictionaryServer $dataDictionaryDbName $exportLocation\$xml

     #create snapshot of current database state
     (Invoke-Expression ("& `"" + $diffLocation +"`" " +$exportSettingsSnapshot))

     Write-Host "Snapshot is not found in the '$snapshotsLocation' folder.`r`n`r`nInitial snapshot has been automatically created and named '$snapshotName'"
     
     #here, add the comparison against empty datasource
     continue
}

 #compare the database with latest snapshot
 (Invoke-Expression ("& ""$diffLocation"" $compareSettingsSnapshot"))
 $returnCode = $LASTEXITCODE

 #differences detected
 if($returnCode -eq 0)
 {
    #Export differences into XML file
    (Invoke-Expression ("& ""$diffLocation"" $diffExportXMLparams"))
    #Add timestamp on each line of log file
    $tsOutput | ForEach-Object { ((Get-Date -format "MM/dd/yyyy hh:mm:ss") + ":  $_") >> $file }
 
    PutRecordsToDataDictionaryDatabase $dataDictionaryServer $dataDictionaryDbName $exportLocation\$xml 
    
    #create snapshot of current database state
    (Invoke-Expression ("& `"" + $diffLocation +"`" " +$exportSettingsSnapshot))
 }
 #there are no differences or an error occurred
 else
 {
     #an error occurred
     if($returnCode -ne 102)
     {
         Write-Host "An error occurred during the application execution.`r`nError code: $returnCode"
         continue
     }
 }

Referencias:

julio 25, 2017