PI Services

Le blog des collaborateurs de PI Services

SQL SSRS–Renouvellement de certificat

Contexte

Lors d’un renouvellement de certificat WEB sur une instance SSRS, le mapping du nouveau certificat échoue avec l’erreur suivante :

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An SSL binding already exists for the specified IP address and port combination. The existing binding uses a different certificate from the current request. Only one certificate can be used for each IP address and port combination. To correct the problem, either use the same certificate as the existing binding, or remove the existing SSL binding and create a new binding using the certificate of the current request.

Explication

Le message d’erreur précise que la combinaison IP:Port est déjà mappée à un certificat existant (celui que l’on tente de renouveler).

La commande suivante permet de visualiser le binding existant :

netsh http show sslcert

image

On remarque que le certificat que l’on tente de renouveller est mappé à la combinaison IP:Port suivante : [::]:443

Résolution

A l’aide de la commande précédemment citée, récupérer la combinaison IP:Port utilisée par le certificat que l’on tente de renouveler, puis supprimer le mappage à l’aide de la commande suivante :

netsh http delete sslcert ipport=[::]:443



DNS–Impossible de supprimer une Zone

Contexte

Depuis une console lancée en tant qu’administrateur du domaine il est impossible de supprimer une zone DNS existante, une erreur de type “AccessDenied” est retournée :

image

Explication

En investiguant sur la cause de cette erreur, on remarque qu’une ACE refuse au groupe Tout le Monde de supprimer cet objet :

image

Cette ACE est en fait créée automatiquement lorsque la protection contre la suppression accidentelle est activée.

Résolution

Après la suppression de cette entrée, il est possible de supprimer l’objet sans erreurs.

SQL Server Mirroring–Réactiver le mirroring sur une base suspendue

Contexte

La mise en miroir des bases de données est une solution de haute-disponibilité fournie par SQL Server depuis la version 2005. Cette fonctionnalité s’implémente au niveau de chaque base de donnée.

L’avantage de cette solution est de disposer d’une base de donnée active en tout temps, ce qui permet de réaliser des maintenances sur un serveur sans interrompre l’accès aux bases utilisées par des application clientes.

Cette fonctionnalité sera supprimée dans une prochaine version de Microsoft SQL Server. Évitez d'utiliser cette fonctionnalité dans de nouveaux travaux de développement, et prévoyez de modifier les applications qui utilisent actuellement cette fonctionnalité. Utilisez à la place Groupes de disponibilité Always On.

Cause

Voici les principales causes provoquant une interruption de la replication Mirroring :

  • Les serveurs partner (+ eventuellement witness) ne communiquent pas correctemment,
  • Erreur d’espace disque sur l’un des serveurs partner,
  • Changement du recovery model sur le serveur principal (le mirroring requière le recovery model Full).

Résolution

La commande suivante exécutée sur l’un des serveur partner permet de rétablir la réplication :

ALTER DATABASE DATABASE SET PARTNER RESUME

SQL Server–Optimiser les paramètre d’exécution parallèle (1/2)

Introduction :

SQL Server est conçu pour optimiser l’exécution des requêtes de manière transparente, l’une des méthodes utilisé par SQL est le calcul parallélisé de requêtes.

Cette méthode d’optimisation consiste à diviser l’exécution d’une requête à travers threads afin d’accélérer l’exécution de la requête.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

  • MAXDOP (partie 1/2),
  • Cost Threshold for Parallelism (partie 2/2)

Explication :

Le paramètre MAXDOP signifie “Max Degree of Parallelism”, ce paramètre permet de maitriser le nombre maximum de processeurs pouvant intervenir dans l’exécution d’une requête parallélisée, il est important de comprendre que ce paramètre ne peut être utilisé pour limiter le nombre de processeurs utilisés par SQL.

Ce lien liste les valeur recommandées par Microsoft afin de paramétrer cette valeur en fonction des versions de SQL Serveur, ce lien peut également être utilisé pour calculer la valeur optimale à positionner. Il est bien entendu nécessaire de tester les différentes configurations en fonction de la charge soumise par l’application qui utilise le serveur SQL.

Réalisation

Le paramètre MAXDOP peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

image

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
dbo.sp_configure 'max degree of parallelism', VALEUR;
GO

RECONFIGURE;
GO

La modification du paramètre MAXDOP est prise en compte sans qu’il n’y a à redémarrerredemmarer l’instance.

Sources :

