SQL Server 2012: Manchmal sind partielle Datenbanken die bessere Wahl

SQL Server 2012 unterstützt partiell eigenständige Datenbanken, wodurch manche Migrations- und Konsolidierungsprobleme gemindert werden sollten.

Denny Cherry

Partiell eigenständige Datenbanken lösen einige wichtige Konsolidierungs- und Migrierungsprobleme, mit denen SQL Server-Datenbanken seit SQL Server Version 4.2 zu kämpfen haben. Dankenswerterweise wird dies eine der neuen Features in SQL Server 2012 sein.

Das erste Problem, das partiell eigenständige Datenbanken lösen, ist die Verschiebung einer Datenbank von einer Instanz von SQL Server zu einer anderen, ohne dass dabei die SQL Server-Anmeldedaten, die die Datenbank verwendet, identifiziert werden müssen. Dazu gehört auch die Erstellung dieser Anmeldedaten für die Ziel-Datenbankinstanz.

SQL Server 2012 bewerkstelligt dies mithilfe eigenständiger SQL Server-Logins. Dies ist ein SQL Server-Benutzer, der innerhalb der eigenständigen Datenbank erstellt wurde, jedoch ohne ein zugehöriges SQL Server-Login. Dieser eigenständige Benutzer innerhalb der eigenständigen Datenbank verfügt über ein Kennwort, das in der eigenständigen Datenbank gespeichert ist, die Authentifizierung funktioniert also wie erwartet.

Das zweite Problem, das gelöst wird, ist, dass Sortierungskonflikte zwischen Tabellen innerhalb eigenständiger Datenbanken und temporären Tabellen verschwinden. SQL Server erstellt bei der Sortierung automatisch temporäre Tabellen im Bereich der eigenständigen Datenbank. Dadurch kann eine Instanz von SQL Server Datenbanken verschiedene Sortierungen enthalten, ohne dass die CREATE TABLE-Anweisungen zur Angabe der Sortierung modifiziert werden müssen oder dass die COLLATE-Anweisung innerhalb der Syntax der JOIN-Anweisung angegeben werden muss.

Anders als einige der neuen Features, bei denen die Datenbankkompatibilitätsebene auf den neuesten Wert gesetzt werden muss, werden partiell eigenständige Datenbanken bis zurück zu SQL Server 2005 unterstützt. Die "Eigenständige Datenbank"-Funktion ist andererseits noch nicht vollständig. Dies sollte zwischen der Version SQL Server 2012 CTP3 und der Fertigungsfreigabe des Produkts bis Mitte 2012 erfolgt sein.

Einrichten einer partiell eigenständigen Datenbank

Bevor Sie eine nicht eigenständige Datenbank zu einer partiell eigenständigen Datenbank verändern können, müssen Sie mit der systemgespeicherten Prozedur "sp_configure" eine Servereinstellung ändern. Ändern Sie mit der systemgespeicherten Prozedur "sp_configure" die Einstellung "Contained Database Authentication" von 0 zu 1, und aktivieren Sie die Einstellung dann mit der RECONFIGURE-Anweisung, wie folgt:

EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO

Nach Aktivierung der Einstellung "Contained Database Authentication" können Sie eine bestimmte Datenbank zu einer partiell eigenständigen Datenbank machen. Sie können dazu das SQL Server Management Studio, wie in Abbildung 1 gezeigt, oder die ALTER DATABASE-Anwendung verwenden, oder eine neue Datenbank als eigenständige Datenbank erstellen:

ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO

Verbinden Sie sich im Objekt-Explorer mit der SQL Server-Instanz, um eine neue partiell eigenständige Datenbank zu erstellen oder um eine vorhandene Datenbank entsprechend zu ändern. Klicken Sie mit der rechten Maustaste, um eine neue Datenbank zu erstellen, oder wählen Sie eine vorhandene Datenbank. Wählen Sie "Properties" (je nachdem, ob Sie eine neue Datenbank erstellen oder eine vorhandene Datenbank verändern). Wählen Sie in beiden Fällen die Registerkarte "Options", und ändern Sie den Wert im Dropdownmenü "Containment type" von "None" zu "Partial".

