SSMS에서 127.0.0.1(localhost) 혹은 별칭으로 접속하기

MSSQL에는 저장프로시저가 아주 강력하고 문법 자체도 편하기(?) 때문에 토이프로젝트를 진행할 때 DB를 MSSQL을 주로 사용한다.

본인 노트북, 혹은 데스크탑에 MSSQL을 설치하고 SSMS로 접속을 하려고 할 때 서버이름에 localhost, 혹은 (local), 127.0.0.1 혹은 서버별칭을 써서 접속을 하려면 설정을 해주어야한다.

먼저 localhost, 혹은 (local), 127.0.0.1로 접속을 하는 방법은 다음과 같다.

위의 캡쳐 화면처럼 구성 관리자에서 SQL Server 네트워크 구성 -> TCP/IP 속성 -> IPALL 부분에서 TCP 포트 부분에 포트 번호(기본 1433)을 입력해주어야 한다.

그리고 SQL Server 엔진을 재시작하면 반영이 된다.

위처럼 이제 서버 이름에 localhost을 쳐서 접속이 가능하다.

다음으로 서버 이름에 별칭을 써서 접속하는 방법은 다음과 같다.

마찬가지로 구성 관리자에서 SQL Native Client 구성에서 별칭을 등록해주면 된다.

(32비트는 별도로 구분되어 있다.)

구성 관리자 ->  SQL Native Client 구성 -> 새별칭에서 별칭과 서버를 등록해주면 된다.

이제 서버 이름에 별칭을 써서 접속을 할 수 있다.

별칭이나 IP를 쓰고 접속해야 편하기 때문에 DB를 설치하면 두 설정 모두 꼭 하는 작업이라고 할 수 있다. 

MSSQL 백업에 대하여

MSSQL은 여러 백업 방법을 제공하고 이를 활용하여 적절한 백업 및 복원 전략을 세워보자.

백업 옵션에는 다음과 같이 여러 가지가 있다. 

보통 1, 2, 3, 4 정도만 알아도 DB 운영에는 문제가 없어보인다.

1. full backup : 데이터베이스를 구성하는 모든 데이터 파일을 백업하고 백업이 진행되는 동안 기록된 트랜잭션 로그를 백업한다.

2. differential backup : 가장 마지막 전체 백업 이후에 변경된 데이터만을 백업한다.

(MSSQL은 내부적으로 풀백업 이후 변경된 데이터를 관리한다.)

3. transaction log backup : 트랜잭션 로그 파일을 백업하고 로그를 지운다.

4. tail-log backup : 현재 활성화된 로그, 즉 백업되지 않는 트랜잭션 로그를 백업한다.

5. file and file group backup : 특정 파일 또는 파일 그룹에 포함된 데이터 파일만을 백업한다. 복잡해서 많이 사용되지는 않는다.

6. partial backup : 파일 그룹 중에서 primary 파일 그룹과 읽기/쓰기 속성의 파일그룹, 그리고 명시적으로 지정된 읽기 전용의 파일 그룹만을 백업한다.

-데이터베이스가 읽기 전용 파일 그룹과 읽기와 쓰기가 가능한 파일 그룹이 있다면 전자는 자주 백업할 필요가 없다. partial backup은 파일 그룹 중에서 primary 파일 그룹과 읽기/쓰기가 가능한 파일 그룹을 백업한다. primary 파일 그룹만으로 구성된 DB는 partial backup이 의미가 없고, 또한 명시적으로 지정한 읽기 전용 파일그룹도 백업에 포함하여 백업할 수 있다.

7. copy only backup : 기존 백업의 흐름에 영향을 주지 않고 임시용으로 백업한다. 즉, 복사 전용 전체 백업은 이후의 differential backup에 영향을 주지 않으며, copy only backup은 이후의 로그 백업에 영향을 주지 않는다.

- 일반 적인 백업은 백업 시퀀스에 영향을 줘서 전체적인 복원 시나리오에 영향을 미친다. 하지만 copy only backup은 기존의 백업 시퀀스와 독립적으로 작용해서 특별한 용도로 백업을 수행해서 데이터 및 프로시저를 복원하는데 유용하게 사용할 수 있다. 풀백업하는데 소요되는 시간이 별로 안된다면 백업 시퀀스는 별거 아니겠지만 그게 아니라면 상당히 까다로울 수 있다.

시나리오...

∎매일 전체 백업

일월화수목금토 00시 - full backup

적합한 곳

1. 크기가 작은 데이터베이스

2. 개발이나 테스트용 데이터베이스

3. 데이터 변경이 거의 발생하지 않는 데이터베이스

4. 읽기 전용 데이터베이스

5. 단순 복구 모델 데이터베이스

6. 복구 시 데이터 유실이 크게 문제되지 않는 데이터베이스

