Azure SQL Managed Instance 에서 SQL Agent Job history 기록 보관하기

 

·         Version : Azure SQL

 

Azure SQL Managed Instance Azure 클라우드에서 호스트되는 완전히 관리되는 SQL Server 이다. Managed Instance 많은 SQL Server기능을 제공하지만 가지 제약이 있다. 제약사항 하나는 SQL Agent작업 실행의 히스토리 기록을 변경할 없다는 것이다. 이번 포트스에서는 제약 사항을 다른 방법으로 우회하여 해결하는 방법 하나를 소개한다.

 

Managed Instance SQL Agent 작업당 100개의 히스토리를 기록하며,  1000개의  히스토리 기록을 유지할 있다. 아래 프로시저를 실행하면 jobhistory_max_rows_per_job열에서 정보를 확인할 있다.

exec msdb.dbo.sp_get_sqlagent_properties

 

Azure SQL Managed Instance SQL Agent 히스토리를 기록하는 정보 값을 기본 레지스트리 값에 저장하므로 SQL Agent등록 정보를 변경할 없다. 그래서 SQL Agent 작업 히스토리에 대한 보존 정책 (작업 100개의 히스토리 또는  전체 히스토리 최대 1000개의 기록) 고정시켯다. 일부 직업의 경우 오래된 작업 기록을 확인하지 있다. 따라서 이러한 작업 히스토리 정보를 장기적으로 또는 삭제되기 이전에 정보를 보존하려면 sysjobhistory 테이블에서 정보를 다른 테이블에 저장해야 한다.  아래 예제를 통해서 어떻게 저장하는지 살펴본다.

 

Azure SQL Managed Instance Job history 테이블의 변경사항(삭제, 업데이트) 기록하기 위한 테이블을 생성하고 기존 테이블에 변경 사항이 있을때 버전 배치작업을 사용하여 히스토리를 새로운 테이블을 기록한다. sysjobhistory 테이블의 변경 사항을 다른 테이블(sysjobhistoryall) 저장하도록 변환하려면 아래 스크립트를 적용한다.

ALTER TABLE [msdb].[dbo].[sysjobhistory]

ADD StartTime DATETIME2 NOT NULL DEFAULT ('19000101 00:00:00.0000000')

GO

 

ALTER TABLE [msdb].[dbo].[sysjobhistory]

ADD EndTime DATETIME2 NOT NULL DEFAULT ('99991231 23:59:59.9999999')

GO

 

ALTER TABLE [msdb].[dbo].[sysjobhistory]

ADD PERIOD FOR SYSTEM_TIME (StartTime, EndTime)

GO

 

ALTER TABLE [msdb].[dbo].[sysjobhistory]

ADD CONSTRAINT PK_sysjobhistory PRIMARY KEY (instance_id, job_id, step_id)

GO

 

ALTER TABLE [msdb].[dbo].[sysjobhistory]

SET(SYSTEM_VERSIONING = ON (HISTORY_TABLE = [dbo].[sysjobhistoryall], DATA_CONSISTENCY_CHECK = ON, HISTORY_RETENTION_PERIOD = 1 MONTH))

GO

 

스크립트를 살펴보면 PK_sysjobhistory라는 기본 키가jobhistory 테이블에 추가되고 변경사항을  추적하는데 필요한 개의 열을 추가한 것을 있다. 작업이 완료되었으면 Managed Instance 기록 정리 작업을 실행하면 변경 사항이 보존되는지 여부를 테스트 있다. 아래 스크립트르 차례로 실행하여 시뮬레이션 있다.

EXEC msdb.dbo.sp_purge_jobhistory

 

기존 sysjobhistory 테이블에서 작업 기록이 삭제되면 sysjobhistoryall 테이블에서 삭제된 작업 기록 확인할 있다.

select * from msdb.dbo.sysjobhistoryall

 

 

 

2018-10-08 / Sungwook Kang / http://sqlmvp.kr

 

Azure SQL, Managed Instance, Azure SQL Agent, jobhistory, sysjobhistory, SQL Agent

Azure VM에서 SQL Server 대한 저장소 구성 지침

 

·         Version : Azure VM

 

