SQL backup to Windows Azure Storage Part 3

Windows Azure Blob Storage as database backup

On-Premise 환경에서 Windows Azure Storage(저장소)에 백업하는 방법이 3가지가 있다.

Session1. Backup to URL in SQL Server 2012, 2014

Part 1 : Backup to URL in SQL Server 2012, 2014

Part 1-1 : Backup to URL in SQL Server 2012, 2014 as SSMS

Session2. Backup To Windows Azure Tool

Part 2 : Backup To Windows Azure Tool 로 백업

Part 2-1 : Windows Azure Tool 구성 옵션 암호화/압축 기능

Session3. Managed Backup to Windows Azure

Part 3  : Managed Backup  - 데이터베이스 수준의 관리되는 백업

Part 3-1 : Managed Backup  - SQL 인스턴스 수준의 관리되는 백업

 

Session3. Managed Backup to Windows Azure ( SQL Server 2014 New Feature)

 

SQL Server 2014 의 새로운 기능이며, 자동화된 데이터베이스 백업을 지원하는 기능이며,

기존의 SQL JobAgent(or 유지관리계획)에서 스케즐링한 백업과 차이가 있다.

동작방식 자체가 내부엔진에서 별도로 수행되며, 별도 JOB이 생성되지는 않는다.

 

 

거두절미. 구성하는 방법을 먼저 알아보자,

 

1. 데이터베이스 수준의 관리되는 백업설정

개별 데이터베이스 별로 설정을 할 수도 있고, 서버수준으로 전체 사용자데이터베이스를 자동 설정이 될 수도 있다.

우선순위는 데이터베이스 수준이 우선이 된다.

 

현재 Azure 저장소에는 컨테이너가 2개만 존재한다.

 

 

로컬 SQL Server에서는 MoveDB를 설정하기로 하자.

암호화된 백업은 2014부터 지원하는 기능으로 Azure 저장소에 암호화된 백업을 하기 위해서는 아래와 같이 Master Key(마스터키) 와 Certificate(인증서)를 우선 생성을 한다.

 

-- 백업 암호화(Backup Encryption) 요구 사항

USE master

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'asdfadsfafd1!@#';

GO

CREATE CERTIFICATE EncBakCertificate WITH SUBJECT = 'EncBakCertificate';

GO

 

 

/*

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

암호의 유효성을 검사하지 못했습니다. 암호가 복잡하지 않기 때문에 Windows 정책 요구 사항에 맞지 않습니다.

메시지 15581, 수준 16, 상태 1, 줄 6

이 작업을 수행하기 전에 데이터베이스에서 마스터 키를 만들거나 세션의 마스터 키를 여십시오.

*/

 

-- Azure 백업을 위한 자격증명 생성 (앞 포스트에서 이미 생성하였음)

use master

go

 

CREATE CREDENTIAL MyCredentialName

WITH IDENTITY = 'ghostbackup', --Identity : Windows Azure Storage Account Name

SECRET = 'lqQSslZqvK4NYugWwDZIHk7j3qx6UEVXQnlN5Z2tIFfFHIPX4VzdfanOlmFYf3OLVKcVc26ML0PECFKfPrNJmg=='; --Secret : Storage Account Primary Access Key

 

 

SELECT * FROM sys.credentials where name='MyCredentialName'

 

 

-- 관리되는 백업 설정(데이터베이스 수준)

<sample code>

 

EXEC msdb.smart_admin. sp_set_db_backup

[@database_name = ] 'database name'

,[@enable_backup = ] { 0 | 1}

,[@storage_url = ] 'storage url'

,[@retention_days = ] 'retention_period_in_days'

,[@credential_name = ] 'sql_credential_name'

,[@encryption_algorithm] 'name of the encryption algorithm'

,[@encryptor_type] {'CERTIFICATE' | 'ASYMMETRIC_KEY' }

,[@encryptor_name] 'name of the certificate or asymmetric key'

 

USE MSDB

GO

 

EXEC msdb.smart_admin.sp_set_db_backup

@database_name ='MoveDB'

,@enable_backup = 1

,@retention_days = '30' -- Min 1 and Max 30

,@credential_name = 'MyCredentialName'

,@encryption_algorithm = 'NO_ENCRYPTION' -- 백업암호화를 안할 경우 아래는 할 경우

--,@encryption_algorithm = 'AES_256'

--,@encryptor_type= 'Certificate'