SQL Server–Optimiser les paramètre d’exécution parallèle (2/2)

Introduction :

Comme vu dans le post précèdent, SQL Server dans sa configuration par défaut optimise automatiquement l’exécution des requêtes, notamment en répartissant son exécution à travers différents threads.

Ce post composé de deux parties à pour objectif de présenter les paramètres suivants :

  • MAXDOP (partie 1/2),
  • Cost Threshold for Parallelism (partie 2/2)

La partie 2/2 de ce post auras pour objectif de configurer SQL afin que seules les requêtes considérées comme volumineuses ne soient traitées en parallèle.

Explication :

Le paramètre “Count Threshold for Parallelism” permet à SQL de ne paralléliser que les requêtes dont le coût d’exécution d’un plan en série (mesuré en secondes) serait plus élevé que la valeur définie.

L’optimisation réalisée par SQL, bien qu’efficace pour les requêtes “volumineuses”, peut se révéler désastreuse pour l’exécution concurrente d’un grand nombre de petites requêtes, il est donc judicieux de revoir la valeur de ce paramètre en fonction du type de charge exercée sur le serveur SQL.

En règle générale, une valeur comprise entre 20 et 50 est un bon début qui doit être affiné par des tests spécifiques à l’application.

Par défaut, la valeur du paramètre “Count Threshold for Parallelism” est de 5, cette valeur provient du temps nécessaire à l’exécution d’une requête sur le poste de “Nick” un employé de Microsoft membre de l’équipe Query Optimizer lorsque le Query Optimizer était en cours de développement pour SQL Server 7.

Nick devait – entre autre – développer la méthode de calcul du coût d’une requête, il a donc utilisé son ordinateur comme étalon, depuis, le coût d’exécution d’une requête estimé par SQL est….le temps d’exécution qu'aurais pris la machine de Nick.

Bien entendu, les machines utilisées ne sont plus les mêmes et les temps d’exécutions sont donc plus courts :

image

La machine de Nick.

SQL Server ignore la valeur de l'option cost threshold for parallelism dans les cas suivants :

  • L'ordinateur ne dispose que d'un seul processeur.
  • Un seul UC est disponible pour SQL Server en raison de l'option de configuration affinity mask.
  • L'option max degree of parallelism a la valeur 1.

 

Réalisation

Le paramètre Count Threshold for Parallelism peut être modifié depuis les propriétés de l’instance SQL concernée dans la partie Advanced->Parallelism :

image

Ou via la requête suivante (en remplaçant VALEUR par la valeur désirée) :

sp_configure 'show advanced options', 1;
GO
reconfigure;
GO
sp_configure 'cost threshold for parallelism', VALEUR;
GO
reconfigure;
GO

La modification du paramètre Count Threshold for Parallelism est prise en compte sans qu’il n’y a à redémarrer l’instance.

Sources :

SQL Server–Rapport de découverte (Discovery Report)

Introduction

Le rapport de découverte SQL Server est utile afin de lister toutes les informations relatives à aux composants installés sur votre serveur SQL, leur version et leur édition.

L’objectif de ce post est de générer un rapport en mode GUI et en via un script.

Réalisation

  • En mode GUI :

Exécuter “SQL Server Installation Center :

image

Aller dans l’onglet “Tool” et cliquer sur “Installed SQL Server features discovery report” :

image

Le rapport de découverte SQL Server est enregistré dans “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\Log\AAAAMMDD_XXXXX

  • En mode script :

Depuis un invite de commande, exécuter la commande suivante: “Setup.exe /q /Action=RunDiscovery” depuis le répertoire “C:\Program Files\Microsoft SQL Server\VERSION\Setup Bootstrap\SQLServer2012”

image

Sources

SQL Server–Supprimer l’un des fichiers utilisé par la TempDB

Contexte

Lors de la suppression d’un des fichiers utilisé par la base TempDB, l’opération échoue avec l’erreur suivante :

Msg 5042, Level 16, State 1, Line 1
The file 'tempdev2' cannot be removed because it is not empty.

Explication

Ce comportement est provoqué par le fait que le fichier est actuellement utilisé par SQL, il faut donc vider le fichier avant de pouvoir le supprimer.

Une solution possible est le redemmarage de l’instance SQL, ce qui va regenerer la base TempDB, moyennant une coupure de service.

Résolution

Afin d’éviter toute coupure de service, il est possible d’executer le script SQL suivant pour vider puis supprimer un fichier utilisé par la TempDB :

