불안하면 공부

[SQL Server 서비스 팩 설치 시 유의 사항]

1) SQL Server 하나의 인스턴스에 속하는 모든 기능은 동시 업데이트 해야한다. 데이터베이스 엔진 업데이트 시 Analysis Services, Reporting Services 구성요소가 동일한 SQL Server 인스턴스의 일부로 설치되어 있다면 함께 업데이트가 필요하다.

관리도구, SQL Data Tools(SSDT) 및 SQL Server Integration Servies

SQL Server 업데이트 설치 준비

1) SQL Server 시스템 데이터 베이스 백업 

- SQL Server 업데이트 전 master, msdb, model 데이터베이스를 백업한다. 업데이트 시 데이터베이스가 변경되어 이전 버전의 SQL Server와 호환되지 않을 수 있으며, 업데이트를 적용하지 않은채 SQL Server를 재설치하고 이전 데이터베이스를 계속 사용하려면 시스템 데이터 베이스 백업이 필요하다.

또한, 사용자 데이터베이스 백업도 함께 진행하는 것이 좋다.

2) Analysis Services 데이터 베이스, 구성 파일 및 리포지토리 백업 

a. Anlaysis Services 데이터베이스는, C:\Program Files\Microsoft SQL Server\MSAS11.<InstanceId>\OLAP\Data에 설치되어 있다. 

> 현 시스템에 설치 되어 있지 않으면 무관

b. msmdrsv.ini 구성파일의 Analysis Service 구성 설정. 

c. 시스템 데이터베이스에 사용 가능한 공간이 충분한지 확인 

master 및 msdb 시스템 데이터베이스에 대해 자동 증가 옵션을 선택하지 않은 경우 이러한 데이터베이스느 각각에 최소 500KB 이상의 여유 공간이 있어야 한다. 데이터베이스에 공간이 충분하지 확인하려면, master 및 msdb 데이터베이스에 sp_spaceused 시스템 저장 프로시저를 실행한다.

각 데이터베이스의 할당되지 않은 공간이 500KB보다 적은 경우 데이터베이스의 크기를 늘려야 한다.

d. 서비스 및 응용 프로그램 정지

시스템이 다시 시작되지 않도록 하기 위해 SQL Server 업데이트 설치 전 업그레이드 중인 인스턴스에 연결된 모든 응용 프로그램 및 서비스를 중지한다.  SSMS, SQL Server Data Tools 등이 포함된다. 

e. 업데이트 설치 후 컴퓨터를 다시 시작해야 할 필요가 없도록 설치 프로그램에 파일을 잠그는 프로세스 목록이 표시된다. 업데이트 설치 프로그램이 설치 중에 특정 서비스를 종료해야 할 경우 설치를 마친 후 해당 서비스를 재 시작한다.

f. 설치 중에 잠겨 있는 파일이 발견될 경우 설치가 끝난 후 컴퓨터를 다시 시작해야 할 수도 있다. 필요의 경우 다시 시작하라는 메시지가 표시된다.

[SQL Server 업데이트 설치]

1) SQL Server 업데이트 시작

누적 업데이트 패키지(CU): <SQLServer2012>-KBxxxxxx-PPP.exe

서비스 팩 패키지(PCU): <SQLServer2012><SPx> -KBxxxxxx-PPP-LLL.exe

> x는 서비스 팩 번호를 나타낸다.

> PPP는 특정 플랫폼을 나타낸다.

> LLL은 SQL Sever 언어의 문자 약어이면 영어는 ENU, 국문은 KOR 이다.

2) 설치 마법사를 통해 설치 진행

3) 성공적인 설치 확인

> SQL Server 서비스가 온라인 상태인지 확인

> @@version 쿼리에서 반환된 버전 정보를 확인

SQL Server Error Log

오류 로그는 서비스가 시작할 때부터 서비스가 중지될 때까지 계속 메시지를 기록한다. 로그는 심각도 수준이 19 ~ 25의 값을 가진 모든 오류를 기록하고, 모니터링 시 오류 로그에서 심각도 수준 19 ~ 25 사이의 값을 가진 오류는 반드시 체크해야 한다. 해당 심각도 수준의 오류는 트랜잭션을 실패하고, 응용 프로그램이 운영에 문제가 된다. 심각도 수준이 20 ~ 25 오류는 치명적이며, 클라이언트 연결은 오류 메시지를 받고 종료된다. 