7. 특정 시점 복원이 요구되지 않는 데이터베이스

※ 전체 복구 모델이나 대량 로그 복구 모델 데이터베이스에 데이터 변경이 많이 발생한다면 정기적으로 트랜잭션 로그를 지워주는 전략을 같이 수립해야한다.

∎전체 백업 + 차등 백업 

일요일 00시 - full backup

월화수목금토 00시 - differential backup

적합한 곳

1. 데이터베이스 크기가 커서 매일 전체 백업이 부담스러울 때

2. 단순 복구모델 데이터베이스

3. 특정 시점이 복구 안되도 상관 없는 데이터베이스

4. 복구 시 데이터 유실에 크게 상관없는 데이터베이스

※ 이 역시 전체 복구 모델이나 대량 로그 복구 모델 데이터베이스에 데이터 변경이 많이 발생한다면 정기적으로 트랜잭션 로그를 지워주는 전략을 같이 수립해야한다.

∎전체 백업 + 트랜잭션 로그 백업

일월화수목금토 00시 - full backup

일월화수목금토 06시, 12시, 18시 - transaction log backup

적합한 곳

1. 데이터베이스 크기가 커서 매일 전체 백업이 부담스러울 때

2. 데이터 변경이 많이 발생하여 트랜잭션 로그가 많이 쌓이는 데이터베이스

3. 특정 시점의 복원이 요구되는 데이터베이스

※ 트랜잭션 로그 백업은 기본적으로 트랜잭션 로그를 지워주므로 트랜잭션 로그 파일이 계속해서 증가하는 문제 해결. 특정 시점으로 복원할 수 있는 장점이 있어서 일반적으로 가장 많이 사용되는 백업 전략.

∎전체 백업 + 차등백업 + 트랜잭션 로그 백업

일 00시 - full backup

일 12시 - transaction log backup

월화수목금토 00시 - differential backup

월화수목금토 12시 - transaction log backup

적합한 곳

1. 데이터베이스 크기가 커서 매일 전체 백업이 부담스러울 때

2. 데이터 변경이 많이 발생하여 트랜잭션 로그가 많이 쌓이는 데이터베이스

3. 특정 시점의 복원이 요구되는 데이터베이스

4. 데이터베이스 복원 과정을 단순하게 가져가고 싶을 때

※ 차등 백업이 있을 때는 데이터베이스를 복원할 때 전체백업과 차등 백업 사이의 로그 백업을 복원할 필요가 없다. 그래서 좀 더 수월하다.

복구 모델(Recovery Model)

복구모델은 단순 < 대량로그 < 전체가 있으며 부등호 방향은 복원가능한 정도를 뜻한다.

∎전체(full)

전체 복구 모델을 선택하면 모든 데이터 변경에 대한 로그를 트랜잭션 로그 파일에 기록한다. 하지만 많은 로그가 쌓여서 정기적으로 트랜잭션 로그를 백업해서 로그를 지워야 한다.

∎대량 로그(bulk-logged)

데이터 변경 작업 중에서 BCP, BULK INSERT, INSERT INTO... SELECT와 같은 작업을 대량 로그 작업이라고 하고 CREATE INDEX, ALTER INDEX REBUILD와 같은 작업을 대 로그 인덱스 작업이라고 한다.

복구 모델을 전체로 하면 엄청난 로그를 남겨 작업이 느리므로 복구 모델을 대량 로그 복구 모델로 설정하면 대량 로그 작업으로 발생하는 트랜잭션 로그를 최소화한다. 하지만 특정 시점으로 복원할 수 없는 단점이 존재한다. 요즘은 디스크 성능이 좋아져서 대량 로그 복구 모델을 많이 사용하지는 않는다.

∎단순(simple)

트랜잭션 로그는 전체 복구 모델이나 대량 로그 복구 모델일 때는 백업해야 지워지지만, 단순 복구 모델이면 트랜잭션이 완료되면 자동으로 지워진다. 그래서 트랜잭션 로그 파일이 무작정 커지는 문제는 거의 발생하지 않는다고 보면된다. 하지만 트랜잭션 로그를 백업할 수 없어서 트랜잭션 로그를 사용한 다양한 복원 작업을 할 수 없다. 특정시점 복원이라던지.. 그래서 운영 데이터베이스에는 적합하지 않고 테스트용, 개발용 또는 읽기 전용의 데이터베이스에 대한 복구 모델로 적합하다.

-- 데이터베이스 복구 모델 변경

ALTER DATABASE TESTDB01

         SET RECOVERY FULL --전체

GO

ALTER DATABASE TESTDB02

         SET RECOVERY SIMPLE --단순

GO

ALTER DATABASE TESTDB03

         SET RECOVERY BULK_LOGGED --대량 로그

GO

데이터베이스 백업 방법

--전체백업