Azure Virtual Machines 에서 SQL Server 사용할 경우 VM SQL Server 완벽하게 제어하고 SQL Server 작업 부하를 위한 가장 간단한 클라우드 마이그레이션 경로를 제공한다.

·         SQL Server on Virtual Machines :  https://azure.microsoft.com/en-us/services/virtual-machines/sql-server/

 

SQL IaaS Extension 기본적으로 Azure Market Place 통해 생성된 SQL VM 설치 된다. SQL IaaS Extension 백업 관리, 자동 보안 패치, 연결 구성, AKV 통합 최적화된 스토리지 구성 기능을 제공한다. Azure Market SQL Server 이미지는 작업 부하 유형에 따라 SQL Server 팀이 성능에 맞춰 조절한다. 그러나 SQL Server 업무상 중요한 서비스를 하는 경우 최적의 저장소 구성은 특정 작업 부하의 I/O 특성 요구 사항에 따라 다르다. 이번 포스트는 Azure IaaS에서 수행된 테스트를 기반으로 SQL Server 팀의 지침을 설명한다.

 

[프리미엄 저장소 적절한 크기의 VM 선택]

프리미엄 스토리지는 낮은 대기 시간과 일관된 높은 I/O 성능을 제공하도록 설계되었다. 캐시되지 않은 읽기는 평균 4ms 대기 시간으로 작동하고 캐시되지 않은 쓰기는 VM 평균 2ms 대기 시간 최대 80,000 IOPS 작동한다. 또한 예측할 없는 작업에서도 호스트의 SSD 기반 BLOB 캐시 기술은 대기 시간이 1ms 미만이며 최대 160,000 IOPS 제공한다. 따라서 일관된 I/O 성능과 높은 성능으로 인해 Azure VM에서 SQL Server 서비스는 프리미엄 스토리지를 사용할 것을 권장한다.

프리미엄 스토리지는VM 따라IOPS, 대역폭 연결할 있는 디스크 수에 대한 크기 제한 성능 사양이 있다. 예를 들어 표준 DS14_v2 VM크기는 64개의 프리미엄 디스크를 사용하여 최대 51,200 IOPS또는 768MBps 디스크 처리량을 제공하며 로컬 SSD 576GB BLOB 캐시를 사용하여 최대 64,000 IOPS또는 512MBps 처리량을 제공한다. 아래 링크에서 모든 VM 크기에 할당된 제한 리소스를 확인할 있다.

·         Memory optimized virtual machine sizes : https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes-memory

 

[읽기가 많은 작업 경우 읽기 전용 캐시를 사용하여 처리량을 향상]

프리미엄 스토리지를 지원하는 Azure VM 캐싱을 위해 가상 시스템 RAM 로컬SSD 조합을 사용하는 다중 계층 캐싱 기술을 사용한다. SQL 서버 작업 부하를 가져오는 읽기 전용 캐시 이득은 작업량 특성에 따라 다르다.

아래의 테스트 결과는  10-P30 디스크가 장착된 DS14_v2 VM에서 최대 50,000 IOPS 사용하는 OLTP 작업 부하로 테스트했다. 데이터 로그 파일은 테스트를 위해 10-P30 디스크에서 동일한 저장 영역 풀에 있다.

읽기 80%, 쓰기 20%, 약간의 tempdb 사용하는 OLTP 작업 부하의 경우 캐시가 없는 동일한 구성에 비해 모든 프리미엄 디스크에 대해 읽기 전용 캐시가 활성화 되었을 처리량이 42% 증가했다. 읽기 50%, 쓰기 50%, 약간의 tempdb 사용하는 OLTP 작업 부하의 경우 캐시가 없는 구성에 비해 읽기 전용 캐시의 처리량이 25% 증가했다.

 

[데이터 로그 파일의 분리]

데이터 로그 파일을 분리한 별도의 저장 영역을 사용하여 읽기 전용 캐시의 이득을 최적화 하는 것이 좋다. 캐시가 없는 프리미엄 스토리지 디스크에 로그파일을 호스팅 하면 읽기 작업에 대해서는 호스트 BLOB 캐시의 사용 가능한 공간과 처리량이 절약되어 이점을 극대화 한다. 특히  SQL Server 데이터 파일을 호스팅하는 저장소에 포함될 프리미엄 저장소 디스크에는 읽기 전용 캐시를 사용하는 것이 좋다. 예를 들어 데이터 파일에 IOPS 요구사항이 30,000 이라면 모든 디스크에 RO 캐시를 활성화 6 - P30 디스크 또는 4 - P50 디스크를 스트라이핑 하는것이 좋다.

 

