Full Text Catalog 의 변경 내용 추적을 AUTO 로 운영중인데 서버 재기동 이후 채우기가 동작하지 않는 상황 발생 하였다.

검색해서 찾은 해결 방법은 

1. rebuild

2. 변경 내용 추적을 MANUAL 로 변경했다 AUTO 로 변경

3. 서버 재기동 

rebuild 는 시간 및 부하 이슈가 있으니 변경 내용 추적을 MANUAL 로 변경했다 AUTO 로 변경했다.

이후 주기적으로 잘 수집되는것을 확인하였다.

# Full Text Catalog 상태 확인 
declare @id int
select @id = id FROM sys.sysobjects where [Name] = 'TableName'
select 'TableFullTextBackgroundUpdateIndexOn' as 'Property', objectpropertyex(@id, 'TableFullTextBackgroundUpdateIndexOn') as 'Value'
union 
select 'TableFullTextChangeTrackingOn', objectpropertyex(@id, 'TableFullTextChangeTrackingOn')
union 
select 'TableFulltextDocsProcessed', objectpropertyex(@id, 'TableFulltextDocsProcessed') 
union 
select 'TableFulltextFailCount', objectpropertyex(@id, 'TableFulltextFailCount') 
union 
select 'TableFulltextItemCount', objectpropertyex(@id, 'TableFulltextItemCount') 
union 
select 'TableFulltextKeyColumn', objectpropertyex(@id, 'TableFulltextKeyColumn') 
union 
select 'TableFulltextPendingChanges', objectpropertyex(@id, 'TableFulltextPendingChanges') 
union 
select 'TableHasActiveFulltextIndex', objectpropertyex(@id, 'TableHasActiveFulltextIndex') 
union 
select 'TableFulltextPopulateStatus', objectpropertyex(@id, 'TableFulltextPopulateStatus') 

# 아래 쿼리의 Command 에서 생성된 쿼리로 변경 내용 추적을 MANUAL 로 변경했다 AUTO 로 변경한다.
# 주의할 점은 crawl_end_date가 없는 경우 전체를 다시 수집한다. 
SELECT [t].[name] [table_name], 
       [i].[name] [index_name],
       [fi].[change_tracking_state_desc], 
       [fi].[has_crawl_completed], 
       [fi].[crawl_type_desc], 
       [fi].[crawl_end_date],
       [ius].[last_user_update], 
       [ius].[last_user_seek],
       (SELECT [name]+',' FROM [sys].[fulltext_index_columns] [fc] INNER JOIN [sys].[columns] [c] ON [c].[object_id] = [fc].[object_id] AND [c].[column_id] = [fc].[column_id] WHERE [fc].[object_id] = [fi].[object_id] FOR XML PATH('')) [columns],
       (CASE WHEN [fi].[crawl_end_date] < ISNULL([ius].[last_user_update], [ius].[last_user_seek]) THEN 'ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING MANUAL; ALTER FULLTEXT INDEX ON ['+[t].[name]+'] SET CHANGE_TRACKING AUTO' ELSE '' END) [Command]
FROM [sys].[fulltext_indexes] [fi]
INNER JOIN [sys].[indexes] [i] ON [i].[index_id] = [fi].[unique_index_id] AND [i].[object_id] = [fi].[object_id]
INNER JOIN [sys].[tables] [t] ON [t].[object_id] = [fi].[object_id]
LEFT JOIN [sys].[dm_db_index_usage_stats] [ius] ON [ius].[index_id] = [fi].[unique_index_id] AND [ius].[object_id] = [fi].[object_id] AND [ius].[database_id] = DB_ID()
ORDER BY [table_name], [index_name]

참고 : 

https://stackoverflow.com/questions/36329633/updating-fulltextsearch-index-immediately-sql-server-2012

https://www.mssqltips.com/sqlservertip/5996/tsql-script-to-correct-sql-server-full-text-indexes-not-updating/

열 암호화시에 char, nchar 데이터 타입 컬럼의 공백  검색 이슈가 있다.

테스트 테이블 생성

이름 컬럼 암호화 

이름 검색시 오류가 발생한다. 

