Skip to content

Latest commit

 

History

History
134 lines (72 loc) · 3.8 KB

03.md

File metadata and controls

134 lines (72 loc) · 3.8 KB

Microsoft SQL Server Data를 Amazon S3로 Migration

Architecture diagram

image-20220327001406991

EC2에 설치된 SQL Server의 Data를 "Database Migration Service"를 사용하여 Amazon S3로 Migration하게 됩니다.

EC2 Instance 접속

  1. console 을 새 창(새탭)으로 띄우고, 왼쪽 메뉴의 Instances를 Click

image-20220416210845257

  1. -EC2Instance를 선택하고 Actions => Connect 실행

image-20220416211017522

  1. RDP client 를 Click 하고 Get Password를 Click

image-20220416211115791

  1. Browse를 Click하고 아까 다운 받았던 DMSKeyPair.pem 또는 DMSKeyPair.cer 를 업로드
  2. Decrypt Password 를 Click

image-20220416211350751

  1. 확인된 Windows Password를 메모장등에 적어둡니다.

image-20220416211505721

  1. Remote Desktop(mstsc.exe)을 실행하고 아까 적어둔 SourceEC2PublicDNS에 해당하는 Host로 접속합니다.
User Name : Administrator
Password : 위에서 확인한 Password 사용
  1. 시작 을 누르고 SQL Server Management Studio 을 실행

image-20220416213801074

  1. Connect 를 Click

image-20220416213859801

  1. ConfigSourceSQLServer.sql 를 다운로드 하고, 메모장등으로 열어서 내용을 복사
--Set master database context
use [master]
GO

--Add the awssct login to the sysadmin server role - required for replication
ALTER SERVER ROLE [sysadmin] ADD MEMBER [awssct]
GO

--Set the recovery model to full for dms_sample - required for replication
ALTER DATABASE [dms_sample] SET RECOVERY FULL WITH NO_WAIT
GO

--Configure this SQL Server as its own distributor
exec sp_adddistributor @distributor = @@SERVERNAME, @password = N'Password1'
exec sp_adddistributiondb @database = N'distribution', @data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\DATA', @log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Data', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1
GO

--Change context to the distribution database
use [distribution] 
GO

--Configure replication
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 

if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO

exec sp_adddistpublisher @publisher = @@SERVERNAME, @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'
GO
  1. 복사한 Query를 SQL Server Management Studio 에 입력
  2. Execute 로 Query 실행

image-20220416214138972

image-20220416214221264

<다음>Workshop04-Target S3 Bucket구성으로 이동