SQL Server 2012: 때로는 일부만 있는 것이 더 나은 법
SQL Server 2012는 부분적으로 포함된 데이터베이스를 지원할 예정이며, 이를 통해 몇 가지 마이그레이션 및 통합 문제를 완화할 수 있을 것입니다.
Denny Cherry
부분적으로 포함된 데이터베이스를 사용하면 SQL Server 버전 4.2 이후로 SQL Server 데이터베이스의 통합 및 마이그레이션에 관련된 몇 가지 문제를 해결할 수 있습니다. 다행히도 SQL Server 2012에는 이 기능이 여러 새 기능과 함께 추가될 것입니다.
부분적으로 포함된 데이터베이스로 해결할 수 있는 첫 번째 문제는 SQL Server의 한 인스턴스에서 다른 인스턴스로 해당 데이터베이스에 사용되는 SQL Server 로그인을 식별하지 않고도 데이터베이스를 이동할 수 있게 된다는 것입니다. 또한 대상 데이터베이스 인스턴스에서 해당 로그인 생성도 처리합니다.
SQL Server 2012는 포함된 SQL Server 로그인을 사용하여 이 작업을 수행합니다. 포함된 SQL Server 로그인이란 포함된 데이터베이스 내에 생성되지만, 일치하는 SQL Server 로그인이 없는 SQL Server 사용자입니다. 포함된 데이터베이스 내의 이 포함된 사용자에는 포함된 데이터베이스 내에 저장된 암호가 있으므로 인증은 예상대로 작동합니다.
두 번째로 해결된 문제는 포함된 데이터베이스 내의 테이블과 임시 테이블 간의 데이터 정렬 충돌이 실질적으로 사라졌다는 것입니다. SQL Server는 데이터가 정렬될 때 포함된 데이터베이스의 범위 내에 자동으로 임시 테이블을 만듭니다. 이를 통해서 데이터 정렬을 지정하기 위해 CREATE TABLE 문을 수정하거나 JOIN 문의 구문에 COLLATE 문을 지정하지 않고도 SQL Server의 같은 인스턴스가 데이터 정렬이 다른 데이터베이스를 호스팅할 수 있습니다.
데이터베이스 호환성 수준을 최신 값으로 설정해야 사용이 가능한 일부 새로운 기능과는 달리, 부분적으로 포함된 데이터베이스는 현재 SQL Server 2005까지 지원합니다. 반면에 포함된 데이터베이스 기능은 아직 완성되지 않았습니다. 이 기능은 SQL Server 2012 CTP3 릴리스 정도에서 완성될 것으로 예상되며, 2012년 초반이나 중반에 제품이 출시될 것입니다.
부분적으로 포함된 데이터베이스 설정
포함되지 않는 데이터베이스를 부분적으로 포함된 데이터베이스로 변경하려면 먼저 sp_configure system 저장 프로시저를 사용해서 서버 설정을 변경해야 합니다. sp_configure 시스템 저장 프로시저를 사용하여 “포함된 데이터베이스 인증” 설정을 0에서 1로 설정하고 RECONFIGURE 문을 사용해서 다음과 같이 새 설정을 활성화합니다.
EXEC sp_configure 'contained database authentication', 1 RECONFIGURE GO
“포함된 데이터베이스 인증” 설정을 활성화한 후에는 특정 데이터베이스를 부분적으로 포함된 데이터베이스로 변경할 수 있습니다. 그림 1과 같이 SQL Server Management Studio를 사용하거나 ALTER DATABASE 문을 사용하거나 새 데이터베이스를 만들 때 포함된 데이터베이스로 만들 수 있습니다.
ALTER DATABASE: USE [master] GO ALTER DATABASE [Cont] SET CONTAINMENT=PARTIAL GO CREATE DATABASE: CREATE DATABASE [Cont1] CONTAINMENT=PARTIAL GO
부분적으로 포함된 데이터베이스를 새로 만들거나 기존 데이터베이스를 부분적으로 포함된 데이터베이스로 변경하려면 개체 탐색기에서 SQL Server 인스턴스에 연결합니다. 새 데이터베이스를 마우스 오른쪽 단추로 클릭하거나 기존 데이터베이스를 선택합니다. 속성을 선택합니다(새 데이터베이스를 만드는지 또는 기존 데이터베이스를 변경하는지에 따라 다릅니다). 두 경우 모두 “옵션” 탭을 선택하고 “포함 유형” 드롭다운에서 “없음”을 “부분”으로 변경합니다.
.jpg)
그림 1 데이터베이스 포함 설정을 변경할 수 있는 데이터베이스 속성 창.
기존의 포함되지 않은 데이터베이스를 부분적으로 포함된 데이터베이스로 변경하려면 T/SQL 및 ALTER DATABASE 문을 사용합니다. 데이터베이스 포함 설정을 변경하려면 ALTER DATABASE 문이 전체 데이터베이스에 대한 배타적 잠금을 확보할 수 있어야 합니다. 따라서 이 특정 데이터베이스를 사용하는 비즈니스 부서에 대한 짧은 작업 중단을 예약해야 합니다.
임시 테이블 데이터 정렬 오류 방지
부분적으로 포함된 새 데이터베이스는 같은 SQL Server 인스턴스에 여러 다른 데이터 정렬이 존재하도록 허용합니다. 이제 임시 테이블을 조인할 때 데이터 정렬 문제 때문에 걱정할 필요가 없습니다. 이 기능을 테스트하려면 먼저 Microsoft SQL Server 인스턴스에서 다른 데이터 정렬을 사용하는 부분적으로 포함된 데이터베이스를 만듭니다. 그런 다음 부분적으로 포함된 데이터베이스 내에 실제 테이블과 임시 테이블을 하나씩 만듭니다. 두 테이블에 데이터를 로드하고 조인합니다.
그림 2에 나오는 것처럼 인스턴스에서 SQL_Latin1_General_CP1_CI_AS 데이터 정렬이 사용되는 상태에서 Albanian_100_CI_AI_KS_WS 데이터베이스 데이터 정렬을 사용하는 데이터베이스를 만듭니다. 그런 다음 새 데이터베이스 내에 dbo.Employee라는 테이블을 만들고 테이블에 행 세 개를 로드합니다. 또한 #emp라는 임시 테이블을 만들고 이 테이블에 행 하나를 삽입합니다. 쿼리 끝에서 테이블을 조인하면 행 하나가 반환됩니다. 포함된 데이터베이스 설정을 부분으로 변경하지 않았다면 오류가 반환되었을 것입니다.
그림 2 부분적으로 포함된 데이터베이스 만들기 및 사용.
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
포함된 사용자
포함된 사용자는 기존 SQL Server 로그인과 비슷하지만 포함된 데이터베이스 사용자에 맞는 로그인이 없다는 점이 다릅니다. 포함된 사용자는 SQL Server Management Studio 또는 CREATE USER T/SQL 문을 사용해서 만듭니다.
SQL Server Management Studio를 사용하는 경우 개체 탐색기에서 포함된 데이터베이스를 호스팅하는 인스턴스를 연결합니다. 데이터베이스 | {사용자의 포함된 데이터베이스} | 보안 | 사용자로 이동합니다. 사용자 폴더를 마우스 오른쪽 단추로 클릭하고 상황에 맞는 메뉴에서 “새 사용자”를 선택합니다. “사용자 유형” 드롭 다운에서 “암호를 사용하는 SQL 사용자”를 선택합니다. 사용자 이름과 암호 필드를 입력하고(그림 3 참조), 그 밖의 구성해야 하는 데이터베이스 역할 멤버 자격과 기본 스키마를 지정합니다.
.jpg)
그림 3 SQL Server Management Studio로 포함된 SQL 사용자 만들기.
포함된 사용자를 만든 후에는 포함된 데이터베이스 내의 sys.database_principals 카탈로그 뷰를 조사할 수 있습니다. 카탈로그 뷰를 쿼리하여 반환된 레코드 집합을 보면 authentication_type이라는 새 열의 값이 2로 설정되었음을 알 수 있습니다. 또한, 사용자가 포함된 사용자일 때 authentication_type_desc 열이 “DATABASE” 값으로 설정되었음을 보여 줍니다.
T/SQL에서 CREATE USER 문을 사용하여 포함된 데이터베이스 사용자를 만듭니다. 다음과 같이 WITH PASSWORD 문을 지정하면 인스턴스 수준에서 로그인에 맞는 사용자가 아닌 포함된 사용자를 만든다는 것을 SQL Server에 알려줄 수 있습니다.
CREATE USER MyContainedUser WITHPASSWORD = 'MyContainedUserPassword' GO
포함된 Windows 로그인과 마찬가지로 포함된 SQL 사용자도 만들 수 있습니다. “사용자 유형” 드롭 다운(그림 4 참조)을 “Windows 사용자”로 변경하고 로그인을 선택하지 않고 사용자 이름을 입력합니다.
.jpg)
그림 4 SQL Server Management Studio를 사용하여 포함된 Windows 로그인 만들기.
다음과 같이 CREATE USER T/SQL 문을 사용해서 포함된 Windows 사용자를 만들 수도 있습니다.
CREATE USER [CAPT-MAL\test] WITH DEFAULT_SCHEMA = [dbo] GO
기존(포함되지 않은) 사용자와 마찬가지로 포함된 로그인으로 포함된 데이터베이스에 로그온합니다. 포함된 사용자에 대한 중요한 점은 연결할 때 연결 문자열에 데이터베이스 이름을 지정해야 한다는 것입니다. 그렇지 않으면 SQL Server에서 기존 SQL Server 로그인으로 연결을 시도하는 것으로 판단합니다. 로그인 시도는 인스턴스 수준에서 정의된 사용자 이름과 암호가 일치하는 로그인이 없는 경우 실패합니다.
직접 확인해 보기 위해서 SQL Server Management Studio의 연결 대화 상자에서 로그인 창 아래쪽에서 옵션 단추를 클릭합니다. “연결 속성” 탭을 선택합니다. “연결 속성” 탭에서 포함된 데이터베이스의 데이터베이스 이름을 “데이터베이스에 연결” 드롭 다운에 입력할 수 있습니다(그림 5 참조).
“로그인” 탭에 지정된 포함된 사용자로는 SQL Server 인스턴스에서 호스팅되고 있는 데이터베이스의 목록을 쿼리할 수 없습니다. 이 포함된 사용자는 해당 사용자 이름과 암호가 저장된 포함된 데이터베이스에 대한 인증을 수행하기 전에는 master 데이터베이스에 대한 권한이 없습니다. 따라서 데이터베이스 이름을 알고 입력란에 수동으로 입력해야 합니다.
.jpg)
그림 5 SQL Server Management Studio 연결 대화 상자 창의 “연결 속성” 탭.
이러한 두 기능에서 볼 수 있듯이, 데이터베이스 인스턴스를 SQL Server 2012의 인스턴스로 통합하기가 훨씬 쉬워졌습니다. 시간도 적게 들며 데이터베이스 사용자의 응용 프로그램과 포함된 데이터베이스 기능이 완전히 호환되는지 확인하는 테스트도 간소해졌습니다. 그러나 일단 포함된 데이터베이스 설정을 활성화하면 향후 데이터베이스 마이그레이션과 통합이 훨씬 원활해집니다. 바로 이것이 핵심입니다.
.jpg)
Denny Cherry는 Microsoft SQL Server, Hyper-V, vSphere 및 Enterprise Storage 솔루션에 대한 십여 년 이상의 경험을 갖춘 독립 컨설턴트이자 MVP입니다. 현재는 Microsoft Certified Master를 비롯해서 SQL Server 버전 2000부터 2008까지와 관련된 여러 Microsoft 인증 자격을 보유하고 있습니다. 그는 또는 SQL Server 관리에 대한 여러 권의 저서와 수십 가지 기사를 썼습니다.