USE [tempdb]
GO
DBCC SHRINKFILE('tempdev2', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [tempdev2]
GO

Sources

https://tenbulls.co.uk/2016/01/13/problem-removing-files-from-tempdb/

SQL Server–Reconfigurer les plans de maintenances après avoir renommé une instance SQL

Introduction

Après avoir renommé un serveur SQL, les plans de maintenances existants échouent avec l’erreur suivante :

Réalisation

1. Lister les procédures stockées en exécutant le script suivant sur la base MSDB et récupérer la valeur “ID” :

SELECT  x.*,
        LocalServerConnectionString = cm.value('declare namespace DTS="www.microsoft.com/SqlServer/Dts";DTS:ObjectData[1]/DTS:ConnectionManager[1]/@DTS:ConnectionString', 'varchar(1000)')
FROM (
    SELECT  id, name, packageXML = CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML)
    FROM dbo.sysssispackages
    WHERE id IN (SELECT id FROM dbo.sysmaintplan_plans)
) x
CROSS APPLY packageXML.nodes('declare namespace DTS="www.microsoft.com/SqlServer/Dts";/DTS:Executable/DTS:ConnectionManagers/DTS:ConnectionManager[@DTS:ObjectName="Local server connection"]') p(cm)

Voici un exemple de résultat :

image

2. Modifier la valeur “Data Source” à l’aide du script suivant :

UPDATE dbo.sysssispackages SET packagedata = CAST(CAST(REPLACE(CAST(CAST(packagedata AS VARBINARY(MAX)) AS VARCHAR(MAX)), 'OldServerName', 'NewServerName') AS XML) AS VARBINARY(MAX))
WHERE id = ‘ID'

Modifier les valeur en rouge :

  • OldServerName : Ancien nom de l’instance SQL telle qu’indiquée dans la partie “Data Source” de la colonne “LocalServerConnectionString” de la requête exécutée précédemment,
  • NewServerName : Nouveau nom de l’instance SQL,
  • ID : GUID de la tâche planifiée à reconfigurée telle qu’indiquée dans la colonne“ID” de la requête exécutée précédemment.

Vérification

  • Valider en exécutant la requête listant les procédure stockées que le nom de la nouvelle instance apparaît bien dans la partie “Data Source” de la colonne “LocalServerConnectionString”,
  • Exécuter le plan de maintenance et valider qu’il s’exécute correctement.

Liens Utiles

KEMP – Ré-écriture d’URL

Introduction

Lors de la publication d’une application WEB via KEMP, vous pouvez être amené à publier l’application en utilisant une URL différente de celle utilisée en interne.

Cet article explique la configuration à mettre en place afin de publier une application sous différents noms sans avoir à créer les bindings correspondant sur le serveur WEB.

Contexte

Une application nommée “APPLI1” est utilisée en interne via l’URL “https://appli1.internaldomain.dom”, le domaine publié en externe est nommé “https://appli1.externaldomain.com”.

L’application “APPLI1” ne supportant pas l’utilisation de bindings multiples, le KEMP de publication doit donc

  1. recevoir depuis l’extérieur les demandes provenant de l’URL “https://appli1.externaldomain.com”
  2. transmettre ces demandes en interne vers l’URL “https://appli1.internaldomain.dom”

Réalisation

Afin de mettre en place la réécriture d’URL, il faut créer les règles suivantes :

HTTP Header Modifications Rule :

image

Cette règle seras utilisée pour transformer l’URL “appli1.externaldomain.com” en “appli1.internaldomain.dom”

Cette règle doit être appliquée au niveau de la VIP dans “Advanced Properties\HTTP Header Modification”

image

Content Matching Rule :

image

