ProblemaUna tarea común que muchos administradores de base de datos (DBA) realizan de forma regular está trabajando con procedimientos almacenados de SQL Server. Los procedimientos almacenados permiten al DBA automatizar una determinada tarea agrupando una consulta y ejecutándola como un solo conjunto de lógica. Esto es un progreso considerable en la inclusión de más automatización, pero ¿por qué no automatizar la automatización? Para crear un procedimiento almacenado, un DBA puede ir a SQL Server Management Studio (SSMS), hacer clic en la GUI y crear un procedimiento almacenado de esa manera o, al igual que, al ejecutar un procedimiento almacenado perseguir la misma ruta. Esto es una pérdida de su precioso tiempo! Vamos a automatizar eso.
Solución
Lo que quiero decir con "automatizar la automatización" es la construcción de una herramienta en PowerShell que nos permite recuperar los procedimientos almacenados para su revisión y ejecutarlos todos desde la línea de comandos. Entre muchos otros beneficios, esto nos da la ventaja de poder agregar estos comandos a proyectos de automatización más grandes por el camino. Veamos cómo podemos construir esta herramienta y mostrar cómo funciona.
En primer lugar, tendremos que descargar e instalar algunos requisitos previos si aún no tiene SSMS instalado. Para acelerar las cosas a continuación se muestra un código de PowerShell que descarga e instala cada uno de los paquetes necesarios para que funcione nuestra herramienta. Este código descargará cada paquete MSI en su directorio temporal de usuario y ejecutará la instalación en cada uno.
$files = [ordered]@{
'SQLSysClrTypes.msi' =
'http://go.microsoft.com/fwlink/?LinkID=239644&clcid=0x409'
'SharedManagementObjects.msi' =
'http://go.microsoft.com/fwlink/?LinkID=239659&clcid=0x409'
'PowerShellTools.msi' =
'http://go.microsoft.com/fwlink/?LinkID=239656&clcid=0x409'
}
foreach ($file in $files.GetEnumerator())
{
$downloadFile
= (Join-Path -Path $env:TEMP -ChildPath $file.Key)
if (-not
(Test-Path -Path $downloadFile -PathType Leaf))
{
Invoke-WebRequest -Uri $file.Value -OutFile $downloadFile
}
Start-Process
-FilePath 'msiexec.exe' -Args "/i $downloadFile /qn" -Wait
}
Ahora que tienes los requisitos previos descargados, podemos empezar a diseñar nuestra herramienta.
Para crear una gran herramienta de PowerShell, es importante que el código sea lo más genérico posible al crear parámetros de entrada de una manera que fomente la reutilización. Esto significa llevar a cabo elementos que pueden cambiar con el tiempo y construirse como parámetro en lugar de una referencia estática. En este caso, esto será cosas como el nombre de instancia de SQL Server, nombre de base de datos, nombre de procedimiento almacenado y así sucesivamente. Empecemos primero con una función para ejecutar un procedimiento almacenado. Esta función (así como todas las siguientes funciones que estaremos construyendo) tienen todos los parámetros apropiados.
function Invoke-SqlStoredProcedure
{
[CmdletBinding()]
param
(
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string]$ServerName,
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string]$Database,
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string]$Name,
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[pscredential]$Credential
)
begin
{
$ErrorActionPreference = 'Stop'
}
process
{
try
{
$connectionString = New-SqlConnectionString -ServerName $ServerName -Database $Database -Credential $Credential
$SqlConnection = New-SqlConnection -ConnectionString $connectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandType=[System.Data.CommandType]’StoredProcedure’
$SqlCmd.CommandText = $Name
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
}
catch
{
$PSCmdlet.ThrowTerminatingError($_)
}
}
}
La mayor parte de este código está disponible con SMO cuando se instala el módulo SQLPS. Sin embargo, observe las referencias a New-SqlConnectionString y New-SqlConnection. Esas son las funciones personalizadas que he elegido para salir. ¿Por qué? Porque no sólo vamos a construir esta función sino también una para recuperar una lista de todos los procedimientos almacenados que existen en un servidor. Con estos comandos estándar divididos en otros comandos, pueden ser compartidos. No voy a aburrir con los detalles sobre ellos, pero si usted está interesado, no dude en echar un vistazo al módulo que he creado en Github.
Ahora observe la otra función que he creado para enumerar todos los procedimientos almacenados en un servidor SQL.
function Get-SqlStoredProcedure
{
[OutputType([Microsoft.SqlServer.Management.Smo.StoredProcedure])]
[CmdletBinding()]
param
(
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string]$ServerName,
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[string]$Database,
[Parameter(Mandatory)]
[ValidateNotNullOrEmpty()]
[pscredential]$Credential
)
begin
{
$ErrorActionPreference = 'Stop'
}
process
{
try
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$connectionString = New-SqlConnectionString -ServerName $ServerName -Database $Database -Credential $Credential
$sqlConnection = New-SqlConnection -ConnectionString $connectionString
$serverInstance = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $sqlConnection
$serverInstance.Databases[$Database].StoredProcedures
}
catch
{
$PSCmdlet.ThrowTerminatingError($_)
}
}
}
Observe aquí también puedo referirme a estas funciones compartidas ya que hacer esa conexión inicial es un atributo común de compartir. Esta es una buena práctica al construir una herramienta como esta. Es importante primero pensar mentalmente en lo que vas a lograr antes de empezar. En nuestro caso, estaba invocando un procedimiento almacenado y enumerándolos en un servidor. Luego, a medida que comience a construir su código de aviso de la herramienta que podría tener en común y romper los que en otras funciones.
Para la prueba final, vamos a tomar nuestra herramienta para una prueba de ejecución. Tengo una base de datos SQL en Azure para probar esto. En primer lugar, intentaré consultar todos los procedimientos almacenados en mi base de datos myazuredatabase en ese servidor.
Get-SqlStoredProcedure -ServerName adbsql.database.windows.net -Database myazuredatabase –Credential (Get-Credential)
Después de proporcionar mi nombre de usuario y contraseña de mySQL, deberías ver algo como esto:
Esto comienza a enumerar cada procedimiento almacenado y mostrar las propiedades de cada procedimiento almacenado.
Sé que tengo un procedimiento almacenado llamado uspLogError en esta base de datos. Vamos a ejecutar Invoke-SqlStoredProcedure y ver si eso funciona también.
Invoke-SqlStoredProcedure -ServerName adbsql.database.windows.net -Database myazuredatabase -Name dbo.uspLogError -Credential (Get-Credential)
Usted puede ver que construir una herramienta en PowerShell puede ahorrarle una tonelada de tiempo. Al usar esto en la consola para realizar consultas ad hoc o incorporarla a un proceso de orquestación más grande, ahorrará mucho tiempo para volver a hacer cosas más importantes.
