[NF 2011 Denali] Contained Database
해당 기능은 데이터베이스 이동을 쉽게 해주고 독립적인 데이터베이스 사용을 가능하게 해주는 것을 목표로 하고 있다. 그러나 현재까지는 부분적인 Contained Database 모드만 지원하며, 아직은 실험적이며, Replication, CDC, Database mail, Service Broker 등 주요한 기능을 지원하지 않고 Job Agent 도 지원하지 않아 2011 정식 릴리즈가 나올때도 이 상황이라면 실제 사용에는 무리가 있어 보인다. 하지만, 향 후 메이저 릴리즈를 몇번 거치면 분명 훌륭한 솔루션으로 완성될 것이다. (개인적으로는 소규모 데이터베이스를 위한 기능 인듯 한데... 이런데 좀 그만 투자하고 힌트 좀 더 만들고 좀 더 가볍게 돌고, TCO 관점에서 메리트 있는 프러덕트를 만들었으면 한다. SSMS 도 좀 고치고......)
원본 아티클
http://sqlblog.com/blogs/aaron_bertrand/archive/2010/11/16/sql-server-v-next-denali-contained-databases.aspx
번역본 (by 김민석)
Aaron Bertrand
Aaron 은 SQL Sentry 의 시니어 컨설턴트이다. SQL Server, Analysis Services 와 Windows 의 성능 모니터링 과 이벤트 관리 소프트웨어 개발자 이다. 관리, 성능, 신 기능에 관심을 두고 2006년 부터 sqlblog.com 에서 블로깅 하고 있다. 1997년 부터 SQL Server MVP 이다. 트위터 아이디는 @AaronBertrand 이며, SQL Saturday 와 유저 그룹에서 종종 스피커로 활동한다.
SQL Server V.Next (Denali) : Contained Databases
SQL Server 데이터베이스가 포터블하지 않은 것은 오랫동안 골치꺼리 였다. 문론 백업 리스토어나 디테치 어테치 등의 방법으로 다른 위치에 이동은 가능하다. 그러나 그러한 작업을 할 때, 어플리케이션의 한 부분인 데이터베이스의 많은 부분이 누락된다. 그리고 많은 부분들은 어플리케이션 파트가 아니라 관리영역이다. 그리고 데이터베이스 밖에 있는 부분들에 대해 동기화 하지 못한다. 예를 들자면, Security, roles, linked servers, CLR, Database mail, service broker, replication, Agent job. 또한 대상 서버가 다른 정렬을 사용하면, 임시테이블을 사용하거나 다른 데이터베이스과 join 할 때 문제가 생긴다. 초기 데이터베이스 이관 후, 호환되지 않는 많은 오류 이슈를 알고 있을것이라 생각된다.
"Contained Databases" 속으로~
Denali 에서는 몇 몇 이슈가 Contained database 로 소개 되었다. 정식 버전이 나올때 이것이 마켓팅 용어가 될지 확신하지 못하겠지만, 한번 살펴보자. (DMF) 동적 관리 프레임워크에서 (PBM) 정책 기반 관리로 바꿀 때 처럼 DDL 이 내장된 이후는 그 이름을 더 고치기 힘들다. 기본적으로 Contained database 는 해당 데이터베이스를 좀 더 black box 화 하고, 서버나 인스턴스 수준의 관리에서 어플리케이션과 특정 데이터베이스로 기능으로 분리시킨다.
Denali 첫번째 버전에서 Contained database 기능은 다음과 같은 솔루션을 제공한다.
* login 없이 특정 데이터베이스 user를 만들 수 있다. (그리고 다른 데이터베이스를 위해 같은 이름으로 이런 다중 user 를 만들수 있다)
* 정렬이 다른 데이터베이스 환경에서 tempdb 호환성(완벽하지 않음)을 유지할 수 있다. 왜냐하면, 임시테이블은 데이터베이스 context 정렬로 만들어 지기 때문이다.
* DMV 를 통해 Contained database (Containment) 에 위협을 가하는 (호환되지 않는) 모든 개체와 코드를 볼 수 있다.
* XEvents 로 동작중 일때만 알 수 있는것도 인식 할 수 있다. (김민석 추가)
개체와 엔티티들은 두개의 다른 카테고리로 나눌 수 있다.
Contained 와 UnContained 로 나눌 수 있다. Containted 엔티티는 외부 의존이 없는 것 또는 적어도 서버나 인스턴스간 의존성이 없는 것이다. Contained 개체의 예는 데이터베이스 내에서 외부 참조가 없는 것이다. 전체 리스트는 BOL의 다음 토픽을 참고 한다.
Features Within the Application Model
Uncontained 오브젝트는 명시적으로 외부 의존성이 있거나 (Three Four part names) 나 판단할 수 없는것 (Dynamic SQL) 이다. 명시적으로 uncontained 로 고려되는 것은 다음 bol 토픽을 참고한다.
Features Outside of the Application Model
몇몇 것들은 빠져 있다. 예를 들자면 HOST_NAME 이 목록에 없다. (이것은 Connect 에 리포트 되었다) 마지막으로, containment 를 수용하기 위해 변경된 것은 bol 의 다음 토픽을 참고한다.
Modified Features (Contained Databases)
가장 흥미로운것은 CREATE / ALTER DATABASE 변경이다. 이들은 Contained Database 에서는 다르게 동작한다. 새로운 옵션인 CURRENT 가 포함되었다. 만약 해당 데이터베이스를 새로운 인스턴스로 옮기거나 데이터베이스 이름을 바꿀때 이 명령이 사용된다. 명령어 설명 문서에 contained database 에서는 반드시 ALTER DATABASE CURRENT 를 사용하고 uncontainted database 에서는 ALTER DATABASE <database name> 을 사용하라고 되어있다. 하지만, 현재 CTP 판에서는 적용되어 있지 않다. 다른 흥미있는 토픽은 Collation Type (정렬), 임시 테이블의 제한사항, user option 이 있다.
Contained database 로 변경하기 (Turning containment on)
Denali 에서는 부분적 Containment 만 지원한다. 이말은 uncontained 엔티티를 만들 수 있다는 것이다. 그리고 이것을 DMV 를 통해 구분 할 수 있다. 하지만 엔진은 이러한 것들을 막지 못한다. 다음 버전의 SQL Server 에서는 완전한 Contained database 를 지원할 것이다. 이말은 containment 를 강제화 할 수 없다는 말이다.
정렬이 다른 여러 데이터베이스를 만들고 이런 기능을 사용해 보자. 주의할 것은 서버레벨 Configuration 옵션을 변경해야 이러한 Contanment DDL 을 사용할 수 있다.
|
USE [master]; GO CREATE DATABASE [ContainedDB1] COLLATE SQL_Latin1_General_CP1_CI_AS; GO CREATE DATABASE [ContainedDB2] COLLATE Finnish_Swedish_100_CI_AI_SC; GO EXEC sp_configure 'show advanced options', 1; GO RECONFIGURE WITH OVERRIDE; GO EXEC sp_configure 'contained database authentication', 1; EXEC sp_configure 'show advanced options', 0; GO RECONFIGURE WITH OVERRIDE; GO ALTER DATABASE [ContainedDB1] SET CONTAINMENT = PARTIAL; GO ALTER DATABASE [ContainedDB2] SET CONTAINMENT = PARTIAL; GO |
로그인 없이 유저 만들기
이제 로그인에 묶이지 않은 유저를 만들 수 있다. 문법은 CREATE LOGIN 과 유사하다. password 호환성 default_schema default_language 등 대부분의 기능을 사용할 수 있다. 여기 각각의 데이터베이스별 유저를 만든다. 이름은 동일하게 MyUser 이고 암호는 다르다.
|
USE [ContainedDB1]; GO
GO
GO
GO |
uncontainted database 에서 위 명령을 실행하면 다음과 같은 에러가 발생한다.
Msg 33233, Level 16, State 1, Line 1
You can only create a user with a password in a contained database.
자신에게 물어봐라, 두명의 유저가 같은 이름이라면 SQL Server 가 어떻게 니가 원하는 유저를 구분하겠는가? 유저는 이제 database 레벨에서 먼저 인증된다. (접속 문자열에 데이터베이스 이름이 명시되어 있다.) 그리고 만약 해당 database 에 유저를 찾지 못하면, 같은 이름의 로그인을 찾는다. 이는 성능 관점에서 고려되었으며, 반대로는 하지 않는다 -> 로그인을 찾고, 실패하면 각각의 모든 데이터베이스에서 같은 이름의 유저가 있는지 찾는것. 데이터베이스가 명시되어 있지 않으면 인증은 바로 실패한다. 그래서 만약 정확한 contained database name 유저와 database context 를 기술하지 않으면, 암호가 일치해도 인증은 실패한다. 이말은 default database 에 의존하지 말라는 것이다. (이러한 것은 Contained database 에는 없다.) 접속 문자열에 "Initial Catalog" 속성을 명시적으로 적어야 한다.
Contained database 에 "SQL user with password" 라는 새로운 속성 페이지를 볼 수 있다.
큰 차이점은 이제 이러한 데이터베이스를 다른 서버로 옮길때 단지 접속 문자열만 다시 가르키면 된다. 로그인 이나 SID 매칭 작업은 필요 없다. 그리고 Windows principals 도 가능하다. 그러나 이것은 별로 흥미롭지 않다. 왜냐하면, 이들 데이터베이스는 로그인에 종속적이지 않기 때문이다.
|
USE [ContainedDB1]; GO
GO |
하지만, contained database 에서 주의할 것은 Windows principals 는 자동으로 엔진과 현재 데이터베이스에 인증된다. 반면 uncontained database 에서는 관련된 로그인이나 Windows group 을 통해 인증 된다. 더 많은 정보는 업데이트된 CREATE USER 구문과, BOL 토픽을 살펴봐라. 그리고 새로운 시스템 저장 프로시저인 sys.sp_migrate_user_to_contained 는 기존의 SQL 인증 유저를 암호와 함께 contained 데이터베이스 유저로의 마이그레이션을 돕는다.
다음을 주의해라. 데이터베이스를 암호 복잡성 룰이 다른 새 서버로 옮길때 로그인을 새로 만들거나 암호를 고치지 않는한 검사하지 않는다. 그래서 복잡한 암호 사용이 강제화된 서버에서 약한 암호가 사용될 수 있다.
보안은 큰 이슈이고, Contained 데이터베이스에서는 로그인과 유저가 어떻게 동시에 존재할 수 있고 없음을 대충 훓터보았다. 흥미로운 시나리오가 있으면 알려달라. 그리고 다음 BOL 토픽을 살펴 봐라. "Threats Against Contained Databases." 또 하나의 흥미로운 토픽은 AUTO_CLOSE 이다. contained databases 와 AUTO_CLOSE 는 서비스 중단 공격이 되기도 한다. 왜냐하면, Contained database 유저 인증에 더많은 리소스가 필요하기 때문이다.
Tempdb 정렬 이슈 해결 (Resolving tempdb collation issues)
이 코드는 Denali 이전 버전이나 uncontained 데이터베이스에서는 흥미로운 점이 없다. (서버는 Finnish_Swedish_100_CI_AI 과 다른 정렬을 사용한다.)
|
USE [master]; GO CREATE DATABASE [test] COLLATE Finnish_Swedish_100_CI_AI; GO USE [test]; GO CREATE TABLE dbo.foo ( bar NVARCHAR(32) ); CREATE TABLE #foo ( bar NVARCHAR(32) ); GO SELECT * FROM #foo AS a INNER JOIN dbo.foo AS b ON a.bar = b.bar; GO DROP TABLE #foo; GO USE [master]; GO DROP DATABASE [test]; GO |
이 조인 조건은 다음과 같은 에러를 발생시킨다. 왜냐하면, 임시테이블은 데이터베이스 기본 정렬이 아니라, 서버 기본 정렬로 생성되기 때문이다.
Msg 468, Level 16, State 9, Line 3
Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Finnish_Swedish_100_CI_AI" in the equal to operation.
Denali Contained database 에서는 임시 오브젝트가 해당 데이터베이스 정렬로 만들어진다. 그래서 Contained 데이터베이스를 만들고 위 코드를 다시 실행하면 잘 동작한다.
문론 이것이 모든 이런 이슈를 해결하지는 않는다. 여전히 다른 정렬에서는 에러가 발생할 수 있다. 그리고 일반적인 정렬이슈를 말하는 것이 아니다. 단지 많은 문제에 노출되지 않는다는 것을 말한다. 그래서 정렬이 민감한 환경에서는 문제 해결에 더 많은 시간을 보내야 한다. 하지만 사용하는 T-SQL 쿼리가 완전히 Contained 되고 정렬이슈 영향이 적다면 더 쉽게 데이터베이스를 옮길 수 있다.
데이터베이스 이식성 조사 (Discovering threats to database portability)
심지어 containemt 기능을 사용하지 않더라도, 이식 대상이 되는 데이터베이스에서 어떤 부분이 이식성에 문제가 있는지 알아내는 것은 중요하다. (contained 로 알려진) 그래서, sys.dm_db_uncontained_entities 라는 DMV 가 이것을 도와준다. objects 뿐만 아니라 프로시저 평선 뷰 트리거도 구분할 수 있다. 같은 모듈에서 여러 위반사항을 확인 할 수 있다. Contained 데이터베이스에서 아래 여러 오브젝트를 생성하고 containment 에 어떤 영향을 주는지 살펴보자.
|
USE [master]; GO -- create a login: CREATE LOGIN MyLogin WITH PASSWORD = 'DB1'; GO
GO
CREATE USER [MyLogin] FROM LOGIN [MyLogin]; GO
CREATE PROCEDURE dbo.foo AS BEGIN EXEC('SELECT * FROM table1'); EXEC('SELECT * FROM table2'); SELECT * FROM dbo.object_does_not_exist; END GO
CREATE SYNONYM dbo.bar FOR [master].sys.backup_devices; GO
CREATE PROCEDURE dbo.getbar AS BEGIN SELECT * FROM dbo.bar; END GO -- create a procedure that calls xp_cmdshell: CREATE PROCEDURE dbo.use_xp_cmdshell AS BEGIN EXEC xp_cmdshell 'dir C:\'; END GO
CREATE PROCEDURE dbo.use_dbmail AS BEGIN EXEC msdb.dbo.sp_send_dbmail; END GO -- create a silly function that generates a random object_id: CREATE FUNCTION dbo.GenRandNumber() RETURNS BIGINT AS BEGIN RETURN ( SELECT TOP 1 [object_id] FROM msdb.sys.objects; ); END GO
CREATE TABLE dbo.nonsense ( id INT NOT NULL DEFAULT dbo.GenRandNumber() ); GO |
나는 해당 DMV 를 통해 문제를 어떻게 해결하지는 보여주기 위해 아래 쿼리를 실행했다.
|
SELECT e.feature_name, [object] = COALESCE( QUOTENAME(SCHEMA_NAME(o.[schema_id])) + '.' + QUOTENAME(o.[name]), QUOTENAME(SCHEMA_NAME(s.[schema_id])) + '.' + QUOTENAME(s.[name]) ), [line] = COALESCE(e.statement_line_number, 0), [statement / synonym target / route / user/login] = COALESCE( s.[base_object_name], SUBSTRING( m.[definition], e.statement_offset_begin / 2, e.statement_offset_end / 2 - e.statement_offset_begin / 2 ) COLLATE CATALOG_DEFAULT, r.[name], 'User : ' + p.[name] + ' / Login : ' + sp.[name] ) FROM sys.dm_db_uncontained_entities AS e LEFT OUTER JOIN sys.objects AS o ON e.major_id = o.[object_id] AND e.class = 1 LEFT OUTER JOIN sys.sql_modules AS m ON e.major_id = m.[object_id] AND e.class = 1 LEFT OUTER JOIN sys.synonyms AS s ON e.major_id = s.[object_id] AND e.class = 1 LEFT OUTER JOIN sys.routes AS r ON e.major_id = r.[route_id] AND e.class = 19 LEFT OUTER JOIN sys.database_principals AS p ON e.major_id = p.principal_id AND e.class = 4 LEFT OUTER JOIN sys.server_principals AS sp ON p.[sid] = sp.[sid]; |
결과
dbo.foo 저장프로시저가 DMV 에 의해서 3번 호출된것을 볼 수 있다. 2번은 동적 SQL 에 의해서, 또 한번은 존재하지 않는 개체 때문이다. (이것이 갑자기 만들어 졌을때 uncontained 개체가 될 수 도 있기 때문에 출력되었다.) 라인 넘버도 매우 유용하다. 그래서 해당 개체 정의를 확인 할 때, 검색하지 않고 바로 그 라인으로 가 볼 수 있다.
이 DMV 가 SYNONYM 을 잡아내지 못했음에 주의 해라. 해당 SYNONYM 은 3부분으로 외부 참조를 한다. 그러므로 SYNONYM 을 모두 찾아 조사해야 한다. 이 건은 Connect #622368 로 보고 되었으며 향 후 고쳐지길 바란다.
그리고 table 이 function 을 사용할 때 function 내부에서 uncontained 참조가 있어도 잡아내지 못한다. 이 건은 Connect 에 올리지 않았다. 이건 잘 일어나지 않는 이슈이기 때문이다. 그래도 알고 있어야 한다.
마지막으로, AutoCreatedLocal 이 뭔지 잘 모르겠다.
(Finally, I am not sure exactly what you can do about the AutoCreatedLocal route; this is the first time I've ever noticed this entity)
해당 기능은 Service Broker 기능으로 잘 모를수도 ...(김민석)
DMV 에서 Assembly , Type, Full-text Index, Database DDL Trigger 와 Audit Specification 등 다른 엔티티도 다루는데 여기서 다루지 않았다. 등이다. 하지만 만약 이러한 아이템을 사용한다면, 같이 살펴봐야 한다.
결론
버전을 올리면서 분명 다른 많은 방면에서 작업 해 연결서버나 데이터베이스 레벨의 에이전트 작업 같은것들도 되게 할것이며 이는 매우 흥미로울 것이다. 나는 이들의 연구 수준을 느낄 수 있었고, 첫번째 증인이 될것이다. 앞으로의 메이저 버전이 나올때 마다 해당기능의 발전을 살펴볼 것이다.
For more information on contained databases, there is a great Books Online topic in the works:
