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.

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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];

GO

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 프로토콜과 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 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

GO

 

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

GO

 

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

GO

 

DROP TABLE #T

GO

 

 

아래 출력을 보면 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 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];

GO

ALTER DATABASE [DatabaseName] SET COMPATIBILITY_LEVEL = 150;

GO

 

 

[참고자료]

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 가용성 그룹에 데이터베이스 자동으로 추가하기

 

·         Version : SQL Server

 

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

 

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

-- runs on primary server

 

USE master

GO

 

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

GO

 

2. Primary 서버의 데이터베이스 복원

단계는 Primary 서버의 데이터베이스를 복원한다. 사용 권한 부여등 복원후 특정 요구사항에 대한 추가 단계를 추가하여 실행 있다. 아래 스크립트는 복원하려는 파일이 네트워크 상의 다른 서버에 있는 경우이다.

-- runs on primary server

 

USE master

GO

 

RESTORE DATABASE [<Database Name>]

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

GO

 

3. Primary 서버에 복원된 데이터베이스를 전체 복구 모델로 변경

-- runs on primary server

 

USE master

GO

 

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

GO

 

4. Primary 서버에서 데이터베이스 전체 백업 로그 백업 진행

복원 작업을 쉽게 수행하기 위해 Primary 서버보다 Secondary 서버가 액세스할 있는 네트워크 공유에 백업파일을 만드는것이 좋다. 이렇게 하면 Secondary 백업을 복사하지 않아도 되며 복사단계를 생략하여 시간을 절약할 있다.

-- 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

 

5. Primary 서버에서 가용성 그룹에 데이터베이스를 추가

-- runs on primary server

 

USE master

GO

 

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

GO

 

6. Secondary 서버에서 데이터베이스 복원

Secondary에서 가요성 그룹에 추가할 데이터베이스를 전체 백업 로그 백업 파일을 이용해서 복원한다.

-- 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

 

7. 데이터베이스 상태를 확인한 다음 가용성 그룹에 추가

-- 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 서버에 Job Agent 추가한다. 순서를 정리하면 아래 표와 같다.

 

Primary

Secondary

1

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

 

2

데이터베이스 복원

 

3

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

 

4

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

 

5

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

 

6

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

 

7

 

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

8

 

가용성 그룹에 추가

 

Primary 서버에서 Secondary 서버의 작업을 호출하지 않는 경우 Primary 작업이 완료 Secondary 서버의 작업이 시작되도록 시간을 설정해야 한다.

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5194/automate-refresh-of-a-sql-server-database-that-is-part-of-an-availability-group/

 

 

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

 

SQL Server, MSSQL, AlwaysOn, SQL Server Availability Group, 가용성 그룹

SQL Server MySQL PostgreSQL 비교

 

·         Version : SQL Server MySQL PostgreSQL

 

RDBMS 많이 사용하는 SQL Server MySQL 그리고 PostgreSQL 특징 차이점에 대해서 살펴본다. 데이터베이스 기능이 너무나 많기 때문에 모두 다루지는 못하며 대표적인 내용 몇가지만 다루도록 한다.

·         MySQL PostgreSQL, SQL Server 데이터베이스 모두 ACID 완벽하게 지원하며 많은 양의 데이터와 높은 수준의 쿼리 동시성을 처리할 있다.

·         PostgreSQL 기능이 풍부하고 확장성이 뛰어나다

·         MySQL 많은 곳에서 사용하고 있으며 애플리케이션과 전자상거래 프로젝트에 적합하며, PostgreSQL 비해 블로그, 지원, 문서가 훨씬 많다

·         PostgreSQL 경우 연결마다 자체 메모리가 있기 때문에 연결수가 많은 환경의 경우 많은 메모리가 필요할수 있다. 그러나 PgBouncers 외부 연결 풀을 사용하여 이러한 문제를 해결할 있는 솔루션이 있다.

·         PostgreSQL 수동 파티션 관리는 너무 많은 오버헤드와 파티션에서 다른 파티션으로 행을 이동시키는 업데이트가 필요하다.

·         MySQL에는 쿼리당 1개의 CPU 사용하는 중첩루프 조인 알고리즘만 있어 MySQL 데이터웨어우스 시스템에는 적합하지 않다.

·         Check 제약 기능이 중요한 서비스라면 MySQL 적합하지 않다.

 

