Skip to main content

Posts

Showing posts from April, 2014

SQL 2014 - Encrypted backup overhead

In SQL 2014 there is a new feature in database backups - an encrypted backup. To encrypt backups you need to create a database master key for the MASTER database and either a certificate (as in example below) or an asymmetric key to use for the backup. Here is a script to backup the IndexDemo database with an encryption. USE master; GO CREATE MASTER KEY ENCRYPTION BY PASSWORD = '1qaz!QAZ'; GO IF EXISTS (select * from sys.certificates where name = 'TestBackupCert') DROP CERTIFICATE TestBackupCert; CREATE CERTIFICATE TestBackupCert WITH SUBJECT = 'Backup Encryption Certificate'; GO BACKUP DATABASE [IndexDemo] TO DISK = 'IndexDemoDB.bak'  WITH FORMAT, STATS=5 , ENCRYPTION (ALGORITHM = TRIPLE_DES_3KEY, SERVER CERTIFICATE = TestBackupCert);   -- AES_128, AES_192, AES_256, TRIPLE_DES_3KEY GO I was wondering how much overhead the encryption algorithms would add and decided to test it on my IndexDemo database which was hosted on a Azur

SQL 2014 performance - Local disk vs. Azure Blob vs. Azure VM attached disk

Today I decided to compare SQL 2014 (RTM) performance when running a test against  - a local database (created on  WD7500BPKT - 750 GB 7200 RPM)  - a DB created on a disk attached to the A3 (3 cores, 7 GB) VM in Azure - a DB created on an Azure blob The VM in Azure was created in the US East region using the SQL 2014 template from the gallary provided by Microsoft. All databases were created with a single 10 GB data file (10 GB) and 1GB  log file. On the local SQL instance the DB was created as CREATE DATABASE TestDBonLocal ON     (NAME = file_data1, FILENAME = 'C:\TEMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 MB )  LOG ON  (NAME = file_log1, FILENAME = 'C:\TEMP\filelog1.ldf', SIZE = 1GB, FILEGROWTH = 32 MB)  On the Azure VM the database on the attached disk (the disk was mapped as drive F:) was created as such CREATE DATABASE TestDBonDisk ON     (NAME = file_data1, FILENAME = 'F:\TMP\filedata1.mdf', SIZE = 10GB, FILEGROWTH = 256 M