'SQL Server' 카테고리의 글 목록

SQL Server

SQL Server Failover Cluster 구성 2023.07.16
SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기 2022.10.31 (1)
SQL Server 에서 AWS S3에 직접 백업하기 2022.10.28 (2)
SSRS 리포트에서 이미지 추가하기 2020.04.17 (1)
SSRS 보고서 실행 로그 및 감사(Audit) 2020.04.08
VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 2020.03.04
SQL Server 복원 성능 최적화 2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 2020.02.28
SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기) 2020.02.12
SSRS에서 표의 값에 따라 폰트 컬러 변경하기 2020.02.11
Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여 2019.11.09
Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙 2019.11.07
Azure SQL Database 보안 기능 개요 2019.10.17
Azure SQL Database의 서버리스 구매 모델 2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 2019.10.10
Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델 2019.10.10
Azure SQL Database 서비스 2019.10.10
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 2019.09.24
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 2019.09.21
SQL Server 2019 Log Writer Workers 2019.09.19
SQL Server Login Timeout 디버깅 2019.09.19
SQL Server Worker Thread 기본 계산 2019.09.18
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) 2019.09.17
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 2019.09.14
BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 2019.09.13
삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) 2019.09.12 (1)
QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 2019.09.11
ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 2019.09.10

SQL Server Failover Cluster 구성

2023. 7. 16. 22:16

l Version : SQL Server 2019

SQL Server의 고가용성 중 하나인 SQL Server Failover Cluster (장애조치 클러스터) 인스턴스를 구성하는 방법에 대해서 알아본다. SQL Server 장애조치 클러스터를 구성하기 위해서는 Windows Failover Cluster이 먼저 구성되어 있어야 한다. 그리고 디스크 또한 공유 디스크를 사용이 필수이다. 이번 포스트에서는 Windows Failover Cluster 구성이 완료 되어 있다는 가정하에 SQL Server Failover Cluster만 구성하는 방법에 대해서 설명한다.

이번 포스트에서 구성하려는 장애조치 클러스터의 구성은 아래 그림과 같다. DB01 은 액티브 서버로 운영되고, DB02는 패시브 서버로 운영되며 비상시 장애조치 되어 역할이 변경된다. SQL Server 운영에 필요한 설치 파일 및 사용자 데이터베이스 파일은 공유 디스크에 저장되어 운영 된다.

[Master 서버, 클러스터 설치]

DB1(Master) 서버에서 SQL Server 설치 파일을 실행한 다음 [설치]-[SQL Server 장애조치(Failover) 클러스터 새로 설치]를 클릭 한다.

사용 조건 단계에서 라이선스 계약에 동의함을 선택하고 [다음]을 클릭한다.

Microsoft 업데이트 단계에서는 업데이트를 체크하지 않고 [다음]을 클릭한다. 업데이트를 클릭하게 되면 설치 과정에서 업데이트가 발생하여 설치 시간이 오래 걸릴 수 있으니, 업데이트는 모든 설치 이후에 별도로 진행 할 수 있도록 한다.

장애 조치(Failover) 클러스터 설치 규칙 단계에서는 클러스터 설치에 적합한 환경인지 등을 검사하게 된다. [다음]을 클릭한다.

기능 선택 단계에서는 사용자에게 필요한 SQL Server 기능을 선택 한다. 설치할 기능을 선택하고 [다음]을 클릭한다.

인스턴스 구성 단계에서는 SQL Server 클러스터에 사용할 네트워크 이름을 입력한다. 이 이름은 이후 다른 서버가 클러스터에 조인할 때 식별할 수 있는 이름이기에 의미 있는 이름으로 지정할 수 있도록 한다. 이름을 입력하였으면 [다음]을 클릭한다.

클러스터 리소스 그룹 단계에서는 리소스 그룹에 포함할 인스턴스를 선택한다. 아래 그림에서는 기본 인스턴스로 생성하였기에 그룹 이름 또한 기본 인스턴스 이름으로 보여진다. 리소스 그룹을 선택하고 [다음]을 클릭 한다.

클러스터 디스크 선택 단계에서는 SQL Server에서 사용할 공유 디스크를 선택한다. 이 단계에서 디스크 목록이 나타나지 않는다면 Windows Failover Cluster를 구성할 때 공유 디스크에 대한 설정을 잘못된 것이므로 윈도우 클러스터부터 다시 확인 할 수 있도록 한다. 디스크 선택을 하고 [다음]을 클릭한다.

클러스터 네트워크 구성 단계에서는 SQL Server Cluster에 사용할 IP를 입력한다. 여기에 입력한 IP는 이후 사용자가 접속할 때 사용되는 Cluster IP이다. 클러스터 IP로 접속을 해야 Failover가 발생하였을 때, 활성 된 SQL 서버로 자동으로 연결해 준다. 중복되지 않은 클러스터 IP입력을 하였다면 [다음]을 클릭한다.

서버 구성 단계에서는 SQL Server 서비스에서 사용할 계정과 암호를 입력한다. 이때 도메인 계정을 사용할 수 있도록 한다. 계정과 이름을 입력하였으면 [다음]을 클릭 한다.

데이터베이스 엔진 구성 단계에서는 SQL Server의 접근 방법 및 SQL 관리에 필요한 sa의 비밀번호를 입력한다.

데이터베이스 엔진 구성 단계에서 [데이터 디렉터리] 탭에서는 SQL Server에 필요한 데이터가 위치할 디렉터리를 지정한다. 이 디렉터리 경로는 앞에서 추가한 클러스터 공유 디스크의 위치를 지정한다.

데이터베이스 엔진 구성 단계에서 [TempDB] 탭에서는 SQL Server TempDB가 사용할 디렉터리 경로를 지정한다. 이 디스크는 로컬을 사용해도 되지만, 보통 클러스터 구성시 로컬 디스크는 많은 공간을 할당하지 않기 때문에 용량이 큰 클러스터의 디스크로 지정하였다. 지금까지 구성이 완료되었으면 [다음]을 클릭 한다.

설치 준비 단계에서는 지금까지 설정한 요소들을 정리해서 보여주며 [설치]를 클릭하여 설치를 진행할 수 있도록 한다.

완료 단계에서는 정상적으로 설치가 완료된 것을 보여주며 [닫기]를 클릭 한다.

설치가 완료된 다음, 앞에서 지정한 디렉터리 경로에 SQL Server에 필요한 파일이 생성된 것을 확인할 수 있다.

[Passive서버, 클러스터 노드 추가]

지금까지 Master 서버에서 SQL 클러스터링에 대한 설치를 진행하였고, 이후 단계는 클러스터 노드에 추가할 SQL Server를 설치한다. 이후 과정은 Passive 서버에서 진행하기 때문에 착오가 없도록 한다.

SQL Server 설치 파일을 실행하여 [설치] – [SQL Server 장애 조치(Failover) 클러스터에 노드 추가를 선택 한다.

이후 설치 과정은 이전과 동일한 부분이 있어 그림으로만 대체한다. 필요한 부분에서는 설명을 추가 한다.

앞에서 생성한 인스턴스 클러스터 그룹의 이름을 선택하면 클러스터 노드의 이름을 자동으로 불러와서 보여준다.

클러스터 IP또한 이미 클러스터 구성 시 입력한 정보를 그대로 보여준다.

인스턴스에서 사용할 계정과 암호를 입력한다. 도메인 계정으로 사용할 수 있도록 한다.

클러스터 노드에 추가가 완료된 다음 SQL Server에 접속 테스트를 진행한다. 이때 각 노드의 로컬IP가 아닌 SQL Server Cluster IP를 입력하도록 한다. 로컬IP로 접속할 경우 해당 노드의 인스턴스에 직접 로그인 하기 때문에 Failover가 발생시 활성 서버로 자동으로 연결되지 않는다. 클러스터 IP를 사용할 경우에는 활성 노드로 자동으로 연결해 준다.

서버 이름을 조회해 보면 로컬의 서버 이름이 아닌 클러스터 이름을 보여준다. 그리고 SQL Server 의 클러스터 상태를 조회해 보면 현재 구성되어 있는 모든 노드의 이름을 보여주며 어떤 노드가 활성 상태인지를 보여준다.

테스트를 위해 TestDB를 생성해 보았는데, 클러스터를 구성할 때 지정했던 디렉터리 경로에 사용자 데이터베이스 파일이 잘 생성되는 것을 확인할 수 있다.

SQL server 장애조치 클러스터를 구성하였을 때, 하나의 서버에 장애가 나도 다른 서버가 그 역할을 대신할 수 있어 가용성을 높일 수 있다. 노드의 개수는 최대 4개 까지 가능하다. 물론 가용성을 높이기 위한 기술로는 클러스터링 외에도 복제, 미러링, AlwaysOn 기술등이 있다. 사용자 환경을 고려하여 최적의 솔루션을 선택하여 가용성을 확보 할 수 있도록 한다.

2023-07-16/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, SQL Failover Cluster, SQL 장애조치, SQL 클러스터링, 장애조치, SQL 고가용성, 페일오버 클러스터

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기 (1)	2022.10.31
SQL Server 에서 AWS S3에 직접 백업하기 (2)	2022.10.28
VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0)	2020.03.04
SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28

SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기

2022. 10. 31. 00:38

SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기

l Version : SQL Server

SQL Server 데이터베이스에는 모든 트랜잭션과 각 트랜잭션에 의해 적용된 데이터베이스 수정 내용을 기록하는 트랜잭션 로그가 있다. 트랜잭션 로그는 데이터베이스의 매우 중요한 요소로 시스템 오류가 발생한 경우 데이터베이스를 일관된 상태로 다시 전환하려면 이 로그가 필요하다.

l 트랜잭션 로그 아키텍처 : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

데이터베이스에 대량의 쓰기 작업이 발생하면 트랜잭션 로그에는 변경사항을 기록하기 위해 오버헤드가 발생하면서 로그 쓰기 작업(Write Log)이 지연되는 경우가 발생한다. 로그 모니터링을 통하여 Write Log에 지연이 발생할 경우 DELAYED_DURABILITY 옵션을 사용하여 해당 문제를 개선할 수 있다. 이 옵션을 사용할 경우 트랜잭션 로그의 커밋을 기다리지 않기 때문에 워크로드의 대기 시간이 줄어들어 성능 개선은 있지만 트랜잭션 데이터를 소실할 수 있기 때문에 주의해서 사용해야 한다. 이번 포스트에서는 DELAYED_DURABILITY 옵션에 대한 특징을 살펴보고, 어떠한 경우에 사용할 수 있는지 살펴본다.

SQL Server는 트랜잭션 로그 파일에 데이터를 저장해야 할 때 트랜잭션 로그 파일이 저장된 디스크에 직접 데이터를 기록하지 않는다. 대신 모든 데이터는 인메모리 구조인 로그 캐시(로그 버퍼, 또는 로그 블록 이라고 불리기도 한다.) 에 직렬로 기록된다. 또한 SQL Server OS는 ACID 원칙을 준수해야 하므로 전체 로그 캐시를 디스크 하위 시스템에 저장하거나 필요한 경우 롤백 되는 트랜잭션 로그 파일로 플러시 한다. 로그 캐시의 크기는 512B ~ 64KB이다.

로그 캐시는 특정 조건에서 디스크로 플러시 된다.

l 트랜잭션이 커밋될 때

l 로그 캐시가 가득 차서 60KB에 도달했을 때

l Sys.sp_flush_log가 실행될 때

l CHECKPOINT 프로세스가 완료 되었을 때

SQL Server는 로그 캐시가 트랜잭션 로그 파일로 플러시 되기 시작하는 순간 WRITELOG 대기 유형에 등록되고 로그 캐시가 메모리에서 디스크 드라이브의 파일로 데이터 플러시를 완료 할 때까지 해당 시간이 누적된다. 이 누적시간이 낮을수록 트랜잭션 로그파일의 쓰기 대기 시간이 낮아 진다.

SQL Server에서는 기본적으로 동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록된 후에만 커밋을 성공으로 보고하고 클라이언트에 컨트롤을 반환한다. 따라서 이러한 커밋이 느릴수록 클라이언트에서는 느린 응답을 받을 수밖에 없다. 동기 커밋은 아래와 같은 완전 내구성이 있는 환경의 경우 필수적으로 사용해야한다.

l 시스템에서 데이터 손실을 허용할 수 없는 경우

l 병목 현상의 원인이 트랜잭션 로그 쓰기 대기 시간이 아닌 경우

DELAYED_DURABILITY 옵션을 활성화 할 경우, 비동기 트랜잭션 커밋을 사용하기 때문에 트랜잭션에 대한 로그 레코드가 디스크에 기록되기 전에 커밋 성공으로 클라이언트에 컨트롤을 반환하기 때문에 클라이언트에서는 빠른 응답을 얻을 수 있다. 또한 동시 트랜잭션의 로그 I/O 경합 가능성이 낮아지고, 더 큰 청크 구성으로 디스크에 플러시하여 경합을 줄이고 처리 속도를 높일 수 있다. 비동기 커밋의 경우 아래와 같은 환경에서 사용할 수 있다.

l 약간의 데이터 손실을 허용하는 경우

l 트랜잭션 로그 쓰기 중에 병목 현상이 발생하는 경우

l 작업의 경합률이 높은 경우

DELAYED_DURABILITY 옵션은 데이터베이스 수준에서 트랜잭션 내구성 수준을 제어할 수 있다. 아래 스크립트는 DELAYED_DURABILITY 옵션을 데이터베이스 수준에서 변경한다.

ALTER DATABASE ... SET DELAYED_DURABILITY = { DISABLED | ALLOWED | FORCED }

l DISABLED : 기본값으로 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF})에 상관없이 데이터베이스 커밋된 모든 트랜잭션이 완전 내구성을 가진다. 저장 프로시저를 변경하고 다시 컴파일할 필요가 없다. 지연된 내구성으로 인해 데이터가 위험에 노출되지 않는다.

l ALLOWED : 각 트랜잭션의 내구성이 트랜잭션 수준에서 결정된다. 즉 커밋 수준 설정 (DELAYED_DURABILITY={ON|OFF})에 의해 결정된다.

l FORCED : 데이터베이스에 커밋되는 모든 트랜잭션이 지연된 내구성을 가진다. 이 설정은 지연된 트랜잭션 내구성이 데이터베이스에 유용하고 어플리케이션 코드를 변경하지 않으려는 경우에 유용하다.

아래 스크립트는 커밋 수준을 설정한다.

DELAYED_DURABILITY = { OFF | ON }

l OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 비동기적이고 지연된 내구성을 갖는 경우를 제외하고 트랜잭션은 완전 내구성을 가진다.

l ON : DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 커밋이 동기적으로 완전 내구성있는 경우를 제외하고 트랜잭션은 지연된 내구성을 가진다.

아래 스크립트는 저장 프로시저에 커밋 수준을 적용한 예시이다.

CREATE PROCEDURE [<procedureName>] /* parameters go here */
WITH NATIVE_COMPILATION, SCHEMABINDING, EXECUTE AS OWNER
AS BEGIN ATOMIC WITH
(
    DELAYED_DURABILITY = ON,
    TRANSACTION ISOLATION LEVEL = SNAPSHOT,
    LANGUAGE = N'English'
)
/* procedure body goes here */
END

지연된 트랜잭션 내구성을 강제로 적용할 수 있도록 COMMIT 구문으로도 확장할 수 있는데, 데이터베이스 수준에서 DELAYED_DURABILITY가 DISABLED 또는 FORCED 인 경우 이 COMMIT 옵션은 무시된다.

COMMIT [ { TRAN | TRANSACTION } ] [ transaction_name | @tran_name_variable ] ] [ WITH ( DELAYED_DURABILITY = { OFF | ON } ) ]

l OFF : 기본값으로 DELAYED_DURABILITY = FORCED 데이터베이스 옵션을 적용하여 CIMMIT이 비동기 적으로 지연된 내구성을 갖는 경우를 제외하고 COMMIT 트랜잭션은 완전 내구성을 가진다.

l ON : DELAYED_DURABILITY = DISABLED 데이터베이스 옵션을 적용하여 COMMIT이 동기적이고 완전 내구성 있는 경우를 제외하고 COMMIT 트랜잭션은 지연된 내구성을 가진다.

지연된 내구성을 사용할 경우 특정 상황에서 데이터를 손실 가능성이 있기 때문에 반드시 해당 비즈니스의 목적과 데이터베이스 성능 등을 잘 고려하여 사용 여부를 결정할 수 있도록 한다.

[참고자료]

l Control Transaction Durability : https://learn.microsoft.com/en-us/sql/relational-databases/logs/control-transaction-durability?view=sql-server-ver16

l Measure Delayed Durability impact in SQL Server 2016 and later : https://www.mssqltips.com/sqlservertip/6355/measure-delayed-durability-impact-in-sql-server-2016-and-later/

l Get SQL Server Delayed Durability Advantages Without Configuration Changes : https://www.mssqltips.com/sqlservertip/6324/get-sql-server-delayed-durability-advantages-without-configuration-changes/

l How to handle the SQL Server WRITELOG wait type : https://www.sqlshack.com/how-to-handle-the-sql-server-writelog-wait-type/

l Improve SQL Server transaction log performance with Delayed Durability : https://www.sqlshack.com/improve-sql-server-transaction-log-performance-with-delayed-durability/

l The Transaction Log (SQL Server) : https://learn.microsoft.com/en-us/sql/relational-databases/logs/the-transaction-log-sql-server?view=sql-server-ver16

l SQL Server Transaction Log Architecture and Management Guide : https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-log-architecture-and-management-guide?view=sql-server-ver16

2022-10-30/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, DELAYED_DURABILITY, 트랜잭션 로그 비동기 커밋, WAL, Write Log

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Failover Cluster 구성 (0)	2023.07.16
SQL Server 에서 AWS S3에 직접 백업하기 (2)	2022.10.28
VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0)	2020.03.04
SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28

SQL Server 에서 AWS S3에 직접 백업하기

2022. 10. 28. 19:07

SQL Server 에서 AWS S3에 직접 백업하기

l Version : SQL Server 2022

SQL Server 2022부터는 데이터 플랫폼에 통합 오브젝트 스토리지를 도입하여 Azure Storage외에도 AWS S3 호환 오브젝트 스토리지도 사용할 수 있다. 즉, SQL Server에서 AWS S3로 직접 백업을 할 수 있게 되었다.

기존 SQL Server 2019의 경우 Microsoft Azure 저장소만 가능 하였다. SQL Server 2022 부터 도입된 S3 REST API는 SQL Server에서 AWS S3 직접 백업, 복원을 할 수 있어, 백업 후 이동에 따른 프로세스 단축 및 대용량 백업을 진행할 경우에도 로컬 공간이 부족하여도 백업을 진행할 수 있게 되었다.

지원되는 백업 종류

l 전체 백업

l 차등 백업

l 트랜잭션 로그 백업

l 복사 전용 백업 (COPY ONLY)

지원되지 않는 백업 종류

l 스냅샷 백업 (FILE_SNAPSHOT)

l 미러 백업 (MIRROR)

AWS S3버킷으로 백업 및 복원을 수행 하기위해서는 아래 정보가 필요 하다.

l S3 버킷이름

l S3 URI

l S3 URL

l IAM 액세스 ID

l IAM 액세스 키

AWS IAM(Idnetity Access Management)의 정책은 사용자의 권한 또는 특정 서비스에 대해 수행할 수 있는 역할을 정의한다. SQL Server 2022에서 S3 버킷으로 직접 데이터베이스 백업을 수행하려면 ListBucket 및 PutObject 권한이 필요하다.

l ListBucket : ListBucket 액세스는 요청의 인증된 발신자가 소유한 모든 버킷 목록을 반환한다.

l PutObject : PutObject 액세스를 통해 버킷에 객체를 작성하고 추가할 수 있다.

SQL Server에서 AWS S3로 백업을 하기 위해 우선 자격증명을 만들어야 한다. 아래는 자격증명을 만드는 스크립트이다.