BACKUP DATABASE TESTDB01

       TO DISK = 'F:\SQLBackup\TESTDB01_20170703_FULL.bak'

       WITH NAME = N'TESTDB01- 전체 백업', COMPRESSION, STATS = 10

GO

NAME 옵션을 사용해서 백업에 대한 정보를 백업 파일의 헤더에 기록하고 STATS = 10을 지정해서 백업 진행 상황을 SSMS 쿼리 창에서 10% 간격으로 표시한다. 뭐 얼마나 걸리겠어?하며 사용하지 않는 경우가 많은데 이 옵션은 무조건 사용하자.

--차등 백업

BACKUP DATABASE TESTDB01

       TO DISK = 'F:\SQLBackup\TESTDB01_20170703_DIFF.bak'

       WITH NAME = N'TESTDB01- 차등 백업',

       DIFFERENTIAL, COMPRESSION, STATS = 10

GO

--트랜잭션 로그 백업

BACKUP LOG TESTDB01

       TO DISK = 'F:\SQLBackup\TESTDB01_20170703_LOG.trn'

       WITH NAME = N'TESTDB01- 트랜잭션 로그 백업', COMPRESSION, STATS = 10

GO

SSIS에서 MSSQL Polybase로 Hadoop 데이터를 외부 테이블 생성할 때 팁

시나리오
하둡 데이터를 MSSQL로 매일 특정 시간에 가져오는 배치를 짠다고 가정한다. 이 경우 MSSQL에서는 해당 테이블을 polybase로 External Table을 만들게 된다.

예를 들어 하둡의 특정 경로 /app/analy/ods/visit/20181101처럼 끝에 날짜 포맷만 바꾸면서 데이터를 저장하고 이 데이터를 mssql로 가져오는 경우이다. 하나의 프로세스(배치)로 이루어지면 떨궈질때까지 기다리고 다 떨궈지면 가져오면 되지만 이 경우에는 하둡 데이터를 저장하는 것, MSSQL로 가져오는 것이 분리되어 있는 경우라고 가정한다.

우리는 하둡 경로를 알고 있을 때 MSSQL에서 다음처럼 데이터를 가져올 수 있다.
CREATE EXTERNAL TABLE ~
LOCATION ~
그러나 이 처럼 단순하게 해결되지 않는 경우가 있다.


문제점
특정 상황이 발생하여 하둡에 데이터가 생성되지 못했을 경우 CREATE 문을 실행하면 PATH가 존재하지 않는다는 에러가 난다.


해결방법
우리는 주기적으로 하둡 경로를 반복체크하면서 데이터가 생성된 이후에 MSSQL로 가져오면 된다.

하둡 명령어 중에 fs -test -s /app/analy/ods/visit/20181101 (-e는 path, -d는 directory, -f는 file 등 여러 옵션이 있다) 를 먼저 날려보고 결과로 0을 리턴하면 path가 존재한다는 의미이다.


즉 정리하면 다음과 같다.

1. 하둡 데이터를 떨굴 때 원하는 것을 다 떨구면 해당 경로에 success라는 크기가 0인 파일이나 폴더를 하나 더 떨구게 한다. (혹은 데이터 생성을 시작 할 때 start라는 폴더나 파일을 생성하게 하고 완료되면 success를 생성하게 해도 된다.)
2. 배치에서 데이터를 가져올 때 루프를 만들고 안에서 테이블을 생성하기 전에 fs -test -s /app/analy/ods/visit/20181101/success 를 날리도록 한다.
3. 리턴값이 0이면 success가 데이터가 존재하는 것이기 때문에 루프를 탈출하고 아니면 주기적으로 위의 명령어를 하둡에 찔러보게 한다.
4. 루프를 탈출하면 MSSQL에서 CREATE EXTERNAL TABLE 문 실행한다.

이런 방법을 데이터가 생성되기전까지 기다리는 것이기 때문에 보다 안전할 수 있다.

MSSQL RAISEERROR VS THROW 팁

시나리오
MSSQL의 가장 강력한 힘은 T-SQL이다. 저장프로시저가 강력한 배치 수단으로 쓰일 수 있기 때문인데 프로그래밍 코드를 작성하는 것 처럼 프로시저 안에서 모든 걸 처리할 수 있다. 그리고 요즘 같이 AI 흐름을 따라가기 위해서 인지 MS에서도 SQL Server 2017 버전부터 프로시저 내에서 Python을 지원한다.
저장 프로시저로 배치를 짜는 경우 의도적으로 에러를 내거나 에러가 났을 때 디버깅을 할 필요가 있다. 이때 유용하게 사용할 수 있는 것이 RAISEERROR와 THROW이다.
잘만 사용하면 배치에서 디버깅으로 정말 유용하게 사용할 수 있다.
필자는 Raiserror를 프로시저의 특정 구간에 걸어놓고 배치 시 디버깅을 할때 사용하거나 후속 배치를 일부러 돌리지 않기 위해 의도적으로 에러를 내는 경우에 종종 사용한다.


