Data Science Lab

-- runs on primary server

USE master

GO

ALTER AVAILABILITY GROUP [<Availability Group>] REMOVE DATABASE [<Database Name>];

GO

-- runs on primary server

USE master

GO

RESTORE DATABASE [<Database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH REPLACE

GO

-- runs on primary server

USE master

GO

ALTER DATABASE [<Database Name>] SET RECOVERY FULL WITH NO_WAIT

GO

-- runs on primary server

BACKUP DATABASE [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.bak' WITH FORMAT, INIT, COMPRESSION

GO

BACKUP LOG [<database Name>]

TO DISK='<Shared Network Location>\<Database Name>.trn' WITH FORMAT, INIT, COMPRESSION 

GO

-- runs on primary server

USE master

GO

ALTER AVAILABILITY GROUP [<Availability Group>] ADD DATABASE [<Database Name>]; 

GO

-- runs on secondary server

USE master

GO

RESTORE DATABASE [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.bak' WITH FILE=1, REPLACE, NORECOVERY

GO

RESTORE LOG [<database Name>]

FROM DISK='<Shared Network Location>\<Database Name>.trn' WITH FILE=1, REPLACE, NORECOVERY 

GO

-- runs on secondary server

-- Wait for the replica to start communicating

begin try

   declare @conn bit

   declare @count int

   declare @replica_id uniqueidentifier

   declare @group_id uniqueidentifier

   set @conn = 0

   set @count = 30 -- wait for 5 minutes

   if (serverproperty('IsHadrEnabled') = 1)

      and (isnull((select member_state

                   from master.sys.dm_hadr_cluster_members

                   where upper(member_name COLLATE Latin1_General_CI_AS) = upper(cast(serverproperty('ComputerNamePhysicalNetBIOS') as nvarchar(256)) COLLATE Latin1_General_CI_AS)), 0) <> 0)

      and (isnull((select state

                   from master.sys.database_mirroring_endpoints), 1) = 0)

   begin

      select @group_id = ags.group_id

      from master.sys.availability_groups as ags

      where name = N'<Availability Group>'

      select @replica_id = replicas.replica_id

      from master.sys.availability_replicas as replicas

      where upper(replicas.replica_server_name COLLATE Latin1_General_CI_AS) = upper(@@SERVERNAME COLLATE Latin1_General_CI_AS) and group_id = @group_id

      while @conn <> 1 and @count > 0

      begin

         set @conn = isnull((select connected_state from master.sys.dm_hadr_availability_replica_states as states where states.replica_id = @replica_id), 1)

         if @conn = 1

         begin

            -- exit loop when the replica is connected, or if the query cannot find the replica status

            break

         end

         waitfor delay '00:00:10'

         set @count = @count - 1

      end

   end

end try

begin catch

   -- If the wait loop fails, do not stop execution of the alter database statement

end catch

ALTER DATABASE [<Database Name>] SET HADR AVAILABILITY GROUP = [<Availability Group>];

GO

Primary

Secondary

1

가용성 그룹에서 데이터베이스 제거

2

데이터베이스 복원

3

데이터베이스 복구 모델을 FULL로 설정

4

가용성 그룹에 데이터베이스 추가

5

데이터베이스 전체 백업 및 로그 백업 생성

6

(sp_start_job을 사용하여 보저 서버의 작업을 호출)

7

데이터베이스 백업 및 로그 복원

8

가용성 그룹에 추가

MySQL

PostgreSQL

SQL Server

Maturity

1995년 릴리즈

1989년 릴리즈

1989년 MSMS OS/2용 SQL Server릴리즈(Sybase와 함께)

1995년 SQL Server 6.0릴리즈

Language

C (일부 C++)

C

C++

Cost

오픈소스 / 오라클 소유의 유료버전

완전 무료 / 오픈소스

SQL Server Express의 무료버전과 그 외의 유료버전

MySQL

PostgreSQL

SQL Server

Row Update

업데이트가 수행되고 변경된 데이터가 롤백 세그먼트로 복사. Vacuum 및 인덱스 압축이 매우 효율적. MySQL은 읽기에는 속도가 느리지만 쓰기는 원자적이며 보조 인덱스의 열이 변경되어도 모든 인덱스를 변경할 필요가 없음

업데이트는 인서트 + 삭제 표시로 구현된다. 모든 색인에는 행의 실제 ID에 대한 링크가 있다. 열이 업데이트되면 새로운 물리적ID가 있는 새 행이 만들어지고 모든 행이 새로운 행의 실제ID에 대한 포인터를 얻기 위해 변경된 열을 참조하지 않는 경우에도 모든 인덱스가  업데이트 되어야 하기 때문에 업데이트 오버헤드가 발생

Row-Store 데이터베이스 엔진 :

인메모리 데이터베이스 엔진 : 업데이트는 인서트 + 삭제 표시로 구현. 가비지 컬렉터는 논 블럭킹 병렬로 작업

Columnstore 데이터베이스 엔진 : in-place 업데이트

Vacuum / Defragmentation

Vacuum 및 인덱스 압축은 매우 효율적

Vacuum은 전체 테이블 스캔을 수행하여 삭제 된 행을 찾는다. 프로세스/사용자의 작업에 큰 오버헤드를 줄 수 있다.

메모리 내 가비지 컬렉턴는 최대 15%의 오버헤드가 발생할 수 있다.

MySQL

PostgreSQL

SQL Server

쿼리 요청에 대한 Buffer Pool / Cache

MySQL 캐시는사용자 쿼리를 버퍼풀이라고 한다. 이 캐시는 필요에 따라 큰 크기로 설정할 수 있으므로 서버의 다른 프로세스에 충분한 메모리만 남겨둔다. 버퍼 풀을 여러 부분으로 분할하여 메모리 구조에 대한 경합을 최소화하고 테이블을 버퍼 풀에 고정할 수 있다. 테이블 스캔 또는 mysqldump는 이전 데이터를 제거한다.

PostgreSQL은 데이터 페이지를 위한 공유 메모리를 유지한다. 프로세스 기반 시스템이기 때문에 각 연결은 고유한 고유  OS 프로세스를 가지며 자체 메모리를 가지고 있다. 프로세스는 실행이 끝난 후 메모리를 해제 한다. 따라서 많은 연결을 확장하는데 문제가 발생할 수 있다.

SQL Server 메모리는 버퍼풀이라고 하며 크기는 필요에 따라 크게 설정할  수 있으며 여러 버퍼 풀을 설정하는 옵션은 없다.

제약 조건 지원

기본키, 외래키, not null 제약 조건, 고유 제약조건, 기본 제약조건을 지원. CHECK 제약 조건을 지원하지 않는다.

기본 키, 외래키, not null 제약 조건, 체크 제약 조건, 유니크 제약 조건, 기본 제약 조건을 지원한다.

기본 키, 외래키, not null 제약 조건, check 제약 조건, 고유 제약 조건, 기본 제약 조건을 지원한다.

임시 테이블

CTE를 지원하고 전역 임시 테이블 및 테이블 변수를 지원하지 않음.

동일한 쿼리에서 TEMPORARY 테이블을 두 번이상 참조할 수 없음. (예 : select * from tbl_temp t1 join tbl_temp as t2)

CTE, 전역 및 로컬 임시 테이블 및 테이블 변수를 지원.

동일한 이름을 가진 두 개의 테이블을 만들면 다른 하나는 일반 테이블이다.

Creatae temp table x (..)

Create table x (…)

Select * from X는 항상 임시테이블에서 데이터를 가져옴

CTE, 전역 및 로컬 임시테이블 및 테이블 변수 지원

Windows /Analytical 함수

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE PERCENTILE_CONT, PERCENTILE_DISC

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE, NTH_VALUE

CUME_DIST, FIRST_VALUE, LAG, LAST_VALUE, LEAD, PERCENTILE_CONT, PERCENTILE_DISC, PERCENT_RANK, ROW_NUMBER, RANK, DENSE_RANK, NTILE

병렬 쿼리 실행

MySQL은 일반적으로 쿼리당 1개의 CPU를 사용

쿼리 계획은 여러 CPU를 활용할 수 있음

쿼리 계획은 여러 CPU를 활용할 수 있음

인덱스

인덱스 구성 테이블을 지원 – 클러스터 인덱스.

지속된 인덱스/구체화된 뷰를 지원하지 않음

인덱스 구성 테이블 미지원.

지속된 인덱스 / 구체화된 뷰 지원

인덱스 구성 테이블을 지원 – 클러스터 인덱스

단일 쿼리에서 다중 인덱스 사용

단일 쿼리에 다중 인덱스 사용 가능

단일 쿼리에 다중 인덱스 사용 가능. X와y에 별도 인덱스가 있는 경우 WHERE x=5 and y=6과 같은 쿼리를 구현할 수 있는 방법 중 하나는 적절한 쿼리 절과 함께 각 인덱스를 사용한 다음 인덱스 결과를 AND로 결합하여 결과 행을 식별하는 것이다.

단일 쿼리에 다중 인덱스 사용 불가능

다중 컬럼 인덱스

다중 열 인덱스에는 최대 16개의 열을 포함할 수 있음

다중 열 인덱스에는 최대 32개의 열을 포함할 수 있음

다중 열 인덱스에는 최대 16개의 열을 포함할 수 있음

부분 인덱스

부분 인덱스를 지원하지 않음

부분 인덱스 지원

부분 인덱스 지원

JOIN 알고리즘

MySQL은 중첩 루프 알고리즘 또는 변형을 사용하여 테이블간의 조인을 실행

중첩 루프 조인, 해시 조인 및 병합 조인 알고리즘 지원

중첩 루푸 조인, 해시 조인 및 병합 조인 알고리즘 지원

쿼리 실행 계획 재사용

준비된 명령문 및 저장된 프로그램에 대한 캐시를 세션별로 유지. 한 세션에 대해 캐시된 명령문은 다른 세션에서 액세스할 수 없음

준비된 문이 열려있는 동안에만 쿼리 계획을 캐시함. 쿼리 계획은 준비된 문이 클로즈때 해제됨.

쿼리가 실행계획을 다시 사용할 수 있도록 공유 된 실행계획 캐시가 있음

통계

지속성 및 비 지속성 통계 유지 (서버 재시작시 지워짐)

메모리 최적화 테이블

MySQL은 테이블을 메모리에 저장할 수 있다. 메모리에 작성된 테이블은 트랜잭션을 지원하지 않으므로 데이터가 손상될수 있다. 이러한 테이블은 임시 영역 또는 읽기 전용 캐시로 사용해야한다.

메모리 엔진을 제공하지 않음

인 메모리 OLTP가 SQL Server의 데이터베이스 엔진에 통합되어 있음

Columnstore 또는 행 저장소

MariaDB는 최근에 여러 서버가 있는 환경에서 대규모 병렬 데이터베이스로 설계된 MySQL용 열 저장소 엔진을 추시. InnoDB 스토리지 엔진 대신 사용 가능

행 저장소, 컬럼형 스토리지 엔진을 제공하지 않음

SQL Server는 큰 테이블을 쿼리하기 위해 열 저장소 인덱스를 제공

MySQL

PostgreSQL

SQL Server

JSON 데이터 유형

MySQL은 JSON 데이터 형식을 지원하며 전체 문서를 바꾸는 대신 JSON을 통한 부분 업데이트를 지원. 그러나 많은 제한이 있다. JSON에 대한 인덱스 생성은 지원하지 않음

PostgreSQL은 JSON 데이터 유형을 지원하며 부분 업데이트를 지원한다.

SQL Server는 JSON 데이터 형식을 지원하며 부분 업데이트를 지원

고급 데이터 형식

지형 공간 데이터 유형을 지원한다. 사용자 정의 유형이 없다.

지형 공간 및 다차원 배열, 사용자 정의 형식 등과 같은 많은 고급 데이터 형식을 지원

지형 공간 데이터 유형, 계층 적 데이터 지원

MySQL

PostgreSQL

SQL Server

파이셔닝 지원

HASH 파이셔닝(모든 컬럼에서 HASH 함수를 사용하여 테이블을 N개의 파티션으로 분할), 여러 컬럼을 기반으로 하는 RANGE 또는 LIST 파티셔닝, HASH와 유사한 KEY 파티셔닝(자동생성된 숫자 기반)을 지원

RANGE및 LIST 파티셔닝을 지원하지만 파티션 인덱스는 수동으로 생성해야하며 테이블 상속을 통한 구식 파티셔닝이 필요(부모 테이블을 쿼리할때 모든 하위 테이블도 쿼리가 될때 하위 테이블은 파티셔닝 컬럼에 제약이 있음)

하위 테이블에는 하위 테이블에 부모 테이블과 인덱스를 별도로 적용해야하는 열이 더 많을 수 있음

RANGE 파이셔닝을 지원

샤딩 지원

공유를 잘 구현하지 못함(MySQL Cluster는 많은 제한 사항 때문에 거의 배포되지 않음)

공유를 구현하는 Postgres 포크는 수십가지가 있지만 아직 커뮤니티 출시에 추가된 것은 없음

표준 공유 구현이 없음

복제

명령문 또는 변경된 행을 기반으로 하는 마스터-슬레이브 복제.

그룹 복제는 마스터 서버에서 자동으로 복제

변경된 행 및 로그 전달을 기반으로하는 마스터-슬레이브 복제

데이터베이스 수준 : 가용성 그룹의 마스터-여러 슬레이브

로그전달

On Data level : 마스터-슬레이브 /양방향 마스터-슬레이브 / 마스터-마스터(병합) 복제

<?xml version="1.0" encoding="utf-16"?>

<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" Version="1.518" Build="13.0.5201.2" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

 <BatchSequence>

  <Batch>

   <Statements>

    <StmtSimple StatementCompId="6" StatementEstRows="419.335" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="TimeOut" ......>

    ...

   <Statements>

  <Batch>

<BatchSequence>

참고 : 쿼리 조건자(WHERE 절의 필터) 및 제약 조건의 존재로 인해 고려되는 액세스 방법 수가 줄어들 가능성은 있다.

select ...

from t1

join t2

on t1.id = t2.id

join t3

on t3.id = t2.id

join t4

on t4.id = t3.id

select ...

into #temp1

from t1

join t2

on t1.id = t2.id

select ...

From t3

join #temp1

on t3.id = #temp1.id

join t4

on t4.id = t3.id

DROP TABLE IF EXISTS [Sales].[SalesOrderHeaderTest]

GO

CREATE TABLE [Sales].[SalesOrderHeaderTest](

    [SalesOrderID] [INT] NOT NULL,

    [CustomerID] [INT] NOT NULL,

    [CreditCardApprovalCode] [nvarchar](13) NULL

)

GO

INSERT INTO [Sales].[SalesOrderHeaderTest]

SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode]

FROM [Sales].[SalesOrderHeader]

GO

Msg 8152, Level 16, State 4, Line 10

String or binary data would be truncated.

The statement has been terminated.

Msg 2628, Level 16, State 1, Line 14

String or binary data would be truncated in table 'AdventureWorks2016CTP3.Sales.SalesOrderHeaderTest', column 'CreditCardApprovalCode'. Truncated value: '1231736Vi8604'.

The statement has been terminated.

 SELECT [SalesOrderID], [CustomerID], [CreditCardApprovalCode], LEN([CreditCardApprovalCode])

FROM [Sales].[SalesOrderHeader]

WHERE CreditCardApprovalCode LIKE '1231736Vi8604%'

CREATE TABLE myTable (myString VARCHAR(120));

GO

INSERT INTO myTable

VALUES ('Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore et dolore magna aliqua.')

GO

Msg 2628, Level 16, State 1, Line 30

String or binary data would be truncated in table 'AdventureWorks2016CTP3.dbo.myTable', column 'myString'. Truncated value: 'Lorem ipsum dolor sit amet, consectetur adipiscing elit, sed do eiusmod tempor incididunt ut labore '.