DB Library

현재 max worker threads 카운트 확인 및 수정

USE AdventureWorks2012 ;  
GO  
EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO

-- 정보 확인
EXEC sp_configure 'max worker threads'

-- max worker threads를 900으로 변경
EXEC sp_configure 'max worker threads', 900 ;  
GO  
RECONFIGURE;  
GO

CPU Core별 사용 가능 카운트

참고

https://docs.microsoft.com/ko-kr/sql/database-engine/configure-windows/configure-the-max-worker-threads-server-configuration-option?view=sql-server-ver15

-- MSSQL Error Log가 너무 많이 쌓였을때 유용함
-- 현재의 오류 로그 파일을 닫고 서버를 다시 시작하는 것처럼 오류 로그 확장 번호를 순환시킵니다.
-- 새 오류 로그는 버전, 저작권에 관한 정보 및 새 로그가 작성되었음을 표시하는 행을 포함합니다.

sp_cycle_errorlog  

참고 자료

https://docs.microsoft.com/ko-kr/sql/relational-databases/system-stored-procedures/sp-cycle-errorlog-transact-sql?view=sql-server-2017 

데이터베이스 단위로 권한 부여

--Example 
-- DB 명 : TEST_DB
-- Account : test_acc


use [TEST_DB]
GO
-- 권한 부여
GRANT SELECT TO [test_acc]
GRANT ALTER TO [test_acc]
GRANT DELETE TO [test_acc]
GRANT UPDATE TO [test_acc]
GRANT INSERT TO [test_acc]
GRANT CREATE TABLE TO [test_acc]
GO

-- 권한 제거
DENY UPDATE TO [test_acc]
DENY DELETE TO [test_acc]

스키마 단위로 권한 부여

--Example 
-- DB 명 : TEST_DB
-- 스키마 : dbo
-- Account : test_acc


USE [TEST_DB]
GO

CREATE USER [test_acc] FOR LOGIN [test_acc] WITH DEFAULT_SCHEMA=[dbo]

GRANT CONNECT TO test_acc AS [dbo]
GRANT SELECT TO test_acc AS [dbo]
GRANT DELETE TO test_acc AS [dbo]
GRANT UPDATE TO test_acc AS [dbo]
GRANT EXECUTE TO test_acc AS [dbo]
GRANT VIEW DEFINITION TO test_acc AS [dbo]
GO

지정 SP에 권한 부여

--Example
--DB 명 : TEST_DB
-- SP명 : sp_TEST_update
-- Account : Test_Acc

USE [TEST_DB]
GO

grant execute on sp_TEST_update to Test_Acc 
GO

일부 DB만 DB 리스트에 보여지도록

-- Database 소유자를 제외하고 DB 리스트가 보이지 않게 하는 형식

--Example
-- DB 명 : TEST_DB
-- Account :
---- TEST_Service : Database 리스트에 'TEST_DB' DB 확인 불가
---- TEST_Admin : Database 리스트에 'TEST_DB' DB 확인 가능

USE [master] 
GO

alter authorization on database::TEST_DB to TEST_Admin;
deny VIEW ANY DATABASE TO TEST_Service;
GO

-- DB 이관 시 계정 권한 동기화 용도로 사용
-- Database 단위로 실행 필요


--구문
sp_change_users_login [ @Action = ] 'action'   
    [ , [ @UserNamePattern = ] 'user' ]   
    [ , [ @LoginName = ] 'login' ]   
    [ , [ @Password = ] 'password' ]  
[;]  

--현재 사용자와 로그인 간의 매핑에 대한 보고서
EXEC sp_change_users_login 'Report';  

--Create the new login.  
CREATE LOGIN MaryB WITH PASSWORD = '982734snfdHHkjj3';  
GO  
--Map database user MB-Sales to login MaryB.  
USE AdventureWorks2012;  
GO  
EXEC sp_change_users_login 'Update_One', 'MB-Sales', 'MaryB';  
GO 

--계정명이 동일한 경우
EXEC sp_change_users_login 'Update_One', 'MaryB', 'MaryB';  
GO

-- Server Role 추가
EXEC sp_addsrvrolemember  '로그인이름' , '역할이름'
 
-- Database Role 추가
EXEC sp_addrolemember  '역할이름' , '데이터베이스 사용자이름' 

참고 자료

http://egloos.zum.com/totoriver/v/3158687

-- MSSQL Hostname은 캐시되고 있기 때문에, OS상 hostname이 변경되면 수동으로 변경해 주어야함.

-- 확인
select @@servername
select host_name()

-- 변경
exec sp_dropserver '기존 hostname명'
exec sp_addserver '변경된 hostname명', 'local'

-- DB 재시작

-- 데이터 베이스 암호화 제거
ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION OFF;  

-- 암호화 키 삭제
DROP DATABASE ENCRYPTION KEY  

-- 데이터베이스 암호화 확인
SELECT db_name(database_id), encryption_state  
FROM sys.dm_database_encryption_keys;  


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

ALTER DATABASE AdventureWorks2012  
SET ENCRYPTION OFF;  
GO  
/* Wait for decryption operation to complete, look for a   
value of  1 in the query below. */  
SELECT encryption_state  
FROM sys.dm_database_encryption_keys;  
GO  
USE AdventureWorks2012;  
GO  
DROP DATABASE ENCRYPTION KEY;  
GO  

---------------------
ALTER DATABASE AdventureWorksPDW2012  
    SET ENCRYPTION OFF;  
