Déjeme prologar esta entrada diciendo que este fue un proceso que evolucionó en el tiempo. Inició como una simple sentencia SQL que me enviaba un archivo cvs en un correo electrónico. Desde ahí, el código SQL real evolucionó para mostrar resultados más claros. Cuando supe lo que powershell podía hacer, pasé algo de tiempo aprendiéndolo durante mis almuerzos en el trabajo. Incluso cuando implementé un script powershell por primera vez, aún no era un reporte con aspecto limpio, pero seguía siendo mejor que lo que teníamos previamente. Incluso ahora hay espacio para mejoras, pero estoy feliz con los resultados y espero que pueda proveer algo de ayuda a otros DBAs SQL ahí afuera.
También, en el espíritu de sinceridad total, yo no tengo estudios formales de programación. La primera mitad de mi carrera fue de administrador de sistemas. ¡El hecho de que yo pueda aprender powershell significa que prácticamente cualquiera puede!
Así que, procedamos.
Tenemos una mezcla de sistemas de respaldo para nuestro ambiente SQL variando desde NetApp snap manager, Commvault tape backup, e incluso el clásico SQL Nativo. La razón para esto es que netapp permite respaldos COMPLETOS rápidos en incluso las bases de datos más grandes cada noche, así como copias de seguridad de registros de transacciones. Es muy flexible y hace las restauraciones muy fáciles y rápidas.
La razón para el respaldo commvault tape es que se requiere que tengamos un respaldo semanal completo a cinta fuera de vista por el bien de DR.
Y SQL Nativo es porque no tenemos suficientes licencias de NetApp o Commvault SQL para estos servidores particulares (estos no son considerados críticos para la misión). Por lo tanto, programamos el respaldo SQL nativo para que suceda previamente al rango de respaldo del archivo de sistema.
Ahora que tenemos todo clarificado, lo primero que necesitamos hacer es escribir la sentencia SQL que mostrará esta información en un servidor dado.
Los puntos clave en esta consulta son diferenciar los tipos de respaldos y sistemas de respaldos. Mi jefe y yo decidimos escribir una sub consulta con la unión de todas las características basados en el tipo de respaldo. Luego, lo filtramos con el uso de una sub consulta diciéndonos que se reportaran sólo respaldos hechos por NetApp, Commvault y SQL Nativo (Luego por supuesto implementamos orden y agrupamos para tener resultados de aspecto más limpio). También note que usamos el comando “max” para obtener las fechas y tiempos más recientes de respaldo. Luego use DATEDIFF para hacer un poco de matemáticas de modo que creemos una columna mostrando los días desde el último respaldo completo. Esto lo hizo fácil de leer a medida que íbamos hacia abajo en la lista y, como verá más adelante, una vez que implementé powershell, lo hice de tal manera que cualquier cosa que tuviera más de 7 días desde el último respaldo completo fuera remarcado en ROJO (RED). Esto hace fácil de ver si hubo un problema o demora en los respaldos. Obviamente podemos ser muy granulares con esto, pero nuestro equipo DBA sintió que esto era suficiente información para determinar si los respaldos eran consistentes.
La siguiente pregunta es, ¿cómo puedo convertir esto en un reporte de powershell y enviarme por correo electrónico un lindo y limpio html incrustado en el mensaje del correo electrónico? Bien, primero necesitaba aprender algo de powershell. Usé una combinación de google y un par de libros (que enlazaré al final de esta entrada).
Me gusta hacer mi trabajo en el ISE de Windows PowerShell incluido con el editor arriba y la salida de la consola abajo:
Vayamos a los básicos, primero tengo que importar el módulo SQL Poweshell.
|
1 2 |
#import SQL Server module Import-Module SQLPS -DisableNameChecking |
Bueno, eso fue bastante fácil. Luego, aprendí a través de mucha lectura que prefiero escribir el grueso de mi script como una función y luego llamar dicha función dentro del script. Esta función toma una lista de servidores desde un archivo y va a través de cada servidor y ejecuta la consulta SQL que hemos escrito, por lo tanto, extrayendo todos los datos relevantes en una sola oportunidad.
|
1 2 3 4 5 6 7 8 9 10 |
function Get-DBBackup-Type{ #List of servers from text file $serverlist = Get-Content -Path C:\PowerShell\PD.txt #Loop through each server and create SMO object) foreach ($serverName in $serverlist){ #create smo object $SQLServer = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Server -ArgumentList $ServerName |
Así que puede ver que creo la función y luego, como los comentarios explican, coloco el contenido de mi lista de servidores (PD.txt en este caso) en una variable. Voy a través de cada elemento en esta variable (ahora un arreglo, ya que tiene un puñado de elementos almacenados). Luego, con cada nombre de servidor creo lo que es conocido como un objeto SMO (alias SQL Management Object). Esto básicamente nos permite administrar SQL Server a través del lenguaje de programación powershell.
|
1 2 3 4 |
$Query = “Place contents of Query in here” #Use SQLCmd to execute the query on the server Invoke-Sqlcmd -ServerInstance $serverName -Query $Query |
Coloqué los comentarios de nuestra amada sentencia SQL en una variable llamada $Query. Luego use el comando Invoke-SQLcmd powershell para ejecutar la consulta contra el servidor de base de datos. Y eso es todo para la función. Ahora nos aseguramos de cerrar paréntesis para abarcar la función. Y ahora la llamamos.
|
1 |
Get-DBBackup-Type | Select ServerName, DatabaseName, BackupSystem, FullBackup, DifferentialBackup, LogBackup, DaysSinceLastFull |
Esto es interesante porque nos provee con la información en la consola powershell. Pero ahora deseo enviar estos resultados en un archivo html. Ahora, el comando regular de powershell ConvertTo-HTML está bien para tareas básicas. Pero no estaba exactamente dándome lo que yo buscaba.
Necesitaba que esto fuera legible, así que la primera cosa que hice fue sumergirme en uno de mis libros y encontré una manera de configurar el HTML. No tengo formación en HTML y no pensaba volverme un experto en unas pocas horas. Yo sólo estaba buscando lo que necesitaba. Encontré eso como una forma de establecer los bordes de tablas, títulos y los colores para los títulos.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
#Setup HTML $Header = @" <style> TABLE {border-width: 1px;border-style: solid;border-color: black;border-collapse: collapse;} TH {border-width: 1px;padding: 3px;border-style: solid;border-color: black;background-color: LightBlue;} TD {border-width: 1px;padding: 3px;border-style: solid;border-color: black;} .odd { background-color:#ffffff; } .even { background-color:#dddddd; } </style> <title> DV Backup Reports </title> "@ $Pre = "<h1>PD BACKUP REPORTS</h1>" |
En mi investigación para descubrir cómo hacer que mis reportes html se vieran bien, me topé con cómo crear reportes html
¡Ahora, esto es lo que estaba buscando! Dos funciones que usted puede añadir a sus scripts de powershell son Set-AlternatingRows y Set-CellColor. Su sitio puede darle los detalles minuciosos acerca de ellos. Yo sólo los utilicé para lo que necesitaba.
Puede que sea mejor importarlos a su script que sólo copiarlos y pegarlos para hacer su script más legible. De todos modos, los incluí en su integridad en la versión completa del script al final de la entrada, para preservar su compleción.
|
1 |
Get-DBBackup-Type | Select ServerName, DatabaseName, BackupSystem, FullBackup, DifferentialBackup, LogBackup, DaysSinceLastFull | ConvertTo-Html -Head $Header -PreContent $Pre | Set-CellColor -Property DaysSinceLastFull -Color red -Filter "DaysSinceLastFull -ge 7" | Set-AlternatingRows -CSSEvenClass even -CssOddClass Odd | Out-File C:\PowerShell\PD_BACKUP_REPORT.Html |
Así que, como puede ver, llamo a la función y uso canalizaciones para recolectar mi información. Convierto los datos a html (incluye la cabecera y la información de pre contenido). Luego llamo set-cellcolor del administrador para dar color rojo al campo “DaysSinceLastFull”, sólo si su valor es mayor o igual que 7. Luego uso Set-AlternatingRows para colorear cada otra fila (uso gris en este caso) para hacerlo más legible. Finalmente, mando los resultados a un archivo HTML.
Ejemplo de salida (nombres del servidor y base de datos difuminados):
Ahora quería que esto se mandara en un correo electrónico.
|
1 |
Send-MailMessage -to "email <email@domain.com" -from "SQLAdmin <sqladmin@domain.com>" -Subject "PD Database Backup Report" -SmtpServer mail.smtpserver.com -Attachments "C:\PowerShell\PD_BACKUP_REPORT.Html" -BodyAsHtml (Get-Content C:\PowerShell\PD_BACKUP_REPORT.Html | Out-String) |
Para esto sólo usé la función de enviar un mensaje de correo electrónico de Powershell. Adjunté el documento y luego usé la opción “bodyAsHtml” para llenar el cuerpo con el contenido del archivo html.
Ejemplo Incrustado en Correo Electrónico (y los datos adjuntos, lo cual es remarcado):
Aquí está el script completo en su forma final.
Enlaces:
- Aprenda Windows PowerShell en un Mes de Almuerzos
- Libro de recetas de SQL Server 2012 con PowerShell v3
- El Administrador Malhumorado (para obtener las funciones Set-color y Set-AlternatingRows entre mucho otro contenido útil.)
- Send-MailMessage en Technet
- Usar variables en SQL dinámico - November 5, 2019
- Cuándo utilizar las tablas temporales de SQL frente a las variables de tabla - September 16, 2019
- Reportes de respaldos SQL con PowerShell - February 28, 2017
Él inició su carreta de IT en el mundo de la atención al cliente y eventualmente se movió al lado de administración de redes/sistemas. Después de un tiempo, él desarrolló afección por trabajar con Bases de Datos (principalmente SQL Server) y ha enfocado su carrera en eso por los últimos ocho años. Él ha trabajado para varias industrias en ambientes grandes y pequeños, todos con necesidades diferentes.
SQL Shack le ha provisto con una oportunidad de contribuir a una comunidad que le dio mucho a través de los años.
Ver todas las entradas de Aamir Syed