서로 다른 데이터베이스의 table과 data를 비교하는 방법

안녕하세요? Holmes 입니다.

SQL Server 를 Migration 하게되면 필연적으로 기존 Database와 신규 Database간에 데이터 동기화 작업이 필요하다.

이를 위해서 여러가지 방법론이 존재하나, downtime을 최소화 하고 마이그레이션/복제 등의 작업 이후에 상호간 데이타 정합성을 확인 하는 방법에 대한 글이다.

테스트 전, Left join을 사용하면 되지 않을까? 라는 단순한 생각을 했었고 실제로 운영서버에서도 이런 경우 대부분 Left Join을 많이들 사용하는데,

Left join의 결과값과 다른 방법의 결과값을 비교하고 장단점을 비교한다.

[Request]

서로 다른 데이터베이스 내의 Table / Data 비교

[Solution]

다음은 샘플 데이터베이스, 테이블 및 데이터를 작성하는 스크립트이다.

CREATE DATABASE dbtest01 GO USE dbtest01 GO CREATE TABLE [dbo].[article] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,   CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED   (   [id] ASC   ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[article]   VALUES ('001', '1', '40'),   ('002', '2', '80'),   ('003', '3', '120') GO CREATE DATABASE dbtest02 GO USE dbtest02 GO CREATE TABLE [dbo].[article] ([id] [nchar](10) NOT NULL, [type] [nchar](10) NULL, [cost] [nchar](10) NULL,   CONSTRAINT [PK_article] PRIMARY KEY CLUSTERED   (   [id] ASC   ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO INSERT INTO [dbo].[article]   VALUES ('001', '1', '40'),   ('002', '2', '80'),   ('003', '3', '120'),   ('004', '4', '160') GO

다음은 처음 생각했던 Left Join을 이용한 Query와 그 결과 값이다.

SELECT *         FROM dbtest02.dbo.article d2 LEFT JOIN dbtest01.dbo.article d1         ON d2.id = d1.id GO

id         type       cost       id         type       cost ---------- ---------- ---------- ---------- ---------- ---------- 001        1          40         001        1          40        002        2          80         002        2          80        003        3          120        003        3          120       004        4          160        NULL       NULL       NULL

원하는 대로, dbtest02 데이터베이스에는 있고, dbtest01 데이터베이스에는 없는 값이 나오기는 했으나, 깔끔하지도 못하고 Insert into ... select로 넣기에는 값이 맞지 않다.

그래서, 다음과 같이 EXCEPT 명령을 사용할 수 있다.

SELECT * FROM dbtest02.dbo.article EXCEPT SELECT * FROM dbtest01.dbo.article GO

id         type       cost ---------- ---------- ---------- 004        4          160

깔끔하고 좋아서 행복했다.

그런데... 두둥~ 이눔의 Oracle에서는 Except가 없다. (물론 MINUS로 고치면 되는데 Linked Server가 많은 구성에서는 찾는 것도 일이다.)

다음에는 Except를 안쓰고, Inner Join을 이용해봤다.

SELECT *         FROM dbtest02.dbo.article d2         WHERE d2.id not in                (select d1.id                        from dbtest01.dbo.article d1) GO

id         type       cost ---------- ---------- ---------- 004        4          160

Linked Server로 Insert Into로도 잘 들어가길래 끝났다고 생각했다.

그러나, 언제나 현실은 만만하지 않은 법.

실제 Database migration 작업시에는 원본에서 Insert만 한 것이 아니라, Delete / Update가 많았다는 것이다.

따라서, 단순히 Full backup / Restore 이후에 INSERT INTO  ... SELECT로는 모든 변경을 적용할 수가 없다.

[Reference]

https://www.mssqltips.com/sqlservertip/2779/ways-to-compare-and-find-differences-for-sql-server-tables-and-data/

https://docs.microsoft.com/ko-kr/sql/t-sql/language-elements/set-operators-except-and-intersect-transact-sql?view=sql-server-2017

https://docs.microsoft.com/ko-kr/sql/relational-databases/performance/joins?view=sql-server-2017