고가용성을 위한 Windows Azure 데이터베이스 미러링 0312

 

이 내용은 기본적으로 고가용성에 대한 충분한 이해가 있으며, Windows Azure와 SQL서버의 여러 고가용성 기능들에 대해서 역시 이해도가 있는 개발자나 IT전문가임을 고려해 상세한 설명 없이 진행.
미러링에 대해 기본적인 사항이 필요할 경우 향상된 가용성 기능 (미러링) – SQLER 강성욱 제작. SQL서버 미러링 기능 이해에 좋을 듯.

Windows Azure의 끝판왕 - 고가용성 및 부하 분산 기술인 AlwaysOn에 대한 내용은 추후 제공 예정

Tutorial: AlwaysOn Availability Groups in Windows Azure (PowerShell)

 

SQL 서버 고가용성 미러링에 대한 첨언

미러링은 구성 및 운영, 장애 상황에 대한 절차 등을 충분히 미리 검토하고 전체 절차를 숙지해 두어야 한다.

특히, 장애 상황시 역할 전환이나 스냅샷 DB 활용시 데이터 생성 시점과 동기화 등 기본적인 내용을 충분히 고려하길 권장.

Microsoft Technet 데이터베이스 미러링(SQL Server)

 

이 내용에서는 Windows Azure에서 구축 가능한 모니터링서버(Witness Server) 및 인증서를 사용해 각 서버에 접근하는 방식을 다룬다.

SQL서버의 미러링 기능 등에 대해서 궁금한 사항이 있을 경우 SQLER.com의 SQL 질문과 답변 게시판을 이용하면 좋음.

 

미러링 튜토리얼  원본문서 : Tutorial: Database Mirroring for High Availability in Windows Azure

원문이 변경될 수 있으며, 원문에 충실히 따르길 권장.

 

파워쉘은 쉘 스크립트 기반으로 마이크로소프트의 여러 다양한 제품들을 제어하기 위한 기술임.

Windows Azure 도 파워쉘로 빠른 관리 가능하고, 파워쉘에서 Azure를 사용하기 위해서는 파워쉘 cmdlets을 설치해야 한다. SDK 링크에서 설치 가능

Windows Azure PowerShell cmdlets.

command-line tool의 Windows PowerShell – install 을 선택하면 설치 가능

웹 플랫폼 설치 관리자(WPI)가 설치 안되었다면 설치하고, 계속 진행한다.

WPI는 여러 종속성(dependency)가 있는 SDK나 플러그인 등의 다운로드를 자동으로 조절해 주는 편리한 설치 관리자이다.

 

 

 

파워쉘 관련자료 참고링크

SQLER의 Windows Server 게시판 김재훈의 파워쉘 강좌 

 

파워쉘 실행

Windows Azure의 고가용성 - 미러링작업은 절차가 복잡하기 때문에 GUI로 하길 권장하지 않는다.

파워쉘과 SQL쿼리에 이해가 있다면, 아래 내용을 따라해 10분 이내에 미러링 배포도 가능하다.

 

powershell 명령을 실행창(윈도우키+R) 또는 프로그램에서 선택해 실행

그러면, 아래와 유사한 화면이 시작된다.

 

 

 

파워쉘 SDK 가져오기(import) 수행 – Azure 관련 파워쉘 명령을 실행 할 수 있다.

Import-Module "C:\Program Files (x86)\Microsoft SDKs\Windows Azure\PowerShell\Azure\Azure.psd1"

 

Azure 출판설정파일(PublishSetting)은 Windows Azure의 구독 정보가 기록된 파일이다.

파워쉘의 현재 세션이 나의 Windows Azure 구독에 접근해 여러 작업을 가능하도록 하는 인증서라고 보면 된다.

구독 파일 - publishSettings 다운로드 방법은 https://manage.windowsazure.com/publishsettings/index?client=powershell 바로 다운로드 시작 되고 저장 가능하다.

또는 파워쉘에서 아래 명령 수행 하면 브라우저가 열리면서 설정 파일을 바로 다운로드 가능

 Get-AzurePublishSettingsFile

 

