[SQL Server] Contained databases : présentation et utilisation

Ce billet présente les contained databases, et quelques éléments d’utilisation.

Qu’est-ce qu’une contained database ?

Une contained database (ou base de données de relation contenant-contenu) est une fonctionnalité apparue à partir de SQL Server 2012 pour répondre – à un certain niveau – à quelques problématiques de portabilité de base de données. Traditionnellement, une migration d’une base de données SQL Server se fait via l’utilisation de la combinaison backup/restore. Toutefois, pour qu’une base migrée vers une nouvelle instance soit 100% opérationnelle, il lui faut rapatrier certains objets stockés dans son instance d’origine, comme les logins.

Cette problématique n’existe plus vraiment dans un contexte de contained database, où les informations d’authentification sont également contenues.

Il existe 3 types de contained databases :

  • NONE : il s’agit du type de bases de données par défaut. Tous les objets et métadonnées de la base de données sont gérés par l’instance SQL Server.
  • PARTIAL : il s’agit de contained databases partiellement isolée de l’instance SQL Server. En clair, certaines métadonnées et objets sont rattachés à l’instance, d’autres contenues dans la contained database. La DMV sys.dm_db_uncontained_entities permet d’avoir une idée des objets contenus ou non.
  • FULL : il s’agit de contained databases totalement isolée de l’instance SQL Server. Ce type de contained database n’est pas supporté, pour l’heure.

 

 

Création et utilisation d’une contained database

    Modus operandi de création d’une contained database

La création d’une contained
database est un exercice particulièrement simple :

  • Activation de la fonctionnalité Contained Database au niveau instance, que ce soit via T-SQL :
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'contained database authentication', 1
GO
RECONFIGURE WITH OVERRIDE
GO
Ou via l’interface SQL Server Management Studio (SSMS), en allant dans les propriétés de l’instance (au sein de l’explorateur d’objets), section Advanced :


  • Création de la contained database :
    • Via T-SQL :
CREATE DATABASE MaBase_CDB
CONTAINMENT = PARTIAL
ON PRIMARY
( NAME =N'MaBase_CDB',
  FILENAME= N'D:\SQL Server\Data\MaBase_CDB.mdf')
  LOG ON
( NAME =N'MaBase_CDB_log',
  FILENAME= N'D:\SQL Server\Data\MaBase_CDB_log.ldf')
GO
    • Via SSMS :

puis, après création de la base de données (section General) :

Simple comme bonjour…

Pour avoir la liste des contained databases, il suffit d’exécuter la requête suivante :

SELECT name
      ,containment
FROM
sys.databases
GO

 

Ce qui donnerait cet exemple de résultat (dans notre contexte) :


La colonne containment permet logiquement d’identifier si une base est de type contained (valeur à 1) ou non (0, sinon). Il est possible de modifier la contained database à un état NONE (c’est-à-dire, non-isolé de l’instance, pour rappel) en exécutant, par exemple, la requête suivante :

USE master
GO
ALTER DATABASE MaBase_CDB
SET CONTAINMENT=NONE
WITH NO_WAIT
GO

 

L’exécution de la requête de vérification donnerait le résultat suivant :


Le changement d’état est également réalisable via SSMS en allant, bien sûr, dans les propriétés de la base de données, section Options :

 

Création d’un « contained user »

La création d’un utilisateur au sein d’une contained database est simple :

USE MaBase_CDB
GO
CREATE USER MonUser_CDB
WITH PASSWORD=N'Blabla!'
GO

 

La difference notable par rapport à la creation d’un login ou d’un utilisateur traditionnel est que:

  • Dans un contexte de base de données traditionnelle, il faut d’abord créer un login avant de créer un utilisateur à assigner.
  • Là où, dans un contexte de base de données traditionnelle, le mot-de-passe est spécifié au niveau d’un login, il l’est au niveau utilisateur dans un contexte de contained database.

En cas de création d’un utilisateur avec mot-de-passe, l’erreur suivante sera levée :

L’interface SSMS permet, bien sûr, de créer un utilisateur de contained database (via l’explorateur d’objets, en allant dans la section Security de la contained database, puis en faisant un clic-droit sur Users, sélection du sous-menu New User…) :

puis :

Le type d’utilisateur à sélectionner est celui avec mot-de-passe.

Il est tout à fait possible de créer un utilisateur de type Windows au sein d’une contained database :

Ou en T-SQL :

USE MaBase_CDB
GO
CREATE USER [MAC-ROG\Mac]
GO

 