SQL Server Error Log 포함된 사항

SQL Server Error Log 읽기

sp_readerrorlog 실행 

SQL Server Error Log 관리하기

테이블로 관리하기

A. SQL Server Error Log 및 Agent Log 관리용 테이블 생성

B. 데이터 저장하기

C. 데이터 확인 

테이블 생성 후 주기적으로 테이블에 데이터를 저장하여 관리하면, 로그를 빠르게 볼 수 있으며 오류 로그 크기가 커져서 디스크 공간을 크게 차지

하는 것을 예방 할 수 있다.

참고: https://blogs.msdn.microsoft.com/repltalk/2011/01/05/how-to-archive-your-sql-errorlogs-into-a-table/

※데이터베이스 엔진 오류 심각도

SQL Server Version & Service Packs List

SQL Server 2016 설치 시   기능 선택 부가 설명

※첨부 파일 참고

SQL Server 설치 기능 선택 설명.docx

- SQL Server 2016 구성요소

1. 인스턴스 기능: SQL Server 인스턴스에 대한 각 인스턴스 기능의 구성과 작업은 다른 SQL Server 인스턴스와 격리된다. 

SQL Server 인스턴스는 동일한컴퓨터에서 함께 작동될 수 있다.

(1)   데이터베이스 엔진 서비스 (Database Engine Service)

-       데이터 저장, 처리 및 보안 설정을 위한 핵심 서비스이다. 데이터베이스 엔진은 데이터 소비형 응용프로그램에 대한 요구사항을 충족하기 위해 액세스 제어 및 빠른 트랜잭션 처리를 제공한다.

-       OLTP 또는 OLAP 데이터에 사용할 관계형 데이터베이스를 만들 수 있고, 데이터 저장할 테이블과 데이터 보기, 관리 및 보안을 위한 인덱스, 뷰, 저장 프로시저 등의 데이터베이스 개체를 만드는 작업이 포함된다.

-       SSMS를 이용하여 데이터베이스 개체를 관리하고, SQL Server 프로파일러를 사용하여 서버 이벤트를 캡처할 수 있다.

-       SQL Server 복제: 특정 데이터베이스에서 다른 데이터베이스로 데이터 및 데이터베이스 개체를 복사 및 배포하고 데이터베이스가 일치하도록 동기화하는 기술 집합을 포함한다. 복제를 사용하여 LAN과 WAN, 전화 접속 연결, 무선 연결, 인터넷을 통해 데이터를 다른 위치와 원격 및 모바일로 사용자에게 배포 가능하다.

-       데이터베이스 내 R Service: 표준 T-SQL문을 사용하여 R 언어와 통합할 수 있는 고급 분석 확장을 포함한다.

-       검색을 위한 전체 텍스트 및 의미 체계 검색: 빠른 텍스트 검색을 위한 전체 텍스트 검색 뿐만 아니라 태그와 같은 핵심 단어에 대한 의미 체계 검색 및 SQL Server에 저장된 콘텐츠에 대한 유사성 검사를 지원하는 검색 엔진을 포함한다.

참고 사이트: https://docs.microsoft.com/ko-kr/sql/relational-databases/search/full-text-search

-       Data Quality Service: DQS 기술자료는 데이터 관리자나 IT 전문가가 데이터 정리 및 데이터 일치를 통해 데이터 품질 향상을 위해 만든 메타데이터 리포지토리이다. DQS 기술 자료 관리에는 컴퓨터 기반은 물론 대화형으로도 기술 자료를 만들고 관리하는데 사용되는 프로세스가 포함된다.

참고 사이트: https://docs.microsoft.com/ko-kr/sql/data-quality-services/data-quality-services-concepts

-       외부 데이터용 PolyBase: 표준 T-SQL문을 사용하여 Hadoop 비 관계형 데이터 및 SQL Server 관계형 데이터 간의 쿼리를 완전히 통합하는 PolyBase 기술을 포함한다.

(2)   Analysis Service

