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

 SSMS – AlwaysOn Group Latency report

 

·         Version : SQL Server 2014 SP2 later, SSMS 17.4 later

 

SQL Server에서 AlwaysOn가용성에 대한 Latency 분석을 하려면 XEvent(확장이벤트)  Performance Counter(성능 모니터) 사용하여 데이터를 캡처하고 분석하는 과정이 필요했다. 이러한 작업은 설정에 많은 시간이 소요되었을 뿐만 아니라 XEvent 대한 광범위한 지식이 필요하였다.

 

SQL Server 관리도구인 SQL Server Management Studio (SSMS) 17.4 버전 부터는AlwaysOn 가용성 그룹에 대한 Latency 대시보드 기능이 추가되었다.   대시보드에서는 데이터베이스 관리자가 가용성 그룹 가용성 복제본 데이터베이스의 현재 상태를 확인하는데 사용할 있다. 대시보드에는 1 복제본과 2 복제본 간의 대기 시간(시간 계산은 Commit LSN, Sent LSN 값을 사용하여 계산) 대한 정보를 제공하고 있다. 이러한 대시보드 정보를 바탕으로 가용성 그룹의 기본 복제본과 보조 복제본간의 로그 전송 흐름에서 병목현상을 쉽고 빠르게 파악할 있다.

 

SSMS에서 AlwaysOn Latency 대한 데이터 수집 설정 보고서를 확인하는 방법에 대해서 살펴본다.

 

SSMS 17.4 이상의 버전을 실행한다. SSMS 대한 최신 버전 다운로드는 아래 링크를 참고 한다.

-          Download SSMS :  https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms

 


 

Always On High Availability 항목에서 [Availability Groups] – [사용자 가용성 그룹]에서 마우스 오른쪽을 클릭하여 [Show Dashboard] 실행 한다.


 

대시보드가 실행되면 Collect Latency Data 클릭 한다. 항목을 실행하면 데이터 수집을 위한 각종 기능이 백그라운드에서 진행되며 SQL Server Job 에이전트에 수집기 스케줄러가 등록된다.


 

데이터 수집 링크를 클릭하면 SSMS 가용성의 모든 복제본에 연결되어 데이터 수집 작업을 시작한다. 이때 복제본 보조 복제본의 SQL Server Agent 실행 중이어야 한다.


 

수집 기능 설정이 완료되면 아래와 같이 Job Agent 데이터 수집기 항목이 등록된 것을 확인할 있다.

 


 

스케줄러를 확인해보면 다양한  XEvent 등록된 것을 있다. 등록된 XEvent 실행하여 2 정도 데이터를 수집한다. 수집된 데이터는 TempDB AGInfo 테이블에 저장된다.


 

데이터 수집이 완료되면 Latency 대한 보고서를 확인한다. 이때 복제본 서버와 보조복제본 서버의 리포트를 따로 확인 한다.

Always On High Availability 그룹에서 마우스 오른쪽 버튼을 클릭하여 [Report] – [Standard Report] – [Primary Replica Latency] 실행한다.

 


 

복제본의 Latency 보고서에는 3개의 섹션이 있다. 번째 섹션에서는 AG 대한 복제본 정보를 제공한다.


 

번째 섹션에서는주 복제본의 Commit 복제 시간의 평균을 시각적으로 제공한다.

 

세번째 섹션은 커밋에 사용된 시간,  원격 복제 시간, 압축시간 로그 블록의 전송 지속시간에 대한 값을 제공한다.

 


 

 

보조복제본 보고서에는 보조복제본에서만 소요된 시간을 제공한다.




 

 

-          Local Flush – 로그 블록을 LDF파일에 기록하는데 소요된 평균 시간. 값은 Log_Flush_Start Log_Flush_Complete_Extended 이벤트를 사용하여 계산된다.

-          Decompression - 1차로 로그블럭을 수신한뒤 압축해제에 소요된 시간. SQL Server 2016 이상에서는 비동기 복제본에 대한 로그 전송이 압축되는 동안 동기 보조복제본에 대한 로그 전송은 압축되지 않는다.

-          Receive – USC 계층에서 로그 블록을 수신하고 추가 처리를 위해 대기열에 대기하는데 소요되는 시간

-          Send – 확인 메지시를 SQL USC 계층으로 보내는데 소요된 시간. 여기에는 보조복제본에서 주복제본까지 소요된 시간이 포함되지 않는다.

 

