SQL Server 2012 : Parfois, une relation contenant-contenu partielle est préférable
SQL Server 2012 prendra en charge les bases de données à relation contenant-contenu partielle, ce qui devrait résoudre certains problèmes de migration et de consolidation.
Denny Cherry
Les bases de données à relation contenant-contenu partielle résolvent certains des problèmes principaux de consolidation et de migration auxquels doivent faire face les bases de données SQL Server depuis la version 4.2. Il s'agit heureusement de l'une des nombreuses nouvelles fonctionnalités introduites dans SQL Server 2012.
Tout d'abord, les bases de données à relation contenant-contenu résolvent le problème de déplacement d'une base de données d'une instance de SQL Server vers une autre, sans devoir identifier les comptes de connexion SQL Server utilisés par la base de données. Cela s'applique également à la création de ces connexions sur l'instance de base de données cible.
Pour cela, SQL Server 2012 utilise une connexion contenue à SQL Server. Il s'agit d'un utilisateur SQL Server créé avec la base de données à relation contenant-contenu, mais sans compte de connexion SQL Server correspondant. Cet utilisateur contenu au sein de la base de données à relation contenant-contenu a un mot de passe stocké dans la base de données à relation contenant-contenu, c'est pourquoi l'authentification fonctionne comme elle le devrait.
Le deuxième problème résolu est le suivant : les conflits de classements entre les tables des bases de données à relation contenant-contenu et les tables temporaires disparaissent. SQL Server crée automatiquement des tables temporaires dans le cadre de la base de données à relation contenant-contenu lors du classement. Cela permet à la même instance de SQL Server d'héberger des bases de données avec des classements différents sans devoir modifier les instructions CREATE TABLE pour spécifier le classement ou sans devoir indiquer l'instruction COLLATE dans la syntaxe de l'instruction JOIN.
Contrairement à certaines des nouvelles fonctionnalités pour lesquelles le niveau de compatibilité de la base de données doit être défini sur la dernière valeur, les bases de données à relation contenant-contenu partielle sont actuellement prises en charge jusqu'à la version SQL Server 2005. Notez cependant que la fonctionnalité de base de données à relation contenant-contenu n'est pas encore terminée. Elle devrait l'être plus ou moins entre la version SQL Server 2012 CTP3 et le lancement du produit en phase de production, vers le début ou la mi-2012.
Configurer une base de données à relation contenant-contenu partielle
Avant de pouvoir transformer une base de données sans relation contenant-contenu en base de données à relation contenant-contenu partielle, vous devez commencer par modifier un paramètre du serveur à l'aide de la procédure stockée système sp_configure. Cette procédure permet de remplacer la valeur 0 du paramètre « contained database authentication » par 1. Utilisez ensuite l'instruction RECONFIGURE pour activer le nouveau paramètre, de la façon suivante :
EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO
Après avoir activé le paramètre « contained database authentication », vous pouvez transformer une base de données spécifique en base de données à relation contenant-contenu partielle. Vous pouvez utiliser SQL Server Management Studio, comme illustré à la figure 1, utiliser l'instruction ALTER DATABASE ou bien créer une nouvelle base de données à relation contenant-contenu :
ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO
Pour créer une base de données à relation contenant-contenu partielle ou modifier une base de données de façon à ce qu'elle en devienne une, connectez-vous à l'instance de SQL Server dans l'Explorateur d'objets. Cliquez avec le bouton droit de la souris pour créer une base de données ou sélectionnez une base de données existante. Sélectionnez les propriétés (en fonction de l'opération effectuée, à savoir la création d'une base de données ou la modification d'une base de données existante). Dans les deux cas, sélectionnez l'onglet « Options » et choisissez « Partielle » au lieu de « Aucune » dans la liste déroulante « Type de relation contenant-contenu ».
.jpg)
Figure 1 La fenêtre Propriétés de la base de données vous permet de modifier le paramètre de relation contenant-contenu pour la base de données.
Pour transformer une base de données existante sans relation contenant-contenu en base de données à relation contenant-contenu partielle, utilisez T/SQL et l'instruction ALTER DATABASE. Pour que vous puissiez modifier correctement le paramètre de relation contenant-contenu d'une base de données, l'instruction ALTER DATABASE doit être capable de verrouiller exclusivement toute la base de données. Vous devez donc planifier une brève indisponibilité avec la division qui utilise cette base de données.
Éviter les erreurs de classement de tables temporaires
Les nouvelles bases de données à relation contenant-contenu partielle permettent la présence de plusieurs classements différents sur la même instance de SQL Server. Vous n'avez plus besoin de vous préoccuper des problèmes de classement lors de la jointure de tables temporaires. Vous pouvez tester cela en prenant une instance de Microsoft SQL Server et en créant une base de données à relation contenant-contenu partielle pour un classement différent. Créez ensuite une table physique et une table temporaire dans la base de données à relation contenant-contenu partielle. Chargez les données dans deux tables et tentez de les joindre.
Créez une base de données à l'aide du classement Albanian_100_CI_AI_KS_WS, alors que l'instance utilise le classement SQL_Latin1_General_CP1_CI_AS, comme illustré à la figure 2. Cela crée une table nommée dbo.Employee dans les nouvelles bases de données et charge trois lignes dans la table. Cela crée également une table temporaire nommée #emp et insère une seule ligne dans la table. Lorsque les tables sont jointes à la fin de la requête, une ligne est retournée. Si le paramètre de base de données à relation contenant-contenu n'était pas défini sur Partielle, une erreur serait retournée à la place.
Figure 2 Créer et utiliser une base de données à relation contenant-contenu partielle.
use master GO CREATE DATABASE [Cont] CONTAINMENT = PARTIAL ONPRIMARY (NAME = N'Cont', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont.mdf', SIZE = 4096KB, FILEGROWTH= 1024KB) LOGON (NAME = N'Cont_log',FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\Cont_log.ldf', SIZE = 1024KB, FILEGROWTH = 10%) COLLATE Albanian_100_CI_AI_KS_WS GO Use Cont GO Create table Employee (EmployeeId INTPRIMARYKEY, LastName nvarchar(100), FirstName nvarchar(100)) GO Insert into Employee (EmployeeId,LastName,FirstName) values (1,'last1','first1'), (2,'last2','first2'),(3,'last3','first3') GO Create table #emp (LastName nvarchar(100)) GO Insert into #emp (LastName) values ('last1') GO select* from Employee join #emp on Employee.LastName = #emp.LastName
Utilisateurs contenus
Les utilisateurs contenus sont similaires aux comptes de connexion SQL Server traditionnels, mais le compte de connexion n'est pas aligné sur l'utilisateur de la base de données à relation contenant-contenu. Créez un utilisateur contenu avec SQL Server Management Studio ou l'instruction CREATE USER T/SQL.
Pour utiliser SQL Server Management Studio, connectez-vous à l'instance qui héberge la base de données à relation contenant-contenu dans l'Explorateur d'objets. Accédez à Bases de données | {Votre base de données à relation contenant-contenu} | Sécurité | Utilisateurs. Cliquez avec le bouton droit de la souris sur le dossier Utilisateurs et sélectionnez « Nouvel utilisateur » dans le menu contextuel. Dans la liste déroulante « Type d'utilisateur », sélectionnez « Utilisateur SQL avec mot de passe ». Remplissez les champs de nom d'utilisateur et de mot de passe (voir la figure 3), ainsi que le schéma par défaut et toute appartenance au rôle de base de données que vous devez configurer.
.jpg)
Figure 3 Créer un utilisateur SQL contenu avec SQL Server Management Studio.
Après avoir créé l'utilisateur contenu, vous pouvez examiner l'affichage catalogue sys.database_principals dans la base de données à relation contenant-contenu. Le jeu d'enregistrements retourné en interrogeant l'affichage catalogue montre la nouvelle colonne nommée authentication_type définie sur la valeur 2. Il montre également la colonne authentication_type_desc définie sur une valeur « DATABASE » lorsqu'il s'agit d'un utilisateur contenu.
Créez une base de données à relation contenant-contenu à l'aide de T/SQL avec l'instruction CREATE USER. SQL Server sait que vous êtes en train de créer un utilisateur contenu au lieu d'un utilisateur aligné sur un compte de connexion au niveau de l'instance si vous spécifiez l'instruction WITH PASSWORD, comme illustré ici :
CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO
Vous pouvez créer des utilisateurs SQL contenus, ainsi que des comptes de connexion Windows contenus. Pour cela, remplacez la liste déroulante « Type d'utilisateur » (voir la figure 4) par « Utilisateur Windows » et entrez le nom de l'utilisateur sans sélectionner de compte de connexion.
.jpg)
Figure 4 Créer un compte de connexion Windows contenu avec SQL Server Management Studio.
Vous pouvez également créer des utilisateurs Windows contenus avec l'instruction CREATE USER T/SQL, comme illustré ici :
CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO
Connectez-vous à la base de données à relation contenant-contenu avec un compte de connexion contenu, comme un utilisateur traditionnel (non contenu). L'astuce ici est de spécifier le nom de la base de données dans la chaîne de connexion lors de la connexion. Si vous ne le faites pas, SQL Server suppose qu'un compte de connexion SQL Server traditionnel tente de se connecter. La connexion échoue si aucun compte de connexion ne correspond au nom d'utilisateur et au mot de passe définis au niveau de l'instance.
Effectuez cette opération dans la boîte de dialogue de connexion SQL Server Management Studio, en cliquant sur le bouton Options situé en bas de la fenêtre de connexion. Sélectionnez l'onglet « Propriétés de connexion ». Dans cet onglet, le nom de la base de données à relation contenant-contenu peut être entré dans la liste déroulante « Se connecter à la base de données » (voir la figure 5).
Vous ne pourrez pas interroger la liste de bases de données hébergées sur l'instance de SQL Server en tant qu'utilisateur contenu spécifié dans l'onglet « Connexion ». Cet utilisateur contenu ne dispose pas de droits d'accès à la base de données master, sauf s'il s'authentifie d'abord auprès de la base de données à relation contenant-contenu qui détient son nom d'utilisateur et son mot de passe. C'est la raison pour laquelle vous devez connaître le nom de la base de données et l'entrer manuellement dans la zone.
.jpg)
Figure 5 L'onglet « Propriétés de la connexion » de la fenêtre de dialogue de connexion SQL Server Management Studio.
Comme l'illustrent ces deux fonctionnalités, la consolidation des instances de bases de données dans une instance de SQL Server 2012 sera nettement plus facile. Cela prendra un peu de temps et vous devrez effectuer des tests afin de vous assurer que votre application de base de données est totalement compatible avec la fonctionnalité de base de données à relation contenant-contenu. Toutefois, une fois que vous aurez activé le paramètre de base de données à relation contenant-contenu, les futures migrations et consolidations de bases de données seront nettement plus simples, et c'est là le but recherché.
.jpg)
Denny Cherry, MVP, est consultant indépendant. Il bénéficie d'une expérience de plus de dix ans avec les solutions Microsoft SQL Server, Hyper-V, vSphere et Enterprise Storage. Il détient plusieurs certifications Microsoft liées aux versions 2000 à 2008 de SQL Server, dont le Microsoft Certified Master. Il a écrit plusieurs ouvrages et des dizaines d'articles techniques sur la gestion SQL Server.