CREATE CREDENTIAL   [s3://<endpoint>:<port>/<bucket>]
WITH
        IDENTITY    = 'S3 Access Key',
        SECRET      = '<AccessKeyID>:<SecretKeyID>';

l [s3://<endpoint>:<port>/<bucket>] : 자격 증명 이름에는 S3 버킷 이름이 포함되어야 한다. S3://로 시작하고 접두사 HTTPS://를 제거한 후 S3 버킷 URL을 따른다.

l IDENTITY : S3 Access Key 값으로, 자격 증명에서 S3 커넥터를 사용하고 있음을 나타낸다.

l SECRET : 접근키 ID와 비밀 키를 콜론으로 구분하여 입력한다. 콜론 기호는 구분자 역할을 한다.

백업 명령은 기존에 사용하던 백업문과 거의 동일하다. 다만 백업 위치가 DISK 가 아닌 URL을 사용한다.

BACKUP DATABASE [DatabaseName] TO URL = 'CredentialName\Backupfilename.bak'

백업 완료 후 S3를 확인해보면 백업파일이 생성된 것을 확인할 수 있다.

아래 스크립트는 백업하려는 데이터베이스를 여러 스트라이프된 백업 파일로 오브젝트 스토리지 엔드포인트를 사용하여 백업을 수행한다.

BACKUP DATABASE <db_name>
TO      URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    FORMAT -- overwrite
,       STATS               = 10
,       COMPRESSION;

아래 스크립트는 오브젝트 스토리지 엔드포인트 위치에서 데이터베이스 복원을 수행한다.

RESTORE DATABASE <db_name>
FROM    URL = 's3://<endpoint>:<port>/<bucket>/<database>_01.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_02.bak'
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_03.bak'
--
,       URL = 's3://<endpoint>:<port>/<bucket>/<database>_64.bak'
WITH    REPLACE -- overwrite
,       STATS = 10;

데이터를 저장하려면 S3 호환 오브젝트 스토리지 공급자는 파트(Part)라 불리는 여러 블록으로 파일을 분할해야 한다. 각 파일은 최대 10,000개의 파트로 분할 수 있으며, 각 파트의 크기는 5MB ~ 20MB이다. 이 범위는 매개변수 MAXTRANSFERSIZE를 통해 T-SQL BACKUP 명령으로 제어할 수 있다. MAXTRANSFERSIZE의 기본값은 10MB 이므로 각 파트의 기본 크기는 10MB이다. 즉, 단일 백업 파일의 크기는 MAXTRANSFERSIZE가 기본값으로 설정되어 있을 경우 100,000MB이다. 백업 스트라이프가 100,000MB를 초과할 경우 아래와 같은 오류 메시지를 반환한다.

Msg 3202, Level 16, State 1, Line 161
Write on 's3://<endpoint>:<port>/<bucket>/<path>/<db_name>.bak' failed: 87(The parameter is incorrect.)
Msg 3013, Level 16, State 1, Line 161
BACKUP DATABASE is terminating abnormally.

단일 백업 파일의 크기가 100,000MB 보다 큰 파일을 백업 해야 할 경우 최대 64개의 URL 스트라이프 백업을 지원한다. 즉, 가능한 최대 백업 파일 크기는 10,000part * MAXTRANSFERSIZE * URL이다. 압축 백업을 사용할 경우 백업 파일 사이즈가 현저히 많이 줄어들기 때문에 백업 시 압축 옵션을 적극 활용할 수 있도록 한다.

아래 예제는 MAXTRANSFERSIZE를 사용하여 20MB으로 설정하고, 백업 시 압축 및 암호화한다.

CREATE MASTER KEY ENCRYPTION BY PASSWORD = <password>;
GO

CREATE CERTIFICATE AdventureWorks2019Cert
WITH SUBJECT = 'AdventureWorks2019 Backup Certificate';
GO
-- Backup database
BACKUP DATABASE AdventureWorks2019
TO URL = 's3://<endpoint>:<port>/<bucket>/AdventureWorks2019_Encrypt.bak'
WITH FORMAT, MAXTRANSFERSIZE = 20971520, COMPRESSION,
ENCRYPTION (ALGORITHM = AES_256, SERVER CERTIFICATE = AdventureWorks2019Cert)
GO

백업 및 복원 엔진에서 총 URL의 길이는 259바이트로 제한되어 있기 때문에 s3://를 제외하고 사용자의 경로 길이(호스트 이름 + 개체키)를 259 - 5 = 254글자까지 입력할 수 있다. 만약 URL 길이를 초과할 경우 아래와 같은 오류가 반환된다.

SQL Server has a maximum limit of 259 characters for a backup device name. The BACKUP TO URL consumes 36 characters for the required elements used to specify the URL - 'https://.blob.core.windows.net//.bak', leaving 223 characters for account, container, and blob names put together'

S3 스토리지는 SQL 호스트와 S3 서버간의 시간 차이가 15분을 초과할 때 마다 연결을 거부하고 “InvalidSignatureException”오류를 SQL Server로 보낸다. SQL Server에서는 아래와 같은 오류로 반환된다.

Msg 3201, Level 16, State 1, Line 28
Cannot open backup device '<path>'. Operating system error 5(Access is denied.).
Msg 3013, Level 16, State 1, Line 28
BACKUP DATABASE is terminating abnormally.

[참고자료]

l SQL Server backup to URL for S3-compatible object storage : https://learn.microsoft.com/en-us/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage?view=sql-server-ver16

l SQL Server back up to URL for S3-compatible object storage best practices and troubleshooting : https://learn.microsoft.com/ko-kr/sql/relational-databases/backup-restore/sql-server-backup-to-url-s3-compatible-object-storage-best-practices-and-troubleshooting?view=sql-server-ver16

l https://www.mssqltips.com/sqlservertip/7301/sql-server-2022-backup-restore-aws-s3-storage/

l https://www.mssqltips.com/sqlservertip/7302/backup-sql-server-2022-database-aws-s3-storage/

2022-10-28/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, AWS S3 backup, S3 백업, 클라우드 백업, cloud backup

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Failover Cluster 구성 (0)	2023.07.16
SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기 (1)	2022.10.31
VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0)	2020.03.04
SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28

SSRS 리포트에서 이미지 추가하기

2020. 4. 17. 13:55

SSRS 리포트에서 이미지 추가하기

· Version : SSRS

SSRS에서 이미지를 출력하는 방법에는 3가지가 있다.

· Embedded : SSRS 서버에 있는 이미지로 리포트에 표시

· Database : 데이터베이스에 저장된 이미지를 표시

· External : 외부 URL 이미지를 표시

이번 포스트에서는 외부 URL 의 이미지를 출력하는 방법에 대해서 살펴본다. 외부 URL의 이미지를 표시하기 위해서는 이미지 주소를 포함하고 있는 데이터셋이 필요하다. 필자의 경우 검색 사이트에서 검색한 로고 이미지의 주소를 사용하여 실습용 데이터셋을 만들었다.

select 'Microsoft' as Name, 'https://cdn.arstechnica.net/wp-content/uploads/2012/08/microsoft-metro-tall.png' as Image_URL

union all

select 'LinkedIn' as Name, 'https://nonesnotes.files.wordpress.com/2019/09/lil.jpg' as Image_URL

union all

select 'Google' as Name, 'https://www.adweek.com/wp-content/uploads/files/blogs/google-logo-hed-2014.jpg' as Image_URL

union all

select 'FaceBook' as Name, 'https://cdn0.tnwcdn.com/wp-content/blogs.dir/1/files/2015/07/sdffdsafsdf-1200x604.png' as Image_URL

리포트에서 테이블을 추가하고 컬럼에 [Image]를 추가하였다. 이미지를 추가하게 되면 속성창이 나타나며, 외부 URL 이미지를 사용할 경우 “External’을 선택 한다. 그리고 이미지 URL 데이터 소스를 선택한다.

보고서 미리보기를 통하여 정상적으로 이미지가 출력되는지 확인한다.

[참고자료]

https://docs.microsoft.com/ko-kr/sql/reporting-services/report-design/add-an-external-image-report-builder-and-ssrs?view=sql-server-ver15

2020-04-16 / Sungwook Kang / http://sungwookkang.com

SSRS, SQL Server, 리포팅 서비스, 대시보드, 이미지 출력, 대시보드 이미지 삽입, SSRS이미지 출력, 보고서 이미지 포함하기

저작자표시 비영리 변경금지

'SQL Server > SSRS 강좌' 카테고리의 다른 글

SSRS 보고서 실행 로그 및 감사(Audit) (0)	2020.04.08
SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기) (0)	2020.02.12
SSRS에서 표의 값에 따라 폰트 컬러 변경하기 (0)	2020.02.11
SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 (1)	2019.03.25
SSRS 2016 향상된 구독(Subscription) 기능 (0)	2015.11.04

SSRS 보고서 실행 로그 및 감사(Audit)

2020. 4. 8. 14:10

SSRS 보고서 실행 로그 및 감사(Audit)

· Version : SSRS

SSRS에서 보고서가 얼마나 자주 실행되는지, 누가 또는 어떤 프로세스가 보고서를 실행하는지, 실행하는데 걸리는 시간, 보고서 매개변수에 대한 통계, 사용되지 않는 보고서등에 대한 정보가 필요할 때 SSRS의 Execution Logging 기능을 사용하여 해당 정보를 얻을 수 있다.

로깅 작동 방식은, Reporting Services 서버에서 로깅 기능을 활성화 하여 사용할 수 있다. 로깅이 활성화 되면 ReportServer 데이터베이스의 dbo.ExectionLogStorage 테이블에 행위가 기록 된다. 감사 데이터에 액세스하려면 ReportServer 데이터베이스의 ExecutionLog, ExecutionLog2, ExecutionLog3의 뷰테이블 중 하나를 사용하면 된다.

SSRS에서 로깅을 활성화 화기 위해서는 SSMS를 사용하여 SSRS 서버에 접속한다. 이때 접속하는 Server Type에 주의한다.

SSRS 서버에 접속 되었으면 서버에서 마우스 오른쪽을 클릭하여 [Properties]를 선택한다.

속성창이 나타면 [Logging] – [Enable report execution logging]을 체크하여 활성화 한다.

[Advanced] 탭에서 동일한 속성을 설정할 수 있다.

로깅을 구성하는 특성을 정의할 수 있는데, ExecutionLevel에 따라 로깅레벨이 달라진다. Normal과 Verbose 로깅 2가지가 제공되며, Verbose 로깅은 데이터 소스 및 데이터 세트에 대한 추가 감사 데이터가 수집된다.

로깅 설정이 완료 되었으면 SSMS에서 SQL Server로 접속하여 dbo. ExectionLogStorage테이블에 보고서 실행에 대한 로깅 정보를 확인한다. 이 테이블에서 마지막열인 AdditionalInfo컬럼은데이터 소스 및 데이터 세트에 대한 정보가 XML로 기록된다. 로그를 좀더 쉽게 확읺기 위해ExecutionLog, ExecutionLog2, ExecutionLog3 뷰가 제공된다.

[참고자료]

https://docs.microsoft.com/en-us/sql/reporting-services/report-server/report-server-executionlog-and-the-executionlog3-view?view=sql-server-ver15

2020-04-07 / Sungwook Kang / http://sungwookkang.com

SSRS, SQL Server, 리포팅 서비스, 보고서 만들기, SQL 보고서, SQL Server Reporting Services, SSRS Logging, SSRS Audit, 보고서 감사, 보고서 실행 로그

저작자표시 비영리 변경금지

'SQL Server > SSRS 강좌' 카테고리의 다른 글

SSRS 리포트에서 이미지 추가하기 (1)	2020.04.17
SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기) (0)	2020.02.12
SSRS에서 표의 값에 따라 폰트 컬러 변경하기 (0)	2020.02.11
SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 (1)	2019.03.25
SSRS 2016 향상된 구독(Subscription) 기능 (0)	2015.11.04

VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상

2020. 3. 4. 00:39

VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상

· Version : SQL Server

Virtual Machine (VM)의 성능이 향상됨에 따라 많은 시스템들이 Physical 서버에서 VM 머신으로 마이그레이션을 진행하고 있다. 이번 포스트는 Physical머신에서 VM으로 마이그레이션 후 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 성능 문제에 대해서 다룬다.

Host Server

OS	Windows Server 2019 Standard
CPU	2 socket (64 Core)
RAM	128GB

VM Server

OS	Windows Server 2019 Standard
SQL Server	SQL Server 2016 Standard
CPU	8 Core
RAM	32GB

아래 그림을 보면 가상머신에 할당된 CPU 8 Core 에서 4 Core만 사용하는것을 확인할 수 있다.

아래 쿼리를 사용하여 실제 SQL Server가 사용중인 CPU를 확인할 수 있다. 역시 할당된 8개의 코어중에 4개만 사용하고 있음이 표시되었다.

SELECT scheduler_id, cpu_id, status, is_online FROM sys.dm_os_schedulers

원인을 확인결과 VM 머신의 세팅에 문제가 있다는 것을 확인하였다. 필자가 구성한 VM환경을 보면, 8 소켓에 8Core로 설정되어 있는것을 확인할 수 있다. 이 뜻은 1소켓 1 코어가 8개 할당되었다는 의미이다. SQL 공식 다큐먼트를 보면 Standard의 경우 4소켓 또는 24코어까지만 지원된다고 되어 있다. 즉 소켓의 개수가 8개로 세팅되어, 실제 4 소켓만 동작을 하기 때문에 4Core만 동작을 하게 된것이다.

VM 환경을 변경하여, 1소켓 8 Core로 설정을 변경한 후 정상적으로 모든 CPU를 사용하는 것을 확인하였다.

VM의 환경을 변경하는것은 시스템 운영에 문제가 발생할 수 있으므로 반드시 시스템 담당자와 상의해서 진행할 수 있도록 한다.

아래 스크립트는 현재 구성된 VM의 환경에서 SQL Server가 할당된 CPU를 모두 사용가능한지 쉽게 확인한다.

----------------------------------------------------------------------------------------------------------------

-- CPU VISIABLE ONLINE CHECK

----------------------------------------------------------------------------------------------------------------

DECLARE @OnlineCpuCount int

DECLARE @LogicalCpuCount int

SELECT @OnlineCpuCount = COUNT(*) FROM sys.dm_os_schedulers WHERE status = 'VISIBLE ONLINE'

SELECT @LogicalCpuCount = cpu_count FROM sys.dm_os_sys_info

SELECT @LogicalCpuCount AS 'ASSIGNED ONLINE CPU #', @OnlineCpuCount AS 'VISIBLE ONLINE CPU #',

CASE

WHEN @OnlineCpuCount < @LogicalCpuCount

THEN 'You are not using all CPU assigned to O/S! If it is VM, review your VM configuration to make sure you are not maxout Socket'

ELSE 'You are using all CPUs assigned to O/S. GOOD!'

END as 'CPU Usage Desc'

----------------------------------------------------------------------------------------------------------------

2020-03-03/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, VM, Virtual Machine, CPU 할당, 가상머신 SQL, SQL on VM, dm_os_schedulers, dm_os_sys_info

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server DELAYED_DURABILITY 옵션으로 트랜잭션 로그 쓰기 성능 개선 하기 (1)	2022.10.31
SQL Server 에서 AWS S3에 직접 백업하기 (2)	2022.10.28
SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24

SQL Server 복원 성능 최적화

2020. 2. 29. 01:12

SQL Server 복원 성능 최적화

· Version : SQL Server

SQL Server에서 백업 파일을 복원할때 빠르게 복원하기 위한 최적화 방법을 소개한다. 이 방법을 사용한다고 해서 무조건 빠르게 복원되지는 않으며, 사용할 수 있는 시스템 리소스에 따라 최적화된 옵션을 제공함으로써 좀 더 빠르게 복원할 수 있게 유도 하는 것이다.

데이터베이스 백업 및 복원에 대한 통계를 확인하기 위해 추적 플래그 3213, 3605를 설정한다.

DBCC TRACEON (3213, -1)

DBCC TRACEON (3605, -1)

데이터베이스를 복원하면, SQL 이벤트 로그에서 아래와 같은 내용을 확인할 수 있다.

기본 설정을 사용하면 최대 전송크기는 1024K 이고 버퍼수는 6인것을 확인할 수 있다. 이때 사용되는 총 버퍼 공간은 6MB이다.

총 버퍼공간 = 최대 전송크기 X 버퍼 수

여기서 주목해야 할 부분은 메모리 제한(Memory limit) 이다. 현재 필자의 메모리 제한은 4095 MB (사용자 마다 다름)이지만 사용되는 총 버퍼 공간은 6MB 이다. 따라서 총 버퍼 공간을 늘려 복원 속도를 높일 수 있다. 최대 전송 크기 및 버퍼수 변경은 데이터베이스 복원시 추가 매개변수로 사용할 수 있다.

아래 예시는 최대 메모리 제한까지 사용하기 위해서 MAXTRANSFERSIZE는 약4096K로 설정하고, BUFFERCOUNT는 1000으로 설정하였다.

RESTORE DATABASE [DB] FROM DISL = N'D:\DB\BACKUP\DB.BAK' WITH REPLACE, STATS = 5, MAXTRANSFERSIZE = 4194302, BUFFERCOUNT = 1000

추가 매개변수를 사용하여 데이터베이스를 복원할 경우, 복원의 속도는 빨라지겠지만 동일 서버에서 운영되는 다른 서비스에 영향을 미칠 수도 있다. 해당 옵션을 사용하기 전에 시스템의 리소스 가용능력, 사용량등을 확인할 수 있도록 한다.

중요한것은 실제 운영 환경에 반영하기전에 테스트 환경에서 먼저 검증을 할 수 있도록 한다.

[참고자료]

· Optimizing Backup and Restore Performance in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms190954(v=sql.105)?redirectedfrom=MSDN

2020-02-28/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, Database Restore, SQL Server Recovery Step, 데이터베이스 복원, Database Recovery Process, BUFFERCOUNT, MAXTRANSFERSIZE

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 에서 AWS S3에 직접 백업하기 (2)	2022.10.28
VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0)	2020.03.04
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상

2020. 2. 28. 01:22

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상

· Version : SQL Server 2016 Enterprise Edition

SQL Server에서 트랜잭션 로그를 사용하여 데이터를 복원시, 평소와 다르게 매우 오래 걸리는 현상이 발생하였다. 처음에는 I/O 서브 시스템을 의심하고 물리장비 까지 교체하였으나, 증상을 동일하였다. 여러가지 가설을 세웠고, 원인 분석 결과, Slow query가 트랜잭션 로그의 복원시간과 관련이 있다는 것을 발견할 수 있었다.

필자의 운영 환경은 10분 마다 트랜잭션 로그 백업을 진행하고, 백업된 로그는 다른 서버에서 Read Only(STAND BY)로 복원하여 각 부서에서 사용할 수 있도록 로그 쉬핑을 구성하였다. 10분 마다 발생하는 로그의 양은 약 10MB~ 20MB 정도로 평상시 복원하는데 약 1분 정도 소요되었다. 그런데 어느날 부터 복원시간이 증가하여 30분정도 걸렸다. 그러다 보니, 연관된 여러 ETL 시스템 및 리포트 시스템에 영향을 주게 되었었다.

아래 그래프는 트랜잭션로그 복원시 디스크의 Idle Time를 캡처한 것으로 파란색이 평상시의 상태이고 빨간색과 노란색이 문제가 발생했던 때의 상태이다. 즉 트랜잭션 로그 복원이 진행되는 동안 엄청난 I/O가 발생하는 것을 확인할 수 있다.

트랜잭션 복원이 진행되는 동안 각 단계에 대한 작업시간을 확인하기 위해서 DBCC TRACE 명령을 사용하여 Inter log를 SQL Server이벤트 로그에 기록하도록 하였다.

DBCC TRACEON(3004, -1)

DBCC TRACEON(3605, -1)

DBCC TRACEON(3213, -1)

트레이스 적용 후 트랜잭션로그 복원을 진행하고, 아래와 같은 이벤트 로그를 확인할 수 있었다.

이벤트 로그를 확인해보면 redo, undo 에 관한 시간이 표시되어 있는데, Slow 쿼리가 발생할 경우 트랜잭션 커밋이나 롤백이 발생하지 않은 상태(더티페이지)에서 백업이 진행되고, 그 다음에 쿼리 실행이 완료되어 다름 트랜잭션 로그에 반영되었을때, 해당 쿼리의 결과에 따라, 데이터가 롤백되거나 커밋을 해야해서 (특히 롤백이 문제다) 데이터 페이지, 인덱스 페이지등에 반영하느라 디스크 I/O 오버헤드가 발생하고 전체적인 복원 시간이 느려졌다. 즉 아래와 같은 단계로 진행된다.

1 단계 : 분석. 트랜잭션 로그의 마지막 체크 포인트에서 시작한다. 이 단계는 SQL Server가 중지 될 때 더티 페이지 테이블 (DPT)을 확인하고 구성한다. 활성 트랜잭션 테이블은 SQL Server가 중지 될 때 커밋되지 않은 트랜잭션으로 구성된다.

2 단계 : 다시 실행. 이 단계는 데이터베이스를 SQL 서비스가 중지 된 시점의 상태로 되돌린다. 가장 오래된 커밋되지 않은 트랜잭션이 롤백의 시작점 이다. DPT의 최소 로그 시퀀스 이름 (각 로그 레코드에 LSN으로 레이블이 지정됨)은 SQL Server가 페이지에서 작업을 다시 실행해야하는 첫 번째 시간이며 가장 오래된 열린 트랜잭션에서 다시 시작하여 기록 된 작업을 다시 실행해야 한다. 필요한 잠금 장치를 확보 한다.

3 단계 : 실행 취소. 여기에서 1 단계에서 식별 된 활성 트랜잭션 (SQL Server가 중단 될 때 커밋되지 않은)의 목록이 개별적으로 롤백된다. SQL Server는 각 트랜잭션에 대한 트랜잭션 로그 항목 간의 링크를 따른다. SQL Server가 중지 될 때 커밋되지 않은 트랜잭션은 모두 취소된다.

정리하면, Slow 쿼리 증가로 인해 활성 트랜잭션이 많아졌고, 트랜잭선 로그 백업이 진행될때 더티페이지가 증가함에 따라 해당 로그 파일로 복원시 롤백 및 커밋 작업이 진행되어 이때 I/O 오버헤드가 발생하여 전체적인 복원이 느려졌다.

[참고자료]

· Understanding How Restore and Recovery of Backups Work in SQL Server : https://docs.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms191455(v=sql.105)?redirectedfrom=MSDN

· SQL Server Mysteries: The Case of the Not 100% RESTORE : https://docs.microsoft.com/en-us/archive/blogs/sql_server_team/sql-server-mysteries-the-case-of-the-not-100-restore

· SQLskills SQL101: Why is restore slower than backup : https://www.sqlskills.com/blogs/paul/sqlskills-sql101-why-is-restore-slower-than-backup/

· SQL Server Database Recovery Process Internals – database STARTUP Command : https://www.sqlshack.com/sql-server-database-recovery-process-internals-database-startup-command/

2020-02-27/ Sungwook Kang / http://sungwookkang.com

SQL Server, MS SQL, Database Restore, Log shipping, 트랜잭션 로그 복원, SQL Server Recovery Step, 데이터베이스 복원, 로그 전달, 로그쉬핑, Database Recovery Process

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