-        Analysis Services와 OLAP(온라인 분석 처리) 및 데이터 마이닝을 지원하는데 사용되는 도구를 포함한다. 데이터베이스 엔진 서비스와 Analysis Services 만 클러스터링 가능하다.

-       사용자가 관계형 데이터베이스와 같은 다른 데이터 원본으로부터 집계된 데이터를 포함하는 다차원 구조를 디자인, 생성 및 관리할 수 있도록 OLAP를 지원한다. 데이터 마이닝 응용프로그램의 경우 Analysis Services를 통해 사용자가 다양한 산업 표준 데이터 마이닝 알고리즘을 사용하여 다른 데이터 원본으로부터 생성되는 데이터 마이닝 모델을 디자인, 생성 및 시각화 할 수 있다.

(3)   Reporting Service

-       관계형 및 다차원 데이터 원본에서 보고하는 포괄적인 데이터를 제공하는 서버 기반 보고 플랫폼이다. Reporting Services에는 처리 구성 요소, 보고서를 만들고 관리하는데 사용할 수 있는 전체 도구 집합, 개발자가 데이터 및 보고서를 처리를 사용자 지정 응용 프로그램으로 통합하거나 확장할 수 있게 해주는 API 등이 포함되어 있다.

-       사용자가 보고서를 열 때 예약된 간격으로 또는 요청 시 데이터를 검색하는 대화형, 테이블 형식 또는 자유 형식 보고서를 만들 수 있다.

2.     공유 기능

: 각 공유 기능은 정의된 범위 내에 한번 설치되어 해당 범위 내에서 작동한다. 정의된 범위는 컴퓨터의 모든 SQL Server 버전을 포함하거나, 특정 주 버전의 SQL Server로 격리되거나 하나 이상의 부 버전으로 격리될 수 있다.

(1)   Reporting Service-SharePoint: 전자 메일, 여러 파일 형식 및 대화형 웹 기반 형식의 보고서를 작성, 관리 및 배달할 수 있는 서버 기반 응용 프로그램인 Reporting Service를 SharePoint 통합 모드에서는 보고서 서버를 SharePoint 제품과 통합한다. 보고서 보기와 보고서 관리가 SharePoint 사이트 및 라이브러리와 통합되며, Reporting Services를 클러스터링 할 수 없다.

(2)   SharePoint 제품용 Reporting Services 추가 기능: 관리 및 사용자 인터페이스 구성 요소를 포함하여 SharePoint 통합 모드에서는 SharePoint 제품을 SSRS 보고서 서버와 통합한다. 추가 기능은 SharePoint 제품을 실행하고 있는 서버에서만 설치 해야한다.

(3)   데이터 품질 클라이언트: 데이터 품질 클라이언트 개체를 포함한다.

(4)   클라이언트 도구 연결: 클라이언트와 서버 간 통신을 위한 구성 요소를 포함한다.

(5)   Integration Services: 데이터 저장소 간의 데이터를 이동, 통합 및 변환 할 수 있는 디자이너, 런타임 및 유틸리티를 포함한다.

(6)   클라이언트 도구 이전 버전과의 호환성: 클라이언트 도구 이전 버전과의 호환성

(7)   클라이언트 SDK: 프로그래머용 리소스가 들어 있는 소프트웨어 개발 키트를 포함한다.

(8)   설명서 구성요소: SQL Server 2016에 대한 설명서를 보고 관리하는데 사용하는 구성요소만 설치한다. 기본적으로 온라인 라이브러리를 사용하며, SQL Server 설치한 후 도움말 라이브러리 관리자 구성요소를 사용하여 로컬 컴퓨터로 설명서를 다운로드 할 수 있다.

(9)   Distributed Replay Controller:  SQL Server Distributed Replay Controller라는 Windows 서비스를 실행하는 컴퓨터. Distributed Replay Controller는 Distributed Replay Client의 동작을 조정한다.. 각 Distributed Replay 환경에는 컨트롤러 인스턴스가 하나만 있을 수 있다.

(10)  Distributes Relay Client: SQL Server Distributed Replay Client라는 Windows 서비스를 실행하는 하나 이상의 컴퓨터(실제 또는 가상). Distributed Replay Client 인스턴스가 함께 작동하여 SQL Server 인스턴스에 대한 작업을 시뮬레이션 한다. 각 Distributed Replay 환경에 하나 이상의 클라이언트가 있을 수 있다.

