Contained Database–die Eigenständige Datenbank

Erst letzte Woche habe ich in meinem Blog nach Contained Databases gesucht .. und nichts gefunden. Wirklich peinlich! Gerade dieses kleine neue Gimmick des SQL Server 2012 ist für  mich eines des praktischsten und häufigst verwendeten neuen Features ab SQL Server 2012.

Zweck der Contained Database

Datenbanken können nicht ohne Systemdatenbanken leben. So werden beispielsweise Logins in der Master Datenbank gespeichert, temporäre Tabellen in der TempDB, Aufträge in der msdb. Dagegen ist überhaupt nichts einzuwenden. Aber sbald man ein Backup einer Datenbank auf einen anderen Server restored fehlen logischerweise wieder diese Dinge. Nicht – zumindest nicht ganz – bei einer Contained Database. Hier werden bspw. Logins in der Contained Database gespeichert und temporäre Tabellen ebenfalls dort erstellt. (zugegebenermaßen: Vor- und Nachteil).

Wie gehts?

Konfiguration des Servers

Bevor Contained Databases erstellt werden können muss auf dem Server die Option Einschlusstyp auf True gesetzt sein:

Das geht natürlich auch per Script:

EXEC sys.sp_configure N’contained database authentication‘, N’1′
GO
RECONFIGURE WITH OVERRIDE
GO

 

Anlegen einer Contained Database

Das Anlegen ist kurz und schmerzlos:

oder per Script natürlich:

USE [master]
GO
ALTER DATABASE [ContDB] SET CONTAINMENT = PARTIAL WITH NO_WAIT
GO

Interessanter ist schon die Tatsache, dass der Einschlusstyp entweder:

1. Keine
2. Teilweise

Nein es ist kein Fehler! Eine vollständige Kapselung einer Datenbank existiert auch nicht unter SQL Server 2014. In der Onlinedokumentation liest man zu folgendes:

Eine teilweise enthaltene Datenbank ist eine enthaltene Datenbank, die die Verwendung nicht enthaltener Funktionen zulässt.

Alles klar, oder?

Letztendlich bedeutet dies, dass es immer noch Dinge gibt, die nicht in der Contained Database vorgehalten werden, sondern immer noch ausserhalb aufgehoben werden. (Jobs, etc.)

Sehen wir uns mal ein paar Beispiele an.

Keine verwaisten User mehr

Legen wir mal einen User an, der nicht wi üblich einem Login (master Datenbank) zugeordnet wird.

 

USE [ContDB]
GO
CREATE USER [Seppl] WITH PASSWORD=N’123@zauberei‘
GO

Natürlich funktioniert das auch mit einem Windows Login

USE ContDB
CREATE USER [FBIss1]

Das Login – auf herkömmliche Art und Weise wird nun natürlich scheitern, da in der master kein Login enthalten ist.

Damit der Login gegenüber der Datenbank funktioniert muss diese explizit angegeben werden:

Das Schöne an der Geschichte ist, dass nicht nur Datenbanken inkl Logins nun leicht von Server zu Server gebracht werden können. (Das Backup selbst reicht ja schon aus), sondern auch Sicht der Administration. Die Benutzer ohne Login haben effektiv keine Zugriffsmöglichkeit auf anderen Datenbanken und noch besser sehen diese nicht einmal:

Allerdings sollte nicht unerwähnt blieben, dass per T-SQL immer noch ein wenig mehr gesehen werden kann.  Zumindest die master und die tempdb sind sichtbar und auch der Zugriff auf Systemsichten ist ebenso möglich. Allerdings ist das nur halb so schlimm, wie man nun denken würde, denn es werden nur Ergebisse zu sehen sein, die den jeweiligen Benutzer der Contained Database betreffen

Datenbankübergreifende Zugriffe?

Ja, das ist möglich. Notwendig dazu ist, dass die Contained Database auf vertrauenswürdig gesetzt ist:

ALTER DATABASE ContDB SET TRUSTWORTHY ON

Anschliessend muss die aktuelle Verbindung des Benutzer zurückgesetzt werden, damit die Änderung aktiv werden kann.

