자율적인 트랜잭션 (AUTONOMOUS TRANSACTION, 독립적인 트랜잭션)
저는 늘 답변했듯이 오라클을 사용할 경우 autonomous trasaction 처리가 가능하지만 mssql에서는 제공을
하지 않아서 안된다고 이야기드렸는데, 왠지 그 날따라 찜찜하여 찾아보니 linked server를 사용하는 방법이
있어 공유드립니다.
[Autonomous Trasaction이란?]
* 블록에서 자신의 작업을 수행하기 위해서 그 블록 고유의 트랜잭션을 생성하는 경우에 해당하며 그 블록의
트랜잭션의 결과가 자신을 포함하거나 호출한 트랜잭션에의 상태에 의해 영향을 받지 않는 속성을 가지는
트랜잭션을 의미한다.
* 마스터 트랜잭션과는 lock, resource, commit 에 관련된 의존성을 가지지 않는다.
[SQL Server]
-- 1. 연결된서버추가
USE MASTER
GO
EXEC sp_addlinkedserver @server = N'loopback',@srvproduct = N' ',@provider = N'SQLNCLI', @datasrc = @@SERVERNAME
GO
EXEC sp_serveroption loopback, N'remote proc transaction promotion','false'
-- SQL Server 2008에서 추가된 옵션(분산트랜잭션사용안함)
go
EXEC sp_serveroption loopback, N'RPC OUT', 'TRUE' -- Enable RPC to the given server.
go
-- 2. 임시테이블생성
use tempdb
go
CREATE TABLE ErrorLogging (logTime DATETIME, msg VARCHAR(255))
CREATE TABLE TestAT (id INT PRIMARY KEY)
GO
-- 3. 프로시져생성
CREATE PROCEDURE usp_ErrorLogging
@errNumber INT
AS
INSERT INTO ErrorLogging VALUES (GETDATE(), 'Error ' + CAST(@errNumber AS VARCHAR(8)) +' occurred.')
GO
-- 4. 테스트하기(TestAT에 동일한PK를 입력하여 오류를 발생시킴)
DECLARE @ERROR AS INT
INSERT INTO TestAT VALUES (1)
BEGIN TRAN OuterTran
INSERT INTO TestAT VALUES (1) -- This will raise primary key constraint violation error
SELECT @ERROR = @@ERROR
IF @ERROR <> 0
BEGIN
BEGIN TRAN InnerTran
EXEC loopback.dbTest.dbo.usp_ErrorLogging @ERROR -- 여기서 연결된서버를 활용함
COMMIT TRAN InnerTran
ROLLBACK TRAN OuterTran
END
IF @@TRANCOUNT > 0
COMMIT TRAN OuterTran
GO
Result>
(1개 행이 영향을 받음)
메시지 2627, 수준 14, 상태 1, 줄 6
PRIMARY KEY 제약 조건 'PK__TestAT__3213E83F173876EA'을(를) 위반했습니다. 개체 'dbo.TestAT'에 중복 키를 삽입할 수 없습니다.
문이 종료되었습니다.
(1개 행이 영향을 받음)
-- 5. 로그가 쌓였는지 체크
SELECT * FROM TestAT
SELECT * FROM ErrorLogging
Result>
[Oracle] (간단히 문법만 공유드립니다. ^^)
declare
pragma autonomous_transaction;
begin
commit; -- 자율적인 트랜잭션은 반드시 commit/rollback으로 종료해야 한다.
end;
[출처] (테이블 변수를 활용한 방법 정리되어 있으니 관심 있는 분은 원문 참고 부탁드립니다.)
http://blogs.msdn.com/b/sqlprogrammability/archive/2008/08/22/how-to-create-an-autonomous-transaction-in-sql-server-2008.aspx
DB 서버 이전 시 체크리스트 (SQL Server 기준)
DB서버를 신규 장비로 교체하거나 2대를 1대로 합치는 작업을 종종하는데요, 자주하는 작업이 아니라서, 조금씩 놓치는 부분이 발생하게 됩니다.
팀에서 DB서버 이전시 챙겨야 하는 작업에 대해서 함께 정리해 보았습니다.
| 구분 | 항목 | 체크 |
| 사전 작업 | SQLIOSim 테스트를 통하여 신규 장비에 물리적인 결함이 없는지 확인(신규장비로 이전시) | |
| 작업 대상서버에서 이전 작업시 수행되는 배치나 백업 작업이 있는지 확인(부하 / 잠금 등) | ||
| 데이터를 이전 받는 서버의 디스크 공간은 충분한지 확인 필요 (백업본 보관도 고려 필요) | ||
| 통계 시스템 등 해당서버에서 데이터를 가지고 가는 부분이 있다면 해당 담당자에게 공유 | ||
| 이전 준비 작업 (이전해야 할 내용 확인/이관, 신규 장비일 경우 추가 설정 내용) |
DTS 또는 SSIS 작업 이관 | |
| hosts 파일에 등록된 내용 확인 및 이관 | ||
| 서버/클라이언트 네트워크 (alias 등록하여 사용중인 내용) 내용 확인 및 이관 | ||
| 연결된 서버 설정 확인 및 이관 | ||
| DB 계정 설정 확인 및 이관 | ||
| 윈도우 예약작업 확인 및 이관 | ||
| MSDTC 설정 (분산 트랜잭션 사용중인 경우) 확인 및 이관 | ||
| execute 로 실행되는 동적쿼리가 있을 경우 그에 대한 실행권한 확인 | ||
| IPSec 확인 및 이관 | ||
| 64bit 장비의 경우 플랜캐시가 커지는 현상이 있어 adhoc 지우는 Job 추가 | ||
| 서비스팩 현재 서비스중인 서버까지 올려놓을 것 | ||
| Default 정렬셋이 동일한지 확인 (사용자 DB만 이관할 경우 문제가 되어질 수 있음.) | ||
| 임의쿼리에 대한 변경을 할 경우 확인 및 변경 (확인 방법 : 프로필러 + 메모리 내의 Ad-Hoc 플랜캐시영역 확인) |
||
| 이전 작업 (작업 당일 진행 순서) |
(현업 또는 관련팀) 서비스 점검 상태 확인 | |
| (복제가 있었다면) 복제 정지 / 삭제 | ||
| 예약된 작업 정지 (이 서버 및 이 서버와 연결된 타 서버) | ||
| (현업 또는 관련팀) DB에 더 이상 데이터 변경이 없을 때까지 대기, sp_who2 등으로 접속자 확인 |
||
| 풀 백업 후 장비간 복사 | ||
| (시스템담당자) (기존 장비 대체인 경우) 호스트 이름 및 IP 변경 요청 | ||
| 리스토어 및 DB 액세스 계정 등 확인 & 설정 | ||
| (복제가 있었다면) 재구성 | ||
| SQL Agent 실행여부 확인, 설정 점검('자동 실행') 및 예약된 작업 시작 | ||
| 접속 테스트, Application 테스트 진행 | ||
| (현업 또는 관련팀) 서비스 오픈 확인 | ||
| 작업 후 체크 | 모니터링 설정하기 (개별적으로 모니터링 툴이 있는 경우) | |
| DB 및 트랜잭션 백업 설정 확인 | ||
| OS 예약 작업은 이전되었으며 동작하고 있는가? | ||
| DB 모드 (특히 로그) 및 호환성 모드 등의 설정은 적절한가? 로그 파일 크기는 적절한가? | ||
| 2차 백업 구성 확인: 백업 폴더가 있는 드라이브 체크, 백업용 공유 폴더 설정 체크, | ||
| IP / 호스트명이 변경되었다면 백업 서버 설정도 확인 후 변경이 필요함 | ||
| 작업 후 모니터링 | 성능상의 이슈가 없는지 체크(profiler로 duration, cpu, pagelookup으로 모니터링) | |
| perfmon로 주요지표에 대해서 실시간으로 모니터링 (pagelookup, cpu, batchrequest, lock, latch, disk 사용량 등) |
||
| 시스템 관련 작업 | 64bit 장비의 경우 원격에서 파일 복사시 메모리 뺏김 현상이 있으니 이 부분에 대한 확인 (동적 캐시 관리 서비스 설치, 또는 2차 백업을 끌어가기가 아닌 밀어넣기로 변경) |
Include Client Statistics 안내
EM 툴의 클라이언트 통계 기능 소개 드립니다.
DMV나 프로필러로는 서버의 성능 부하를 확인할 수 있습니다. 네트워크 비용이나 클라이언트에서 데이터 요청 후,
결과를 받는데까지 어디서 얼만큼의 비용이 발생하였는지 확인하기 위해서 해당 기능을 활용하면 좋을 듯 합니다.
개별 실행결과를 바로 비교하여 보여주기 때문에 매우 유용하게 활용할 수 있을 것 같습니다. 메뉴는 Query 메뉴 하단에
존재하고, 버튼은 아래의 이미지에 빨강색 동그라미 친 버튼입니다.
다음에는 진짜 SQL Server 2011의 새로 나온 Contained Databases에 대해서 소개하도록 하겠습니다.
