'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)
[AWS RDS] Modify RDS instance type 2022.03.23
[AWS RDS] Modify RDS SQL Server Standard Edition to Enterprise Edition 2022.03.22
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
SQL Linux의 fsync 및 버퍼된 IO (버퍼된 쓰기중 오류가 발생하였을때 파일은 유효할까?) 2019.09.17
SQL Server와 SQL Linux에서 인스턴스 파일 초기화 차이점 2019.09.14
SQL Server Agent Job에서 sysploicy_purge_history 작업실패 2019.08.08
SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 2019.05.03
SQL Server master 데이터베이스 정리 2019.04.19
SQL Server 프로토콜과 SQLCMD를 사용한 연결 2019.04.18
SQL Server Edition 다운그레이드 후 확인사항 2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 2019.04.11
Azure SQL에서 네트워크를 구성하는 방법 2019.03.26
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block 2019.03.26 (2)
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 2019.03.26
SQL Server MySQL PostgreSQL 비교 2019.03.26
SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout 2019.03.26
SQL Server 2019 에서 문자열 잘림에 대한 향상된 에러 메시지 반환 2019.03.26
SQL Server 2019 에서 업그레이드된sp_estimate_data_compression_savings 프로시저 (컬럼스토어 압축율 예상) 2019.03.26
SQL Server 2019 에서 추가된sys.dm_db_page_info, sys.fn_PageResCracker 기능으로 대기 관련 정보 확인 2019.03.26
SQL Server 2019에서 향상된 Rowstore batch mode 2019.03.26

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

[AWS RDS] Modify RDS instance type

2022. 3. 23. 02:28

[AWS RDS] Modify RDS instance type

- AWS RDS 인스턴스 타입 변경 (업그레이드 또는 다운그레이드)

l Version : Amazon RDS

AWS RDS 환경에서는 운영중인 RDS 인스턴스의 용량 증설 및 감소(Scale-Up / Scale-Down) 작업을 Management Console을 통해서 매우 간단하게 변경할 수 있다. 아래 순서에 따라 인스턴스를 변경할 수 있다.

1. 변경할 인스턴스를 선택하고 Modify버튼을 클릭한다.

2. DB Instance size 항목에서 변경할 인스턴스 타입을 선택 한다. 이 글에서는 db.r5.large 인스턴스를 db.t3.medium으로 다운그레이드 한다.

3.맨 아래 위치한 Continue 버튼을 클릭한다.

4. 변경하려는 인스턴스 타입을 다시 한번 확인하고, Scheduling of modifications 항목에서 정해진 시간에 수정사항을 적용할 것인지, 아니면 즉시 적용할 것인지 선택 한다. 이 글에서는 즉시 변경 (Apply immediately)를 선택한다.

5. 인스턴스가 수정되고 있는 상황을 확인할 수 있다.

즉시 변경을 선택하였더라도 인스턴스의 종류, 백업할 데이터 양에 따라 수초에서 수분이 걸릴 수 있다. 따라서 일정 시간 다운타임이 발생한다. 하지만 이러한 다운타임은 예방하기 위해서는 데이터베이스를 이중화 구성하여 롤링 업그레이드 방식을 통해서 하나씩 업그레이드 하면서 failover로 역할을 변경하면 다운타임을 예방할 수 있다.

높은 성능의 인스턴스에서 낮은 성능의 인스턴스로 변경할 때 간혹 아래와 같은 오류가 발생할 수 있다.

이때에는 Performance Insights 항목에서 Enable Performance Insights 항목을 선택해제하고, 적용한 후 인스턴스 타입을 변경할 수 있도록 한다. 이번 실습에서 db.r5.large에서 db.t3.medium으로 먼저 선택하면 해당 항목이 숨겨져서 보이지 않게 되는데, 실제 변경시에는 오류를 반환한다.

[참고자료]

l https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PerfInsights.Enabling.html

2022-03-23 / Sungwook Kang / http://sungwookkang.com

AWS RDS, SQL Server, RDS 인스턴스 타입 변경, Modify RDS Instance type

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

'AWS' 카테고리의 다른 글

[AWS] What is AWS Graviton processor? (0)	2022.03.26
[AWS RDS] RDS Proxy (0)	2022.03.23
[AWS RDS] Modify RDS SQL Server Standard Edition to Enterprise Edition (0)	2022.03.22
[AWS Aurora] Aurora I/O Planning (0)	2022.03.20
[AWS Aurora] Aurora Storage Engine (0)	2022.03.19

[AWS RDS] Modify RDS SQL Server Standard Edition to Enterprise Edition

2022. 3. 22. 00:05

[AWS RDS] Modify RDS SQL Server Standard Edition to Enterprise Edition

- AWS RDS SQL Server 스탠다드 에디션에서 엔터프라이즈 에디션으로 변경

l Version : Amazon RDS for SQL Server

Microsoft SQL Server는 다양한 버전으로 제공되며 각 버전은 고유한 기능, 성능 및 가격 옵션을 제공한다. 설치하는 버전도 특정 요구사항에 따라 다르다. 일부 고객은 더 높은 메모리와 고가용성 기능을 활용하기 위해 SQL Server용 Amazon RDS Standard Edition에서 Amazon RDS Enterprise Edition으로 변경을 원할 수 있다. 이번 글은 RDS SQL Server 스탠다드 에디션에서 RDS SQL Server 엔터프라이즈 에디션으로 업그레이드 하는 방법에 대해서 살펴 본다.

업그레이드를 위해서 작업자는 아래의 권한을 가지고 있어야 한다.

l Amazon RDS for SQL Server

l AWS Management Console 접근 권한

l SQL Server Management Studio

업그레이드 프로세스는 아래와 같은 단계가 포함된다.

l 기존 RDS SQL Server Standard Edition 인스턴스의 스냅샷 생성

l 스냅샷을 RDS SQL Server Enterprise Edition으로 복원

l RDS SQL Server Enterprise 인스턴스 확인

먼저 콘솔을 통해 SQL Server용 RDS 버전을 수정하는 방법을 안내한다. 기존 RDS for SQL Server 인스턴스의 스냅샷을 만든 다음 다른 버전의 SQL Server로 복원한다. SQL Server Management Studio에서 RDS 버전을 확인할 수 있다.

1. Amazon RDS 콘솔에서 데이터베이스를 선택

2. 데이터베이스를 선택하고 작업 메뉴에서 스냅샷 생성을 선택

3. 스냅샷 이름을 입력, 스냅샷을 생성

4. 스냅샷 페이지에서 스냅샷이 성공적으로 생성되었는지 확인하고 상태가 사용 가능한지 확인

5. 스냅샷을 선택하고 작업 메뉴에서 스냅샷 복원을 선택

6. DB 사양에서 SQL Server의 새 버전(SQL Server Enterprise Edition)을 선택

7. DB 인스턴스 식별자에 새 인스턴스 이름을 입력

8. 인스턴스 클래스를 선택

9. DB 인스턴스 복원 선택

10. 데이터베이스 복원 진행

11. 데이터베이스가 복원된 후 SQL Server 버전 확인

AWS CLI를 통해서도 SQL Server용 RDS 인스턴스를 업그레이드 할 수 있다. 아래 스크립트를 실행하여 DB 스냅샷을 생성한다.

aws rds create-db-snapshot ^
–db-instance-identifier mydbinstance ^
–db-snapshot-identifier mydbsnapshot

아래 스크립트를 실행하여 스냅샷에서 데이터베이스를 복원한다.

aws rds restore-db-instance-from-db-snapshot ^
–db-instance-identifier mynewdbinstance ^
–db-snapshot-identifier mydbsnapshot^
–engine sqlserver-ee

업그레이드가 완료되면 향후 비용이 발생하지 않도록 기존 RDS Standard Edition은 삭제 하도록 한다.

[참고자료]

l https://dataintegration.info/modify-an-amazon-rds-for-sql-server-instance-from-standard-edition-to-enterprise-edition

2022-03-21 / Sungwook Kang / http://sungwookkang.com

AWS RDS, SQL Server, AWS SQL Server, Modify SQL Server Edition, 에디션 변경

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

'AWS' 카테고리의 다른 글