피연산자 유형 충돌: varchar은(는) varchar(8000) encrypted with (encryption_type = 'DETERMINISTIC', encryption_algorithm_name = 'AEAD_AES_256_CBC_HMAC_SHA_256', column_encryption_key_name = 'ColEncKey', column_encryption_key_database_name = 'Test') collation_name = 'Korean_Wansung_CI_AS'과(와) 호환되지 않습니다.

아래와 같은 형식으로 검색해야 에러없이 검색된다. 

그런데 '일호'가 검색되지 않는다.. 

왜?

이름 컬럼은 nchar(20) 이다. char, nchar 는 입력한 값이 데이터 타입의 크기보다 작으면 그만큼 공백이 입력된다. 

'일호                  ' 로 저장되어 있다. 

열 암호화가 적용된 컬럼에 테스트용 데이터 입력 

열 암호화가 적용된 컬럼은 insert 시에도 변수를 선언해서 사용해야 한다.

특이사항은 insert 문 하나인데 (1개 행 적용됨)이 3개가 뜬다..

궁금하면 이것은 프로파일러를 떠보기 바란다. 

문제가 되는 부분

열 암호화 이후에 insert 된 데이터는 공백 없이 조회가 된다. 

빈 테이블을 열 암호화해서 사용할 경우 문제가 없겠지만 데이터가 있는 상태에서 열 암호화 해서 사용할 경우

서비스에서 데이터 조회하는 부분에서 이슈가 된다. (데이터가 입력된 시점에 따라 어떤건 조회되고 어떤건 안되고..)

해결방안

- trim() 해서 업데이트 하면되지 않아? 라고 생각할 수 있다.

  여러가지 방법으로 trim() 해보려고 했지만 쿼리나 프로시져로는 안된다. 

  정리하기 힘들정도로 여러가지 형태의 쿼리, 프로시져를 짜서 삽질을 해봤다.  안된다. 

- set ansi_padding off 를 적용해서 임시 테이블을 만들어서 데이터를 입력하고 열 암호화를 적용하면 되지 않을까?

  char 는 set ansi_padding off 가 먹히지만 nchar 는 안먹힌다. 

  문제되는 대부분의 컬럼이 nchar 다.. 

  컬럼 타입을 char 나 nvarchar 로 바꿀까? 안된데.. 못 바꾼데.. 

- 데이터를 다 읽어서 다시 update 하는 어플리케이션을 만들어서 돌려야된다.

  이거 말고는 답을 못 찾았다. ㅡ_ㅡ);

SQL Server 2016 에 추가된 기능으로 특정 컬럼을 암호화할 수 있는 기능이다.

이때만 해도 내가 이걸 쓰게될줄은 몰랐다. 

시작하기전에 아래의 몇가지 사항을 이해하고 넘어가면 좋다. 

- 열 마스터 키 (CMK) : 열 암호화 키를 보호하는 암호화 키

- 열 암호화 키 (CEK) : 암호화된 열을 보호하는 암호화 키

- 열 마스터 키의 저장소 

   - Azure Key Vault : Microsoft Azure 내에서 중요한 데이터의 암호화 및 암호 해독에 사용되는 암호화 키 및 비밀을 보호하고 관리하는 데 사용되는 키 자격 증명 모음 (주의사항 이걸 사용하다 그만 써야지 하면 뒷감당이 힘들다.)

  - Windows 인증서 저장소 : 인증서를 저장하고 관리하는 Windows에 내장된 인증서 컨테이너

  - HSM : 민감한 데이터를 안전하게 저장하도록 특별히 설계된 하드웨어 장치 

- 열 수준 암호화 유형
  - 결정적 : 항상 동일한 암호문으로 암호화하므로 특정 작업(포인트 조회, 구별, 그룹화 기준)에 사용할 수 있고 인덱싱가능
  - 무작위 : 더 안전하지만 어떤 작업에서도 평가하거나 사용할 수 없으며(쓰기/표시 전용) 인덱싱불가

1. 열 마스터 키 만들기 (column master key)

- 개체 탐색기에서 데이터베이스를 선택
- 보안 -> Always Encrypted 키 -> 열 마스터 키 
  새 열 마스터 키 생성 

- 키 저장소 Windows 인증서 저장소 - 로컬 컴퓨터를 선택
  인증서 생성 

