Se dovessi nominare uno strumento che consiglio anzi che ritengo essenziale nel lavoro di tutti i giorni come DBA SQL Server non posso fare altro che menzionare dbatools.
dbatools è un modulo Powershell: lo possiamo immaginare come un Management Studio a riga di comando. Nato come semplice script per semplificare attività di migrazione tra istanze nel tempo è cresciuto raggiungendo ormai alla versione 2.1.8 ad un set di più di 500 comandi per le più disparate attività.
dbatools è attivamente supportato dalla community di professionisti e appassionati di SQL Server, riceve aggiornamenti costanti e quasi tutti i comandi (dove applicabile) sono compatibili o dedicati a istanze Azure.
Cos’è Powershell?
Prima di parlare delle caratteristiche principali di dbatools facciamo un passo indietro, abbiamo detto che è un modulo di Powershell: di cosa si tratta?
Powershell è un ambiente command-line crossplatform (Windows, Linux e macOs) nato principalmente per automatizzare processi tramite comandi e/o script in un linguaggio proprietario, è gratuito e facilmente installabile nei vari ambienti.
Per fare un facile paragone lo possiamo immagine come una naturale evoluzione del Command Prompt di Windows dal quale si discosta con un set di migliorie ed estensioni:
Robusta command-line history
Auto completamento e prediction attraverso il tasto TAB
Supporto alias sia per i comandi che per i parametri
Pipeline per concatenazione dei comandi
In-console help, similare alle man pages di Unix
Linguaggio di scripting ampiamente migliorato
Integrazione con il framework .Net
Installazione di dbatools
Dbatools ha come requisito minimo Powershell v3, ma il consiglio e di installare la versione più recente (al momento v7.4). Powershell è un prodotto Microsoft supportato ufficialmente e gratuito, solitamente le installazioni standard di Windows installano di default la versione 5.
Il modulo è facilmente installabile tramite il comando:
Install - Module dbatools - Scope CurrentUser
Qualora non fosse disponibile la connessione internet sono presenti in rete comunque vari tutorial per l’installazione offline.
Come si utilizza dbatools
dbatools, come detto, è un insieme di comandi che posso essere eseguiti uno per volta o concatenati per ottenere il risultato desiderato.
Una volta aperta una sessione di Powershell si compila il comando desiderato fornendo i parametri richiesti e eseguendolo con Invio:
Nell’esempio in figura, per esempio, ho richiesto l’estrazione di tutti i database presenti nell’istanza locale dove Powershell è installata. La parte in giallo GetDbaDatabase è il comando, successivamente in grigio abbiamo un parametro (-SqlInstance) e dopo lo spazio la valorizzazione del parametro (localhost).
Subito sotto come risultato abbiamo l’output del comando che consiste in una lista di valori (proprietà) per ogni oggetto (database in questo caso) rilevati nell’istanza.
Ogni comando ha quindi la sua lista di parametri e produce il suo output specifico.
Powershell da possibilità di concatenare più comandi con il carattere “|“(pipe), la concatenazione si può immaginare come: a tutti gli oggetti restituiti dal comando a sinistra della pipe applica il comando specificato a destra.
Riprendendo il comando precedente viene concatenato il comando Select-Object per indicare a Powershell di restituire solamente la proprietà “Name” in modo da ottenere una lista più facilmente consultabile.
Nulla vieta infine di concatenare più volte comandi, in questo caso con l’aggiunta del comando Where-Object la lista è stata filtrata dei soli database con dimensione maggiore di 200 MB con il solo output delle proprietà Name e SizeMB.
È possibile creare dei veri e propri programmi compilando un file ps1 (script di Powershell) per aggiungere logiche complesse, parametri, strutture di controllo e invio di mail.
La suite di comandi Powershell oltre che a dbatools è variegata e compre svariate funzionalità, un utente avanzato sarà in grado di creare veri e propri programmi!
Moduli di dbatools
Come detto, nell’introduzione la suite si arricchita nel tempo di svariati comandi arrivando a superare i 500! Anche volendo suddividerli per categorie otterremo un elenco davvero poco pratico da elencare in questo articolo.
Vado a menzionare alcune categorie che trovo particolarmente utili nelle attività più comuni; è possibile vedere la lista completo alla pagina della documentazione: [link: https://dbatools.io/commands/]
- Backup and Restore – Database: comandi per l’esecuzione e controllo dell’attività di backup.
- Connections: comandi per la connessione e test della connettività alle istanze.
- Databases: comandi per il recupero informazioni e configurazioni di tutti i database a bordo di un’istanza (lista dei database, stato, spazio occupato e datafiles, compatibilità, ecc).
- DBCC: comandi per l’automazione e recupero informazioni in ambito comandi DBCC e controlli di integrità dei database.
- Export: set di comandi per l’esportazione (script SQL inclusi) degli oggetti dell’istanza (databse, ruoli, logins, configurazioni, ecc).
- Login and User Management: comandi per automatizzare la creazione rimozione e modifica di logins, ruoli, user, ecc.
- SQL Agent: domandi per automatizzare la gestione della configurazione e job presenti per il SQL Agent.
Generalmente (ma con molte eccezioni) possiamo suddividere i comandi dbatools in base alla prima parte del loro nome che ne indica con buona approssimazione lo scopo:
- Comandi Get-*: per il recupero di informazioni.
- Comandi New-*: per la creazione di una nuova entità non presente (login, database, ecc ).
- Comandi Set-*: per la modifica di un valore o configurazione esistente.
- Comandi Remove-*: per la rimozione/cancellazione di un’entità.
- Comandi Move-*: per la migrazione di entità.
- Comandi Test-*: per la verifica di funzionalità.
Esempi pratici
Per comprendere meglio le potenzialità della suite vado a presentare alcuni casi di utilizzo e quali possono essere comandi da lanciare per ottenere il risultato:
Scenario 1:
Di una lista di istanze SQL in gestione si vuole per ognuna testare la raggiungibilità.
Comando da eseguire:
Test - DbaConnection - SqlInstance server1.mydomain.LOCAL ,server2.mydomain.LOCAL ,server3.mydomain.LOCAL
Utilizzando il comando Test-DbaConnection è possibile testare la raggiungibilità dell’istanza, è possibile fornire la lista da controllare valorizzando la lista nel parametro -SqlInstance come elenco separato da virgole:
Nell’esempio si vede come è stato richiesto di controllare la lista dei server XXXXT, XXXX1, XXXM ed ottenere il risultato per tutte e tre le istanze con un unico comando.
Il comando Test-DbaConnection a fronte di una connessione avvenuta correttamente stampa indicativamente una ventina di proprietà relative alla macchina (dominio, versione del s.o., tipo di autenticazione accettata, ecc…) per ottenere una lista più comoda da consultare è stato concatenato al comando di test il comando Select-Object per estrarre solamente una parte delle informazioni (ComputerName, ConnestSuccess, IsPingable).
Variante: della lista di istanze fornite voglio solo quelle con esito negativo.
Aggiungo al comando una condizione di filtro solo connessioni non riuscite:
WHERE - OBJECT ConnestSuccess - eq $FALSE
Si immagini di schedulare il comando, potrebbe con l’aggiunta dell’invio di una mail una sorta di monitoraggio attivo.
Scenario 2:
È richiesto un refresh di un database da un ambiente di produzione ad uno inferiore. È necessario, dopo la restore, rimpiazzare gli utenti del database in modo da allinearli alle login compatibili a questo ambiente:
Eseguendo in serie questi comandi possiamo arrivare al risultato atteso:
1. Backup degli utenti del database pre restore
Export - DbaUser - SqlInstance myIstance - DATABASE mydb - FilePath C: \temp\mydbUser.sql
Con il commando Export-DbaUser è possibile genare uno script SQL per la creazione degli utenti, viene specificato in questo esempio istanza a cui collegarsi, database da analizzare, e nome del file da generare
2. Restore del database
RESTORE - DbaDatabase - SqlInstance myIstance - Path \\myBackupPath\mydb.bak - WithReplace
Con il commando Restore-DbaDatabase è possibile eseguire automaticamente un restore del database, se non specificato nome e posizione dei data files e log saranno standard come previsto dalla configurazione di SQL Server. Con questi parametri il comando esegue sull’istanza myInstance il restore del database contenuto nel backup mydb.bak, con l’opzione WithReplace si indica che è possibile sovrascrive l’eventuale esistente.
3. Pulizia degli utenti ereditati dall’ambiente superiore (tranne quelli di sistema) sul database appena restorato
Get - DbaDbUser - SqlInstance myIstance - DATABASE mydb - ExcludeSystemUser | Remove - DbaDbUser - FORCE
È sempre buona cosa eliminare eventuali user orfani di login o in generali incompatibili con l’ambiente in cui il database viene restorato, con Get-DbaUser andiamo a recuperare la lista di tutti gli utenti del database mydb esclusi quelli di sistema (ExcludeSystemUser), la lista viene concatenata a RemoveDbaUser rimuovendoli.
Invoke - DbaQuery - SqlInstace myIstance - DATABASE mydb - FILE C: \temp\mydbUser.sql
Concludiamo con l’ultimo passaggio, al punto uno abbiamo generato gli script per la creazione degli utenti e salvato il file .sql in C:\temp utilizziamo adesso lo script per ripristinarli su un database “pulito” con il comando Invoke-DbaQuery passando oltre ai consueti parametri anche il file con le istruzioni da eseguire.
Con relativo poco lavoro è possibile incapsulare questa sequenza di comandi in un unico script Powershell che accetta due parametri: nome istanza e percorso del backup per automatizzare e riutilizzare all’occorrenza ogni volta che un’operazione simile è richiesta!
Scenario 3:
Dopo un penetration test è stato consigliato di disattivare l’utenza “sa” nelle varie istanze. È richiesto di conseguenza, per tutte le istanze, la riconfigurazione dei job presenti nel SQL Agent sostituendo “sa” con altro utente come Job Owner.
Get - DbaAgentJob - SqlInstance myInstace1 ,myInstance2 ,myInstance3 | WHERE - OBJECT OwnerLoginName - eq "sa" | SET - DbaAgentJobOwner - LOGIN "myNewAdminUser"
Anche in questo caso utilizziamo la concatenazione di più comandi per eseguire l’attività:
- iniziamo ad estrarre la lista dei job presente nelle istanze myInstace1, myInstance2, myInstance3
- successivamente applichiamo un filtro ai risultati ottenuti con Where-Object dove OwnerLoginName è sa
- in fine con Set-DbaAgentJobOwner andiamo a impostare per tutti i job la nuova login myNewAdminUser.
Conclusione
L’articolo non ha la pretesa di essere un compendio esaustivo né sull’utilizzo di Powershell né su dbatools, tuttavia, ad un lettore attento spero non siano sfuggite le enormi potenzialità dei prodotti.
Posso garantire dalla mia esperienza come dba che dbatools è diventato parte integrante quando pianifico nuove attività, sia come strumento monitoraggio attivo sia per semplificare i normali lavori di routine!
di Riccardo Trattenero, pubblicato il 28 marzo 2024
