여기까지 하면 db connection이 생성될 때 Always Encryed 설정이 활성화 된다. 하지만 CEK가 설정된 Table을 읽어 오지는 못한다. 제대로 값을 읽어 오기 위해서는 CEK Provider를 서비스 시작 시점에 설정을 해주어야 한다.
CEK Provider는 Program.cs에 다음과 같이 Provider를 구성해주면 된다.
var azureKeyVaultProvider = new SqlColumnEncryptionAzureKeyVaultProvider(new DefaultAzureCredential());
var dics = new Dictionary<string, SqlColumnEncryptionKeyStoreProvider>();
dics.Add(SqlColumnEncryptionAzureKeyVaultProvider.ProviderName, azureKeyVaultProvider);
SqlConnection.RegisterColumnEncryptionKeyStoreProviders(dics);
이제, 아래와 같이 간단히 앞서 암호화 했던 Users 테이블을 select해 보면 복호화 된 데이터를 확인 할 수 있다.
await using var conn = (SqlConnection)_context.Database.GetDbConnection();
await conn.OpenAsync();
await using var cmd = new SqlCommand("select * from Users", conn);
await using var reader = await cmd.ExecuteReaderAsync();
await reader.ReadAsync();
var result = $"{reader[0]}, {reader[1]}, {reader[2]}";
//id, email, name, registered date
//1, abc@gmail.com, abc, 2023-01-01 00:00.000
데이터를 저장 및 관리 하다 보면, 민감한 정보를 아무나 볼 수 없도록 식별 불가능하게 하도록 비식별화 작업을 해야 할 때가 있다.
데이터 비식별화를 하는 방법은 여러가지가 있지만, 그 중에서 데이터를 암호화하는 방법을 Azure SQL의 Always Encrypted 기능을 사용해서 구현해보려고 한다.
Always Encryted 기능은 Azure SQL에 저장된 민감한 데이터를 암호화 시킬 수 있고, 암호화에 사용된 암호화 키를 DB engine과 공유하지 않기 때문에 권한이 있는 클라이언트만 실제 데이터를 확인 할 수 있다. 그래서 권한에 따라서 데이터를 볼 수 있는 사람과 없는 사람을 명확히 구분할 수 가 있게 된다.
SQL Server Management Studio(SSMS)를 실행시킨다. Object Explore에서 DB Instance를 선택하고, Security 폴더로 이동한다. Always Encryted Key 폴더 밑에 Column master key폴더를 우클릭하여 master key 추가 메뉴를 클릭한다.
key store를 auzre keyvault를 선택하면, AAD Login을 해야한다. 정상적으로 로그인 하게 되면, subscription과 keyvault 리소스를 선택할 수 있다. 위에서 만든 RSA 키 정보를 찾아서 추가해준다.
여기까지 하면 Column master key가 생성된 것을 확인 할 수 있다.
Create CEK
이제 암호화를 대상이 되는 테이블을 우클릭 하면 Column Encryption key를 추가할 수 있는 메뉴를 확인 할 수 있다.
암호화를 해야 하는 Column들을 선택하고 위에서 만들 Master key를 사용해서 Column들을 암호화 한다.
이제 Table을 조회해 보면 해당 Column(email, name)의 정보가 식별할 수 없도록 암호화된 것을 확인 할 수 있다.
이 정보를 다시 복호화 하기 위해서는 SQL master 계정이나 Keyvault에 Cryptographic Operations 권한이 있는 계정을 사용해야 하며 Connection 정보에 “Column Encryption Setting = Enabled” 옵션을 추가해서 접근을 하면 복호화된 정보를 얻을 수 있다.
Azure SQL(이하 sql)의 data를 ElasticSearch(이하 es)로 검색을 하기 위해서 sql 데이터를 es의 index로 옮겨야 한다. 데이터를 옮기는 방법은 여러가지 방법이 있지만 일반적으로 많이 쓰이는 데이터 처리 오픈소스인 Logstash를 사용하여 옮기는 방법을 알아보자.
Logstash를 운영하는 방식도 다양하지만 간단하게 사용하려면 역시 K8S만 한 것이 없기 때문에 K8S에 구성해보자.
FOR XML could not serialize the data for node ‘[ Column name]’ because it contains a character (0x001F) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.
XML로 변활 할 대상 데이터에 ASCII control code가 포함되어 있어서 발생하는 경우이다. 그래서 대상 데이터 안에 포함 되어 있는 해당 아래 스크립트 같이 XML로 변환 전에 모두 치환해 주어야 한다.
SELECT
REPLACE([Column name], char(0), '')
FROM
[Table Name]
Control code를 삭제하는 Function을 만들어 두면 필요할 때 가져다 쓰면 좋다.
CREATE FUNCTION [dbo].[FN_ReplaceControlCharacter]
(
@mString NVARCHAR(MAX)
)
RETURNS NVARCHAR(MAX)
AS
BEGIN
SET @mString = REPLACE(@mString, char(0), '')
SET @mString = REPLACE(@mString, char(1), '')
SET @mString = REPLACE(@mString, char(2), '')
SET @mString = REPLACE(@mString, char(3), '')
SET @mString = REPLACE(@mString, char(4), '')
SET @mString = REPLACE(@mString, char(5), '')
SET @mString = REPLACE(@mString, char(6), '')
SET @mString = REPLACE(@mString, char(7), '')
SET @mString = REPLACE(@mString, char(8), '')
SET @mString = REPLACE(@mString, char(9), '')
SET @mString = REPLACE(@mString, char(10), '')
SET @mString = REPLACE(@mString, char(11), '')
SET @mString = REPLACE(@mString, char(12), '')
SET @mString = REPLACE(@mString, char(13), '')
SET @mString = REPLACE(@mString, char(14), '')
SET @mString = REPLACE(@mString, char(15), '')
SET @mString = REPLACE(@mString, char(16), '')
SET @mString = REPLACE(@mString, char(17), '')
SET @mString = REPLACE(@mString, char(18), '')
SET @mString = REPLACE(@mString, char(19), '')
SET @mString = REPLACE(@mString, char(20), '')
SET @mString = REPLACE(@mString, char(21), '')
SET @mString = REPLACE(@mString, char(22), '')
SET @mString = REPLACE(@mString, char(23), '')
SET @mString = REPLACE(@mString, char(24), '')
SET @mString = REPLACE(@mString, char(25), '')
SET @mString = REPLACE(@mString, char(26), '')
SET @mString = REPLACE(@mString, char(27), '')
SET @mString = REPLACE(@mString, char(28), '')
SET @mString = REPLACE(@mString, char(29), '')
SET @mString = REPLACE(@mString, char(30), '')
SET @mString = REPLACE(@mString, char(31), '')
RETURN @mString
END
0부터 31까지 순차적으로 있어서 While Loop를 사용하면 간결하게 보일 수 있긴 하지만, 데이터가 많은 경우(ex 100만개 이상의 row) 2배 이상의 속도 차이가 날 수 있다. 성능을 고려해야 한다면, 최대한 필요한 Control code를 특정 지어서 직접 치환하는 것을 권장한다.
Custom type을 정의하면 Stored procedure(이하 SP)에 List(or Array) 형태의 정보를 전달하여 처리 할 수 있다.
먼저, List로 전달할 정보를 담을 Custom table type을 정의한다. 객체 정보를 담는 Class를 정의한다고 생각하면 된다.
CREATE TYPE CodeList
AS TABLE
(
Code varchar(32)
);
SP에 변수를 type을 위에서 만든 Custom type으로 정의한다. 그러면, SP는 정의된 Table 정보를 받아 처리 할 수 있게 된다.
CREATE PROCEDURE GetInfoByCodes
@pCodeList AS CodeList READONLY
AS
BEGIN
SET NOCOUNT ON;
SELECT * FROM InfoTable
WHERE Code in (SELECT * FROM @pCodeList)
END
GO
임의로 Custom table type으로 만든 변수를 만들어 다음과 같이 SP를 실행해 볼 수 있다.
DECLARE @pCodes CodeList
INSERT INTO @pCodes VALUES('A40274208')
INSERT INTO @pCodes VALUES('A10028014')
INSERT INTO @pCodes VALUES('A56087115')
EXEC GetInfoByCodes @pCodeList = @pCodes
On-Prem이나 VM 형태로 사용하던 MSSQL Server를 Azure SQL로 Migration을 한다고 가정해보자. 기존에 사용하던 처리 성능에 맞춰 Cloud 상에 알맞은 수준의 리소스를 준비하고 옮겨야 할 것이다.
기존에 사용하던 SQL Server는 CPU와 Memory, Disk I/O등 Hardware적인 요소로 처리 성능을 나타낸다. 하지만 Azure SQL에서는 DTU(Database Throughput Unit)라는 단위를 사용하여 처리 성능을 나타내기 때문에 성능 비교가 쉽지가 않다.
DTU는 Hardware 성능 요소인 CPU, Disk I/O와 Database Log flush 발생양을 이용하여서 계산해낸 단위이다. 때문에 MSSQL Server에서 각 Metric들을 추출해 낼 수 있다면, DTU를 계산해 낼 수 있다. DTU 계산은 이미 Azure DTU Calculator라는 계산기가 제공되고 있기 때문에 추출해낸 Metric 값들을 설명대로 잘 넣어 주기만 하면 쉽게 확인해 볼 수 있다.
Metric을 추출하는 방법은 대상이 되는 SQL Server에서 DTU Calculator에서 제공하는 PowerShell Script를 관리자 권한으로 실행시켜 주기만 하면 된다. Script는 CPU, Disk I/O, Database Log flush에 대한 Metric들을 DTU Calculator에서 요구하는 형식으로 추출해준다. 때문에 일반적으로 On-Prem이나 VM 형태로 MSSQL Server를 사용하고 있다면, 계산해 내는데 별다른 어려움이 없을 것이다.
하지만 AWS RDS for MSSQL 같은 경우 MSSQL Server를 AWS에서 Cloud Service로 제공하기 위해 Wrapping한 서비스다보니 SQL Server에 대한 관리자 권한을 얻을 수가 없다. 이러한 이유 때문에 DTU Calculator에서 제공하는 PowerShell Script를 사용할 수 가 없다.
이런 경우, DTU 계산에 필요한 Metric들을 AWS에서 별도로 추출해야 한다. AWS에는 Cloudwatch라는 서비스를 제공하고 있는데, AWS 서비스들에 대한 Performance Metric을 기록하고 제공하는 역할을 한다.
Cloudwatch에서 metric을 추출하기 위해서는 AWS CLI를 사용하는 것이 편리하다. AWS Console의 우측 상단에 있는 CLI 실행 아이콘을 눌러 AWS CLI를 실행해 보자.
AWS CLI가 실행 되었다면, 아래 조회 명령어(list-metrics)를 실행시켜 제공되는 metric들에 대한 정보를 확인해 보자.
aws cloudwatch list-metrics --namespace AWS/RDS --dimensions Name=DBInstanceIdentifier,Value=[RDS for MSSQL Name]
잘 실행되었다면, cloudwatch에서 제공하는 Metric 리스트들을 확인 할 수 있다. 리스트 중 DTU계산에 필요한 CPU Processor Time과 Disk Reads/sec, Writes/sec에 값에 해당 하는 Metric 다음과 같다. (참고로, Log Bytes Flushed/sec에 해당하는 metric은 제공되지 않는다.)
생성한 csv 파일은 AWS CLI Storage에 저장 되어있다. 우측 상단의 Actions > Download File 메뉴를 사용하면 로컬 환경으로 다운 받을 수 있다.
3개의 파일 모두 다운 받아서 DTU 계산에 필요한 형식으로 맞춰준다.
% Processor Time = CPUUtilization
Disk Reads/sec = ReadIOPS
Disk Writes/sec = WriteIOPS
Log Bytes Flushed/sec에 해당하는 데이터가 없기때문에 0으로 넣어준다.
그림과 같은 형태로 구성될 것이다.
RDS for MSSQL metrics for DTU Calculator
이제, 한땀 한땀 준비한 데이터를 Azure DTU Calculator에 넣어 주기만 하면 되는데 한가지 주의할 점이 있다. 우리가 얻은 데이터는 RDS for MSSQL Server에 대한 정보이다. 때문에 여러 DB Instance에 대한 계산을 하는 Elastic Database 메뉴를 선택하여 계산 하도록 해야한다.
계산이 끝나면 아래와 같이 나오는데 이 결과를 통해서 AWS RDS에서 사용하던 성능 수준을 Azure SQL에서 그대로 사용하기 위해서 구성 해야하는 Service Tire/Performance Level을 확인 할 수 있다.
SQL 작업을 하다보면 조회된 쿼리 결과에 대해서 행 단위 작업이 필요할때가 있다. 이때, Cursor를 사용하면 효율적으로 처리 할 수 있다.
Cursor Command
DECLARE: Cursor에 관련된 선언을 하는 명령
OPEN: Cursor가 Query결과의 첫번째 Tuple을 포인트로 하도록 설정하는 명령
FETCH: Query 결과의 Tuple들 중 현재의 다음 Tuple로 커서를 이동시키는 명령
CLOSE: Query 수행을 모두 마친 후 Cursor를 닫기 위한 명령
DEALLOCATE: Close된 Cursor의 자원을 반환하는 명령
Cursor life cycle
Cursor Example
DECLARE @pColum_1 NVARCHAR(100), @pColum_2 INT, @pColum_3 BIT
DEClARE pCursor CURSOR
FOR
select * from [Target Table]
OPEN pCursor
FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3
WHILE(@@FETCH_STATUS <> -1)
BEGIN
update [Target Table] SET Colum = @Colum_1 + 1
where Colum_2 = @pColum_2 and Colum_3 = @pColum_3
FETCH NEXT FROM pCursor INTO @pColum_1, @pColum_2, @pColum_3
END
CLOSE pCursor
DEALLOCATE pCursor
-유의사항-
Cursor를 사용하게 되면 내부적인 임시테이블을 사용하기 때문에 많이 사용하게 되면 DB성능에 영향을 미치게 된다. 되도록 사용량이 적은 시간때이거나 적은양의 데이터를 처리할 때 사용하는 것이 바람직 하다.
