Cómo realizar un seguimiento automático de los cambios en el esquema de la base de datos mediante scripts de PowerShell

En este artículo, encontrará una guía detallada sobre cómo monitorear automáticamente una base de datos de SQL Server en busca de cambios de esquema y generar automáticamente un informe y un archivo de registro. Se dan ejemplos de escenarios para configurar un proceso.

La automatización del seguimiento de cambios en el esquema de la base de datos aumenta los lanzamientos de aplicaciones y agrega un cierto nivel de agilidad al desarrollo de la base de datos de una organización. Esta práctica facilita la vida de los desarrolladores y administradores de bases de datos (DBA) y mejora su rendimiento.

Administrar cambios en la base de datos no es fácil. Una base de datos no es una simple colección de archivos o fragmentos de código, y no puede volver a una versión anterior para corregir un error como cuando se desarrollan aplicaciones. Los cambios en la base de datos son sin duda la parte más riesgosa de cualquier actualización de la aplicación y, a medida que la base de datos crece, los administradores de la base de datos comienzan a vigilarla de cerca para evitar problemas inesperados. Por eso es tan importante automatizar la supervisión de los cambios en el esquema de la base de datos, para ahorrar tiempo y energía al equipo de la base de datos.

En un entorno multiusuario, para minimizar los riesgos, es vital monitorear periódicamente los cambios realizados en los esquemas de la base de datos. ¿Es posible automatizar el proceso?

