SQL Server 에서 AWS S3에 직접 백업하기
l Version : SQL Server 2022
SQL Server 2022부터는 데이터 플랫폼에 통합 오브젝트 스토리지를 도입하여 Azure Storage외에도 AWS S3 호환 오브젝트 스토리지도 사용할 수 있다. 즉, SQL Server에서 AWS S3로 직접 백업을 할 수 있게 되었다.
기존 SQL Server 2019의 경우 Microsoft Azure 저장소만 가능 하였다. SQL Server 2022 부터 도입된 S3 REST API는 SQL Server에서 AWS S3 직접 백업, 복원을 할 수 있어, 백업 후 이동에 따른 프로세스 단축 및 대용량 백업을 진행할 경우에도 로컬 공간이 부족하여도 백업을 진행할 수 있게 되었다.
지원되는 백업 종류
l 전체 백업
l 차등 백업
l 트랜잭션 로그 백업
l 복사 전용 백업 (COPY ONLY)
지원되지 않는 백업 종류
l 스냅샷 백업 (FILE_SNAPSHOT)
l 미러 백업 (MIRROR)
AWS S3버킷으로 백업 및 복원을 수행 하기위해서는 아래 정보가 필요 하다.
l S3 버킷이름
l S3 URI
l S3 URL
l IAM 액세스 ID
l IAM 액세스 키
AWS IAM(Idnetity Access Management)의 정책은 사용자의 권한 또는 특정 서비스에 대해 수행할 수 있는 역할을 정의한다. SQL Server 2022에서 S3 버킷으로 직접 데이터베이스 백업을 수행하려면 ListBucket 및 PutObject 권한이 필요하다.
l ListBucket : ListBucket 액세스는 요청의 인증된 발신자가 소유한 모든 버킷 목록을 반환한다.
l PutObject : PutObject 액세스를 통해 버킷에 객체를 작성하고 추가할 수 있다.
SQL Server에서 AWS S3로 백업을 하기 위해 우선 자격증명을 만들어야 한다. 아래는 자격증명을 만드는 스크립트이다.
| CREATE CREDENTIAL [s3://<endpoint>:<port>/<bucket>] WITH IDENTITY = 'S3 Access Key', SECRET = '<AccessKeyID>:<SecretKeyID>'; |
l [s3://<endpoint>:<port>/<bucket>] : 자격 증명 이름에는 S3 버킷 이름이 포함되어야 한다. S3://로 시작하고 접두사 HTTPS://를 제거한 후 S3 버킷 URL을 따른다.
l IDENTITY : S3 Access Key 값으로, 자격 증명에서 S3 커넥터를 사용하고 있음을 나타낸다.
l SECRET : 접근키 ID와 비밀 키를 콜론으로 구분하여 입력한다. 콜론 기호는 구분자 역할을 한다.
백업 명령은 기존에 사용하던 백업문과 거의 동일하다. 다만 백업 위치가 DISK 가 아닌 URL을 사용한다.
| BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak' |
백업 완료 후 S3를 확인해보면 백업파일이 생성된 것을 확인할 수 있다.
아래 스크립트는 백업하려는 데이터베이스를 여러 스트라이프된 백업 파일로 오브젝트 스토리지 엔드포인트를 사용하여 백업을 수행한다.
| BACKUP DATABASE <db_name> TO URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak' , URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak' , URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak' -- , URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak' WITH FORMAT -- overwrite , STATS = 10 , COMPRESSION; |
아래 스크립트는 오브젝트 스토리지 엔드포인트 위치에서 데이터베이스 복원을 수행한다.
| RESTORE DATABASE <db_name> FROM URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak' , URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak' , URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak' -- , URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak' WITH REPLACE -- overwrite , STATS = 10; |
데이터를 저장하려면 S3 호환 오브젝트 스토리지 공급자는 파트(Part)라 불리는 여러 블록으로 파일을 분할해야 한다. 각 파일은 최대 10,000개의 파트로 분할 수 있으며, 각 파트의 크기는 5MB ~ 20MB이다. 이 범위는 매개변수 MAXTRANSFERSIZE를 통해 T-SQL BACKUP 명령으로 제어할 수 있다. MAXTRANSFERSIZE의 기본값은 10MB 이므로 각 파트의 기본 크기는 10MB이다. 즉, 단일 백업 파일의 크기는 MAXTRANSFERSIZE가 기본값으로 설정되어 있을 경우 100,000MB이다. 백업 스트라이프가 100,000MB를 초과할 경우 아래와 같은 오류 메시지를 반환한다.
| Msg 3202, Level 16, State 1, Line 161 Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.) Msg 3013, Level 16, State 1, Line 161 BACKUP DATABASE is terminating abnormally. |
단일 백업 파일의 크기가 100,000MB 보다 큰 파일을 백업 해야 할 경우 최대 64개의 URL 스트라이프 백업을 지원한다. 즉, 가능한 최대 백업 파일 크기는 10,000part * MAXTRANSFERSIZE * URL이다. 압축 백업을 사용할 경우 백업 파일 사이즈가 현저히 많이 줄어들기 때문에 백업 시 압축 옵션을 적극 활용할 수 있도록 한다.
아래 예제는 MAXTRANSFERSIZE를 사용하여 20MB으로 설정하고, 백업 시 압축 및 암호화한다.
| CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>; GO CREATE CERTIFICATE AdventureWorks2019Cert WITH SUBJECT = 'AdventureWorks2019 Backup Certificate'; GO -- Backup database BACKUP DATABASE AdventureWorks2019 TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2019_Encrypt.bak' WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION, ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2019Cert) GO |
백업 및 복원 엔진에서 총 URL의 길이는 259바이트로 제한되어 있기 때문에 s3://를 제외하고 사용자의 경로 길이(호스트 이름 + 개체키)를 259 - 5 = 254글자까지 입력할 수 있다. 만약 URL 길이를 초과할 경우 아래와 같은 오류가 반환된다.
| SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together' |
S3 스토리지는 SQL 호스트와 S3 서버간의 시간 차이가 15분을 초과할 때 마다 연결을 거부하고 “InvalidSignatureException”오류를 SQL Server로 보낸다. SQL Server에서는 아래와 같은 오류로 반환된다.
| Msg 3201, Level 16, State 1, Line 28 Cannot open backup device '<path>'. Operating system error 5(Access is denied.). Msg 3013, Level 16, State 1, Line 28 BACKUP DATABASE is terminating abnormally. |
[참고자료]
l SQL Server backup to URL for S3-compatible object storage : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16
l SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting : https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage-best-practices-and-troubleshooting?view=sql-server-ver16
l https://www.mssqltips.com/sqlservertip/7301/sql-server-2022-backup-restore-aws-s3-storage/
l https://www.mssqltips.com/sqlservertip/7302/backup-sql-server-2022-database-aws-s3-storage/
2022-10-28/ Sungwook Kang / http://sungwookkang.com
SQL Server, MS SQL, AWS S3 backup, S3 백업, 클라우드 백업, cloud backup
'SQL Server > SQL Server Tip' 카테고리의 다른 글
| SQL Server Failover Cluster 구성 (0) | 2023.07.16 |
|---|---|
| SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기 (1) | 2022.10.31 |
| VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0) | 2020.03.04 |
| SQL Server 복원 성능 최적화 (0) | 2020.02.29 |
| SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0) | 2020.02.28 |