가용성 그룹 대기시간 데이터 수집기능은sysadmin(Windows 인증 전용) 계정에서만 사용할 있으며 현재 사용자 계정은 모든 복제본의 인스턴스 관리자 계정이어야 한다.

 

데이터 수집 기능에 대한 제약사항은 아래와 같다.

1.       한번에 하나의 가용성 그룹에 대한 대기 시간 데이터를 수집하는데에만 사용할 있다.

2.       TempDB 저장되는 데이터는 테이블이름이 하드코딩되어 하나의 컬렉션만 실행할 있다.

3.       TempDB 저장되므로 SQL Server 재시작되면 이전 기록된 데이터 정보가 손실된다.

4.       로그인 계정이 서버관리자 계정이어도 SQL 인증 사용자 또는 로그인에 대해서 수집 기능을 사용할 없다.

5.       Windows 인증만 지원하므로 도메인간 트러스트 없는 도메인간 배포에서는 작동하지 않는다.

6.       분산된 가용성 그룹에서는 기능이 작동하지 않는다.

7.       SQL Server 2014 SP2 이상에서 동작한다.

 

 

[참고자료]

https://blogs.msdn.microsoft.com/sql_server_team/new-in-ssms-always-on-availability-group-latency-reports/

 

 

 

2018-01-19 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

SQL Server, MS SQL, SSMS, Always On, 가용성 그룹, Latency Report, 복제 시간, commit time, High availability Group, AG,

SQL Operations Studio

-         Linux, MAC에서 사용할 있는 SQL DBMS

 

·         Version : Preview

 

SQL Server 관리하기 위한 Microsoft 툴은SQL Server Management Studio(SSMS) 있다. 툴은 Windows 환경에서 실행되며 막강한 기능을 가지고 있다. 아마 SQL Server 운영하는 대부분의 관리자는 DBMS 운영하는 관리자와는 달리 서드파티의 툴을 사용하지 않고 대부분 SSMS 사용하고 있을 것이다. 그만큼 SSMS 기능은 엄청나다.

 

최근 Microsoft 오픈소스 친화적인 정책을 지향하면서 다양한 툴들이 Linux 또는 Windows 아닌 운영체제에서 실행될 있게 되었다. 대표적인 툴이 Visual Studio Code 것이다. 이처럼 Microsoft 크로스 플랫폼 또는 멀티 플랫폼을 지원하는 다양한 서비스를 출시하고 있다.

 

2017 Microsoft에서 SQL Server Linux 버전을 출시하였다. 기본적인 기능은 이미 Windows 버전의 SQL Server 거의 동일하다. 일부 편의 기능만 아직 출시 되지 않았을 , DBMS 기능은 훌륭하다. 하지만 SSMS Windows 버전 밖에 지원되지 않아, SQL Server Linux 설치함에도 불구하고 관리툴은 윈도우 환경에서 실행하여야 했다. 하지만 최근 Microsoft에서는 SQL Operations Studio라는 새로운 DB 관리 툴을 출시 하였으며, 윈도우가 아닌 다른 환경도 지원하게 되었다. 이번 포스트에서는 SQL Operations Studio 대해서 살펴본다.

 

SQL Operations Studio 아래 링크에서 다운로드 받을 있다. 포스팅 하는 시점에는 Preview 버전이 공개된 상태로 향후 어떠한 많은 기능이 추가될지 기대 된다.

·         Download and install Microsoft SQL Operations Studio (preview)

https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

 

다운로드 받은 파일을 압축 해제 한다. SQL Operations Studio 설치형으로 실행되는 툴이 아닌 exe 바로 실행하여 사용하는 툴이다. 그래서 따로 설치 과정이 필요 없다. Sqlops.exe 실행 한다.


 

SQL Operations Studio 실행한 화면이다. VS Code 비슷한 UI 하고 있다.


 

서버 등록 정보를 생성하기 위해 왼쪽의 메뉴바에서 Server 클릭하고 새로운 서버를 등록한다.


 

접속하려는 SQL Server 정보를 입력한다. 필자의 경우 로컬에 설치된 SQL Server 접속 정보를 입력하였으며, 여러번의 테스트로 인해 최근 히스토리에 서버 내용이 표시됨을 있다.


 

