PI Services

Le blog des collaborateurs de PI Services

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

SQL Server – Chiffrer les connexions Client/Serveur

Introduction

Afin d’améliorer la sécurité des connexions réalisées depuis les clients SQL vers une base, il est possible d’activer la fonction de chiffrement de SQL (Encryption) depuis SQL Server Configuration Manager.

Cette fonctionnalité permet de chiffrer la connexion entre le client et le serveur afin d’éviter l’interception et la lecture des commandes réalisées par le client.

Prérequis

  • Un certificat avec sa clé privée délivré pour l’authentification server d’installé sur le serveur SQL (le certificat doit avoir pour nom le même que celui utilisé dans la source de donnée du client, souvent il s’agit du FQDN du serveur),
  • Dans le cas d’une utilisation d’un cluster, le nom du certificat doit être correspondre au nom DNS utilisé par le cluster, le certificat devras être installé sur tous les nœuds du cluster,
  • Le compte de service utilisé par le service SQL Server doit avoir les droits Read sur la clé privée du certificat,
  • Le client doit être en mesure de valider et de faire confiance à l’autorité ayant délivré le certificat,
  • Une fenêtre d’interruption de l’instance SQL.

Réalisation

1. Depuis SQL Server Configuration Manager, faire un clic-droit sur l’instance souhaitée et sélectionner “Properties”

image

2. Depuis l’onglet certificate, sélectionner le certificat à utiliser pour le chiffrement des connexions,

image

3. Depuis l’onglet Flags, passer la valeur de “ForceEncryption” à ‘'Yes”,

image

4. Rebooter l’instance.

Verification

Un test réalisé à l’aide d’un outil de capture réseau, et d’un client SQL permet de prouver le chiffrement des connexions :

  • Avant

image

  • Après

image

SQL Server – Configuration de la base TempDB (Partie 1/2)

Introduction

Ce post est composé de deux parties :

  • Recommandations sur l’emplacement et la taille de TempDB (Partie 1)
  • Répartition de la base TempDB sur plusieurs fichiers (Partie 2)

Présentation de la base TempDB :

La base TempDB est une base temporaire régénérée à chaque démarrage du serveur SQL, cette base est accessible à tous les utilisateurs (il est possible de retirer l’accès à cette base aux utilisateurs, mais cela n’est pas conseillé car la base TempDB est utilisé dans des opérations de routines).

Cette base, de par sa nature temporaire, ne permet pas les actions d’administrations suivantes : Sauvegarde/Restauration, Changement du “Recovery Model”.

Dans cette première partie nous verrons comment déplacer la base TempDB vers un stockage dédié.

Recommandations sur l’emplacement et la taille de TempDB

Recommandations Générales relatives à l’emplacement des fichiers de bases de données

De manière générale, il est recommandé de séparer les fichiers Data, Logs et temp. L’objectif de cette séparation est de permettre une meilleure séparation de la charge à travers différents disques physiques.

Actuellement, avec l’augmentation des solutions de stockage virtuel, la séparation des fichiers n’est parfois plus nécessaire pour des raisons de performances, néanmoins, séparer les fichiers à travers différents disques logiques permet plus de souplesse dans l’administration.

Dans le cas d’une infrastructure SQL utilisant un stockage partagé, le déplacement de la base TempDB vers un stockage local peut apporter les bénéfices suivants :

  • Meilleure performances notamment lors de l’utilisation de disques rapides (SSD),
  • Certaines bases TempDB peuvent être très sollicitées entrainant une baisse de performance générale sur stockage partagé.

Certains administrateurs peuvent être tentés de déplacer la base TempDB vers un stockage local, attention toutefois à bien valider les points suivants pour une configuration cluster :

  • Utiliser une base TempDB locale dans une configuration Cluster n’est supporté que depuis SQL Server 2012 (les versions précédentes requièrent que toutes les bases soient présentes sur le stockage partagé),
  • Utiliser le même emplacement pour TempDB à travers tous les nœuds du cluster, le compte de service SQL Server doit avoir un droit Lecture/Ecriture sur chacun des dossiers.

Recommandations Générales relatives à la taille des fichiers de bases de données

Lors de la création d’une nouvelle base de données, il est possible de préciser les éléments suivants relatifs à la taille de la base de données :

  • Taille initiale de la base de données,

La taille initiale de la base de donnée doit correspondre à la taille que la base va occuper à terme, ce afin d’éviter des actions d’expansion de la base (Autogrowth) qui en plus d’être lourdes entraînent une fragmentation des fichiers ainsi qu’une indisponibilité de ceux-ci durant l’expansion.

  • Taille maximum de la base de données,