다운로드 받은 설정 파일을 먹여 준다.  나의 경우는 위의 이미지처럼, c:\temp 폴더에 저장했고 수행했다.

 

Import-AzurePublishSettingsFile "c:\설정파일저장폴더\설정파일"

 

빠른 Windows Azure 설정을 위해 파워쉘에 변수값 지정

현재는 일본 서부 데이터센터이고, 위치 및 선호도 그룹 이름을 ContosoAG 으로 설정했다. 가용성집합 이름은 SQLHADR 이다.  현재 VM은 SQL2012 Enterprise이고 4코어를 쓰는 Large 인스턴스이며 과금 여부에 주의한다.

이후 VM이 생성된 후 VM에 RDP로 접근 가능한 아이디 정보는 AzureAdmin / Contoso!000 이다. – 꼭 바꿔서 수행한다.

$location = "Japan West" $affinityGroupName = "ContosoAG" $affinityGroupDescription = "Contoso SQL HADR Affinity Group" $affinityGroupLabel = "IaaS BI Affinity Group" $workingDir = "C:\script\" $networkConfigPath = $workingDir + "NetworkConfig.xml" $virtualNetworkName = "ContosoNET" $storageAccountName = "<고유값으로 생성>" $storageAccountLabel = "Contoso SQL HADR Storage Account" $storageAccountContainer = "https://" + $storageAccountName + ".blob.core.windows.net/vhds/" $serviceName = "<고유값으로 생성>" $sqlImageName = (Get-AzureVMImage | where {$_.Label -like "SQL Server 2012 SP1 Enterprise*"} | sort PublishedDate -Descending)[0].ImageName $availabilitySetName = "SQLHADR" $subnetName = "Back" $domainUser = "Administrator" $sql1ServerName = "SQL1" $sql2ServerName = "SQL2" $sql3ServerName = "SQL3" $dataDiskSize = 100 $vmAdminUser = "AzureAdmin" $vmAdminPassword = "Contoso!000"

$storageAccountName 과 $serviceName은 식별 DNS로 사용되니 고유해야 한다. 아울러, DNS명이기 때문에 소문자와 숫자만 사용 가능하다.

참고로, help azure 명령을 수행하면 Azure 파워쉘 명령 리스트 도움말을 볼수 있고, 명령들이 직관적이라 쉽게 파악 가능하다.

 

위치/선호도 그룹(affinity group) 생성

위치/선호도 그룹(affinity group)을 파워쉘에서 수행해 생성한다.

New-AzureAffinityGroup `     -Name $affinityGroupName `     -Location $location `     -Description $affinityGroupDescription `     -Label $affinityGroupLabel

문제 없이 실행되면 이런 결과를 파워쉘에서 볼 수 있다.(이하 생략)

 OperationDescription                    OperationId                             OperationStatus 
--------------------                    -----------                             --------------- 
New-AzureAffinityGroup                  7a01991c-75f5-7fdb-a32b-0f224307ad64    Succeeded

파워쉘에서 Success 라고 안 나오면 뻘건 오류 화면이 나온다. 오류를 읽어보면 대부분 쉽게 스크립트 문제를 해결 가능하다.

 

구성 파일을 가져와 virtual network 생성

c:\script 폴더 내에 NetworkConfig.xml 파일로 아래 파일이 위치 해야함. 아래 XML 파일을 저장하고 위에서 설정한 c:\script\ 폴더에 둔다.

<NetworkConfiguration xmlns:xsi="https://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="https://www.w3.org/2001/XMLSchema" xmlns="https://schemas.microsoft.com/ServiceHosting/2011/07/NetworkConfiguration">   <VirtualNetworkConfiguration>     <Dns />     <VirtualNetworkSites>       <VirtualNetworkSite name="ContosoNET" AffinityGroup="ContosoAG">         <AddressSpace>           <AddressPrefix>10.10.0.0/16</AddressPrefix>         </AddressSpace>         <Subnets>           <Subnet name="Front">             <AddressPrefix>10.10.1.0/24</AddressPrefix>           </Subnet>           <Subnet name="Back">             <AddressPrefix>10.10.2.0/24</AddressPrefix>           </Subnet>         </Subnets>       </VirtualNetworkSite>     </VirtualNetworkSites>   </VirtualNetworkConfiguration> </NetworkConfiguration>

 

