DB Library

MSSQL  패치 버전 선정 기준

2017 이상의 버전은 Service Packs 없이 CU로만 업데이트가 진행됨

CU 패치의 경우 대부분 버그 및 보안 관련 내용이 진행됨

CU 패치 이후 버그가 확인되면 "WithDrawn" 문구가 포함되며 대부분 한달 이내로 수정 버전이 패치됨.

2019 

CU18 버전이 최신, 최신 버전 패치일이 얼마 되지 않기 때문에 CU17을 선정.

링크 : https://sqlserverbuilds.blogspot.com/#sql2019x

CU 17 다운로드

Recommended를 선택 시, 가장 최신 버전(현 시점 CU18)이 선택되기 때문에 두번째 메뉴를 클릭

CU 17 다운로드 링크 : https://support.microsoft.com/en-us/topic/kb5016394-cumulative-update-17-for-sql-server-2019-3033f654-b09d-41aa-8e49-e9d0c353c5f7#obtain

2016

Service Pack 3를 패치 하거나, Service Pack 2 및 CU 17 버전을 패치

CU 17 버전의 경우 확인 시점 기준 약 1년간 이슈 없이 서비스됨.

링크 : https://sqlserverbuilds.blogspot.com/#sql2016x

CU 17 다운로드

Recommended를 선택 시, 가장 최신 버전(현 시점 CU18)이 선택되기 때문에 두번째 메뉴를 클릭

CU 17 다운로드 링크 : https://support.microsoft.com/en-us/topic/kb5001092-cumulative-update-17-for-sql-server-2016-sp2-5876a4d6-59ac-484a-93dc-4be456cd87d1

현상

MSSQL AlwaysOn 설정 이후 auto failover 혹은 manually failover 가 실패

에러 로그

“Failed to perform a manual failover of the availability group“…..“Failed to bring availability group <name> online. The operation timed out”……“41131”

해결

Event Viewer UI를 통한 확인

PowerShell을 사용하여 Clustger Error Log 확인

“The use does not have permission to perform this action. (297)” 문구 확인

권한 부여

참고

https://geekshangout.com/unable-to-manually-failover-a-sql-availability-group/

현상 

Quorum 상태가 Online 으로 수정이 되지 않고 Fail로 유지

원인

쿼럼 서버 장비가 다운되거나 통신이 되지 않는 경우 발생 할 수 있음

수정

Fail 상태로 유지되는 쿼럼의 경우 일반적인 방법의 삭제(우 클릭 후 Remove)가 되지 않는 경우가 많음

이 경우 쿼럼 설정 창에서 쿼럼을 제거하고 다시 구성 진행

Failover Cluster Manager -> More Actions -> Configure Cluster Quorum Settings ...

3번째 매뉴의 'Select Quorum Witness'에서 'Do not configure a quorum witness'를 선택하여 쿼럼 삭제 후 재구성

MSSQL AlwaysOn 환경에서는 Job Agent에 등록시, Active에서만 Job Schedule이 실행되어야 한다.

dmv 정보를 활용하여 Active 환경을 체크 후 Job을 실행 하도록 설정 필요

설정

예외 사항

Secondary 환경에서 Job을 등록하였을때 아래와 같이 실행이 되지 않는 경우가  발생

아래는 Job Schedule의 로그 화면

Secondary 환경에서 동기화 중인 DB 선택 시, 아래와 같은 에러 발생

해결

Active 장비의 AlwaysON 속성에 들어가서 '읽을 수 있는 보조(E)' 항목을 '예'로 변경

서버가 잠겨 있거나 비정상적인 상태로 SQL Server에 연결이 되지 않는 경우

DAC 접근을 위한 설정

remote admin connection 옵션을 활성화

SSMS -> DB 인스턴스 우클릭 -> 패싯(A) 클릭

패싯 속성(P) -> 'RemoteDacEnabled' Ture 설정

스크립트 적용

DAC 접근 방법

cmd창에서 sqlcmd 명령어로 접속

sqlcmd 설명 : https://docs.microsoft.com/ko-kr/previous-versions/sql/sql-server-2008-r2/ms162773(v=sql.105)?redirectedfrom=MSDN 

참고
https://sungwookkang.com/368

※현상

DB를 single_user에서 multi_user로 변경하는 도중 프로세스 Deadlock 발생

원인

DB 커넥션이 존재하는 상황에서, 사용자 세션이 master를 바라보는 도중 DB  커넥션을 모두 off시켜,

사용자 세션이 DB를 바라보지 않아 system 내부에서 DB의 소유자를 찾지 못하는 상태

deadlock이 발생하는 세션을 끊으려 하여도 사용자 세션이 아닌 시스템 세션이라 kill이 불가한 상태 

해결방안

1번으로 작업해야 하지만, 이미 deadlock이 발생한 상태라면 2번으로 해결

1. 작업하려는 DB를 소유하고 single_user로 변경

use [작업DB]
 
alter database [작업DB] set single_user WITH ROLLBACK IMMEDIATE-- immediate 커넥션 한번에 off
go
  
-- 작업 할 쿼리 내역
  
alter database [작업DB] set multi_user
go

2. Deadlock 발생 시, DB 재기동 후 아래 쿼리 실행

