Mettre à jour et appliquer des updates cumulatives a plusieurs instances en quelques minutes

L’application de correctifs à SQL Server peut parfois prendre du temps, en particulier lorsque plusieurs serveurs doivent être corrigés. La connexion à distance à chaque boîte pour exécuter l’assistant de mise à jour est fastidieuse, et si vous avez plusieurs correctifs à appliquer, vous allez y passer beaucoup de temps.

Heureusement, le module dbatool Powershell rend ce processus beaucoup plus facile! En fait, une fois que vous l’avez compris, utiliser Update-DbaInstance pour patcher vos serveurs SQL devient très simple. Mais, comprendre cela peut être un peu déroutant,

 Les plus gros problèmes que j’ai rencontrés ont été la configuration d’un emplacement central de mise à jour et l’utilisation d’un identifiant pour y accéder.

Voici le code PowerShell pour ceux qui aimeraient jouer à la maison.

$ServerName = "DESKTOP-95KKMMH\REPLICA","SDESKTOP-95KKMMH\DISS","DESKTOP-95KKMMH"

$UserName = Get-Credential

$PathCU = "\\SERVEUR1\SQL2019\correctif\SQLServer2019-KB4577194-x64.exe"

$VersionCU = "2019RTM"

Update-DbaInstance -ComputerName $ServerName -Restart -Version $VersionCU -Path $PathCU -Credential $UserName -Confirm:$false

Et voilà, toutes les mises à jour étaient en même temps, avec des cases dans PowerShell ISE me montrant le statut de chacune. C’était magique.

Pour ceux d’entre vous qui connaissent encore moins PowerShell que votre humble hôte, laissez-moi vous expliquer ce qui se passe.

• Les choses avec les signes $ sont des variables. Je déclare simplement ce qu’ils sont. Ils ne sont pas obligatoires – vous pouvez les transmettre en tant que valeurs littérales à la commande à la fin du script. Je l’ai juste fait pour la lisibilité.
• Get-Credential est une applet de commande qui crée un objet d’informations d’identification pour un nom d’utilisateur et un mot de passe spécifiques. Ce que cela signifie pour vous, c’est que lors de son exécution, vous recevrez une invite vous permettant de saisir un identifiant et un mot de passe pouvant être utilisés pour exécuter les mises à jour cumulatives sur tous les serveurs. C’est bien car vous ne devez saisir cette information qu’une seule fois, pas une fois pour chaque serveur.
• ComputerName est explicite, mais encore une fois, vous pouvez lui transmettre PLUSIEURS valeurs. C’est la clé de la vitesse.
• Redémarrer indique de redémarrer le serveur une fois la mise à jour cumulative appliquée. C’est vrai – la durée de 12 minutes comprenait les redémarrages!
• La version est une sorte de code transmis à UpdateDbaInstance. Il peut également inclure des service packs (c’est-à-dire «2019RTM») mais 2019 n’a pas de service packs, vous utilisez donc «RTM» dans cet espace.
• Path est le répertoire dans lequel vous avez le fichier .exe que vous avez téléchargé à partir de Microsoft pour appliquer la mise à jour cumulative. Placez-le quelque part où vous pouvez y accéder et NE le renommez PAS. Si vous faites cela ne fonctionnera pas car UpdateDbaInstance appelle une autre cmdlet qui sait ce que ce nom de fichier doit être basé sur ce que vous avez transmis pour Version.
• Credential utilise tout ce qui a été saisi pour Get-Credential. Si vous avez noté cette information, rien de tout cela ne fonctionnera. Cela pourrait être votre point de défaillance unique.
• Confirmer est défini sur false, car je ne souhaite pas cliquer sur « Êtes-vous sûr? » type d’invite. Vivez dangereusement, mes amis.

Si vous avez toujours des appréhensions à propos de tout cela, vous pouvez d’abord exécuter tout cela avec -WhatIf ajouté à la fin de la ligne Update-DbaInstance. Cela peut aider à identifier au préalable les erreurs telles que les problèmes de connectivité sans rien faire.