- 인증서 생성 버튼 클릭시 인증서가 생성되며 생성된 정보가 노출된다. 

- Windows 인증서 저장소에 인증서가 생성되어 있는지 확인 

2. 열 암호화 키 생성 (column encryption key)

- 개체 탐색기에서 데이터베이스를 선택
- 보안 -> Always Encrypted 키 -> 열 암호화 키 
  새 열 암호화 키 생성 

- 새 열 암호화키 생성 
  생성한 열 마스터 키를 선택하고 열 암호화 키 이름을 입력한다. 

3. 테스트 데이터 생성

CREATE TABLE [dbo].[tb_test]
(
 [Seq] [int] IDENTITY(1,1), 
 [Name] [nvarchar](20) not null, 
 [Phone] [varchar](20) not null, 
 [Email] [varchar](50) not null,
 PRIMARY KEY CLUSTERED ([Seq] ASC)
)
GO

insert into [dbo].[tb_test] values ('홍길동','010-1234-5678','hhhhh@email.com');
insert into [dbo].[tb_test] values ('김길동','010-1234-1234','kim@email.com');
insert into [dbo].[tb_test] values ('최길동','010-5678-5678','choi@email.com');
insert into [dbo].[tb_test] values ('강길동','010-5678-1234','kang@email.com');
insert into [dbo].[tb_test] values ('한길동','010-5678-5678','han@email.com');
GO

select * from [dbo].[tb_test];

4. 열 암호화 

- 개체 탐색기에서 데이터베이스를 선택
   태스크 -> 열 암호화 

- 암호화할 열 선택 

5. 데이터 확인 (데이터베이스 서버에서)

- 암호화한 컬럼은 데이터가 암호화되어 확인이 안된다. 

- 새 세션으로 접속시 Always Encrypted -> Always Encrypted(열 암호화) 사용 선택후 접속 

- 데이터 조회시 오류가 발생한다. 

6. Windows 인증서 저장소에 저장된 열 마스터 키 만들때 사용한 인증서 내보내기 

- 열 마스터 키를 만들때 사용한 인증서를 내보낸다. 

7. 인증서 파일을 클라이언트로 복사하여 등록

- 인증 파일을 더블클릭하면 인증서 가져오기 마법사가 시작된다. 
  인증서 저장소 위치를 로컬 컴퓨터로 지정한다. 

- 인증서 내보내기에서 지정한 암호를 입력한다.

8. 인증서를 가져온 후 클라이언트에서 데이터 조회

테이블 생성시 데이터 타입 char(n), nchar(n) 으로 생성한 컬럼의 경우 입력한 데이터의 길이거 n보다 짧은 경우 뒤에 공백이 들어간다.

예) char(10) 에 'A' 를 넣으면 'A          ' 가 들어간다.

문제는.. char(10) 에 'A' 를 넣어야되는 상황이 발생했다.

set ansi_padding off 를 설정하면 char(n) 데이터 타입에도 공백이 들어가지 않는다. 

https://docs.microsoft.com/ko-kr/sql/t-sql/statements/set-ansi-padding-transact-sql 

-- 테스트 테이블 생성 
create table tb_test (
   colA char(10),
   colB varchar(10),
   colC nchar(10),
   colD nvarchar(10)
);

-- 테스트 데이터 입력 
insert into tb_test (colA, colB, colC, colD) values ('A','B','C','D');

-- 데이터 확인 
select 
	colA + '*',
	colB + '*',
	colC + '*',
	colD + '*'
from tb_test;

char(10), nchar(10) 은 공백이 들어간다. 

set ansi_padding off 테스트 

-- 테스트 테이블 삭제 
drop table tb_test;

set ansi_padding off;
-- 테스트 테이블 생성 
create table tb_test (
   colA char(10),
   colB varchar(10),
   colC nchar(10),
   colD nvarchar(10)
);
-- 테스트 데이터 입력
insert into tb_test (colA, colB, colC, colD) values ('A','B','C','D');
-- 데이터 확인 
select 
	colA + '*',
	colB + '*',
	colC + '*',
	colD + '*'
from tb_test;

char(10) 은 해결되었으나 nchar(10) 은 공백이 들어간다. 

BOL 을 보면.. nchar 는 안되는구나 ㅠㅠ