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

Regresar

Comentarios enviados

¿Este artículo solucionó su problema?

Seleccionar calificación

Título

Cómo construir un diccionario de Datos SQL Server “inteligente”
Resolución
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" 
```

Comentarios enviados

¿Este artículo solucionó su problema?

Seleccionar calificación

Solicitar un artículo KB

Contenido recomendado

Producto(s):: ApexSQL Doc

Tema(s):: How To

Historial del artículo:: Creado el: 6/19/2019
Última actualización: 11/25/2024

Buscar todos los artículos

Seleccione su producto:

Con el fin de ofrecerle un mejor servicio, complete el campo Motivo del chat:

Soluciones recomendadas para su problema

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

Título

Resolución

Requerimientos

Configuración

Configurando la línea base

Crear el repositorio del diccionario de datos

Ajustes de configuración

Ejecución

Prueba

Programando para una ejecución sin supervisión

Descargas

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

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

Apéndice C – archivo de configuración

Apéndice D – el script PowerShell

Leave a Comment