VM 환경의 SQL Server에서 할당된 CPU를 모두 사용하지 못하는 현상 (0)	2020.03.04
SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21

SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기)

2020. 2. 12. 00:44

SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기)

· Version : SSRS

SSRS 인디케이터(Indicators)는 보고서에서 표시되는 단일 데이터 값의 상태를 시각화 할 수 있는 작은 표시기 이다. 인디케이터의 종류는 다양하며, 추세, 상태, 조건, 등급 등의 상태를 시각적으로 표현할 수 있다.

인디케이터를 사용하는 방법은 [Toolbox] – [Indicator] 항목을 리포트 페이지로 드래그앤 드롭으로 끌어다 놓으면 된다. 조건에 따라 인디케이터의 상태를 변화 값을 설정하기 위해서 인디케이터에서 마우스 오른쪽 버튼을 클릭하여 [Indicator Properties]를 선택한다.

Indicator properties 창에서 [Value and States] 탭을 클릭하여 인디케이터의 속성값을 설정한다. 아래 그림에 보이는 설정은 시작값이 0~33인경우 빨간색, 33~66인경우 노란색, 66~100인경우 녹색을 표시 한다.

SSRS에서 표에 인디케이터를 추가하여, 값의 상태에 따라 증가 및 감소를 방향 및 색상으로 표시하여 한눈에 추세를 쉽게 확인할 수 있다.

2020-02-11 / Sungwook Kang / http://sungwookkang.com

SSRS, SQL Server, 리포팅 서비스, 보고서 만들기, SQL 보고서, SQL Server Reporting Services, SSRS Indicator, 인디케이터, 상태값 시각화, 보고서 시각화

저작자표시 비영리 변경금지

'SQL Server > SSRS 강좌' 카테고리의 다른 글

SSRS 리포트에서 이미지 추가하기 (1)	2020.04.17
SSRS 보고서 실행 로그 및 감사(Audit) (0)	2020.04.08
SSRS에서 표의 값에 따라 폰트 컬러 변경하기 (0)	2020.02.11
SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 (1)	2019.03.25
SSRS 2016 향상된 구독(Subscription) 기능 (0)	2015.11.04

SSRS에서 표의 값에 따라 폰트 컬러 변경하기

2020. 2. 11. 00:45

SSRS에서 표의 값에 따라 폰트 컬러 변경하기

· Version : SSRS

SSRS에서 표의 폰트 색상을 정해진 룰에 따라 자동으로 색상을 변경하는 방법에 대해서 알아본다.

폰트의 컬러 속성을 지정하기 위해서, 값을 표시하려는 표의 컬럼에서 마우스 오른쪽 버튼을 클릭하여 [Text Box Property] 항목을 선택 한다.

[Text Box Properties] 설정 창이 나타나면 [Font] 탭으로 이동하여 Color 항목의 펑션 버튼(fx)을 클릭한다.

펑션 창에서 아래와 같이 조건을 입력하면 해당 룰에 따라 폰트 컬러가 변경된다.

=SWITCH(Fields!Pay.Value <= 10, "Red", Fields!Pay.Value >= 20, "Green")

폰트 컬러외에도 굵기, 이탤릭체 등 설정할 수 있으며, 표의 텍스트 배경색 변경도 가능하다.

2020-02-10 / Sungwook Kang / http://sungwookkang.com

SSRS, SQL Server, 리포팅 서비스, 보고서 만들기, SQL 보고서, SQL Server Reporting Services, 폰트 색상 변경

저작자표시 비영리 변경금지

'SQL Server > SSRS 강좌' 카테고리의 다른 글

SSRS 보고서 실행 로그 및 감사(Audit) (0)	2020.04.08
SSRS 인디케이터 (데이터 값에 따라 표시기로 시각화 하기) (0)	2020.02.12
SSRS에서 E-mail 세팅시 SSL 사용 유무 수정하기 (1)	2019.03.25
SSRS 2016 향상된 구독(Subscription) 기능 (0)	2015.11.04
SSRS 보고서에 사용된 쿼리문 확인(테이블, 컬럼, 프로시저 찾기) (0)	2015.08.11

Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여

2019. 11. 9. 01:13

Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여

· Version : Azure SQL

Azure SQL Database 및 Azure SQL Data Warehouse 방화벽 규칙이 구성된 후에는 관리자 계정 중 하나로, 데이터베이스 소유자로 또는 데이터베이스의 데이터베이스 사용자로 SQL Database 및 SQL Data Warehouse에 연결할 수 있다.

· 방화벽 규칙 설정 : https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-firewall-configure?WT.mc_id=DP-MVP-4039834

관리자로 작동하는 계정에는 두 가지가 (서버 관리자 및 Active Directory 관리자)이 있습니다. SQL 서버에 대해 이러한 관리자 계정을 식별하려면 Azure Portal을 열고 SQL Server 또는 SQL Database의 [속성] 탭으로 이동한다.

· Server Admin login은 Azure SQL 서버를 만들 때 서버 관리자 로그인을 지정해야 한다. SQL 서버는 master 데이터베이스에 로그인으로 해당 계정을 만든다. 이 계정은 SQL Server 인증(사용자 이름 및 암호)을 사용하여 연결되며 하나만 생성할 수 있다. 서버 관리자에 대한 암호를 다시 설정하려면 Azure Portal로 이동하여 SQL Server를 클릭하고 목록에서 서버를 선택한 다음, 암호 재설정을 클릭한다.

· Azure Active Directory admin은 하나의 Azure Active Directory 계정, 개인 또는 보안 그룹 계정을 관리자로 구성할 수도 있다. Azure AD 관리자를 구성하는 것은 선택 사항이지만, Azure AD 계정을 사용하여 SQL Database에 연결하려면 Azure AD 관리자를 반드시 구성해야 한다.

Server Admin login과 Azure Active Directory admin 관리자 계정에는 다음과 같은 특징이 있다.

· 서버에서 모든 SQL Database에 자동으로 연결할 수 있는 유일한 계정이다. (사용자 데이터베이스에 연결하려면 다른 계정은 데이터베이스의 소유자이거나 사용자 데이터베이스에 사용자 계정이 있어야 한다.)

· 계정은 dbo 사용자로 사용자 데이터베이스에 연결하고 사용자 데이터베이스에서 모든 권한을 갖는다. (사용자 데이터베이스의 소유자는 또한 dbo 사용자로 데이터베이스에 접속한다.)

· master 데이터베이스에 dbo 사용자로 접속할 수 없으며 master에서는 제한된 사용 권한을 갖는다.

· SQL 데이터베이스에서 sysadmin 역할의 멤버가 아니다.

· 데이터베이스, 로그인, master의 사용자 및 서버 수준 IP 방화벽 규칙을 만들고 변경하고 삭제할 수 있다.

· dbmanager 및 loginmanager 역할에 멤버를 추가하고 제거할 수 있다.

· sys.sql_logins 시스템 테이블을 볼 수 있다.

서버 수준 방화벽이 제대로 구성되면 SQL 서버 관리자 및 Azure Active Directory 관리자가 SQL Server Management Studio 또는 SQL Server Data Tools와 같은 클라이언트 도구를 사용하여 연결할 수 있다. 최신 도구만 모든 특징 및 기능을 제공한다. 다음 다이어그램에서는 두 명의 관리자 계정에 대한 일반적인 구성을 보여 준다. 서버 수준 방화벽에서 열려 있는 포트를 사용하면 관리자가 모든 SQL Database에 연결할 수 있다.

[Serve Level Administrative Roles]

Database creators

dbmanager 역할의 멤버는 새 데이터베이스를 만들 수 있다. 이 역할을 사용하려면 master 데이터베이스에 사용자를 만든 다음 해당 사용자를 dbmanager 데이터베이스 역할에 추가해야 한다. 데이터베이스를 만들려면 사용자가 master 데이터베이스의 SQL Server 로그인을 기반으로 한 사용자이거나 Azure Active Directory 사용자를 기반으로 한 포함된 데이터베이스 사용자여야 한다. 아래 스크립트를 사용하여 생성할 수 있다.

USE master

CREATE LOGIN Mary WITH PASSWORD = '<strong_password>';

CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER; -- To create a user with Azure Active Directory

CREATE USER Ann WITH PASSWORD = '<strong_password>'; -- To create a SQL Database contained database user

CREATE USER Mary FROM LOGIN Mary; -- To create a SQL Server user based on a SQL Server authentication login

ALTER ROLE dbmanager ADD MEMBER Mary;

ALTER ROLE dbmanager ADD MEMBER [mike@contoso.com];

Login managers

Loginmanager 멤버는 master 데이터베이스에 새 로그인을 만들 수 있다. 원한다면 동일한 단계(로그인 및 사용자 만들기, 사용자를 login manager 역할에 추가)를 완료하여 사용자가 master에서 새 로그인을 만들 수 있도록 한다. 일반적으로 Microsoft는 로그인 기반 사용자를 사용하는 대신 데이터베이스 수준에서 인증하는 포함된 데이터베이스 사용자를 사용할 것을 권장하므로 로그인이 필수는 아니다.

[Non-administrator users]

일반적으로 비관리자 계정은 master 데이터베이스에 액세스할 필요가 없다. 비관리자 계정은 CREATE USER(Transact-SQL) 문을 사용하여 데이터베이스 수준에서 포함된 데이터베이스 사용자를 생성한다. 사용자는 Azure Active Directory 인증 포함 된 데이터베이스 사용자 (Azure AD 인증용 환경을 구성한 경우) 또는 SQL Server 인증 포함 된 데이터베이스 사용자 또는 SQL Server 기반 SQL Server 인증 사용자 일 수 있다. 아래 예시 스크립트를 사용하여 계정을 생성할 수 있다.

CREATE USER Mary FROM LOGIN Mary;

CREATE USER [mike@contoso.com] FROM EXTERNAL PROVIDER;

GRANT ALTER ANY USER TO Mary;

--db_owner 역할 멤버로 설정

ALTER ROLE db_owner ADD MEMBER Mary;

--Azure SQL Data Warehouse 경우 아래 실행

EXEC sp_addrolemember 'db_owner', 'Mary';

데이터베이스 수준 방화벽이 제대로 구성되었으면 데이터베이스 사용자는 SQL Server Management Studio 또는 SQL Server Data Tools와 같은 클라이언트 도구를 사용하여 연결할 수 있다. 다음 다이어그램에서는 일반적인 비관리자 액세스 경로를 보여준다.

이 외에도 그룹 및 역할, 권한을 정의 할 수 있으며, SQL Database에는 개별적으로 부여하거나 거부할 수 잇는 100개가 넘는 권한이 있다. 대부분 권한은 중첩되어 사용된다. 자세한 내용은 참고자료의 링크를 확인할 수 있도록 한다.

[참고자료]

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-manage-logins?WT.mc_id=DP-MVP-4039834

2019-11-08 / Sungwook Kang / http://sungwookkang.com/

Azure SQL Database, Azure SQL Data Warehouse, Azure 방화벽, Azure Database 방화벽, Azure Security, Controlling SQL Database Access, DB 접근, DB 액세스

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙 (0)	2019.11.07
Azure SQL Database 보안 기능 개요 (0)	2019.10.17
Azure SQL Database의 서버리스 구매 모델 (0)	2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙

2019. 11. 7. 01:04

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙

· Version : Azure SQL

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙에 대해서 살펴본다. 여기에서 설명되는 내용은 Azure SQL Database Management Instance에 적용되지 않으니 혼돈하지 않도록 한다.

인터넷 및 Azure 환경에서 SQL Server 또는 SQL Database에 연결하기 전에 방화벽을 통과해야한다. 크게 서버 수준 IP 방화벽 규칙과 데이터베이스 수준의 IP 방화벽 규칙이 있다. 가능하면 항상 데이터베이스 수준 IP 방화벽 규칙을 사용 하는 것이 좋다. 이 방법을 사용하면 보안을 강화하 고 데이터베이스의 이식성을 높일 수 있다. 관리자에 대해서도 서버 수준 IP 방화벽 규칙을 사용을 권장한다. 동일한 액세스 요구사항을 가진 데이터베이스가 많고 각 데이터베이스를 개별적으로 구성하지 않으려는 경우에도 데이터베이스 수준의 IP 방화벽 규칙을 사용 한다.

[서버 수준 IP 방화벽 규칙]

클라이언트는 규칙에 따라 전체 Azure SQL Server, 즉 동일한 SQL Database 서버 내의 모든 데이터베이스에 액세스 할 수 있다. 규칙은 master 데이터베이스에 저장 된다. Azure SQL Server에 대해 최대 128개의 서버 수준 IP 방화벽 규칙을 사용할 수 있다. Azure Portal, PowerShell 또는 Transact-SQL 문을 사용하여 서버수준 IP 방화벽 규칙을 구성할 수 있다.

· 포털 또는 PowerShell을 사용 하려면 구독 소유자 또는 구독 참가자 여야한다.

· Transact-SQL을 사용 하려면 서버 수준 보안 주체 로그인 또는 Azure Active Directory 관리자로 SQL Database 인스턴스에 연결해야 한다. 먼저 Azure 수준 사용 권한이 있는 사용자가 서버 수준 IP 방화벽 규칙을 만들어야 한다.

[데이터베이스 수준 IP 방화벽 규칙]

클라이언트는 규칙에 따라 동일한 SQL Database 서버 내의 특정(보안) 데이터베이스에 액세스 할 수 있다. 각 데이터베이스(master 데이터베이스 포함)에 대 한 규칙을 만들어 개별 데이터베이스에 저장한다.

· 서버 수준 방화벽을 구성한 후에만 Transact-SQL 문을 사용하여 master 및 사용자 데이터베이스에 대한 데이터베이스 수준 IP 방화벽 규칙을 만들고 관리할 수 있다.

· 서버 수준 IP 방화벽 규칙의 범위를 벗어나는 데이터베이스 수준 IP 방화벽 규칙의 IP 주소 범위를 지정하면 데이터베이스 수준 범위에서 IP 주소가 있는 클라이언트만 데이터베이스에 액세스 할 수 있다.

· 데이터베이스에 대해 최대 128개의 데이터베이스 수준 IP 방화벽 규칙을 가질 수 있다. 데이터베이스 수준 IP 방화벽 규칙을 구성 하는 명령은sp_set_database_firewall_rule (Azure SQL Database)를 사용한다.

[인터넷으로 통한 데이터베이스 서버 연결]

컴퓨터에서 인터넷을 통해 데이터베이스 서버에 연결 하려고 하면 먼저 방화벽은 연결에서 요청하는 데이터베이스에 대한 데이터베이스 수준 IP 방화벽 규칙에 대해 요청의 원래 IP 주소를 확인한다. 주소가 데이터베이스 수준 IP 방화벽 규칙에 지정 된 범위 내에 있는 경우 해당 규칙을 포함 하는 SQL database에 대해 연결이 가능하다. 주소가 데이터베이스 수준 IP 방화벽 규칙의 범위 내에 없는 경우 방화벽은 서버 수준 IP 방화벽 규칙을 확인한다. 주소가 서버 수준 IP 방화벽 규칙의 범위 내에 있는 경우 연결이 허용된다. 서버 수준 IP 방화벽 규칙은 Azure SQL Server에 있는 모든 SQL Database에 적용된다. 주소가 데이터베이스 수준 또는 서버 수준 IP 방화벽 규칙의 범위에 포함 되지 않은 경우 연결 요청이 실패한다. 로컬 컴퓨터에서 SQL Database에 액세스 하려면 네트워크와 로컬 컴퓨터의 방화벽이 TCP 포트 1433에서 나가는 통신을 허용 하는지 확인 한다.

[Azure 내부에서 데이터베이스 서버 연결]

Azure 내에서 호스트 되는 응용 프로그램이 SQL server에 연결할 수 있도록 하려면 Azure 연결을 사용 하도록 설정 해야 한다. Azure의 응용 프로그램이 데이터베이스 서버에 연결 하려고 하면 방화벽은 Azure 연결이 허용 되는지 확인한다. 시작 및 끝 IP 주소가 0.0.0.0 과 같은 방화벽 설정은 Azure 연결이 허용 됨을 나타낸다. 연결이 허용 되지 않으면 요청이 SQL Database 서버에 도달 하지 않는다. 이 옵션은 다른 고객의 구독에서 연결을 포함하여 Azure의 모든 연결을 허용 하도록 방화벽을 구성한다. 이 옵션을 선택 하는 경우 로그인 및 사용자 권한이 권한 있는 사용자만 액세스할 수 있도록 제한 해야한다.

[IP 방화벽 규칙 만들기 및 관리]

· Azure Portal에서 방화벽 규칙 생성

· Transact-SQL을 사용한 IP 방화벽 규칙 관리

View or SP	Level	Description
sys.firewall_rules	서버	현재 서버 수준 IP 방화벽 규칙을 표시
sp_set_firewall_rule	서버	서버 수준 IP 방화벽 규칙을 생성 및 업데이트
sp_delete_firewall_rule	서버	서버 수준 IP 방화벽 규칙을 제거
sys.database_firewall_rules	데이터베이스	현재 데이터베이스 수준 IP 방화벽 규칙을 표시
sp_set_database_firewall_rule	데이터베이스	데이터베이스 수준 IP 방화벽 규칙을 생성 및 업데이트
sp_delete_database_firewall_rule	데이터베이스	데이터베이스 수준 IP 방화벽 규칙을 제거

아래 예시는 기존 규칙을 검토ㅠㅏ고 Contoso서버에서 IP주소 범위를 사용하도록 설정하고 IP 방화벽 규칙을 삭제한다.

SELECT * FROM sys.firewall_rules ORDER BY name;

아래 예시는 서버 수준 IP 방화벽 규칙을 추가한다.

EXECUTE sp_set_firewall_rule @name = N'ContosoFirewallRule', @start_ip_address = '192.168.1.1', @end_ip_address = '192.168.1.10'

아래 예시는 서버 수준의 IP 방화벽 규칙을 삭제한다.

EXECUTE sp_delete_firewall_rule @name = N'ContosoFirewallRule'

[참고자료]

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-firewall-configure?WT.mc_id=DP-MVP-4039834

2019-11-06 / Sungwook Kang / http://sungwookkang.com/

Azure SQL Database, Azure SQL Data Warehouse, Azure 방화벽, Azure Database 방화벽, Azure Security

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여 (0)	2019.11.09
Azure SQL Database 보안 기능 개요 (0)	2019.10.17
Azure SQL Database의 서버리스 구매 모델 (0)	2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10

Azure SQL Database 보안 기능 개요

2019. 10. 17. 00:14

Azure SQL Database 보안 기능 개요

· Version : Azure SQL

Microsoft Azure SQL Database는 클라우드 및 엔터프라이즈 애플리케이션용 관계형 데이터베이스 서비스를 제공한다. Azure SQL Database보안 전략은 아래 그림 처럼 계층형 심층 방어 방식을 따르며 외부에서 내부로 적용된다.

고객 데이터를 보호하기 위해 방화벽은 Azure Virtual Network 트래픽 출처 또는 IP 주소를 기준으로 액세스 권한이 명시적으로 부여될 때까지 네트워크에서 데이터베이스 서버에 액세스할 수 없도록 차단한다. Azure SQL Database 보안 기능의 자세한 내용은 Microsoft 공식 문서를 참고한다.

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-security-overview?WT.mc_id=DP-MVP-4039834

[네트워크 보안]

· IP 방화벽 규칙 : IP 방화벽 규칙은 각 요청이 시작된 IP 주소를 기준으로 하여 데이터베이스 액세스 권한을 부여한다.

· Virtual Network 방화벽 규칙 : 가상 네트워크 서비스 엔드포인트는 Azure 백본을 통해 가상 네트워크 연결을 확장하며, 트래픽이 생성되는 가상 네트워크 서브넷을 Azure SQL Database가 식별할 수 있도록 한다. 트래픽이 Azure SQL Database로 전송되도록 하려면 SQL 서비스 태그를 사용해 네트워크 보안 그룹을 통한 아웃바운드 트래픽을 허용한다. Azure SQL Database는 가상 네트워크 규칙을 통해 Virtual Network 내의 선택한 서브넷에서 전송된 통신만 수락할 수 있습니다.

[액세스 관리]

Azure 내에서 데이터베이스와 데이터베이스 서버를 관리하는 작업은 포털 사용자 계정의 역할 할당을 통해 제어된다. Azure SQL Database는 두 가지 인증 유형을 지원한다.

· SQL 인증 : SQL 데이터베이스 인증은 사용자 이름과 암호를 사용하여 Azure SQL Database에 연결할 때 사용자가 수행하는 인증을 지칭한다.

· Azure Active Directory 인증 : Azure Active Directory 인증은 Azure AD(Azure Active Directory)의 ID를 사용하여 Azure SQL Database 및 SQL Data Warehouse에 연결하는 메커니즘이다. 관리자는 Azure AD 인증을 통해 데이터베이스 사용자의 ID 및 권한과 기타 Microsoft 서비스를 중앙 위치 한 곳에서 관리할 수 있다. SQL Database를 사용한 Azure AD 인증을 사용하려면 서버 관리자 Active Directory 관리자를 만들어야 한다. Azure AD 인증에서는 관리 계정과 페더레이션된 계정이 모두 지원된다. 페더레이션된 계정은 Azure AD와 페더레이션된 고객 도메인용 Windows 사용자 및 그룹을 지원한다. 사용 가능한 추가 Azure AD 인증 옵션으로는 다단계 인증 및 조건부 액세스를 비롯한 SQL Server Management Studio용 Active Directory 유니버설 인증 연결이 있다.