Índice

    Cómo configurar el seguimiento automático de los cambios en el esquema de la base de datos

    requisitos previos

    Para monitorear la base de datos de SQL Server en busca de cambios de esquema, usaremos la herramienta de comparación de esquemas dbForge, que es parte del kit de herramientas de SQL, el archivo de configuración personalizado y los scripts de PowerShell.

    La lógica del proceso.

    Paso 1.
    El script personalizado de PowerShell ejecuta dbForge SQL Schema Compare, crea una instantánea de la base de datos y la coloca en D: Monitor Línea base carpeta.

    Paso 2.
    Después de un intervalo de tiempo específico, se ejecuta otro script de PowerShell e inicia una comparación de esquemas entre la base de datos actual y la instantánea.

    Paso 3.
    Si se encuentran diferencias en el esquema, se crea una nueva instantánea con la fecha y hora actuales y se coloca en D: Monitor DiffSnapshots carpeta, el informe se crea y se coloca en D:MonitorDiffReports carpeta, y en el archivo de registro se crea un archivo de registro con el texto: "Las bases de datos son diferentes". D:MonitorRevistas carpeta.

    Si no hay diferencias en los esquemas, se devuelve el código de salida 100 y, en consecuencia, no se generan una nueva instantánea ni un informe, sino solo un archivo de registro con el texto: "Las bases de datos son idénticas". D:MonitorRevistas carpeta para que el DBA pueda estar seguro de que el proceso se está ejecutando.

    Consulte la imagen a continuación para comprender mejor la lógica del proceso.

    Implementación

    Crear un archivo de configuración

    En el archivo de configuración D:MonitorBases de datos.txt, debe especificar el nombre del servidor, el nombre de la base de datos, el tipo de autenticación, el inicio de sesión y la contraseña para crear la instantánea y la base de datos de destino para la comparación. Por ejemplo:

    JORDANSMSSQLSERVER2019,AdventureWorks2019_P1,,login,password

    Nuestro archivo de configuración se ve así cuando usamos la autenticación de Windows para conectarnos a SQL Server.

    JORDANSMSSQLSERVER2019,AdventureWorks2019_P1,true,,

    Cree un script de PowerShell personalizado para crear una instantánea de la base de datos

    #region Variables
    
    $rootFolder = "D:Monitor"
    $databasesTxtPath = "D:MonitorDatabases.txt"
    $diffToolLocation = "C:Program FilesDevartdbForge SQL Tools ProfessionaldbForge Schema Compare for SQL Serverschemacompare.com"
    
    #endregion
    
    foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
       # Read the connection parameters for the current Base de datos from the configuration file
       $server = ($line -split ",")[0]
       $Base de datos = ($line -split ",")[1]
       $isWindowsAuthentication = [boolean]($line -split ",")[2]
       $userName = ($line -split ",")[3]
       $password = ($line -split ",")[4]
        
       $BaselineLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "" + "BaseLine") 
        
       $srvCleanName = ($server -replace "\", "")
       $currentSnapshotFile = Join-Path $BaselineLocation "$srvCleanName.$Base de datos.snap"    
        
            
       # Create Base de datos connection
       if ($isWindowsAuthentication) {
          $connection = New-DevartSqlDatabaseConnection -Server $server -Base de datos $Base de datos -WindowsAuthentication $true       
       }
       else {
          $connection = New-DevartSqlDatabaseConnection -Server $server -Base de datos $Base de datos -UserName $userName -Password $password
       }
    
       # Test Base de datos connection
       Write-Host "Pruebas the Base de datos connection..."
       $result = Test-DevartDatabaseConnection -Connection $connection;
       if (!$result) {
          Write-Error "Cannot connect to the Base de datos (Server: $server; Base de datos: $Base de datos)"
          continue
       }
       
          
       Write-Host "Creating a snapshot for the Server: $server; Base de datos: $Base de datos"
          
       # Create a snapshot
       Start-Process -FilePath $diffToolLocation -ArgumentList "/snapshot /connection:`"$connection`" /Base de datos:$Base de datos /file:`"$currentSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden        
    }

    Puede ejecutar el script manualmente, programarlo para que se ejecute o usarlo en su CI.

    Después de que el script se ejecute con éxito, se creará una nueva instantánea en el archivo D: Monitor Línea base carpeta.

    Cree un script de PowerShell personalizado para ejecutar una comparación entre la instantánea y la base de datos de destino

    #region Variables
    
    $rootFolder = "D:Monitor"
    $databasesTxtPath = "D:MonitorDatabases.txt"
    $diffToolLocation = "C:Program FilesDevartdbForge SQL Tools ProfessionaldbForge Schema Compare for SQL Serverschemacompare.com"
    
    #endregion
    
    
    foreach ($line in [System.IO.File]::ReadAllLines($databasesTxtPath)) {
       # Read the connection parameters for the current Base de datos from the configuration file
       $server = ($line -split ",")[0]
       $Base de datos = ($line -split ",")[1]
       $isWindowsAuthentication = [boolean]($line -split ",")[2]
       $userName = ($line -split ",")[3]
       $password = ($line -split ",")[4]
       $today = (Get-Date -Format "dd-MM-yyyy_HH_MM_ss")
       $BaselineLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "" + "BaseLine") 
       $DiffsnapshotsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "" + "DiffSnapshots") 
       $ReportsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "" + "DiffReports") 
       $logsLocation = New-Item -ItemType Directory -Force -Path ($rootFolder + "" + "Logs") 
    
       $srvCleanName = ($server -replace "\", "")
       $currentSnapshotFile = Join-Path $BaselineLocation "$srvCleanName.$Base de datos.snap"
       $currentReportFile = Join-Path $ReportsLocation "$srvCleanName.$Base de datos.$today"
       $logName = Join-Path $logsLocation "$srvCleanName.$Base de datos.$today.txt"
       $diffSnapshotFile = Join-Path $DiffsnapshotsLocation "$srvCleanName.$Base de datos.$today.snap"
                        
    
       Write-Host "Server: $server; Base de datos: $Base de datos; isWindowsAuthentication: $isWindowsAuthentication"
    
        
       # Create Base de datos connection
       if ($isWindowsAuthentication) {
          $connection = New-DevartSqlDatabaseConnection -Server $server -Base de datos $Base de datos -WindowsAuthentication $true       
       }
       else {
          $connection = New-DevartSqlDatabaseConnection -Server $server -Base de datos $Base de datos -UserName $userName -Password $password
       }
    
       # Test Base de datos connection
       Write-Host "Pruebas the Base de datos connection..."
       $result = Test-DevartDatabaseConnection -Connection $connection;
       if (!$result) {
          Write-Error "Cannot connect to the Base de datos (Server: $server; Base de datos: $Base de datos)"
          continue
       }
           
        
       # Log information about checking the Base de datos 
       New-Item -ItemType File -Force -Path $logName
    
       # Initiate the comparison of the current snapshot with the Base de datos and generate a report   
       $process = Start-Process -FilePath $diffToolLocation -ArgumentList "/schemacompare /source snapshot:`"$currentSnapshotFile`" /target connection:`"$connection`" /report:`"$currentReportFile`" /reportformat:html" -PassThru -Wait -windowstyle hidden 
    
             
       # Return exit code 100 in case the databases are identical
       if ($process.ExitCode -eq 100) {
          Add-Content -Path $logName -Value "The databases are identical"
          #remove the newly created report, since no differences are detected
          Remove-Item -Path $currentReportFile".html" -Force:$true -Confirm:$false
          continue
       }
       else {
          Add-Content -Path $logName -Value "The databases are different"
          # Generate a new snapshot in case there are differences detected      
          Start-Process -FilePath $diffToolLocation -ArgumentList "/snapshot /connection:`"$connection`" /Base de datos:$Base de datos /file:`"$diffSnapshotFile`" /compress:No" -PassThru -Wait -windowstyle hidden 
       }
    }
    

    Puede ejecutar el script manualmente, programarlo para que se ejecute o usarlo en su CI.

    Si el script tiene éxito, se crea una nueva instantánea en el archivo D: Monitor DiffSnapshots carpeta, y se crea un informe diff en el archivo D:MonitorDiffReports carpeta.

    flujo de trabajo

    El administrador de la base de datos monitorea todas las mañanas D:MonitorRevistas y D: Monitor DiffSnapshots carpetas y, en consecuencia, puede ver si ha habido algún cambio.
    Si ha habido cambios, el archivo tiene un archivo de instantánea recién creado D: Monitor DiffSnapshots carpeta. A continuación, el administrador puede comprobar D:MonitorDiffReports una carpeta para ver y analizar las diferencias.
    El administrador de la base de datos también puede comparar la instantánea base con D: Monitor Línea base una carpeta y una instantánea que se crearon automáticamente y se colocaron en un archivo D: Monitor DiffSnapshots carpeta.

    De una manera tan simple pero elegante, puede usar dbForge Schema Compare para SQL Server para automatizar el proceso de detección y seguimiento de cambios en la base de datos.

    Escalada

    El ejemplo propuesto para automatizar el seguimiento de los cambios en el esquema de la base de datos se puede escalar a varias bases de datos. Simplemente realice cambios en el archivo de configuración: agregue las bases de datos para las que desea realizar un seguimiento de los cambios de esquema.

    JORDANSMSSQLSERVER2019,AdventureWorks2019_P1,true,,
    JORDANSMSSQLSERVER2019,AdventureWorks2019_P2,true,,
    JORDANSMSSQLSERVER2019,AdventureWorks2019_P3,true,,

    Conclusión

    Este artículo proporciona una forma sencilla de automatizar las tareas de comparación de esquemas de bases de datos de SQL Server que se pueden escalar a varias bases de datos.

    Cabe señalar que la función de comparación de esquemas también está disponible en dbForge Studio para SQL Server, nuestro IDE universal que cubre casi todos los aspectos del desarrollo, la gestión y la administración de bases de datos de SQL Server. Para realizar un seguimiento automático de los cambios en el esquema de la base de datos de SQL Server, puede utilizar dbForge Schema Compare para SQL Server o dbForge Studio para SQL Server; simplemente elija la herramienta que mejor se adapte a sus necesidades.

    Los productos de Devart vienen con una prueba gratuita de 30 días. Descargue dbForge Studio para SQL Server o dbForge Schema Compare para SQL Server y asegúrese de que la automatización del seguimiento de cambios de esquema sea fácil e indolora.

    Nota
    Para dar a nuestros clientes más opciones, presentamos dbForge Schema Compare para SQL Server como parte de dos conjuntos de herramientas diferentes: Paquete de comparación de dbForge para SQL Server (un paquete de dos herramientas necesarias para la comparación) y Herramientas SQL de dbForge (paquete de 15 herramientas avanzadas para el desarrollo, la gestión y la administración de bases de datos): simplemente elija un conjunto de herramientas que cumpla con los requisitos de su proyecto.

    Artículos de interés

    Subir