AWS Database Migration Service (AWS DMS) ã¯ãããŒã¿ããŒã¹ãšåæã¯ãŒã¯ããŒãã AWS ã«è¿
éãã€å®å
šã«ç§»è¡ããããã®ãããŒãžãåã®ãã€ã°ã¬ãŒã·ã§ã³ããã³ã¬ããªã±ãŒã·ã§ã³ãµãŒãã¹ã§ããç§»è¡äžããœãŒã¹ããŒã¿ããŒã¹ã¯å®å
šã«çšŒåãç¶ãããããããŒã¿ããŒã¹ã«äŸåããã¢ããªã±ãŒã·ã§ã³ã®ããŠã³ã¿ã€ã ãæå°éã«æããããšãã§ããŸããAWS DMS ã¯ãæãåºã䜿çšãããŠããåçšããã³ãªãŒãã³ãœãŒã¹ã® ãœãŒã¹ããŒã¿ããŒã¹ ãš ã¿ãŒã²ããããŒã¿ããŒã¹ ã®éã§ããŒã¿ãç§»è¡ã§ããŸãã SQL Server 㯠Microsoft ãéçºãã ãªã¬ãŒã·ã§ãã«ããŒã¿ããŒã¹ ã§ãã Amazon Relational Database Service (Amazon RDS) for SQL Server ã䜿çšãããšã ã¯ã©ãŠã äžã§ SQL Server ã®å±éãç°¡åã«èšå®ãéçšãã¹ã±ãŒãªã³ã°ã§ããŸããAmazon RDS ã¯ããŒã¿ ã¬ããªã±ãŒã·ã§ã³ããµããŒãããŠããã 倿ŽããŒã¿ãã£ãã㣠(CDC) ãæå¹ã«ããããšããAWS DMS ãš Amazon RDS for SQL Server ã䜿çšããããã® åææ¡ä»¶ ã® 1 ã€ãšãªã£ãŠããŸããCDC ã¯ããŒãã«ã®ããŒã¿ã«å ãããã倿Žããã£ããã£ããŸããããããã®å€æŽã«é¢ããã¡ã¿ããŒã¿ãæ ŒçŽããåŸã§åç
§ã§ããŸãã ãã®æçš¿ã§ã¯ãCDC ãã©ã¡ãŒã¿ã«ã€ããŠæ·±ãæãäžããAWS DMS ã®èšå®æã®åœ±é¿ã«ã€ããŠèª¬æãããšãšãã«ãããã€ãã®ãã¹ããã©ã¯ãã£ã¹ã«ã€ããŠã説æããŸãã åææ¡ä»¶ ãã®æçš¿ã«æ²¿ã£ãŠé²ããã«ã¯ã次㮠AWS ãµãŒãã¹ã«ç²ŸéããŠããå¿
èŠããããŸãã AWS DMS Amazon RDS for SQL Server ããã«ããã®ãœãªã¥ãŒã·ã§ã³ã«å¿
èŠãªãªãœãŒã¹ãèµ·åããã®ã«ååãªæš©éãæã€ AWS ã¢ã«ãŠã³ããå¿
èŠã§ãã AWS DMS 㯠Amazon RDS for SQL Server ãšã©ã®ããã«é£æºããã®ã Amazon RDS for SQL Server ã®å ŽåãAWS DMS 㯠Microsoft ã®é¢æ°ã䜿çšããŠãã©ã³ã¶ã¯ã·ã§ã³ãã°ãèªã¿åããããã©ã«ãã§äžäœ 50,000 ã€ãã³ããååŸããŸããAWS DMS ã¯ã AWS DMS ã¿ã¹ã¯ ã§å®çŸ©ãããããŒãã«ã«é¢é£ããç¹å®ã®ããŒãã£ã·ã§ã³ ID ã®ããŒã¿ããŒã¹ãã°ãç
§äŒããããšããå§ãŸããŸããããŒãã£ã·ã§ã³ ID ã¯ããã«ããŒããš CDC ã®äž¡æ¹ã§ãåããŒãã«ã®åããŒããã¿ã¹ã¯ã®åèµ·åãã¿ã¹ã¯ã®åéæã«èªã¿åãããŸããAWS DMS ã¯ãªããžã§ã¯ã ID ãååŸãããããã®ãªããžã§ã¯ã ID ã«å¯Ÿå¿ããããŒã¿ããŒãã£ã·ã§ã³ ID ãååŸããŸããããŒãã£ã·ã§ã³ ID ãååŸããåŸãé¢é£ããããŒãã£ã·ã§ã³ããã©ã³ã¶ã¯ã·ã§ã³ãã°ãããã§ããããŸãããã®ãµã€ã¯ã«ã¯ 1 ç§ããšã«å®è¡ãããŸãã æ¬¡ã®å³ã¯ãã¢ãŒããã¯ãã£ã瀺ããŠããŸãããã®äŸã§ã¯ãAmazon RDS for SQL Server ããœãŒã¹ãšããŠäœ¿çšããŠããŸããAWS DMS ã¿ã¹ã¯ã®ã¿ãŒã²ããã¯ã ãµããŒããããŠããä»»æã®ãšã³ããã€ã³ã ã«ãªããŸãã AWS DMS ã¯ãMicrosoft ã®é¢æ°ã䜿ã£ãŠãã©ã³ã¶ã¯ã·ã§ã³ãã°ãèªã¿åããAWS ã® DMS ã¿ã¹ã¯ã®å¯Ÿè±¡ãšãªãããŒãã«ãšãœãŒã¹ããŒã¿ããŒã¹ã§ CDC ãæå¹ã«ãªã£ãŠããå¿
èŠããããŸãã ãªã AWS DMS ã§ CDC ãå¿
èŠãªã®ã ? ããŒãã«ã§ CDC ãæå¹ã«ãªããšãSQL Server 㯠cdc ã¹ããŒãã«ããŒãã«ãäœæããŸããäœæãããããŒãã« (倿ŽããŒãã«) ã«ã¯å€æŽããŒã¿ãæ ŒçŽããããã©ããã³ã°ãããŠããã¹ããŒããšããŒãã«ã«åºã¥ããŠååãä»ããããŸããããšãã°ã dbo ã¹ããŒãã« customer ãšããååã®ããŒãã«ãããå Žåã dbo.customer ããŒãã«ã«å¯Ÿãããã¹ãŠã®å€æŽãèšé²ããããã« cdc.customer_CT ãšããååã®ããŒãã«ã cdc ã¹ããŒãã«äœæãããŸãã AWS DMS ã¯å€æŽããŒãã«ãèªã¿åããŸãããAWS DMS ã¯ã倿Žããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«ç¢ºå®ã«èšé²ãããããã« CDC ãæå¹ã«ããå¿
èŠããããŸããåã®ã»ã¯ã·ã§ã³ã§èª¬æããããã«ãAWS DMS 㯠Microsoft ã®é¢æ°ã䜿ã£ãŠãã©ã³ã¶ã¯ã·ã§ã³ãã°ãã倿Žãèªã¿åããŸãããœãŒã¹åŽã®æ¬¡ã®ããŒãã«ãèããŠã¿ãŸãããã CREATE TABLE [ dbo ] . [ dmstest ] ( [ id ] [ int ] NOT NULL , [ name ] [ varchar ] ( 50 ) NULL , CONSTRAINT [ PK_dmstest ] PRIMARY KEY CLUSTERED ( [ id ] ASC ) WITH ( PAD_INDEX = OFF , STATISTICS_NORECOMPUTE = OFF , IGNORE_DUP_KEY = OFF , ALLOW_ROW_LOCKS = ON , ALLOW_PAGE_LOCKS = ON , OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF ) ON [ PRIMARY ] ) ON [ PRIMARY ] SQL ãã®ããŒãã«ã« UPDATE ã¹ããŒãã¡ã³ããçºè¡ã㊠[name] åãæŽæ°ãããšã [RowLog Contents 0] ãš [RowLog Contents 1] ã«å€æŽæ
å ±ãæŽæ°ãããŸããAWS DMS ããœãŒã¹äžã§å®è¡ããæ¬¡ã®ã¯ãšãªã®äžéšã瀺ããŸãã select top 50000 [ Current LSN ] , [ operation ] , [ RowLog Contents 0 ] , [ RowLog Contents 1 ] -- After Image SQL ã¯ãšãªã®çµæãèŠããšã2 çªç®ã®ã¬ã³ãŒãã« CDC ãæå¹ã«ããåŸã«çºè¡ãã UPDATE ã¹ããŒãã¡ã³ãã®ç¶æ
ããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«ãã£ããã£ãããŠããããšãåãããŸãã Current LSN operation RowLog Contents 0 RowLog Contents 1 0000014f:0000c16d:0002 LOP_MODIFY_ROW 0x1800 746573747573657267 0x1900 74657374757365726162 0000014f:0000c9ba:0016 LOP_MODIFY_ROW 0x300008000200000002000001001900 74657374757365726162 0x300008000200000002000001001800 746573747573657267 CDC ãã©ã¡ãŒã¿ã®çè§£ CDC ã§ã¯ã2 ã€ã®ãžã§ããäœæãããŸãã ãã£ããã£ãžã§ã â ãã©ã³ã¶ã¯ã·ã§ã³ãã°ãã¡ã€ã«ãèªã¿åãã倿Žããã©ããã³ã°ããããŒãã«ã«ããã·ã¥ããŸãã ã¯ãªãŒã³ã¢ãããžã§ã â ä¿ææéãéãã倿Žãã©ããã³ã°ããŒãã«ã®ã¬ã³ãŒããåé€ããŸãã 以äžã¯ãAWS DMS ã«é¢é£ãã CDC ãã©ã¡ãŒã¿ ã§ãã max_trans â åã¹ãã£ã³ãµã€ã¯ã«ã§åŠçãããã©ã³ã¶ã¯ã·ã§ã³ã®æå€§æ° max_scans â ãã°ãããã¹ãŠã®è¡ãæœåºããããã«å®è¡ããã¹ãã£ã³ãµã€ã¯ã«ã®æå€§æ° continuous â ãã£ããã£ãžã§ããç¶ç¶çã«å®è¡ããã (1)ã1åã ãå®è¡ããã (0) ã瀺ããŸã polling_interval â ãã°ã¹ãã£ã³ãµã€ã¯ã«éã®ç§æ° retention â 倿Žè¡ãããŒãã«ã«ä¿æãããåæ° AWS DMS ã¯å€æŽããŒãã«ãèªã¿åããªãã®ã§ããã©ã³ã¶ã¯ã·ã§ã³ãã°ã®å€æŽã®ä¿æãå¶åŸ¡ããããã« CDC ãã©ã¡ãŒã¿ã調æŽããå¿
èŠããããŸãã æ¬¡ã®ã»ã¯ã·ã§ã³ã§ã¯ã max_trans ã max_scans ã polling_interval ãã©ã¡ãŒã¿ããã©ã³ã¶ã¯ã·ã§ã³ãã°å
ã®ã¬ã³ãŒãã®ä¿æã«ã©ã®ããã«åœ¹ç«ã€ã®ããAWS DMS ã倿Žããã£ããã£ããã®ã«ååãªæé倿Žãä¿æãããããã«èª¿æŽããæ¹æ³ã«ã€ããŠèª¬æããŸãã CDC ãã©ã¡ãŒã¿ã®å®è¡ ãããã®ãã©ã¡ãŒã¿ãŒã説æããããã«ãæ¬¡ã®æé ãèžã¿ãŸãã dmscdc ããŒã¿ããŒã¹ãšãã®äžã® dmstestcdc ããŒãã«ãäœæããŠãã ããã create database dmscdc ; use dmscdc ; CREATE TABLE dbo . dmstestcdc ( n INT NOT NULL PRIMARY KEY ) ; SQL ããŒã¿ããŒã¹ dmscdc ããã³ããŒãã« dmstestcdc ã§ CDC ãæå¹ã«ããŸãã exec msdb . dbo . rds_cdc_enable_db 'dmscdc' ; use dmscdc ; exec sys . sp_cdc_enable_table @source_schema = 'dbo' , @source_name = 'dmstestcdc' , @role_name = 'CDCRole' , @supports_net_changes = 1 ; SQL CDC ãã©ã¡ãŒã¿ã調æŽããŠããã°ã¬ã³ãŒããååãªæéä¿æãããããã«ããå¿
èŠããããŸããããããããšã§ãAWS DMS ããã©ã³ã¶ã¯ã·ã§ã³ã¬ã³ãŒããã€ãŸãããœãŒã¹ããŒã¿ããŒã¹ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ãã¡ã€ã«ã§æ¢ããŠããç¹å®ã®ãã°ã·ãŒã±ã³ã¹çªå· (LSN) ãç
§äŒã§ããããã«ãªããŸãããããã¯æ¬¡ã®èŠå ã«å·Šå³ãããŸãã ã¿ãŒã²ããããœãŒã¹ã«æ¯ã¹ãŠã©ãããããã©ã³ã¶ã¯ã·ã§ã³ãé
ããŠããã CDC ãžã§ãã®å®è¡é »åºŠãã€ãŸãç¹å®ã®ããŒãªã³ã°ééã¯ã©ã®ãããã Maxtrans ãš Maxscans ã®å€ã¯äœãããããã®ãã©ã¡ãŒã¿ã¯ãCDC ãåå®è¡ã§ã©ããããã®ãã©ã³ã¶ã¯ã·ã§ã³ãåŠçããããæ±ºå®ããŸãã æ¬¡ã®ããã«åã蟌ã¿ãžã§ããæ§æããŠãã ãããåã蟌ã¿ãžã§ãã®ãã©ã¡ãŒã¿ãŒã倿Žããå Žåã¯ãæ¯å CDC ãžã§ãã忢ããŠåèµ·åããå¿
èŠããããŸãããã®å Žåã pollinginterval ã倿Žããå¿
èŠããããŸãã EXECUTE sys . sp_cdc_change_job @job_type = N 'capture' , @pollinginterval = 3599 ; --Setting the polling interval for 1 hour EXEC sys . sp_cdc_stop_job @job_type = N 'capture' ; EXEC sys . sp_cdc_start_job @job_type = N 'capture' ; SQL 次ã®ã³ãã³ããå®è¡ã㊠CDC ãã©ã¡ãŒã¿ã確èªããŠãã ããã exec sys . sp_cdc_help_jobs ; SQL job_id job_type job_name maxtrans maxscans continuous pollinginterval retention threshold A49487C5-BF3C-4A8C-9385-6AFA7A3541B9 capture cdc.dmscdc_capture 500 10 1 3599 0 0 17511020-59D2-4C9E-BEA9-0578C0D23B11 cleanup cdc.dmscdc_cleanup 0 0 0 0 4320 5000 åè¿°ã®èšå®ã§ã¯ããã£ããã£ãžã§ã㯠1 æéã®åšæã§ 5,000 ã¬ã³ãŒã ( maxtrans * maxscans ) ãåŠçããŸãã ããŒãã« dmstestcdc ã«ã¬ã³ãŒããããã€ãæ¿å
¥ããŠç¢ºèªããŠãã ããã DECLARE @max AS INT , @min AS INT ; SET @max = 100000 ; SET @min = 1 ; WHILE @min <= @max BEGIN INSERT INTO dbo . dmstestcdc VALUES ( @min ) ; SET @min = @min + 1 ; END SQL ãã£ããã£ãžã§ãã¯ããã©ã³ã¶ã¯ã·ã§ã³ãã°ããåã®ååŒãèªã¿åãããããã replicated ããããã®ãšããŠããŒã¯ããŸãããã®å Žå㯠100,001 ã¬ã³ãŒãã§ããCDC ãžã§ããå®è¡ããããšããã£ããã£ãžã§ãã¯ãããã®ååŒãå®äºãšããŠããŒã¯ããŸãã æ¬¡ã®ã¯ãšãªãå®è¡ã㊠CDC ã»ãã·ã§ã³ã確èªããŠãã ãããããã«ãã 10 è¡ãååŸãããã¯ãã§ãããã®ã¯ãšãªã«ãããCDC ãåŠçããã¬ã³ãŒãæ°ãåãããŸããä»åã®å Žå㯠5,000 ä»¶ã§ãã SELECT tran_count , start_time , end_time , scan_phase from sys . dm_cdc_log_scan_sessions where scan_phase <> 'Aggregate' order by end_time desc SQL tran_count start_time end_time scan_phase 500 2023-12-07 20:34:15.100 2023-12-07 20:34:15.123 Done 500 2023-12-07 20:34:15.067 2023-12-07 20:34:15.083 Done 500 2023-12-07 20:34:15.037 2023-12-07 20:34:15.053 Done 500 2023-12-07 20:34:15.003 2023-12-07 20:34:15.023 Done 500 2023-12-07 20:34:14.963 2023-12-07 20:34:14.990 Done 500 2023-12-07 20:34:14.927 2023-12-07 20:34:14.950 Done 500 2023-12-07 20:34:14.883 2023-12-07 20:34:14.910 Done 500 2023-12-07 20:34:14.840 2023-12-07 20:34:14.870 Done 500 2023-12-07 20:34:14.797 2023-12-07 20:34:14.827 Done 500 2023-12-07 20:34:14.540 2023-12-07 20:34:14.773 Done åè¿°ã®ã¬ã³ãŒãã¯ãéåžž 5 åããšã«è¡ããã Amazon RDS for SQL Server ã®ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã®ããã¯ã¢ããæã«ãã©ã³ã¶ã¯ã·ã§ã³ãã°ããåé€ãããŸããããã«ããããã©ã³ã¶ã¯ã·ã§ã³ãã°ã®ãµã€ãºãç¶æããLSN ãé²ããããšãã§ããŸããæ®ãã®ã¬ã³ãŒã (95,001ä»¶) ã¯ã次ã®ãã£ããã£ãžã§ãã®å®è¡æã«åã蟌ãŸããŸãã SQL Server 㯠CDC ã«ãã£ãŠãã©ã³ã¶ã¯ã·ã§ã³ãèªã¿åãããåŸã§ãã ãã©ã³ã¶ã¯ã·ã§ã³ãã°ããã©ãã·ã¥ããŸããããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«ä¿æããã¬ã³ãŒãæ°ãš AWS DMS ã®ã¬ããªã±ãŒã·ã§ã³ã©ã°ã®ãã©ã³ã¹ãåãå¿
èŠããããŸãããã®å ŽåãããŒãªã³ã°ééãçãããããšã§ããã£ããã£ãžã§ãã®ãã©ã¡ãŒã¿ãç©æ¥µçã«èšå®ããŸãããã®çµæããã©ã³ã¶ã¯ã·ã§ã³ãã°ãã LSN ãæ¬ èœããã·ããªãªãçºçããå¯èœæ§ããããŸãããã©ã³ã¶ã¯ã·ã§ã³ãã°ã®åãæšãŠãåé¿ããŠã倿Žãååãªæéãã©ã³ã¶ã¯ã·ã§ã³ãã°ã«ä¿æãããããã«ããã«ã¯ã次ã®ã³ãã³ããå®è¡ããŠããŒãªã³ã°ééã 1 æ¥ã«èšå®ããããšããå§ãããŸãã use dbname EXEC sys . sp_cdc_change_job @job_type = 'capture' , @pollinginterval = 86399 exec sp_cdc_stop_job 'capture' exec sp_cdc_start_job 'capture' SQL CDC ã®å±¥æŽæ
å ±ã®ãã£ãã㣠ãã£ããã£ãžã§ãã®å±¥æŽæ
å ±ãç£èŠããã«ã¯ã sys.dm_cdc_log_scan_sessions ããŒãã«ãç
§äŒã§ããŸãããã®ããŒãã«ã«ã¯ãçŸåšã®ããŒã¿ããŒã¹å
ã®åãã°ã¹ãã£ã³ã»ãã·ã§ã³ã«å¯Ÿã㊠1 è¡ãå«ãŸããŠããŸããæå€§ 32 ã®ã¹ãã£ã³ã»ãã·ã§ã³ãå«ãŸããŸããæ¬¡ã®ã¯ãšãªãå®è¡ããŠãææ°ã® 10 ã¬ã³ãŒããååŸããŸãã SELECT session_id , start_time , end_time , duration , scan_phase , error_count , tran_count , command_count , last_commit_cdc_time , latency , empty_scan_count , failed_sessions_count FROM sys . dm_cdc_log_scan_sessions order by end_time desc OFFSET 0 ROWS FETCH NEXT 10 ROWS ONLY ; SQL 以äžã¯ãµã³ãã«åºåã§ãã session_id start_time end_time duration scan_phase error_count tran_count command_count last_commit_cdc_time latency empty_scan_count failed_sessions_count 0 2023-12-07 19:21:27.283 2023-12-08 00:34:12.837 6 Aggregate 0 125001 125001 2023-12-07 19:50:32.657 17020 0 0 651 2023-12-08 00:34:12.820 2023-12-08 00:34:12.837 0 Done 0 500 500 2023-12-07 19:50:32.657 17020 0 0 650 2023-12-08 00:34:12.790 2023-12-08 00:34:12.810 0 Done 0 500 500 2023-12-07 19:50:31.700 17021 0 0 649 2023-12-08 00:34:12.760 2023-12-08 00:34:12.780 0 Done 0 500 500 2023-12-07 19:50:30.707 17022 0 0 648 2023-12-08 00:34:12.703 2023-12-08 00:34:12.723 0 Done 0 500 500 2023-12-07 19:50:29.757 17023 0 0 647 2023-12-08 00:34:12.670 2023-12-08 00:34:12.693 0 Done 0 500 500 2023-12-07 19:50:28.620 17024 0 0 646 2023-12-08 00:34:12.633 2023-12-08 00:34:12.660 0 Done 0 500 500 2023-12-07 19:50:27.523 17025 0 0 645 2023-12-08 00:34:12.587 2023-12-08 00:34:12.620 0 Done 0 500 500 2023-12-07 19:50:26.527 17026 0 0 644 2023-12-08 00:34:12.530 2023-12-08 00:34:12.573 0 Done 0 500 500 2023-12-07 19:50:25.490 17027 0 0 643 2023-12-08 00:34:12.500 2023-12-08 00:34:12.520 0 Done 0 500 500 2023-12-07 19:50:24.450 17028 0 0 ãã¹ããã©ã¯ãã£ã¹ãšæ¢ç¥ã®åé¡ ãã®ã»ã¯ã·ã§ã³ã§ã¯ãCDC ãã©ã¡ãŒã¿ãŒã«é¢ãããã¹ããã©ã¯ãã£ã¹ãšèæ
®äºé
ãããã€ã説æããŸãã Multi-AZ ã€ã³ã¹ã¿ã³ã¹ã§ã®ãã§ã€ã«ãªãŒããŒæã«ãã©ã³ã¶ã¯ã·ã§ã³ãã°ã¬ã³ãŒããåãæšãŠããã ãã©ã€ããªã€ã³ã¹ã¿ã³ã¹ã§ CDC ãã©ã¡ãŒã¿ã倿Žããå Žåã¯ãå¿
ã rds_set_configuration ã³ãã³ããå®è¡ããŠããã§ã€ã«ãªãŒããŒæã«ã倿Žå
容ãä¿æãããããã«ããŠãã ããã äŸãã°ã dms_test ããŒã¿ããŒã¹äžã§æ¬¡ã®ãµã³ãã«ã³ãã³ããå®è¡ããŠã maxtrans ãš pollinginterval ãã©ã¡ãŒã¿ãèšå®ã§ããŸãã USE dms_test ; EXEC sys . sp_cdc_change_job @job_type = 'capture' , @maxtrans = 10000 , @pollinginterval = 6000 ; SQL ãã§ã€ã«ãªãŒããŒåŸããããã®å€ãä¿æãããããã«ã次ã®ã³ãã³ããå®è¡ããŠãã ããã EXEC rdsadmin . . rds_set_configuration 'cdc_capture_maxtrans' , 10000 ; EXEC rdsadmin . . rds_set_configuration 'cdc_capture_pollinginterval' , 6000 ; SQL AWS DMS ã¬ããªã±ãŒã·ã§ã³ã€ã³ã¹ã¿ã³ã¹ã®èšç»çãã§ã€ã«ãªãŒããŒãŸãã¯ã¡ã³ããã³ã¹ Amazon RDS for SQL Server ã®å ŽåããœãŒã¹ã§ã¡ã³ããã³ã¹äœæ¥ãè¡ãéããããã¯é¢é£ãã AWS DMS ã¬ããªã±ãŒã·ã§ã³ã€ã³ã¹ã¿ã³ã¹ã®èšç»çãªã¹ã±ãŒãªã³ã°ãè¡ãéã«ãAWS DMS ã¿ã¹ã¯ã忢ãã床ã«ãã£ããã£ãžã§ããå®è¡ãããªãããã«ããå¿
èŠããããŸãããã£ããã£ãžã§ããå®è¡ããããšãã¹ãã£ã³ãããã€ãã³ã㯠Amazon Simple Storage Service (Amazon S3) ã§ 5 åããšã«ãã©ã³ã¶ã¯ã·ã§ã³ãã°ããã¯ã¢ãããè¡ããããšãã«ãã©ã³ã¶ã¯ã·ã§ã³ãã°ããåé€ãããŸãã æ¬¡ã®ã³ãã³ããå®è¡ããŠãã£ããã£ãžã§ãã忢ããŠãã ããã exec sp_cdc_stop_job 'capture' SQL AWS DMS ã¿ã¹ã¯ã忢ããŸãã å¿
èŠãªã¡ã³ããã³ã¹ãå®äºããŸãã AWS DMS ã¿ã¹ã¯ãåéããŸãã ãœãŒã¹ã®é
å»¶ ã 0 ã«ãªãã®ãåŸ
ã¡ãŸãã æ¬¡ã®ã³ãã³ããå®è¡ããŠãã£ããã£ãžã§ããéå§ããŸãã exec sp_cdc_start_job 'capture' SQL AWS DMS ã¿ã¹ã¯ã¯ãäžèšã®æé ã«åŸããªãå Žåãæ¬¡ã®ãšã©ãŒã¡ãã»ãŒãžã§å€±æããŸãã 2023-10-06T15:02:05 [SOURCE_CAPTURE ]E: Failed to access LSN '0000019f:00007fff:0008' in the backup log sets since BACKUP/LOG-s are not available. [1020465] (sqlserver_endpoint_capture.c:813) JSON ãœãŒã¹ã§ãã£ããã£ãžã§ãã忢ããåŸã« LSN ãåãæšãŠãããŠããã®ã確èªããå Žåãã¢ã¯ãã£ããªãã©ã³ã¶ã¯ã·ã§ã³ãã°å
ã«åãæšãŠãé²ã CDC ã€ãã³ãããªãå¯èœæ§ããããŸããããã¯ããŒã¿ããŒã¹ãã¢ã€ãã«ç¶æ
ã«ãããããã©ã³ã¶ã¯ã·ã§ã³æ°ãå°ãªãå Žåã«çºçããå¯èœæ§ããããŸãããã®å Žåãæé ã¯æ¬¡ã®ãšããã§ãã æ¬¡ã®ã³ãã³ããå®è¡ããŠãã£ããã£ãžã§ãã忢ããŠãã ããã exec sp_cdc_stop_job 'capture' SQL AWS DMS ã¿ã¹ã¯ã忢ããåã«ãCDC 察å¿ã®ããŒã¿ããŒã¹ã«ããã€ãã®ãã©ã³ã¶ã¯ã·ã§ã³ãŸãã¯å€æŽãããããšã確èªããŠãã ãããæ¯ç§ DML ã¹ããŒãã¡ã³ããå®è¡ããã¹ã¯ãªãããå®è¡ã§ããŸãããã¹ãã¹ã¯ãªãããäœæããå Žåã¯ããã®ã»ã¯ã·ã§ã³ã®åŸåã«èšèŒãããŠããæé ã«åŸã£ãŠãã ããã AWS DMS ã¿ã¹ã¯ã忢ããŸãã å¿
èŠãªã¡ã³ããã³ã¹ãå®äºãããŸãã AWS DMS ã¿ã¹ã¯ãåéããŸãã ãœãŒã¹ã¬ã€ãã³ã·ãç£èŠããŠåæãåŸ
ã¡ãŸãã ã¹ããã 2 ã§èšå®ããã¹ã¯ãªããã忢ããŸãã æ¬¡ã®ã³ãã³ããå®è¡ããŠãã£ããã£ãžã§ããéå§ããŸãã exec sp_cdc_start_job 'capture' SQL ã¹ããã 2 ã§èšåããããã¹ãã¹ã¯ãªãããå®è¡ããããã®ã¹ã¯ãªãããèšå®ããã«ã¯ã以äžã®æé ã«åŸã£ãŠãã ããã以äžã®ã¹ã¯ãªããã§ã¯ãdbo ã¹ããŒãã®äžã« test_table ãšããååã®ããŒãã«ãäœæãããã® test_table ããŒãã«ã§ CDC ãæå¹ã«ããŸããæ¬¡ã«ãSQL Server ãšãŒãžã§ã³ããžã§ããèšå®ããäžèšã®ããŒãã«ã«ã¬ã³ãŒããæ¿å
¥ããŠããåé€ããŸããããã«ãããCDC ãžã§ãã«ãã£ãŠããã¯ã¢ãããããå¿
èŠã®ãããã©ã³ã¶ã¯ã·ã§ã³ãã°ã«å€æŽãããããã©ã³ã¶ã¯ã·ã§ã³ãã°ã®åãæšãŠãé²ãããšãã§ããŸãã ãã¹ãããŒãã«ãäœæããŸãã create table dbo . test_table ( id int not null PRIMARY KEY ) ; SQL æ°ããããŒãã«ã CDC ã«è¿œå ããŠãã ããã use dmscdc ; exec sys . sp_cdc_enable_table @source_schema = 'dbo' , @source_name = 'test_table' , @role_name = 'CDCRole' , @supports_net_changes = 1 ; SQL Amazon RDS ã§ SQL Server ãšãŒãžã§ã³ããžã§ããäœæãã1 åããšã«ã¬ã³ãŒããæ¿å
¥ãŸãã¯åé€ããŸãããšãŒãžã§ã³ããžã§ãã§é©å㪠owner_login_name ãš database_name ã®å€ã䜿çšããŠãã ããã USE [ msdb ] GO /****** Object: Job [aws_dms_traffic_to_test_table] Script Date: 10/9/2023 4:17:28 PM ******/ BEGIN TRANSACTION DECLARE @ReturnCode INT SELECT @ReturnCode = 0 /****** Object: JobCategory [[Uncategorized (Local)]] Script Date: 10/9/2023 4:17:28 PM ******/ IF NOT EXISTS ( SELECT name FROM msdb . dbo . syscategories WHERE name = N '[Uncategorized (Local)]' AND category_class = 1 ) BEGIN EXEC @ReturnCode = msdb . dbo . sp_add_category @class = N 'JOB' , @type = N 'LOCAL' , @name = N '[Uncategorized (Local)]' IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback END DECLARE @jobId BINARY ( 16 ) EXEC @ReturnCode = msdb . dbo . sp_add_job @job_name = N 'aws_dms_traffic_to_test_table' , @enabled = 1 , @notify_level_eventlog = 0 , @notify_level_email = 0 , @notify_level_netsend = 0 , @notify_level_page = 0 , @delete_level = 0 , @description = N 'No description available.' , @category_name = N '[Uncategorized (Local)]' , @owner_login_name = N 'admin' , @job_id = @jobId OUTPUT IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback /****** Object: Step [generate_traffic] Script Date: 10/9/2023 4:17:28 PM ******/ EXEC @ReturnCode = msdb . dbo . sp_add_jobstep @job_id = @jobId , @step_name = N 'generate_traffic' , @step_id = 1 , @cmdexec_success_code = 0 , @on_success_action = 1 , @on_success_step_id = 0 , @on_fail_action = 2 , @on_fail_step_id = 0 , @retry_attempts = 0 , @retry_interval = 0 , @os_run_priority = 0 , @subsystem = N 'TSQL' , @command = N 'insert into dbo.test_table values (30); delete from dbo.test_table where id = 30; ' , @database_name = N 'dmscdc' , @flags = 0 IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback EXEC @ReturnCode = msdb . dbo . sp_update_job @job_id = @jobId , @start_step_id = 1 IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback EXEC @ReturnCode = msdb . dbo . sp_add_jobschedule @job_id = @jobId , @name = N 'schedule for running DML statements for generating user_traffic' , @enabled = 1 , @freq_type = 4 , @freq_interval = 1 , @freq_subday_type = 4 , @freq_subday_interval = 1 , @freq_relative_interval = 0 , @freq_recurrence_factor = 0 , @active_start_date = 20231006 , @active_end_date = 99991231 , @active_start_time = 0 , @active_end_time = 235959 , @schedule_uid = N '84a2b2ab-4234-40a3-add4-c04d561ad88f' IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback EXEC @ReturnCode = msdb . dbo . sp_add_jobserver @job_id = @jobId , @server_name = N '(local)' IF ( @ @ERROR <> 0 OR @ReturnCode <> 0 ) GOTO QuitWithRollback COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF ( @ @TRANCOUNT > 0 ) ROLLBACK TRANSACTION EndSave: GO SQL AWS DMS ã³ã³ãœãŒã«ã§ãAWS DMS ã¿ã¹ã¯ã®ããŒãã«éžæã«ãŒã«ã«ã¯ã€ã«ãã«ãŒã (%) ã䜿çšããŠãã®ããŒãã«ãè€è£œããå Žåã¯ã ãããã³ã° ã«ãŒã«ã䜿çšããŠãã®ããŒãã«ã AWS DMS ã¿ã¹ã¯ããé€å€ããŠãã ããã { "rule-type": "selection", "rule-id": "1", "rule-name": "1", "object-locator": { "schema-name": "dbo", "table-name": "test_table" }, "rule-action": "exclude", "filters": [] }, JSON SQL Server ã€ã³ã¹ã¿ã³ã¹ã® RDS ã®èšç»çãªåèµ·åãŸãã¯ãã§ã€ã«ãªãŒã㌠RDS for SQL Server ãšãŒãžã§ã³ããµãŒãã¹ã¯ãRDS for SQL Server ã€ã³ã¹ã¿ã³ã¹ã®åèµ·åããã§ã€ã«ãªãŒããŒãçºçãããã³ã«åèµ·åããããã«ããåèµ·åãŸãã¯ãã§ã€ã«ãªãŒããŒåŸã« CDC ãžã§ããåå®è¡ãããŸãããã©ã³ã¶ã¯ã·ã§ã³ãã°ã®åãæšãŠãåé¿ããã«ã¯ãæ¬¡ã®æé ã«åŸã£ãŠãã ããã AWS DMS ã¿ã¹ã¯ã忢ããŸãã ãã§ã€ã«ãªãŒããŒåŸã«å
ã«æ»ããããçŸåšã® maxtrans ãš maxscans ã®å€ãååŸããŸãã sys . sp_cdc_help_jobs ; SQL CDC ã®èšå®ã倿ŽããŠã maxtrans ãš maxscans ã 1 ã«èšå®ããŸãã EXEC sys . sp_cdc_change_job @job_type = 'capture' , @maxtrans = 1 , @maxscans = 1 exec sp_cdc_stop_job 'capture' GO SQL ãã§ã€ã«ãªãŒããŒåŸã CDC ãã©ã¡ãŒã¿ãŒãä¿æãããããæ¬¡ã®ã¹ããŒãã¡ã³ããå®è¡ããŠãã ããã EXEC rdsadmin . . rds_set_configuration 'cdc_capture_maxtrans' , 1 ; EXEC rdsadmin . . rds_set_configuration 'cdc_capture_maxscans' , 1 ; SQL RDS for SQL Server ã€ã³ã¹ã¿ã³ã¹ãåèµ·åããŸãã AWS DMS ã¿ã¹ã¯ãåéããŸãã ãã£ããã£ãããæ§æã§ããã£ããã£ããããžã§ããåèµ·åããŸããæ¬¡ã®ã¹ã¯ãªããã§ã¯ã maxtrans ã 500ã maxscans ã 10 ãšæ³å®ããŠããŸãããã¹ããã 2 ã§ãã£ããã£ããå€ã䜿çšããå¿
èŠããããŸãã EXEC sys . sp_cdc_change_job @job_type = 'capture' , @maxtrans = 500 , @maxscans = 10 exec sp_cdc_stop_job 'capture' exec sp_cdc_start_job 'capture' GO SQL ãã§ã€ã«ãªãŒããŒåŸã CDC ãã©ã¡ãŒã¿ãä¿æããããã«ã次ã®ã¹ããŒãã¡ã³ããå®è¡ããŠãã ããã EXEC rdsadmin . . rds_set_configuration 'cdc_capture_maxtrans' , 500 ; EXEC rdsadmin . . rds_set_configuration 'cdc_capture_maxscans' , 10 ; SQL åŸçä»ã 宿çãªèª²éãé¿ããããã«ããªãœãŒã¹ãã¯ãªãŒã³ã¢ããããŠãã ããã AWS DMS ã³ã³ãœãŒã«ã§ãèšå®ãããã¹ãŠã® AWS DMS ã¿ã¹ã¯ãåé€ããŸãã æ¬¡ã®ã³ãã³ããå®è¡ããŠããŒã¿ããŒã¹ãåé€ããŸãã EXECUTE msdb . dbo . rds_drop_database N 'dmscdc' SQL çµè« ãã®æçš¿ã§ã¯ãAmazon RDS for SQL Server ããœãŒã¹ãšã㊠AWS DMS ã¿ã¹ã¯ãæ§æããéã® CDC ãã©ã¡ãŒã¿ãŒã®èšå®ã®éèŠæ§ã説æããããã«ãã¹ããã©ã¯ãã£ã¹ã«ã€ããŠã説æããŸããã 翻蚳ã¯ãœãªã¥ãŒã·ã§ã³ã¢ãŒããã¯ãã® Yoshinori Sawada ãæ
åœããŸãããåæã¯ ãã¡ã ã§ãã èè
ã«ã€ã㊠Suchindranath Hegde ã¯ãAmazon Web Services ã®ããŒã¿ãã€ã°ã¬ãŒã·ã§ã³ã¹ãã·ã£ãªã¹ããœãªã¥ãŒã·ã§ã³ã¢ãŒããã¯ãã§ããAWS DMS ã䜿çšã㊠AWS ã¯ã©ãŠããžã®ããŒã¿ç§»è¡ã«é¢ããã¬ã€ãã³ã¹ãšæè¡æ¯æŽãã客æ§ã«æäŸããŠããŸãã Abhishek Chaturvedi ã¯ãAmazon Web Services ã® DMS ããŒã ã®ã·ãã¢ããŒã¿ããŒã¹ãšã³ãžãã¢ã§ãã Mahesh Kansara ã¯ãAmazon Web Services ã®ããŒã¿ããŒã¹ãšã³ãžãã¢ãªã³ã°ãããŒãžã£ãŒã§ãã圌ã¯éçºããã³ãšã³ãžãã¢ãªã³ã°ããŒã ãšå¯æ¥ã«ååããç§»è¡ããã³ã¬ããªã±ãŒã·ã§ã³ãµãŒãã¹ã®æ¹åã«åãçµãã§ããŸãããŸããã客æ§ãšååããããŸããŸãªããŒã¿ããŒã¹ããã³åæãããžã§ã¯ãã«é¢ãããªãŒããšãã¯ãã«ã«ãµããŒããæäŸããAWS ã䜿çšããéã®ãœãªã¥ãŒã·ã§ã³ã®äŸ¡å€åäžããµããŒãããŠããŸãã Junu Thankappan ã¯ãAmazon Web Services ã®ã·ãã¢ããŒã¿ããŒã¹ãšã³ãžãã¢ã§ãã圌㯠AWS RDS ããŒã ã«æå±ããåçšããŒã¿ããŒã¹ãšã³ãžã³ãš SQL Server ã«æ³šåããŠããŸãã