MS SQL2014.04.28 09:21

 

 

[내용]

   

최근 보안에 대한 이슈가 대두되고 있는 가운데, SQL Server 2014에서는 Backup Encryption 기능이 생겼습니다. 백업을 받을 때, 암호화를 통하여 백업 파일을 보호 할 수 있는 기능이 바로 Backup Encryption 기능입니다. 해당 기능에 대해 알아 보도록 하겠습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   


[Test]

   

--master key 생성합니다.

USE master;

GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'P@ssw0rd';

   

--백업 암호화를 위한 인증서 생성합니다.

CREATE CERTIFICATE BackupEncryptCert

WITH SUBJECT = 'Backup Encryption Certificate';

GO

   

   

--테스트를 위한 데이터베이스를 생성합니다.

CREATE DATABASE EncTestDB

GO

   

USE EncTestDB

GO

   

CREATE TABLE t1 (id INT PRIMARY KEY IDENTITY, strA VARCHAR(1000))

GO

   

SET NOCOUNT ON

INSERT INTO t1 VALUES(REPLICATE('a', 1000))

GO 10000

   

   

-- 테스트 데이터베이스 정보를 확인 합니다.

sp_helpdb EncTestDB

go

   

   

--암호화 백업을 진행합니다.

BACKUP DATABASE EncTestDB

TO DISK = N'C:\Backup\EncTestDB_enc.bak'

WITH

ENCRYPTION

(ALGORITHM = AES_256, SERVER CERTIFICATE = BackupEncryptCert )

GO

   

   

   

--테스트 DB를 삭제합니다.

USE MASTER

GO

   

DROP DATABASE EncTestDB

GO

   

   

   

--인증서가 없는 경우 상황 재현을 위해서 미리 인증서 백업을 받습니다.

BACKUP CERTIFICATE BackupEncryptCert TO FILE = 'c:\BackupEncryptCert.cer'

WITH PRIVATE KEY (

FILE = 'c:\pk.key',

ENCRYPTION BY PASSWORD = 'Pa$$w0rd');

GO

   

--인증서 삭제

DROP CERTIFICATE BackupEncryptCert

GO

   

   

   

--데이터베이스 복원을 시도합니다.

RESTORE DATABASE EncTestDB FROM DISK = N'C:\Backup\EncTestDB_enc.bak'

GO

   

아래와 같이 인증서가 없기 때문에 실패 합니다.

   

   

--삭제한 인증서를 복원 합니다.

CREATE CERTIFICATE BackupEncryptCert

FROM FILE = 'C:\BackupEncryptCert.cer'

WITH PRIVATE KEY (

FILE = 'C:\pk.key'

, DECRYPTION BY PASSWORD = 'Pa$$w0rd'

)

GO

   

--이제 데이터베이스 복원을 시도합니다.

RESTORE DATABASE EncTestDB FROM DISK = N'C:\Backup\EncTestDB_enc.bak'

GO

   

아래와 같이 DB가 복원된 것을 확인할 수 있습니다.

   

   

[참고]

   

http://msdn.microsoft.com/ko-kr/library/dn449489(v=sql.120).aspx

   

   

신고
Posted by TM ~ing
MS SQL2014.04.25 14:24

 

 

[내용]

   

SQL Server 2014에서는 Windows Azure Blob 저장소 서비스를 백업 대상으로 사용할 수 있습니다. 백업을 위한 대용량의 스토리지 구매가 어려운 회사 입장에서는 유용한 기능이 될 것 같습니다. Azure 가입은 이미 되어 있다는 가정하에 백업을 진행해 보겠습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   

[Windows Azure 가입 방법]

   

http://blogs.msdn.com/b/jspark/archive/2013/11/25/gm-1.aspx

http://blogs.msdn.com/b/jspark/archive/2013/11/25/14-gm.aspx

   

   

[Backup Test]

   

use master

go

   

--자격 증명을 생성합니다.

CREATE CREDENTIAL AzureCredential

WITH IDENTITY = 'etlee'

,SECRET = '저장소 액세스 키';

go

   

--생성한 자격 증명을 가지고, Windows Azure 저장소 URL에 백업을 진행합니다.

BACKUP DATABASE AdventureWorksDW2012

TO URL = 'http://etlee.blob.core.windows.net/sqltestbackup/AdventureWorksDW2012.bak'

WITH CREDENTIAL = 'AzureCredential'

,STATS = 5

, compression

   

Windows Azure 컨테이너에서 백업파일 저장된 것을 확인할 수 있습니다.

   

또는 개체 탐색기 - Azure 저장소를 클릭합니다.

   

저장소 계정과 계정 키를 입력하고 연결을 클릭합니다.

   

   

[Restore Test]


개체 탐색기 - 데이터 베이스 우클릭 - 데이터베이스 복원을 클릭합니다.

   