A moins de parfaitement maitriser la taille maximum que la base de données doit avoir, il est préférable de conserver à “Unlimited” la taille maximum d’une base.

  • Autogrowth de la base de données

La fonction Autogrowth permet d’automatiquement augmenter la taille des fichiers de la base de données, ce qui permet une administration plus simple. L’opération d’autogrowth est généralement lourde (à moins d’utiliser l’IFI, Instant File Initialization) et comprends plusieurs contraintes.

Il est donc préférable d’éviter qui doit être considéré comme une solution de fortune. L’idéal en l’absence d’informations précises concernant la taille de la base est d’analyser l’évolution de la base en paramétrant un autogrowth fixe en MB, puis après une période d’analyse suffisante, de paramétrer la taille des fichiers utilisés par la base à une valeur légèrement supérieure à celle constatée (cette préconisation s’applique d’autant plus à la base TempDB, celle-ci étant recréée à chaque démarrage de l’instance SQL).

Afin d’estimer la taille à positionner pour l’Autogrowth, il est possible de visualiser la fréquence d’autogrowth d’une base depuis le rapport Disk de celle-ci :

clip_image001

Réalisation

Afin de déplacer la base TempDB, voici les actions à réaliser :

1. Récupérer l’emplacement actuel de la base

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

clip_image002[5]

2. Déplacer les fichiers utilisés par TempDB (attention à veiller à ce que le compte du service SQL Server y ait un droit de Lecture/Ecriture)

USE master;
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'T:\MSSQL\DATA\tempdb.mdf');
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'T:\MSSQL\DATA\templog.ldf');
GO

3. Redémarrer le service SQL Server

Verification

Vérifier le nouvel emplacement des fichiers TempDB :

Use master
GO
SELECT
name AS [LogicalName]
,physical_name AS [Location]
,state_desc AS [Status]
FROM sys.master_files
WHERE database_id = DB_ID(N'tempdb');
GO

SQL Server – Configuration de la base TempDB (Partie 2/2)

Introduction

Ce post est composé de deux parties :

  • Recommandations sur l’emplacement et la taille de TempDB (Partie 1)
  • Répartition de la base TempDB sur plusieurs fichiers (Partie 2)

Dans cette seconde partie nous verrons comment repartir la base TempDB à travers plusieurs fichiers comme Microsoft le recommande.

Recommendation

Bien que Microsoft recommande de répartie la base TempDB à travers autant de fichiers que de processeurs logiques présents sur le serveur et ce jusqu’à 8 fichiers, dans le cas d’un serveur avec plus de 8 CPU, si malgré l’utilisation de 8 fichiers la base TempDB souffre toujours de lenteurs, Microsoft préconise d’augmenter le nombre de fichiers par un multiple de 4.

Différents membres de la communauté SQL s’accordent à penser qu’au-delà de 8 fichiers, le gain en performance seras négligeable par rapport à l’effort d’administration.

Réalisation

1. Récupérer le nombre de CPU logique du serveur SQL

clip_image001

2. Ajouter trois fichiers TempDB sur trois disques différents (attention à veiller à ce que le compte du service SQL server y ait un droit de Lecture/Ecriture), il est préférable d’avoir des options de tailles et d’Autogrowth identiques

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev2, FILENAME = 'W:\tempdb2.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev3, FILENAME = 'X:\tempdb3.mdf', SIZE = 1024);

ALTER DATABASE tempdb

ADD FILE (NAME = tempdev4, FILENAME = 'Y:\tempdb4.mdf', SIZE = 1024);

GO

SQL Server 2016 : Configuration tempdb à l’Installation

Avec SQL Server 2016 nous aurions la possibilité de configurer la base de données tempdb dans le cadre du processus d'installation.

Un nouvel onglet a été ajouté à la rubrique de configuration du serveur de base de donnés sous le nom de tempdb et il nous permettra de préciser :

  1. Le nombre de fichiers de données de la base de données tempdb
  2. La taille initiale de chaque fichier et le taux de croissance en Mb
  3. La possibilité d'ajouter plusieurs emplacements pour le stockage des fichiers tempdb
  4. L'emplacement et la taille du fichier log de la base de données tempdb

SQL Server – Mettre en place une réplication asynchrone via le Log Shipping

Contexte

Le log shipping est l’une des technologies proposée par SQL Server pour mettre en place une haute-disponibilité d’une ou plusieurs bases de données à moindre prix.

Le fonctionnement du log shipping peux être résumé en trois étapes “backup-copy-restore”.

