안녕하세요. SQLER의 코난 김대우입니다. 
이번 강좌에서는, 4-2. 데이터베이스 생성을 진행 하겠습니다.

SQLER에서 진행되는, 챗GPT와 함께 배우는 SQL Server 강좌 목록

이번에 진행할 강좌는 데이터베이스 생성입니다.

TL;DR

SQL Server 데이터베이스는 기본 파일 확장자 MDF, LDF로 구성됩니다. 파일 그룹을 이용해 데이터 저장 장소를 세분화시켜 구성 가능하고, 자동증가 등 여러 옵션을 설정할 수 있습니다.

이전 강좌에서 SSMS로 GUI로 이미 만든 거 아닌가요?

SSMS와 SQL 쿼리를 이용해 아주 간단한 데이터베이스를 생성했습니다. 하지만 데이터베이스 생성은 생각보다 더 복잡하고, 고려할 사항이 많습니다.

데이터베이스 파일

SQL Server 데이터베이스는 기본적으로 SQL서버가 설치된 경로의 Data 폴더에 생성됩니다. 

기본 설치 경로는 C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA 입니다.

지난 강좌에서 기본 설정으로 만들었던 sqler_test 데이터베이스는 이렇게 sqler_test.mdf와 sqler_test_log.ldf 파일로 구성되어 있습니다. 파일 확장자를 주의해서 확인해 주세요. mdf 확장자와 ldf 확장자가 보입니다.

SQL Server 데이터베이스 파일 확장자

SQL Server 데이터베이스는 3개의 파일 확장자를 사용합니다.

MDF
MDF 주 데이터 파일(Primary data file)은 모든 데이터베이스가 반드시 가지는 파일입니다.

NDF
NDF 파일은 보조 데이터 파일(Secondary data file)로 파일그룹(File group)으로 데이터베이스를 생성할 때 사용됩니다.

LDF
LDF 파일은 로그 데이터 파일(Log data file)이며 데이터 변경이 기록되는 파일입니다. 

데이터베이스 파일과 파일그룹

데이터베이스의 데이터 파일 - MDF와 NDF 파일에 대해 조금 더 알아보겠습니다.

데이터베이스 파일은 반드시 주 파일 그룹(Primary file group)이 존재해야 하고, 주 파일 그룹은 반드시 MDF 파일을 하나 포함 해야 합니다. 아울러 LDF 파일도 반드시 존재해야 합니다. NDF 파일은 보조 데이터 파일이기 때문에 반드시 존재할 필요는 없습니다.

"지난 강좌에서 만들 때에는 이런 복잡한 거 없이 확인만 하니까 만들어지던데요?"

기본 설정으로 만들어졌기 때문입니다. 하지만, 실제 운영환경을 구성하게 되면 이렇게 파일 그룹을 활용해 데이터베이스를 구성하게 됩니다. 다음으로 파일 그룹에 대한 설명입니다.

파일 그룹

파일 그룹은 주 데이터 파일 그룹과 보조 데이터 파일 그룹으로 구성됩니다.

파일 자동증가

SQL Server에서 데이터베이스 파일의 자동 증가를 설정할 수 있습니다. 
만약 데이터베이스의 최대 크기를 최초 10M로 설정했는데 금방 데이터가 쌓여서 10M를 넘으면 어떻게 될까요? 더 이상 데이터가 추가 안되고 멈추게 됩니다. 이럴 경우를 위해 자동으로 데이터베이스 파일의 크기를 증가하는 옵션이 제공됩니다. 잠시 후에 쿼리로 설명드리겠습니다.

SQL 구문으로 데이터베이스 생성

그럼 가장 중요한 SQL 구문으로 데이터베이스를 생성하겠습니다. SQL 구문으로 항상 SELECT 쿼리나 이런 데이터베이스 생성도 연습하면 이후 코드만 가지고 있으면 재활용이 가능합니다. 항상 UI를 이용하는 방법보다 이렇게 SQL 쿼리 코드로 수행하는 방법을 잘 숙지해 두시면 큰 도움이 됩니다.

데이터베이스 생성 구문 패턴

역시나 샘플 쿼리로 진행하겠습니다. 저와 같이 AdventureWorks 데이터베이스를 생성하셨다면 C:\sql_data 폴더가 있을 겁니다. 만약에 없다면 생성하고 다음 SQL 구문을 진행하세요.

USE master;
GO

-- Sales 예제 데이터베이스 생성
CREATE DATABASE Sales ON
(NAME = Sales_dat,
    FILENAME = 'C:\sql_data\saledat.mdf',
    SIZE = 10,
    MAXSIZE = 50,
    FILEGROWTH = 5)