SET DEADLOCK_PRIORITY HIGH -- 교착상태 최상위 레벨로 격상
GO
 
ALTER DATABASE [작업DB] SET MULTI_USER WITH ROLLBACK IMMEDIATE -- 작업 DB multi_user로 변경
GO
  
-- https://myadventuresincoding.wordpress.com/2014/03/06/sql-server-alter-database-in-single-user-mode-to-multi-user-mode/

현상

동일한 OS 버전 / 동일한 MSSQL 버전 / 동일한 구조 / 동일한 요청량 

동일한 조건이지만 트랜잭션 로그 및 로그 백업 파일의 사이즈가 약 8배 차이가 나는 경우

원인

Physical Disk Sector 크기의 차이

기존에는 Physical Disk Sector 사이즈를 512 Byte를 사용하였지만, 최근 H/W 스펙이 올라가며 4096 Byte를 디폴트로 설정되어 구성되는 경우가 있음.

확인

명령프롬포트(CMD)를 관리자모드로 실행하여 아래 명령어 실행

fsutil fsinfo ntfsinfo yourLogDrive:
fsutil fsinfo sectorinfo yourLogDrive:

------------- 실행 예

C:\WINDOWS\system32>fsutil fsinfo ntfsinfo D:
NTFS 볼륨 일련 번호 :        0x???????????????
NTFS 버전      :                3.1
LFS 버전       :                2.0
총 섹터     :                1,953,519,615  (931.5 GB)
총 클러스터    :                  244,189,951  (931.5 GB)
사용 가능한 클러스터     :                  217,448,760  (829.5 GB)
예약된 총 클러스터 :                  5,087  ( 19.9 MB)
저장소 예약을 위해 예약됨 :                 0  (  0.0 KB)
섹터당 바이트  :                512
실제 섹터당 바이트 :        4096
클러스터당 바이트 :                4096


C:\WINDOWS\system32>fsutil fsinfo sectorinfo D:
LogicalBytesPerSector:                                 512
PhysicalBytesPerSectorForAtomicity:                    4096
PhysicalBytesPerSectorForPerformance:                  4096
FileSystemEffectivePhysicalBytesPerSectorForAtomicity: 4096
장치 맞춤:                                      정렬됨(0x000)
장치 파티션 맞춤:                         정렬됨(0x000)
일반 검색 수행
자르기 지원 안 됨
DAX 지원 안 함
씬 프로비저닝되지 않음

C:\WINDOWS\system32>

PhysicalBytesPerSector와 Transaction Log I/O간의 관계

SQL Server transaction log 파일은 physical sector-aligned boundary 로 생성되며, sector-aligned sizes 와 sector-aligned boundaries 로 데이터가 쓰여집니다.
즉, PhysicalBytesPerSector 크기에 따라 IO 단위가 달라지며, 이 값이 512 bytes 인 경우 트랜잭션 로그는 512 Bytes – 60KB 단위로 저장될 수 있습니다.
또한 PhysicalBytesPerSector 크기가 4KB일 경우 4 KB – 60KB 단위로 저장될 수 있습니다.

현상

powershell의 invoke-sqlcmd를 사용하여 DB 쿼리를 실행하는 도중 강제 종료

에러 내용

invoke-sqlcmd : Execution Timeout Expired.  The timeout period elapsed prior to completion of the operation or the
server is not responding.

원인

쿼리 실행 timeout 설정을 하지 않아, 쿼리 실행 시간이 Default 30초를 초과하여 종료되는 현상

해소

-Querytimeout 옵션으로 쿼리 실행 가능 시간을 설정

-- Query Timeout 제한 없음 : 0
Invoke-Sqlcmd -ServerInstance "$serverInstance" -Querytimeout 0 -Query "Select * from ...."

참고

https://stackoverflow.com/questions/52545013/getting-execution-timeout-expired-running-sql-server-backup-through-invoke-sql/56991850

정보

Core별 MSSQL 'max worker threads' Default 값 설정

Core 기준 : 논리 코어

'max worker threads' 설정 값 확인

• SELECT max_workers_count FROM sys.dm_os_sys_info
• sp_configure 'max worker threads'

기준

예시

CPU : 물리 코어 24 ( 논리 코어 48 )

512 + (( 48 - 4 ) * 16 ) = 1,216

 참고 : https://blog.sqlauthority.com/2018/06/15/sql-server-optimal-value-max-worker-threads/

mssql 라이선스를 standard로 운영 도중 enterprise로 올리고 싶은 경우 가능

enterprise에서 standard로 다운그레이드는 불가능

아래 setup.exe 파일은 enterprise의 실행 파일( enterprise 설치 파일이 로컬 디스크에 있어야함 )

아래 예의 경우 SQL2016SP1_ENT_ENG 폴더에 mssql 설치 파일이 있음

-- InstanceName : 설치된 SQLServer 인스턴스 정보 ( Default : MSSQLSERVER )
-- PID : MSSQL 제품 키 ( 제품키가 포함된 버전은 생략 )
setup.exe /q /ACTION=editionupgrade /InstanceName=MSSQLSERVER /PID=<appropriatePid> /IAcceptSQLServerLicenseTerms

참고

https://laigo.tistory.com/786