로그 파일을 호스팅 하는 저장소에 포함될 프리미엄 저장소 디스크는 읽기전용 캐시를 구성하지 않는것이 좋다. 또한 로그 파일의 디스크 처리량 요구 사항이 7500 IOPS 미만인 경우 단일 P50 디스크로 IOPS 달성할 있으므로 로그 파일에 대한 저장소 풀이 필요하지 않다. 특히 작은 호스트 BLOB 캐시가 있는 VM에서SQL Server 작업의 부하가 경우 로그 파일과 데이터 파일을  분리하였을때 테스트에서 상당한 처리량 증가를 보였다. 평균적인 예로 데이터를 호스팅하는것과 비교하여 RO 캐시가 있는 별도의 스토리지 풀에서 호스트되는 캐시 데이터 파일이 없는 스토리지 풀에서 로그 파일을 호스팅할  쓰기 50% 비율의 OLTP 테스트 경우 처리량이 35%  증가했다.  

 

[올바른 크기의 VM 선택  로컬 SSD tempdb 배치]

VM 크기에 따라 로컬 SSD BLOB 캐시에 대해 최대 대역폭, 최대 처리량 크기 제한이 가능한 스토리지 용량을 정의한다. Tempdb 사용량이 높은 업무용 SQL Server 경우 로컬 SSD temp db 호스팅하면 작업 부하 성능과 처리량에 영향을 준다. VM 로컬 SSD 대한 크기, 처리량 대역폭 제한이 tempdb IO 요구 사항을 허용해야 한다. 그리고  VM 호스트 BLOB 캐시 스케일 한계는 로컬 SSD 읽기 호스트 BLOB 캐시 읽기가 발생하는 읽기 활동을 허용할 만큼 충분히 커야 한다.

최대 로컬 디스크 + SSD 캐시 성능한계는 로컬 SSD 읽기 쓰기 작업 SSD 캐시의 읽기 작업에 대한 최대 작업을 정의한다. tempdb 로컬 SSD 디스크에 배치되고 SQL Server 데이터 파일을 호스팅하는 프리미엄 디스크에 RO 캐시가 사용되는 경우 tempdb에서 구동되는 읽기 + 쓰기 IOPS RO 캐시의 읽기 IOPS 공유된다. 예를들어 표준 DS14_v2 임시 디스크의 크기가 224GB이고 호스트 캐시가 576GB이며 로컬 캐시의 tempdb 활동과 함께 읽기 캐시에 사용할 있는 최대 처리량은 64,000 IOPS 524MB/s이다.

 

Storage Optimized L-Series 또는 Memory Optimized M-Series 로컬 SSD 최대 14TB이고 메모리가 최대 4TB 이다. 이러한 서버는 고부하의 SQL Server 작업을 처리하는데 가장 적합하다. 적절한 VM 크기 선택 구성으로 Azure VM SQL Server 작업 부하에 대해 미션 크리티컬 성능을 얻을 있다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/09/25/storage-configuration-guidelines-for-sql-server-on-azure-vm/

 

 

2018-10-02 / Sungwook Kang / http://sqlmvp.kr

 

Azure SQL, Azure VM, BLOB Storage, local SSD, local cache, SQL Server On Azure VM, SQL Server on Azure

Azure SQL 도입된 새로운 집계 함수 APPROX_COUNT_DISTINCT

 

·         Version : Azure SQL

 

다양한 서비스를 운영하다보면 서비스에 따른 중요 포인트가 조금씩 다른 경우가 있다. 예를들어 정확한 숫자(결과값) 중요시하는 서비스(일일 매출, 재고량 ) 있으며, 빠른 응답을  (실시간 데이터 사용량, DB 전체 데이터 ROW 등의 대시보드 ) 중요시하는 서비스가 있다. 물론 응답성을 중시한다고 해서 데이터가 틀리면 안되겠지만 허용되는 오차범위에서 최대한 빠른 응답이 필요할 때가 있다.  이번 포스트에서는 매우 데이터 세트 전체에 대한 집계에 대해 빠른 응답을 수있는 Azure SQL APPROX_COUNT_DISTINCT 대해서 다루어 본다.

 