[Authorization]

권한은 데이터베이스 역할에 사용자 계정을 추가 하 고 해당 역할에 데이터베이스 수준 사용 권한을 할당 하거나 사용자에 게 특정 개체수준 사용권한을 부여하여 제어 된다. 필요한 경우 작업 기능을 수행 하는 데 필요한 최소한의 권한만 가진 사용자를 역할에 추가 한다. 서버 관리자 계정은 기본적으로 db_owner 역할의 구성원이며, 관리 업무를 사용하는 소수의 사용자 에게만 부여 되어야 한다.

Azure SQL Database 응용 프로그램의 경우 EXECUTE AS 를 사용 하 여 호출 된 모듈의 실행 컨텍스트를 지정 하거나 제한 된 권한으로 응용 프로그램 역할을 사용한다. 이렇게 하면 데이터베이스에 연결 하는 응용 프로그램에 응용 프로그램에 필요한 최소한의 권한만 부여된다.

· 행 수준 보안 : 행 수준 보안을 통해 고객은 쿼리를 실행하는 사용자의 특성(예: 그룹 멤버 자격 또는 실행 컨텍스트)을 기반으로 하여 데이터베이스 테이블의 행에 대한 액세스를 제어할 수 있다.

[위협 보호]

· Azure Monitor 로그 및 Event Hubs의 SQL 감사 : SQL Database 감사는 데이터베이스 활동을 추적하며 고객이 소유한 Azure Storage 계정의 감사 로그에 데이터베이스 이벤트를 기록하여 보안 표준 규정 준수 상태를 유지할 수 있도록 지원한다. 사용자는 감사를 통해 진행 중인 데이터베이스 활동을 모니터링하고 이전 활동을 분석 및 조사하여 잠재적 위협이나 악용 의심 사례 및 보안 위반을 식별할 수 있다.

· Advanced Threat Protection : Advanced Threat Protection은 SQL Server 로그를 분석 하 여 비정상적인 동작을 감지 하 고 잠재적으로 유해한 데이터베이스 액세스 또는 악용 시도를 감지 한다.

[정보 보호 및 암호화]

· TLS(전송 계층 보안)(전송 중 암호화) : SQL Server는 모든 연결에 대해 항상 암호화 (SSL/TLS)를 적용 한다. 연결 문자열에서 Encrypt 또는 TrustServerCertificate의 설정에 관계 없이 모든 데이터가 클라이언트와 서버 간에 "전송 중"으로 암호화 된다.

· 투명한 데이터 암호화(미사용 데이터 암호화) : Azure SQL Database용 TDE(투명한 데이터 암호화)는 원시 파일이나 백업에 무단/오프라인으로 액세스할 수 없도록 미사용 데이터를 보호하기 위해 보안 계층을 추가한다. TDE는 AES 알고리즘을 사용하여 전체 데이터베이스를 암호화 한다. Azure에서는 새로 만드는 모든 SQL Database가 기본적으로 암호화되며, 기본 제공 서버 인증서를 통해 데이터베이스 암호화 키가 보호된다. 서비스에서 인증서 유지 관리 및 순환을 관리하므로 사용자 입력은 불필요하며, 암호화 키를 직접 제어하려는 고객은 Azure Key Vault에서 키를 관리할 수 있다.

· Azure Key Vault으로 키 관리 : 고객은 TDE( 투명한 데이터 암호화)용으로 지원되는 BYOK(Bring Your Own Key)를 활용해 Azure의 클라우드 기반 외부 키 관리 시스템인  Azure Key Vault를 사용하여 키 관리 및 순환을 직접 제어할 수 있다. 데이터베이스의 키 자격 증명 모음 액세스 권한이 철회되면 데이터베이스를 암호 해독하여 메모리로 읽어들일 수 없다. 중앙 키 관리 플랫폼을 제공하며 철저하게 모니터링되는 HSM(하드웨어 보안 모듈)을 활용하는 Azure Key Vault를 사용하면 키와 데이터 관리 작업을 분리하여 보안 규정 준수 요구 사항을 충족할 수 있다.

· Always Encrypted(사용 중인 데이터 암호화) : Always Encrypted는 신용 카드 번호, 주민 등록 번호 또는 확인이 필요한 데이터와 같이 특정 데이터베이스 열에 저장된 중요한 데이터를 액세스할 수 없도록 보호하는 기능이다. 데이터는 항상 암호화되므로 암호화 키 액세스 권한이 있는 클라이언트 애플리케이션에서 처리해야 하는 경우에만 암호화된 데이터의 암호가 해독된다. 암호화 키는 SQL에 표시되지 않으며 Windows 인증서 저장소 또는 Azure Key Vault에 저장할 수 있다.

· 동적 데이터 마스킹 : SQL Database 동적 데이터 마스킹에서는 권한이 없는 사용자에 대해 중요한 데이터를 마스킹해 표시함으로써 데이터 노출을 제한한다. 동적 데이터 마스킹은 Azure SQL Database에서 잠재적으로 중요한 데이터를 자동으로 검색하고 애플리케이션 계층에 미치는 영향을 최소화하면서 이러한 필드를 마스킹할 수 있는 실행 가능한 권장 사항을 제공한다. 이 기능은 지정된 데이터베이스 필드를 통해 쿼리의 결과 집합에 있는 중요한 데이터를 혼란스럽게 만들면서 작동하지만 데이터베이스의 데이터를 변경하지는 않는다.

[참고자료]

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-security-overview?WT.mc_id=DP-MVP-4039834

2019-10-16 / Sungwook Kang / http://sungwookkang.com/

Azure SQL Database, Azure TDE, Azure Security, Azure Network Security, Azure SQL Dynamic Data Masking

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 및 Azure SQL Data Warehouse에 대한 액세스 제어 및 권한 부여 (0)	2019.11.09
Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙 (0)	2019.11.07
Azure SQL Database의 서버리스 구매 모델 (0)	2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10

Azure SQL Database의 서버리스 구매 모델

2019. 10. 15. 02:51

Azure SQL Database의 서버리스 구매 모델

· Version : Azure SQL

Azure SQL을 사용할 때, 다양한 구매모델이 있다. vCore 기반 모델, DTU 서비스 계층, 서버리스 컴퓨팅 계층이 있는데, 이번 포스트에서는 서버리스 구매 모델에 대해서 살펴본다. 자세한 내용은 Microsoft 공식 문서인 아래 링크를 참고 한다.

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-serverless?WT.mc_id=DP-MVP-4039834

Azure SQL Database 서버리스는 단일 데이터베이스에 대한 컴퓨팅 계층으로, 워크로드 수요 및 초당 사용된 컴퓨팅 양에 대한 청구를 기반으로 계산을 자동으로 조정한다. 서버리스 컴퓨팅 계층은 스토리지만 청구될 때 비활성 기간동안 데이터베이스를 자동으로 일시중지하고 활동이 반환되면 데이터베이스를 자동으로 다시 시작한다. 서버리스 컴퓨팅 계층은 자동 확장 및 자동 일시 중지에 대한 설정이 가능하고, 데이터베이스 성능 및 비용계산을 결정한다.

서버리스 Azure SQL Database 는 아래와 같은 특징을 가지고 있다.

· 최소 vCores 및 최대 vCores는 데이터베이스에 사용할 수 있는 계산 용량 범위를 정의할 수 있다. 메모리 및 I/O 제한은 지정된 vCore 범위에 비례한다.

· Autopause delay는 데이터베이스를 자동으로 일시 중지 하기 전에 비활성 상태로 유지 해야하는 기간을 정의한다. 데이터베이스는 다음 로그인 또는 다른 작업이 발생할 때 자동으로 다시 시작된다. 또는 autopausing를 사용 하지 않도록 설정할 수 있다.

· 서버리스 데이터베이스의 비용은 컴퓨팅 비용과 스토리지 비용의 합계이다.

· 계산 사용량이 구성된 최소 및 최대 한계 사이 인 경우 계산 비용은 사용 된 vCore 및 메모리를 기반으로 한다.

· 컴퓨팅 사용량이 구성된 최소 한계 미만인 경우 컴퓨팅 비용은 구성된 최소 vCore 및 최소 메모리를 기준으로 한다.

· 데이터베이스가 일시 정지되면 계산 비용은 0이며 스토리지 비용만 발생 한다.

· 스토리지 비용은 프로비저닝 된 컴퓨팅 계층과 동일한 방식으로 결정된다.

아래 표는 서버리스 컴퓨팅 계층과 프로비저닝된 컴퓨팅 계층 간의 차이점을 요악한다.

	서버리스 컴퓨팅	프로비저닝된 컴퓨팅
데이터베이스 사용패턴	시간이 지남에 따라 평균 컴퓨팅 활용도가 낮아 간헐적이고 예측할 수없는 사용량	시간이 지남에 따라 평균 컴퓨팅 사용률이 높은 정규 사용 패턴 또는 탄력적 풀을 사용하는 여러 데이터베이스
성능 관리 작업	적음	많음
컴퓨팅 크기 조정	자동	수동
컴퓨팅 응답성	비활성 기간 후 낮음	즉시
청구 세분성	초당	시간당

일반적으로 서버리스 데이터베이스는 max vCores 값에의해 설정된 한계 내에서 요청된 컴퓨팅에 대한 중단없이 리소스 요구를 충족시키기에 충분한 용량을 가진 머신에서 실행된다. 시스템이 몇 분 내에 리소스 요구를 충족시킬 수없는 경우로드 밸런싱이 자동으로 발생하는 경우가 있다. 예를 들어, 리소스 수요가 4 개의 vCore이지만 2개의 vCore 만 사용할 수 있는 경우 4 개의 vCore가 제공되기 전에로드 밸런싱에 최대 몇 분이 걸릴 수 있다. 연결이 끊어 질 때 작업이 끝날 때 짧은 기간을 제외하고 데이터베이스는로드 균형 조정 중에 온라인 상태로 유지 된다.

서버리스 데이터베이스의 메모리는 프로비저닝 된 컴퓨팅 데이터베이스보다 더 자주 회수됩니다. 이 동작은 서버리스 비용을 제어하는 데 중요하며 성능에 영향을 줄 수 있다. 프로비저닝 된 컴퓨팅 데이터베이스와 달리 CPU 또는 캐시 사용률이 낮으면 SQL 캐시의 메모리가 서버리스 데이터베이스에서 회수된다.

· 가장 최근에 사용한 캐시 항목의 전체 크기가 일정 기간 동안 임계값 아래로 떨어지면 캐시 활용도가 낮은 것으로 간주된다.

· 캐시 재사용이 트리거되면 대상 캐시 크기가 이전 크기의 일부로 점차 감소하고 사용량이 낮을 경우에만 회수 된다.

· 캐시 재사용이 발생하면 제거 할 캐시 항목을 선택하기위한 정책은 프로비저닝 된 컴퓨팅 데이터베이스와 동일하다.

· 캐시 크기는 구성 할 수있는 min vCore에 의해 정의 된 최소 메모리 제한 아래로 절대 축소되지 않는다.

프로비저닝 된 데이터베이스와 동일한 방식으로 동일한 속도로 디스크에서 데이터를 가져올 때 SQL 캐시가 커진다. 데이터베이스가 사용중이면 캐시는 최대 메모리 제한까지 제한되지 않은 상태로 커질 수 있다.

Autopausing 및 Autoresuming는 아래와 같은 제약조건에 따라 자동 활성 비활성 된다. 서버를 사용 하지않는 데이터베이스를 autoresume 및 autopause 하는 대기 시간은 일반적으로 1 분에서 autoresume로, 1-10 분에서 autopause로 정렬 된다.

[Autopausing]

Autopause 지연 기간 동안 다음 조건이 모두 true 인 경우 Autopausing이 트리거된다.

· 세션 수 = 0

· 사용자 풀에서 실행 되는 사용자 작업에 대한 CPU = 0

다음 기능은 autopausing을 지원 하지 않는다. 즉, 다음 기능 중 하나를 사용 하는 경우 데이터베이스 비활성 기간에 관계 없이 데이터베이스가 온라인 상태로 유지 된다.

· 지역에서 복제 (활성 지역 복제 및 자동 장애 조치 그룹)

· 장기 백업 보존 (LTR).

· SQL 데이터 동기화에 사용되는 동기화 데이터베이스 동기화 데이터베이스와 달리 허브 및 멤버 데이터베이스는 autopausing을 지원 한다.

· Elastic Pool에 사용 되는 작업 데이터베이스 이다.

Autopausing는 데이터베이스를 온라인 상태로 만들어야 하는 일부 서비스 업데이트를 배포 하는 동안 일시적으로 차단 된다. 이러한 경우 서비스 업데이트가 완료 되 면 autopausing가 다시 허용 된다.

[Autoresuming]

언제 든 지 다음 조건 중 하나라도 충족 되 면 Autoresuming가 트리거 된다.

기능	자동 다시 시작 트리거
인증 및 권한 부여	로그인
위협 검색	데이터베이스 또는 서버 수준에서 위협 감지 설정 사용/사용 안 함 데이터베이스 또는 서버 수준에서 위협 검색 설정 수정
데이터 검색 및 분류	민감도 레이블 추가, 수정, 삭제 또는 보기
감사	감사 레코드 보기, 감사 정책 업데이트 또는 보기
데이터 마스킹	데이터 마스킹 규칙 추가, 수정, 삭제 또는 보기
투명한 데이터 암호화	투명한 데이터 암호화 상태 또는 상태 보기
쿼리(성능) 데이터 저장소	쿼리 저장소 설정 수정 또는 보기
자동 튜닝	자동 인덱싱과 같은 자동 실행 추천 사항의 적용 및 확인
데이터베이스 복사	복사본으로 데이터베이스를 만든다. BACPAC 파일로 내보낸다.
SQL 데이터 동기화	구성 가능한 예약에 따라 실행되거나 수동으로 수행되는 허브 및 멤버 데이터베이스 간의 동기화
특정 데이터베이스 메타데이터 수	새 데이터베이스 태그를 추가 하 고 있다. 최대 vCores, min vCores 또는 autopause delay를 변경 한다.
SSMS	18.1 이전의 SSMS 버전을 사용 하 고 서버의 모든 데이터베이스에 대 한 새 쿼리 창을 열면 동일한 서버에서 자동으로 일시 중지 된 데이터베이스를 다시 시작 합니다. SSMS 버전 18.1 이상을 사용 하는 경우에는이 동작이 발생 하지 않는다.

[참고자료]

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-serverless?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-serverless?WT.mc_id=DP-MVP-4039834

2019-10-14 / Sungwook Kang / http://sungwookkang.com/

Azure SQL, Managed Instance, Azure DTU,

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 및 Azure SQL Data Warehouse IP 방화벽 규칙 (0)	2019.11.07
Azure SQL Database 보안 기능 개요 (0)	2019.10.17
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10
Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델 (0)	2019.10.10

Azure SQL에서 선택할 수 있는 다양한 배포 옵션

2019. 10. 11. 00:26

Azure SQL에서 선택할 수 있는 다양한 배포 옵션

· Version : Azure SQL

Microsoft Azure SQL은 다양한 배포 옵션을 제공한다. 리프트 앤 시프트 마이그레이션에서 기존 애플리케이션 현대화, 최신 클라우드 서비스 구축에 이르기 까지 Microsoft SQL Server 엔진으로 구동되는 여러 배포 옵션을 제공한다. Azure SQL은 단일 통합 관리 환경을 제공하여 다양한 SQL Server 기반 응용 프로그램 모음을 대규모로 관리하는 복잡성을 제거한다. 배포에 옵션에 관한 자세한 내용은 아래 링크를 참고 한다.

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=DP-MVP-4039834

Microsoft의 데이터 플랫폼은 SQL Server 기술을 활용하며 물리적 온-프레미스 컴퓨터, 사설 클라우드 환경, 타사 호스팅 사설 클라우드 환경 및 공용 클라우드에 걸쳐 사용할 수 있도록 한다. Azure virtual machine ( SQL virtual machines)에 대한 SQL Server를 사용하면 동일한 서버 제품, 개발 도구 및 전문 지식 집합을 사용하는 동시에 온-프레미스 및 클라우드 호스팅 배포를 조합하여 고유하고 다양한 비즈니스 요구사항을 충족할 수 있다. 아래 그림에 표시된 것처럼 각 제품은 인프라를 통해 보유하고 있는 관리 수준 및 비용 효울성을 기준으로 지정할 수 있다.

[SQL Database ]

안정적인 최신 SQL Server 기능을 사용하고 개발 및 마케팅에 시간 제한이 있는 최신 클라우드 응용 프로그램에 가장 적합하다. 안정적인 최신 SQL Server Enterprise Edition을 기준으로 하는 완전 관리형 SQL 데이터베이스 엔진은PaaS(Platform as a Service) 범주로 분류되며, Azure 클라우드에서 호스트되는 관계형 DBaaS(Database as a Service)이다. SQL 데이터베이스는 여러 배포 옵션을 제공하며, 각 배포 옵션은 Microsoft에서 소유하고 호스트하고 유지 관리하는 표준화된 하드웨어 및 소프트웨어를 기반으로 한다. SQL Server를 사용하면 광범위한 구성 (온-프레미스 또는 Azure 가상 컴퓨터)을 필요로 하는 기본 제공기능 및 기능을 사용할 수 있다. SQL Database를 사용할 경우 추가 성능에 맞게 중단 없이 강화 및 확장하는 옵션을 통해 사용량에 따라 지불한다. SQL Database에는 기본 제공 고가용성, 인텔리전스 및 관리와 같이 SQL Server에서 사용할 수 없는 몇 가지 추가 기능이 있다.

· 데이터베이스 서버를 통해 관리되는 자체 리소스 집합이 포함 된 단일 데이터베이스는 SQL Server의 포함 된 데이터베이스와 비슷하다. 이 옵션은 새로운 클라우드 기반 애플리케이션의 최신 애플리케이션 개발에 맞게 최적화되어 있다. Hyperscale 및 서버 리스 옵션을 사용할 수 있다.

· 데이터베이스 서버를 통해 관리 되는 리소스의 공유 집합이 있는 데이터베이스를 Elastic Pool로 이동하거나 Elastic Pool에서 제거할 수 있다. 이 옵션은 다중 테넌트 SaaS 응용 프로그램 패턴을 사용하여 새로운 클라우드를 사용 하는 응용 프로그램의 최신 응용 프로그램 개발에 최적화 되어 있다. Elastic Pool은 변수 사용 패턴이 있는 여러 데이터베이스의 성능을 관리 하는 비용 효율적인 솔루션을 제공한다.

· 단일 데이터베이스 및 Elastic Pool의 그룹을 관리하는 데 사용되는 데이터베이스 서버입니다. 데이터베이스 서버는 여러 단일 데이터베이스 또는 풀링된 데이터베이스, 로그인, 방화벽 규칙, 감사 규칙, 위협 검색 정책및 장애조치 (failover) 그룹에 대한 중앙 관리 지점 역할을 한다.

[SQL Managed Instance]

클라우드로의 대부분 마이그레이션에 적합 하다.Managed Instance는 리프트 앤 시프트 준비가 된 리소스의 공유 집합을 사용하는 시스템 및 사용자 데이터베이스의 컬렉션이다. 안정적인 최신 SQL Server 기능을 사용하고 최소한의 변경으로 클라우드로 마이그레이션되는 새 응용 프로그램 또는 기존 온-프레미스 응용 프로그램에 가장 적합하다. Managed Instance는 데이터베이스의 공유 리소스 및 추가 인스턴스 범위 기능을 제공 하는 Microsoft SQL Server 데이터베이스 엔진의 인스턴스와 비슷하다. Managed Instance는 데이터베이스 변경없이 또는 최소한의 변경으로 온-프레미스에서 데이터베이스를 마이그레이션할 수 있도록 지원한다. 이 옵션은 Azure SQL Database의 모든 PaaS 혜택을 제공하지만 이전에 SQL VM에서만 사용할 수 있었던 기능을 추가한다. 여기에는 기본 가상 네트워크(VNet) 및 온-프레미스 SQL Server와의 거의 100% 호환성이 포함된다. Managed Instance는 SQL Server를 Azure로 마이그레이션하기 위한 전체 SQL Server 액세스 및 기능 호환성을 제공한다.

[SQL virtual machines]

OS 수준 액세스를 필요로 하는 마이그레이션 및 응용 프로그램에 가장 적합하다. SQL 가상 머신은 변경 내용을 최소화 하거나 변경 하지 않고 클라우드로 신속하게 마이그레이션해야 하는 기존 응용 프로그램에 대해 리프트 앤 시프트가 준비된다. SQL 가상 머신은 Azure로의 마이그레이션을 위해 SQL Server 인스턴스 및 기본 OS에 대 한 모든 관리 권한을 제공한다. SQL 가상 머신은 IaaS (Infrastructure as a Service )에 포함 되며, Azure 클라우드의 완전히 관리 되는 VM (가상 머신) 내에서 SQL Server를 실행할 수 있다. SQL 가상 머신은 Microsoft에서 소유하고 호스트 하며 유지관리하는 표준화된 하드웨어에서도 실행된다. SQL 가상 컴퓨터를 사용하는경우 이미 SQL Server 이미지에 포함 된 SQL Server 라이선스를 지불하거나 기존 라이선스를 쉽게 사용할 수 있다. 필요에 따라 VM을 중지하거나 다시 시작할 수도 있다. 클라우드에 설치 되 고 호스트 되는 SQL Server는 Azure에서 실행 되는 Windows Server 또는 Linux 가상 컴퓨터 (IaaS (infrastructure as a service) 라고도 함)에서 실행된다. SQL 가상 머신은 데이터베이스를 변경 하지않고 온-프레미스 SQL Server 데이터베이스 및 응용 프로그램을 마이그레이션하는데 적합한 옵션이다. 모든 최신 버전의 SQL Server를 IaaS 가상 머신에 설치할 수 있다. SQL database와 SQL Managed Instance의 가장 중요한 차이점은 SQL Server VM이 데이터베이스 엔진에 대한 모든 권한을 허용 한다는 것이다. 유지 관리/패치를 시작할 시기를 선택하고, 복구 모델을 단순 또는 대량 로그로 변경하거나, 필요에 따라 서비스를 일시 중지 하거나 시작 하 고, SQL Server 데이터베이스 엔진을 완벽하게 사용자 지정할 수 있다. 이 추가 컨트롤을 사용 하면 가상 머신을 관리하는 추가 책임이 있다.

