Oracle에서 SQL Server로 및 SQL Server에서 Oracle로 마이그레이션 가이드
게시 됨: 2022-03-11"공급업체 의존성"은 많은 기업 경영진에게 무서운 단어입니다. 반면에 완전한 "벤더 독립성"을 달성할 수 없다는 것은 업계에서 이미 널리 이해되고 있습니다. 그리고 데이터베이스의 경우 특히 그렇습니다.
가장 많이 보급된 엔터프라이즈 RDBMS 플랫폼 중 두 가지는 Oracle Database와 Microsoft SQL Server입니다(간결함을 위해 이 기사의 나머지 부분에서는 각각 "Oracle" 및 "SQL Server"로 부를 것입니다). 물론 IBM Db2는 계속 줄어들고 있지만 여전히 많은 영역에서 중요한 메인프레임 플랫폼에서 Oracle과 경쟁합니다. 그리고 PostgreSQL과 같이 빠르게 발전하는 오픈 소스 대안은 중저가 상용 하드웨어와 웹의 동적 환경에서 확고한 기반을 확보하고 있습니다.
그러나 Oracle과 SQL Server 는 조직에 새로운 RDBMS가 필요할 때 많은 비즈니스 경영진이 직면하는 선택입니다. 최종 선택은 라이선스 비용, 사용 가능한 내부 전문 지식 및 과거 경험, 기존 환경과의 호환성, 파트너 관계, 향후 사업 계획 등 여러 요소를 기반으로 합니다. 그러나 가장 철저한 사전 평가와 최고의 교육을 받은 의사 결정에도 불구하고 때로는 요소 변해야 플랫폼도 변해야 합니다. 제가 경력을 쌓는 동안 그런 마이그레이션을 두 번 구현했고, 전환 타당성 평가를 한 번 준비했으며, 지금 바로 크로스 플랫폼 기능 마이그레이션 작업을 하고 있기 때문에 이것을 압니다.
Oracle과 SQL Server는 모두 "구식"이며 부분적으로 ANSI 호환 RDBMS 구현입니다. 절차적 확장(PL/SQL과 Transact-SQL은 구문이 다르지만 일반적으로 변환이 간단함)과 새로운 객체 지향 미래를 제외하면 SQL 코드는 믿을 수 없을 정도로 유사해 보일 수 있습니다. 그리고 이것은 위험한 꿀 덫입니다.
Oracle과 SQL Server(어느 방향이든) 간의 마이그레이션 프로젝트에서 가장 중요한 두 가지 사항은 트랜잭션 범위를 해결하는 핵심 도구인 트랜잭션 과 밀접하게 관련된 임시 테이블 입니다. 또한 중첩된 트랜잭션(다른 트랜잭션의 범위 내에 존재하는 트랜잭션)은 Oracle에서 사용자 보안 감사를 구현하는 핵심 부분이기 때문에 다룰 것입니다. 그러나 SQL Server에서 사용자 보안 감사는 해당 컨텍스트에서의 COMMIT 동작 때문에 다른 접근 방식이 필요합니다.
트랜잭션 구조 이해: 만 피트에서 Oracle 대 SQL Server 관찰
Oracle 트랜잭션은 암시적입니다. 이는 트랜잭션을 시작할 필요가 없음을 의미합니다. 항상 트랜잭션에 있습니다. 그리고 이 트랜잭션은 커밋 또는 롤백 문을 실행할 때까지 열려 있습니다. 예, 트랜잭션을 명시적으로 시작하고, 롤백 안전 지점을 정의하고, 내부/중첩 트랜잭션을 설정할 수 있습니다. 그러나 중요한 것은 "트랜잭션에 있지" 않고 항상 커밋 또는 롤백을 실행해야 한다는 것입니다. 또한 데이터 정의 언어(DDL) 문( CREATE , ALTER 등, 트랜잭션에서 동적 SQL을 통해 수행할 수 있음)을 발행하면 그것이 발행된 트랜잭션을 커밋한다는 점에 유의하십시오.
Oracle과 달리 SQL Server에는 명시적 트랜잭션이 있습니다. 즉, 트랜잭션을 명시적으로 시작하지 않는 한 모든 변경 사항이 "자동으로" 커밋됩니다. 모든 DML 문( INSERT , UPDATE , DELETE )이 자체적으로 트랜잭션을 생성하고 오류가 발생하지 않는 한 커밋하므로 문이 처리되는 즉시 밖.
이것은 데이터 저장소 구현의 차이, 즉 데이터가 데이터베이스에 기록되는 방식과 데이터베이스 엔진이 데이터를 읽는 방식의 차이의 결과입니다.
Oracle에서 DML 문은 데이터 파일에서 직접 레코드를 변경합니다. 레코드의 이전 복사본( INSERT 의 경우 빈 레코드 대체)이 현재 롤백 파일에 기록되고 정확한 변경 시간이 레코드에 표시됩니다.
SELECT 문이 발행되면 발행되기 전에 수정된 데이터를 기준으로 처리됩니다. SELECT 가 발행된 후 레코드가 수정된 경우 Oracle은 롤백 파일의 이전 버전을 사용합니다.
이것이 Oracle이 읽기 일관성 및 비차단 읽기/쓰기를 구현한 방법입니다. 매우 활동적인 트랜잭션 데이터베이스에 대한 장기 실행 쿼리가 때때로 악명 높은 오류 ORA-01555, snapshot too old: rollback segment ... too small 로 실행되는 이유이기도 합니다. (이것은 이전 버전의 레코드에 대한 쿼리에 필요한 롤백 파일이 이미 재사용되었음을 의미합니다.) 이것이 "내 Oracle 트랜잭션이 얼마나 오래되어야합니까?"라는 질문에 대한 정답이 된 이유입니다. "필요한 만큼만, 더 이상은 아니다"입니다.
SQL Server의 구현은 다릅니다. 데이터베이스 엔진은 데이터 파일에서만 직접 쓰고 읽습니다. 모든 SQL 문( SELECT / INSERT / UPDATE / DELETE )은 여러 명령문을 함께 그룹화하여 변경 사항을 롤백할 수 있는 명시적 트랜잭션의 일부가 아닌 한 트랜잭션입니다.
모든 트랜잭션은 필요한 리소스를 잠급니다. Microsoft SQL Server의 현재 릴리스는 필요한 리소스만 잠그는 데 고도로 최적화되어 있지만 필요한 것은 SQL 코드에 의해 정의되므로 쿼리를 최적화하는 것이 중요합니다. 즉, Oracle과 달리 SQL Server의 트랜잭션은 가능한 한 짧아야 하며 이것이 자동 커밋이 기본 동작인 이유입니다.
그리고 Oracle과 SQL Server의 어떤 SQL 구조가 트랜잭션 구현의 차이에 의해 영향을 받습니까? 임시 테이블.
Oracle 및 SQL Server의 임시 테이블
ANSI SQL 표준이 로컬 및 전역 임시 테이블을 정의할 때 구현 방법을 명시적으로 언급하지 않습니다. Oracle과 SQL Server는 모두 전역 임시 테이블을 구현합니다. SQL Server는 로컬 임시 테이블도 구현합니다. Oracle 18c는 또한 "진정한" 로컬 임시 테이블("개인 임시 테이블"이라고 함)을 구현했습니다. 따라서 SQL Server 코드를 Oracle 18c로 변환하는 작업이 이전 버전보다 눈에 띄게 간단해집니다. 자동 증가 ID 열과 같은 기능.
그러나 순수한 기능 분석 관점에서 개인용 임시 테이블의 도입은 SQL Server에서 Oracle로의 마이그레이션 문제를 실제보다 덜 보이게 만들기 때문에 복합적인 축복이 될 수 있습니다. 이것은 또 다른 허니 트랩입니다. 그 자체로 몇 가지 새로운 도전 과제를 도입할 수 있기 때문입니다. 예를 들어 개인 임시 테이블에서는 디자인 타임 코드 유효성 검사를 수행할 수 없으므로 이를 사용하는 코드는 항상 오류가 발생하기 쉽습니다. 동적 SQL을 사용했다면 다음과 같이 설명하겠습니다. 개인 임시 테이블은 디버그하기 복잡하지만 명백한 고유 사용 사례가 없습니다. 따라서 Oracle이 이전이 아닌 18c에서만 로컬(개인) 임시 테이블을 추가한 이유입니다.
요컨대, 같거나 더 나은 전역 임시 테이블을 사용하여 구현할 수 없는 Oracle의 개인 임시 테이블에 대한 사용 사례는 보지 못했습니다. 따라서 심각한 변환을 위해서는 Oracle과 SQL Server 전역 임시 테이블의 차이점을 이해해야 합니다.
Oracle 및 SQL Server의 전역 임시 테이블
Oracle 전역 임시 테이블은 DDL 문에 의해 디자인 타임에 명시적으로 생성된 영구 데이터 사전 개체입니다. 데이터베이스 수준 개체이고 필요한 권한이 있는 모든 데이터베이스 세션에서 액세스할 수 있기 때문에 "전역"입니다. 그러나 그 구조 가 전역적임에도 불구하고 전역 임시 테이블의 모든 데이터 는 해당 테이블이 내부에서 작동하는 세션으로만 범위가 지정되며 어떤 상황에서도 이 세션 외부에서 볼 수 없습니다. 즉, 다른 세션은 동일한 전역 임시 테이블의 자체 사본에 고유한 데이터를 가질 수 있습니다. 따라서 Oracle에서 전역 임시 테이블은 코드 단순화 및 성능 최적화를 위해 PL/SQL에서 주로 사용되는 세션 로컬 데이터를 보유합니다.
SQL Server에서 전역 임시 테이블은 Transact-SQL 코드 블록에서 생성된 임시 개체입니다. 생성 세션이 열려 있고 구조와 데이터 모두에서 데이터베이스의 다른 세션에서 볼 수 있는 한 존재합니다. 따라서 세션 간에 데이터를 공유하기 위한 전역 임시 개체입니다.
SQL Server의 로컬 임시 테이블은 이를 생성하는 세션에서만 액세스할 수 있다는 점에서 전역 테이블과 다릅니다. 그리고 SQL Server에서 로컬 임시 테이블을 사용하는 것은 전역 임시 테이블을 사용하는 것보다 훨씬 더 광범위합니다(데이터베이스 성능에 더 중요하다고 말하고 싶습니다).
그렇다면 로컬 임시 테이블은 SQL Server에서 어떻게 사용되며 Oracle로 어떻게 변환되어야 할까요?
SQL Server에서 로컬 임시 테이블의 중요한(올바른) 사용은 특히 다음과 같이 트랜잭션 리소스 잠금을 줄이거나 제거하는 것입니다.
- 일부 집계에서 일련의 레코드를 처리해야 하는 경우
- 데이터 세트를 분석 및 수정해야 하는 경우
- 동일한 범위에서 동일한 데이터 집합을 여러 번 사용해야 하는 경우
이러한 경우에는 이 레코드 세트를 로컬 임시 테이블로 선택하여 소스 테이블에서 잠금을 제거하는 것이 더 나은 솔루션인 경우가 많습니다.
SQL Server의 공통 테이블 표현식(CTE, 즉 WITH <alias> AS (SELECT...) 문)은 단지 "구문 설탕"에 불과하다는 점에 유의할 필요가 있습니다. SQL 실행 전에 인라인 하위 쿼리로 변환됩니다. Oracle CTE( /*+ materialize */ 힌트 포함)는 성능에 최적화되어 있으며 구체화된 뷰의 임시 버전을 생성합니다. Oracle의 실행 경로에서 CTE는 소스 데이터에 한 번만 액세스합니다. 이 차이를 기반으로 SQL Server는 Oracle 쿼리에서와 같이 동일한 CTE에 대한 여러 참조 대신 로컬 임시 테이블을 사용하여 더 나은 성능을 보일 수 있습니다.
트랜잭션 구현 간의 차이로 인해 임시 테이블도 다른 기능을 제공합니다. 결과적으로 SQL Server 임시 테이블을 Oracle "있는 그대로"(Oracle 18c의 개인 임시 테이블 구현 포함)으로 이동하면 성능이 저하될 뿐만 아니라 기능적으로도 잘못될 수 있습니다.
반면 Oracle에서 SQL Server로 이동할 때 트랜잭션 길이, 전역 임시 테이블의 가시성 범위, "구체화된" 힌트가 있는 CTE 블록의 성능에 주의를 기울여야 합니다.
두 경우 모두 마이그레이션된 코드에 임시 테이블이 포함되자마자 코드 번역이 아니라 시스템 재구현에 대해 이야기해야 합니다.
테이블 변수 입력
개발자는 아마도 다음과 같이 궁금해할 것입니다. 테이블 변수는 어떻습니까? Oracle에서 SQL Server로 마이그레이션 단계에서 변경해야 하거나 테이블 변수를 "있는 그대로" 이동할 수 있습니까? 글쎄, 이것은 코드에서 사용되는 이유와 방법에 따라 다릅니다.
임시 테이블과 테이블 변수를 모두 사용하는 방법을 살펴보겠습니다. Microsoft SQL Server부터 시작하겠습니다.
Transact-SQL의 테이블 변수 구현은 임시 테이블과 다소 일치하지만 자체 기능을 추가합니다. 주요 차이점은 테이블 변수를 함수 및 저장 프로시저에 매개변수로 전달하는 기능입니다.
이것은 이론이지만 실제 사용 고려 사항은 조금 더 복잡합니다.
깊게 자리 잡은 Oracle 배경에서 처음으로 심각한 Transact-SQL 최적화 작업을 맡았을 때 저는 이것이 다음과 같을 것이라고 예상했습니다. 테이블 변수 는 메모리에 있고 임시 테이블 은 디스크에 있습니다. 그러나 2014년까지의 Microsoft SQL Server 버전은 메모리에 테이블 변수를 저장하지 않는다는 것을 발견했습니다. 따라서 임시 변수에 대한 전체 테이블 스캔은 실제로 디스크에 대한 전체 테이블 스캔입니다. 고맙게도 SQL Server 2017 이상 버전은 임시 테이블과 테이블 변수 모두에 대해 선언적 메모리 최적화를 지원합니다.
그렇다면 임시 테이블을 사용하여 모든 것을 잘하거나 더 잘 수행할 수 있는 경우 Transact-SQL의 테이블 변수에 대한 사용 사례는 무엇입니까? 테이블 변수 의 키 속성은 변수 이므로 트랜잭션 롤백의 영향을 받지 않고 매개변수로 전달할 수 있습니다.
Transact-SQL 함수 는 매우 제한적입니다. 함수 의 작업은 일부 단일 반환 값을 반환하는 것이기 때문에 의도적 으로 부작용이 없습니다 . Transact-SQL은 SQL Server에서 테이블에 대한 모든 액세스가 암시적 트랜잭션 및 관련 트랜잭션 잠금을 생성하기 때문에 SELECT 조차 부작용으로 간주합니다. 즉, 함수 내부에서 기존 임시 테이블의 데이터에 액세스하거나 임시 테이블을 생성할 수 없습니다. 결과적으로 레코드 집합을 함수에 전달 해야 하는 경우 테이블 변수를 사용해야 합니다.
(전역) 임시 테이블 및 컬렉션 변수 (Transact-SQL 테이블 변수와 동일한 Oracle PL/SQL) 사용에 대한 Oracle의 고려 사항은 다릅니다. Oracle 컬렉션 변수는 메모리에 있는 반면 임시 테이블 은 임시 테이블스페이스에 있습니다. Oracle 기능은 영구 또는 임시 테이블에 대한 읽기 전용 액세스를 허용합니다. Oracle의 간단한 SELECT 는 리소스에 잠금을 설정하지 않습니다.
Oracle에서 컬렉션 변수와 임시 테이블의 사용 선택은 예상 데이터 양, 이 데이터를 보존해야 하는 기간, 메모리 대 디스크 할당 및 가용성을 기반으로 합니다. 또한 컬렉션 변수는 행 집합을 호스트 프로그램에 대한 출력으로 다시 가져오는 표준 방법입니다.
대부분의 SQL 구문 요소는 SQL Server와 Oracle 간에 매우 유사해 보이기 때문에 테이블 변수가 포함된 코드 블록을 SQL Server Transact-SQL에서 Oracle PL/SQL로 변환하는 것이 더 간단하고 구문적으로 용서되는 프로세스입니다. 기본 유효성 검사 테스트를 통과할 수 있지만 위에서 설명한 대로 임시 테이블 재구현 단계를 수행하지 않으면 기능적으로 올바르지 않습니다. 반면에 Oracle에서 SQL Server로 이동한 코드에는 구문적으로만 유효하기 위해 더 많은 수정 단계가 필요합니다. 기능적으로도 정확하려면 임시 테이블과 CTE를 사용하는 심층 사례를 해결해야 합니다.
내부 거래("중첩 거래")
Oracle에서 SQL Server로의 마이그레이션 문제와 관련하여 다음으로 살펴볼 주요 영역은 중첩 트랜잭션입니다.
임시 테이블과 마찬가지로 Transact-SQL 코드에 중첩 여부에 관계없이 트랜잭션이 포함되어 있거나 Oracle 코드에 중첩 트랜잭션이 포함되어 있는 경우 일반 코드 마이그레이션이 아니라 기능 재구현에 대해 이야기하고 있습니다.
먼저 Oracle 중첩 트랜잭션이 어떻게 작동하고 어떻게 사용하는지 살펴보겠습니다.
Oracle의 중첩 트랜잭션
Oracle 중첩 트랜잭션은 완전히 원자적이며 외부 범위와 독립적입니다. 일반 대화형 Oracle SQL 쿼리에서 중첩 트랜잭션은 실제로 사용되지 않습니다. 대화식 모드에서 Oracle과 함께 작업할 때 상태에 도달했음을 확인했을 때 변경 사항을 수동으로 커밋하면 됩니다. 롤백해야 할 수도 있는 마지막 단계(예: 불확실한 단계)를 수행할 때까지 아직 커밋할 수 없는 변경을 수행했지만 이미 수행한 작업을 보존하려면, 전체 트랜잭션을 커밋하거나 롤백하지 않고 롤백할 수 있는 안전 지점을 만듭니다.
그렇다면 중첩 트랜잭션은 어디에 사용됩니까? PL/SQL 코드에서. 보다 구체적으로 PRAGMA AUTONOMOUS_TRANSACTION 으로 선언된 자율 프로시저에서. 이는 이 코드가 호출될 때(명명된 저장 프로시저로 또는 익명으로) 트랜잭션이 이 코드를 호출한 트랜잭션과 독립적으로 커밋되거나 롤백됨을 의미합니다.
중첩 트랜잭션을 사용하는 목적은 호출 코드에 어떤 일이 발생하는지에 관계없이 자체 포함된 작업 단위를 커밋하거나 롤백하는 것입니다. 내부 트랜잭션이 커밋 되거나 롤백될 수 있는 경우 공유 리소스의 가용성을 확인(또는 예약)하는 데 사용됩니다(예: 객실 예약 시스템 구현). 커밋 전용 내부 트랜잭션의 주요 용도는 활동 모니터링, 코드 추적 및 보안 액세스 감사입니다(즉, 사용자가 변경하는 것이 허용되지 않았지만 시도한 경우).
SQL Server Transact-SQL 코드의 중첩 트랜잭션은 완전히 다릅니다.
SQL Server의 중첩 트랜잭션
Transact-SQL에서 내부 트랜잭션이 완전히 커밋되는지 여부는 가장 바깥쪽 트랜잭션에 따라 다릅니다. 내부 트랜잭션이 롤백된 경우 그냥 롤백됩니다. 그러나 내부 트랜잭션이 커밋된 경우 외부 범위 트랜잭션의 수준이 롤백되면 롤백될 수 있으므로 여전히 완전히 커밋되지 않습니다.
그렇다면 외부 트랜잭션을 롤백하여 커밋을 취소할 수 있는 경우 내부 트랜잭션의 용도는 무엇입니까? 대답은 로컬 임시 테이블의 사용 사례와 동일합니다. 리소스에 대한 잠금을 해제합니다. 차이점은 전역 잠금 해제가 아니라 바로 외부(직접 "상위") 트랜잭션 범위 내의 잠금이라는 것입니다. 복잡한 Transact-SQL 코드에서 외부 트랜잭션에 대한 내부 리소스를 해제하는 데 사용됩니다. 성능 최적화 및 리소스 관리 도구입니다.
Oracle과 SQL Server 내부/중첩 트랜잭션은 동작이 다르고 사용 사례가 완전히 다르기 때문에 한 플랫폼에서 다른 플랫폼으로 마이그레이션하려면 다시 작성하는 것뿐만 아니라 중첩된 트랜잭션 블록이 포함된 범위를 완전히 다시 설계해야 합니다. .
기타 요인
이러한 임시 테이블 및 트랜잭션 중심 고려 사항이 Oracle에서 SQL Server로의 마이그레이션에서 해결해야 하는 유일한 사항입니까? 그것들이 가장 중요할 수 있지만 분명히 다른 것들이 있으며, 각각은 다룰 가치가 있는 고유한 특징이 있습니다. 다음은 내가 가장 잘못 이해한 주제로 밝혀진 나머지 내용입니다.
- SQL Server의 ID 열
- Oracle의 시퀀스
- 오라클의 동의어
- 필터링된 인덱스
- 읽기 일관성(Oracle에서 SQL Server로만 해당)
- 마이그레이션 도구 사용
이 시리즈의 다음 부분은 특히 처음 세 가지를 탐구하면서 계속됩니다.
임시 테이블, 테이블/컬렉션 변수, 중첩 트랜잭션: 상위 3가지 마이그레이션 문제
임시 테이블, 테이블 변수/컬렉션 및 중첩 트랜잭션으로 시작했습니다. 이러한 트랜잭션은 변환 프로젝트에서 가장 일반적이고 명백한 실패 지점이기 때문입니다. Oracle Database 또는 Microsoft SQL Server의 중요하지 않은 시스템은 의심할 여지 없이 그 중 일부를 사용할 것이며 이러한 요소를 사용하는 것은 각 RDBMS 구현에 의한 트랜잭션 지원의 특정 설계와 매우 밀접하게 결합됩니다.
2부에서 계속 읽어보세요!