Abbildung 1 Im Fenster "Database Properties" können Sie die "Containment"-Einstellung für die Datenbank ändern.

Verwenden Sie zur Änderung einer vorhanden Datenbank von "nicht eigenständig" zu "partiell eigenständig" T/SQL und die ALTER DATABASE-Anweisung. Um die Containment-Einstellung erfolgreich ändern zu können, muss die ALTER DATABASE-Anweisung eine exklusive Sperre der gesamten Datenbank vornehmen können. Daher müssen Sie mit der Unternehmenseinheit, die diese Datenbank nutzt, eine kurze Ausfallzeit einplanen.

Vermeiden temporärer Sortierungsfehler

Neue partiell eigenständige Datenbanken ermöglichen das Vorhandensein mehrerer verschiedener Sortierungen auf einer Instanz von SQL Server. Sie müssen bei der Zusammenführung temporärer Tabellen nicht mehr über Sortierungsprobleme nachdenken. Sie können dies testen, indem Sie eine Microsoft SQL Server-Instanz nehmen und eine partiell eigenständige Datenbank mit einer anderen Sortierung erstellen. Erstellen Sie dann eine physische Tabelle und eine temporäre Tabelle in der partiell eigenständigen Datenbank. Laden Sie Daten in beide Tabellen, und versuchen Sie, sie zusammenzuführen.

Erstellen Sie eine Datenbank mit der Albanian_100_CI_AI_KS_WS-Datenbanksortierung, während die Instanz die SQL_Latin1_General_CP1_CI_AS-Sortierung verwendet, wie in Abbildung 2 gezeigt. In den neuen Datenbanken wird eine Tabelle mit der Bezeichnung "dbo.Employee" erstellt, in die drei Zeilen geladen werden. Darüber hinaus wird eine temporäre Tabelle mit der Bezeichnung "#emp" erstellt, in die eine einzige Zeile eingefügt wird. Wenn die Tabellen am Ende der Anfrage zusammengeführt werden, wird eine Zeile ausgegeben. Ohne die Einrichtung einer partiell eigenständigen Datenbank wäre eine Fehlermeldung die Folge.

Abbildung 2 Erstellen und Verwenden einer partiell eigenständigen Datenbank

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

Eigenständige Benutzer

Eigenständige Benutzer ähneln herkömmlichen SQL Server-Logins, mit dem Unterschied, dass Sie nicht über ein Login verfügen, das dem eigenständigen Datenbankbenutzer entspricht. Erstellen Sie einen eigenständigen Benutzer mit dem SQL Server Management Studio oder der T/SQL-Anweisung CREATE USER.

Verbinden Sie sich zur Verwendung von SQL Server Management Studio im Objekt-Explorer mit der Instanz, die die eigenständige Datenbank hostet. Navigieren Sie zu "Databases | {Ihre eigenständige Datenbank} | Security | Users". Klicken Sie mit der rechten Maustaste auf den Ordner "Users", und wählen Sie aus dem Kontextmenü "New User" aus. Wählen Sie im Dropdownmenü “User type” die Option “SQL user with password.” Geben Sie den Benutzernamen und das Kennwort ein (vgl. Figure 3), sowie das Standardschema und alle Datenbankrollenmitgliedschaften, die Sie möglicherweise konfigurieren müssen.

Abbildung 3 Erstellen eines eigenständigen SQL-Benutzers mit SQL Server Management Studio.

Nach Erstellung des eigenständigen Benutzers können Sie in der eigenständigen Datenbank die Kataloganzeige "sys.database_principals" anzeigen. Der Datensatz, der durch die Anfrage der Katalogansicht ausgegeben wird, zeigt die neue Spalte "authentification_type" mit dem Wert 2. Wenn der Benutzer ein eigenständiger Benutzer ist, zeigt die Spalte "authentication_type_desc" den Wert "DATABASE".