Authentification à une contained database

Pour connecter un contained
user à une contained database, le nom de la base de données doit être indiquée dans la chaîne de connexion.

  • En mode SQL :

Au sein de la boîte de dialogue de connexion SSMS, il faut entrer les informations de connexion :


Puis spécifier la contained database cible comme base de données par défaut, dans les propriétés des options avancées de connexion (bouton Options >>, de la figure ci-dessus) :


  • En mode Windows :

Même approche, mais en optant pour le mode d’authentification Windows NT :


Sitôt la connexion établie, l’utilisateur MonUser_CDB ne pourra interagir qu’avec la contained database et ses objets.

 

Migration d’un utilisateur

Lors de la conversion d’une base de données en contained database, les utilisateurs qui y sont associés ne sont pas automatiquement migrés vers la nouvelle base de données.

Supposons que la base de données MaBase_CDB soit une base de données traditionnelle, et qu’un utilisateur MonUser_CDB y a déjà été créé (ainsi que son login associé) :

USE master
GO
CREATE LOGIN MonUser_CDB
	WITH PASSWORD=N'Blabla!',
	DEFAULT_DATABASE=master
GO
USE MaBase_CDB -- la base est paramétrée à NONE
GO
CREATE USER MonUser_CDB
	FOR LOGIN MonUser_CDB
GO

 

Et supposons que ladite base est convertie en contained database :

USE master
GO
ALTER DATABASE MaBase_CDB
	SET CONTAINMENT = PARTIAL
	WITH NO_WAIT
GO

 

La requête ci-dessous va permettre d’afficher la liste des utilisateurs de la base :

USE MaBase_CDB
GO
SELECT name
	  ,type_desc
	  ,authentication_type
	  ,authentication_type_desc
FROM sys.database_principals
GO

 

Dont voici un extrait:

On peut, ici, noter que l’utilisateur MonUser_CDB ne peut se connecter à la base que via son login associé au niveau de l’instance.

Si l’on procède à la conversion de MaBase_CDB en contained database…

USE master
GO
ALTER DATABASE MaBase_CDB
	SET CONTAINMENT = PARTIAL
	WITH NO_WAIT
GO

 

… et qu’on relance la requête qui liste les utilisateurs associés, on remarque que les informations restent les mêmes, c’est-à-dire inchangées (type d’authentification restée à INSTANCE pour MonUser_CDB). Cela sous-entend que malgré la conversion de la base, il n’y a pas eu migration automatique de l’utilisateur MonUser_CDB.

Pour migrer ledit utilisateur, il suffit d’exécuter la requête suivante :

USE MaBase_CDB
GO
EXECUTE sp_migrate_user_to_contained
@username =N'MonUser_CDB',
@rename =N'keep_name',
@disablelogin =N'disable_login'
GO

Une petite vérification donnerait :


On voit, ci-dessus, que MonUser_CDB a été migré avec succès, puisque son type d’authentification à la base est désormais DATABASE, soit la contained database.

 

Avantages et inconvénients de l’utilisation d’une contained database

Toute contained database possède des avantages et des inconvénients. En voici une liste non-exhaustive :

  • Avantages principaux :
    • Moins de dépendances à une instance spécifique : les objets et fonctionnalités contenues au sein de ce type de base de données peuvent être gérées d’eux-mêmes, avec pour effet de réduire la surutilisation de bases systèmes ou de l’instance.
    • L’authentification d’utilisateurs peut être effectuée directement au niveau d’une contained database. Qu’il s’agisse de connexions en mode Windows ou SQL Server.
    • Facilité de les migrer rapidement, d’un serveur à l’autre. Par exemple, les erreurs relatives aux utilisateurs orphelins n’existent plus dans un contexte de contained database.
    • Flexibilité au niveau de la gestion des paramètres de bases de données, plutôt qu’au niveau de la base master. De cette façon, il y a possibilité d’avoir plus de contrôles sur la contained database sans bénéficier du rôle sysadmin.
    • Très utile dans un contexte de basculement AlwaysOn.
  • Inconvénients principaux :
    • Les contained databases ne peuvent pas supporter certaines fonctionnalités comme CDC (Change Data Capture), la réplication, le change traking.
    • Quelques objets ne sont pas pris en compte : procédures stockées numérotées, objets liés par schéma qui dépendent de fonction intégrées avec modifications de collation.

 

Pour aller plus loin…

Vous pouvez garder un œil ici, pour tout sujet relatif aux contained databases.

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s