Script PowerShell-Inventaire des instances SQL Server

Il existe une besoin récurrent d’inventorier els instances SQL sur el réseau. Ne serait-ce que pour valider la conformité au niveau des licences acquises, en fonction des éditions, du nombre de cœurs, etc …

Mais on peut aussi avoir un simple besoin de lister les bases de différentes instances, de valider les modèles de récupération ou d’autres paramètres fondamentaux comme Page_Verify, Auto_Close et Auto_Shrink, mais encore la taille ou même la nombre de connexions et la date de dernière activité recensée …

Pour poursuivre la série de petits scripts PowerShell, je vous propose ce petit bout de code permettant d’inventorier les instances et les bases … Notez que l’on peut faire des exclusions mais également ajouter des instances non “découvrables” (pour cause de Firewall, WAN, etc..) au travers d’un fichier texte.



[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

clear-host 

#$DiscoveredInstanceList = sqlcmd -L
# or
$DiscoveredInstanceTable = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
$DiscoveredInstanceList = @()
ForEach ($InstanceName in $DiscoveredInstanceTable) {
    $s = $InstanceName.ServerName
    if (-not ([string]::IsNullOrEmpty($InstanceName.Instancename))) {
        $s += "\" + $InstanceName.Instancename
    }
    $DiscoveredInstanceList += $s
}
# or
# $DiscoveredInstanceList = "srv1","srv2"

try {
    $AdditionalInstancesListFile = Get-Content "C:\temp\AdditionalServers.txt" 
    $DiscoveredInstanceList += $AdditionalInstancesListFile
}
catch {}


$ExcludedHostList = "devsql2017","(local)"


[int]$global:TotalDatabaseStorage = 0
[int]$global:TotalDatabaseCount = 0

$ListInstances = @()
$ListDatabases = @()
$InstanceErrorList = @()
$DisplayInstanceErrorList = $false

$Debug = $false

$CheckDatabaseDetails = $true 
$CheckLastUserAccess = $true
$CheckSystemDatabases = $true 

$OutGridView = $true
$ExportCSV = $true
$ExportCSVFile = "c:\temp\SQLInstancesInventory.csv"

ForEach ($InstanceName in $DiscoveredInstanceList) {
    $InstanceName = $InstanceName.trim()
    if ($InstanceName -eq "") {continue}
    if ($InstanceName -eq "Servers:") {continue}
    
    
    # Check excluded instances
    if ($ExcludedHostList -contains $InstanceName) {
        if ($Debug) {
            Write-Host $InstanceName " excluded" -ForegroundColor yellow
        }
        continue
    }

    $Server = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($InstanceName)
    $Server.ConnectionContext.ConnectTimeout = 1

    if (!($Server.ComputerNamePhysicalNetBIOS)) {
        $InstanceErrorList +=  "Error connecting $InstanceName"
        continue
    }
    else {

        $Databases = $server.Databases | Where-Object Status -eq "normal" | sort-object ID

        Write-Host $InstanceName "-"$Server.Edition "-" $Server.VersionString "(" $Server.ProductLevel ") -" $Server.collation `

        $InstanceStorage = 0
        $DatabaseCount = 0
        foreach ($Database in $Databases) {
             try {
                 If (($CheckSystemDatabases) -or ($Database.iD -gt 4)) {
                    $InstanceStorage += $Database.size
                    $DatabaseCount += 1
                    if ($CheckDatabaseDetails) {
                        if ($debug) {
                            Write-Host "  " $Database.Name "- Owner" $Database.Owner "- RecoveryModel" $Database.RecoveryModel "- Size" $Database.Size.ToString("N") "MB"
                        }

                        if ($CheckLastUserAccess) {
                            $tSQL = "SELECT database_id , 
	                                       CASE WHEN max(last_user_seek) > max(last_user_scan) THEN max(last_user_seek)
	                                            ELSE max(last_user_scan)
	                                       END AS LastUserRead,
                                           max(last_user_update) as LastUserWrite
                                    FROM sys.dm_db_index_usage_stats
                                    WHERE database_id = " + $Database.ID + "
                                    GROUP BY database_id "
                            
                            $LastUserRead = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserRead
                            $LastUserWrite = $Database.ExecuteWithResults($tSQL).Tables[0].LastUserWrite

                            if (-not ([string]::IsNullOrEmpty($LastUserRead))) {$LastUserRead = $LastUserRead.ToString("yyyy-MM-dd HH:mm:ss")}
                            if (-not ([string]::IsNullOrEmpty($LastUserWrite))) {$LastUserWrite = $LastUserWrite.ToString("yyyy-MM-dd HH:mm:ss")}

                        }
                        else {
                            $LastUserRead = ""
                            $LastUserWrite = ""
                        }


                        $LastKnownGood = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"} | Select-Object -First 1).value

                        $ListDatabases += New-Object PSObject -Property @{InstanceName=$Server.name;`
                                                                          VersionMajor=$Server.VersionMajor;`
                                                                          DatabaseName=$Database.Name;`
                                                                          CompatibilityLevel=$Database.CompatibilityLevel.ToString().replace("Version","");`
                                                                          RecoveryModel=$Database.RecoveryModel;`
                                                                          Size=$Database.Size.ToString("N");`
                                                                          Owner=$Database.Owner;`
                                                                          Collation=$Database.collation;`
                                                                          AutoClose=$Database.AutoClose;` 
                                                                          AutoShrink=$Database.AutoShrink;`
                                                                          IsReadCommittedSnapshotOn=$Database.IsReadCommittedSnapshotOn;`
                                                                          PageVerify=$Database.PageVerify;`
                                                                          ActiveConnections=$Database.ActiveConnections;`
                                                                          CreateDate=$database.CreateDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastFullBackupDate=$database.LastBackupDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastLogBackupDate=$database.LastLogBackupDate.ToString("yyyy-MM-dd HH:mm:ss");`
                                                                          LastKnownGood=$LastKnownGood;`
                                                                          LastUserRead=$LastUserRead;`
                                                                          LastUserWrite=$LastUserWrite;`
                                                                          }
                    }
                 }
            }
            catch {
                Write-host -ForegroundColor Red $_.Exception.Message
            }
        }
        $global:TotalDatabaseStorage += $InstanceStorage
        $global:TotalDatabaseCount += $DatabaseCount

        if ($Debug) {
        
            Write-Host $InstanceName ": " $DatabaseCount " Databases ("$InstanceStorage.ToString("N") "MB )" 
        }

        $TFList = $Server.EnumActiveGlobalTraceFlags() | Where-Object Global -EQ 1 | Select-Object TraceFlag
        if (-not ([string]::IsNullOrEmpty($TFList))) {
            $TraceFlags = [string]::Join(",",$TFList.TraceFlag)
        }
        else {$TraceFlags = ""} 
        
        

        $ListInstances += New-Object PSObject -Property @{NetName=$Server.NetName;`
                                                         InstanceName=$Server.name;`
                                                         Edition=$Server.Edition;`
                                                         VersionMajor=$Server.VersionMajor;`
                                                         Version=$Server.VersionString;`
                                                         ProductLevel=$Server.ProductLevel;`
                                                         Collation=$Server.collation;`
                                                         Processors=$server.Processors;` 
                                                         PhysicalMemory=$Server.PhysicalMemory;`
                                                         MaxServerMemory=$Server.Configuration.MaxServerMemory.RunValue;`
                                                         DatabaseCount=$DatabaseCount;`
                                                         TotalSizeMB=$InstanceStorage.ToString("N");`
                                                         ServiceAccount=$Server.ServiceAccount;`
                                                         LoginMode=$Server.LoginMode;`
                                                         DatabaseEngineType=$Server.DatabaseEngineType;`
                                                         ActiveSessions=$server.EnumProcesses($false).Rows.Count;`
                                                         TraceFlags=$TraceFlags;`
                                                         }
    }

}


if ($OutGridView) {
    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`
                                                             Collation,Processors,PhysicalMemory,MaxServerMemory,DatabaseCount,`
                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `
                                                Out-GridView

    if ($CheckDatabaseDetails) {
        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,CompatibilityLevel,`
                                                                               ActiveConnections,RecoveryModel,Collation,AutoClose,AutoShrink,`
                                                                               IsReadCommittedSnapshotOn,PageVerify,Size,Owner,CreateDate,`
                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `
                                                                 Out-GridView
    }
}

 
if ($ExportCSV) {

    $ListInstances | Sort-Object InstanceName | Select-Object NetName, InstanceName,Edition,VersionMajor,Version,ProductLevel,`
                                                             Collation,Processors,PhysicalMemory,MaxServerMemory,DatabaseCount,`
                                                             TotalSizeMB,ServiceAccount,LoginMode,DatabaseEngineType,ActiveSessions,TraceFlags |   `
                                                Export-Csv $ExportCSVFile -NoTypeInformation  -Force -Delimiter ";"

    if ($CheckDatabaseDetails) {
        $ListDatabases | Sort-Object InstanceName,DatabaseName | Select-Object InstanceName,VersionMajor,DatabaseName,CompatibilityLevel,`
                                                                               ActiveConnections,RecoveryModel,Collation,AutoClose,AutoShrink,`
                                                                               IsReadCommittedSnapshotOn,PageVerify,Size,Owner,CreateDate,`
                                                                               LastFullBackupDate,LastLogBackupDate,LastKnownGood,LastUserRead,LastUserWrite | `
                                                                 Export-Csv $ExportCSVFile -NoTypeInformation -Force -Delimiter ";"
    }
}

# Display grand total 
if ($global:TotalDatabaseCount -gt 0) {
    write-host ""
    write-host "Grand Total :"
    Write-Host $global:TotalDatabaseCount " Databases ("$global:TotalDatabaseStorage.ToString("N") "MB )" 
}

if ($DisplayInstanceErrorList) {
    write-host ""
    write-host "Errors :"
    $InstanceErrorList
}


Bien entendu je vous encourage à modifier ce bout de code pour qu’il s’adapte à vos attentes … Merci cependant de conserver les crédits …

Happy PowerShell

Publicités
Publié dans PowerShell, SQL Server | Tagué | Laisser un commentaire

Erreurs 41017, 41160 et 41152 lors de la création d’un groupe de disponibilité

Il y a quelques semaines, je travaillais sur des groupes de disponibilités SQL Server, comme souvent me direz vous.
Malgré un grand nombre d’install chez divers clients, et encore plus de démos, j’ai eu affaire à une erreur jamais vue auparavant :

SNAGHTML50aa99b

Pour résumer, impossible de créer l’AG. Le cluster était monté, il était possible de créer une groupe de ressources et de le faire basculer les 2 nouds en question. Mon profil avait suffisamment de permissions et malgré tout en retour, les erreurs SQL 41017, 41160 et 41152.

En fouillant dans la log du Cluster (Get-Clusterlog), des erreurs bien plus parlantes sont apparues.

SNAGHTML514c8fa

DLL is not present !!!!

En fait, lorsque l’on veut mettre en place de la haute disponibilité au travers d’un WSFC, celui ci doit trouver un “moyen” de communiquer avec l’applicatif en HA. Donc pour tout rôle “clusterisable” on doit trouver les types de ressource correspondants, la fameuse DLL manquante : hadrres.dll.

Effectivement, point de SQL Server dans les propriétés du cluster :

SNAGHTML54f4f37

J’avoue ne pas avoir totalement le fin mot de l’histoire, pourquoi la DLL ne s’est pas correctement enregistrée …

J’aurais pu ajouter manuellement un type de ressource  mais J’ai opté pour une méthode assez radicale. J’ai désactivé la fonctionnalité Haute Disponibilité sur les services SQL, puis supprimé la feature cluster de Windows. Un reboot plus tard, l’ajout de la fonctionnalité et la réactivation de HADRON effectuées, les types de ressources sont bien présents.

SNAGHTML54d1350

SNAGHTML5539eda

Happy troubleshooting AlwaysON AGs ….

Publié dans SQL Server | Tagué , | Laisser un commentaire

Get-ChildItem ‘SQLSERVER:\SQLRegistration’ -Recurse

Un petit billet, rapide, pour se faire un peu peur avant le Week-End …

SSMS est un outil vraiment complet pour l’administration de SQL Server. Possibilité d’utiliser des dizaines d’assistants, écrire du code T-SQL, débugger, etc … Bref tous les administrateurs ont cet outil à portée de main. Les développeurs également, même si SSDT offre certaines fonctionnalités non présentes dans SSMS comme la comparaison de Schéma ou de Data, le refactoring de code en cas de renommage d’objet par exemple …

Il offre également la possibilité de travailler sur plusieurs instances simultanément, au travers des requêtes multi serveur. On peut enregistrer les différentes instances soit

  • au travers d’un Central Management Server : une instance SQL Server, potentiellement édition Express, est choisie comme repository et seule l’authentification Windows est utilisable
  • au travers des registered servers, dans ce cas la liste est enregistrée en local. Cela offre la possibilité de choisir un mode d’authentification Windows ou bien SQL. Extrêmement pratique. D’autnat plus que l’on peut opter pour la persistance du mot de passe afin de ne pas avoir à ressaisir ce satané mot de passe pour le compte SA qui fait 28 caractères !

Lorsque l’on me pose la question : que vaut il mieux utiliser comme méthode d’authentification, Windows ou bien SQL ? La casquette sécurité me fait répondre Windows, au travers de Kerberos. Mais fonctionnellement, c’est aussi se priver de connexions depuis des environnements non liés à Active Directory, comme des serveurs Linux par exemple. La réponse n’est donc pas si catégorique que cela.

Je vous propose donc de lancer deux lignes de PowerShell sur votre machine, sur laquelle est installée SSMS.

Import-module SQLPS
Get-ChildItem 'SQLSERVER:\SQLRegistration' -Recurse `
        | select displayname, servername, secureconnectionstring| ft -AutoSize

Si vous avez opté pour la sauvegarde de vos mot de passe, mais que vous avez quelques trous de mémoire, profitez en pour les noter, ils sont affichés en clair !

image

Voilà voilà, je pose ça là et je vous laisse donc modifier tout vos mots de passe, ne pas les persister dans SSMS et switcher tant que faire se peux sur une authentification Windows.

happy hacking …

Publié dans PowerShell, SQL Server | Tagué | Laisser un commentaire

AlwaysOn availability Groups – misaligned log IOs

Un petit billet, rapide, concernant la mise en place des groupes de disponibilité sous SQL Server.

Extrêmement simple à mettre en place (une installation classique de SQL Server, l’ajout de la fonctionnalité Windows Server failover Cluster, une case à cocher pour activer AlwaysOn Availability Group) et permettant de disposer à la fois d’une solution de haute disponibilité, de disaster recovery, voire de load balancing pour de la lecture massive sur une ferme de serveur secondaires (depuis SQL Server 2016 et 2017 nativement).

Simple à mettre en place, donc, mais il ne faut pas négliger les prérequis Active Directory et disque. En effet, même si le système est conçu pour fonctionner sur un stockage asymétrique (on n’est pas liée à une baie de disque ou à des disques en attachement local, on pout tout à fait panacher) il convient de respecter quelques règles.

La première assez évidente, bien qu’il soit possible de la transgresser, consiste à utiliser les mêmes lettres de volumes ou points de montage, les mêmes répertoires pour stocker les fichiers de Data et Log.

La seconde est de disposer de disques de … même génération ! En effet, depuis quelques temps, des disques dit 4K remplacent les « vieux » disques 512 octets. Attention, je ne parle pas du formatage au sens Allocation Unit Size NTFS, mais bien du pan physique du nombre d’octets par secteurs.

Imaginez donc mette en place un AG sur 2 réplicas, chacun sur une baie de disque spécifique. Tout fonctionne parfaitement, jusqu’au jour où on ajoute un tiroir dans une des baies et que l’on déplace un volume (opération extrêmement simple dans le cadre de la virtualisation) sur une LUN hébergées sur ce nouveau tiroir.

Et puis, le journal de transaction sur le serveur principal commence à grossir, et malgré les backup log, ne se vide pas …
Et le journal de transaction commence à présenter des messages mentionnant des IO mal alignés … Et un serveur secondaire qui n’est plus synchrone …
Hum hum …

image

Sur le serveur principal on dispose de disques 512 octets

clip_image002[5]

Alors que le serveur secondaire dispose lui de disques 4K.

clip_image002

Deux possibilités pour résoudre le problème :

  • Stopper l’instance sur le serveur secondaire, présenter un nouveau volume sur une LUN 512 octets, utiliser une commande du style ROBOCOPY /SEC pour recopier le contenu du volume de log et ensuite intervertir les lettre de volumes et relancer le service SQL.
  • La seconde possibilité consiste à utiliser le Trace Flag 1800 qui permet d’éliminer le problème. Une fois le service SQL redémarré, les erreurs disparaissent.

image

Well done.
Happy troubleshooting

Publié dans SQL Server | Tagué , | 3 commentaires

Script PowerShell–Check des sauvegardes

Le planning plutôt chargé de ces dernières semaines ne m’a pas permis de poster à ma guise sur ce blog. Malheureusement, l’année 2018 qui se profile risque d’être encore plus chargée … Le premier semestre est d’ores et déjà complet, quant au second, le taux de remplissage frôle déjà les 75%. Mais on ne va pas se plaindre, hein.

J’initie donc aujourd’hui une série de billets concernant PowerShell. Pour être totalement honnête, il m’a fallu un certain temps avant d’adhérer à ce langage. D’une part SQL Server dispose déjà d’un langage de script, pourquoi en utiliser un second ??? Et d’autre part, avant que le PS ne soit disponible, pour des besoins d’administration, je faisais beaucoup de VBScript.

Avec du recul, je regrette presque de ne pas avoir franchi le cap plus tôt ! Bon, cela fait quand même quelques années que je pratique maintenant (à mon niveau bien sûr) et ceux qui me suivant en formation ou lors d’évènements ont pu voir (et récupérer) quelques scripts que j’ai écrits. Soyons clair, ces scripts sont fonctionnels, et ne se targuent pas d’être une référence dans l’art de coder en PowerShell. Je reste tout à fait lucide quant à la marge de progression qui est la mienne dans ce langage.

Pour débuter cette série, je vous propose un script permettant de checker vos sauvegardes rapidement. Le script va lister les instances visibles et se connecter (authentification Windows) et vérifier les dates des dernières sauvegardes. Eh oui, n’oubliez pas qu’un DBA est jugé sur sa capacité à restaurer une base. Alors pas question « d’oublier » des bases …




[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null

clear-host 

# Can use a predefined list instead ....
$InstanceList = sqlcmd -L
# or
# $InstanceList = [System.Data.Sql.SqlDataSourceEnumerator]::Instance.GetDataSources()
# or
# $InstanceList = "srv1","srv2"

# excluded instances
$ExcludedHostList = "devsql2016","devsql2017"

$ShowSystemDatabases = $false 
[int]$global:TotalDatabaseStorage = 0
[int]$global:TotalDatabaseCount = 0


$Debug = $false
$ConnectionTimeout = 1

$CheckBackup = $true
$PctLogUsedThreshold = 70
$LastFullBackupAge = -1
$LastDiffBackupAge = -1 # hoping there is at least a full or a diff backup the day before ...

$LastdbccLastKnownGoodAge = -7

$CheckAutoShrink = $true
$CheckAutoClose = $true
$CheckdbccLastKnownGood = $true

$InstanceErrorList = @()
$DisplayInstanceErrorList = $false


function Get-SQLBackup ([object] $Server) {

    Write-host  -ForegroundColor Green "Connecting "$Server.name
    Write-host $Server.Edition " - Version " $Server.VersionString " (" $Server.ProductLevel ") - " $Server.collation

    if (!($Server.ConnectionContext.IsInFixedServerRole("sysadmin"))) { 
        Write-Host $Server.ConnectionContext.TrueLogin " is not sysadmin !" -ForegroundColor Magenta
        $InstanceErrorList += "Missing permissions on $InstanceName"
    }

    $Databases = $server.Databases | Where-Object Status -eq "normal" | sort-object ID


    write-host ""
    $InstanceStorage = 0
    foreach ($Database in $Databases) {
         try {
             $InstanceStorage += $Database.size


             if (!($Server.ConnectionContext.IsInFixedServerRole("sysadmin"))) { 
                continue 
             }



             If (($ShowSystemDatabases) -or ($Database.iD -gt 4)) {
                Write-Host $Database "("$Database.size.ToString("N") "MB ) " -NoNewline

                if ($CheckdbccLastKnownGood) {
                    $LastKnownGood = $($Database.ExecuteWithResults("DBCC DBINFO() WITH TABLERESULTS").Tables[0] | Where-Object {$_.Field -eq "dbi_dbccLastKnownGood"}).value #  | Select-Object Value
                    $LastKnownGood = [datetime]::ParseExact($LastKnownGood.split(" ")[0],'yyyy-MM-dd',$null)
                    if ($LastKnownGood -lt (Get-date).AddDays($LastdbccLastKnownGoodAge)) {
                        Write-Host " | CheckDB " $LastKnownGood.ToString("yyyy-MM-dd") " " -NoNewline -ForegroundColor Red
                    }
                    else{
                        Write-Host " | CheckDB " $LastKnownGood.ToString("yyyy-MM-dd") " " -NoNewline 
                    }
                 }
                
                
                if ($Database.AutoShrink -and $CheckAutoShrink) {
                    Write-Host " | AutoShrink " -NoNewline -ForegroundColor Red
                }
                if ($database.AutoClose -and $CheckAutoClose) {
                    Write-Host " | AutoClose " -NoNewline -ForegroundColor Red
                }


                if ($CheckBackup) {

                    $PercentLogUsed =  [math]::round(($Database.LogFiles[0].UsedSpace*100.0/($Database.LogFiles[0].Size)),2)
                    if ($PercentLogUsed -ge $PctLogUsedThreshold) {
                        Write-Host "| Recovery" $database.RecoveryModel "("$database.LogReuseWaitStatus","$PercentLogUsed "% ," $([math]::round(($Database.LogFiles[0].UsedSpace),2)) "KB)"   -NoNewline -ForegroundColor red
                    }
                    else {
                        Write-Host "| Recovery" $database.RecoveryModel "("$database.LogReuseWaitStatus","$PercentLogUsed "% ," $([math]::round(($Database.LogFiles[0].UsedSpace),2)) "KB)"   -NoNewline 
                    }
                     

                    if ($database.LastBackupDate -lt (Get-date).AddDays($LastFullBackupAge)) {
                        $OupsHopeForDiffBackup = $true
                    }
                    else{
                        $OupsHopeForDiffBackup = $false
                    }

               
                    if ($OupsHopeForDiffBackup) {
                        if ($database.LastDifferentialBackupDate -lt (Get-date).AddDays($LastDiffBackupAge)) {
                            $ButThereIsaDiffBackup = $false
                        }
                        else{
                            $ButThereIsaDiffBackup = $true
                        }
                    }

                    if (($OupsHopeForDiffBackup) -and (!($ButThereIsaDiffBackup)) ) {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Red
                        Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Red
                    }
                    elseif (($OupsHopeForDiffBackup) -and ($ButThereIsaDiffBackup)) {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline -ForegroundColor Magenta
                        Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline
                    }
                    else {
                        Write-Host " | Last Full" $database.LastBackupDate.ToString("yyyy-MM-dd") -NoNewline 
                        if ($database.LastDifferentialBackupDate.Year -ne 1){
                            Write-Host " | Last Diff" $database.LastDifferentialBackupDate.ToString("yyyy-MM-dd") -NoNewline
                        }
                        else {
                            Write-Host " | Last Diff - none - " -NoNewline
                        }
                   
                    }


                    if ($database.RecoveryModel -ne "Simple"){
                        if ( $database.LastLogBackupDate -lt (Get-date).AddHours(-4) ) {
                            Write-Host " | Last Log" $database.LastLogBackupDate.ToString("yyyy-MM-dd hh:mm:ss") -NoNewline -ForegroundColor Red
                        }
                        else
                        {
                            Write-Host " | Last Log" $database.LastLogBackupDate.ToString("yyyy-MM-dd hh:mm:ss") -NoNewline 
                        }
                    }
                    else {
                        Write-Host " | Last Log N/A" -NoNewline
                    }
                }

                write-host ""
             }
        }
        catch {
            
            if ($Debug) {
                #Write-Host "Missing permissions on $server $Database" -ForegroundColor Red
                Write-host -ForegroundColor Red $_.Exception.Message
            }
        }
    }


    Write-Host ""
    write-host $server.Databases.Count "Databases ("$InstanceStorage.ToString("N") "MB )"
    $global:TotalDatabaseStorage = $global:TotalDatabaseStorage + $InstanceStorage   
    $global:TotalDatabaseCount = $global:TotalDatabaseCount  + $server.Databases.Count
}



ForEach ($InstanceName in $InstanceList) {
    $InstanceName = $InstanceName.trim()
    if ($InstanceName -eq "") {continue}
    if ($InstanceName -eq "Servers:") {continue}

    # Check excluded instances
    if ($ExcludedHostList -contains $InstanceName) {
        if ($Debug) {
            write-host "#############################################################################" -ForegroundColor yellow
            Write-Host $InstanceName " excluded" -ForegroundColor yellow
            write-host "_____________________________________________________________________________" -ForegroundColor yellow
            Write-Host " "
        }
        continue
    }
        
    $Server = New-Object -TypeName  Microsoft.SQLServer.Management.Smo.Server($InstanceName)
    $Server.ConnectionContext.ConnectTimeout = $ConnectionTimeout

    if (!($Server.ComputerNamePhysicalNetBIOS)) {
        $InstanceErrorList +="Error connecting $InstanceName"
    }
    else {
        write-host "#############################################################################" -ForegroundColor Green
        Get-SQLBackup $Server
        write-host "_____________________________________________________________________________" -ForegroundColor Green
        Write-Host " "
    }

}


# Display grand total 
if ($global:TotalDatabaseCount -gt 0) {
    write-host ""
    write-host "Grand Total :"
    Write-Host $global:TotalDatabaseCount " Databases ("$global:TotalDatabaseStorage.ToString("N") "MB )" 
}

if ($DisplayInstanceErrorList) {
    write-host ""
    write-host "Errors :"
    $InstanceErrorList
}

Bien entendu je vous encourage à modifier ce bout de code pour qu’il s’adapte à vos attentes … Merci cependant de conserver les crédits …

Happy PowerShell

Publié dans PowerShell, SQL Server | Tagué | 2 commentaires

SSMS 17.3 est disponible

Comme vous le savez probablement, depuis l’été 2016, et SQL Server 2016, SSMS possède dorénavant son propre cycle de vie, avec des mises à jour régulières, et dont la livraison est totalement distincte de SQL Server, le SGBD. SSMS, tout comme SSDT, ne ont plus livrés sur le média d’installation du moteur relationnel mais téléchargeable ici.

Je vous encourage a télécharger et installer la dernière version en date, estampillée 17.3, qui outre le support de SQL Server 2017 (mais les versions précédentes l’étaient aussi, apporte quelques améliorations, dont deux ont retenu mon attention.

Tout d’abord, et c’était une attente forte de toute les personnes, dont je fais partie, qui lors de démos passent un temps considérable à configurer une session xEvent pour ne pas avoir à utiliser le bon vieux Profiler, ou l’API SQL Trace. D’un point de vue Troubleshooting, ils sont tous deux utiles, mais que de consommation de ressource ! D’où le conseil de basculer sur le profiler. Oui, c’est un peu plus long à mettre en œuvre. C’était devrais-je dire.

Un nouvel item est apparu dans l’interface de SSMS : le XE Profiler

image

Cela permet de, très rapidement, configurer une session de xEvent, de la démarrer et de visualiser le contenu à l’écran. Un équivalent de trace par défaut du profiler. Bien joué !

Deux sessions sont proposées : Standard et TSQL. Chacune d’elle lors du premier démarrage va créer une “vrai” session XE.

image

Passons rapidement sur la session TSQL qui n’offre à mes yeux, moins d’intérêt que sa consœur. Seulement du BatchStarting ou RPCStarting, ce n’est pas suffisent pour faire du Troubleshooting.

image

La session Standard est bien plus intéressante puisqu’elle reprend les évènements Starting mais aussi les events Completed. D’ailleurs, si j’avais eu mot à dire, je n’auris opté que pour les Completed, mais bon …

image

Si vous êtes du même avis, je vous suggère de modifier le xEvent créé et votre modèle sera conservé tant que vous ne supprimerez pas la session.

Le point vraiment positif reste sans conteste le lancement vraiment rapide de la “trace”. Un click droit “Launch” et la session démarre immédiatement et produit un affichage. Pensez à faire un Stop Session, fermer la fenêtre ne suffit pas !

image

Ensuite, rien de neuf, on garde toute la puissance des xEvents et surtout de la capacité native de SSMS à fournir des opérations de regroupement et d’agrégation qu’on lui connaissait avant.

Bref, un petit incontournable.

 

La seconde fonctionnalité, du coup peut paraitre anecdotique. Mais à y regarde de plus près, l’import de fichier texte, même relativement bien formé, à la mode CSV, peut parfois s’avérer problématique lors de démos au pied levé, alors que cela devrait être on ne peut plus fluide.

Un nouvel assistant voit le jour : Import Flat File

image

Quoi de neuf me direz vous ?

Imaginez un fichier de ce type : on voit bien un format CSV qui se dessine, mais il y a un certain nombre de lignes à “exclure” en début de fichier.

image

Simple à gérer avec un BULKINSERT (idem avec un OPENROWSET). Mais il faut prendre le temps de créer la table, de coder l’insert …

image

Lors d’une démo c’est clairement moins pratique. Ce nouvel assistant va permettre d’importer rapidement le fichier sans erreur :

imageimageimageimage

Dans ce fichier, SQL Server n’a pas réussi à deviner le nom des colonnes. D’ailleurs en regardant de près les données importées, je me suis aperçu qu’un certain nombre de lignes manquaient. Les premières de mon fichier ! Comme si la machine réalisait un “apprentissage” des données. Pas trop gênant en démo, beaucoup plus en production.

Pour les curieux, c’est basé sur sur le PROSE SDK (https://microsoft.github.io/prose/), le programme va rechercher des patterns dans les données pour en déduire la structure.

Par contre, il faut admettre qu’un certain effort a été fait sur la reconnaissance des type de données. Tout comme le traitement automatique de chaines de caractère avec des caractères de type DoubleQuote qui auparavant faisait échouer le Wizard. Je vous le concède, ce n’est pas parfait, mais c’est toujours mieux que l’assistant d’import de données historique.

Je vous encourage à télécharger et installer …

md c:\sources
cd c:\sources
Invoke-WebRequest -Uri "https://go.microsoft.com/fwlink/?linkid=858904" -OutFile ssms-setup-enu.exe 
.\ssms-setup-enu.exe /install /passive

Enjoy

Publié dans SQL Server | Tagué | 3 commentaires

SQL Server 2012 SP4 disponible

Avec la sortie récente de SQL Server 2017 certaines news passent un peu inaperçu. Voici donc un petit rappel : le Service Pack 4 pour SQL Server 2012 vient d’être publié. Rien d’extraordinaire me direz vous : eh bien si. Il s’agit tout simplement du dernier service pack pour cette version. Aucun autre ne verra le jour, SQL Server 2012 étant en fin de support “Mainstream”. Donc a moins que vous ne payez pour un support étendu, il n’y aura pas d’autres correctifs pour cette version. De quoi vous inciter à migrer vers une version plus récente !

Selon le communiqué officiel, outre des corrections de bug, plus de 20 améliorations ont été apportées, tant au niveau performance que scalabilité ou diagnostic.

A télécharger d’urgence ici.

Enjoy

Publié dans SQL Server | Tagué , | Laisser un commentaire

SQL Server 2017 disponible au téléchargement

Il y a quelques jours, SQL Server 2017 a été publiquement annoncé. Première version de SQL Server pouvant être exécuté sur Windows, Linux et Docker, les images étaient disponibles sur Azure sur les différentes plateformes.

image

Pour ceux qui souhaitent utiliser OnPrem SQL Server 2017, une édition Express, une édition développeur et une version d’essai, édition entreprise limitée à 180 jours peut être téléchargée ici.

image

Pour les personnes disposant d’un abonnement MSDN, le portail présente les édition Développeur et Express. Espérons que les autres éditions seront rapidement disponibles.

image

Happy download …

Publié dans SQL Server | Tagué , | Laisser un commentaire

SQL Server 2016–Failover d’un groupe de disponibilité distribué

Dans le précédent article, afin de pouvoir bénéficier d’un groupe de disponibilité distribué entre 2 sites, nous avons créé 2 clusters. Chaque cluster, et donc chaque groupe de disponibilité assurait la HA localement sur un site. Le groupe de disponibilité distribué, lui, assurait le Disaster Recovery en cas de perte de l’un des sites.

Pour faire suite, donc à ce première article, je vous propose donc une petite procédure permettant de basculer sur le site de secours, de manière planifiée.

Encore une fois, rien de vraiment complexe au niveau des scripts. Mais il convient d’être méticuleux et patient, de ne pas basculer trop tôt sur le site de DR sans avoir attendu que la synchronisation des données soit terminée avant de rendre le site actif.

Dans un premier temps, il faut basculer en mode synchrone le réplica secondaire du DAG, correspondant au Listener de l’AG local au site 2.

$tSQL = "
ALTER AVAILABILITY GROUP [distributedag]  
   MODIFY   
   AVAILABILITY GROUP ON  
      'DC1-AG' WITH    
   (   
         LISTENER_URL = 'tcp://DC1-AG-VIP.demo.local:5022',    
          AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
      ),   
      'DC2-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC2-AG-VIP.demo.local:5022',   
         AVAILABILITY_MODE = SYNCHRONOUS_COMMIT
      );    
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"

Il faut alors attendre que la synchronisation soit totalement effectuée pour s’assurer qu’il n’y aura pas de perte de donnée. La requête suivante affiche l’état de synchronisation des différentes réplicas des AG et du DAG. Ainsi, tant que le réplica primaire du site secondaire n’est pas SYNCHRONIZED, il convent d’attendre.

tSQL = "
SELECT   ar.replica_server_name
       , ag.name as availabilitygroup_name
       , ag.is_distributed
       , DB_NAME(drs.database_id) As database_name
       , drs.is_primary_replica
       , drs.synchronization_state_desc
       , drs.synchronization_health_desc
       , drs.log_send_queue_size
       , drs.redo_queue_size
       , drs.end_of_log_lsn 
       , drs.last_sent_lsn
       , drs.last_received_lsn
       , drs.last_hardened_lsn
       , drs.last_redone_lsn
       , drs.secondary_lag_seconds
FROM sys.dm_hadr_database_replica_states drs 
INNER JOIN sys.availability_groups ag ON drs.group_id = ag.group_id
inner join sys.availability_replicas ar on ar.replica_id = drs.replica_id

"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1" | Out-GridView

Pour effectuer ne bascule vers le site de secours, il faut ensuite spécifier au site 1 qui set primaire qu’il prend le rôle de secondaire.

# first transform primary into secondary
$tSQL = "
ALTER AVAILABILITY GROUP [distributedag] SET (ROLE = SECONDARY);    
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"

Et ensuite de provoquer la bascule depuis le réplica primaire de l’AG du site 2.

# and then failover from a secondary
$tSQL = "
ALTER AVAILABILITY GROUP [distributedag] FORCE_FAILOVER_ALLOW_DATA_LOSS;
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"

Une fois cette manipulation terminée, on peut revenir sur un mode Asynchrone pour tous les réplicas.

$tSQL = "
ALTER AVAILABILITY GROUP [distributedag]  
   MODIFY   
   AVAILABILITY GROUP ON  
      'DC1-AG' WITH    
  (   
          LISTENER_URL = 'tcp://DC1-AG-VIP.demo.local:5022',    
           AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
      ),   
      'DC2-AG' WITH    
      (   
         LISTENER_URL = 'tcp://DC2-AG-VIP.demo.local:5022',   
         AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
      );    
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"

 

Le DAG est maintenant primaire sur le site 2.

Le Fail Back ne présente aucune difficultés. Il suffit de changer les noms de réplicas sur le script précédent.

Point important : il n’est pas possible de créer de listener sur un DAG. Les plus attentifs parmi vous auront noté que je n’en ai pas parlé dans l’article précédent. Il reste donc une opération manuelle sur un serveur DNS par exemple afin de rediriger le trafic sur le listener du second site. Il set aussi possible de passer par des Load Balancers du marché.

Happy DR !

Publié dans SQL Server | Tagué , , , | Laisser un commentaire

SQL Server 2016–Ajout d’une base dans un groupe de disponibilité distribué

Dans le précédent article, afin de pouvoir bénéficier d’un groupe de disponibilité distribué entre 2 sites, nous avons créé 2 clusters. Chaque cluster, et donc chaque groupe de disponibilité assurait la HA localement sur un site. Le groupe de disponibilité distribué, lui, assurait le Disaster Recovery en cas de perte de l’un des sites.

Pour faire suite, donc à ce première article, je vous propose donc une petite procédure permettant d’ajouter une base dans un groupe de disponibilité local et donc de propager cette base sur le DAG.

Le script est relativement simple. Globalement, l’ajout dans l’AG sur le site principal se fait au travers du Direct Seeding et du ALTER AVAILABILITY GROUP ADD DATABASE.

Mais pour le site secondaire il est nécessaire de procéder à l’initialisation des réplicas (les deux, le primaire et le secondaire) au travers d’un backup/restore. Ensuite, la base rejoint le DAG et l’AG local.


$Database = "NewDB"
$tSQL = "
CREATE DATABASE [$Database];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"


# because we need to manually deploy the database to secondary site
# a regular backup is mandatory
$tSQL = "
BACKUP DATABASE [$Database] TO DISK = '$Database.bak' WITH INIT,FORMAT;
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"

$tSQL = "
BACKUP LOG [$Database] TO DISK = '$Database.trn' WITH INIT,FORMAT;
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"



$tSQL = "
ALTER AVAILABILITY GROUP [DC1-AG]
ADD DATABASE [$Database];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC1-SQL1"


Copy-Item E:\MSSQL\Backup\$Database.bak  \\DC2-SQL3\E$\MSSQL\Backup\$Database.bak 
Copy-Item E:\MSSQL\Backup\$Database.trn  \\DC2-SQL3\E$\MSSQL\Backup\$Database.trn 
Copy-Item E:\MSSQL\Backup\$Database.bak  \\DC2-SQL4\E$\MSSQL\Backup\$Database.bak 
Copy-Item E:\MSSQL\Backup\$Database.trn  \\DC2-SQL4\E$\MSSQL\Backup\$Database.trn


$tSQL = "
RESTORE DATABASE [$Database] FROM DISK = '$Database.bak' WITH NORECOVERY;
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

$tSQL = "
RESTORE LOG [$Database] FROM DISK = '$Database.trn' WITH NORECOVERY;
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"



$tSQL = "
ALTER DATABASE [$Database] 
SET HADR AVAILABILITY GROUP = [distributedag];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL3"


$tSQL = "
ALTER DATABASE [$Database] 
SET HADR AVAILABILITY GROUP = [DC2-AG];
"
Write-Host $tSQL 
Invoke-SqlCmd -Query $tSQL -Serverinstance "DC2-SQL4"

 

Enjoy !

Publié dans SQL Server | Tagué , , , | Laisser un commentaire