SQL Server 2005 to 2008R2 Database Move

SQL Server 2005 to 2008R2 Database Move

상위버전의 SQL Server 로 데이터베이스 이전할 때 고려해야 하는 몇가지가 있다.

아래와 같이 순서대로 따라해 보자.

 

#. 시나리오.

SQL Server 2005 에 MoveDB 의 데이터베이스가 있고, 해당 DB에 overtop 이란 계정으로 접근 권한을 준 상태이다. 이 환경에서 MoveDB만 SQL Server 2008R2 로 이전한다.

 

USE MASTER

GO

CREATE DATABASE MoveDB

GO

 

CREATE LOGIN moveuser WITH PASSWORD=N'qwer1234!', DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

USE MoveDB

GO

CREATE USER moveuser FOR LOGIN moveuser

GO

EXEC sp_addrolemember N'db_owner', N'moveuser'

GO

 

 

#.1 MoveDB에 연결된 세션을 확인 해보자.

아래에 3개 세션이 연결되어 있는 것을 확인이 가능하다.

-- dbname 컬럼으로확인이가능하다.

sp_who

 

MoveDB에 APP에서 주기적으로 연결을 하고 있다고 가정하자.

이때 Backup을 하기 위해서는 해당 세션을 모두 끊어야 한다.

 

-- 세션이주기적으로접속을차단하기위해서단일사용자모드로변경하자

-- 수행되는세션이비정상종료되기때문에어플리케이션의서비스를종료하는방법이최적이다.

USE MASTER

GO

ALTER DATABASE MoveDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

/*

불완전한트랜잭션이롤백되고있습니다. 예상롤백완료율: 100%.

*/

 

#.2 MoveDB 를 전체백업을 수행한다.

-- 해당데이터베이스에연결을차단한상태에서전체백업을수행한다.

BACKUP DATABASE MoveDB TO DISK='C:\temp\MoveDB.bak'

/*

파일1에서데이터베이스'MoveDB', 파일'MoveDB'에대해176개의페이지를처리했습니다.

파일1에서데이터베이스'MoveDB', 파일'MoveDB_log'에대해2개의페이지를처리했습니다.

BACKUP DATABASE이(가) 178개의페이지를0.354초동안처리했습니다(4.116MB/초).

*/

 

-- 여기서잠깐

-- 단일사용자모드지만1개세션은연결이가능하다.

-- 세션개를연결한상태에서전체백업을해보자.

-- 오류: 백업을하는동안에해당데이터베이스에접속시도를하기에실패되는것같다.

-- 해결: 해당세션을KILL 하면된다.

BACKUP DATABASE MoveDB TO DISK='C:\temp\MoveDB.bak'

/*

메시지924, 수준14, 상태1, 줄4

데이터베이스'MoveDB'이(가) 이미열려있고한번에한사용자만사용할수있습니다.

메시지3013, 수준16, 상태1, 줄4

BACKUP DATABASE이(가) 비정상적으로종료됩니다.

*/

 

#.3 MoveDB.bak 신규서버에 COPY 한다.

#.4 신규서버에서 MoveDB.bak 을 복원한다.

-- 복원경로확인후복원한다.

RESTORE DATABASE MoveDB FROM DISK = N'C:\tmp\MoveDB.bak' WITH

MOVE N'MoveDB' TO N'C:\MSSQL\DATA\MoveDB.mdf',

MOVE N'MoveDB_log' TO N'C:\MSSQL\DATA\MoveDB_1.LDF', STATS = 10

GO

/*

13 percent processed.

....

100 percent processed.

Processed 176 pages for database 'MoveDB', file 'MoveDB' on file 1.

Processed 1 pages for database 'MoveDB', file 'MoveDB_log' on file 1.

Converting database 'MoveDB' from version 611 to the current version 661.

Database 'MoveDB' running the upgrade step from version 611 to version 621.

Database 'MoveDB' running the upgrade step from version 621 to version 622.

.....

Database 'MoveDB' running the upgrade step from version 660 to version 661.

RESTORE DATABASE successfully processed 177 pages in 0.158 seconds (8.751 MB/sec).

*/

 

ㄴㅇㄹ

 

 

복원 후 SSMS 개체탐색기에서 확인해보자.

복원 후에도 MoveDB 는 Single_User(단일사용자모드) 상태로 유지된다.

 

 

#.5 Multi_User 모드로 변경하자.

변경시 1개 세션이 이미 MoveDB에 접속한 상태에서는 변경이 안된다. 해당 세션을 Kill 을 해야 한다.

USE MASTER

GO

ALTER DATABASE MoveDB SET MULTI_USER ;

GO

 

 

#.6 사용자정보에 대하여 확인한다.

신규서버에는 로그인사용자인 MoveUser가 생성이 되지 않았지만 MoveDB에는 매핑이 된 상태로 복원이 되어 있다.

 

이 상황에서 로그인사용자 moveuser를 생성해보자

CREATE LOGIN moveuser WITH PASSWORD=N'qwer1234!', DEFAULT_DATABASE=[master],

CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

 

생성한 moveuser 에서 복원한 MoveDB에 매핑을 하려면 아래와 같이 오류가 발생한다.

이미 계정이 존재한다는 오류이다.

 

해당 계정으로 SSMS 로그인 후 MoveDB에 접속을 하면 오류가 발생한다.

계정은 모두 존재하지만

 

 

#.7 계정 SID 매핑하기

계정생성을 통하여 똑 같은 환경으로 설정하였지만 위 처럼 오류가 발생한다.

이것은 이전한 moveuser와 신규서버에서 생성한 moveuser 간의 SID 가 틀리기 때문에

수동으로 매칭하는 방법으로 해결 가능하다.

 

 

USE MoveDB

GO

sp_change_users_login 'report'

GO

sp_change_users_login 'update_one', 'moveuser', 'moveuser'

GO

sp_change_users_login 'report'

GO

 

 

다시 접속하여 가능한지 확인하면 정상적으로 접근이 가능할 것이다.

 

#.8

SQL Server 2005 에서 상위버전으로 이전을 하게 되면 호환성수준(Compatibility Level)을 확인을 해야한다.

업그레이드 어드바이저(Upgrade Advisor)툴로 호환성검사를 하는 경우도 발생한다.

 

현재 상태에서는 복원을 했기 때문에 호환성수준이 90 (2005) 수준으로 복원이 된다.

상위버전으로 이전한 경우에는 아래와 같이 변경을 해줘야 한다. (선택)

 

USE [master]

GO

ALTER DATABASE [MoveDB] SET COMPATIBILITY_LEVEL = 100

GO

 

 

#.9 완료.

어플리케이션에서 접속 및 서비스 테스트를 해야 하며, 정상적으로 서비스가 가능하다 판단이 되면 최종으로 MoveDB 를 전체백업을 해주고 , 백업 정책을 설정하고 마무리한다.