GO  
/* Wait for decryption operation to complete, look for a   
value of  1 in the query below. */  
WITH dek_encryption_state AS   
(  
    SELECT ISNULL(db_map.database_id, dek.database_id) AS database_id, encryption_state  
    FROM sys.dm_pdw_nodes_database_encryption_keys AS dek  
        INNER JOIN sys.pdw_nodes_pdw_physical_databases AS node_db_map  
           ON dek.database_id = node_db_map.database_id AND dek.pdw_node_id = node_db_map.pdw_node_id  
        LEFT JOIN sys.pdw_database_mappings AS db_map  
            ON node_db_map .physical_name = db_map.physical_name  
        INNER JOIN sys.dm_pdw_nodes AS nodes  
            ON nodes.pdw_node_id = dek.pdw_node_id  
    WHERE dek.encryptor_thumbprint <> 0x  
)  
SELECT TOP 1 encryption_state  
       FROM  dek_encryption_state  
       WHERE dek_encryption_state.database_id = DB_ID('AdventureWorksPDW2012 ')  
       ORDER BY (CASE encryption_state WHEN 3 THEN -1 ELSE encryption_state END) DESC;   
GO  
USE AdventureWorksPDW2012;  
GO  
DROP DATABASE ENCRYPTION KEY;  
GO  

참고 자료

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/drop-database-encryption-key-transact-sql?view=sql-server-ver15 

방법

데이터베이스 파일 (MDF, NDF)에 대해 초기화(Zeroing)하지 않고 즉시 생성되도록 하려면 SQL Server 서비스 시작계정에 대해 [볼륨 유지관리 작업 수행] (Perform Volume Maintenance Tasks) 권한이 부여되어 있어야 합니다.
권한을 부여해 준 후 SQL Server 서비스를 재시작해줘야 적용되며, ERRORLOG에서 권한 적용 여부를 확인할 수 있습니다

1. 이터 파일을 생성할 컴퓨터에서 로컬 보안 정책 애플리케이션(secpol.msc)을 엽니다.
2. 왼쪽 창에서 로컬 정책 을 확장한 다음 사용자 권한 할당 을 클릭합니다.
3. 오른쪽 창에서 '볼륨 유지 관리 작업 수행'(Perform volumn maintenance tasks) 을 두 번 클릭합니다.
4. 사용자 또는 그룹 추가 를 클릭하고 SQL Server 서비스를 실행하는 계정(Default: NT SERVICE\MSSQLSERVER)을 추가합니다.
5. 적용 을 클릭한 다음 모든 로컬 보안 정책 대화 상자를 닫습니다.
6. SQL Server 서비스를 다시 시작합니다

확인

SQLServer 재시작 이후 ERRORLOG에서 권한 적용 여부를 확인

sp_readerrorlog
Database Instant File Initialization: 사용. For security and performance considerations see the topic 'Database Instant File Initialization' in SQL Server Books Online. This is an informational message only. No user action is required.

참고 자료

[https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/database-instant-file-initialization?view=sql-server-ver15\]

방법

USE master;
GO
ALTER DATABASE MyTestDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE MyTestDatabase MODIFY NAME = MyTestDatabaseCopy
GO 
ALTER DATABASE MyTestDatabaseCopy SET MULTI_USER 
GO

참고 자료

[https://docs.microsoft.com/ko-kr/sql/relational-databases/databases/rename-a-database?view=sql-server-2017]

구성 시 주의점

• Windows Cluster 용도 VIP 1ea, 리스너 용도 VIP 1 ea 필요함
• 구성 장비의 IP는 Static IP로 설정
  • DHCP로 IP를 할당 받을 경우 Cluster 및 리스너 VIP를 설정 할 수 없음
• 이중화 구성 장비 간 아래 PORT의 방화벽 오픈이 필요 ( 오픈해야 하는 PORT가 많으므로 보안 이슈 확인 후 장비 간 Any 오픈 )
  • MSSQL 실행 PORT : 1433(기본포트)
  • 클러스터 서비스 : 3343(TCP, UDP)
  • RPC : 135(TCP)
  • 클러스터 어드민 : 137(UDP)
  • 랜덤 포트 : 1024 ~ 65535, 49152 ~ 65535
• 원할한 Failover를 위해 쿼럼 서버를 추가
  • 장비가 3 ea 이상인 경우도 쿼럼 서버를 추가하는것이 안전함.
• Active와 Standby의 DB 데이터 저장 구조(mdf, ldf Path)가 같아야함
• EnterPrise 버전만 여러개의 DB를 AlwaysOn 구성이 가능
  • Standard 버전은 하나의 DB만 AlwaysOn 구성이 가능
• 안정적인 AlwaysOn 운영을 위해 클러스터 장비 간 Heartbeat Network 구성이 필요
• Remote Registry Service 사용
• 로컬 보안 설정의 ‘네트워크에서 이 컴퓨터로 엑세스’ 속성에 “Authenticated Users”가 포함되어야 함)
• 클러스터 구성 시 스토리지를 공유해서 사용하지 않도록 설정
  • 스토리지 공유 : MSCS(Microsoft Cluster Service)방식 이중화
  • 스토리지 공유 하지 않음 : AlwaysOn 방식 이중화
• AlwaysOn 구성 후, Alwayon Group의 OwnerNode와 클러스터 그룹의 OwnerNode 그룹이 일치 해야함
  • powerShell의 ‘get-Cluster-Group’ 명령어로 확인 가능
  • Node 이동은 Failover Clustering의 ‘추가 작업’ -> 코어 클러스터 리소스 이동 -> 노드 선택 메뉴로 변경 가능
• 일반적으로 AlwaysOn 구성 시, 처리 속도는 20% ~ 30% 느려질 수 있음
  • 처리 지연이 발생하는 경우 mdf, ldf 모두 고성능 SSD로 변경하거나, 동기화 간 암호화 로직을 제거하는 방법이 있음.

참고 자료

[https://docs.entcloud.swisscom.com/guide/managed-services/managed-os/technical-description/windows-server-failover-cluster/#network]