(11)  SQL 클라이언트 연결 SDK: 데이터베이스 응용 프로그램 개발을 위한 SQL Server Native Client (ODBC/OLE DB) SDK를 포함한다.

3.     재배포 가능 기능

: 필요에 따라 오류 및 사용 보고, SQL Server Native Client, MSXML 버전 6.0, Sync Services for ADO.NET 및 SQL Server Browser와 같은 재배포 가능 기능 및 공유 기능이 설치된다.

DB Server Setting

| SQL Server 2016 설치

※설치 시 첨부된 파일 참고

- SQL Server Installation Center

1) SQL Server 2016 Standard Edition iso 파일을 마운트 한다.

2) [설치] 에서 새 SQL Server 독립 실행형 설치 또는 기존 설치에 기능 추가 를 선택한다.

- SQL Server Install Program

3) [제품 키] 탭에서 제품 키 입력 확인 후 [다음] 으로 이동한다.

4) [사용 조건] 탭에서 동의함(A) 체크 후 [다음] 으로 이동한다.

5) [Microsoft Update] 탭에서 업데이트 항목을 검사하나, 인터넷이 연결되지 않은 환경에서 선택하지 않는다.

6) [설치 파일 설치] 탭에서 [다음] 버튼이 활성화 되면 이동한다.

7) [설치 규칙] 탭에서는 규칙의 상태를 보여주며 성공/경고 외 실패 항목이 있으면 확인 후 수정하고 재진행 한다.

  특이 사항 없다면 [다음] 으로 이동한다.

8) [기능 선택] 탭에서 필요한 인스턴스 및 기능을 선택할 수 있다. 기본적으로 데이터 베이스 엔진 서비스를 선택하며, 추가로 Integration Service 를 선택했다.

※기능 선택 시 "SQL Server 2016 설치(2)- 기능 설명" 참고

9) [기능 규칙] 탭에서 규칙과 상태를 확인하고, 실패내역이 없다면 [다음] 을 눌러 이동한다.

10) [인스턴스 구성] 탭에서 SQL Server 인스턴스의 이름 및 인스턴스 ID를 지정할 수 있다. 신규 설치라면 기본 인스턴스를 선택한다.

11) [서버 구성] 탭에서 서비스 별 계정 및 시작 유형을 설정할 수 있다. SQL Server 에이전트가 Default로 "수동"으로 되어 있므로, "자동" 으로 바꿔준다.

또한, 2016 부터는 하단에 본륨 유지 관리 작업 수행 권한 부여를 체크 박스 형태로 제공하여 설치 후 별도의 설정을 하지 않아도 되므로 체크 해주도록 한다. [다음] 으로 이동한다.

12) [데이터베이스 엔진 구성] 탭에서 [서버구성] 에서는 인증 모드를 혼합 모드로 변경하고, 암호 입력란에 암호를 입력한다. 이는 sa 계정의 암호이며 설치 이후 변경 가능하나 가급적 설치 시 설정한다. 또한 하단의 "현재 사용자 추가" 버튼을 클릭하여 현재 Windows 사용자를 추가한다.

[데이터 디렉터리] 에서는 Data, Log, Backup 파일 경로 설정이 필요하다. 기본으로 C 드라이브로 설정되어 있으나, C 드라이브는 운영체제의 사용이 많기 때문에 보통 D 드라이브에 분리한다. 혹은 Data 파일은 D 드라이브, Log 파일은 E 드라이브로 분리하기도 한다.

[Temp DB] 에서는 Temp DB의 데이터 파일과 로그 파일을 분리하여 지정할 수 있다. 위에서 설정한 D\Data /  D\Log 와 동일한 경로로 지정해준다. Temp DB의 데이터 파일은 현재 시스템의 Core 수 만큼 생성한다.

Chapter 08 Knowing Temp DB

Subject: 임시테이블과 테이블 변수

1. 임시 테이블과 테이블 변수 (Temp Tables Vs. Table Variables)

테이블 변수는 로컬 임시 테이블과 유사하다. 테이블 변수는 변수를 선언하여 사용하며, Batch 범위이다. 