파워쉘 명령으로 설정을 적용한다.

Set-AzureVNetConfig `     -ConfigurationPath $networkConfigPath

저장소(Storage) 생성

파워쉘 명령으로 저장소를 생성한다.

Set-AzureSubscription `     -SubscriptionName (Get-AzureSubscription).SubscriptionName `     -CurrentStorageAccount $storageAccountName

 

이어서, 저장소를 구독(Subscription)에 연결한다.,

Set-AzureSubscription `     -SubscriptionName (Get-AzureSubscription).SubscriptionName `     -CurrentStorageAccount $storageAccountName

 

VM 생성 진행 - 첫 VM인 SQL1을 생성

# Create SQL1... New-AzureVMConfig `     -Name $sql1ServerName `     -InstanceSize Large `     -ImageName $sqlImageName `     -MediaLocation "$storageAccountContainer$sql1ServerName.vhd" `     -AvailabilitySetName $availabilitySetName `     -HostCaching "ReadOnly" `     -DiskLabel "OS" |     Add-AzureProvisioningConfig `         -Windows `         -DisableAutomaticUpdates `         -AdminUserName $vmAdminUser `         -Password $vmAdminPassword |         Set-AzureSubnet `             -SubnetNames $subnetName |             Add-AzureEndpoint `                 -Name "SQL" `                 -Protocol "tcp" `                 -PublicPort 1 `                 -LocalPort 1433 |                 New-AzureVM `                     -ServiceName $serviceName `                     –AffinityGroup $affinityGroupName `                     -VNetName $virtualNetworkName

 

VM2 생성

# Create SQL2... New-AzureVMConfig `     -Name $sql2ServerName `     -InstanceSize Large `     -ImageName $sqlImageName `     -MediaLocation "$storageAccountContainer$sql2ServerName.vhd" `     -AvailabilitySetName $availabilitySetName `     -HostCaching "ReadOnly" `     -DiskLabel "OS" |     Add-AzureProvisioningConfig `         -Windows `         -DisableAutomaticUpdates `         -AdminUserName $vmAdminUser `         -Password $vmAdminPassword |         Set-AzureSubnet `             -SubnetNames $subnetName |             Add-AzureEndpoint `                 -Name "SQL" `                 -Protocol "tcp" `                 -PublicPort 2 `                 -LocalPort 1433 |                 New-AzureVM `                     -ServiceName $serviceName

VM3 생성

# Create SQL3... New-AzureVMConfig `     -Name $sql3ServerName `     -InstanceSize Large `     -ImageName $sqlImageName `     -MediaLocation "$storageAccountContainer$sql3ServerName.vhd" `     -AvailabilitySetName $availabilitySetName `     -HostCaching "ReadOnly" `     -DiskLabel "OS" |     Add-AzureProvisioningConfig `         -Windows `         -DisableAutomaticUpdates `         -AdminUserName $vmAdminUser `         -Password $vmAdminPassword |         Set-AzureSubnet `             -SubnetNames $subnetName |             New-AzureVM `                 -ServiceName $serviceName

VM이 모두 올라오는 것을 대기하고 이후 작업을 진행한다.

 

RDP 파일 다운로드