RAISEERROR VS THROW
1. Raiseerror는 msg_id가 RAISERROR에 전달되는 경우 ID가 sys.messages에 정의되어있어야 하지만 THROW는 error_number 매개 변수가 sys.messages에 정의되어있지 않아도 된다.
2. RAISEERROR는 msg_str 매개 변수는 printf 서식 지정 스타일을 포함할 수 있지만 THROW는 아니다.
3. RAISEEROR에서 severity 매개 변수는 예외의 심각도를 지정하지만 THROW에는 그런 파라메터가 없고 예외 심각도는 항상 16으로 고정되어있다.


사용방법
1. RAISEERROR
계획된 오류가 있는 테스트용 프로시저를 생성한다. (duplication error를 의도했다.)

그리고 프로시저를 실행해본다. 실무에서는 배치 프로시저라고 생각을 하면 되겠다.

2. THROW
마찬가지로 계획된 오류가 있는 테스트용 프로시저를 생성한다. 
(똑같이 duplication error를 의도했다.)

프로시저를 수행해본다.

3. SQL Server Log에 RAISEERROR가 남는 것을 볼 수 있다.

MSSQL 어셈블리 만들기 - How to Implementing CLR Object DLL

시나리오
1. SQL Server에서 PDW(Parallel Data Warehouse)로 동적 쿼리를 만들어서 날리려고 하는데 8000자 이상의 쿼리문을 수행해야 한다.
2. A서버의 SQL Server (가정 주소:321.321.1.321,9999)에서 B서버의 PDW (가정 주소 : 123.123.1.123,8888)로 쿼리를 날려 SELECT 결과를 받아오고자 한다.
3. 특정 사용자에게만 DB 접근권한을 주고 계정을 분리하고자 한다.

해결방법
CLR Object DLL를 만들어서 ASSEMBLY로 등록하고 CLR을 통해 쿼리를 수행한다.
CLR을 통해 DB 접근 권한을 분리하고 8000자 이상의 동적쿼리를 쿼리를 수행할 수 있다.

  고려사항
얼마 전 MSSQL이 설치된 윈도우 서버에서 메모리가 부족한 현상을 겪었고 덕분에 수 번의 장애를 겪었다. 윈도우 서버의 메모리가 약 100기가 이상이 되지만 보통의 경우에는 DB에 대부분의 메모리를 할당하고 최소한의 메모리를 OS에 할당하겠지만 필자의 경우에는 MSSQL에서 PDW로 CLR을 호출하는 경우 딱 그 시점에 윈도우 서버의 메모리가 확 튀며 장애 현상을 겪었다. 이런 경우에는 MS 엔지니어가 메모리를 DB에 적당히 할당한 후 안전하다고 판단되는 수준까지 점진적으로 올릴 필요가 있다고 한다.


방법

1. DLL을 만들기 위해 Visual Studio에서 Visual C# CLR Database Project를 만든다.

(파일 -> 새 프로젝트 -> SQL Server -> SQL Server 데이터베이스 프로젝트 생성)

2. 솔루션 탐색기에서 추가 -> 새항목 -> SQL CLR C# -> SQL CLR C# 저장 프로시저 생성한다.

3. 쿼리를 날릴 때 SelectPdwByLogin 함수를 호출할 것이고 이 함수 내부에서 분리시키고자 하는 login_name을 "exuser"를 구분했다.

4. 빌드를 하고 DLL(CLR_PDW.dll)을 얻는다. 이를 서버(321.321.1.321,9999)의 적당한 위치에 복사한다.

5. 서버(321.321.1.321,9999)에서 CREATE ASSEMBLY문으로 CREATE ASSEMBLY를 하고

이 때 위에서 만든 CLR_PDW.dll의 경로를 입력해준다. 그리고 프로시저를 하나 생성하여 어셈블리를 수행하도록 한다.

6. 테스트를 한다.

A 서버 SQL Server(321.321.1.321,9999)에서 USP_CLR_PDW를 호출하여 B 서버의 PDW(123.123.1.123,8888)의 TEST DB CUSTOMER 테이블을 SELECT 해본다.

※ 별도로 test DB에는 로그인 생성, 사용자 추가, 롤에 사용자를 추가해야한다.

--로그인 생성
CREATE LOGIN exuser WITH PASSWORD = 'exuser',
CHECK_EXPIATION = OFF,
CHECK_POLICY = OFF;

--TEST DB에 사용자 추가
USE test;
CRATE USER exuser WITH DEFAULT_SCHEMA=dbo;

--롤에 사용자 추가
EXEC test..sp_addrolemember db_datareader, exuser;