Azure SQL 새로운 집계함수인 APPROX_COUNT_DISTINCT NULL 아닌 고유한 값이 있는 그룹의 대략적인 수를 반환한다. 함수는 데이터 시나리오에 사용하도록 설계되었으며 아래 조건에 최적화 되어 있다.

·         수백만행 이상의 데이터 세트에 대한 액세스

·         많은 수의 고유한 값을 가진 열의 집계

이러한 조건을 가정하면 대부분의 작업에서 정확도 오차는 2%이내가 된다. 또한  APPROX_COUNT_DISTINCT 정확도 오차는 COUNT DISTINCT에서 20%이상 되어서는 안된다.

 

APPROX_COUNT_DISTINCT 고유 카운트를 계산할때, COUNT DISTINCT 보다 훨씬 적은 메모리를 사용한다. 이러한 이유 때문에 데이터 집합에 수십억개의 행이 있더라도 디스크를 사용하지  않고 메모리에서 계산을 수행할 가능성이 훨씬 크다. 일반적으로 COUNT DISTINCT 경우 메모리가 부족하면 tempdb 사용하므로 성능 저하를 초래(일반적으로 디스크가 가장 느리기 때문)한다.  APPROX_COUNT_DISTINCT tempdb 사용하지 않고 내부적 알고리즘을 사용하므로 결과적으로 APPROX_COUNT_DISTINCT COUNT DISTINCT보다 훨씬 빠르게 실행된다.

 

아래 그림은 일반적인 COUNT(DISTINCT ()) 사용한 것과 APPROX_COUNT_DISTINCT 사용한 경우이다.

[DISTINCT COUNT]

 


[APPROX_COUNT_DISTINCT]

 


COUNT DISTINCT 경우 정렬 연산자가 추가되어 있으므며 APPROX_COUNT_DISTINCT 경우 Hash match 대한 Stream Aggregate 대체 된다는 것을 있다. 또한 계획은 모두 동일한 클러스터된 인덱스 검색을 사용하며 COUNT DISTINCT 경우 검색에 95% 비용을 사용하지만, APPROX_COUNT_DISTINCT 경우99% 검색에 사용되는 것을 있다. 뜻은 전통적인 COUNT DISTINCT 작업은 외부의 스캔 처리 작업이 많다는 것을 의미한다.

 

APPROX_COUNT_DISTINCT 사용할 경우 실행 계획에 아래와 같은 연산자를 확인할 있다.

<ScalarOperator ScalarString=”APPROX_COUNT_DISTINCT_CONVERT([globalagg1004])”>

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/07/16/approximate-count-distinct-enters-public-preview-in-azure-sql-database/

 

 

2018-09-10 / Sungwook Kang / http://sqlmvp.kr

 

Azure SQL,  count distinct, APPROX_COUNT_DISTINCT

Azure SQL 데이터베이스 소유권 체인

 

·         Version : Azure SQL

 

Azure SQL Database Managed Instance에서는 SQL Server 동일한 방식으로 데이터베이스간 쿼리를 실행할 있다. 또한 데이터베이스간 소유권 체인을 지원한다. 이번 포스트에서는 Managed Instance 소유권 체인에 대해서 다루어 본다.

 

데이터베이스간의 소유권 체인을 사용하면 명시적 권한이 객체에 부여되지 않더라도SQL 인스턴스의 다른 데이터베이스에 있는 객체에 액세스 있다. DB_CHAINING옵션이 데이터베이스에서 설정되어 있는 경우 동일한 소유자로 다른 데이터베이스에 액세스할 있다.  

DB_CHAINING is disabled by default on new databases because you need to be aware what it exactly do and does it violates some security policy in your system before you explicitly enable it.

 

DB_CHAINING 시스템의 일부 보안 정책을 위반하므로 기본적으로 비활성화 되어있다.

 