Foreach ($VM in $VMs = Get-AzureVM -ServiceName $serviceName) {     write-host "Waiting for " $VM.Name "..."     # loop until the VM status is "ReadyRole"     While ($VM.InstanceStatus -ne "ReadyRole")     {         write-host "  Current Status = " $VM.InstanceStatus         Start-Sleep -Seconds 15         $VM = Get-AzureVM -ServiceName $VM.ServiceName -Name $VM.InstanceName     }     write-host "  Current Status = " $VM.InstanceStatus     # Download remote desktop file     Get-AzureRemoteDesktopFile -ServiceName $VM.ServiceName -Name $VM.InstanceName -LocalPath "$workingDir$($VM.InstanceName).rdp" }

위 스크립트는 15초에 한번씩 서버별로 VM이 올라온지 확인해 올라 왔으면 RDP 파일을 편한 원격 관리 목적으로 다운로드 하는 스크립트다.

위에서 구성한 기본 작업 폴더(c:\script)에 보면 rdp 파일을 확인 가능하다.

 

RDP를 이용해 3대의 SQL서버에 각각 접근하고 파워쉘을 실행

다운로드 받은 RDP를 실행해 각 서버에 AzureAdmin and password Contoso!000 계정으로 로그인한다.(ID 암호를 위에서 변경했다면 다르게 로그인을 해야 한다.)

 

 

3대의 서버 모두에서 파워쉘을 “관리자 모드”로 실행하고, 아래 명령을 수행해 SQL서버에 원격 접속이 가능하도록 방화벽을 열어 준다.

 

netsh advfirewall firewall add rule name='SQL Server (TCP-In)' program='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Binn\sqlservr.exe' dir=in action=allow protocol=TCP

 

 

인증서를 사용하는 데이터베이스 미러링 구성 시작

SQL1에서 미러링에 이용할 서버 인증서를 생성하고 백업한다. 참고로, SQLCMD는 SQL명령을 명령프롬프트에서 실행하도록 하는 쿼리 실행기이다.

인증서 암호는 원하는 형태로 지정 가능하다. 실행은 마찬가지로, 파워쉘 창에서 실행하면 된다

SQLCMD -S SQL1 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#1>'; GO CREATE CERTIFICATE SQL1_cert WITH SUBJECT = 'SQL1 certificate'; GO CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL1_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL); GO BACKUP CERTIFICATE SQL1_cert TO FILE = 'SQL1_cert.cer'; GO

 

SQL2에서도 미러링에 이용할 서버 인증서를 생성하고 백업한다.

SQLCMD -S SQL2 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#2>'; GO CREATE CERTIFICATE SQL2_cert WITH SUBJECT = 'SQL2 certificate for database mirroring'; GO CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL2_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL); GO BACKUP CERTIFICATE SQL2_cert TO FILE = 'SQL2_cert.cer'; GO

SQL3에서도 미러링에 이용할 서버 인증서를 생성하고 백업한다.

SQLCMD -S SQL3 USE master; CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<Strong_Password_#3>'; GO CREATE CERTIFICATE SQL3_cert WITH SUBJECT = 'SQL3 certificate for database mirroring'; GO CREATE ENDPOINT Endpoint_Mirroring    STATE = STARTED    AS TCP (LISTENER_PORT=5022, LISTENER_IP = ALL)    FOR DATABASE_MIRRORING (AUTHENTICATION = CERTIFICATE SQL3_cert, ENCRYPTION = REQUIRED ALGORITHM AES, ROLE = ALL); GO BACKUP CERTIFICATE SQL3_cert TO FILE = 'SQL3_cert.cer'; GO

 

위에서 생성한 인증서는 모두 각 서버의 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA 폴더에 있다.

각 서버가 나머지 2개의 서버 인증서를 모두 가지고 있도록 인증서를 복사한다. 예를 들면, SQL1은 SQL2와 SQL3의 인증서를 가지도록 복사한다. SQL2와 SQL3도 마찬가지.

 

SQL1에서 SQL2와 SQL3의 인증서로 로그인을 허용하는 퍼미션을 구성한다.

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000'; GO CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin; GO CREATE CERTIFICATE SQL2_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL2_cert.cer' GO CREATE CERTIFICATE SQL3_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL3_cert.cer' GO GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin]; GO

SQL2에서 SQL1와 SQL3의 인증서로 로그인을 허용하는 퍼미션을 구성한다.

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000'; GO CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin; GO CREATE CERTIFICATE SQL1_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL1_cert.cer' GO CREATE CERTIFICATE SQL3_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL3_cert.cer' GO GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin]; GO

 

SQL3에서 SQL1와 SQL2의 인증서로 로그인을 허용하는 퍼미션을 구성한다.

CREATE LOGIN DBMirroringLogin WITH PASSWORD = 'Contoso!000'; GO CREATE USER DBMirroringLogin FOR LOGIN DBMirroringLogin; GO CREATE CERTIFICATE SQL1_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL1_cert.cer' GO CREATE CERTIFICATE SQL2_cert AUTHORIZATION DBMirroringLogin FROM FILE = 'SQL2_cert.cer' GO GRANT CONNECT ON ENDPOINT::Endpoint_Mirroring TO [DBMirroringLogin]; GO

작업을 수행할 DB는 MyDB1 이다. DB를 생성하고, SQL1에서 풀 백업과 로그 백업을 수행한다.

CREATE database MyDB1 GO BACKUP DATABASE MyDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.bak'; GO BACKUP LOG MyDB1 TO DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.log'; GO

생성된 백업 파일들(풀백업 - BAK 파일과 로그 백업 - LOG 파일)을 SQL2의

C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP

폴더로 복사한다.

 

SQL2에서 미러링으로 사용 되도록 WITH NORECOVERY 모드로 복구하고, SQL1을 미러링 파트너로 구성한다.

RESTORE DATABASE MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.bak' WITH NORECOVERY; GO RESTORE LOG MyDB1 FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\BACKUP\MyDB1.log' WITH NORECOVERY; GO ALTER DATABASE MyDB1 SET PARTNER = 'TCP://SQL1:5022'; GO

 

SQL1에서 SQL2를 미러링 파트너로, SQL3을 모니터링(Witness)  서버로 구성한다.

ALTER DATABASE MyDB1 SET PARTNER = 'TCP://SQL2:5022'; GO ALTER DATABASE MyDB1 SET WITNESS = 'TCP://SQL3:5022'; GO

자 이렇게 해서 미러링 구성은 모두 끝났다. 이제 동작 여부를 테스트 한다.

 

미러링 테스트 수행

--SQL1에서 수행한다. 테이블을 만들고 데이터를 삽입/조회한다. use MyDB1 go create table sqler01(     sqlerNumber int ,    mystring varchar(10) ) go insert into sqler01(sqlerNumber, mystring) values(1, 'SQLER') insert into sqler01(sqlerNumber, mystring) values(2, 'Mirror') go select * from sqler01 go

 

주서버(Primary)인 SQL1에서 처리하고 이제 미러링 서버인 SQL2에서 아래 내용을 수행해 미러링을 확인한다.

--SQL2에서 수행 use MyDB1 go --미러링, Recovery 상태라 접근 불가 select * from sqler01 go --읽기 전용 스냅샷 DB를 생성한다. DB명은 snapshot_MyDB1 -- 이 스냅샷 DB는 Recovery 상태인 DB의 상태를 새 DB로 복사하는것이며, 이 DB를 수정해도 SQL1이나 다른 곳으로 데이터가 동기화 되지 않는 DB이다. 추후 AlwaysOn에서 상세히 다룬다.create database snapshot_MyDB1 on (name = MyDB1, filename='C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\snapshot_MyDB1.mdf') as snapshot of MyDB1 go -- 복사한 스냅샷 DB에서 데이터 조회 use snapshot_MyDB1 go select * from sqler01 go

 

이제 장애 상황을 가정해 SQL1을 중지시킨다.

--SQL1 에서 SQL서버 중지 명령 실행 shutdown

 

SQL1의 이벤트 로그를 확인해 보면 서버가 중지된 것을 확인 가능하고, 모니터링 서버인 SQL3에 이벤트가 바로 발생한 것을 볼수 있다.

1초 이내에 Failover되어 SQL2가 Recovery 모드에서 Principle DB로 역할이 변경되고 서비스가 지속된다.

 

참고자료

향상된 가용성 기능 (미러링) – SQLER 강성욱 제작. SQL서버 미러링 기능 이해에 좋다.

Tutorial: Database Mirroring for High Availability in Windows Azure

SQLER의 Windows Server 게시판 김재훈의 파워쉘 강좌 

Tutorial: AlwaysOn Availability Groups in Windows Azure (PowerShell)

 SQLER.com의 SQL 질문과 답변 게시판을