[MySQL, PostgreSQL, SQL Server일반 정보]

 

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 데이터 변경]

 

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 데이터 쿼리]

 

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 활용할 있음

인덱스

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

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

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

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

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

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

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

단일 쿼리에 다중 인덱스 사용 가능. Xy 별도 인덱스가 있는 경우 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

PostgreSQL

SQL Server

JSON 데이터 유형

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

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

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

고급 데이터 형식

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

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

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

 

[MySQL, PostgreSQL, SQL Server 샤딩/파티셔닝/복제]

 

MySQL

PostgreSQL

SQL Server

파이셔닝 지원

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

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

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

RANGE 파이셔닝을 지원

샤딩 지원

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

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

표준 공유 구현이 없음

복제

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

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

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

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

로그전달

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

 

[참고자료]

https://www.mssqltips.com/sqlservertip/5745/compare-sql-server-mysql-and-postgresql-features/

 

 

2018-10-30 / Sungwook Kang / http://sqlmvp.kr

 

SQL Server, MSSQL, MySQL, PostgreSQL


SQL Server 복잡한 쿼리가 옵티마이저에 미치는 영향과 옵티마이저 timeout

 

·         Version : SQL Server

 

SQL Server 비용 기반(cost-based) 쿼리 최적화 프로그램을 사용한다. 따라서 여러 쿼리 계획을 작성하고 검사한 비용이 가장 낮은 쿼리 계획을 선택한다. SQL Server 쿼리 최적화 프로그램(QO) 목적중 하나는 쿼리 실행과 비교하여 쿼리 최적화에 합리적인 시간 소비하는 것이다. 따라서 QO에는 최적화 프로세스를 중지하기 전에 고려해야 태스크 임계값이 내장되어 있다. QO 모든 계획은 아니지만 가능한 대부분의 계획을 고려하기 전에 임계값에 도달하면 Optimizer Timeout 한계에 도달한다. 이벤트 로그에는 “Reason For Early Termination of Statement Optimization.” 기록된다. 여기서 이해야하는 중요한 부분이 임계 값은 실제 시간이 아니라 고려된 가능성의 수를 기반으로 한다는 것이다. 현재 SQL QO 버전에서는 Timeout 도달하기 전에 50만개가 넘는 가능성이 고려된다.

 

최적화 프로그램 Timeout Microsoft SQL Server에서 디자인 하였으며 많은 경우 쿼리 성능에 영향을 주는 요소가 아니다. 그러나 경우에 따라 SQL 쿼리 계획 선택에서 최적화 프로그램 Timeout 적용될 있으므로 성능에 영향을 있다. 이러한 문제가 발생하면 SQL Server 최적화 프로그램 Timeout 메커니즘과 복잡한 쿼리가 영향을 받을 있음을 이해하면 성능 문제를 보다 효과적으로 해결하고 개선하는데 도움이 있다.

증상은 무엇인가?

·         여러 개의 조인된 테이블을 포함하는 복잡한 쿼리가 있다.( : 8 이상의 테이블이 조인됨)

·         쿼리를 다른 SQL Server 버전이나 다른 시스템과 비교하였을 느리게 실행되거나 느리게 실행될 있다. (성능 베이스라인이 필수적으로 있어야 한다.)

·         XML 쿼리 계획에서 StatementOptmEarlyAbortReason=”TimeOut” 정보를 확인한다. 또는 Microsoft SQL Server Management Studio에서 가장 왼쪽 계획 연산자의 속성을 확인해보면 “Reason For Early Termination of Statement Optimization” 값이 “TimeOut”인지 확인한다.

 

아래 결과는 옵티마이저 Timeout 보여주는 쿼리 계획의 XML 출력이다.

<?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>

 

아래 그림은 TimeOut값을 표시하는 실행계획의 그래픽 표현 이다.

 

어떻게 작동 할까?

옵티마이저 임계값에 도달하거나 초과하게 하는 조건을 판별하는 간단한 공식은 없다. 그러나 아래 요소는 QO 최상의 계획을 결정하는 가지 요소이다.

·         테이블을 조인할 순서 : (테이블 수가 많을 수록 가능성이 커짐)

o   Table1 Table2, Table3 조인

o   Table1 Table3, Table2 조인

·          (Heap) 또는 이진트리(HoBT) 테이블에서 행을 검색하는데 사용하는 액세스 구조 :

o   Nonclustered Index 1

o   Nonclustered Index 2

o   Clustered Index

·