Entweder man schaltet den guest Account aktiv (nicht unbedingt das, was man gerne macht) oder man legt den Benutzer in der anderen Datenbank ebenfalls an.  Das funktioniert allerdings auch nur bei Windows Usern, da SQL Server intern immer SIDs verwendet. Im Falle eines SQL Users werden eigene SID generiert, im Falle von Windows Konten werden die Windows SID verwendet. Wenn man also einen weiteren SQL Benutzer in der Datenbank anlegt, wird dieser bei SQL Konten, defintiv eine andere SID besitzen.

Temporäre Tabellen

Ein weiterer Klassiker der Contained Database sind temporäre Tabellen. Verwendet man temporäre Tabellen, dann landen diese in der tempdb und nehmen dort auch die Sortierung der tempdb an. Weist jedoch aktuelle Datenbank eine andere Sortierung auf, kommt es zu einem Konflikt:

Ändert man die Datenbank auf eine Contained Database, funktioniert das plötzlich wieder:

Die temporäre Tabellen liegen aber nun nicht wirklich in der Contained Database, wie man immer wieder gerne mal liest, sondern nach wie vor in der tempdb, bekommt allerdings die Sortierung der Ursprungsdatenbank verpasst.

Das ist gut so, da die tempDB nach wie vor die Leistung von Auslagerungsvorgangängen übernehmen sollte.

Migration von Datenbanken und Benutzern in Contained Databases

Gelinde gesagt, ist die Migration einer Datenbank in eine Contained Database nichts anderes als das Aktvieren der Contained Database (siehe Einschlusstyp = True s.o.)

Für die Migration eines Benutzers steht explizit eine Prozedur zur Verfügung

sp_migrate_user_to_contained [ @username = ] N'user' ,     [ @rename = ] { N'copy_login_name' | N'keep_name' } ,     [ @disablelogin = ] { N'disable_login' | N'do_not_disable_login' }

Also beispielsweise:

sp_migrate_user_to_contained 
@username = N'Hias',@rename = N'keep_name',@disablelogin = N'do_not_disable_login' ;

Wobei keep_name den Benutzername des Benutzers in der Datenbank übernimmt, dagegen übernimmt copy_login_name den Loginnamen des Benutzers für die Contained Database.

Microsoft stellt dazu sogar ein Codesnippet zur Verfügung mit dem man alle Benutzer auf einen Schlag migrieren könnte.

http://technet.microsoft.com/de-de/library/ff929275.aspx

DECLARE @username sysname ;
DECLARE user_cursor CURSOR     FOR         SELECT dp.name         FROM sys.database_principals AS dp         JOIN sys.server_principals AS sp         ON dp.sid = sp.sid         WHERE dp.authentication_type = 1 AND sp.is_disabled = 0;
OPEN user_cursor
FETCH NEXT FROM user_cursor INTO @username     WHILE @@FETCH_STATUS = 0     BEGIN         EXECUTE sp_migrate_user_to_contained         @username = @username,         @rename = N'keep_name',         @disablelogin = N'disable_login';     FETCH NEXT FROM user_cursor INTO @username     END
CLOSE user_cursor ;
DEALLOCATE user_cursor ;

Einschränkungen und Tipps

• Vermeiden Sie Logins mit gleichen Namen wie der Contained DB Benutzer. Falls doch, dann ändern sie beim Login die Standarddatenbank so, dass nicht gleich auf die ContDB zugegriffen wird. Sonst gibts Fehler bei der Anmeldung.
• keine Kerberos Authentifizierung möglich
• beim Anfügen einer Datenbank den Zugriff auf Restricted User stellen, um nicht unegwollte zugriffe auf dem SQL Server zu haben.
• keine Replikation
• kein CDT und CDC
• keine temporären Prozeduren

PS: Die Contained Database nicht auf AUTO_CLOSE stellen. Ausnahme wäre 1.April Sobald die Datenbank geschlossen ist, gibts auch keine Anmeldung mehr.

 

Coole Sache.. und warten mal auf Einschlusstyp FULL