--,@encryptor_name='EncBakCertificate'

 

/*

SQL Server Managed Backup to Windows Azure가 자격 증명 'MyCredentialName',

보존 기간 30일로 데이터베이스 'MoveDB'에 대해 구성되었습니다. 암호화는 off이고 백업은 on입니다.

*/

 

위 설정 후 즉시 백업이 수행되지 않을 수 있다. 기다리다 보면 생성이 된다.

 

 

 

위 구성된 설정을 확인 하기 위해서는 아래와 같이 DMF로 확인이 가능하다.

--- Checking Database Meta data

SELECT * FROM msdb.smart_admin.fn_backup_db_config ('MoveDB')

    WHERE 1=1

    and is_managed_backup_enabled = 1 and is_dropped = 0;

 

SELECT * FROM msdb.smart_admin.fn_backup_db_config (Null)

    WHERE 1=1

    and is_managed_backup_enabled = 1 and is_dropped = 0

 

이러면 자동으로 알아서 백업이 된다.

 

 

 

 

#. 데이터베이스 수준의 백업 암호화 해제하기

-- 개별 데이터베이스 암호화 설정 해제하기

EXEC msdb.smart_admin. sp_set_db_backup

@database_name='MoveDB',

@encryption_algorithm ='NO_ENCRYPTION'

GO

 

 

#. 데이터베이스 수준의 백업 해제하기

-- 개별 데이터베이스 관리되는 백업 해제하기

EXEC msdb.smart_admin. sp_set_db_backup

@database_name='MoveDB',

@enable_backup =0

GO

/*

SQL Server Managed Backup to Windows Azure가 자격 증명 'MyCredentialName', 보존 기간 30일로 데이터베이스 'MoveDB'에 대해 구성되었습니다. 암호화는 off이고 백업은 off입니다.

 

 

*/

 

 

 

그럼 언제 백업이 자동으로 해주는지 궁금할 것이다.

 

아래 조건에 의해서 생성이 된다.

 

 

 

 

 

 

 

 

 

 

전체 백업이 자동 수행되는 조건

• 기능을 활성화 한 타이밍
• 마지막 전체 백업에서 로그가 1GB 이상 증가할 때
• 마지막 전체 백업에서 1 주일 경과할 때
• 로그 체인이 손상된 경우 (트랜잭션 로그가 지워진 경우)

 

 

트랜잭션 로그 백업이 자동 수행되는 조건

• 로그 백업 기록이 없는 경우 (일반적 기능을 활성화했을 때)
• 트랜잭션 로그가 5MB 이상 증가했을 때
• 마지막 트랜잭션 로그 백업에서 2 시간 경과할 때
• 전체 백업이 지연되는 경우

   

 

 

[시나리오]

-- 로그를 5MB 이상 사용하게 만들기

use MoveDB

go

 

sp_helpdb MoveDB

MoveDB_log 6400 KB

 

 

 

IF EXISTS (SELECT * FROM sysobjects WHERE name = 'test')

DROP TABLE test

GO

CREATE TABLE test (

x INT NOT NULL

,x2 INT NOT NULL

,y CHAR(10) NOT NULL DEFAULT ('')

,z CHAR(10) NOT NULL DEFAULT('')

)

 

DECLARE @rows INT = 666666, @toKeep INT = 400000, @diff INT

 

INSERT test (x, x2)

SELECT TOP(@rows)

ROW_NUMBER() OVER (ORDER BY (SELECT 1)) AS r

,ROW_NUMBER() OVER (ORDER BY (SELECT 1)) % 10 AS s

FROM master..spt_values a CROSS JOIN master..spt_values b

GO 5

 

 

sp_helpdb MoveDB

MoveDB_log 223104 KB

 

결과 : 데이터파일 및 로그파일을 겁나게 늘려봤다. (기다려봤지만, 자동 백업이 되지 않아 마구 늘려봤다.)

전체백업 및 로그백업이 자동 수행 되었다. 그러나. 어떤 조건으로 수행 되었는지는 잘 모르겠다.

 

[시나리오.2]

로그 체인이 깨지면 전체백업을 한다.

그래서 마지막 .log 파일을 삭제하고 기다려봤다. 안 되서 마구 삭제해봤다.

결과 : 한 20분 후에 자동 백업이 되었다. 역시 반응속도가 느리다.

 

 

 

다음 포스트에서 이어서 보자.