서버 정보를 입력하고 처음 서버를 접속했을 화면이다. 기본적으로 현재 생성되어 있는 데이터베이스에 대한 정보를 표시해 주며, 생성되어 있는 데이터베이스의 디스크 사용량 백업 유무에 대한 관리적인 관점에서의 정보도 표시해서 보여준다. (일반적으로 DBA 들에겐 크게 도움이 되지 않는 정보이지만 DBA 아닌 사람들에겐 유용한 정보일 듯하다. 하지만 해당 정보를 생성하기 위해 많은 쿼리를 호출하는 과정이 대용량 서비스 경우 살짝 걱정이 된다.)


 

 

쿼리를 호출하기 위해 쿼리창을 실행한다. 상단 메뉴에서 New Query 실행하여 쿼리 창을 실행 있지만 이렇게 실행하면 해당 쿼리 창을 다시 DB 연결해야하는 번거로움(순수 에디트 창만 오픈) 있다. 그래서 쿼리를 실행하려는 데이터베이스에서 마우스 오른쪽을 클릭하여 New Query 선택한다. 그러면 해당 데이터베이스에 연결된 쿼리창이 실행 된다.


 

 

AdventureWorks 데이터베이스에 쿼리를 실행해 보았다. 기본적으로 SSMS 동일하게 결과 집합이 표시된다. 가지 흥미로운 점은 Visual Studio 처럼 해당 리소스에 대해서 직접 참조하거나 Peek 창으로 오버레이하여 참조되는 정보를 확인할 있다. 여기서 참조 되는 정보는 테이블 정보라 있다.


 

 

아래 그림은 Go to Define 했을 새창으로 테이블 정보를 나타낸 모습이다.


 

 

실행 계획 버튼으로 쿼리 결과에 대한 실행 계획도 바로 확인 있다. 물론 SSMS 처럼 실행 중의 쿼리 실행 계획 아직은 많은 기능이 제공되지는 않지만 부족한 기능은 정식버전에서 많이 추가 될것이라 생각한다.


 

결과 집합 창의 오른쪽에 위치한 가지 아이콘이 있는데, CSV, Excel, JSON 형식의 파일로 저장할 있는 기능이다. 아마도 많은 사용자들이 결과셋을 파일로 저장하는데 편의 기능을 보충하기 위해 퀵메뉴를 지원하는 느낌이었다.


 

결과셋 오른쪽 아이콘 중에 마지막 아이콘은 Chart 아이콘인데, 결과에 대한 내용을 Chart 보여준다. Chart 가지 형태로 제공되고 있었다. 하지만 어떤 내용을 기준으로 차트를 생성하는지는 정확히 없었다. 만약 간단한 카운트나 비율에 대한 결과 값을 조회 하였다면 차트 기능을 이용하여 굳이 엑셀로 내려 받아서 차틑를 그리지 않아도 확인 있을 했다.


 

아직은 Preview 버전이어서 기존의 SSMS 비하면 많은 기능이 지원되지 않지만 가벼우면서 기본기에 충실한 툴의 느낌이 들었다. 아직은 프로파일러 기능이나, 어드바이저 기능들이 제공되지 않지만 시간이 지나면서 많은 기능이 포함되리라 생각이 든다. 무엇보다 Windows 환경이 아닌 곳에서도 SQL Operations Studio 설치해서 사용할 있으니 Linux   다양한 환경에서 SQL Server 생태계 확대가 기대된다.

 

[참고자료]

·         https://docs.microsoft.com/en-us/sql/sql-operations-studio/download

·         https://blogs.technet.microsoft.com/dataplatforminsider/2017/11/15/announcing-sql-operations-studio-for-preview/

·         https://docs.microsoft.com/en-us/sql/sql-operations-studio/quickstart-sql-server

 

 

 

 

 

2017-12-04 / 강성욱 / http://sqlmvp.kr / http://sqlangeles.com

 

 

SQL Server, MSSQL, SQL 2016, SQL Server Management Studio, SSMS, SQL Operations Studio, SQL 관리 , DB 관리 , SQL Linux, SSMS Linux



SQL Server 데이터베이스 속성을 확인할 때 사용되는 master.dbo.spt_values 복구

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014, 2016

 

SSMS에서 SQL Server의 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하였을 때 해결하는 방법에 대해서 알아본다.

 

SSMS에서 서버 속성을 클릭하였을 때 master.dbo.spt_values 오류가 발생하면 다음과 같은 경고문이 나타난다.

 

master.dbo.spt_values 프로시저는 다양한 데이터 항목을 포함하는 문서화 되지 않은 항목이다. 이 프로시저는 C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Install에서 u.table.sql에서 생성하는 것을 확인할 수 있다.

 