Azure Portal에서는 SQL 가상 머신을 비롯 한 모든 Azure SQL 리소스 를 관리할 수 있는 단일 페이지를 제공 한다. Azure SQL 리소스 페이지에 액세스 하려면 Azure Portal의 왼쪽 메뉴에서 Azure SQL을 선택한. Azure SQL이 목록에 없는 경우 모든 서비스를 선택한 다음 검색 상자에 Azure SQL 을 입력 합니다.기존 리소스를 관리 하려면 목록에서 원하는 항목을 선택한다. 새 Azure SQL 리소스를 만들려면 [+ Add]를 선택한다.

[+ Add]를 선택한 후 여러 옵션에 대한 추가 정보를 확인할 수 있다.

[참고자료]

· (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=DP-MVP-4039834

· (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=DP-MVP-4039834

2019-10-10 / Sungwook Kang / http://sungwookkang.com/

Azure SQL, Managed Instance, Azure SQL Database, Azure Virtual Machine

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database 보안 기능 개요 (0)	2019.10.17
Azure SQL Database의 서버리스 구매 모델 (0)	2019.10.15
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10
Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델 (0)	2019.10.10
Azure SQL Database 서비스 (0)	2019.10.10

Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션

2019. 10. 10. 23:33

Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션

l Version : Azure SQL

Azure SQL Database에서 vCore(가상코어) 구매 모델은 컴퓨팅 및 스토리지 리소스를 독립적으로 확장하고 온-프레미스 성능을 조정하며 가격을 최적화할 수 있다. 또한 하드웨어 세대(Generation)을 선택할 수 있다. 하드웨어 세대는 시간 및 지역에 따라 지원되는 세대가 다르므로, 항상 Microsoft의 공식 문서를 참고할 수 있도록 한다.

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-vcore/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-service-tiers-vcore/?WT.mc_id=DP-MVP-4039834

vCore 기반 구매 모델은 범용, 중요 비스니스, 하이퍼 규모의 세 계층을 제공한다. 이러한 서비스 계층은 다양한 계산 크기, 고가용성 디자인, 내결함성 방법, 저장소의 형식 및 크기, I/O 범위로 구분된다. 백업에 필요한 스토리지와 보존 기간을 개별적으로 구성해야 한다. 백업 보존 기간을 설정하려면 Azure Portal에서 백업 관리에서 할 수 있다. 아래 표는 세 계층 간의 차이점을 설명한다.

	범용	업무상 중요	대규모
적합한 대상	예산 중심의 균형 잡힌 컴퓨팅 및 스토리 옵션을 제공	트랜잭션 속도가 높고 IO 대기 시간이 낮은 OLTP 및 빠른 장애조치에 높은 복원력을 제공	대부분의 비즈니스 워크로드. 최대 100TB의 자동 스케일링 스토리지 크기, 유동적인 수직 및 수평 컴퓨팅 스케일링, 빠른 데이터 베이스 복원
컴퓨팅	프로 비전 된 계산: Gen4: 1 개에서 24 개 까지의 vCores Gen5: 2 ~ 80 vCores 서버를 사용 하지 않는 계산: Gen5: 0.5-16 vCores	프로 비전 된 계산: Gen4: 1 개에서 24 개 까지의 vCores Gen5: 2 ~ 80 vCores	프로 비전 된 계산: Gen4: 1 개에서 24 개 까지의 vCores Gen5: 2 ~ 80 vCores
메모리	프로 비전 된 계산: Gen4: vCore당 7GB Gen5: vCore당 5.1GB 서버를 사용 하지 않는 계산: Gen5: VCore 당 최대 24gb	프로 비전 된 계산: Gen4: vCore당 7GB Gen5: vCore당 5.1GB	프로 비전 된 계산: Gen4: vCore당 7GB Gen5: vCore당 5.1GB
스토리지	원격 저장소 사용	로컬 SSD 저장소 사용	필요에 따라 유연하게 증가하는 저장소 사용, 최대 100TB 저장소 지원
I/O 처리량 (근사치)	단일 데이터베이스 및 탄력적 풀: 최대 4만의 vCore 당 500 IOPS. 관리 되는 인스턴스: 파일의 크기에 따라 다름	최대 20만의 코어 당 5000 IOPS 최대 IOPS	Hyperscale은 여러 수준에서 캐싱을 사용 하는 다중 계층 아키텍처이며 유효 IOPs는 워크 로드에 따라 다름
가용성	복제본 1 개, 읽기 확장 복제본 없음	3개 복제본, 1개 읽기 크기 조정 복제본, 영역 중복 HA (고가용성)	1 읽기/쓰기 복제본 및 0-4 읽기 확장 복제본
백업	읽기 액세스 지역 중복 저장소 (RA-GRS), 7-35 일 (기본적으로 7 일)	RA-GRS, 7-35일(기본값: 7일)	Azure 원격 저장소의 스냅숏 기반 백업 복원은 빠른 복구를 위해 이러한 스냅샷을 사용, 백업은 즉시 수행
In-Memory	지원되지 않음	지원됨	지원되지 않음

vCore 기반 구매 모델의 프로비전 된 계산 계층에서는 SQL Server에 대한 Azure 하이브리드 혜택을 사용하여 SQL Database에 대한 할인된 요금으로 기존 라이선스를 교환할 수 있다. 이 Azure 혜택을 통해 소프트웨어 보증이 있는 온-프레미스 SQL Server 라이선스를 사용하여 Azure SQL Database 최대 30%까지 절약할 수 있다.

Azure 하이브리드 혜택을 사용하면 SQL database 엔진 자체 (기본 계산 가격 책정)에 기존 SQL Server 라이선스를 사용하여 기본 Azure 인프라에 대해서만 비용을 지불하도록 선택하거나 기본 인프라와 SQL Server에 대해 비용을 지불할 수 있다.

DTU 기반 모델에서 vCore 기반 구매 모델로 마이그레이션하는 것은 standard 및 premium 서비스 계층에 있는 데이터베이스 간의 지역에서 복제 관계를 업그레이드하거나 다운그레이드 하는 것과 비슷하다. 마이그레이션 중에 지역에서 복제를 중지할 필요는 없지만 아래 규칙을 따라야 한다.

l 업그레이드하는 경우 보조 데이터베이스를 먼저 업그레이드한 다음, 주 데이터베이스를 업그레이드해야 한다.

l 다운그레이드하는 경우 반대 순서로 주 데이터베이스를 먼저 다운그레이드한 다음, 보조 데이터베이스를 다운그레이드 해야 한다.

l Elastic Pool 복제를 사용하는 경우 하나의 풀을 기본 풀로 지정하고 다른 풀을 보조 데이터베이스로 지정하는 것이 좋다. 이 경우 Elastic Pool을 마이그레이션하는 경우 동일한 순서의 지침을 사용해야 한다.

l 주 데이터베이스와 보조 데이터베이스를 모두 포함하는 Elastic Pool을 사용하는 경우 사용률이 높은 풀을 주 데이터베이스로 처리하고 그에 따라 시퀀싱 규칙을 따른다.

아래 표는 특정 마이그레이션 시나리오에 대한 지침을 제공한다.

현재 서비스 계층	대상 서비스 계층	마이그레이션 유형	사용자 작업
Standard	범용 가상 컴퓨터	수평	순서에 관계없이 마이그레이션할 수 있지만 적절한 vCore 크기 조정을 보장해야 한다.
Premium	중요 비즈니스용	수평	순서에 관계없이 마이그레이션할 수 있지만 적절한 vCore 크기 조정을 보장해야 한다.
Standard	중요 비즈니스용	업그레이드	먼저 보조 데이터베이스를 마이그레이션해야 한다.
중요 비즈니스용	Standard	다운그레이드	먼저 주 데이터베이스를 마이그레이션해야 한다.
Premium	범용 가상 컴퓨터	다운그레이드	먼저 주 데이터베이스를 마이그레이션해야 한다.
범용 가상 컴퓨터	Premium	업그레이드	먼저 보조 데이터베이스를 마이그레이션해야 한다.
중요 비즈니스용	범용 가상 컴퓨터	다운그레이드	먼저 주 데이터베이스를 마이그레이션해야 한다.
범용 가상 컴퓨터	중요 비즈니스용	업그레이드	먼저 보조 데이터베이스를 마이그레이션해야 한다.

여러 데이터베이스가 있는 장애 조치 그룹을 마이그레이션하려면 주 데이터베이스와 보조 데이터베이스를 개별적으로 마이그레이션해야 한다. 이 과정에서 동일한 고려사항과 순서지정 규칙이 적용된다. 데이터베이스를 vCore 기반 구매 모델로 변환한 후에는 장애 조치 (failover) 그룹이 동일한 정책 설정에 계속 적용된다.

주 데이터베이스에 사용한 것과 동일한 서비스 계층을 사용하여 지역에서 복제 보조 데이터베이스 (지역 보조 데이터베이스)를 만들 수 있다. 로그 생성 비율이 높은 데이터베이스의 경우 주 데이터베이스와 동일한 계산 크기로 지역 보조 데이터베이스를 만드는 것이 좋다.

단일 주 데이터베이스에 대한 Elastic Pool에서 지역 보조 데이터베이스를 만드는 경우 풀에 대 한 maxVCore 설정이 주 데이터베이스의 계산 크기와 일치하는지 확인한다. 다른 Elastic Pool에서 주 데이터베이스에 대 한 지역 보조 데이터베이스를 만드는 경우 풀에 동일한 @no__t 0 설정을 갖는 것이 좋다.

[참고자료]

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-vcore/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-service-tiers-vcore/?WT.mc_id=DP-MVP-4039834

2019-10-09 / Sungwook Kang / http://sungwookkang.com/

Azure SQL, Managed Instance, Azure vCore

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database의 서버리스 구매 모델 (0)	2019.10.15
Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델 (0)	2019.10.10
Azure SQL Database 서비스 (0)	2019.10.10
Azure SQL Managed Instance 에서 SQL Agent의 Job history 기록 보관하기 (0)	2019.03.26

Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델

2019. 10. 10. 23:32

Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델

l Version : Azure SQL

이전 포스트에서 Azure SQL Database 의 전반적인 내용에 대해서 살펴 보았다. Azure SQL의 Overview에 대해서 자세히 알고 싶으면 아래 링크를 참고한다.

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-technical-overview/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-technical-overview/?WT.mc_id=DP-MVP-4039834

Azure SQL Database의 구매 모델에는 vCore 기반, DTU 기반, 서버리스 등 몇 가지 모델이 있지만 이번 포스트에서는 DTU 기반 구매 모델에 대해서 살펴본다. DTU 기반 구매 모델에 대한 자세한 정보는 아래 링크를 통해서 확인할 수 있다.

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-service-tiers-dtu/?WT.mc_id=DP-MVP-4039834

DTU 기반 구매 모델에서 서비스 계층은 포함된 스토리지의 고정된 양, 고정된 백업 보존 기간 및 고정 가격을 갖춘 다양한 컴퓨팅 크기로 구분된다. DTU 기반 구매 모델의 모든 서비스 계층은 가동 중지 시간을 최소화하면서 계산 크기를 유연하게 변경할 수 있다. Single Database 및 Elastic Pool은 서비스 계층 및 컴퓨팅 크기에 따라 시간 단위로 청구된다. 단, Managed Instance는 DTU 구매 모델을 지원하지 않는다.

서비스 계층을 선택하는 방법은 비즈니스의 연속성, 스토리지 및 성능 요구사항에 따라 다르다. 아래 표는 서비스 계층에 따른 특징을 간략히 표로 정리한 것이다.

	기본	표준	Premium
대상 워크로드	개발 및 프로덕션	개발 및 프로덕션	개발 및 프로덕션
SLA	99.99%	99.99%	99.99%
최대 백업 보존	7일	35일	35일
CPU	낮음	낮음, 보통, 높음	보통, 높음
I/O 처리량 (근사치)	DTU 당 1-5 IOPS	DTU 당 1-5 IOPS	DTU 당 25 IOPS
I/O 대기시간 (근사치)	5ms(읽기), 10ms(쓰기)	5ms(읽기), 10ms(쓰기)	2ms (읽기/쓰기)
Columnstore indexing	해당 사항 없음	S3 이상	지원됨
In-memory OLTP	해당 사항 없음	해당 사항 없음	지원됨

컴퓨팅 크기는 단일 데이터베이스에 대해서는 DTU(데이터베이스 트랜잭션 단위), Elastic Pool에 대해서는 eDTU(Elastic 데이터베이스 트랜잭션 단위)로 표현된다. DTU 및 eDTU에 대한 내용은 아래 링크에서 자세히 확인할 수 있다.

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-purchase-models#dtu-based-purchasing-model/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-purchase-models#dtu-based-purchasing-model/?WT.mc_id=DP-MVP-4039834

아래 표는 Single Database 대한 최대 스토리지 및 DUT 크기를 나타낸다.

	기본	표준	Premium
최대 스토리지 크기	2GB	1TB	4TB
최대 DTU	5	3000	4000

아래 표는 Elastic Pool의 최대 스토리지 및 eDTU 크기를 나타낸다.

	기본	Standard	Premium
데이터베이스당 최대 스토리지 크기	2GB	1TB	1TB
풀당 최대 스토리지 크기	156GB	4TB	4TB
데이터베이스당 최대 eDTU	5	3000	4000
풀당 최대 eDTU	1600	3000	4000
풀당 최대 데이터베이스 수	500	500	100

[참고자료]

l (영어) https://docs.microsoft.com/en-us/azure/sql-database/sql-database-service-tiers-dtu/?WT.mc_id=DP-MVP-4039834

l (한국어) https://docs.microsoft.com/ko-kr/azure/sql-database/sql-database-service-tiers-dtu/?WT.mc_id=DP-MVP-4039834

2019-10-09 / Sungwook Kang / http://sungwookkang.com/

Azure SQL, Managed Instance, Azure DTU

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL에서 선택할 수 있는 다양한 배포 옵션 (0)	2019.10.11
Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10
Azure SQL Database 서비스 (0)	2019.10.10
Azure SQL Managed Instance 에서 SQL Agent의 Job history 기록 보관하기 (0)	2019.03.26
Azure VM에서 SQL Server에 대한 저장소 구성 지침 (0)	2019.03.26

Azure SQL Database 서비스

2019. 10. 10. 00:41

Azure SQL Database 서비스

· Version : Azure SQL

Azure SQL Database는 관리 서비스로 제공되는 범용 관계형 데이터베이스 이며, Azure Portal에서 응용 프로그램 및 솔루션에 대한 고가용성 및 고성능 데이터 저장소 계층을 만들 수 있다. 데이터 또한graph, JSON, spatial(공간), XML등 관계형 데이터와 비관계형 구조 모두 처리할 수있다. Azure SQL Database는 Microsoft SQL Server 데이터베이스 엔진을 기반으로 하며, high-performance In-Memory Optimize 기능및 지능형 쿼리 같은 고급 처리 기술을 사용할 수 있다. 실제 Microsoft에서는 최신 기능은 Azure SQL Database로 출시한 다음 SQL Server로 출시한다.

Azure SQL Database에 대한 자세한 내용은 아래 링크를 참고 한다.

· What is the Azure SQL Database service? : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-technical-overview/?WT.mc_id=DP-MVP-4039834

SQL Database를 사용하면 vCore 기반 구매 모델 및 DTU 기반 구매 모델이라는 두 가지 구매 모델 내에서 성능을 쉽게 정의하고 확장할 수 있다. (vCore 및 DTU에 대한 내용은 다른 포스트에서 자세히 다루도록 한다.) SQL Database는 고가용성, 백업 및 기타 일반적인 유지 관리 작업을 기본 제공하는 완전히 관리되는 서비스이다. Microsoft는 SQL 및 운영체제 코드의 모든 패치 및 업데이트를 처리한다. 사용자는 기본 인프라를 관리할 필요가 없다.

[Deployment models]

Azure SQL Database는 Azure SQL 데이터베이스에 다음과 같은 옵션을 제공한다.

· Single Database는 완전히 관리되는 격리된 데이터베이스를 나타낸다. 안정적인 단일 데이터 원본이 필요한 경우 최신 클라우드 응용프로그램 및 마이크로 서비스를 사용하는 경우 이 옵션을 사용할 수 있다. 단일 데이터베이스는 Microsoft SQL Server 데이터베이스 엔진의 포함된 데이터베이스와 비슷하다.

· Managed instance는 Microsoft SQL Server 데이터베이스 엔진의 완전히 관리되는 인스턴스이다. 함께 사용할 수 있는 데이터베이스 집합이 포함되어 있다. 온-프레미스 SQL Server 데이터베이스를 Azure 클라우드로 쉽게 마이그레이션할 수 있으며, 데이터베이스 엔서 제공하는 SQL Server 데이터베이스 기능을 사용해야하는 경우 이 옵션을 선택한다.

· Elastic Pool은 CPU, 메모리등의 공유 리소스 집합을 포함하는 단일 데이터베이스 컬렉션이다. 단일 데이터베이스를 Elastic Pool로 이동하거나 Elastic Pool에서 제거할 수 있다.

[Scalable performance and pools]

할당된 리소스의 양을 정의할 수 있다.

· Single Database를 사용하는 경우 각 데이터베이스는 다른 데이터베이스와 격리 되며 이식 가능하다. 각 데이터베이스에는 자체 보장된 컴퓨팅, 메모리 및 스토리지 리소스가 있다. 데이터베이스에 할당된 리소스양은 해당 데이터베이스 전용이며 Azure의 다른 데이터베이스와 공유되지 않는다. 단일 데이터베이스 리소스를 동적으로 확장 및 축소 할 수 있다. 또한 hyperscale service tier를 사용하면 빠른 백업 및 복원기능을 통해 100TB 까지 확장할 수 있다.

· Elastic Pool을 사용하면 풀에 있는 모든 데이터베이스에서 공유하는 리소스를 할당할 수 있다. 새 데이터베이스를 만들거나 기존 단일 데이터베이스를 리소스 풀로 이동하여 리소스 사용을 최대화 하고 비용을 절감할 수 있다. 또한 이 옵션을 사용하면 Elastic Pool 리소스를 동적으로 확장 및 축소할 수 있다.

· Managed Instance를 사용할 경우 각각의 관리되는 인스턴스가 다른 인스턴스에서 격리되고 리소스가 보장된다. Managed Instance 내에서 데이터베이스 인스턴스는 리소스 집합을 공유한다. Managed Instance는 리소스를 동적으로 확장 및 축소할 수 있다.

동적확장성과 자동 크기 조정은 의미가 조금 다르다. 자동 크기 조정은 서비스가 조건에 따라 자동으로 크기를 조정하는 경인 반면, 동적 확장성은 가동 중지 시간 없이 수동 크기 조정을 허용한다. Single Database 옵션은 수동 동적확장성을 지원하지만 자동 크기 조정 기능은 지원하지 않는다. 더 많은 자동 환경을 위해 데이터베이스에서 개별 요구 사항에 따라 풀의 리소스를 공유할 수 있도록 하는 Elastic Pool을 사용하는 것이 좋다.

[Purchasing models]

SQL Database는 다음과 같은 구매 모델을 제공한다.

· vCore 기반 구매 모델 : vCore 수, 메모리 양, 스토리지 양 및 속도를 선택할 수 있다. vCore 기반 구매 모델을 사용하면 SQL Server 용 Azure 하이브리드 혜택을 사용하여 비용을 절감 할 수 있다.

· DTU 기반 구매 모델 : DTU 기반 구매 모델은 3 가지 서비스 계층에 컴퓨팅, 메모리 및 I / O 리소스를 혼합하여 다양한 워크로드를 지원합니다. 각 계층 내의 컴퓨팅 크기는 이러한 리소스를 다르게 혼합하여 추가 스토리지 리소스를 추가 할 수 있다.

· 서버리스 모델은 워크로드 요구를 기반으로 계산을 자동으로 조정하고 초당 사용 된 계산량을 청구한다. 또한 서버리스 컴퓨팅 계층은 스토리지만 청구될 때 비활성 기간 동안 데이터베이스를 자동으로 일시 중지하고 활동이 반환되면 데이터베이스를 자동으로 다시 시작한다.

[Elastic pools to maximize resource utilization]

사용 패턴이 비교적 예측 가능한 경우 많은 비즈니스 및 애플리케이션에서 단일 데이터베이스를 만들고 필요에 따라 충분히 성능을 확정하거나 축소할 수 있다. 예측할 수 없는 경우 비즈니스 모델을 관리하기 어려운데 Elastic Pool은 이 문제를 해결하도록 설계되어 있다. 개별 데이터베이스 대신 풀에 성능 리소스를 할당한다. 단일 테이터베이스 성능이 아닌 풀의 집합적 성능 리소스에 대해 비용을 지불한다.

Elastic Pool을 사용하면 리소스에 대한 요구가 변동함에 따라 데이터베이스 성능을 높이거나 낮추는데 집중할 필요가 없다. 풀링된 데이터베이스는 필요에 따라 탄력적 풀의 성능 리소스를 사용한다. 풀링된 데이터베이스는 풀의 한도를 사용하지만 초과하지 않으므로 개별 데이터베이스 사용량을 예측할 수 없는 경우에도 비용을 계속 예측할 수 있다.

[Extensive monitoring and alerting capabilities]

Azure SQL Database는 고급 모니터링 및 문제 해결 기능을 제공한다. 최신 버전의 SQL Server 데이터베이스 엔진에서 제공하는 기본 모니터링 뿐만 아니라 Azure에서 제공되는 PassS 모니터링 기능을 통해 수 많은 데이터베이스 인스턴스를 모니터링하고 문제를 해결 할 수 있다.

· Azure Storage : 적은 비용으로 방대한 양의 원격 분석을 보관

· Azure Event Hubs : SQL Database 원격 분석을 사용자 지정 모니터링 솔루션 또는 핫 파이프 라인과 통합한다.

· Azure Monitor logs : 보고, 경고 및 완화 기능이있는 기본 제공 모니터링 솔루션.

[Availability capabilities]

Azure 가용 영역은 단일 지역 내 단일 데이터 센터 건물의 중단을 방지하려고한다. 건물의 전력 또는 네트워크 손실을 방지한다. SQL Database에서는 서로 다른 가용 영역 (다른 건물, 효과적으로)에 서로 다른 복제본을 배치한다. SQL Database는 기본 제공 비즈니스 영속성 및 글로벌 확장성 기능을 제공한다.

· Automatic backups : SQL Database는 SQL 데이터베이스의 전체, 차등 및 트랜잭션 로그 백업을 자동으로 수행하여 특정 시점으로 복원할 수 있도록 한다. 단일 데이터베이스 및 풀링된 데이터베이스의 경우 장기 백업 보존을 위해 Azure Storage에 전체 데이터베이스 백업을 저장하도록 SQL Database를 구성할 수 있다. Managed Instance의 경우 장기 백업 보존을 위해 복사 전용 백업도 수행할 수 있다.

· Point-in-time restores : 모든 SQL Database 배포 옵션은 SQL 데이터베이스에 대해 자동 백업 보존기간내의 특정 시점으로 복구를 지원한다.

· Active get-replication : 단일 데이터베이스 및 풀링된 데이터베이스 옵션을 사용하여 동일하거나 전 세계적으로 분산된 Azure 데이터센터에서 최대 4개의 읽기 가능한 보조 데이터베이스를 구성할 수 있다.

· Auto-failover groups : 모든 SQL Database 배포 옵션을 사용하면 장애 조치 그룹을 사용하여 글로벌 규모로 고가용성 및 부하 분산을 사용하도록 설정할 수 있다. 여기에는 데이터베이스, Elastic Pool 및 Managed Instance의 지역 복제 및 장애조치가 포함된다.

· Zone-redundant database : SQL Database를 사용하면 여러 가용 영역에 걸쳐 프리미엄 또는 비즈니스 크리티컬 데이터베이스 또는 Elastic Pool을 프로비저닝 할 수 있다. 이러한 데이터베이스와 Elastic Pool에는 고 가용성을위한 중복 복제본이 여러 개 있으므로 이러한 복제본을 여러 가용 영역에 배치하면 복원력이 향상된다. 여기에는 데이터 손실없이 데이터 센터 스케일 오류에서 자동으로 복구하는 기능이 포함된다.

[참고자료]

· What is the Azure SQL Database service? : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-technical-overview/?WT.mc_id=DP-MVP-4039834

2019-10-09 / Sungwook Kang / http://sungwookkang.com

Azure SQL, Managed Instance, Azure overview

저작자표시 비영리 변경금지

'SQL Server > SQL on Azure' 카테고리의 다른 글

Azure SQL Database의 vCore 모델과 DTU 모델에서 마이그레이션 (0)	2019.10.10
Azure SQL Database의 서비스 계층에 따른 DTU 구매 모델 (0)	2019.10.10
Azure SQL Managed Instance 에서 SQL Agent의 Job history 기록 보관하기 (0)	2019.03.26
Azure VM에서 SQL Server에 대한 저장소 구성 지침 (0)	2019.03.26
Azure SQL에 도입된 새로운 집계 함수 APPROX_COUNT_DISTINCT (0)	2019.03.26

SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소

2019. 9. 24. 23:52

SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소

· Version : SQL Server 2019

SQL Server 2019에는 응용프로그램 코드에 필요한 변경을 최소화 하면서 성능을 향상시키는 몇 가지 성능 최적화가 도입 되었다. 이번 포스트에서는 SQL Server 2019 성능 개선 사항 중 하나인 temp 테이블을 사용한 작업 부하에 대해 리컴파일 감소로 인한 성능 향상을 설명한다.

· Intelligent query processing in SQL databases : https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-2017

이 개선 사항을 이해하기 위해 먼저 SQL Server 2017 및 그 이전의 동작을 살펴본다. DML 문 (SELECT, INSERT, UPDATE, DELETE)을 사용하여 임시 테이블을 참조할 때 임시 테이블이 외부 범위 배치에 의해 생성된 경우에는 실행 될 때 마다 DML 문을 다시 컴파일 힌다.

아래 스크립트는 실습를 사용하여 재현할 수 있다. OuterProc 프로시저에서는 아래와 같은 기능을 한다.

1. 임시테이블 만들기

2. InnerProc 저장 프로시저 호출

InnerProc 저장 프로시저의 경우 OuterProc에서 생성된 임시 테이블을 참조하는 두 개의 DML 문이 있다.

3. 임시 테이블에 행을 삽입

4. 임시 테이블에서 행을 리턴

우리는 임시 테이블을 DML문과 다른 범위로 만들었으며 기존 구현(SQL Server 2019 이전)에 대해 이 임시 테이블 스키마가 실질적으로 변경되지 않음을 “신뢰”하지 않으므로 실행될 때마다 연관된 DML문이 추가 리컴파일 활동을 하여 CPU 사용률을 높이고 전체 워크로드 성능 및 처리량을 줄여 성능 저하로 이어질 수 있다.

SQL Server 2019 부터는 불필요한 리컴파일을 피하기 위해 추가 검사를 수행한다.

· 컴파일 타임에 임시 테이블을 생성하는데 사용된 외부 범위 모듈이 연속 실행에 사용된 것과 동일한지 확인한다.

· 초기 컴파일시 변경 한 DDL (Data Definition Language) 변경 사항을 추적하고 이를 연속 시행을 위한 DDL작업과 비교한다.

결과적으로 보증하지 않은 리컴파일 및 관련 CPU 오버헤드가 줄어든다.

아래 그림은 각 “OuterProc“ 저장 프로시저를 1,000번 (in a loop) 실행하는 16개의 동시 스레드의 테스트 결과를 보여준다. Y축은 발생횟수를 나타내며 파란색 선은 Batch Requests/sec 를 나타내고 녹색 선은 SQL Re-Compilations/sec를 나타낸다.

이 예제에서 기능이 활성화 되면 다음과 같은 결과가 나타난다.

· Batch Requests/sec (파란색 선, 두 번째 혹)로 표시되는 처리량 개선

· 전체 작업 시간 단축

· SQL Re-Compilations/sec (녹색선)로 표시되는 리컴파일 감소. (두 번째 테스트 시작시 약간의 증가가 발생하였음)

이 기능은 모든 데이터베이스 호환성 수준에서 SQL Server 2019에서 기본적으로 사용된다. 이 글을 쓰는 시점에서 이 기능은 데이터베이스 호환성 수준 150에서 Azure SQL Database에서도 사용할 수 있지만, 향후 다른 호환성 수준에서도 적용될 수 있다.

2019-09-24/ Sungwook Kang / http://sungwookkang.com

Azure SQL, SQL Server 2019, temp table, SQL Re-compilations, Batch Requests, Intelligent query processing in SQL databases

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 복원 성능 최적화 (0)	2020.02.29
SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21
SQL Server 2019 Log Writer Workers (0)	2019.09.19

Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석

2019. 9. 24. 00:35

Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석

· Version : Azure SQL, SQL Server 2016 Later

대기 통계(Wait Statistics)는데이터베이스 엔진에서 무언가를 기다리는 쿼리를 식별하는데 도움이 되며 쿼리 지속시간이 긴 이유를 분석할 수 있는 정보를 나타낸다. 이번 포스트에서는 워크로드가 대기하는 이유와 일부 리소스에서 대기중인 쿼리를 식별하는 방법에 대해서 살펴본다.

Azure SQL Managed Instance를 사용하면 아래 DMV를 사용하여 쿼리가 리소스를 대기하는 이유를 찾을 수 있다.

· sys.dm_os_wait_stats : 인스턴스 레벨에서 대기 정보 반환

· sys.query_store_wait_stats : 데이터베이스 레벨에서 대기중인 쿼리의 실행계획 반환

이러한 정보는 DMO/Query Store를 사용하여 찾을 수 있다. 그러나 분석을 쉽게 하기 위해 무료 오픈소스인 QPI 라이브러리를 사용할 수 있다.

· QPI : https://github.com/JocaPC/qpi

QPI 라이브러리를 설치하려면 아래 링크에서 SQL Server 버전에 대한 SQL 스크립트를 다운로드 할 수 있다. Query Store에 의존하기 때문에 SQL Server 2016 이상, Azure SQL 에서 가능하다.

· QPI Installation : https://github.com/JocaPC/qpi#installation

sys.dm_os_wait_stats 는 인스턴스 시작 이후 또는 통계를 마지막으로 재설정 한 이후에 대기 통계를 수집하므로 대기 통계의 스냅샷을 작성하거나 최소한 재설정을 해야한다. 아래 스크립트를 실행하여 QPI에서대기 통계를 재설정 할 수 있다.

exec qpi.snapshot_wait_stats

이 절차는 인스턴스의 대기 통계를 재설정하고 관리형 인스턴스는 새 대기 통계를 수집을 시작한다. 워크로드가 실행되는 동안 qpi.wait_stats보기에서 대기 통계 정보를 읽을 수 있다. 결과를 보면 인스턴스의 기본 대기 통계는 INSTANCE_LOG_RATE_GOVERNON이며 Log Rate Governor로 분류 된다. 대기 유형의 영향을 받는 쿼리를 찾으려면 실제 대기 유형 이름이 아닌 쿼리 저정소에서 Category를 사용해야 하므로 Category가 중요하다. 쿼리 저장소의 대기 통계는 대기 유형별로 기록되지 않는다.

대기가 발생한 Category의 영향을 받는 상위 쿼리를 보려면 qpi.db_query_wait_stats 보기를 사용하여 Category에 해당하는 대기 통계를 필터링 할 수 있다.

예제의 경우 관리형 인스턴스에서 로그 속도 제한에 도달하고 있으며, 원인은 인덱스 재구축으로 인한것일 수 있다. 데이터베이스가 여러개인 경우 데이터베이스마다 쿼리 저장소가 구성되므로 각 데이터베이스에서 이 쿼리를 실행해야한다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/05/analyzing-wait-statistics-on-managed-instance/

2019-09-23/ Sungwook Kang / http://sungwookkang.com

Azure SQL, SQL Server Managed Instance, Query Store, QPI, sys.dm_os_wait_stats, sys.query_store_wait_stats

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 트랜잭션 로그 복원시 복원 시간이 오래 걸리는 현상 (0)	2020.02.28
SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21
SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Login Timeout 디버깅 (0)	2019.09.19

SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인

2019. 9. 21. 01:05

SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인

· Version : SQL Server 2019

SQL Server에서 통계정보는 옵티마이저가 실행 계획을 생성할 때 참고하는 중요한 지표이다. 통계 자동 업데이트가 true 로 설정된 경우, 데이터의 변경이 특정 임계치 이상되면 자동으로 통계 정보를 업데이트 한다.

· SQL Server Statistics : http://sqlmvp.kr/140165557766

이때 통계 정보를 업데이트하면서 블럭킹이 발생하는데 이전까지는 블럭킹이 발생한것에 대해서 확인할 방법이 없었다. SQL Server 2019 부터는 이러한 문제를 해결하기 위해 새로운 진단 데이터가 도입되었다. 통계 업데이트시 블럭킹을 발생하는 것을 재현하기 위해 아래와 같은 시나리오를 만들었다.

· 자동 통계 업데이트를 트리거하는 SELECT 쿼리를 실행한다.

· 동기 통계 업데이트가 실행을 시작하고 새 통계가 생성될때 까지 쿼리가 대기한다. (기본적으로 차단됨)

· 동기 통계 업데이트 조작이 완료 될 때 까지 쿼리 컴파일 및 실행이 재개되지 않는다.

이 시간 동안 쿼리는 동기화 통계 업데이트 작업이 완료될 때 까지 대기하고 있으며, 이 문제를 확인하기 어려웠다. 대용량 테이블또는 사용량이 많은 시스템등 통계 업데이트에 시간이 오래 걸리는 경우 원인을 쉽게 확인할 수 있는 방법이 없다.

SQL Server 2019에서는 동기화 통계 업데이트로 인해 쿼리가 차단되면 sys.dm_exec_requests에서‘command’컬럼에 (STATMAN)가 표시된다. 그리고 통계 업데이트 작업이 완료되면 초기 명령이름으로 돌아간다.

또한 새로운 WAIT_ON_SYNC_STATISTICS_REFRESH 대기 유형은 동기 통계 업데이트에서 집계된 대기 시간(블럭)을 측정한다. 이 대기시간 누적은 sys.dm_os_wait_stats 동적 관리뷰에서 확인할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/13/diagnostic-data-for-synchronous-statistics-update-blocking/

2019-09-20/ Sungwook Kang / http://sungwookkang.com

SQL Server2019, SQL Statistics, WAIT_ON_SYNC_STATISTICS_REFRESH, sys.dm_os_wait_stats, sys.dm_exec_requests

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2019 temp table을 사용한 워크로드에서 recompile 감소 (0)	2019.09.24
Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24
SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Login Timeout 디버깅 (0)	2019.09.19
SQL Server Worker Thread 기본 계산 (0)	2019.09.18

SQL Server 2019 Log Writer Workers

2019. 9. 19. 23:48

SQL Server 2019 Log Writer Workers

· Version : SQL Server 2014, SQL Server 2016, SQL Server 2017, SQL Server 2019

SQL Server 2017은 숨겨진 스케줄러에서 최대 4개의 Log Writer Worker를 활용하여 트랜잭션 로그 처리 활동을 지원한다.

SQL Server 2019 버전부터는 하드웨어 성능에 따라 최대 Log Writer Worker 수가 최대 8개까지 증가한다.

;with kgroups AS

(SELECT kgroup_count = COUNT(DISTINCT processor_group)

FROM sys.dm_os_nodes osn)

SELECT SQLServer_version = SERVERPROPERTY('ProductVersion'), sinfo.scheduler_count,

sinfo.cpu_count, sinfo.softnuma_configuration_desc, sinfo.socket_count,

sinfo.numa_node_count, kgroups.kgroup_count

FROM sys.dm_os_sys_info sinfo

CROSS JOIN kgroups;

SELECT req.session_id, req.command, sch.scheduler_id, sched_status = sch.[status],

sch.cpu_id, sch.parent_node_id, osn.memory_node_id, osn.processor_group

FROM sys.dm_exec_requests req

JOIN sys.dm_os_schedulers sch ON req.scheduler_id = sch.scheduler_id

JOIN sys.dm_os_nodes osn ON sch.parent_node_id = osn.node_id

WHERE req.command = 'LOG WRITER';

SQL Server는 서비스 시작시 시스템 리소스 상태에 따라 Log Writer Worker수를 결정하며 아래 그림은 SQL Server 2019를 2Core CP, 2GM RAM에서 실행 하였을때, Log Writer Worker 수를 SQL Server Error Log에서 확인한 것이다.

여러 Log Writer Worker가 허용되지 않는 경우 단일 Log Writer Worker 를 사용한다. 그렇지 않으면 아래 공식을 사용하여 Log Writer Worker 수를 계산한다.

· 총 NUMA 노드 X 2

· NUMA 노드에서 사용사능한 CPU 수 계산 (affinity mask 설정에 따라 CPU 카운트에 영향을 줄 수 있음)

MAX_LOG_WRITERS 허용에 따라 4 또는 8개가 할당 된다.

[참고자료]

· https://blogs.msdn.microsoft.com/bobsql/2019/02/11/sql-server-log-writer-workers/

· http://sql-sasquatch.blogspot.com/2019/06/sql-server-2019-ctp-30-max-number-of.html

2019-09-19/ Sungwook Kang / http://sungwookkang.com

SQL Server2017, SQL Server 2019, Log Writer Worker, 로그 쓰기 워커, NUMA, OS NODE

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

Azure SQL Managed Instance 및 SQL Server 2016 Later에서 대기 통계 분석 (0)	2019.09.24
SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21
SQL Server Login Timeout 디버깅 (0)	2019.09.19
SQL Server Worker Thread 기본 계산 (0)	2019.09.18
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) (0)	2019.09.17

SQL Server Login Timeout 디버깅

2019. 9. 19. 01:34

SQL Server Login Timeout 디버깅

· Version : SQL Server, SQL Server Linux

SQL Server 2017 및 SQL Linux에서 로그인시 랜덤하게 연결이 실패하는 경우가있다. 이번 포스트는 SQL Server Login 시 Timeout이 발생하는 원인을 분석하기 위한 디버깅 과정을 소개한다.

[Report Symptoms]

· SQL 인증 및 AD 기반 로그인 모두에서 무작위 연결 실패

· 서버의 원격 클라이언트 또는 sqlcmd 실행시 디렉토리(/opt/mssql-tools/bin)에서 오류가 발생

· 장애 발생시 예측 할 수 없음

· Non-yielding 스케줄러 보고서

· SQL Server 2017 CU10에 동작이 표시되지 않음

· SQL Server 2017 CU12는 동작을 나타냄

· 서버는 많은 CPU와 RAM을 가진Superdome 상태

[Health Session Information]

이 문제를 디버깅하는 방법 중 하나는 /var/opt/mssql/log/system_health*.xel 파일에 있는 SQL Server 상태 세션을 추적하는 것이다. SSMS (SQL Server Management Studio)에서 XEvent 상태 세션을 보고 조정할 수 있다.

- 실시간 데이터 시청

- 마우스 오른쪽 ->세션을 조정

- 마우스 오른쪽 -> 스크립트 세션

상태 세션에서는 여러 이벤트 유형이 포함되며, 일부 이벤트 유형에서는 시스템 상태 세션에서 노이즈를 제거하기 위한 조건자가 포함된다.

sqlclr.clr_allocation_failure

sqlclr.clr_virtual_alloc_failure

sqlos.memory_broker_ring_buffer_recorded

sqlos.memory_node_oom_ring_buffer_recorded

sqlos.process_killed

sqlos.scheduler_monitor_deadlock_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_iocp_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_ring_buffer_recorded

sqlos.scheduler_monitor_non_yielding_rm_ring_buffer_recorded

sqlos.scheduler_monitor_stalled_dispatcher_ring_buffer_recorded

sqlos.scheduler_monitor_system_health_ring_buffer_recorded

sqlos.wait_info

sqlos.wait_info_external

sqlserver.connectivity_ring_buffer_recorded

sqlserver.error_reported

sqlserver.security_error_ring_buffer_recorded

sqlserver.sp_server_diagnostics_component_result

sqlserver.sql_exit_invoked

sqlserver.xml_deadlock_report

Non-yielding, wait_info* 및 연결 이벤트는 이 문제에 필요한 정보를 제공한다. health record를 보면 다음과 같은 패턴이 반복적으로 나타난다.

시스템 상태에 기록된 이벤트는 Non-yielding 시작, 로그인 타임아웃 및 종료 및 Non-yielding 종료를 표시한다.

[connectivity_ring_buffer_record]

connectivity_ring_buffer_record에서는 세부 정보가 표시된다.

연결시도가 실패하면 type=LoginTimers 레코드가 기록되어 로그인 활동에 대한 타이밍을 제공한다. total_login_time_ms = 23309는 실패한 로그인 시도가 23.3초임을 표시한다. SSL 교환 시간은 낮았지만 login_trigger_and_resouce_governor_ms 값은 대부분의 시간을 소비했다. find_login_ms 이벤트는 다른 이용자였으며 login_trigger_and_resouce_governor_ms의 하위이다. 아래 스크립트를 실행하여 링 버퍼 항목 관계를 통해서 LoginTimer에 대해서 어떤 타이밍에 대한 부모 또는 자식 값인지 확인할 수 있다.

Select

from sys.dm_os_ring_buffers

where ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

링 버퍼 xml 형식은 상위 및 하위 관계를 출력한다. LoginTimer 항목은 타이밍 정보를 표시한다. (전체 레코드에서 SSL, SSPI, 읽기, 쓰기 등에 대한 추가 정보가 포함됨)

<RecordType>LoginTimers</RecordType>

</TriggerAndResGovTime>

</LoginTimersInMilliseconds>

</ConnectivityTraceRecord>

이름에서 알 수 있듯이 SQL Server는 로그인 유효성 검사의 일부로 로그인 정보를 검색하고 필요한 경우 AD에 연결한다. 즉, 마스터에서 데이터베이스 페이지를 읽고 잠금을 획득하며 도메인 컨트롤러(DC/KDC)를 호출한다.

[waint_info *]

또 다른 관찰은 시스템 상태 세션이 5초보다 긴 wait_info* 이벤트를 포함하지 않는다는 것이다. 데이터베이스 읽기, 잠금 또는 선제적외부 호출을 5초 이상 기다리는 경우 이벤트가 기록되었기 때문에 waint_info* 이벤트의 부족이 드러난다. 이는 데이터베이스 읽기, 잠금 또는 preemptive, 외부 호출에 문제가 발생하지 않을 것임을 의미한다.

[Network Trace]

네트워크 추적을 살펴보면 문제를 이해하는데 도움이 된다.

[Normal SQL Login, execute select @@VERSION and logout]

다음은 유요한 SQL Server 로그인의 예이다.

1. ODBC CLIENT에서 LINUX SQL Server(SYN)로의 TCP 연결 설정

2. TDS 사전 로그인 교환

3. 512 바이트 응답 (데이터베이스 컨텍스트 XXXX, 기본언어 XXXX, 테스트 크기 XXXX 사용)

4. SQL Batch (@@VERSION 선택)

5. FIN-TCP 연결종료 분리

[Failing Login]

다음은 실패한 SQL Server 로그인의 예이다.

1. ODBC CLIENT에서 LINUX SQL Server(SYN)로 TCP 연결 설정

2. TDS 사전 로그인 교환

3. 로그인 응답

4. 연결 끊김 상태를 유지하면서 다른 활동 없이 시간이 경과하기를 시작

5. RST-클라이언트가 TCP연결을 재설정함(OS오류 : 10054)

몇 가지 실패 변형이 있었지만 로그인 응답을 처리하는 가장 일반적인 대기가 발생.

[Network Layer Overloaded]

실패한 SQL Server 로그인과 달리 네트워크 계층이 오버로드 되어 SQL Server에 대한 요청을 완료하지 않은 경우 네트워크 추적이 나타난다. 일반적으로 SYN의 재전송이 시작된다.

[Attempting A Reproduction]

이제 패턴에 대한 아이디어를 얻었다. 문제를 재현하는 것은 문제를 추적하고 올바르게 해결되도록 하는데 도움이 된다. Ostress툴을 사용해서 재현할 수 있다.

"C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -Usa -Pxxxxxxx -Q"--" -Stcp:10.193.17.114 -q -n64 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output

- Use -U and -P for SQL authentication

- Use -E for AD authentication

- -q ignores query results

- -S target server name forcing tcp protocol

- -Q with a query of -- (comment only to do nothing)

- -n controls the number of threads

- -r controls the number of loops for each thread

- -c the control file

- -T146 disable encrypted TDS

- -o output location

설정 파일에서 DisconnectPct=100.0을 설정한다. 이를 통해 ostress가 연결, 쿼리 실행,연결 끊기에 대해서 각 -n 스레드에 대해서 -r회 반복하도록 지시한다. XEvent에서 로그인 및 로그 아웃 이벤트를 추적하면 연결 및 연결 끊기 활동을 볼 수 있다. 다음은 테스트가 수행하는 로그인 및 로그아웃 비율을 확인하기 위한 간단한 쿼리이다.

drop table #before

drop table #after

select * into #before

from sys.dm_os_performance_counters where counter_name in ('Logins/sec', 'Logouts/sec')

waitfor delay '00:00:10'

select * into #after from sys.dm_os_performance_counters where counter_name in ('Logins/sec', 'Logouts/sec')

select b.object_name, a.cntr_value - b.cntr_value as , (a.cntr_value - b.cntr_value) / 10.0 as [Rate/sec]

from #before b

inner join #after a on a.counter_name = b.counter_name

연결 경로를 연습하는 cmd 파일 스크립트로 시작했다.

start "ostress" "C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -E -Q"--" -Stcp:xxxxxxx -q -n32 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output

start "ostress" "C:\Program Files\Microsoft Corporation\RMLUtils\ostress" -E -Q"--" -Stcp:xxxxxxx -q -n32 -r99999999 -cc:\temp\sample.ini -T146 -oc:\temp\output2

:top

sqlcmd -E -Stcp:xxxxxxx -Q"select @@VERSION"

ping -n 10 xxxxxxxx

goto top

[Windows TCP Settings]

2TB, 144CPU 시스템에서 스트레스 수준을 높이면서 TCP 문제가 발생했다. 몇 초 동안 SQL Server에서 트래픽이 발생하지 않고 갑자기 실행된 후 스트레스가 발생했다. 활동이 중단되는 동안, local /opt/mssql-tools/bin/sqlcmd 연결이 계속 작동했다. Linux SQL Server시스템에서 로컬로 실행되는 아래 bash 스크립트를 사용하여 원격 연결이 실패한 동안 성공적인 연결을 확인했다.

while [ 1 -gt 0 ]

echo

echo $(date)

./sqlcmd -Usa -Pxxxxxxx -Q"select @@VERSION" -Stcp:.

sleep 2

done

Windows 클라이언트 TCP 포트가 부족하여 중단 및 연결 동작이 발생했다.

netsh int ipv4 show dynamicport tcp

netsh int ipv6 show dynamicport tcp

netsh int ipv4 set dynamicport tcp start=10000 num=50000

netsh int ipv6 set dynamicport tcp start=10000 num=50000

이제 네트워크 포화 상태 (재전송이 있는 SYN)를 보기 시작할 때 까지 높은 수준의 연결 및 연결 끊기 작업을 시작했지만 Non-yielding 패턴은 보이지 않았다.

[Disrupt The Domain Controller (Keytab)]

FindLogin시 도메인 컨트롤러 호출을 수행한다는 것을 알면 다음에 도메인 컨트롤러 VM을 일시 중지하여 연결시간 초과로 인해 Non-yielding 발생하여 FindLogin에 많이 기록되도록 하였다. KDC/DC 쿼리 동작을 나태나는 키탭 파일(잘못된 DC 대상)에 항목을 추가할 수 있다. 그런 다음 FindLogin에서 Linux, AD 활동을 위해 SSSD 또는 LDAP를 호출하기 전에 preemptive으로 전환되지 않는 몇 가지 코드 경로를 찾았다. SQL Server 가 preemptive으로 전환하지 않았기 때문에 시스템 상태 로그에 wait_type * 레코드가 없다.

[Non-Yielding Scheduler]

Non-Yielding은 이 문제와 관련된 주요 행동이다. 아래 다이어그램은 발생한 문제를 강조하는데 도움이 된다.

소유한 작업자가 SQL Server 스케줄러를 끄지 않고 도메인 컨트롤러를 호출(preemptive)했다. 사전 로그인 시퀀스를 통해 SQL 인증 및 AD 인증 작업자가 일부 생성되어 있다. 소유 작업자가 yield일때 실행 가능한 목록은 다음에 실행할 작업자를 결정하는데 사용된다. 실행 가능 목록이 스케줄러의 최대 작업자 수에 도달하면 새 요청이 큐에 대기된다.

KDC/DC 쿼리가 5초이상 걸리고 Scheduler Monitor(NUMA노드당 하나)가 non-yielding condition (BEGIN)을 보고한다. SchedulerMonitor는 5초마다 진행률을 확인하여 통과 횟수를 증가 시킨다. 통과 횟수가 한계(12회, 60초)에 도달하면 non-yielding 상황이 보고된다.

테스트의 경우 non-yielding은 일반적으로 5~15초 동안 지속되었으며 시스템 상태 세션에서 non-yielding BEGIN 및 END 이벤트가 브라켓 연결시간 종료 이벤트와 함께 보고되었다. 그런 다음 다양한 로그인 시간 초과가 트리거 된다. 클라이언트가 연결을 감지하면 시간 초과에 도달하면 TCP 연결을 닫기 위해 FIN이 전송된다. 일반적으로 오류 10054로 SQL Server에 버블링-연결이 닫힌다.

· 로그인이 사전 로그인을 처리하는 중이라면 링 버퍼 항목에 SSL 읽기 등의 시간과 연결 끊김이 표시될 수 있다.

· 로그인이 DC 호출에 멈춘 경우 일반적으로 찾기 고르인 시간이 누적된다.

· 로그인이 스케줄러에 대기된 경우 로그인 실패 이벤트에 대해 1 또는 0ms 경과 시간이 표시된다. 작업자가 새 작업을 받을수 있을 때까지 클라이언트는 이미 10054를 보냈으며 SQL Server가 로그인을 처리하려고 하면 연결이 닫히고 실패한것으로 감지된다.

[SQL Authentication Impact]

이제 AD 인증이 어떻게 SQL 인증, 로그인 시간 초과로 이어질 수 있는지 알 수 있다. SQL 인증 요청이 지연된 KDC/DC 쿼리와 동일한 스케줄러에 있는 경우 리퀘스트에 영향을 준다.

[Other Scheduler Impacted]

이 코드 경로는 많은 SQL 잠금에 관여하지 않으므로 KDC/DC 쿼리를 실행하는 정지된 사용자는 일반적으로 다른 스케줄러 및 해당 스케줄러와 관련된 로그인에 영향을 미치지 않는다. 다른 스케줄러는 두 가지 방식으로 KDC/DC쿼리의 영향을 받는다.

1. 다른 스케줄러가 AD로그인을 처리하려고 시도하고 KDC/DC 쿼리가 느리면 쿼리에 동일한 동작이 발생할 수 있다.

2. non-yielding 감지되면 덤프가 호출된다. 덤프는 SQL Server 프로세스를 일시 중단한다. 덤프 시간이 로그인 시간 초과를 초과하면 클라이언트가 연결을 닫고 일반적으로 SQL Server 스케줄러에서 10054 TCP 오류가 발생한다.

[Recap]

버그는 도메인 컨트롤러를 쿼리하기 전에 FindLogin 코드가 preemptive되지 않아 지연 및 로그인 시간 초과를 초래한다. 수정된 경우 KDC/DC 쿼리에서 지연이 발생한 경우 개별 AD 로그인 시도에 여전히 로그인 시간 초과가 발생하지만 다른 로그인 및 쿼리는 더 이상 영향을 받지 않는다.

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2019/02/10/sql-mysteries-sql-server-login-timeouts-a-debugging-story/

2019-09-18/ Sungwook Kang / http://sungwookkang.com

SQL Server2017, XEvent, Login TimeOut, SQL Authentication Impact, FindLogin, Non-Yielding Scheduler, SQL Login, SQL TimeOut, SQL Logout

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2019에서 동기 통계 업데이트시 발생하는 쿼리 Blocking 확인 (0)	2019.09.21
SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Worker Thread 기본 계산 (0)	2019.09.18
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) (0)	2019.09.17
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 (0)	2019.09.14