장치 선택 - 우측 버튼을 클릭합니다.

   

백업 미디어 유형을 URL로 변경하고, 추가를 클릭합니다.

   

저장소 계정, 계정 키, 해당 저장소의 자격 증명을 선택하고 연결을 클릭합니다.

   

복원 시킬 백업 파일을 찾아 확인을 클릭합니다.

   

대상에 데이터베이스 이름을 적절히 넣고, 확인을 클릭합니다.

   

복원이 완료 되었습니다.

   

   

[참고]

   

http://msdn.microsoft.com/ko-kr/library/dn435916(v=sql.120).aspx

 

신고
Posted by TM ~ing
MS SQL2014.04.24 09:20

 

 

[내용]

   

SQL Server 2012에서 부터 Columnstore 인덱스가 나오기 시작했습니다. 하지만 2012 버전에서는 Nonclustered Index만 지원을 했습니다. 2014 버전 부터는 Clustered Conlumnstore 인덱스를 지원하기 시작했습니다. 추가로, 2012에서는 데이터 변경이 불가능 했으나 2014부터는 데이터 변경이 가능하게 되었습니다. 그럼 Clustered Columnstore Index에 대해 좀 더 알아 보도록 하겠습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   

   

[Test]

   

-- 데이터 베이스에 연결합니다.

USE AdventureWorksDW2012;

GO

   

--테스트용 테이블을 생성합니다.

SELECT *

INTO dbo.FactResellerSales_CCI

FROM dbo.FactResellerSales

GO

   

--인덱스 생성 전에 테이블 크기와 인덱스를 확인합니다.

exec sp_spaceused FactResellerSales_CCI

   

select * from sys.indexes where object_id = object_id('FactResellerSales_CCI')

   

--이제 Clustered Columnstore Index를 생성합니다.

CREATE CLUSTERED COLUMNSTORE INDEX CCI_FactResellerSales_CCI ON FactResellerSales_CCI

--인덱스 생성 후 테이블 크기와 인덱스를 확인합니다.

exec sp_spaceused FactResellerSales_CCI

   

select * from sys.indexes where object_id = object_id('FactResellerSales_CCI')

   

--테이블의 내용을 수정해 봅니다.

update FactResellerSales_CCI set UnitPrice = UnitPrice * 10

insert into FactResellerSales_CCI select top 1 * from FactResellerSales

   

--테스트 쿼리로 ColumnStoreindex를 잘 사용하는 확인해봅니다.

SELECT SalesTerritoryKey, SUM(ExtendedAmount) AS SalesByTerritory

FROM FactResellerSales_CCI

GROUP BY SalesTerritoryKey;

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/gg492088(v=sql.120).aspx

http://msdn.microsoft.com/en-us/library/gg492153(v=sql.120).aspx

 

 

신고
Posted by TM ~ing
MS SQL2014.04.23 12:03

   

   

[내용]

   

SQL Server 2014에서 부터는 파티션 Index Rebuild, 파티션 Switch 시, Online 작업이 가능 합니다.

이런 Online 작업 시에 잠금의 우선 순위를 정하는 옵션이 새롭게 추가 되었습니다.

   

   

[환경]

   

OS: Windows Server 2012 R2 Ent

Product: SQL Server 2014 RTM

   

   

[Test1]

   

--세션1

use AdventureWorksDW2012

go

   

-- 파티션 테이블을 만들기 위하여, 파티션 함수를 만듭니다.

CREATE PARTITION FUNCTION [ByOrderDateMonthPF](int) AS RANGE RIGHT

FOR VALUES (

20050701, 20050801, 20050901, 20051001, 20051101, 20051201,

20060101, 20060201, 20060301, 20060401, 20060501, 20060601,

20060701, 20060801, 20060901, 20061001, 20061101, 20061201,

20070101, 20070201, 20070301, 20070401, 20070501, 20070601,

20070701, 20070801, 20070901, 20071001, 20071101, 20071201,

20080101, 20080201, 20080301, 20080401, 20080501, 20080601,

20080701, 20080801, 20080901, 20081001, 20081101, 20081201

)

GO

   

-- 파티션 테이블을 만들기 위하여, 파티션 구성표를 만듭니다.

CREATE PARTITION SCHEME [ByOrderDateMonthRange]

AS PARTITION [ByOrderDateMonthPF]

ALL TO ([PRIMARY])

GO

   

-- 파티션 구성표에 의거한, 테이블을 생성합니다.