[AWS RDS] RDS Proxy (0)	2022.03.23
[AWS RDS] Modify RDS instance type (0)	2022.03.23
[AWS Aurora] Aurora I/O Planning (0)	2022.03.20
[AWS Aurora] Aurora Storage Engine (0)	2022.03.19
[AWS RDS MySQL] RDS MySQL와 Aurora MySQL 차이점 (0)	2022.03.18

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

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

SQL Server Agent Job에서 sysploicy_purge_history 작업실패

2019. 8. 8. 03:43

SQL Server Agent Job에서 sysploicy_purge_history 작업실패

· Version : SQL Server

SQL Server 2008 이후로 SQL Server Agent Job에서는 syspolicy_purge_history라는 job이 등록되어 있다. 해당 Job은 MSDB에 쌓여 있는 히스토리를 정리하는 작업을 진행한다. 가끔 해당 Job 실행시 아래와 같은 오류가 발생할 수 있는데, 오류가 무엇인지 확인해보고 해결하는 방법에 대해서 살펴 본다.

Job이 실패하였을때, 해당 로그를 살펴보면 아래와 같은 오류로그가 발생하였다.

Date 7/29/2019 1:23:41 PM

Log Job History (syspolicy_purge_history)

Step ID 3

Server

Job Name syspolicy_purge_history

Step Name Erase Phantom System Health Records.

Duration 00:00:00

Sql Severity 0

Sql Message ID 0

Operator Emailed

Operator Net sent

Operator Paged

Retries Attempted 0

Message

Executed as user: NT Service\SQLSERVERAGENT. The job script encountered the following errors. These errors did not stop the script: A job step received an error at line 1 in a PowerShell script. The corresponding line is 'import-module SQLPS'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The specified module 'SQLPS' was not loaded because no valid module file was found in any module directory. ' A job step received an error at line 1 in a PowerShell script. The corresponding line is 'SQLSERVER:'. Correct the script and reschedule the job. The error information returned by PowerShell is: 'The term 'SQLSERVER:' is not recognized as the name of a cmdlet, function, script file, or operable program. Check the spelling of the name, or if a path was included, verify that the path is correct and try again. '. Process Exit Code -1. The step failed.

해당 Job 이 실행하는 명령을 직접 Powershell콘솔에서 실행하여도 아래와 같은 오류가 발생한다.

해당 오류는 명령이 참조하는 모듈의 PATH 설정이 누락되어 발생한 경우로, 시스템 변수에서 해당 변수값을 지정하면 해결이 가능하다. 모듈의 PATH 경로는 SQL 버전마다 조금씩 다르며 아래 경로를 참고할 수 있도록 한다.

C:\Program Files (x86)\Microsoft SQL Server\140\Tools\PowerShell\Modules

모듈이 위치한 경로를 확인하였으면, [My Computer] – [right click] – [Properties]에서 [Advance] 탭을 선택하여 [Environmeont Variables] 를 클릭한다. PSModulePath 이름으로 모듈이 위차한 PATH를 등록(또는 수정)한다.

경로가 올바르게 수정되었으면, SQLPS를 시작하여 정상적으로 PATH 설정이 완료되었는지 확인이 가능하다. 또는 SQL Agent에서 해당 Job을 실행하여 정상적으로 실행이 되는지 확인할 수 있다.

[참고자료]

https://blog.sqlauthority.com/2016/03/08/sql-server-syspolicy_purge_history-job-failing-step-erase-phantom-system-health-records/

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

SQL Server, Agent job, sysploicy_purge_history , Agent 히스토리 삭제, PSModulePath, Erase Phantom System Health Records

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

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

Azure SQL Managed Instance에서 로컬 스토리지 사용량 모니터링 (0)	2019.09.07
SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 (0)	2019.09.04
SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 (0)	2019.05.03
SQL Server master 데이터베이스 정리 (0)	2019.04.19
SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18

SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기

2019. 5. 3. 03:55

SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기

· Version : SQL Server

SQL Server에서 파티션 테이블을 사용하기 위해서는 SQL Server Enterprise Edition을 사용해야한다. Standard Edition에서도 파티션 테이블 처럼 사용하기 위해서는 새로운 테이블을 생성하고 sp_rename 함수를 사용하여 기존테이블과 새로운 테이블의 이름을 변경하는 방식으로 사용하였다.

-- Replace live with staging

BEGIN TRAN

DROP TABLE DataTable;

EXEC sp_rename DataTable_Staging, DataTable;

COMMIT

-- Swap live and staging

BEGIN TRAN

EXEC sp_rename DataTable, DataTable_Old;

EXEC sp_rename DataTable_Staging, DataTable;

EXEC sp_rename DataTable_Old, DataTable_Staging;

COMMIT

위와 같은 방법을 사용하여 메타 데이터 레벨에서 테이블 이름을 변경하면 스키마가 일치하더라도 이름이 바뀐 테이블이 지정된 이름의 메타 데이터와 연결되지 않는다. 시스템은 캐시된 ObjectID를 사용하는데 스왑아웃을 수행하려면 캐시 데이터를 업데이트하는 추가 단계가 필요하다. 스위치 아웃으로 인해 ObjectID및 관련 메타정보가 그대로 유지되면 이러한 추가단계가 불필요하다.

ALTER TABLE SWITCH TO 명령은 기존 테이블을 유지하면 데이터 세트를 스와핑힌다. 이 명령을 사용하기 위해서는 스키마 정보가 호환 가능해야한다. 컬럼이름, 컬럼 순서, 조약 조건등 특성이 동일해야한다.

-- Replace live with staging

BEGIN TRAN

TRUNCATE TABLE DataTable;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

COMMIT

-- Swap live and staging

/* Note: An extra table, DataTable_Old, is required to temporarily hold the data being replaced before it is moved into DataTable_Staging. The rename-based approach did not require this extra table. */

BEGIN TRAN

ALTER TABLE DataTable SWITCH TO DataTable_Old;

ALTER TABLE DataTable_Staging SWITCH TO DataTable;

ALTER TABLE DataTable_Old SWITCH TO DataTable_Staging;

COMMIT

[참고자료]

https://bengribaudo.com/blog/2016/11/15/3521/swapping-data-sets

2019-05-02 / Sungwook Kang / http://sungwookkang.com

SQL Server, partition table, table swap, data set swap, alter table switch to, sp_rename, alter table, 테이블 변경, 테이블 이름 변경, 데이터 변경

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

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

SQL Server Enterprise Edition 에서 CPU를 40 Core 이상 사용하지 못하는 현상 (0)	2019.09.04
SQL Server Agent Job에서 sysploicy_purge_history 작업실패 (0)	2019.08.08
SQL Server master 데이터베이스 정리 (0)	2019.04.19
SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18
SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12

SQL Server master 데이터베이스 정리

2019. 4. 19. 01:47

SQL Server master 데이터베이스 정리

· Version : SQL Server

SQL Server에서 master 데이터베이스의 역할은 시스템에 대한 모든 정보를 기록한다. 여기에는 로그인 계정, 끝점, 연결된 서버 및 시스템 구성설정과 같은 인스턴스 차원의 메타 데이터가 포함된다.

· master database : https://docs.microsoft.com/en-us/sql/relational-databases/databases/master-database?view=sql-server-2017

우리는 가끔 의도하지 않게 master 데이터베이스에 개체를 생성하는 경우가 있다. 대부분 개체를 생성할 때 USE 문을 생략했을 수도 있고 수 많은 작업창을 띄어 놓고 사용하다가 실수할 수도 있다. 이번 아티클에서는 master 데이터베이스를 정리하는 방법에 대해서 알아본다. master 데이터베이스는 시스템의 설정을 저장하고 있으므로 해당 작업시 주의가 필요 하다.

[master에서 삭제하지 않을 목록]

아래 표에 정으되어 있는 오브젝트 유형은 시스템과 관련있기 때문에 삭제 목록에서 제외한다.

ET	External table
IT	Internal table
PC	Assembly (CLR) stored procedure
PG	Plan guide
RF	Replication-filter-procedure
S	System table
SQ	Service queue
TA	Assembly (CLR) DML Trigger
X	Extended procedure
-	CLR User-Defined Data Type (UDDT)

사용자는 자신만의 모듈을 생성하여 시스템 객체로 표시할 수도 있다. 이 경우 시스템 저장프로시저와 구별 할 수 있는 확실한 방법이 없기 때문에 주관적으로 식별해야 한다.

[master 에서 삭제할 목록]