SQL Server Worker Thread 기본 계산

2019. 9. 18. 01:01

SQL Server Worker Thread 기본 계산

· Version : SQL Server

SQL Server 2017부터는 소규모 환경을 고려하여 SQL Server의 기본 worker thread 수가 약간 변경되었다. 소규모 환경에서 SQL Server를 실행하는 경우 SQL Server는 worker thread를 줄인다. X64 설치의 경우 sp_configure ‘max worker threads’ 값을 0으로 설정하면 SQL Server는 아래 계산 공식을 사용한다.

Default	512
Small Environment	256

소규모 환경에서는 SQL Server는 항상 worker thread 256을 사용한다. 더 큰 환경에서 실행하면 worker thread는 512가 기본값이며 CPU 수에 따라 조정된다.

CPU가 4개보다 많으면 worker thread 수는512 까지 증가하며, CPU 수에 따라32또는 16씩 증가하여 최대 512까지 증가한다. 만약 시스템에 64개 이상의 CPU가 있으면 추가 worker thread는 CPU당 32 worker thread가 증가한다.

이러한 설계는 CPU 및 메모리 리소스가 적은 소규모 환경 및 더 큰 CPU 및 메모리를 사용하는 시스템을 고려한다. worker thread는 보이는 스케줄러에만 적용된다. 숨겨진 스케줄러 및 DAC (Dedicated Admin Connection) 스케줄러는 계산에 영향을 받지 않는다.

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2019/02/10/sql-server-worker-thread-default-calculation/