Les logs de transactions des bases de donnée à répliquer sont sauvegardées depuis le serveur primaire puis copiés vers le (ou les) serveur secondaire où ils y seront restaurés sur les bases secondaires correspondantes.

Plus d’informations sur le fonctionnement du log shipping sont disponibles ici.

Prérequis

Les prérequis à l’installation sont les suivants :

Prérequis Applicatifs :

  • Au moins deux serveurs avec SQL Server 2005 (Standard, Entreprise ou Workgroup) ou ultérieur,
  • Un partage de fichier pour la copie des logs de transactions,
  • Le service SQL Server Agent,
  • Il est fortement conseillé d’avoir des instances SQL partenaires configurés à l’identique.

Permissions nécessaires :

  • Disposer des droits sysadmin sur l’instance primaire et la secondaire,

Configuration du log shipping

Les actions décrites sont effectuées depuis le serveur primaire.

1. Vérifier que la base de donnée à répliquer est en mode de recovery “full” ou “bulk-logged” à l’aide de la requête suivante :

SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'MyPrimaryDB'

 

2. Depuis les propriétés de la base à répliquer, depuis l’onglet “Transaction Log Shipping” (1) cochez la case “Enable this as a primary database in a log shipping configuration” (2) puis cliquer sur “Backup Settings” (3) :

image

3. Depuis la fenêtre Backup Settings, indiquer le chemin réseau où le serveur primaire écriras les backups des logs de transactions des bases de données à répliquer (1) et éventuellement le chemin local (2) si un chemin local est utilisé. Noter que dans les deux cas le compte utilisé par le service SQL Server Agent du serveur secondaire doit avoir un accès en lecture à ce dossier, et le service SQL Server du serveur primaire doit avoir un accès en read/write.

Indiquer le nom du job de backup du serveur primaire (3) de manière à facilement le reconnaitre, éventuellement modifier le schedule proposé (4) par un schedule correspondant mieux à vos contraintes spécifiques puis cliquer sur OK.

image

4. Depuis la fenêtre Secondary Database Settings, se connecter au serveur secondaire (1), choisir une base de donnée existante ou indiquer le nom d’une nouvelle base (2) qui seras utilisée/créée sur le serveur secondaire, puis choisir le mode pour initialiser la base de donnée secondaire.

Ayant déjà créé initialisé la base distante, j’ai choisi l’option “No, the secondary database is initialized” (3)

image

5. Depuis l’onglet “Copy Files” (1), indiquer le chemin où les sauvegardes des logs de transactions seront copiés (2), indiquer la rétention des fichiers copiés (3) en faisant attention à ce que les fichiers ne soient pas supprimés avant leur restauration sur le serveur secondaire, personnaliser le nom du job de copie (4) et modifier son schedule si nécessaire (5)

image

6. Depuis l’onglet “Restore Transaction Logs” (1), choisir “Standby Mode” et cocher la case “Dissconnet users in the database when restoring backups” (2) celà permet d’accéder à la base de données secondaire en Read-Only contrairement au mode “No-Recovery” qui met la base en état de restauration, personnaliser le nom du job de restauration (3) et en modifier le schedule si nécessaire (4)

image

Vérification

Il convient suite à la mise en place du log shipping de vérifier son fonctionnement en mode manuel (c-à-d en exécutant manuellement les jobs de Backup-Copy-Restore) et automatiquement selon les schedule définis pour chaque jobs.

Il est possible de mettre en place des alertes par email pour informer le DBA de la bonne exécution des jobs de réplication depuis l’onglet Transaction log shipping des propriétés de la base répliquée.

SQL Server – Amélioration de la reconstruction d’un index en ligne sous SQL Server 2012.

SQL Server 2012 cache quelques petites nouveautés qui peuvent bien vous surprendre.

J’ai découvert récemment qu’il est désormais possible de reconstruction un index en ligne sur SQL Server 2012 de tables qui contiennent des types VARCHAR(MAX) et NVARCHAR(MAX).

Démonstration :

Commencez par exécuter le script sous SQL Server 2008 R2 puis sur SQL Server 2012.

USE [tempdb]
GO
CREATE TABLE T1
(ID INT, C1 NVARCHAR(10), C2 NVARCHAR(MAX))
GO
CREATE CLUSTERED INDEX [IX_T1]
ON T1
(ID)
GO
CREATE NONCLUSTERED INDEX [IX_T1_Cols]
ON T1
(C1)
INCLUDE (C2)
GO
USE [tempdb]
GO
ALTER INDEX [IX_T1_Cols] ON [dbo].[T1]
REBUILD WITH (ONLINE = ON)
GO
DROP TABLE T1
GO