Erstellen Sie einen eigenständigen Datenbankbenutzer mit der T/SQL-Anweisung CREATE USER. SQL Server weiß, dass Sie einen eigenständigen Benutzer erstellen und keinen Benutzer, der sich auf Instanzenebene an ein Login anpasst, wenn Sie die WITH PASSWORD-Anweisung angeben, wie hier gezeigt:

CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO

Sie können eigenständige SQL-Benutzer und eigenständige Windows-Logins erstellen. Ändern Sie den Wert im Dropdownmenü “User Type” (vgl. Abbildung 4) zu “Windows user”, und geben Sie den Benutzernamen ein, ohne ein Login auszuwählen.

Abbildung 4 Erstellen eines eigenständigen Windows-Logins mit SQL Server Management Studio

Sie können eigenständige Windows-Benutzer auch mit der T/SQL-Anweisung CREATE USER erstellen, wie hier gezeigt:

CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO

Melden Sie sich mit einem eigenständigen Login bei der eigenständigen Datenbank an, wie ein herkömmlicher (nicht eigenständiger) Benutzer. Der große Unterschied bei einem eigenständigen Benutzer besteht darin, dass Sie bei der Verbindung den Namen der Datenbank in der Verbindungszeichenfolge angeben müssen. Wenn Sie dies nicht tun, geht SQL Server davon aus, dass der Verbindungsversuch von einem herkömmlichen SQL Server-Login ausgeht. Die Anmeldung schlägt dann fehl, da kein Login vorhanden ist, das dem Benutzernamen und dem Kennwort entspricht, die auf Instanzenebene definiert wurden.

Tun Sie dies im SQL Server Management Studio-Verbindungsdialogfeld, indem Sie auf die Schaltfläche "Options" am unteren Rand des Anmeldefensters klicken. Wählen Sie die Registerkarte "Connection Properties". Auf dieser Registerkarte können Sie den Namen der eigenständigen Datenbank in das Dropdownfeld “Connect to database” eingeben (vgl. Abbildung 5).

Sie können als eigenständiger Benutzer auf der Registerkarte "Login" nicht die Liste der auf der SQL Server-Instanz gehosteten Datenbanken abfragen. Dieser eigenständige Benutzer hat keine Berechtigung für die Master-Datenbank, ohne sich vorher für die eigenständige Datenbank zu authentifizieren, die den jeweiligen Namen und das Kennwort enthält. Daher müssen Sie den Namen der Datenbank kennen und manuell in das Feld eingeben.

Abbildung 5 Die Registerkarte “Connection Properties” des SQL Server Management Studio-Verbindungsdialogfensters.

Wie Sie anhand dieser beiden Features sehen, wird die Konsolidierung von Datenbankinstanzen zu einer Instanz auf SQL Server 2012 deutlich vereinfacht. Es sind etwas Zeit und einige Tests erforderlich, um sicherzustellen, dass Ihre Datenbankanwendung vollständig mit dem "Eigenständige Datenbank"-Feature kompatibel ist. Sobald Sie jedoch die Einrichtung einer eigenständigen Datenbank aktiviert haben, werden künftige Datenbankmigrierungen und -konsolidierungen viel einfacher vonstatten gehen, und darum geht es schließlich.

Denny Cherry, MVP, ist ein unabhängiger Berater mit mehr als zehn Jahren Erfahrung mit Microsoft SQL Server-, Hyper-V-, vSphere- und Enterprise Storage-Lösungen. Er besitzt verschiedene Microsoft-Zertifizierungen im Zusammenhang mit SQL Server-Versionen von 2000 bis 2008, darunter den Microsoft Certified Master. Er ist der Verfasser zahlreicher technischer Artikel und Bücher zum SQL Server-Management.

Verwandter Inhalt

• SQL – Fragen und Antworten: Sicherung und Einrichtung
• SQL Server: Verwaltung der Migration
• SQL Server: Benutzerdefinierte Rollen