오브젝트를 삭제하기 위해서는 약간의 순서가 필요하다. 예를 들어 테이블을 삭제하려면 외부키나 뷰를 제거해야하며, 테이블 함수를 사용하는 테이블을 삭제 될 때까지 파티션 함수를 제거할 수 없다. 또한 순환 참조가 있을 수 있으므로 많은 경우 스크립트를 여러번 실행해야 할 수도 있다.

아래 표시된 유형은 객체를 삭제하면 자동으로 삭제되기 때문에 크게 걱정할 필요가 없다.

C	Check constraint
D	Default constraint
PK	Primary key constraint
TR	DML trigger
UQ	Unique constraint

나머지는 아래 순서대로 삭제해야한다.

1	F	Foreign key constraint	Need to be dropped before tables, as described below.
2	V	View	Should be dropped before tables, since they can have direct or indirect SCHEMABINDING.
	FN	SQL scalar function
	IF	SQL inline table-valued function
	TF	SQL table-valued-function
3	P	Stored procedure	Need to be dropped before tables, but after views and functions (since procedures can reference views and functions, but can't be referenced by them except in rare cases using OPENQUERY()).
4	U	Table (user-defined)	Dropped after foreign keys and any type of module that can reference them with SCHEMABINDING.
5	TT	Table type	Dropped after tables and modules, but before old-style rules that could be bound to them. Table types need to be dropped before other types.
5	Alias types
6	SO	Sequence object	Dropped after tables/modules because they can't be dropped if any table or module references them.
	R	Rule (old-style, stand-alone)
	D	Old-style CREATE DEFAULT
	Partition functions
7	Partition schemes		Can't be dropped until after partition functions.
8	SN	Synonym	I drop these toward the end because they can reference many of the items above (though this entity is not really prone to any issues on its own, unless you have external references pointing at them).
9	Schemas		I drop these almost last because almost all entities above can belong to a certain schema. For any entities you want to keep but move to a different schema, you'll need to first use ALTER SCHEMA ... TRANSFER.
10	Roles and Users		For users you don't want to keep, you'll need to first remove them from user-defined role membership and ownership, as well as ownership of any schemas. (For users you do want to keep, you may also want to remove any inappropriate permissions, but I'll deal with that in a separate post.) For roles you don't want to keep, you'll need to first remove any members; but you can't remove members who also happen to own roles.

[기본 접근]

순환 참조는 여러 개체가 서로를 참조하므로 개체를 삭제하는 올바른 순서를 결정하기가 복잡하다. 그래서 스크립트가 실패하더라도 다시 실행하여 스크립트가 성공할때 까지 반복해서 실행 할 수 있는 스크립트로 작성해야 한다. 예를 들어 두 개의 SCHEMABINDING 뷰를 사용하는 경우 두 개의 뷰를 모두 삭제해야 테이블이 삭제된다. 뷰 순서가 명확하다면 뷰 순서대로 하면 되지만 뷰가 여러개 이면서 서로 뷰를 참조하는 경우 순서를 예측하기 어렵기 때문에 뷰 삭제 명령을 반복해서 실행한다.

try { drop view 1; go; drop view 2; }

[삭제 스크립트 (순서대로 실행)]

Foreign keys

-- script to drop Foreign Keys

USE [master];

SET NOCOUNT ON;

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

ALTER TABLE ' + objectname + N'

DROP CONSTRAINT ' + fkname + N';

END TRY

BEGIN CATCH

SELECT N''FK ' + fkname + N' failed. Run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT fkname = QUOTENAME(fk.[name]),

objectname = QUOTENAME(s.[name]) + N'.' + QUOTENAME(t.[name])

FROM sys.foreign_keys AS fk

INNER JOIN sys.objects AS t

ON fk.parent_object_id = t.[object_id]

INNER JOIN sys.schemas AS s

ON t.[schema_id] = s.[schema_id]

) AS src;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

View and functions

-- script to drop views

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

DROP VIEW ' + objectname + N';

END TRY

BEGIN CATCH

SELECT N''View ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(v.[name])

FROM sys.views AS v

INNER JOIN sys.schemas AS s

ON v.[schema_id] = s.[schema_id]

WHERE v.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop functions

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Function ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.objects AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

AND o.[type] IN ('FN','IF','TF')

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Stored Procedures

-- script to drop procedures

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP PROCEDURE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Procedure ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.objects AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.[type] = 'P'

AND o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Tables

-- script to drop user tables

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP TABLE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Table ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.tables AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Table Types

-- script to drop table types

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Type ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.types AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_table_type = 1

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Alias Types

-- script to drop alias types

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP TYPE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Type ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.types AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.user_type_id > 256

AND o.is_table_type = 0

AND o.is_assembly_type = 0 -- not a CLR UDT

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Sequences

-- script to drop sequences

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP SEQUENCE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Sequence ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.sequences AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Old-style CREATE RULE

-- script to drop old-style Rules

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP RULE ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Rule ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.objects AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

AND [type] = 'R'

AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Old-style CREATE DEFAULT

-- script to drop old-style Defaults

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP DEFAULT ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Default ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.objects AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

WHERE o.is_ms_shipped = 0

AND [type] = 'D'

AND parent_object_id = 0

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Partition functions

-- script to drop partition functions

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP PARITITON FUNCTION ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Partition function ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME([name])

FROM sys.partition_functions

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Partition schemes

-- script to drop partition schemes

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP PARITITON SCHEME ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Partition scheme ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME([name])

FROM sys.partition_schemes

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Synonyms

-- script to drop synonyms

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP SYNONYM ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Synonym ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name]) + N'.' + QUOTENAME(o.[name])

FROM sys.synonyms AS o

INNER JOIN sys.schemas AS s

ON o.[schema_id] = s.[schema_id]

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Schemas

-- script to drop schemas

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

EXEC sys.sp_executesql N''DROP SCHEMA ' + objectname + N';'';

END TRY

BEGIN CATCH

SELECT N''Schema ' + objectname + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

' FROM

(

SELECT QUOTENAME(s.[name])

FROM sys.schemas AS s

WHERE [schema_id] BETWEEN 5 AND 16383

) AS src(objectname);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to re-route default schemas

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

ALTER USER ' + QUOTENAME(name) + N' WITH DEFAULT_SCHEMA = dbo;

END TRY

BEGIN CATCH

SELECT N''User ' + p.name + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

FROM sys.database_principals AS p

WHERE p.default_schema_name IS NOT NULL

AND NOT EXISTS

(

SELECT 1

FROM sys.schemas AS s

WHERE name = p.default_schema_name

);

SELECT @sql;

--EXEC sys.sp_executesql @sql;

Roles and Users

-- script to change ownership of roles to dbo

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

ALTER AUTHORIZATION ON ROLE::' + QUOTENAME(r.name) + N' TO dbo;

END TRY

BEGIN CATCH

SELECT N''Role ' + r.name + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

FROM sys.database_principals AS r

INNER JOIN sys.database_principals AS u

ON r.owning_principal_id = u.principal_id

WHERE r.[type] = 'R'

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##' -- hopefully you don't name users/roles this way!

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to change ownership of schemas to dbo

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

ALTER AUTHORIZATION ON SCHEMA::' + QUOTENAME(name) + N' TO dbo;

END TRY

BEGIN CATCH

SELECT N''Schema ' + name + N' failed - run the script again.'',

ERROR_MESSAGE(); END CATCH

FROM sys.schemas

WHERE [schema_id] BETWEEN 5 AND 16383

AND principal_id BETWEEN 5 AND 16383;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to remove members from roles

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

' + CASE WHEN CONVERT(tinyint,

PARSENAME(CONVERT(nvarchar(128),

SERVERPROPERTY(N'ProductVersion')),4)) >= 11 -- 2012+

THEN

N'ALTER ROLE ' + QUOTENAME(r.name) + N' DROP MEMBER '

+ QUOTENAME(m.name) + N';'

ELSE

N'EXEC [sys].[sp_droprolemember] @rolename = N''' + r.name