Résultat :

Sur le serveur SQL Server 2008 R2, on a un message d’erreur du type

Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index ‘IX_T1_Cols’ because the index contains column ‘C2’ of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

Tandis que sur le serveur SQL Server 2012

Command(s) completed successfully.

SQL Server 2005 – Erreur 28086 lors de la réinstallation de SQL Server Reporting.

Lors d’une mission d’installation de SQL Server chez un client, j’ai rencontré un problème d’installation du service SQL Server Reporting suite à une réinstallation du serveur.

L’erreur en question mentionne que le service SQL Server Reporting ne peut pas etre installé car il existe une instance du même nom sur le serveur.

An instance with the same name is already installed on this computer. To proceed with SQL Server Setup, provide a unique instance name.

error sql article

Pour résoudre ce problème :

1. Commencez par désinstaller tous les binaires de SQL Server 2005

2. Supprimez le dossier correspondant à C:\Program Files\Microsoft SQL Server\, ou spécifiquement le dossier C:\Program Files\Microsoft SQL Server\MSSQL.1.

3. Puis ouvrez l’éditeur du registre recherchez les clés suivantes pour les supprimer :

HKEY_LOCAL_MACHINE\System\CurrentControlSet\Services\MSSQLServer

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\MSSQL.1

HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Services\SQL Server

4. Puis redémarrer votre serveur et relancez votre installation.

SQL Server – Comparaison de base de données avec SSDT 2012.

Dans le cadre de l’administration de serveurs de base de données, vous serez probablement un jour amené à comparer des bases de données entre elles.

La procédure ci-dessous vous aide à comparer deux bases de données entre à l’aide de SQL Server Data Tools 2012.

1. Commencez par télécharger le SQL Server Data Tools de Visual Studio 2012 en suivant ce lien :

http://msdn.microsoft.com/en-us/jj650015

Cliquez ensuite sur le lien Download SQL Server Data Tools for Visual Studio 2012

2. Une fois que votre outil est téléchargé, installez le puis lancez le SQL Server Data Tools.

L’icône ressemble à cette image :

clip_image001

3. Dans le menu SQL, sélectionnez Comparaison de schémas puis cliquez sur Nouvelle comparaison de schémas…

clip_image002

4. Sélectionnez dans un premier temps la source en cliquant sur Sélectionner source…

clip_image003

5. Cliquez sur Nouvelle connexion… pour choisir la base de données d’un serveur que vous désirez mettre en source.

clip_image004

6. Choisissez le nom du serveur puis votre base de données hébergée sur ce dernier.

a. Dans l’exemple ci-dessous notre serveur se nomme JELAV-HP-01\SQL2012 et notre base de données source se prénomme AdventureWorks2012

b. Cliquez ensuite sur OK

clip_image005

7. Cliquez sur OK.

clip_image006

8. Sélectionnez maintenant votre base de données cible en cliquant sur Sélectionner cible…

clip_image007

9. Une fenêtre de sélection pour le schéma cible apparait. Cliquez sur Nouvelle connexion… pour ajouter la base de données cible que vous souhaitez.

clip_image008

10. Choisissez le nom du serveur puis votre base de données hébergée sur ce dernier.

a. Dans l’exemple ci-dessous notre serveur se nomme JELAV-HP-01\SQL2008R2 et notre base de données cible se prénomme AdventureWorks2008R2

b. Cliquez ensuite sur OK

clip_image009

11. Cliquez à nouveau sur OK.

clip_image010

12. Vos deux champs Source et Cible sont renseignés, vous pouvez maintenant comparer le schéma des deux bases de données en cliquant sur l’icône Comparer.

clip_image011

13. Une fois que la comparaison des schémas est terminée, dans notre exemple nous observons des objets qui comportent des points de divergence. Nous avons la possibilité de mettre à jour le schéma cible pour que le schéma de la base de données AdventureWorks2008R2 soit identique de celle de AdventureWorks2012.

clip_image012

14. Pour mettre à jour, le schéma de la base de données cible, cliquez sur l’icône Mettre à jour.

clip_image013

15. Une fenêtre apparait pour vous avertir et vous demander confirmation, cliquez sur Oui.

clip_image014

16. Une fois que la mise à jour est achevée, vous pouvez voir le détail des opérations dans le menu Opérations des outils de données et le succès de celui-ci.

clip_image015

17. Relancez la comparaison des deux schémas pour valider la mise à jour. Résultat de la comparaison, le schéma des deux bases de données sont identique.

clip_image016