LOG ON
(NAME = Sales_log,
    FILENAME = 'C:\sql_data\salelog.ldf',
    SIZE = 5 MB,
    MAXSIZE = 25 MB,
    FILEGROWTH = 5 MB);
GO

SQL 쿼리를 살펴보겠습니다. 최소한의 Primary 파일 그룹과 MDF, LDF 파일만 사용해 구성했습니다. SIZE로 기본 10M 데이터 파일 크기를 지정했고, 5M 단위로 파일 크기를 증가시켜 최대 50M까지 증가시키는 설정으로 데이터베이스 파일을 생성합니다.
SSMS에서 데이터베이스를 “새로고침” 하시고 확인하면 이런 정보를 볼 수 있습니다.

추가적으로 아래 SQL 쿼리를 이용해 데이터베이스 정보를 볼 수도 있습니다.

-- 데이터베이스 목록과 정보 표시
EXEC sp_databases;
GO

-- 데이터베이스 정보 리스트 - 시스템 카탈로그
SELECT * FROM sys.databases WHERE name like 'Sales';
GO

이번에는 파일그룹을 이용해 데이터베이스를 생성하는 예제를 수행합니다.

-- MyDB 데이터베이스 생성
CREATE DATABASE MyDB
ON PRIMARY  -- 주 파일 그룹
  ( NAME='MyDB_Primary',
    FILENAME=
       'c:\sql_data\MyDB_Prm.mdf',
    SIZE=4MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
FILEGROUP MyDB_FG1  -- 보조 파일 그룹 MyDB_FG1
  ( NAME = 'MyDB_FG1_Dat1',
    FILENAME =
       'c:\sql_data\MyDB_FG1_1.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB),
  ( NAME = 'MyDB_FG1_Dat2',
    FILENAME =
       'c:\sql_data\MyDB_FG1_2.ndf',
    SIZE = 1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB)
LOG ON
  ( NAME='MyDB_log',  -- 로그파일 설정
    FILENAME =
       'c:\sql_data\MyDB.ldf',
    SIZE=1MB,
    MAXSIZE=10MB,
    FILEGROWTH=1MB);
GO

-- 기본 파일 그룹을 MyDB_FG1으로 변경.
ALTER DATABASE MyDB 
  MODIFY FILEGROUP MyDB_FG1 DEFAULT;
GO

USE MyDB;
GO
-- MyDB_FG1 파일 그룹에 테이블을 생성
CREATE TABLE MyTable
  ( cola int PRIMARY KEY,
    colb char(8) )
ON MyDB_FG1;
GO

-- 테이블을 생성할 때 파일 그룹을 지정하지 않으면, 기본 파일 그룹인 MyDB_FG1에 테이블을 생성
CREATE TABLE MyTable2
  ( cola int PRIMARY KEY,
    colb char(8) );
GO

이렇게 파일 그룹을 이용하면 원하는 위치에 데이터 파일을 위치시킬 수 있습니다. 물리적으로 다른 디스크를 이용해 테이블이나 인덱스 데이터 입출력 속도를 높일 수 있습니다.

sp_helpdb를 이용하면, 데이터베이스 파일 그룹과 파일 정보를 볼 수 있습니다.

sp_helpdb 'MyDB';

(옵션) 테이블이 위치한 파일그룹을 확이고 싶다면 아래 쿼리를 수행하세요.

USE MyDB;
GO

-- 참조링크: https://learn.microsoft.com/en-us/answers/questions/497385/tables-in-a-particular-sql-server-filegroup

SELECT distinct o.[name], o.[type], f.[name]
FROM sys.indexes i
  INNER JOIN sys.filegroups f
    ON i.data_space_id = f.data_space_id
  INNER JOIN sys.all_objects o
    ON i.[object_id] = o.[object_id] 
      AND o.type = 'U'; -- User Created Tables
GO

파일 그룹의 장점 - 백업

여러 성능적인 장점 외에 백업에서도 파일 그룹이 유용합니다. 백업을 파일 그룹 단위로 받을 수 있어서 SQL Server 실행 중에 부하를 줄이면서 백업할 수 있습니다.

MDF와 LDF만 남았을 경우 데이터베이스 연결(Database Attach) 복원

가장 많이 받는 질문이기도 합니다. 시스템을 이전하거나, SQL Server를 종료하고 잠시 데이터 파일과 로그 파일을 복사해 두었다가 다시 연결하고 싶을 경우 등입니다.
다음 SQL 구문으로 데이터베이스를 분리하고 연결할 수 있으며, 몇 가지 주의사항도 말씀드립니다.

데이터베이스 분리 - detach

USE master;
GO

EXEC sp_detach_db 'sqler_test', 'true';
GO

이럴 경우에는 현재 해당 데이터베이스를 사용 중인 사용자 세션을 kill 하고 detach 합니다.

use master;
GO

-- 어느 프로세스가 해당 DB를 사용 중인지 확인하고 kill
EXEC sp_who;
GO

kill SPID번호;
GO

운영이나 중요 개발 시스템이라면, 해당 SPID가 중요 작업 중인지 미리 체크하고 kill 하세요.

아래 SQL 구문은 데이터베이스의 모든 사용자를 일괄적으로 KILL 하는 쿼리입니다.

-- 데이터베이스의 세션을 모두 자동 kill 하는 SQL 쿼리
-- 수행 전에 중요 작업 프로세스가 있는지 미리 체크하고 실행
USE master;
GO

DECLARE @kill varchar(max) = '';
SELECT @kill = @kill + 'KILL ' + CONVERT(varchar(10), spid) + '; '
FROM master..sysprocesses 
WHERE spid > 50 AND dbid = DB_ID('Your_DB_Name');  -- 데이터베이스 이름으로 변경
EXEC(@kill);
GO

-- 실행하면 SINGLE_USER 설정. 이후 detach 수행.
ALTER DATABASE Your_DB_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

--detach 수행
EXEC sp_detach_db 'Your_DB_Name', 'true';
GO

데이터베이스 연결 - attach

같은 SQL Server에서 분리한 다음, 다시 연결(attach)할 경우에는 후처리 작업이 없습니다.
Attach 쿼리 템플릿

CREATE DATABASE 데이터베이스이름  
    ON (FILENAME = 'mdf파일경로'),   
    (FILENAME = 'ldf파일경로')   
    FOR ATTACH;
GO
-- 기본 multi user mode로 연결됨.

-- 만약 Single user mode를 일반 multi user로 재활성화하려면 아래 쿼리 수행
ALTER DATABASE [<Your_DB_Name>] SET MULTI_USER WITH NO_WAIT;

--sp_attach_db는 차기 버전에서 제거될 예정이니, 위의 CREATE DATABASE - ATTACH를 사용하세요.

실제 Attach 쿼리

CREATE DATABASE sqler_test 
    ON (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sqler_test.mdf'),   
    (FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\DATA\sqler_test_log.ldf')   
    FOR ATTACH;
GO
-- 기본 multi user mode로 연결됨.

-- 만약 Single user mode를 일반 multi user로 재활성화하려면 아래 쿼리 수행
ALTER DATABASE sqler_test SET MULTI_USER WITH NO_WAIT;

만약, 다른 SQL Server에서 MDF와 LDF 파일을 연결(attach)할 경우에는, cross db ownership chaining 처리가 필요합니다.
- 데이터베이스 분리 및 연결(SQL Server)

- db 간 소유권 체인(서버 구성 옵션) - SQL Server

Cross db ownership chaining(소유권 체인) 처리

다른 시스템에서 MDF와 LDF 파일을 복사해 연결(attach)할 경우, 연결하는 데이터베이스 사용자는 새로운 SQL Server에 존재하지 않습니다. SQL Server 로그인, 역할, 사용자의 데이터베이스 개체 소유권(ownership) 일부 정보는 이전 SQL Server의 master 데이터베이스에 존재하기 때문입니다. 대부분의 경우 mdf와 ldf만 복사했기 때문에 새로운 SQL Server에 연결할 경우 ownership 연결 고리가 끊어지는 문제가 발생합니다. 이 경우 위의 가이드대로 수행합니다.

여러 데이터 파일(NDF)로 구성된 파일 그룹 데이터베이스를 연결(attach) 할 경우

같은 SQL 서버에서는 위의 CREATE DATABASE - ATTACH를 이용해 mdf와 ldf만 붙이면 자동으로 ndf와 파일 그룹들이 데이터베이스에 연결됩니다.

다른 SQL Server에서는 파일이 위치한 디스크 등의 구성 정보가 다를 수 있습니다. 만약 다른 디스크 등에 위치한 NDF 파일을 연결할 경우 이렇게 ALTER DATABASE 구문을 사용할 수도 있습니다.
- Move LDF, MDF or NDF file in SQL Server 2016 using Alter Database

 
여러개의 NDF가 있는 경우, attach 하는 방법보다 전체 백업(Full backup)을 받고 RESTORE DATABASE를 이용하시길 권장합니다. 시스템 데이터베이스도 함께 백업하고 같이 복구하는 방법입니다. 
이 방법은 이후 백업과 복구 강좌에서 진행하겠습니다.
 

SQL 강좌 책 구매

강좌가 도움이 되셨다면, 책으로 구매 가능합니다. 책 판매 수익금은 전액 코딩 교육 사회공헌 활동에 기부되며, 아래 링크에서 구매하시면 더 많은 금액이 기부됩니다. 

책구매 링크: 챗GPT와 함께하는 마이크로소프트 SQL Server 2022