Script Powershell MSSQL

Ce script peut être utilisé pour importer automatiquement les fichiers sql vieux de plus de 20 minutes dans la base de données MSSQL. Il est utile si vous activez la journalisation SQL Text pour des configurations hautes performances. Pour exécuter ce script, vous devez l'enregistrer avec l'extension .ps1, et vous devez activer l'exécution des scripts PowerShell pour Windows dans l'éditeur de stratégie de groupe (ou vous devez exécuter le script en tant qu'Administrateur).

Vidéo 1 - Comment utiliser un script PowerShell dans une base de données MSSQL (Tutoriel vidéo)

Créez la table MSSQL suivante

CREATE TABLE messagereport
(
    logid int IDENTITY(1,1),
    messageid varchar(64),
    fromconnection varchar(64),
    fromaddress varchar(64),
    requestedconnection varchar(64),
    toconnection varchar(64),
    toaddress varchar(64),
    messagetext varchar(1024),
    route varchar(64),
    datecreated datetime,
    datedeliverytoconnectionsuccess datetime,
    datedeliverytoconnectionfailed datetime,
    deliverytoconnectionreference varchar(256),
    deliverytoconnectionstatusmessage varchar(1024),
    datedeliverytorecipientsuccess datetime,
    datedeliverytorecipientfailed datetime,
    deliverytorecipientstatusmessage varchar(1024),
    status varchar(64)   
)

La première étape consiste à créer la table messagereport dans la base de données. Tous les messages entrants et sortants seront stockés dans cette table et mis à jour selon leur statut. Si vous avez déjà créé cette table, vous pouvez ignorer cette étape. Copiez le script de création de table ci-dessus et exécutez-le dans votre base de données comme vous pouvez le voir sur la Figure 1.

créer la table messagereport
Figure 1 - Créer la table messagereport

Enregistrez le script PowerShell suivant sous "dbimport.ps1"

L'étape suivante consiste à créer le fichier "dbimport.ps1" dans le dossier C:\Ozeki comme illustré à la Figure 2. Ce fichier contiendra le script qui exécute périodiquement des requêtes SQL dans la base de données.

créer le fichier de script
Figure 2 - Créer le fichier de script

$ProcessedDir = "C:\Ozeki\Processed"
New-Item -ItemType Directory -Force -Path $ProcessedDir

#boucle infinie
while (1) {
	#lister les fichiers vieux de plus de 20 minutes
	#assurez-vous que le temps est supérieur à la période utilisée dans la configuration de reporting
	Get-ChildItem "C:\Ozeki\Reporting_SQL" -Filter *.sql | 
		Where{$_.LastWriteTime -le (Get-Date).AddMinutes(-20)} |
	
	Foreach-Object {
		#charger le contenu du fichier dans la base de données mssql
		Write-Output $_.FullName
		$Text = Get-Content -Path $_.FullName
		Invoke-Sqlcmd -ServerInstance MyComputer\MainInstance -Database MyDatabase
		-Query "$Text" -Username "MyUser" -Password "MyPass"

		
		#déplacer le fichier traité dans le dossier Processed
		Move-Item -Path $_.FullName -Destination $ProcessedDir
	}
	
	#pause de 5 secondes
	Start-Sleep -s 5
}

Copiez le script PowerShell ci-dessus et collez-le dans le fichier "dbimport.ps1" comme vous pouvez le voir sur la Figure 3.

coller le script dans le fichier
Figure 3 - Coller le script dans le fichier

Enfin, modifiez le script en fonction de votre propre base de données MSSQL. Dans l'instance du serveur, définissez le nom de l'ordinateur et de l'instance de la base de données MSSQL. Entrez le nom d'utilisateur et le mot de passe que vous souhaitez utiliser, et remplacez "Mydatabase" par le nom de votre base de données (Figure 4).

modifier la commande MySQL
Figure 4 - Modifier la commande MySQL

Exécutez le script en tant qu'administrateur

powershell -ExecutionPolicy Bypass -File dbimport.ps1

Maintenant, exécutez le script dans PowerShell en exécutant la commande ci-dessus comme vous pouvez le voir sur la Figure 5.

exécuter le script PowerShell
Figure 5 - Exécuter le script PowerShell

Vous pouvez voir que le script exécute tous les fichiers SQL vieux de plus de 20 minutes dans le dossier 'C:\Ozeki\Reporting_SQL' un par un (Figure 6).

script exécutant des requêtes SQL
Figure 6 - Script exécutant des requêtes SQL

Chaque fichier SQL que le script a exécuté est déplacé dans le dossier Processed afin qu'aucune requête ne soit perdue (Figure 7). À partir de là, vous pouvez les supprimer manuellement si vous n'en avez plus besoin.

fichiers SQL déplacés dans le dossier Processed
Figure 7 - Fichiers SQL déplacés dans le dossier Processed

Enfin, si vous regardez dans la base de données, vous pouvez voir que tous les messages ont été insérés dans la table messagereport comme le montre la Figure 8.

messages insérés dans la base de données
Figure 8 - Messages insérés dans la base de données

More information