CREATE TABLE [dbo].[FactResellerSalesPtnd](

[ProductKey] [int] NOT NULL,

[OrderDateKey] [int] NOT NULL,

[DueDateKey] [int] NOT NULL,

[ShipDateKey] [int] NOT NULL,

[CustomerKey] [int] NOT NULL,

[EmployeeKey] [int] NOT NULL,

[PromotionKey] [int] NOT NULL,

[CurrencyKey] [int] NOT NULL,

[SalesTerritoryKey] [int] NOT NULL,

[SalesOrderNumber] [nvarchar](20) NOT NULL,

[SalesOrderLineNumber] [tinyint] NOT NULL,

[RevisionNumber] [tinyint] NULL,

[OrderQuantity] [smallint] NULL,

[UnitPrice] [money] NULL,

[ExtendedAmount] [money] NULL,

[UnitPriceDiscountPct] [float] NULL,

[DiscountAmount] [float] NULL,

[ProductStandardCost] [money] NULL,

[TotalProductCost] [money] NULL,

[SalesAmount] [money] NULL,

[TaxAmt] [money] NULL,

[Freight] [money] NULL,

[CarrierTrackingNumber] [nvarchar](25) NULL,

[CustomerPONumber] [nvarchar](25) NULL,

OrderDate [datetime] NULL,

DueDate [datetime] NULL,

ShipDate [datetime] NULL

) ON ByOrderDateMonthRange(OrderDateKey);

GO

   

--파티션된 테이블에 데이터를 입력합니다.

INSERT INTO dbo.FactResellerSalesPtnd WITH(TABLOCK)

SELECT * FROM dbo.FactResellerSales

GO

   

   

--PK 생성 및 NCI를 생성합니다.

alter table FactResellerSalesPtnd add constraint PK_FactResellerSalesPtnd_SalesOrderNumber_SalesOrderLineNumber primary key (OrderDateKey, SalesOrderNumber, SalesOrderLineNumber)

GO

   

create index IX_FactResellerSales_ShipDateKey on FactResellerSalesPtnd(ShipDateKey)

GO

   

exec sp_helpindex FactResellerSalesPtnd

   

--파티션(4) 인덱스 리빌드를 온라인으로 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 4

WITH ( ONLINE = ON)

GO

   

   

[Test2]

   

--잠금 우선 순위를 낮게 설정 하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = SELF )))

GO

   

   

[Test3]

   

--세션2에서 파티션(5)에 대한 X잠금을 설정합니다.

begin tran

update FactResellerSalesPtnd set ShipDateKey = ShipDateKey + 1 where OrderDateKey = 20051001

   

--세션1에서 잠금 우선 순위를 낮게 설정 하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = SELF )))

GO

   

-- 아래와 같이 인덱스 리빌드 작업이 취소 된 것을 확인할 수 있습니다.

   

-- 세션1에서 ABORT_AFTERWAIT 옵션을 blockers로 변경하고, 리빌드 작업을 진행합니다.

ALTER INDEX IX_FactResellerSales_ShipDateKey ON dbo.FactResellerSalesPtnd

REBUILD Partition = 5

WITH ( ONLINE = ON (WAIT_AT_LOW_PRIORITY (MAX_DURATION = 1 minutes, ABORT_AFTER_WAIT = blockers )))

GO

   

-- 아래와 같이 인덱스 리빌드 작업이 완료 된것을 확인할 수 있습니다.

   

[Test4]

   

--데이터베이스로 이동합니다.

use AdventureWorksDW2012

go

   

--Switch 대상 테이블 생성합니다.

SELECT * INTO FactResellerSalesPtnd_SWITCH FROM FactResellerSalesPtnd WHERE 1=0;

go

alter table FactResellerSalesPtnd_SWITCH add constraint PK_FactResellerSalesPtnd_SWITCH primary key (OrderDateKey, SalesOrderNumber, SalesOrderLineNumber);

go

   

SP_HELP '[dbo].[FactResellerSalesPtnd_SWITCH]'

   

--파티션(2)를 switch 대상 테이블로 swith 합니다.

ALTER TABLE FactResellerSalesPtnd SWITCH PARTITION 2 TO FactResellerSalesPtnd_SWITCH

WITH ( WAIT_AT_LOW_PRIORITY (MAX_DURATION = 10 minutes, ABORT_AFTER_WAIT = SELF ))

GO

   

   

[추가 설명]

   

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/ms188388(v=sql.120).aspx

   

   

신고
Posted by TM ~ing
MS SQL2014.04.22 10:30

 

   

[내용]

   

MSSQL Server 2014에서 가장 눈에 띄는 In-Memory OLTP라는 새로운 기능 외에도 다양한 기능들이 추가 되었습니다. 새롭게 등장한 기능들은 아래와 같이 있으며, 다음 글 부터 이러한 기능들을 사용하는 글을 블로깅 해보도록 하겠습니다.

   

   

[New Features]

   

   

   

[참고]

   

http://msdn.microsoft.com/en-us/library/bb510411(v=sql.120).aspx

   

신고
Posted by TM ~ing

티스토리 툴바