+ ''', @membername = N''' + m.name + N''';'

END + N'

END TRY

BEGIN CATCH

SELECT N''Role ' + r.name + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

FROM sys.database_role_members AS rm

INNER JOIN sys.database_principals AS r

ON rm.role_principal_id = r.principal_id

AND r.is_fixed_role = 0

INNER JOIN sys.database_principals AS m

ON rm.member_principal_id = m.principal_id;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop roles

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

DROP ROLE ' + QUOTENAME(r.name) + N';

END TRY

BEGIN CATCH

SELECT N''Role ' + r.name + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

FROM sys.database_principals AS r

WHERE r.[type] = 'R'

AND r.name <> N'public'

AND r.is_fixed_role = 0;

SELECT @sql;

--EXEC sys.sp_executesql @sql;

-- script to drop users

USE [master];

DECLARE @sql nvarchar(max) = N'';

SELECT @sql += N'BEGIN TRY

DROP USER ' + QUOTENAME(u.name) + N';

END TRY

BEGIN CATCH

SELECT N''User ' + u.name + N' failed - run the script again.'',

ERROR_MESSAGE();

END CATCH

FROM sys.database_principals AS u

WHERE u.[type] IN ('U','S')

AND u.name NOT IN (N'public', N'dbo', N'guest', N'INFORMATION_SCHEMA', N'sys')

AND u.name NOT LIKE N'##%##';

SELECT @sql;

--EXEC sys.sp_executesql @sql;

지금까지 master 데이터베이스를 정리하는 작업 및 순서, 스크립트 생성에 대해서 알아보았다. 객체를 삭제하는 명령은 자동화 할 수 있지만 모든것은 완벽히 분석할 수는 없다. (사용자 모듈을 시스템 모듈로 생성한 경우 등) 위 스크립트를 사용하더라도 생성된 스크립트를 반드시 DBA 가 검토를 해야한다. master 데이터베이스를 정리하는것은 위험한 작업이므로 반드시 많은 테스트와 검증이 필요하다.

[참고자료]

https://www.mssqltips.com/sqlservertip/4530/cleaning-up-the-sql-server-master-database/

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

SQL Server, SSMS, mssql, DBA, master database, sys.database_principals, sys.database_role_members, sys.foreign_keys, sys.objects, sys.partition_functions, sys.partition_schemes, sys.procedures, sys.schemas, sys.sequences,

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

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

SQL Server Agent Job에서 sysploicy_purge_history 작업실패 (0)	2019.08.08
SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 (0)	2019.05.03
SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18
SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11

SQL Server 프로토콜과 SQLCMD를 사용한 연결

2019. 4. 18. 01:08

SQL Server 프로토콜과 SQLCMD를 사용한 연결

· Version : SQL Server

SQL Server에 연결하는 것은 이미 많은 문서를 통해서 잘 알려져 있으며 GUI를 제공하는 툴들이 많아서 어렵지 않게 느껴질 수가 있다. 하지만 상황에 따라 복잡해 질 수 있다. 만약 TCP 포트에서 수신대기를 하지 않는 SQL Server 인스턴스에는 어떻게 연결할까? 호스트 컴퓨터가 서버 DNS를 확인할 수 없는 경우 어떻게 SQL Server에 연결할까? 명명된 인스턴스는 무엇일까? 이번 포트스에서 SQLCMD를 사용하여 다양한 프로토콜의 의미와 사용법을 알아 본다.

SQL Server를 관리하는 다양한 툴들이 있다. 특히SSMS(SQL Server Management Studio)는 Microsoft에서 공식으로 제공하는 SQL Server관리툴이며GUI를 제공한다. 그리고 SQLCMD라는 CLI가 있다. SQLCMD의 경우 명령줄로 이루어져 있다. SQL Server가 Linux를 지원하면서 우리는 GUI 뿐만아니라 쉘 환경에도 익숙해져야 하기 때문에 SQLCMD에 대해 다루어 본다.

SSMS를 사용하여 SQL Server 인스턴스에 연결할 때 특정 프로토콜을 사용하기 위해서 Connection Properties 탭에서 프로토콜을 선택할 수 있다.

대부분 SQL Server를 연결할 때, 프로토콜을 지정하지 않으면 TCP/IP를 사용한다고 생각할 수 있다. 하지만 프로토콜을 지정하지 않으면 TCP/IP를 사용한다는 보장을 할 수 없다. SSMS및 SQLCMD에는 SQL Server 구성관리자의 프로토콜에 따라 순서가 결정된다. 아래 그림은 SQL Server 구성관리자의 프로토콜 구성이다. 구성관리자에는 32bit, 64bit 두 가지 클라이언트 구성이 있다. SSIS를 사용하는 경우 32bit 커넥터를 사용할 수 있기 때문에 중요하다. 패키자가 64bit 버전에서 실행되고 64bit 설정을 하지 않을 경우 패키지가 실패한다.

[SQLCMD를 사용한 SQL Server TCP/IP 연결]

TCP/IP 프로토콜을 사용하여 SQL Server 인스턴스에 연결할 때, 인스턴스가 기본 포트가 아닌 경우 서버 IP 주소, 또는 호스트 이름과 포트가 필요하다. 아래 스크립트는 TCP/IP 를 사용하여 연결하는 일반적인 구문이다.

sqlcmd -S tcp:<computer name>,<port number>

IP 주소를 입력하면 TCP/IP 프로토콜을 사용할 것이라는 보장을 할 수 없다. TCP/IP를 보장하기 위해서 tcp:라는 접두어를 사용하여 연결한다. 예를 들어 Windows 인증을 사용하는 SQL Server의 기본 인스턴스를 사용하여 SQL-A라는 서버에 TCP/IP 프로토콜을 사용하여 연결하려는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A

sqlcmd –S tcp:SQL-A –U sa –P Pa$$w0rd

IP를 사용할 경우 아래 구문을 이용한다.

sqlcmd –S tcp:10.10.10.10

sqlcmd –S tcp:10.10.10.10 –U sa –P Pa$$w0rd

명명된 인스턴스를 사용하는 경우 아래 구문을 이용한다.

sqlcmd –S tcp:SQL-A\TEST

sqlcmd –S tcp:SQL-A\TEST –U sa –P Pa$$w0rd

포트를 지정해야 할 경우 콤마(,)로 구분하며 포트를 지정해야 한다. 예를 들어 기본 인스턴스가 1433 포트를 사용하고 TEST 인스턴스가 51613포트를 사용하는 경우 연결 문자열을 아래와 같다.

sqlcmd –S tcp:SQL-A,1433

sqlcmd –S tcp:SQL-A,1433 –U sa –P Pa$$w0rd

sqlcmd –S tcp:10.10.10.10,1433

sqlcmd –S tcp:10.10.10.10,1433 –U sa –P Pa$$w0rd

sqlcmd –S tcp:SQL-A,51613

sqlcmd –S tcp:SQL-A,51613 –U sa –P Pa$$w0rd

sqlcmd –S tcp:10.10.10.10,51613

sqlcmd –S tcp:10.10.10.10,51613 –U sa –P Pa$$w0rd

[SQLCMD와 SQL Server 명명된 파이트 연결]

명명된 파이프는 서버와 클라이언트 간의 통신을 위해 명명된, 단방향, 또는 이중 파이프이다. 내부적으로 명명된 파이프의 모든 인스턴스는 동일한 파이프 이름을 갖지만 메시지 기반 통신과 클라이언트 가장을 허용하는 자체 버퍼를 유지한다. 명명된 파이프는 프로세스간 통신(IPC)에 의존한다. 명명된 파이프를 사용하여 로컬 인스턴스에 연결하면 해당 파이프가 커널 모드에서 로컬 프로시저 호출(LPC)로 실행된다는 점에 유의한다. 일반적으로 프로토콜은 원격 SQL Server 인스턴스에 연결할때 TCP/IP를 사용하는것이 바람직하기 때문에 사용되지 않는다. 반면 로컬 인스턴스에 연결할 때 공유 메모리는 종종 선택되어 사용된다. 아래는 기본 인스턴스의 명명된 파이트 연결구문이다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\sql\query

예를 들어 서버 MYSERVER의 기본 인스턴스에 대한 명명된 파이프는 다음과 같다.

\\ MYSERVER \ pipe \ sql \ query

명명된 인스턴스의 구문은 다음과 같다.

\\[COMPUTER NAME OR IP ADDRESS]\pipe\MSSQL$[SQL Server Instance Name]\sql\query

예를 들어 서버 SQL-A에서 SQL Server 인스턴스 TEST에 명명된 파이프를 사용하려는 경우 구문은 아래와 같다.

\\SQL-A\pipe\MSSQL$TEST\sql\query

명명된 파이프 프로토콜을 사용하여 SQL Server 인스턴스에 연결하는 것은 TCP/IP를 사용하는것과 크게 다르지 않다. 연결문자열에np: 접두어를 사용한다. 아래 구문은 Windows 및 SQL Server 인증을 사용하여 SQL Server 기본 인스턴스와 명명된 인스턴스에 연결한다.

sqlcmd –S np:\\SQL-A\pipe\sql\query

sqlcmd –S np:\\SQL-A\pipe\sql\query –U sa –P Pa$$w0rd

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query

sqlcmd –S np:\\SQL-A\pipe\MSSQL$TEST\sql\query –U sa –P Pa$$w0rd

[SQLCMD를 사용한 SQL Server 공유 메모리 연결]

이 프로토콜은 클라이언트 연결이 로컬 서버에서 실행될 때만 사용될 수 있다. 기본적으로 커널 모드에서 실행되는 로컬 프로시저 호출(LPC)이다. MDAC 2.8 이하를 사용하는 경우 공유 메모리 프로토콜을 사용할 수 없다. 이 경우 sqlcmd가 자동으로 명명된 파이프로 전환한다. 공유 메모리를 사용하는 접두사는 lpc: 이다.

sqlcmd –S lpc:SQL-A

sqlcmd –S lpc:SQL-A –U sa –P Pa$$w0rd

sqlcmd –S lpc:SQL-A\TEST

sqlcmd –S lpc:SQL-A\TEST –U sa –P Pa$$w0rd

[참고자료]

https://www.mssqltips.com/sqlservertip/5133/different-ways-to-connect-to-sql-server-using-sqlcmd/

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

SQL Server, SSMS, sqlcmd, named pipe, sql server protocol, sql connection, mssql

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

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

SQL Server에서 테이블 이름을 변경하지 않고 테이터만 교체하기 (0)	2019.05.03
SQL Server master 데이터베이스 정리 (0)	2019.04.19
SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block (2)	2019.03.26

SQL Server Edition 다운그레이드 후 확인사항

2019. 4. 12. 00:23

SQL Server Edition 다운그레이드 후 확인사항

· Version : SQL Server

SQL Server Enterprise Edition 에서 SQL Server Standard Edition으로 다운 그레이드 후 확인해야 할 몇 가지 사항에 대해서 알아본다. SQL Server Enterprise Edition을 SQL Server Standard Edition으로 다운그레이드 후 일부 구성이 기본값으로 다시 설정된다.

[SQL Server 오류 로그 수]

SQL Server 오류 로그 파일의 수가 기본 6개로 재설정된다. 이 설정을 확인하고 필요한 수로 설정한다. SSMS의 GUI를 사용할 수도 있으며 T-SQL 코드를 사용할 수 있다.

USE [master]

EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'NumErrorLogs', REG_DWORD, 99

[SQL Agent 메일 프로필]

SQL Server Agent 속성의 메일 프로필이 비활성 된다. 메일 프로필 사용 시 올바른 메일 프로필이 사용되는지 확인 한다. SSMS에서 GUI를 사용하거나T-SQL 코드를 사용할 수 있다.

USE [msdb]

EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1,

@databasemail_profile=N'DBServerAlerts_Profile', -- replace with your Agent's profile

@use_databasemail=1

[토큰 교체 설정]

“Replace tokens for all jobs responses to alert” 옵션을 사용하는 경우 다시 활성화 해야 한다. 위 그림(SQL Agent 메일 프로필 그림) 처럼 SSMS의 GUI를 사용하거나 T-SQL 코드를 사용할 수 있다.

USE [msdb]

EXEC msdb.dbo.sp_set_sqlagent_properties @alert_replace_runtime_tokens=1

[기타 잠재적 문제]

위의 설정은 레지스트리를 통해 구성되는 설정이며 시스템 데이터베이스는 저장되지 않는다.

· SQL Server 2014의 레지스트리 키 위치는 아래와 같다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.MSSQLSERVER\SQLServerAgent

· SQL Server 2017의 레지스트리 키 위치는 아래와 같다.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL14.MSSQLSERVER\SQLServerAgent.

이 레지스트리 키 아래 저장되는SQL Server 에이전트는 다음과 같다.

· AlertFailSafeEmailAddress

· AlertFailSafeNetSendAddress

· AlertFailSafeOperator

· AlertFailSafePagerAddress

· AlertNotificationMethod

· DatabaseMailProfile

· ErrorLogFile

· ErrorLoggingLevel

· IdleCPUDuration

· IdleCPUPercent

· JobHistoryMaxRows

· JobHistoryMaxRowsPerJob

· JobShutdownTimeout

· MonitorAutoStart

· RestartSQLServer

· UseDatabaseMail

SQL Server Edition 다운그레이드 위 사항을 확인하여 시스템 운영에 참고 할 수 있도록 한다.

[참고자료]

https://www.mssqltips.com/sqlservertip/4698/sql-server-edition-postdowngrade-steps/

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

SQL Server, Edition post-down grade, Job Agent, Mail profile, Error log number, Token replacement setting, SQL registry key, MS SQL, SQL Edition

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

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

SQL Server master 데이터베이스 정리 (0)	2019.04.19
SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block (2)	2019.03.26
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 (0)	2019.03.26

AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기

2019. 4. 11. 00:14

AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기

· Version : SQL Server

SQL Server에서 AlwaysOn을 구성하였을때, Failover 를 대비하여 Secondary 서버에서도 Primary서버와 동일하게 계정, Job Agent 등이 구성되어 있어야 한다. 그런데 Secondary의 경우 DB가 동기화 되고 있는 대기 서버이기 때문에 Job Agent를 Primary와 동일하게 설정하면 Secondary에서 Job 실행 시 Job Fail이 발생한다. 또한 Secondary에서 일부 Job 의 경우 실행이 되지 말아야 할 것들이 있다. 아래 스크립트는 AlwaysOn 의 role을 확인하여 서버가 Primary일 때만 Job Agent가 수행되도록 한다. 각각의 Job Agent의 첫 번째 단계에 추가하여 Primary Role이 아닐때 Job 수행을 중지하도록 한다.

DECLARE @SERVER_ROLE nvarchar(50)

SELECT

@SERVER_ROLE = A.ROLE_DESC

FROM sys.dm_hadr_availability_replica_states AS A

INNER JOIN sys.availability_groups AS B ON A.GROUP_ID = B.GROUP_ID

WHERE A.IS_LOCAL = 1

IF @SERVER_ROLE <> 'PRIMARY'

EXEC msdb.dbo.sp_stop_job N'Job Name' ;

Secondary에서 Job 이 실행되었을때, Role을 확인하는 부분이 있어 Job이 중지된것을 확인할 수 있다.

[참고자료]

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-stop-job-transact-sql?view=sql-server-2017

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

SQL Server, AlwaysOn, Job Agent, sys.dm_hadr_availability_replica_states, AG Role check

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

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

SQL Server 프로토콜과 SQLCMD를 사용한 연결 (0)	2019.04.18
SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block (2)	2019.03.26
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 (0)	2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 (0)	2019.03.26

Azure SQL에서 네트워크를 구성하는 방법

2019. 3. 26. 00:26

Azure SQL에서 네트워크를 구성하는 방법

· Version : Azure SQL

Azure SQL Managed Instance는 Azure 클라우드에서 호스팅되고 Azure 가상 네트워크를 사용하는 완전히 관리되는 SQL Server 인스턴스이다. Managed Instance는 가상 네트워크를 사용하기 때문에 Azure 관리 서비스에서 사용자가 네트워크 액세스 규칙을 정의할 수 있다. 잘못된 네트워크 구성은 Managed Instance를 배포할때 오류가 발생하므로 아래 순서에 따라서 네트워크 환경을 구성하여 사용한다. 일반적으로 인스턴스용 가상 네트워크를 구성할 때 기본 5가지 단계로 정리할 수 있다.

1. Managed Instance가 사용할 가상 네트워크를 구성

2. Managed Instance가 Azure 관리 서비스와 통신할 수 있도록Route 테이블 생성

3. 가상 네트워크 생성시 생성된 기본 서브넷을 사용하거나Managed Instance 전용 서브넷을생성

4. 서브넷에 Route 테이블 할당

5. 4단계 까지 올바르게 구성되었는지 확인

[1. 가성 네트워크 구성]

Managed Instance는 사설 IP주소가 할당된 Azure Virtual network를 사용한다. 가상 네트워크는 Managed Instance를 만들기 전에 생성해야 한다. 가상 네트워크는 Azure Portal, PowerShell, Azure CLI등을 이용하여 생성할 수 있다. Azure Portal을 사용하여 가상 네트워크를 생성할때 Resource Manager 배포 모델을 사용해야 한다. 클래식 가상 네트워크 모델은 지원되지 않는다. 가상 네트워크를 생성할 때 Virtual Network Blade 는 Service Endpoint 옵션이 Disable로 설정되어 생성 되는데 는기본 옵션이므로 변경하지 않는 것을 권장한다.

Managed Instance 서브넷은 16 ~ 256개의 주소를 사용할 수 있다. 따라서 기본 서브넷의IP범위를 정의할 때 서브넷 마스크 /28 - /24 범위를 사용한다. 많은 인스턴스가 있을 경우 기본 서브넷에 (인스턴스 수 X 2) + 5개의 주소가 있는지 확인한다.

[2. Route 테이블 생성]

가성 네트워크 생성이 완료 되었으면 Managed Instance가 Azure 관리 서비스와 통신할 수 있도록 Route 테이블을 구성해야한다. Route 테이블은 Managed Instance가 배포된 서브넷에 연결되며 서브넷을 통해서 나가는 각 패킷은 연결된 Route 테이블 기반으로 처리된다. 서브넷은 단일 Route 테이블에만 연결할 수 있다. (Azure에서 Route 테이블을 만드는데는 비용이 부과 되지 않는다.)

Azure Portal에서 ‘Route table’이라는 새 리소스를 추가하고 Route 규칙에 “0.0.0.0/0 Next Hop Internet” 경로를 추가한다. 이 경로를 사용하면 가상 네트워크에 있는 Managed Instance가 인스턴스를 관리하는 Azure 관리 서비스와 통신할 수 있다. 이렇게 하지 않으면 Managed Instance를 배포 할 수 없다.

[3. 추가 서브넷 생성(선택사항)]

Managed Instance는 서브넷에 배포되므로 Managed Instance를 배포하기 전에 서브넷을 생성해야 한다. 기본 서브넷에 인스턴스를 배포하고 기본 경로를 사용하는경우 이 단계를 건너 뛸 수 있다. 서브넷은 Managed Instance 전용이며 다른 리소스를 포함할 수 없다. 서브넷에는 최소한 16개의 주소가 있어야 하고 5개의 주소는 Azure 내부 서비스용으로 예약되어 있다. 서브넷에 포함되는 모든 Managed Instance 에는 인스턴스당 2개의 주소가 필요하다. 서브넷에 Managed Instance를 배치하면 서브넷의 크기를 변경할 수 없으므로 주의한다.

[4.서브넷 구성]

서브넷(기본값 1 또는 신규)에는 “0.0.0.0/0 Next Hop Internet” 정보를 포함한 User Route Table(UDR)이 있어야 한다. “0.0.0.0/0 Next Hop Internet” Route 테이블을 생성하면 Managed Instance를 서브넷에 할당 할 수 있다. Azure Portal에서 서브넷을 정보를 확인할 수 있으며 아래 항목을 확인한다.

1. 서브넷에 할당된 Managed Instance Route 테이블 있다.

2. 서브넷에는 네트워크 보안그룹이 없어야 한다.

3. 서브넷에는 서비스 엔드 포인트가 없어야 한다.

4. 서브넷에는 다른 리소스가 없다.

[5. 체크리스트]

4단계 까지 구성이 완료 되었으면 Managed Instance를 배포할때 문제가 발생하지 않도록 아래 체크리스트 항목을 확인한다.

1. 가상 네트워크에서 서비스 끝점을 사용하지 않도록 설정한다.

2. 서브넷에는 16 ~256개의 IP 주소가 있어야 한다. (Masks from /28~ /24)

3. Managed Instance 서브넷에는 다른 리소스가 없어야 한다.

4. 서브넷에는 0.0.0.0/0 Next Hop Internet 경로가 있어야 한다.

5. 서브넷에는 보안 그룹이 없어야 한다.

6. 서브넷에는 서비스 끝점이 없어야 한다.

모든 구성이 완료 되었어면 Azure Portal을 사용하여 새로운 Managed Instance를 만들고 사용자가 만든 가상 네트워크 및 서브넷을 할당 할 수 있다.

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/03/14/how-to-configure-network-for-azure-sql-managed-instance/

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

SQL Server, Azure SQL, Azure Managed Instance, Azure network, Azure route table, Azure Virtual Network

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

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

Azure SQL에서 이메일 보내기 (0)	2019.03.26
Azure SQL에서 읽기 전용 복제본에 대한 접속 및 링크 서버 생성 (0)	2019.03.26
How to check if Azure SQL is using In-Memory (0)	2018.03.31
Azure SQL Server AlwaysOn Listener 연결 오류 (서브넷 구성 오류) (0)	2017.03.13
SQL Azure blob storage 백업을 위한 프록시 세팅 (0)	2016.10.26

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

2019. 3. 26. 00:17

인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block

· Version : SQL Server

SQL Server에서 인덱스 재구성 및 통계 업데이트 작업을 할 때 일반적으로 DML(SELECT, INSERT, UPDATE, DELETE)문은 차단하지 않는 것으로 알고 있다. 하지만 인덱스 재구성할 때 SELECT 문에서 차단이 발생하는 경우가 있다. 아래 실습을 통해서 어떻게 차단이 발생하는지 알아본다.

실습을 진행하기 위해 간단한 시나리오를 만든다. 해당 시나리오는 SQL Server Adventurework2014 데이터베이스를 사용하며 더 큰 데이터 집합을 만들기 위해 아래 링크의 스크립트를 적용하였다.

· Enlarging the AdventureWorks Sample Databases : https://www.sqlskills.com/blogs/jonathan/enlarging-the-adventureworks-sample-databases/

차단이 발생하는것을 재현하기 위해 ALTER INDE…REORGANIZE를 실행하고 UPDATE 구문 및 SELECT 구문을 실행 한다.

세션 1에서 아래 스크립트를 실행 한다.

ALTER INDEX [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]

ON [Sales].[SalesOrderDetailEnlarged] REORGANIZE;

세션2에서 아래 스크립트를 실행한다.

UPDATE STATISTICS [Sales].[SalesOrderDetailEnlarged] WITH FULLSCAN, ALL;

sp_who2를 실행하고 두 프로세스가 모두 실행중인지 확인한다.

SPID	Status	BlkBy	DBName	Command
57	RUNNABLE	.	AdventureWorks2014	DBCC
59	RUNNABLE	.	AdventureWorks2014	UPDATE STATISTIC

세션3에서 아래 스크립트를 실행한다.

SELECT *

FROM [Sales].[SalesOrderDetailEnlarged]

WHERE [SalesOrderId]=1302257;

sp_who2를 실행하면 BlkBy 컬럼 정보를 통해서 블럭킹이 발생한 것을 확인할 수 있다.

SPID	Status	BlkBy	DBName	Command
57	RUNNABLE	.	AdventureWorks2014	DBCC
58	SUSPENDED	59	dventureWorks2014	SELECT
59	RUNNABLE	57	AdventureWorks2014	UPDATE STATISTIC

지금까지는 매우 간단한 시나리오였으며 SELECT가 UPDATE STATISTICS에 의해 차단되고 UPDATE STATISTICS는 INDEX REORG(DBCC)에 의해 차단되었음을 확인할 수 있다. 실제 서비스에서 sp_who2를 실행하면 블로킹 체인의 SPID를 한번에 확인하기 어려울 수 있으므로 아래 스크립트를 사용하면 전체 블럭킹 트리가 생성되고 체인에 포함된 SPID를 확인할 수 있다.

(https://blog.sqlauthority.com/2015/07/07/sql-server-identifying-blocking-chain-using-sql-scripts/ )

SET NOCOUNT ON

SELECT SPID, BLOCKED, REPLACE (REPLACE (T.TEXT, CHAR(10), ' '), CHAR (13), ' ' ) AS BATCH INTO #T

FROM sys.sysprocesses R

CROSS APPLY sys.dm_exec_sql_text(R.SQL_HANDLE) T

WITH BLOCKERS (SPID, BLOCKED, LEVEL, BATCH)

AS (SELECT

SPID,

BLOCKED,

CAST (REPLICATE ('0', 4-LEN (CAST (SPID AS VARCHAR))) + CAST (SPID AS VARCHAR) AS VARCHAR (1000)) AS LEVEL,

BATCH

FROM #T R

WHERE (BLOCKED = 0 OR BLOCKED = SPID)

AND EXISTS (SELECT * FROM #T R2 WHERE R2.BLOCKED = R.SPID AND R2.BLOCKED <> R2.SPID)

UNION ALL

SELECT

R.SPID,

R.BLOCKED,

CAST (BLOCKERS.LEVEL + RIGHT (CAST ((1000 + R.SPID) AS VARCHAR (100)), 4) AS VARCHAR (1000)) AS LEVEL,

R.BATCH

FROM #T AS R

INNER JOIN BLOCKERS ON R.BLOCKED = BLOCKERS.SPID

WHERE R.BLOCKED > 0 AND R.BLOCKED <> R.SPID

)

SELECT

N' ' + REPLICATE (N'| ', LEN (LEVEL)/4 - 1) +

CASE WHEN (LEN(LEVEL)/4 - 1) = 0 THEN 'HEAD - ' ELSE '|------ ' END

+ CAST (SPID AS NVARCHAR (10)) + N' ' + BATCH AS BLOCKING_TREE

FROM BLOCKERS ORDER BY LEVEL ASC

DROP TABLE #T

아래 출력을 보면 ALTER INDEX가 체인의 머리 부분에 있고 UPDATE STATISTICS가 차단되어 있어 간단한 SELECT문을 차단하고 있음을 확인할 수 있다.

BLOCKING_TREE

HEAD - 57 alter index [PK_SalesOrderDetailEnlarged_SalesOrderID_SalesOrderDetailID]...

| |------ 59 UPDATE STATISTICS Sales.SalesOrderDetailEnlarged WITH FULLSCAN, ALL

| | |------ 58 SELECT * FROM [Sales].[SalesOrderDetailEnlarged] WHERE [SalesOrderId]=...

아래 스크립트를 사용하면 각 명령문에서 획득한 잠금을 보다 깊게 어떤 잠금이 잠금 경합을 일으키는지 확인할 수 있다. WHERE절에 SPID를 수정해서 사용한다.

SELECT

tl.request_session_id as spid,tl.resource_type,

tl.resource_subtype,

CASE

WHEN resource_type = 'OBJECT' THEN OBJECT_NAME(tl.resource_associated_entity_id, tl.resource_database_id)

ELSE ''

END AS object,

tl.resource_description,

request_mode,

request_type,

request_status,

wt.blocking_session_id as blocking_spid

FROM sys.dm_tran_locks tl

LEFT JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address

WHERE tl.request_session_id in (57,58,59);

스크립트 실행결과에서 STATS 자원의 잠금이 차단을 야기하는 것을 볼 수 있다. ALTER INDEX가 보유한 Sch-S(스키마 안정성) 잠금은 UPDATE STATISTICS가 획득하려고 시도하는 Sch-M(스키마 수정) 잠금을 차단한다. 이것은 SELECT 쿼리가 획득하려고 하는 Sch-S 잠금을 막는것이다.

AUTO_UPDATE_STATISTICS가 활성화 되어 있고 AUTO_UPATE_STATISTICS_ASYNC가 비활성화된 경우 조회를 실행하기 전에 통계가 갱신될 때 까지 SELECT가 대기하게 되어 위에서 설명한 것과 동일한 상황이 발생할 수 있다.

[참고자료]

https://www.mssqltips.com/sqlservertip/5880/why-is-index-reorganize-and-update-statistics-causing-sql-server-blocking/

2019-01-22 / Sungwook Kang / http://sqlmvp.kr

SQL Server, index reorganize, update statistics, 인덱스 재구성, 통계 업데이트, mssql, DBA

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

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

SQL Server Edition 다운그레이드 후 확인사항 (0)	2019.04.12
AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11
SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상 (0)	2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 (0)	2019.03.26
SQL Server MySQL PostgreSQL 비교 (0)	2019.03.26

SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

2019. 3. 26. 00:15

SQL Server Scala UDF Inline 기능을 사용한 쿼리 성능 향상

· Version : SQL Server 2017 later, Azure SQL Database

SQL Server 2017 및 Azure SQL Database는 런타임 환경에 최적화된 쿼리 처리 개선 기능을 도입했다. 이러한 개선 사항에는 Batch mode Adaptive Joins, Batch mode memory grant feedback, Interleaved execution for multi-statement table valued functions이 포함된다.

· Batch mode Adaptive Joins : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-adaptive-joins

· Batch mode memory grant feedback : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#batch-mode-memory-grant-feedback

· Interleaved execution for multi-statement table valued functions : https://docs.microsoft.com/en-us/sql/relational-databases/performance/adaptive-query-processing?view=sql-server-2017#interleaved-execution-for-multi-statement-table-valued-functions

SQL Server 2019에서는 Intelligent query processing(QP) 제품군에서 몇 가지 새로운 기능으로 쿼리 처리 기능을 더욱 확장하고 있다.

· 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 2019 CTP 2.1에서 사용할 수 있는QP 기능 중 하나인 Scalar T-SQL UDF inline 대해서 살펴본다.

T-SQL UDF는 SQL 쿼리에서 코드 재사용 및 모듈화를 구현한다. 복잡한 비즈니스 규칙과 같은 일부 계산은 명령형 UDF 형식으로 표현하기가 더 쉽다. UDF는 복잡한 SQL 조회를 작성하는 전문 지식 없이 복잡한 논리를 해결하는데 도움이 된다. 하지만 이러한 장점에도 불구하고 열악한 성능문제 때문에 최대한 사용을 자제하거나 금지한다.

Scala UDF inline 기능의 목표는 UDF 실행의 주요 병목인 Scala UDF를 호출하는 쿼리의 성능을 향상 시키는 것이다. Scala UDF가 느렸던 이유는 Scala UDF가 도입되었을 때, 변수 할당, IF-ELSE 분기, 루프 등과 같은 친숙한 구문을 사용하여 사용자가 비즈니스 로직을 표현하였다. 아래 스크립트는 @ckey값이 주어지면 스칼라 UDF를 사용하여 고객이 지정한 모든 주문의 총 가격을 계산한 다음 IF-ELSE 논리를 사용하여 총 가격을 기준으로 카테고리 값을 반환한다.

CREATE OR ALTER FUNCTION dbo.customer_category(@ckey INT)

RETURNS CHAR(10) AS

BEGIN

DECLARE @total_price DECIMAL(18,2);

DECLARE @category CHAR(10);

SELECT @total_price = SUM(O_TOTALPRICE) FROM ORDERS WHERE O_CUSTKEY = @ckey;

IF @total_price < 500000

SET @category = 'REGULAR';

ELSE IF @total_price < 1000000

SET @category = 'GOLD';

ELSE

SET @category = 'PLATINUM';

RETURN @category;

END

UDF는 여러 조회에서 사용할 수 있으며 임계 값을 갱신하거나 새 카테고리를 추가해야하는 경우 UDF에서만 변경해야한다. 아래 스크립트는 위에서 만든 UDF를 호출하는 간단한 쿼리이다.

-- Q1:

SELECT C_NAME, dbo.customer_category(C_CUSTKEY) FROM CUSTOMER;

SQL Server 2017(호환성 수준 140 및 이전 버전) 버전에서는 쿼리의 실행 계획은 아래와 같다.

SQL Server는 CUSTOMER 테이블의 모든 튜플에 대해 UDF를 호출하고 결과를 출력한다. 이 실행계획 매우 비효율적이다. 이러한 쿼리는 다음과 같은 이유로 성능에 좋지 않다.

· 반복 호출 : UDF는 대상 튜플 당 반복적으로 호출된다. 이로 인해 함수 호출로 인한 반복된 컨텍스트 전환의 추가 비용이 발생한다. 특히 본문에서 SQL 조회를 실행하는 UDF는 심각한 영향을 준다.

· 비용 증가 : 최적화 중에는 관계 연산자만 비용이 청구되고 스칼라 연산자는 계산되지 않는다. Scala UDF가 도입되기 전에 다른 스칼라 연산자는 일반적으로 저렴했으며 비용이 필요하지 않았다. 스칼라 작업에 추가된 작은 CPU 비용으로 충분했다.

· Interpreted 실행 : UDF는 명령문 별로 실행되고 명령문의 일괄 처리로 평가 된다. 각 명령문 자체는 컴파일 되고 컴파일된 계획은 캐시 된다. 이 캐싱 전략은 재컴파일이 발생할때 까지 시간을 절약하지만 각 문은 독립적으로 실행된다. 교차 명령문 최적화는 수행되지 않는다.

· Serial 실행 : SQL Server는 UDF를 호출하는 쿼리에서 쿼리 내 병렬 처리를 사용하지 않는다.

새로운 Scalar UDF inline 기능으로 변경된 사항은 스칼라 UDF가 표현식이나 서브쿼리로 변환되어 UDF 연산자의 호출 조회에서 대체된다. 그러면 이러한 표현식과 서브쿼리가 최적화 된다. 결과적으로 쿼리 계획에는 더 이상 사용자 정의 함수 연산자가 없지만 뷰 또는 인라인 TVF와 같은 계획에서 그 효과가 관찰된다. 이를 더 잘 이해하기 위해서 먼저 아래 예제를 살펴 본다.

-- Q2 (Query with no UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (L_EXTENDEDPRICE *(1 - L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

위 쿼리는 광고 항목의 할인 가격 합계를 계산하고 출하 날짜 및 배송 우선 순위별로 그룹화 하여 결과를 표시한다. 표현식 L_EXTENDEDPRICE * (1 - L_DISCOUNT)는 해당 광고 항목의 할인 가격에 대한 수식이다. 할인된 가격을 계산해야하는 곳이면 어디서든 사용할 수 있도록 계산 함수를 만드는 것이 합리적이다.

-- Scalar UDF to encapsulate the computation of discounted price

CREATE FUNCTION dbo.discount_price(@price DECIMAL(12,2), @discount DECIMAL(12,2))

RETURNS DECIMAL (12,2) AS

BEGIN

RETURN @price * (1 - @discount);

END

이제 다음과 같이 쿼리 Q2를 수정하여 UDF를 사용할 수 있다.

-- Q3 (Query with UDF):

SELECT L_SHIPDATE, O_SHIPPRIORITY, SUM (dbo.discount_price(L_EXTENDEDPRICE, L_DISCOUNT))

FROM LINEITEM INNER JOIN ORDERS ON O_ORDERKEY = L_ORDERKEY

GROUP BY L_SHIPDATE, O_SHIPPRIORITY ORDER BY L_SHIPDATE

아래 표는 위 3개의 쿼리를 실행한 결과이다. 앞에서 설명한 이유 때문에 Q3는 Q2에 비해 성능이 좋지 않다. 이제 Scala UDF inline을 통해 SQL Server는 스칼라 표현식을 쿼리로 직접 대체하여 UDF 성능의 한계를 극복한다.

[2CPU (12 core), 96GB RAM, SSD, TPC-H 10GB CCI 데이터 사용]

Query	Q2(UDF 없음)	인라인 업이 Q3	인라인이 포함된 Q3
Execution Time	1.6 second	29 minute 11 second	1.6 second

알 수있듯이 인라인 없이 Q3는 Q2에 비해 속도가 매우 느리다. 그러나 Scala UDF inline을 사용하면 Q3의 성능은 거의 오버헤드 없이 거의 Q2와 동일하다. 쿼리성능을 저하시키지 않으면서 UDF의 모든 이점을 누릴 수 있다. 또한 쿼리나 UDF에 수정이 없었음을 확인할 수 있다. Scala UDF inline을 통해 SQL Server는 다중문 UDF도 인라인 할 수 있다. 위에서 주어진 dbo.customer_category 함수와 Q1함수를 살펴보고 이것이 어떻게 동작하는지 살펴보자. 쿼리 Q1의 경우 UDF가 인라인된 쿼리 계획은 아래와 같다.

위의 계획에서 얻은 몇 가지 주요 관찰 내용은 아래와 같다.

1. SQL Server는 CUSTOMER와 ORDERS 사이의 암시적 조인을 유추했으며 Join 연산자를 통해 이를 명시적으로 만들었다.

2. SQL Server는 ORDERS에 대한 암시적 GROUP BY O_CUSTKEY를 유추했으며 이를 구현하기 위해 Index Spool 및 Stream Aggregate를 사용했다.

3. SQL Server는 모든 연산자에서 병렬처리를 사용한다.

UDF의 논리 복잡성에 따라 결과 쿼리 계획이 더 커지고 복잡해 질 수도 있다. 알 수 있듯이 UDF 내부의 작업은 이제 더 이상 블랙 박스가 아니므로 쿼리 최적화 프로그램은 비용을 절감하고 이러한 작업을 최적화 할 수 있다. 또한 UDF가 더 이상 계획에 없으므로 반복 UDF 호출은 함수 호출 오버헤드를 회피하는 계획으로 대체된다.

Scala UDF inline의 장점은 성능 오버헤드에 대한 걱정없이 사용자가 Scala UDF를 사용할 수 있다. 이로써 사용자는 모듈화되고 재사용가능한 응용프로그램을 구축 할 수 있다. UDF를 사용한 쿼리에 대해 집합 지향, 병렬 계획을 수행할 수 있을뿐만 아니라 기능은 또 다른 장점이 있다. Scala UDF는 더 이상 interpreted 되지 않으므로 (예 : 명령문마다 실행됨) 데드코드 제거, 상수 폴딩 및 상수 전파와 같은 최적화가 가능하다. 이러한 기술은 UDF에 따라 더 간단하고 효율적인 쿼리 계획으로 이어질 수 있다.

Scala UDF inline 가능한 범위는 아래 링크를 참고 한다.

· Inlineable Scalar UDFs requirements : https://docs.microsoft.com/en-us/sql/relational-databases/user-defined-functions/scalar-udf-inlining?view=sqlallproducts-allversions#inlineable-scalar-udfs-requirements

Scala UDF inline이 가능한지 여부는 sys.sql_modules 카탈로그 뷰에서 is_inlineable라는 속성이 있다. 값 1은 인라인이 가능함을 나타내고 0은 그렇지 않다. 이 속성값 정의에 의해 인라인 가능하기 때문에 인라인 테이블 반환 함수의 값은 1이다.

Scala UDF inline은 UDF 실행이 쿼리의 주요 병목일때 가장 유용하다. 병목 현상이 다른곳에 있다면 이점이 없을 수도 있다. 인라인은 CREATE /ALTER FUNCTION문에서 INLINE = OFF 옵션을 사용하여 UDF에 대해 해제할 수 있다. Scala UDF inline을 테스트하려면 기본적으로 데이터베이스 호환성 150을 사용해야한다. SQL Server 2019 CTP 2.1에서 Scala UDF inline을 사용하려면 쿼리를 실행할 때 연결된 데이터베이스에 대해 데이터베이스 호환성 수준 150을 활성화 한다.

USE [master];

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150;

[참고자료]

https://blogs.msdn.microsoft.com/sqlserverstorageengine/2018/11/07/introducing-scalar-udf-inlining/

2018-11-12 / Sungwook Kang / http://sqlmvp.kr

SQL Server, MSSQL, UDF, Scala UDF Inline, 스칼라 UDF 인라인

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

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

AlwaysOn 구성환경에서 Server Role 체크 후 Job Agent 실행 중지하기 (0)	2019.04.11
인덱스 재구성과 통계 업데이트시 발생하는 SQL Server Block (2)	2019.03.26
SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기 (0)	2019.03.26
SQL Server MySQL PostgreSQL 비교 (0)	2019.03.26
SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout (0)	2019.03.26

SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

2019. 3. 26. 00:13

SQL Server 가용성 그룹에 데이터베이스 자동으로 추가하기

· Version : SQL Server

SQL Server AlwaysOn Availability Group으로 데이터베이스가 구성된 경우 해당 데이터베이스는 복원할 수 없다. 먼저 데이터베이스를 복원하기 위해서는 가용성 그룹의 SQL Server 데이터베이스를 제거해야 한다. 이번 포스트에서는 SQL Server Job Agent를 사용하여 AlwaysOn 설정이 자동으로 구성되록 하는 방법을 살펴 본다. 각 단계별 스크립트를 Job Agent에 등록하여 사용할 수 있다.

1. Primary 서버의 AlwaysOn 가용성 그룹에서 데이터베이스 제거