SQL Server 2016

Sifiso W. Ndlovu

Top 4 options for fixing Microsoft.AnalysisServices.AdomdClientUI.dll assembly error in SQL Server 2016

June 26, 2018 by

I’ve always been in favor of an orthodox strategy when it comes to applying SQL Server updates which often goes like:

  • Instead of installing SQL Server Cumulative Updates, wait for release of service packs
  • When a service pack is released, install it in phases starting from the non-production environment (i.e. DEV, UAT) to eventually roll it out on production
Read more »
Esat Erkec

How to use parallel insert in SQL Server 2016 to improve query performance

December 8, 2017 by

Introduction

In the first part of this article, we will discuss about parallelism in the SQL Server Engine. Parallel processing is, simply put, dividing a big task into multiple processors. This model is meant to reduce processing time.

  • SQL Server can execute queries in parallel
  • SQL Server creates a path for every query. This path is execution plan
  • The SQL Server query optimizer creates execution plans
  • SQL Server query optimizer decides the most efficient way for create execution plan

Execution plans are the equivalent to highways and traffic signs of T-SQL queries. They tell us how a query is executed.

Read more »
Prashanth Jayaram

How to configure Always Encrypted in SQL Server 2016 using SSMS, PowerShell and T-SQL

October 2, 2017 by

In an era of remote storage and retrieval of data, including the cloud, data security plays a vital role, especially since it’s vulnerable during the transit. Situations like database backup or copy from or to the cloud, there is always a risk of data exposure to outside world lurking around one corner or the other. We have seen a noticeable surge in the technologies around protection and security of data from the world full of unsafe hands. Efforts are being made to protect data at a very granular level of the encryption hierarchy. Protection of business data cannot be stressed upon more.

Read more »
Ahmad Yaseen

SQL Server 2016 Maintenance Plan Enhancements

September 18, 2017 by

SQL Server Maintenance Plans is a SQL Server Management Studio built-in feature that helps in creating a workflow of variant database administration tasks, which can be run automatically using a predefined schedule or manually triggered by the user.

SQL Server Maintenance Plans allow you to use typical database maintenance tasks or customize your own task using a T-SQL script that runs on the local server or group of SQL Servers, providing more flexibility to the database administration tasks.

Read more »
Ahmad Yaseen

In-Memory OLTP Enhancements in SQL Server 2016

August 22, 2017 by

SQL Server In-Memory OLTP, also known as Hekaton when it was introduced in SQL Server 2014, provides us with the ability to move specific database tables and suitable stored procedures into memory and compile the stored procedures into native x86 code. As a result, you can easily query these database objects directly from memory with the best performance and the least possible data access latency. In addition to that, the SQL Server Engine will no longer use the old latching and locking mechanism to control the data access concurrency. Instead, a high performance row versioning mechanism will be used to control the concurrency. This optimistic concurrency mechanism is 5 times to 20 times faster than the normal disk-based processing, due to reading the data from the memory directly.

Read more »
Prashanth Jayaram

How to Configure TDE database with AlwaysOn using the Azure Key Vault in SQL 2016

July 28, 2017 by

One of the recent tasks I undertook on configuring Transparent Data encryption (TDE) using asymmetric key protection with Azure Key Vault with Always On opened a different dimension on securing data for me. Even though it seems slightly complex, if you have the key details, the steps are in fact, really straight forward.

I strongly recommend going through this MSDN article on SQL Server Transparent Data Encryption TDE using Azure Key Vault in order to understand Azure Key Vault configuration and Integration

Background

In order to encrypt the database encryption key with an asymmetric key, use an asymmetric key that resides on an Extensible Key Management Provider. Extensible Key Management (EKM) is another new feature in SQL Server that adds the ability to store the encryption keys used for TDE on hardware, specifically designed for key security and management. Such devices are called High-Security Modules (HSM), whose vendors are referred to as EKM providers. A good Key Management Vendor should supply you with software libraries that easily add and implement Key Management in SQL Server encryption.

In asymmetric encryption, two different keys are used: A public key for encrypting, and a private key for decrypting the data. This type of asymmetric encryption is referred to as Public Key Infrastructure (PKI)- or Public Key Cryptography-based encryption.

Azure Key Vault

Azure Key Vault is a secure key management feature that is essential to secure and protect data in the Azure cloud. We use Azure Key Vault to encrypt keys and small secrets like passwords that use keys stored in hardware high-security modules (HSMs). For added assurance, import or generate keys in HSMs, and let Microsoft process your keys in FIPS 140-2 Level 2 validated HSMs (hardware and firmware).