2019-09-17/ Sungwook Kang / http://sungwookkang.com

SQL Server2017, worker thread

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server 2019 Log Writer Workers (0)	2019.09.19
SQL Server Login Timeout 디버깅 (0)	2019.09.19
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) (0)	2019.09.17
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 (0)	2019.09.14
BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 (0)	2019.09.13

SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?)

2019. 9. 17. 00:16

SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?)

· Version : SQL Server Linux

PostgreSQL에서 fsync() 오류처리는 안전하지 않으며 XFS에서 데이터 손실이 발생할 수 있다는 내용이 있다.

· PostgreSQL's handling of fsync() errors is unsafe and risks data loss at least on XFS : https://www.postgresql.org/message-id/flat/CAMsr%2BYE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG%2BvzHun1EqMxyQg%40mail.gmail.com#CAMsr+YE5Gs9iPqw2mQ6OHt1aC5Qk5EuBFCyG+vzHun1EqMxyQg@mail.gmail.com

이번 포스트는 SQL Server에서도 Linux 동일한 문제가 발생하는지 유효성을 검증하는 내용으로, SQL Server의 경우 O_DIRECT를 사용하기 때문에 PostgreSQL과 같은 문제가 발생하지 않는다.

· Direct I/O : https://access.redhat.com/documentation/en-us/red_hat_enterprise_linux/5/html/global_file_system/s1-manage-direct-io

응용 프로그램이 버퍼된 쓰기를 수행하고 성공을 수신한다. (이는 안정된 미디어 파일 시스템 캐시에 데이터를 저장할 수 있음을 의미한다.) fsync/fdatasync는 데이터가 안정적인 미디어에 저장되도록 하는데 사용된다. 안정적인 미디어 쓰기는 동기화 작업중에 발생하며 EIO 오류를 보고하는 여러가지 이유로 (디스크 공간부족, SAN 연결 끊김 등) 실패할 수 있다.

· fsync/fdatasync : http://man7.org/linux/man-pages/man2/fdatasync.2.html

위의 PostgreSQL링크에 설명된 문제는 동기화에서 오류를 반환하지만 캐시된 페이지의 상태를 지울수 있다는 것이다. 다음 동기화는 캐시된 쓰기가 안정적인 미디어로 플러시 되지 않지만 응용 프로그램에 알려졌다는 것을 의미하는 ESUCCESS를 반환한다.

데이터베이스 응용프로그램이 백업 파일을 열어 파일 시스템 캐싱 (~_O_DIRECT)을 허용한다고 가장헌다. SQL Server는 이 작업을 수행하지 않으며 실제로 Linux의 SQL Server에서 이 작업을 수행할 수 없도록 했다.

1. 버퍼링된 I/O를 사용하여 백업이 시작된다.

2. 백업이 진행되면서 파일 시스템 캐시에 쓰기가 발생한다.

3. 파일 시스템 캐시 쓰기가 발생하는 동안 동기화에 대한 외부 호출이 발생한다.

4. 백업에 속한 버퍼에서 동기화쓰기 오류가 발생한다. 데이터베이스 응용프로그램 외부의 응용 프로그램에서 동기화를 호출하여 데이터베이스 응용프로그램이 실패를 인식하지 못한다.

5. 백업이 끝나면 데이터베이스 응용 프로그램에서 fdatasync를 실행하고 EIO를 반호나하는 대신 ESUCCESS가 반환된다.

6. fdatasync가 성공적으로 완료되면 미디어 백업이 안정적으로 강화되고 데이터베이스 응용 프로그램이 트랜잭션 로그의 비활성 부분을 자른다.

7. 데이터베이스에 더 이상 트랜잭션 조작 또는 복구를 수행 할 적절한 로그 레코드가 없고, 일단 유효하다고 생각된 백업파일이 유효하지 않는다.

이 문제는 SQL Server 데이터베이스, 로그 및 백업파일에 영향을 미치지 않는다. SQL Server는 파일 시스템 캐시를 무시하기 위해 O_DIRECT를 사용하여 이러한 파일 형식을 연다. Linux의 SQL Server가 강제 플러시 모드에서 실행 중인 경우에도 파일은 O_Direct로 열리므로 문제가 발생하지 않는다.

[참고자료]

· SQL Server Linux: fsync and Buffered I/O : https://blogs.msdn.microsoft.com/bobsql/2018/12/18/sql-server-linux-fsync-and-buffered-i-o/

2019-09-16 / Sungwook Kang / http://sungwookkang.com

SQL Server, SQL Linux, File System, Windows, Linux, fsync, fdatasync, O_Direct

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Login Timeout 디버깅 (0)	2019.09.19
SQL Server Worker Thread 기본 계산 (0)	2019.09.18
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 (0)	2019.09.14
BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 (0)	2019.09.13
삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) (1)	2019.09.12

SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점

2019. 9. 14. 01:42

SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점

· Version : SQL Server, SQL Server Linux

SQL Server는 로그 파일 또는 데이터 파일이 증가하거나 새로 작성될때, 인스턴트 파일 초기화 작업을 진행한다. 이번 포스트에서는 인스턴스 파일이 초기화 될때, 기본 파일 시스템 구현과 Windows 와 Linux 간의 동작 차이를 알아본다.

· Database File Initialization : https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-2017

SQL Server는 데이터 및 로그 파일을 만들거나 확장(증가)할때 아래 API를 호출 한다.

· CreateFile : 파일 작성 또는 열기

· SetEndOfFile : 파일 크기를 설정하고 I/O 장치에서 공간을 확보

· SetFileValidData : 유효한 데이터 크기 설정

파일이 로그 파일(LDF)인 경우 SQL Server는 알려진 패턴값을 할당된 공간에 쓴다. 데이터 파일 (MDF, NDF)인 경우 SQL Server는 인스턴스 파일 초기화 및 추적 플래그 1805 설정을 확인하여 할당된 공간에 패턴값을 쓸지 여부를 결정한다.

TF 1805 : 데이터 파일에 대한 인스턴스 파일 초기화를 비활성화 한다.

참고 : 스탬핑은 일반적으로 최적의 성능과 Windows 및 Linux 파일 시스템 페이지 및 블록크기 정렬과 정렬을 위해 4MB 청크로 수행

[Windows]

Windows 파일 시스템(NTFS, RTFS)에는 파일을 즉시 초기화 하기 위한 두 개의 멤버키가 있다.

· EOF : 파일 끝 위치

· VDL : 유효한 데이터 길이 위치

Empty File

파일이 처음 작성될 때 EOF와 VDL은 모두 파일의 시작을 가리킨다.

· EOF = 0

· VLD = 0

SetEndOfFile

SetEndOfFile은 파일을 확장하여 I/O 장치에서 공간을 확보하고 EOF 값을 조정한다. VLD 값은 변경되지 않은 상태로 유지된다.

· EOF = 10G

· VLD = 0

SetFileValidData

SetFileValidData는 VDL을 이동하는데 사용된다. VDL이 기록된 것으로 간주되는 경우(쓰기가 수행되지 않은 경우에도) VDL 오프셋 이전의 모든 데이터와 VDL 이전의 공간 읽기는 I/O 장치에서 오래된 데이터를 반환할 수 있다. VDL 이후의 데이터는 유효하지 않은 것으로 간주되며 읽기 요청에 대해 0이 리턴된다.

· EOF = 10GB

· VDL = 1GB

참고 : 보안 고려사항과 관련된 내용은 위의Database File Initialization 문서를 참고한다.

Write beyond the current VDL (WriteFile*)

VDL 오프셋 이상으로 쓰기가 발생하면 Windows는VDL을 이동하여 쓰기를 수용하고 이전 VDL과 쓰기 요청 시작 사이의 오프셋에 0을 쓴다.

· EOF = 10GB

· 이전 VDL =1 GB

· VDL = 5GB

Instant File Initialization (New File)

VDL 증분 변경 대신 SQL Server는 SetEndOfFile의 빠른 할당 기능을 사용하고 동일한 오프셋으로 SetFileValidData를 호출한다. VDL이전의 모든 데이터는 Windows 파일 시스템에 의해 쓰여진(유효한) 것으로 간주된다. 인스턴트 파일 초기화가 활성화 된 경우 Windows는 0을 쓰지 않으며 SQL Server가 데이터 파일의 패턴을 스탬프 처리 하지 않는다. 내부 SQL Server 데이터베이스 할당 구조는 SQL Server 데이터 파일 할당 및 유효한 데이터 읽기 활동을 추적한다.

Instant File Initialization (Grow)

인스턴스 파일 초기화를 사용하여 파일을 확장하면 새 오프셋으로 SetEndOfFile 및 SetFileValidData가 수행된다. Windows 는 새 오프셋과 이전 오프셋 사이의 데이터를 유요한 것으로 취급한다.

[Linux]