Et voila Finalement le kb4552255 CU5 a été applique a toutes mes instances dans quelques minu

Surveiller vos Instances SQL a travers Powershell

Problème

ce script de surveillance PowerShell collectera des données au niveau de l’instance pour tous les serveurs SQL qui ont été enregistrés pour cette solution de surveillance.

Solution

Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs  et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table

Ce module rassemblera des informations sur l’instance SQL Server, telles RAM ,PLE,cache Hit ration ,CPU l. Il se connectera à chaque serveur de LA TABLE Serveurs  et capturera les données pour chacune de ces instances. La connexion à chaque instance SQL Server sera basée sur les paramètres de la table Serveurs.

Le script PowerShell qui crée les objets ci-dessus et insère des données dans la table nomé CPU_Memory_Usage

Import-Module SqlServer
 $server = "COSxxxxxxx"
 $inventoryDB = "DBA_TOOLS"
 This is the definition of the table that will contain the values for each instance you wish to collect information from
 $resourcesUsageTable = "
 IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = 'CPU_Memory_Usage' AND xtype = 'U')
 CREATE TABLE CPU_Memory_Usage(
    [server]varchar(10) NOT NULL,
    [max_server_memory] [int] NOT NULL,
    [sql_memory_usage] [int] NOT NULL,
    [physical_memory] [int] NOT NULL,
    [available_memory] [int] NOT NULL,
    [system_memory_state]varchar(30) NOT NULL,
    [page_life_expectancy] [int] NOT NULL,
    [cache hit ratio] NUMERIC(10,6) NOT NULL,
    [cpu_usage_30] [int] NOT NULL,
    [cpu_usage_15] [int] NOT NULL,
    [cpu_usage_10] [int] NOT NULL,
    [cpu_usage_5] [int] NOT NULL,
    [data_sample_timestamp] [datetime] NULL
 ) ON [PRIMARY]
 "
 Make sure you create this table in your central environment, where you wish to gather the information from all the desired instances
 $instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $resourcesUsageTable
 /* Récupérer List Serveur Pour leur parcours */
 $instanceLookupQuery = "SELECT name  FROM servers" 
 $instances = Invoke-Sqlcmd -ServerInstance $server -Database $inventoryDB -Query $instanceLookupQuery
 $resourcesQuery = "
 WITH SQLProcessCPU
 AS(
    SELECT TOP(30) SQLProcessUtilization AS 'CPU_Usage', ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS 'row_number'
    FROM ( 
          SELECT 
            record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int') AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]', 'int') AS [SQLProcessUtilization], 
            [timestamp] 
          FROM ( 
               SELECT [timestamp], CONVERT(xml, record) AS [record] 
               FROM sys.dm_os_ring_buffers 
               WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR' 
               AND record LIKE '%%'
               ) AS x 
         ) AS y
 ) 
 SELECT 
    SERVERPROPERTY('SERVERNAME') AS 'Instance',
    (SELECT value_in_use FROM sys.configurations WHERE name like '%max server memory%') AS 'Max Server Memory',
    (SELECT physical_memory_in_use_kb/1024 FROM sys.dm_os_process_memory) AS 'SQL Server Memory Usage (MB)',
    (SELECT total_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Physical Memory (MB)',
    (SELECT available_physical_memory_kb/1024 FROM sys.dm_os_sys_memory) AS 'Available Memory (MB)',
    (SELECT system_memory_state_desc FROM sys.dm_os_sys_memory) AS 'System Memory State',
    (SELECT [cntr_value] FROM sys.dm_os_performance_counters WHERE [object_name] LIKE '%Manager%' AND [counter_name] = 'Page life expectancy') AS 'Page Life Expectancy',
       (SELECT CAST(
         (
           SELECT CAST (cntr_value AS BIGINT)
           FROM sys.dm_os_performance_counters  
           WHERE counter_name = 'Buffer cache hit ratio'
         )* 100.00
         /
         (
           SELECT CAST (cntr_value AS BIGINT)
           FROM sys.dm_os_performance_counters  
           WHERE counter_name = 'Buffer cache hit ratio base'
         ) AS NUMERIC(6,3)
       ))as [cache hit ratio] ,
    (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 30) AS 'SQLProcessUtilization30',
    (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 15) AS 'SQLProcessUtilization15',
    (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 10) AS 'SQLProcessUtilization10',
    (SELECT AVG(CPU_Usage) FROM SQLProcessCPU WHERE row_number BETWEEN 1 AND 5)  AS 'SQLProcessUtilization5',
 GETDATE() AS 'Data Sample Timestamp'
 "
 Try
 {
 Parcours Lists serveur par Foreach
 foreach ($instance in $instances){
    Write-Host "Fetching CPU/RAM information for instance" $instance.name
    $results = Invoke-Sqlcmd -Query $resourcesQuery -ServerInstance $instance.name -ErrorAction Stop -querytimeout 30
 #Insert statement 
    if($results.Length -ne 0){    
       $insert = "INSERT INTO CPU_Memory_Usage VALUES"
       foreach($result in $results){        
          $insert += "
          (
          '"+$result['Instance']+"',
          "+$result['Max Server Memory']+",
          "+$result['SQL Server Memory Usage (MB)']+",
          "+$result['Physical Memory (MB)']+",
          "+$result['Available Memory (MB)']+",
             '"+$result['System Memory State']+"',
             "+$result['Page Life Expectancy']+",
             "+$result['cache hit ratio']+",
             "+$result['SQLProcessUtilization30']+",
             "+$result['SQLProcessUtilization15']+",
             "+$result['SQLProcessUtilization10']+",
             "+$result['SQLProcessUtilization5']+",
             GETDATE()
             ),
        "
        }
 #Perform the INSERT in the central table
    Invoke-Sqlcmd -Query $insert.Substring(0,$insert.LastIndexOf(',')) -ServerInstance $server -Database $inventoryDB
    }
 }
 Write-Host "Done!" 
 }
 catch
 {
 write-host  "Error de connectivité" $instance.name
 }

Si nous interrogeons la table CPU_Memory_Usage , nous pouvons voir les données qui ont été collectées

Bonne surveillance de Vos instances

Déployer Sp_whoisactive et la solution d’olla sur plusieurs instances en moins d’une minute

À l’aide du module PowerShell open source dbatools, je peux déployer la nouvelle version de de la procédure stockée. sp_whoisactive et celle solution https://ola.hallengren.com/ sur plusieurs instances SQL server en quelque minutes

Ci joint le code Powershell

La première ligne récupérera les listes de mes instances. après c’est une petit vérification d’existence de la base cible
après il téléchargera la dernière version et compilera la procédure stockée sur la base de données sur chacune des instances de cette liste

$servers=Get-Content C:\BACKUP\server.txt
$database = 'maintenance'
foreach($server in $servers)
{

$server = New-Object Microsoft.SqlServer.Management.Smo.Server -ArgumentList $server
 
$dbcheck = $server.Databases | Where-Object { $_.Name -eq $database }
 
if ($null -eq $dbcheck){
    $db = New-Object -TypeName Microsoft.SqlServer.Management.Smo.Database -Argumentlist $server, $database 
    $db.Create()
}
Else {
  Install-DbaWhoIsActive -SqlInstance $server -database $database   
  Install-DbaFirstResponderKit -SqlInstance $server -Database $database
  Install-DbaSqlWatch -SqlInstance $server
  Install-DbaMaintenanceSolution -SqlInstance $server -Database $database -ReplaceExisting
}
}

Merci d’avoir lu

Surveiller SQL Server Agent with Powershell

bonjour
Dans Cet article je vous présente du code PowerShell
le code il montre comment Surveiller l’agent SQL Server pour s’assurer qu’elle est en cours d’exécution sur des multi serveurs .
Il y a quelques mois, une situation intéressante s’est produite. SQLAgent s’est arrêté et il a fallu un certain temps avant de réaliser ce qui s’était passé. Heureusement, il ne s’agissait pas d’un problème critique sur ce serveur, mais cela aurait pu être un problème majeur sur d’autres serveurs de production. Cela m’a fait poser une question intéressante. comment surveillez-vous SQL Server, en particulier SQLAgent sur un Parc SQL alors c’est a travers Powershell
Commençons par résoudre le problème que je viens d’évoquer en définissant un fichier texte contenant les listes de serveurs à surveiller. Ouvrez simplement un éditeur de texte, tapez les adresses IP ou les noms de votre serveur, ou un mélange des deux, puis enregistrez le fichier dans votre répertoire local. Dans mon cas, j’utilise c: \servers.txt, par exemple:

$servers=get-content "c:\servers.txt"
$servers

Pour chaque objet renvoyé à partir de la variable $servers, nous allons le placer dans la variable $server, puis vérifier le serveur à la recherche d’instances d’agent SQL Server en statut Agent Stopped.
Dans Powershell, vous utilisez le caractère | pour indiquer que vous souhaitez Powershell transmette les valeurs de la commande de gauche à celle de droite. Voici ce que le script ci-dessus fait:
Nous demandons d’abord à Powershell d’obtenir les valeurs WMIobject pour tous les services win32 et nous passons le nom du serveur dans le paramètre –computername.
Ensuite, nous prenons les valeurs de l’appel get_wmiobject et on sélectionne uniquement le nom et les valeurs d’état renvoyées par la commande, car ce sont les seules sur lesquelles nous souhaitons travailler dans notre exemple.
Nous filtrons ces résultats de 2 manières. Premièrement, nous utilisons une recherche générique et l’opérateur –like sur les noms ($ _. Name) pour tout nom commençant par « SQLAgent » ou commençant par « SQL » et se terminant par « Agent » et qui a (-match ) un état « arrêté ».
Enfin, nous prenons nos résultats filtrés et demandons à Powershell de générer ces informations sous forme de chaîne. La dernière étape de la sortie sur une chaîne devient importante plus tard.
Maintenant, si nous nous arrêtions là, vous pourriez surveiller manuellement les serveurs de votre choix et voir quels services étaient arrêtés. Bien que cela soit modérément utile, il serait bien que cela puisse fonctionner sur un ou plusieurs serveurs distants et le démarrer automatiquement a travers start-service . Laissons cela en modifiant légèrement le code ci-dessus.

$servers=get-content "c:\servers.txt"

foreach($server in $servers)
{
$statut=get-wmiobject win32_service -computername $server |
  select name,state |
  where {$_.name -like "SQLAGENT*" -or $_.name -like "SQL*AGENT" `
   -and $_.state -match "Stopped"}
   $name=$statut.name
  }    

if ($statut.state -match "stopped")
{
start-service -Name  $name
}

 bonne surveillance

dbatools pour migrer vos instance SQL Server

bonjour

• Introduction:La communauté dbatool compte aujourd’hui plus de 50 contributeurs avec plus de 300 meilleures pratiques SQL Server, des commandes d’administration et de migration. Un aperçu des commandes peut être trouvé ici:
  https://dbatools.io/functions/
• Installation: j’aime  la facilité et la simplicité d’installation de dbatools. Tant que vous avez accès à Internet vous pouvez simplement installer le module depuis la Galerie PowerShell en utilisant

Install-Module dbatools

• Migration:Vous n’avez généralement besoin que d’une seule commande pour migrer tout un serveur SQL vers un autre. Comme décrit dans la documentation d’aide, il s’agit d’un « clic d’un bouton » dbamigration                                                         

 

Cela migre les parties suivantes comme indiqué ci-dessous. Chaque partie peut être ignorée avec un paramètre -no *** comme décrit dans la documentation d’aide – par exemple, utilisez -NoLogins si vous ne souhaitez pas transférer les connexions

 

 Start-DbaMigration -Source ServerA -Destination ServerB -Verbose -BackupRestore -NetworkShare \\Partage

 

Copy-DbaDatabase -Source ServerA -Destination ServerB -verbose
-AllDatabases -Exclude AdventureWorksLT2014 -BackupRestore
-NetworkShare \\partage -Force

Vous pouvez ajouter l’option -Force pour forcer la recréation des bases

Bonne configuration

PowerShell Backup/Restore Multi Databases

bonjour a tous

vous avez  deux serveurs, Prod et Test, et tous les deux exécutent  SQL Server
Est-il possible d’avoir une solution qui me permet de  copier toutes les bases  de données d’un serveur à un autre? alors la bonne méthode c’est du PowerShell

vous voulez faire une copie de plusieurs Bases de données toutes les semaines et synchroniser vos instances avec un seul script alors c’est du Powershell

Vous pouvez modifier le script si nécessaire pour correspondre à votre environnement et à vos besoins

ci joint mon instance qui contient plusieurs base de données

Ci joint Le script du backup qui sauvegarde toutes les bases de données à l’exception de la base de données système et écrit les fichiers de sauvegarde sur le disque local

 

$SQLInstance = ".\sqlprod"
$BackupFolder = "C:\backup\"
$a = get-date -UFormat %V
[string]$num_semaine= 52 – $a #numeror de semaine
[string]$year=Get-Date -UFormat %Y #récupéree anné
$format_date=$num_semaine +$year
$format_date="_"+$format_date
$tStamp = Get-Date -format yyyyMMddHHmm 

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null

$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance
$dbs = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases 

foreach ($Database in $dbs | where { $_.IsSystemObject -eq $False })
{
$Database.name
$bk = New-Object ("Microsoft.SqlServer.Management.Smo.Backup")
$bk.Action = [Microsoft.SqlServer.Management.Smo.BackupActionType]::Database
$bk.BackupSetName = $Database.Name + "_backup_" + $tStamp
$bk.Database = $Database.Name
$bk.CompressionOption = 1
$bk.MediaDescription = "Disk"
$bk.Devices.AddDevice($BackupFolder + "" + $Database.Name +$format_date + ".bak", "File") 

TRY
{$bk.SqlBackup($srv)}
CATCH
{$Database.Name + " backup failed."
$_.Exception.Message}
}

Maintenant, si vous avez une base de données qui existe dans votre instance , vous pouvez les supprimer comme suit:

$SQLInstance = ".\sqlprod"
$srv = New-Object ("Microsoft.SqlServer.Management.Smo.Server") $SQLInstance
$dbs = New-Object Microsoft.SqlServer.Management.Smo.Database
$dbs = $srv.Databases
foreach ($Database in $dbs | where { $_.IsSystemObject -eq $False })
{
$Database.Drop()
}

Ce Script restaurera toutes les bases de données (* .bak) qui se trouvent dans le dossier défini

$server=".\sqlprod"
$files = Get-ChildItem -Path "c:\Backup\" -filter *.bak

foreach ($f in $files)
{
$fs = $f.Name
$s = $fs.split(".")
[string]$s[0]
Restore-SqlDatabase -ServerInstance $server -Database $s[0] -BackupFile $f -ReplaceDatabase
}

Vous obtiendrez les résultats comme ci-dessous

Voyons  maintenant comment Exécuter un script PowerShell via une tâche planifiée

Etape1 : Ouvrir le Planificateur de tâches
Panneau de configuration – Outils d’administrations – Planificateur de tâches

Etape 2:Clique droit – Créer une nouvelle tâche

Etape 3:Configurer les options de base suivant vos besoins puis spécifier le chemin du votre script 

Indiquer dans Programme/script :
C:\Windows\System32\WindowsPowerShell\v1.0\powershell.exe
Indiquer dans Ajouter des arguments :
Le chemin de votre script PowerShell (ici C:\backup\SYnchron_instance.ps1)            Valider l’action en cliquant sur OK

finalement Valider votre nouvelle tâche planifiée. Les informations d’identification utilisateur peuvent vous être demandées

 

Bonne configuration && synchronisation