Cette règle est utilisée afin que la VIP réponde à l’URL “*.externaldomain.com/*”

L’option content switching doit être activée depuis “Advanced Properties\Content Switching”

image

Cette règle doit être appliquée au niveau de la VIP dans “Real Servers\Rules” pour chaque serveur devant répondre à l’URL externe

image

Dans le cas où l’URL interne serait également publiée, il faut penser à activer la “default” content switching rule niveau de la VIP dans “Real Servers\Rules” pour chaque serveur devant répondre à l’URL externe et interne, la default rule doit toujours être positionnée en dernier :

image

PowerShell – Envoi de fichier chiffrés via FTP

Introduction

Afin de réaliser une action régulière et standart, deux solutions sont possible :

  • Réaliser l’action manuellement,
  • Développer un script qui s’en charge automatiquement.

Prérequis

  • Powershell,
  • 7zip,
  • Créer une tâche planifiée

Script

Le script suivant permet d’automatiquement envoyer par FTP dans un fichier chiffré, ce script utilise principalement les variables suivantes :

  • $days_to_send : L’age maximum des fichiers à envoyer
  • $source_path : Repertoire contenant les fichier à envoyer
  • $zipdirectory : Repertoire provisoire où seront compressé les fichiers
  • $log_location : emplacement du fichier de log
  • $sz : emplacement l’executable de 7zip
  • $ftp : FTP cible
  • $user : username utilisé pour se connecter au FTP
  • $pass : password utiliser pour se connecter au FTP et pour chiffré le fichier ZIP
  • $From : Adresse email qui emet les notifications par mail
  • $To : Adresse email recevant les notifications par mail
  • $SMTPServer : serveur SMTP utilisé pour les notifications par mail

 

#MAIN
function main
{
#Date Variables
$full_date = Get-Date
$custom_date = $full_date.ToString("yyyyMMdd")
$days_to_send = 4
$limit = $full_date.AddDays(-$days_to_send)

#Location Variables
$source_path = "E:\BACKUP\DATA\Database"
$zipdirectory = "G:\FTP_Folder\TEMP_ZIP"
$log_location = "G:\FTP_Folder\Logs"
$sz = ("C:\Program Files\7-Zip\7z.exe")

#FTP Variables
$ftp = "ftp://ftpr.domain.com/" 
$user = "FTP_Username" 
$pass = "FTP_Password"

#General variables
$LogFile
$compressed_folder
$folder_to_compress
$environment = "PROD"

#Mail Variables
$From = "Hatem.DJELASSI@piservices.com"
$To = "Hatem.DJELASSI@piservices.com"
$Cc = "Jean.DUPONT@piservices.com"
$Attachment
$Subject 
$Body
$SMTPServer = "smtp.domain.com"
$SMTPPort = "25"

Create-log-file -_log_location $log_location;

#Start Log
Append-log-file -_text "---------------------------------------------------------------"
Append-log-file -_text "START LOG @$full_date"

Create-Directory -_path $zipdirectory;
Copy-backups-older-than -_source_path $source_path -_destination_path $folder_to_compress -_limit $limit;
Compress_folder -_sz $sz -_pass $pass -_zipdirectory $zipdirectory -_folder_to_compress $folder_to_compress;
Delete_Folder -_file_to_delete $folder_to_compress;
Send_FTP -_user $user -_pass $pass -_ftp $ftp -_file_to_send $compressed_folder;
Notify -_from $From -_to $To -_cc $cc -_subject $Subject -_body $Body -_smtp $SMTPServer -_port $SMTPPort;

$compressed_folder = Get-ChildItem -Path $zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
try
    {
        Remove-Item $compressed_folder.FullName -Recurse -ErrorAction Stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
catch
    {
        Append-log-file -_text "ERROR when deleting the item $compressed_folder" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $compressed_folder" + $_.Exception.Message 
    }


#End log
Append-log-file -_text "END LOG @$full_date"
Append-log-file -_text "---------------------------------------------------------------"

}

#SendMail Function
function Notify
{
    param($_from, $_to, $_cc, $_attachment, $_subject, $_body, $_smtp, $_port)
    try 
            {           
                Send-MailMessage -From $_From -to $_To -Cc $_Cc -Subject $_Subject `
                -Body $_Body -SmtpServer $_SMTP -port $_Port 
                Append-log-file -_text "The email notification has been sent successfully" -_LogFile $LogFile;
            }
        catch
            {
                write-host "An error occurred when sending the notification" + $_.Exception.Message 
                Append-log-file -_text "The email notification has not been sent" -_LogFile $LogFile;
            }
}


#Log file functions
function Create-log-file 
{
    param ([string]$_log_location)        
    if (!(Test-Path "$_log_location\$custom_date - FTP_Script.log"))
    {
        try 
            {           
                $_LogFile = "$_log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when creating the log file" + $_.Exception.Message 
            }
        
    }
    else
    {
        try 
            {
                $_LogFile = "$log_location\$custom_date - FTP_Script.log"
                Set-Variable -Name LogFile -Value $_LogFile -Scope 1
            }
        catch
            {
                write-host "An error occurred when reusing the log file" + $_.Exception.Message 
            }
    }
}

function Append-log-file 
{
    param ($_text)
    try 
        {
            $custom_date+" "+$_text | Out-File $LogFile -Append -Force
        }
    catch
        {
            write-host "An error occurred when writing into the log file" + $_.Exception.Message 
        }
}

#Folders & files functions
function Create-Directory
{
    param ($_path)
    try 
        {
            $_folder_to_compress = New-Item -ItemType directory -Path "$_path\$custom_date - $environment Database Export" -force -ErrorAction Stop
            Set-Variable -Name folder_to_compress -Value $_folder_to_compress -Scope 1
            Append-log-file -_text "OK New folder to compress created successfully" -_LogFile $LogFile;

        }
    catch
        {
            Append-log-file -_text "ERROR when creating new folder to compress" -_LogFile $LogFile;
            write-host "An error occurred when creating new folder to compress" + $_.Exception.Message 
        }
}

function Copy-backups-older-than
{
    param ($_source_path, $_destination_path, $_limit)
    try
        {
            Get-ChildItem -Path $_source_path -Recurse -Force | Where-Object {$_.extension -in ".dif",".bak" -and !$_.PSIsContainer -and $_.CreationTime -gt $_limit } | copy-item -Destination  $_destination_path -Force -Container -ErrorAction stop
            Append-log-file -_text "OK The copy of the backups made after $limit has been made successfully" -_LogFile $LogFile;
        }
    catch
        {
            Append-log-file -_text "ERROR when copying the backups made after $limit" -_LogFile $LogFile;
            write-host "An error occurred when copying the backups made after $limit" + $_.Exception.Message 
        }
}

function Compress_folder
{
    param ($_sz, $_pass, $_zipdirectory, $_folder_to_compress)
    try
    {
        & $sz a -p"$_pass" "$_zipdirectory\$custom_date - $environment Database Export.7z" "$_folder_to_compress\*"
        $_compressed_folder = Get-ChildItem -Path $_zipdirectory -Recurse -Force | Where-Object {$_.extension -match ".7z"} -ErrorAction stop
        Set-Variable -Name compressed_folder -Value $_compressed_folder -Scope 1
        Append-log-file -_text "OK Compression/encryption of the folder has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR during compression/encryption of the folder" -_LogFile $LogFile;
        write-host "An error occurred during compression/encryption of the folder" + $_.Exception.Message 
    }
}

function Delete_Folder
{
    param ($_file_to_delete)
    try
    {
        Remove-Item "$_file_to_delete" -Recurse -ErrorAction stop
        Append-log-file -_text "OK Deletion of the item has been made successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when deleting the item $_file_to_delete" -_LogFile $LogFile;
        write-host "An error occurred when deleting the item $_file_to_delete" + $_.Exception.Message 
    }
}

#FTP Function
function Send_FTP
{
    param ($_user, $_pass, $_ftp, $_file_to_send, $_subject, $_body)
    #Create Web client
    try
    {
        $webclient = New-Object System.Net.WebClient -ErrorAction stop
        $webclient.Credentials = New-Object System.Net.NetworkCredential($_user,$_pass) -ErrorAction Stop 
        $webclient.Proxy = $NULL 
        Append-log-file -_text "OK FTP WebClient created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP WebClient" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP WebClient" + $_.Exception.Message 
    }
    #Create URI
    try
    {
        $_uri = New-Object System.Uri($_ftp+$_file_to_send.Name) -ErrorAction stop 
        Append-log-file -_text "OK FTP URI created successfully" -_LogFile $LogFile;
    }
    catch
    {
        Append-log-file -_text "ERROR when creating the FTP URI" -_LogFile $LogFile;
        write-host "An error occurred when creating the FTP URI" + $_.Exception.Message 
    }
    #Send the file
    try
    {
        $webclient.UploadFile($_uri, $_file_to_send.FullName)
        Append-log-file -_text "OK FTP file sent successfully" -_LogFile $LogFile;
        $global:Subject = "FTP Upload Success"
        $global:Body = "The file $_file_to_send has been sent successfully @$full_date"
    }
    catch
    {
        Append-log-file -_text "ERROR when sending the file through FTP" -_LogFile $LogFile;
        write-host "An error occurred when sending the file through FTP" + $_.Exception.Message 
        $global:subject = "FTP Upload Failure"
        $global:Body = "An error occurred when sending the FTP backups from $environment to FTP @$full_date"
    }
}

main