Introduction

SQL Server running on an Azure Virtual Machine can use an asymmetric key from the Key Vault. When running SQL Server in an Azure VM, SQL Server can use keys stored in the Azure Key Vault using EKM. This article covers the role of a DBA in setting up SQL 2016 TDE database on Azure VM with Always On, using the Azure Key Vault. It also outlines the prerequisites and required details for seamless implementation of protecting the data using an asymmetric key.

Let’s proceed, with the assumption that we have the necessary details from the Azure Key Manager such as:

  • KeyValutName
  • Active Directory Application Client ID
  • Active Directory Client Secret
  • Key Encryption Key Name (KEK)

Let’s also add another set of assumptions from the DBA side:

  • Always-On Availability Group site that is already configured

For example,

Name Description Value
ResourceGroupName Key Vault Resource Group Name az-prod-sql-001
VaultName Key Vault Name az-kv-sql-001
AADApp AAD Application Name az-aadapp-kv-001
AADObjectID Azure Active Directory Application Client ID 2db602bd-4x4x-4322-8xxf-d128c143c8a9
AADClientSecret Active Directory Client Secret FZCzXY3K8RpZoK12MxF/WFxxAw6aOxxPU2ix
xEkQBbc=
Secret The SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together 2db602bd4x4x43228xxfd128c143c8a9FZCz
XY3K8RpZoK12MxF/WFxxAw6aOxxPU2ixxEkQBbc=
Key Encryption
Key Name
Key Encryption Key Name (KEK) az-kek-sql-001

SQL Server Connector

The SQL Server Connector for Microsoft Azure Key Vault enables SQL Server encryption to use the Azure Key Vault service as an Extensible Key Management (EKM) provider to protect SQL Server encryption keys.

Download the SQL Server Connector from the Microsoft Download Center, and follow the steps below to complete the installation.

  • Browse the installation folder
  • Click Next
  • Click the agree terms and license agreement radio button
  • Select the location for libraries to install
  • Click Next and then, Finish

By default, the connector installs at C:\Program Files\SQL Server Connector for Microsoft Azure Key Vault. This location can be changed during setup. (If changed, adjust the scripts below.) The Connector is the cryptographic EKM provider DLL that needs to be registered with SQL Server by using the CREATE CRYPTOGRAPHIC PROVIDER statement.

The SQL Server Connector installation also allows you to optionally download sample scripts to help with SQL Server encryption.

Configure SQL Server

In this section, we shall see how we could add a TDE-encrypted database to Always On Availability Group site that is already configured. Assume that we have configured the Always On Availability Group SQLAG2 that contains three replicas.

In the following screenshot, iServerReportingDB is going to be configure for TDE. Let’s see the steps to configure TDE and use of EKM on Always On setup.

On the Principal,

  • Setup EKM
  • Setup Credentials
  • Configure TDE
  • Add database to Always On availability group
  • Perform full database backup
  • Perform log backup

On the Secondary, the steps are a little different from the procedure above

  • Create the database
  • Setup EKM
  • Setup credentials
  • Configure TDE
  • Restore the database with replace option
  • Restore the log
  • Join the database to Always On availability group

Let’s look at all of the above steps in detail and configure TDE with asymmetric key on the Availability Group using Azure Vault

Check the database’s encryption configuration

This indicates that no databases are configured for TDE.

Step 1: EKM Setup

The below SQL query is used to create a cryptographic provider, using the SQL Server Connector which is an EKM provider for the Azure Key Vault. This example uses the name AzureKeyVault_EKM_Prov. Notice that the DLL location should be the taken from where you installed—this is the Connector path.


STEP 2: Setup Credentials

Create a credential from your Azure Active Directory Client ID and Secret that you can use to grant an SQL Server account access to your Azure key vault

  1. IDENTITY here is the name of your Azure key vault.

  2. SECRET here is your AAD Client ID (with the hyphens removed) and your AAD Client Secret concatenated together


Add the credentials to the SQL Server administrator’s domain login.


STEP 3: Create asymmetric Key and SQL Login

  • Use the EKM to open the asymmetric KEK
  • Key Encryption Key name

Step 4: Create SQL Server Login





Enable TDE on SQL Database by Using Transact-SQL

  • Connect to the master database

  • Execute the following SQL statement to encrypt the database


  • Monitor the progress of encryption,


STEP 5: Add Database to Always On Group

On the primary, add the database to the Availability Group using ALTER AVAILABILITY GROUP … ADD DATABASE


Backup the database (Full and Log backup)

  • Initiate full and log backup on the primary replica using the following SQL statements:



On the secondary,

  • Create the database iServerReportingDB
  • Move the database backup file to a location where Always On Replica #1 can restore the file
  • Run steps 1 through 4 from the last section
  • Repeat the process for each Always On Replica node
  • Restore the full database backup

  • Restore the log database backup

  • Join the database to the availability group using the ALTER AVAILABILITY GROUP T-SQL


The AlwaysOn Group status

The below screenshot shows that iServerReportingDB is configured successfully with EKM provider, Azure Key vault.

Conclusion

In an Always-On-with-Azure-Key-Vault scenario, enabling TDE on one or more secondary replicas is indeed a tedious task. One has to get the help of Azure Key Manager to get all the required key information.

This article articulates every step required to setup and configure asymmetric TDE with Always On using Azure Key Vault. If you are able to configure the setup on Primary then similar steps should be applied to Secondary, with a few steps being different (which are also covered in this article). You will be performing a backup and adding the database to Availability Group in case of the former, whereas you would restore the database and join the database to the Availability Group in the latter: the secondary nodes.

See more

Consider these Free tools and community editions for Azure SQL Database

References



Ahmad Yaseen

Compression and decompression functions in SQL Server 2016

June 2, 2017 by

The concept of data compression is not a new on for SQL Server Database Administrators , as it is was introduced the first time in SQL Server 2008. In that SQL Server version, you were able to configure the compression at the row and page levels on the table, index, indexed view or the partition. The row and page level compression is not the best choice in all cases, as it does not work well on the Binary Large Objects (BLOB) datatypes, such as the videos, images and text documents.

Read more »
Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCache\Content.Word\10.png

How to connect and use Microsoft SQL Server Express LocalDB

May 9, 2017 by

Microsoft SQL Server Express LocalDB is intended for developers, it is very easy to install and doesn’t require any complex configuration task to create an instance or to use the database. The Introduction on the installation of Local DB be found on “How to install Microsoft SQL Server Express LocalDB” article.

This article will show different ways of connecting to LocalDB and how to create and use an instance of LocalDB.

Read more »
Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\20.png

How to install Microsoft SQL Server Express LocalDB

May 9, 2017 by

Microsoft SQL Server Express LocalDB, a solution primarily intended for developers, is a lightweight version of SQL Server Express. It is very easy to install and set up. The installation copies a minimum set of files which are necessary to start SQL Server Database Engine. LocalDB supports the same T-SQL language and has the same limitations as SQL Server Express.

Read more »
Ahmad Yaseen

How to Configure Read-Only Routing for an Availability Group in SQL Server 2016

March 23, 2017 by

The SQL Server Always On Availability Groups concept was introduced the first time in SQL Server 2012 as an enterprise-level high availability and disaster recovery solution that will replace the database mirroring feature. Always On Availability Group provides a high availability solution on the groups level, where each group can contain any number of databases that can be replicated to multiple secondary servers known as Replicas.

Read more »

Some uncommon but useful T-SQL and Database Engine Enhancements in SQL Server 2016

March 20, 2017 by

SQL Server 2016 is the most advanced version of Microsoft’s Data Platform released yet. This is obviously my favorite one as it has tremendous capabilities and enormous features. These new additions not only enhance the productivity of its users (Database Developer, DBA or Application Developer), but also enable the enterprise to use its data more effectively and efficiently.

Read more »
Marko Zivkovic
C:\Users\Marko\AppData\Local\Microsoft\Windows\INetCacheContent.Word\JSON.PNG

How to import/export JSON data using SQL Server 2016

March 7, 2017 by

JSON is an abbreviation for JavaScript Object Notation. JSON is very popular and currently the most commonly used data exchange format. Most modern web and mobile services return information formatted as JSON text, all database web services, web browsers (Firefox, Internet Explorer) return results formatted as JSON text or accept data formatted as JSON. Since external systems format information as JSON text, JSON is also stored in SQL Server 2016 as text. You can use standard NVARCHAR columns to store JSON data in SQL Server 2016.

Read more »
Craig Porteous
C:\Dropbox\Blog Articles\Tabular_02.PNG

How to automate SSAS tabular model processing in SQL Server 2016

March 6, 2017 by

There are many ways to process your SSAS Tabular Model. This can be achieved in SSIS using the Analysis Services Execute DDL Task or manually, through Management studio GUI but to have a little fun & make the task more flexible I’m going to script this with ASSL/TMSL & build a notification round it. We can then schedule this as a step in a SQL agent job, call it from SSIS or PowerShell.

Read more »
Page 1 of 41234