Fallocate(http://man7.org/linux/man-pages/man2/fallocate.2.html) 시스템 호출(ABI)을 사용한 Linux 지원 파일 할당 Windows API호출은 아래와 같이 Linux ABI 호출에 매핑 된다.

· CreateFile : Linux를 사용

· SetEndOfFile : Linux fallocate 사용

· SetFileValidData : Linux의 Noop

Windows와 Linux 파일 시스템의 주요 차이점은 유효한 데이터 길이( VDL)이 아닌 범위를 추적한다. Linux에서 각 범위에는 I/O 장치에 쓰여 졌는지 여부를 나타내는 플래그가 포함된다.

Empty File

파일이 처음 작성될 때 EOF= 0이고 포함 범위는 기록되지 않도록 (N)으로 설정된다. 쓰지 않은 범위의 읽기는 Linux에서 항상 0을 반환한다. Linux는I/O 장치를 사용하지 않지만 단순히 쓰지 않는 범위로 추적된 공간에 대해 리턴 버퍼를 0으로 채운다.

힌트 : 익스텐트 크기 및 조정에 대해서는 Linux 파일 시스템 설명서를 확인한다. 기본 크기는 일반적으로 최적의 성능을 위해 SQL Server 페이지는8K 및 64K 범위 경계에 잘 맞는 메모리 페이지 크기 경계(주로4K)와 정렬된다.

SetEndOfFile

파일 크기 증가는 대체 호출로 발생한다. Linux는 I/O 장치에서 공간을 확보하고 EOF와 추적 범위 메타 데이터를 설정하여 기록되지 않음을 나타낸다. Fallocate는 SetEndOfFile이 Windows 파일 시스템의 공간을 확보하는 것처럼 공간을 확보하여 대용량 파일을 빠르게 생성할 수 있다. 차이점은 SetFileValidData이다. Linux는 실제 쓰기 없이 범위 추적을 ‘쓰기’로 설정하는 기능을 제공하지 않는다.

성능 고려 사항 : 대상 파일 시스템에 대해 fallocate가 지원되지 않으면 SQL Server는 ftruncate를 사용한다. 이름과 달리 ftruncate ABI는 파일을 늘리는데 사용될 수 있지만 씬 프로비저닝된 조장이다.(공간은 메타데이터만 업데이트 되지 않는다.) ftruncate가 필요한 경우 실제 공간을 확보하고 제공하기 위해 SQLPAL이 파일에 0을 쓴다. SQLPAL 프로세스에 대한 오류가 없고 읽기 동작이 없다.

Write

첫 번째 쓰기가 수행되면 범위에 대한 메타 데이터도 업데이트 된다. 쓰기 및 쓰기 되지 않은 데이터를 추적하기 위해 익스텐트를 분할하거나Linux 커널에 의해 확장된 쓰기는 디스크의 공간에 0을 쓰므로 전체 익스텐트가 쓰기 된것으로 표시될 수 있다.

참고 : 대부분의 Linux 파일 시스템에서는 두 번의 쓰기 요청이 발생하지만 쓰기 크기 및 오프셋 정렬에 따라 더 커질수 있다. (데이터 파일 요청1개와 메타데이터 변경 요청 1개)

Windows 에서 SetFileValidData는 단일 메타 데이터 작업이다. VDL이 설정되면 쓰기(순차 또는 임의)는 VDL == EOF로 추가 메타 데이터 업데이트가 필요하지 않다. Linux에서 쓰기에는 데이터 쓰기 및 메타 데이터 쓰기가 필요한 익스텐트 업데이트가 필요하다. Linux 또는 Windows에서 가능한 한 빨리 파일을 쓰고 확장할 수 있다. 그러나 Linux에서 처음 쓰기를 수행하면 메타 데이터가 유지관리 된다.

· 데이터베이스에서 쓰기 속도가 중요한 경우 익스텐트 파일 초기화를 사용하고 첫 번째 쓰기에 추가 오버헤드가 발생하도록 한다.

참고 : 대부분의 쓰기 작업은Checkpoint 또는 Lazy Write와 같은 백그라운드 프로세스로 수행되므로 SQL Server에서 오버헤드를 숨기는 경우가 많다. 활성 SQL Server 세션에서 쓰기가 발생할 수 있으므로 대량 로드는 예외이다.

· 쓰기 속도를 늘릴수 있는 경우 -T1805를 사용하면 데이터베이스가 쓰기 및 확장 중에 데이터파일 공간을 스탬핑 되도록 할 수 있다. 스탬핑은 큰 청크로 최적화되어 있으며 첫 번째 쓰기 데이터 및 메타 데이터 작업이 발생하는 쓰기 경로가 된다. 위치가 기록(스탬프) 되면 더 이상 추가 메타 데이터 쓰기가 필요하지 않다.

참고 : 파일 시스템이 fallocate를 지원하지 않으면 SQLPAL에 의해 파일에 0이 기록된다. 로그 파일(LDF)은 알려진 패턴(0으로 작성)을 표시하며 SQLPAL 공간을 0으로 채울때 메타데이터가 이미 업데이트 되었으므로 데이터 파일에 대한 인스턴스 파일 초기화를 안전하게 유지할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/12/10/sql-server-instant-file-initialization-setfilevaliddata-windows-vs-fallocate-linux/

2019-09-13 / Sungwook Kang / http://sungwookkang.com

SQL Server, SQL Linux, File System, Windows, Linux, SetFileValidData (Windows) vs fallocate (Linux), T1805, SetEndOfFile, SetFileValidData, VDL

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server Worker Thread 기본 계산 (0)	2019.09.18
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) (0)	2019.09.17
BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 (0)	2019.09.13
삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) (1)	2019.09.12
QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 (0)	2019.09.11

BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유

2019. 9. 13. 00:28

BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유

· Version : SQL Server

SQL Server에서 BCP 명령을 사용하여 대량의 데이터를 로드할때, sys.dm_exec_requests 항목을 모니터링 해보면 command 항목에 BCP 대신 BULK INSERT 라고 표시되어 있다. 아래 표를 보면 동일한 세션에 BULK INSERT라고 여러개의 작업이 표시된 것을 확인할 수 있다.

cpu_time	total_elapsed_time	writes	session_id	start_time	command
1387	1396	32	51	2018-08-07 00:45:42.670	BULK INSERT
1930	1941	66	51	2018-08-07 00:46:02.087	BULK INSERT
632	638	32	51	2018-08-07 00:46:23.313	BULK INSERT

단일 BULK INSERT 대신 동일한 세션에 BULK INSERT 배치가 여러번 발생하는 이유는 무엇일까? BCP 명령을 사용할때, 옵션 -b (Batch Size) 를 지정하면서 배치크기에 도달할때까지 행이 스트링되고 커밋이 발생한다. 그리고 후속 행에 대해서 새로운 배치가 실행된다. 따라서 BCP가 아닌 일련의 BULK INSERT 작업이 표시되는 것이다.

[참고자료]

· https://blogs.msdn.microsoft.com/bobsql/2018/08/07/sql-mysteries-tracing-bcp-might-fool-you/

· https://docs.microsoft.com/en-us/sql/tools/bcp-utility?view=sql-server-2017

2019-09-12 / Sungwook Kang / http://sungwookkang.com

SQL Server BCP, Bulk Load, BULK INSERT

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) (0)	2019.09.17
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 (0)	2019.09.14
삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) (1)	2019.09.12
QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 (0)	2019.09.11
ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 (0)	2019.09.10

삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항)

2019. 9. 12. 00:33

삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항)

· Version : SQL Server

도메인 [DOMAIN\group]에 멤버 [DOMAIN\user]가 있고 [DOMAIN\group]에 SQL Server 인스턴스에 대한 로그인 권한이 있는 그룹이 있다고 가정한다. “Drop Login [DOMAIN\group]”을 실행하면 [DOMAIN\user] 사용자는 더 이상 SQL Server에 액세스 할 수 없을 것으로 예상했다. 그러나 아래와 같은 결과가 나왔다.

· [DOMAIN\user]의 새로운 연결은 허용되지 않음

· 기존 연결된 사용자는 계속 작동함 (기존의 [DOMAIN\user] 연결에는 그룹 구성원 및 권한이 캐시되므로 해당 값을 새로 고칠때 까지 연결을 통해 쿼리를 계속 실행할 수 있다.)

“DROP LOGIN [DOMAIN\user]”가 실행될 때 [DOMAIN\user]가 로그인으로 추가되어있고 SQL Server 에 연결이 되어 있는 경우 다음과 같은 오류 메시지와 함께 명령이 실패 한다.

DROP LOGIN” command fails with error “Could not drop login 'DOMAIN\user' as the user is currently logged in.

[DOMAIN\user]의 연결이 해제되면 “DROP LOGIN [DOMAIN\user]”가 성공하고 사용자는 더 이상 SQL Server에 액세스할 수 없다.

[DOMAINO\group]이 로그인으로 추가되고 “DROP LOGIN [DOMAINO\group]” 명령을 실행하면 [DOMAINO\user]가 로그인 된 경우에도 “DROP LOGIN”명령이 성공한다. 그러나 [DOMAIN\ user]가 여전히 로그인되어 있는지 확인하기 위해해서는 아래 스크립트를 실행하여 확인할 수 있다.

select login_name from sys.dm_exec_sessions where login_name = 'DOMAIN\user'

"DROP LOGIN <loginName>"을 실행하면 SQL Server는 <loginName> 계정에 대한 활성 연결이 있는지 확인한다. 이 로그인에 대해 활성화 된 연결이 없으면 로그인이 제거되고 명령이 성공한다. 그러나 <loginName>이 AD 그룹 인 경우 SQL Server는 그룹의 구성원 중 활성 로그인이 있는지 확인하지 않는다. [DOMAIN\ group]을 삭제하면 [DOMAIN\user]가 로그인되어 있고 [DOMAIN\ group]의 구성원 인 경우에도 [DOMAIN\ group]에 대한 활성 연결이 없기 때문에 명령이 성공한다. 실제로 [DOMAIN\ user]는 권한있는 작업 (예 : 로그인 생성)을 수행하거나 연결을 재설정 (예 : sp_reset_connection 로그 아웃 및 다시 로그인) 할 때까지 연결 상태를 유지한다. 이때 SQL Server는 [DOMAIN\ user]의 권한을 확인하고 더 이상 유효한 로그인이 없는지 확인하고 연결을 끊는다. 이러한 연결을 강제로 재설정 할 수 있다. 그룹 로그인을 삭제 한 후 아래 스크립트를 실행한다.

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = 'DOMAIN\user'

스크립트를 실행하면 [DOMAIN\ user] 가 연결의 모든 세션 ID를 보여준다. 이제 “KILL <session_id>”를 실행하여 강제로 사용자 연결을 종료한다. [DOMAIN\user]의 세션은 강제 종료된 상태이기 때문에 쿼리를 실행할 때 더 이상 유효한 로그인이 없는 상태여서 다시 연결을 해야한다. 이때 그룹이 삭제되어 있기 때문에 연결할 수 없게 된다.

연결 자체는 sp_reset_connection을 호출하여 스레드가 로그 아웃했다가 SQL Server에 다시 로그인하도록하여 권한 업데이트를 강제 할 수도 있다. 연결이 영구적이며 지속적으로 로그 아웃했다가 다시 로그인하여 권한 검사를 수행하지 않기 때문에 연결 풀링에 유용하다. 풀링 된 연결을 검색 할 때 sp_reset_connection을 호출하면 로그인 권한이 취소 된 경우 연결이 실패된다.

그룹에 대한 작업 권한이 취소되면 구성원 사용자의 권한이 즉시 취소된다. 예를 들어 [DOMAIN\group]에 "foo"테이블에 대한 선택 권한이 있고 "REVOKE SELECT ON foo TO [DOMAIN\ group]"을 실행 한 경우 [DOMAIN\user]는 즉시 "foo"에 대한 선택 권한을 잃게된다. 아래 스크립트를 사용하여 실습 가능하다.

# Login as an admin user to SQL Server and add [DOMAIN\group] as a login

CREATE LOGIN [DOMAIN\group] FROM WINDOWS

# In another terminal, login to SQL Server as [DOMAIN\user] and run a query to show you have access

SELECT @@VERSION

# Switch back to your admin terminal and drop the group login. It will succeed even though [DOMAIN\user] is connected.

DROP LOGIN [DOMAIN\group]

# Switch back to your [DOMAIN\user] window and check you still have access

SELECT @@VERSION

# Switch to the admin terminal and kill the connection

SELECT session_id FROM sys.dm_exec_sessions WHERE login_name = ‘DOMAIN\user’

KILL <session_id from query above>

# Switch to your [DOMAIN\user] window to see they no longer have access. This command should not return the version information.

SELECT @@VERSION

[참고자료]

https://blogs.msdn.microsoft.com/bobsql/2018/08/03/sql-server-mysteries-the-case-of-the-dropped-ad-group-login/

2019-09-11 / Sungwook Kang / http://sungwookkang.com

SQL Server Login, SQL Server Account, Active Directory, AD, DROP LOGIN, CREATE LOGIN

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 (0)	2019.09.14
BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 (0)	2019.09.13
QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 (0)	2019.09.11
ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 (0)	2019.09.10
Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 (0)	2019.09.07

QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인

2019. 9. 11. 00:27

QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인

· Version : Azure SQL

Azure SQL Managed Instance는 Azure 클라우드에서 호스팅되는 완전히 관리되는 SQL Server 인스턴스이다. 관리형 인스턴스에는 최대 로그쓰기 처리량 성능에 일부 제한이 있어 워크로드 속도가 느려질 수 있다. 이번 포스트에서는 관리형 인스턴스에서 쓰기 로그 처리량 문제를 식별하는 방법에 대해서 살펴본다.

Azure SQL Managed Instance에는 최대 로그 쓰기 속도와 같은 몇 가지 기본 제공 리소스 제한이 있다. 로그 쓰기 제한을 도입한 이유는 로그 백업이 들어오는 데이터를 포착할 수 있어야 하기 때문이다.

· Overview Azure SQL Database managed instance resource limits : https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-resource-limits#service-tier-characteristics

이번 포스트에서는 QPI 라이브러리를 사용하여 관리형 인스턴의 대기 통계를 쉽게 분석하고 있다. QPI 라이브러리를 설치하려면 아래 링크로 이동하여 SQL Server 버전에 대한 SQL 스크립트를 다운로드 한다. QPI 라이브러리는 Query Store 보기에 의존하기 때문에 SQL Server 2016이상, Azure SQL 에서 사용이 가능하다.

· Download QPI(Query Performance Insights) : https://github.com/JocaPC/qpi#installation

Disclaimer: QPI library is open source library provided as-is and not maintained by Microsoft. There are not guarantees that the results are correct and that there are not bugs in calculations. This is a helper library that can help you to more easily analyze performance of your Managed Instance, but you can do the same job by looking directly at DMVs.

이 라이브러리를 사용하면 대기 통계의 스냅샷을 쉽게 작성하고 잠시 기다렸다가 대기 통계 값을 읽을 수 있다.

exec qpi.snapshot_wait_stats;

waitfor delay '00:00:03';

select *

from qpi.wait_stats

order by wait_time_s desc;

위 결과를 살펴보면, 관리 인스턴스의 태스크가 INSTANCE_LOG_RATE_GOVERNOR 대기 유형을 기다리고 있음을 알 수 있다. 위 스크립트를 주기적으로 실행하여 이러한 문제쿼리를 식별할 수 있다. 또한 qpi에서 제공하는 다양한 명령어를 사용하여 관리형 인스턴스의 IO 성능을 분석하여 병목 현상을 식별할 수 있다.

· qpi.snapshot_file_stats 프로시저는 sys.dm_io_virtual_file_stats DMV 함수에서 I/O 통계 스냅샷을 작성한다. sys.dm_io_virtual_stats에는 누적 정보가 포함되어 있으며 최근 시간 간격으로 샘플을 계산해야 하므로 스냅샷을 작성해야 한다.

· qpi.file_stats보기는 마지막 스냅샷 이후 파일 통계를 가져온다. 이 qpi는 sys.dm_io_virtual_file_stats의 데이터를 기반으로 하는 IOPS 처리량등 여러 계산이 포함된다.

아래 스크립트는 파일 유형(LOG/DATA)별로 분류된 인스턴스의 쓰기 처리량(MB/s)및 IOPS를 요약한다.

exec qpi.snapshot_file_stats;

waitfor delay '00:00:01';

select

type,

write_mbps = sum(write_mbps),

iops = sum(iops)

from qpi.file_stats

group by rollup (type);

결과를 살펴보면 현재 인스턴스에서는 47.5MB/s 의 로그파일 쓰기와 총 1226 IOPS를 사용하고 있음을 알 수 있다. Azure 설명서에서 리소스 제한에 대한 설명을 보면 ~48MB/s가 현재 사용되는 인스턴스의 리밋이며, 이러한 리밋으로 인해서 INSTNACE_LOG_RATE_GOVERNOR 대기 통계가 발생하는 것이다. 인스턴스의 최대 사용량 리밋으로 인해서 성능 저하가 발생하지 않도록QPI 스크립트를 주기적으로 실행하여, 인스턴스의 리밋과 QPI결과값을 비교하여,모니터링 할 수 있도록 한다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/identify-log-write-limits-on-azure-sql-managed-instance-using-qpi-library/

2019-09-10 / Sungwook Kang / http://sungwookkang.com

Azure SQL, QPI, Query Performance Insights, SQL Server 2016, Query Store

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

BCP 실행시 동일 세션에서 여러개의 BULK INSERT 문으로 표시되는 이유 (0)	2019.09.13
삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) (1)	2019.09.12
ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행 (0)	2019.09.10
Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 (0)	2019.09.07
SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 (0)	2019.09.04

ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행

2019. 9. 10. 00:46

ODBC 드라이버를 사용하여 SQL Server에서 Azure CosmosDB 쿼리 실행

· Version : SQL Server, Azure CosmosDB

Azure CosmosDB는 기존의 SQL Server와 같은 클래식 데이터베이스에서 CosmosDB 컬렉션을 조회할 수 있는 ODBC 드라이버를 제공한다. 이번 포스트에서는 Transaction-SQL을 사용하여 SQL Server에서 CosmosDB 컬렉션을 쿼리하는 방법을 설명한다.

SQL Server에서 CosmosDB를 쿼리하는 이유는 무엇일까? CosmosDB는 문서 및 기타 비관계형 데이터 유형을 저장 및 검색하고, 필터링하고 정렬할 수 있는 SQL / API를 제공한다. 그러나 경우에 따라 GROUP BY, HAVING, 분석 기능이 있는 더 복잡한 쿼리를 실행하거나 CosmosDB의 비관계형 데이터를 SQL Server 테이블에 저장하는 데이터와 조인해야 하는 경우가 있다. 이 경우 CosmosDB의 데이터를 쿼리하기 위해 Transaction-SQL의 모든 기능을 활용할 수 있다.

[CosmosDB 설정]

먼저 CosmosDB계정을 생성하고 컬렉션에 실습할 데이터를 추가 한다. 데이터베이스 WWI 및 Orders 라는 콜렉션을 생성하고, 세 개의 문서를 추가한다.

실습 데이터 등록이 완료 되었으면, SQL API를 사용하여 컬렉션에 액세스한다.

[드라이버 설정]

SQL Server가 설치된 컴퓨터에 CosmosDB용 ODBC 드라이버를 설치해야한다. 이 드라이버를 설치하면 DSN에서 ODBC 소스를 설정하고 연결을 테스트해야한다.

· ODBC 드라이버 다운로드 : https://aka.ms/cosmos-odbc-64x64

[CosmosDB 쿼리]

모든 설정 후에 기존 SQL Server에서 OPENROWSET 함수를 사용하여 CosmosDB DSN을 지정하여 CosmosDB 데이터를 쿼리 할 수 있다.

실습 데이터의 결과를 보면 CosmosDB에서 3개의 문서를 나타내는 3개의 행을 얻게 된다. 누락된 필드는 NULL로 반한된다. 또한 아래 스크립트처럼 필터를 사용할 수도 있다.

SELECT a.*

FROM OPENROWSET('MSDASQL',

'DSN=cosmosdb1',

'select * from Orders where billto_Name = ''John Smith''') as a

실습데이터의 JSON 형식을 보면 shipTo 또는 billTo와 같이 복잡한 JSON 객체가 모두 동일한 ROW로 표시되고 있는 것을 알 수 있다. 즉, 모든 필드가 <object name>_ <field name> 형식으로 반환된다는 것이다. 하위 오프젝트가 있는 것으로 예상되는 경우 사용자는 이를 알고 있어야 한다. 또한 필자의 경우 태그와 같은 배열 속성이 매핑되거나 반환되지 않는다.

[결론]

CosmosDB용 ODBC 드라이버를 사용하면 CosmosDB 데이터에 대해 Transact-SQL 쿼리를 실행할 수 있다. 이는 CosmosDB에 저장된 원격 데이터에 대해서 데이터 분석이 필요한 경우 유용햘 수 있다. 이 경우 결과를 필터링 하고 필요한 필드만 선택한 다음 전체 Transact-SQL 언어를 사용하여 SQL Server에서 다양한 분석을 수행하는 조건자를 사용하여 CosmosDB에 보낼 수 있다.

(이 글을 쓰는 시점에는) CosmosDB드라이버는 Azure SQL에 설치되어 있지 않으며 자체 드라이버를 추가할 수 없기 때문에 이는 Azure SQL 데이터베이스가 아닌 SQL Server에서만 가능하다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2019/03/11/running-azure-cosmosdb-queries-from-sql-server-using-odbc-driver/

2019-09-09 / Sungwook Kang / http://sungwookkang.com

Azure CosmosDB, SQL Server에서 Azure CosmosDB 연결하기, CosmosDB ODBC

저작자표시 비영리 변경금지

'SQL Server > SQL Server Tip' 카테고리의 다른 글

삭제된 AD 그룹 계정으로 SQL Server 로그인 사례 (로그인 그룹 삭제 후 조치해야할 사항) (1)	2019.09.12
QPI(Query Performance Insights) 라이브러리를 사용하여 Azure SQL Managed Instance의 로그쓰기 사용량 확인 (0)	2019.09.11
Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 (0)	2019.09.07
SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 (0)	2019.09.04
SQL Server Agent Job에서 sysploicy_purge_history 작업실패 (0)	2019.08.08

PREV 1 2 3 4 ···28 NEXT

SQL Server

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SSRS 강좌' 카테고리의 다른 글

'SQL Server > SSRS 강좌' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SSRS 강좌' 카테고리의 다른 글

'SQL Server > SSRS 강좌' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL on Azure' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

'SQL Server > SQL Server Tip' 카테고리의 다른 글

티스토리툴바