master.dbo.spt_values 오류 문제는 동일한 버전 및 에디션을 실행중인 다른 서버에서 복사하여 해결할 수 있다.

 

개체를 복사하는 방법으로는 스크립트를 생성하거나 가져오기 및 내보내기 마법사를 사용한다.

 

 

가져오기 및 내보내기에서 객체를 가져올 서버를 선택하고 다음을 클릭한다.

 

 

마찬가지로 master.dbo.stp_values를 생성할 대상 정보를 입력하고 다음을 클릭한다.

 

 

다음과 같이 화면이 나타나면 두 번째 옵션을 선택하고 다음을 클릭한다.

 

Master.dbo.stp_values는 시스템 오브젝트이기 때문에 T-SQL 쿼리를 사용하여 직접 가져와야 한다. 아래와 같은 스크립트를 입력하고 다음을 클릭한다.

SELECT * FROM master.dbo.spt_values

 

 

 

대상 객체의 이름을 설정한다. 이때 이름은 master.dbo.stp_values로 설정한다.

 

 

미리보기 버튼을 클릭하면 소스 오브젝트의 데이터를 표시한다. 실제 가져오기를 수행하려면 다음을 클릭한다.

 

 

가져오기 작업이 진행 되며 경고가 발생하면 해당 경고를 해결 후 다시 실행 한다.

 

 

정상적으로 가져오기 작업이 완료 되면 SSMS에서 서버 속성을 클릭하여 정상적으로 정보가 표시되는 것을 확인할 수 있다.

 

 

[참고자료]

https://www.mssqltips.com/sqlservertip/3694/fix-invalid-object-name-masterdbosptvalues-when-viewing-sql-server-database-properties/

 

 

2015-08-19 / 강성욱 / http://sqlmvp.kr

 

SQL Server, MSSQL, SSMS, master.dbo.stp_values, 데이터베이스 속성 오류, SQL 서버 속성 오류

SQL Server Agent 공유 일정 생성하기

 

  • Version : SQL Server 2005, 2008, 2008R2, 2012, 2014

 

SQL Server Agent에서 Job을 등록하고 Job이 실행될 일정을 등록한다. 이때 여러 job에서 같은 시간에 반복되는 일정이 있더라도 매번 일정을 등록해 주어야 한다. 공유된 일정을 만들어서 해당 일정을 여러 Job에서 가져다 쓸 수 있으면 얼마나 편리할까? 이번 포스트는 SQL Server Agent의 공유 일정 사용법에 대해서 알아 본다.

 

SSMS에서 [SQL Server Agent] – [Job]에서 마우스 오른쪽을 클릭하여 [일정 관리]를 선택 한다.

 

일정 관리를 클릭하면 현재 등록된 모든 일정에 대해서 확인 할 수 있다. 사용자가 등록한 일정 외에 SQL Server에서 사용하는 일정도 확인이 가능하다.

 

이 일정내의 작업의 숫자는 해당 일정을 사용하고 있는 Job의 개수를 의미한다. 해당 숫자를 클릭하면 일정을 사용하는 Job의 목록을 확인 할 수 있다.

 

 

공유된 일정을 만드는 방법은 [일정 관리] – [새로 만들기]를 클릭해서 만들 수 있다.

 

공유된 일정을 사용하는 방법은 Job을 생성할 때 일정 등록에서 [선택] 버튼을 이용해서 이미 생성된 일정을 가져다 쓸 수 있다.

 

 

이과 같이 공유된 일정을 사용하면 여러 Job에서 동일한 일정을 사용할 수 있으며 일정 변경이 발생할 경우 해당 공유 일정을 사용하는 Job의 일정을 일괄적으로 변경 할 수 있다.

 

주의할 점은 공유된 일정을 사용하는 Job에서 일정을 변경 할 경우 공유된 일정 또한 변경된다는 점이다. 만약 다른 사용자가 공유된 일정을 사용하는지 인지하지 못하는 상태에서 일정을 변경하게 되면 동일한 공유 일정을 사용하는 모든 Job의 일정이 변경됨으로 주의해야 한다.

 

 

[참고자료]

http://www.mssqltips.com/sqlservertip/3554/understanding-how-sql-server-agent-jobs-can-share-schedules/

 

2015-03-31 / 강성욱 / http://sqlmvp.kr

 

 

SQL Server, mssql, SQL Agent, 일정등록, SQL Job Agent, 공유일정 등록, SQL Job, DBA, share schedules, SSMS

 

 

+ Recent posts