여러 데이터베이스의 여러 오브젝트에서 동일한 소유자가 있고 오브젝트에 액세스하는 스토어드 프로시저가 있는 경우 프로시저가 액세스해야하는 모든 오브젝트에 대한 액세스 권한을 GRANT 필요가 없다. 프로시저와 오브젝트의 소유자가 동일한 경우 프로시저에 대한 GRANT 권한을 부여할 있으며 데이터베이스 엔진은 프로시저가 동일한 소유자를 공유하는 다른 모든 개체에 액세스 있게 한다.

아래 예제에서는 데이터에 액세스하는데 사용되는 동일한 소유자 로그인이 있는 개의 데이터베이스를 생성한다. PrimaryDatabase DataTable 이라는 테이블을 가지고 있으며,  SecondaryDatabase PrimaryDatabase DataTable 에서 데이터를 읽는 저장프로시저를 갖는다. 저장 프로시저를 실행하기 위해 로그인이 부여되었지만 테이블 데이터를 읽지는 못하였다.

-- Create two databases and a login that will call procedure in one database

CREATE DATABASE PrimaryDatabase;

GO

CREATE DATABASE SecondaryDatabase;

GO

CREATE LOGIN TheLogin WITH PASSWORD = 'Very strong password!'

GO

 

-- Create one database with some data table,

-- and another database with a procedure that access the data table.

USE PrimaryDatabase;

GO

CREATE PROC dbo.AccessDataTable

AS

BEGIN

SELECT COUNT(*) FROM SecondaryDatabase.dbo.DataTable;

END;

GO

CREATE USER TheUser FOR LOGIN TheLogin;

GO

GRANT EXECUTE ON dbo.AccessDataTable TO TheUser;

GO

 

USE SecondaryDatabase;

GO

SELECT * INTO dbo.DataTable FROM sys.objects;

GO

CREATE USER TheUser FOR LOGIN TheLogin;

GO

 

저장 프로시저를 사용하여 데이터를 읽으려고 하면 로그인에 테이블에 대한 GRANT 권한이 없기 때문에 오류가 발생한다.

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ;

GO

-- Msg 229, Level 14, State 5, Line 34

-- The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

저장 프로시저를 사용하여 테이블에서 데이터를 읽으려고 하는 경우에도 동일한 문제가 발생한다.

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ;

GO

--Msg 229, Level 14, State 5, Procedure dbo.AccessDataTable, Line 5 [Batch Start Line 65]

--The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

사용자에게 프로시저를 실행할 있는 권한은 있지만 데이터베이스 엔진은 해당 로그인에 대해 SecondaryDatabase 테이블에서 읽을 있는 액세스 권한이 없으므로 쿼리를 차단한다. 아래 스크립트는 데이터베이스 소유권 체인을 활성화 한다.

ALTER DATABASE PrimaryDatabase SET DB_CHAINING ON;

GO

ALTER DATABASE SecondaryDatabase SET DB_CHAINING ON;

GO

 

저장 프로시저를 통해 테이블에 다시 액세스하면 결과가 표시된다.

EXECUTE('EXEC PrimaryDatabase.dbo.AccessDataTable') AS LOGIN = 'TheLogin' ;

 

Managed Instance / Database Engine 저장 프로시저와 테이블이 동일한 소유자를 가지며 DB_CHAINING 켜져있기 때문에 테이블에 대한 액세스를 허용한다. 그러나 해당 로그인에 대해 직접적인 테이블에 액세스하는 권한은 없으므로 직접 테이블에 액세스 수는 없다.

EXECUTE('SELECT * FROM SecondaryDatabase.dbo.DataTable') AS LOGIN = 'TheLogin' ;

GO

--Msg 229, Level 14, State 5, Line 54

--The SELECT permission was denied on the object 'DataTable', database 'SecondaryDatabase', schema 'dbo'.

 

데이터베이스 소유권 체인이 유용할 뿐만 아니라 보안 관점에서 예기치 않은 동작이 발생할 있으므로 해당 옵션을 활성화 할때는 신중함이 필요하다.

 

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/08/17/database-ownership-chaining-in-azure-sql-managed-instance/

 

 

 

2018-09-06 / Sungwook Kang / http://sqlmvp.kr

 

Azure SQL,  Managed Instance, SQL, Azure SQL Database, SQL Server Security, DB_CHAINING


+ Recent posts