[MsSQL] Database File 디스크 이동 DBA 실무 - detach / attach

안녕하세요.

초보 DBA 다뉴입니다.

 

얼마전 야간작업으로 서버를 내리고, MsSQL Database File의 디스크 이동을 하게 되었는데요.

해당 작업을 하며 알게 된 점과 작업의 순서 혹은 주의사항에 대해 간략하게 정리 및 공유 할까 합니다.

 

MsSQL의 Database File에 대해서는 아래의 글에 자세히 정리해 두었습니다.

[MsSQL] Database File(Data File, Log File) / File Group

요번 야간작업의 Database File의 디스크 이동은 전체적인 서비스 효율을 향상하기 위해,

더 좋은 디스크로 바꾸는 작업이였는데요.

 

디스크를 바꾸면서 Data의 I/O 분산 및 관리를 위해,

각 Data File / Log File을 여러 디스크에 분산 하여 저장하도록 균형을 맞추며 진행하였습니다.

 

생각보다 별거 없는 작업이였지만, 역시나 이런 큰 작업을 하는 것은 언제나 무섭더라구요.

 

데이터베이스의 연결을 끊고, 데이터베이스 파일을 옮긴 후

다시 데이터베이스의 연결을 하는 과정으로 작업을 진행하였습니다.

 

데이터베이스의 연결과 관련된 작업을 어떻게 하는지, 신경써야 하는 부분은 무엇인지 한번 알아보도록 하죠.

---

데이터베이스 분리 / 연결 (Database Detach / Attach)

데이터베이스 분리 (Database Detach)

아래의 내용은 데이터베이스를 분리하는 스크립트입니다.

exec sp_detach_db '[Database_Name]'

해당 스크립트를 사용하여, 데이터베이스를 분리하기에 앞서 몇가 지 선행 조건이 있는데요.

1. 분리하려는 데이터베이스가 시스템 데이터베이스가 아닐 것
2. 데이터베이스에 활성 연결이 없을 것 (배타적 엑세스 권한 획득)

간략하게 정리하자면, 이렇게 두가지로 볼 수 있습니다.

 

시스템 데이터베이스는 우리가 흔하게 알고있는

• master
• model
• msdb
• tempdb

이렇게 4가지를 말하는데요. 자세한 내용은 나중에 기회가되면 다루도록 하겠습니다.

 

그렇다면, 데이터베이스에 활성 연결이 없다는 것은 무엇일까요?

 

데이터베이스가 활성화(OnLine)가 되어있지 않아야 한다는 뜻인데요.

당연한 이야기겠지만, 사용중인 데이터베이스를 그냥 분리 시킬 수는 없을 것입니다.

활성화 중인 데이터베이스

그렇다면 먼저 데이터베이스를 비활성화(OffLine)으로 만들어 줘야할 필요가 있습니다.

alter database [Database_Name] set offline

위 스크립트를 통해 데이터베이스의 상태를 OffLine으로 바꿀 수 있습니다.

위 스크립트를 실행한다면, 데이터베이스가 비활성화 될까요?

 

실행 중으로 멈추어 있는 상태

상황마다 다를 수 있지만, 데이터베이스는 대체로 비활성화가 되지 않을 것입니다.

 

해당 데이터베이스에 다른 세션이 없다면 비활성화가 바로 되겠지만,

다른 세션이 접속해 있다면 진행되지 않고 대기상태가 되는데요.

 

아래의 쿼리로 Lock을 한번 확인해보도록 하겠습니다.

select * 
  from sys.dm_exec_requests

그렇다면 이런 값을 확인 할 수 있습니다.

 

제가 alter databse 쿼리를 날린 57번 세션 ID, 미리 접속을 유지하고있던 52번 세션 ID를 확인 할 수 있는데요.

last_wait_type으로 LCK_M_X 라는 상태 값을 확인할 수 있습니다.

 

LCK_M_X 상태는 "태스크가 배타 잠금을 획득하려고 대기하는 경우" 에 발생합니다.

 

즉, 배타 잠금 권한을 획득해야 대기하지 않고 넘어갈 수 있는데요.

분리하고자하는 데이터베이스를 Single_User 모드로 전환하여, 배타적 엑세스를 하도록 유도합니다.

alter database [Database_Name] set single_user

이렇게 싱글 유저만 접속하도록 변경해주는데요.

 

저희는 혹시 모를 예상치 못한 다른 연결이 접속 권한을 가져가는 것을 방지하고자

포트번호를 먼저 변경하여 다른 접속을 원천 차단하여 작업하였습니다.

single_user

이렇게 싱글 유저로 전환 후 

database offline

데이터베이스 비활성화

분리되어 없어짐

데이터베이스 분리 까지 완료하였습니다.

 

이렇게 데이터베이스를 분리하였는데요.

 

이제 DBMS와 분리된data file과 log file을 원하는 디스크 및 경로로 이동하여,

다음작업인 데이터베이스 연결을 합니다.

 

데이터베이스 연결 (Database Attach)

데이터베이스 연결은 아주 간단한데요.

아래의 스크립트로 진행합니다.

exec sp_attach_db '[Database_Name]', 
                  '[Data File.mdf]',
                  '[Data File.ndf]',
                  '[Log File.ldf]'

데이터베이스 연결

데이터베이스 연결하는 스크립트를 실행합니다.

연결할 데이터베이스 이름과 해당 데이터베이스에 연결될 Data File들과 Log File 을 지정해줍니다.

이렇게 다시 데이터베이스가 연결되며, 자동으로 활성화가 되어있음을 알 수 있습니다.

---

Outro

오늘은 MsSQL Database의 분리와 연결에 대해서 알아보았습니다.

Detach / Attach를 활용하여 작업하였는데요.

 

다 이해하고나면 생각보다 쉽습니다.

물론 실무에 사용하시기 앞서 한번 꼭 테스트하고 진행하시길 바랍니다.

 

감사합니다!