차이점 1) 통계

임시 테이블과 테이블 변수의 주된 차이점은 테이블 변수는 통계를 생성하지 않는다는 것이다. 주요한 2개의 결과를 갖는데, 하나는 쿼리 옵티마이저는 테이블 변수는 포함된 데이터를 무시하고 row 수로만 고정된 견적 값을 사용한다. 데이터가 증가하거나 삭제되어도 이 값은 변하지 않게된다.

쿼리 옵티마이저는 하나의 row가 반환되는 계획을 기반하는 반면에 1 million rows가 사실상 실행된다. 테이블 변수에서 row의 수는 옵티마이저에서 항상 한 row로 간주되어 계산된다. 신뢰할만한 통계가 없기 때문에, 더 나은 비용의 계획이 발생하던지 또는 나쁜 실행계획이 사용될 수 있다. 

차이점 2) Indexes

테이블 변수에서는 제약조건을 생서할 수 있지만, 인덱스를 생성할 수는 없다. 즉, PK 또는 유니크 제약조건으로 인덱스를 가질 수 있다는 의미이다.

제약 조건으로 인해 인덱스를 만들고, 통계를 갖을 수 있지만, 인덱스를 쿼리의 컴파일 시 사용할 수 없다. 컴파일 시간이 존재하는 것도 아니고 재컴파일도 발생하지 않기 때문이다. 

차이점 3) 스키마 수정 (Schema Modifications)

스키마 수정은 임시테이블에서만 가능하다. 비록 스키마 수정은이 가능하지만 사용하는 것은 지양해야한다. 테이블 사용시 재 컴파일 이슈가 있기 때문이다. 

주요 차이점 정리표

테이블 변수나 임시 테이블을 사용하는 것은 테스트를 통해서 결정해야하는 것이지만, 기본적으로 임시테이블 사용하는 것이 좋지 않는 결과로 갈 확률이 적다. 

적은 row를 다루기 위해 테이블 변수를 사용하는 경우 임시테이블 보다 빠르지만 수 백개, 수천개의 row가 테이블 변수내에 있다면 performance는 아주 나빠진다. 그렇기 때문에 둘 중 하나를 결정하기 전에 여러개 용량계획을 검토할 필요가 있다.  (so, try and allow for some capacity planning when you make your decision!)

2. 테이블 변수는 메모리 내에 생기지 않는다

테이블 변수는 메모리 내 생성되기 때문에 임시테이블보다 빠르게 수행된다는 잘못된 개념이 있다. 

-> 실제 몇몇 블로그에서 임시테이블과 테이블 변수 차이점 검색 과정해서 테이블 변수는 메모리에 올려서 사용하기 때문에 빠르다고 언급한 곳이 있었다.

DMV 중 sys.dm_db_session_alloc_page_count; 를 통해서 세션의 의한 tempdb 사용 확인 가능하다. DMV 비우기 위해 SQL Server 재시작 하면 DMV 조회 시 0이 반환된다.

1) 임시 테이블이 얼마나 공간을 차지하는지 확인할 수 있다. 하나의 임시테이블을 생성하고 하나의 row을 입력하고 조회하면 session_id, database_id, user_objects_alloc_page_count 를 확인할 수 있다. 즉, 새로 생성된 테이블이 tempdb 내에 하나의 페이지로 위치하고 있음을 알 수 있다.

2) 같은 방법으로 테이블 변수로 생성한 후, DMV를 조회하면 user_objects_alloc_page_count가 +1 됨을 알 수있다. 즉, 테이블 변수는 tempdb 내 또 다른 페이지를 생성하고, 테이블 변수는 메모리 내 생성되는 것이 아님을 알 수 있다.

** 테이블 변수와 임시 테이블 모두 캐시되는 것과 연관 있지만 사실은 서버의 메모리를 제한하지 하지 않거나 특별히 큰 테이블을 사용하지 않는 한 메모리 내에서 작업할 수 있다.

SQL Server 버전 비교표

* SQL Server Enterprise 및 Business Intelligence 버전에만 해당

* 출처: https://www.microsoft.com/ko-kr/sql-server/sql-server-2016-comparison