Exporting Masked Data with Dynamic Data Masking

The SQL Herald | Databases et al…

Dynamic Data Masking is a presentation layer that got added to Azure SQL DB and SQL Server 2016. In a nutshell it prevents end users from seeing sensitive data, and lets administrators show some data (e.g. the last 4 digits of social security number) for verification purposes. I’m not going to focus too much on the specifics of data masking in this post—that’s a different topic. This is how once you have a masking strategy you can protect your sensitive data going to other environments.

Well at PASS Summit, both in our booth and during my presentation on security in Azure DB, another idea came up—exporting data from production to development, while not releasing any sensitive data. This is a very common scenario—many DBAs have to export sensitive data from prod to dev, and frequently it is done in an insecure fashion.

Doing this requires a little bit of trickery, as dynamic data masking does not work for administrative users. So you will need a second user.

First step—let’s create a database and a masked table.


FirstName varchar(100) MASKED WITH (FUNCTION = ‘partial(1,”XXXXXXX”,0)’) NULL,
LastName varchar(100) NOT NULL,
Phone# varchar(12) MASKED WITH (FUNCTION = ‘default()’) NULL,
Email varchar(100) MASKED WITH (FUNCTION = ’email()’) NULL);

INSERT Membership (FirstName, LastName, Phone#, Email) VALUES
(‘Roberto’, ‘Tamburello’, ‘555.123.4567’, ‘RTamburello@contoso.com’),
(‘Janice’, ‘Galvin’, ‘555.123.4568’, ‘JGalvin@contoso.com.co’),
(‘Zheng’, ‘Mu’, ‘555.123.4569’, ‘ZMu@contoso.net’);


USE msdb

ALTER ROLE db_datareader ADD MEMBER demoexport;
ALTER ROLE db_datawriter ADD MEMBER demoexport;

Next I’ll login as this user and select from the membership table.


From here, I’m going to (as the TestUser) take an export of the database. You can do this by selecting the Export Data Tier Application option from the tasks menu in Management Studio.


I won’t bore you with clicking through the process, but this will give you an export of your database, with the data masked. Your next step is to import the .bacpac file you created. In this case I’m going to the same instance, so I changed the database name.

Right click on “Databases” in SSMS and select “Import Data-tier application”. Import the file you created in the previous step.


Now try selecting as your admin user.


Boom, you’ve exported and imported masked data in your lower environments.

Circles and Squares–What Do They Mean in the Query Store?

Denny Cherry and I are on a tuning mission this week, and fortunately the customer has SQL Server 2016, so we don’t have to waste a lot of time finding out problematic queries in the system. However, we were a little bit confused when we saw a graphic in the query store diagram today.


You will note that plan 44 has two durations, and even though has a single a plan id, the table to the right of the plan summary window indicates two plans. You will note that one of the plans is represented by a circle, the other a square. So what does this mean?

If the query is circle that means it completed. When the icon is a square that represents query that is cancelled, but had a plan generated for it. In this case I cancelled the query in SSMS.

We can confirm by looking in the Query Store catalog views.


The second run of the query shows as aborted and is represented as such in the query.

Does In-Memory OLTP Work with Always Encrypted? (AKA That time Bob Ward asked us a question)

Last week was the PASS Summit, which is the biggest confab of SQL Server professionals on the planet (and educational as ever), Denny Cherry  (b|t) and I ran into Bob Ward (b|t) of Microsoft and of 500 level internals presentations. And for the first time ever, Bob asked us a question about SQL Server—of course we didn’t know the answer of the top of our heads, but we felt obligated to research it like we’ve made Bob do so many times. Anyone, the question came up a Bob’s internals session on Hekaton (In-Memory OLTP) and whether it supported the new Always Encrypted feature in SQL Server 2016. I checked books online, but could not find a clear answer, so I fired up SSMS and setup a quick demo.


Set up Hekaton

Let’s get started with setting up Hekaton.

— create database with a memory-optimized filegroup and a container. 
ALTER DATABASE imoltp ADD FILE (name=’imoltp_mod1′, filename=’c:\temp\imoltp_mod1′) TO FILEGROUP imoltp_mod  

In that code, I’m just doing the setup for In-Memory OLTP—you need to added a filegroup, as well as enable the MEMORY_OPTIMIZED_ELEVATE_TO_SNAPSHOT_ON.

Always Encrypted Setup

Next, we needed to setup up Always Encrypted.


In order to configure this, you’ll first need to setup a Column Master Key, and then a Column Encryption Key. I took a sample table from books online, and attempted to encrypt it.

CREATE TABLE [Employee_Encrypted](
    [BusinessEntityID] [int] NOT NULL IDENTITY(1,1) ,
    [NationalIDNumber] [char](11) COLLATE Latin1_General_BIN2
        ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
        COLUMN_ENCRYPTION_KEY = ColumnKey1)
        NOT NULL,
    [LoginID] [nvarchar](256) NULL,
    [BirthDate] [date]
        ALGORITHM = ‘AEAD_AES_256_CBC_HMAC_SHA_256’,
        COLUMN_ENCRYPTION_KEY = ColumnKey1)
        NOT NULL,
         CONSTRAINT [PK_Employee_Encrypted_BusinessEntityID] PRIMARY KEY NONCLUSTERED
    [BusinessEntityID] ASC

With that T-SQL (and a couple of attempts at getting my parentheses in the right place) it just ran. Nothing major to do—just create table. I confirmed this behavior with Jos de Bruijn, who is the program manager for Hekaton.

But What about Postgres?

What About Postgres?

Since I wrote my post yesterday about Oracle and SQL Server, I’ve gotten a lot of positive feedback (except for one grouchy Oracle DBA) on my post. That said, I should probably stay clear of Redwood Shores anytime soon. However there was one interesting comment from Brent Ozar (b|t)

Screen Shot 2016-08-20 at 12.05.36 PM

While Postgres is a very robust database that is great for custom developed applications, this customer has built a pretty big solution on top of SQL Server, so that’s not really an option.


However, let’s look at the features they are using in SQL Server and compare them to Postgres. Since this a real customer case, it’s easy to compare.

1. Columnstore indexes—Microsoft has done an excellent job on this feature, and in SQL Server 2016 new features like batch mode push-down drive really solid performance on large analytic queries. Postgres has a project for columnstore but it is not developed. There’s also this add-on feature https://www.citusdata.com/blog/2014/04/03/columnar-store-for-analytics/ which does not offer batch execution mode performance enhancements and frankly offers extremely mediocre performance.

You can compare this benchmark:


to the SQL Server one:

SQL Server 2016 posts world record TPC-H 10 TB benchmark

2. Always On Availability Groups—In this system design we are using readable secondaries as a method to deliver more data to customers. It doesn’t work for all systems, but in this case it works really well. Postgres has a readable secondary option, but it is far less mature than the SQL Server feature. For example, you can’t create a temp table in a readable secondary.

3. Analysis Service Tabular—There is no comparison here. Postgres has some OLAP functions that are comparable to windowing functions in T-SQL. Not an in-memory calculation engine.

4. R Services—You can connect R to Postgres. However, SQL Server’s R Services leverages the SQL Server engine to process data, unlike Postgres which uses R’s traditional approach of needing the entire dataset in memory. Once again, this would require a 3rd party plug in to work in Postgres.

5. While Postgres has partitioning, it is not as seamless as in SQL Server, and requires some level of application changes to support.


While I feel that SQL Server’s implementation of partitioning could be better, I don’t have to change any code to implement.

6. Postgres has nothing like the Query Store. There are data dictionary views that offer some level of insight, but the Query Store is a fantastic addition to SQL Server that helps developers and DBAs alike

7. Postgres has no native spatial feature. There is a plug-in that does it, but once again we are making an even bigger footprint of 3rd party add-ins to manage.

Postgres is a really good database engine, with a rich ecosystem of developers writing code for it. SQL Server on the other hand, is a mature product that has had a large push to support analytic performance and scale.

Additionally, this customer is leveraging the Azure ecosystem as part of their process, and that is only possible via SQL Server’s tight integration with the platform.

Please, Please Stop Complaining about SQL Server Licensing Costs and Complexity

Recently, I’ve seen a number of folks on twitter and in the blogosphere complaining about the complexity of SQL Server licensing. While it is a slightly complicated topic and nowhere near as simple as Azure SQL Database (need more perf? Spend more $€£), there are other products in our space like Oracle and SAP that make licensing SQL Server look like a piece of brioche.




And while talking about vendors who spend your hard earned licensing dollars on racing sailboats and MIG fighter planes, through a recent project, I’ve had the opportunity to make a direct comparison between the licensing cost for SQL Server and Oracle. These numbers are not from quotes (list price), however this is a real customer of mine, and the features they use. This customer was an early adopter of SQL Server 2016, and uses MANY of the features in the product. Most of which are cost options in Oracle.


SQL Server Oracle
Core Engine (16 cores)  $109,980.00 Database Engine  $380,000.00
Compression Advanced Compression  $92,000.00
Columnstore Database In-Memory  $184,000.00
Analysis Tabular OLAP  $184,000.00
R Services Advanced Analytics  $184,000.00
Partitioning Partitioning  $92,000.00
Query Store Tuning Pack  $40,000.00
Spatial Spatial  $140,000.00
Availability Groups Active Data Guard  $92,000.00
Total  $109,980.00  $1,388,000.00


When I see those numbers in Microsoft marketing slides, I sometimes wonder if they can be real, but then I put these numbers together myself. Granted you would get some discounts, but the fact that all of these features are built into SQL Server, should convince you of the value SQL Server offers. Pricing discounts are generally similar between vendors, so that is not really a point of argument. If you are doing a really big Oracle deal you may see a larger upfront discount, but you will still be paying your 23% support fees on that very large list price. (Software Assurance from Microsoft will be around 20%, but from a much lower base) Additionally, several of these features ae available in SQL Server Standard Edition. None of these features are in Oracle’s Standard Edition.

SQL Server 2016 Database Mail Not Working

One of the nice things about SQL Server 2016 is that .NET 3.5 is no longer required for installation. The .NET requirement wouldn’t have been problematic, but in order to add the .NET feature access to the Windows media was required (this was particularly painful in Azure VMs). Fortunately the product team eliminated the requirement as part of the release. Unfortunately, a few things broke–namely database mail, native log shipping, and distributed replay. You can identify this problem, by a couple of symptoms:

  • Your database mail log has no entries
  • You have messages with a status of “unsent’ in sysmail_allitems in MSDB

Screen Shot 2016-07-27 at 2.41.59 PM

  • If you try to execute the file C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\DatabaseMail.exe you get a .NET 3.5 error

This should be fixed in CU2, but there are reports below of it failing in SP1. The XML to put in the .config files is:

<?xml version=1.0 encoding=utf-8 ?>


  <startup useLegacyV2RuntimeActivationPolicy=true>

    <supportedRuntime version=v4.0/>

    <supportedRuntime version=v2.0.50727/>



SQL Server 2016 Automated Installs and a SQL 2016 Webinar

I hate installing things through a GUI–I’ve talked about this before at PASS Summit, and when I worked at big cable, we build a private cloud and fully automated the SQL Server installation process. When SQL Server 2014 launched, my process had virtually no changes, however a number of things broke it in SQL Server 2016 (mostly for the better)


  • TempDB installs with the right number of files–they are still too small though
  • The behavior of trace flags 1117 and 1118 are built in to TempDB so you don’t need to turn them on
  • SSMS is a now a separate installation and download
  • I used batch and I wanted to rewrite into PowerShell

So since this is 2016, I decided to share it on Github. You can find it here: https://github.com/DC-AC/SQL2016_Scripted_Install. Feel free to fork and make edits in the code, or tell me how crappy my PowerShell logic is. Read the readme–there’s a few assumptions about your configuration. I’d like to parameterize those over time.

One other thing I wanted to mention is that Denny Cherry and I are doing a webinar this Friday July 29th at 11 PDT/2 EDT talking about managing SQL Server 2016 in production. We’ve have a customer live for a year now, and will share what we’ve learned and answer your questions. You can sign up here.



What Happens to Pages in the Buffer Pool when your Availablity Group Fails Over?

Recently at SQL Saturday Philadelphia, we started discussing failover  as it relates to mirroring and Always On Availability Groups. Specifically, we were wondering what would happen if you had a relatively busy readable secondary replica (which would have a lot of pages in the buffer pool on the secondary instance) and if those pages would be flushed from cache or anything like that. So I reached out to the product group and Kevin Farlee from Microsoft was extremely helpful:

Pages in the buffer pool are still valid, as they are updated by the redo thread.  By the time the secondary has transitioned to primary, all pending updates will have been applied, so pages in the bufferpool will all have correct contents. Note that the set of pages in bufferpool may be different between primary and secondary so you may have some cache warm up to do.”

So in a nutshell, if you have a busy readable secondary your cache won’t take much time to warmup, since the hot pages are already there.

PASS Summit 2016 I’m Speaking

pass_2016_websiteI have been honored to be selected to speak at this October’s PASS Global Summit in Seattle this October. I will be speaking on a topic near and dear to my heart, Security in Azure SQL Database. I worked last year to write a white paper with Stacia Varga and Microsoft discussing the best practices and security for Azure SQL Database.

In this session we’ll talk about all the encryption features, the security certifications that Azure has, and how audit is better in Azure SQL Database than it is in SQL Server. Additionally, you’ll learn about some of the other enhancements Microsoft has made to protect your data in Azure.



SQL Server 2016—The Licensing Info

SQL Server 2016 launched last week to great reviews and with a ton of great new features. I have been working with this version for well over a year now and extremely happy to see it hit RTM and be broadly adopted. So as DBAs it always sucks when you get excited about new features, only to find out the price changed, or vendor “O” made that feature a cost option. So what’s new with SQL Server 2016 licensing? (you won’t this as a session title at any upcoming SQL Server events).  Well first the good news—SQL Server 2016 is the same price and 2012 and 2014 (roughly $6800 core for Enterprise Edition). That’s definitely good news—Microsoft gave us a bunch of new functionality and didn’t raise the price. Additionally, if you see my below post on what is in Standard Edition, they added a lot of functionality there, too.

But we know finance and marketing employees have jobs to do as well, and there is no way they were letting a major version release happen without some changes. So let’s take a look at the one’s Denny Cherry (b|t) and I could glean out of the licensing guide. Please download and read for yourself.

Licensing Changes

Additional licenses are required when:
 A single hardware thread is supporting multiple virtual cores. (A core license is required for each v-core.)
 Multiple hardware threads are supporting a single virtual core simultaneously. (A core license allows a
single v-core to be supported by a single hardware thread.)

What does this mean? It means you can’t over provision CPUs on your VMs (which you shouldn’t be doing for DB servers anyway). There may be something that applies to hyperthreading here, but if you are licensing individual VMs, you probably shouldn’t be using hyperthreads.

“Beginning with SQL Server 2016, deploying and running SQL Server PDW is done through SQL Enterprise
Edition Per Core licensing with SA coverage. The number of SQL Server Enterprise Edition core licenses for an
APS appliance will depend on the total number of physical cores in the SQL Server PDW compute servers
configured within the appliance.”

What does this mean? It means you can actually know what an APS costs, at least from a licensing perspective. Hardware costs will need to be gathered from a reseller. This is a good change as it makes SQL Server’s pricing consistent across platforms (pro-tip: use SQLDW)

“For each server licensed with SQL Server 2016 and covered by active SA, customers can run up to the same
number of passive failover instances in a separate, OSE to support failover events. A passive SQL Server
instance is one that is not serving SQL Server data to clients or running active SQL Server workloads. The
passive failover instances can run on a separate server. These may only be used to synchronize with the primary
server and otherwise maintain the passive database instance in a warm standby state in order to minimize
downtime due to hardware or software failure.

 The secondary server used for failover support does not need to be separately licensed for SQL Server as
long as it is truly passive, and the primary SQL Server is covered with active SA. If it is serving data, such as
reports to clients running active SQL Server workloads, or performing any “work”, such as additional
backups being made from secondary servers, then it must be licensed for SQL Server.”

What does this mean? I had to consult Microsoft on this one, as it was a change in my understanding of the “free” secondary licensing benefit. Basically, if you are going to dedicated hardware (that you own or lease) your secondary license (if you have SA) is still included in your primary license. However, if you go to Azure from on-prem for your HA model, you will need to license the secondary. This does get murky because if both of those workloads are in Azure, you only license the primary.

“All SQL Server licenses with active SA can be reassigned to another server within the server farm as often as
needed; however, they can only be reassigned to another server in another server farm, or to a non-private
cloud, once every 90 days.
A server farm may consist of up to two data centers located in time zones that are within four hours of
one another and/or with the European Union (EU) and/or European Free Trade Association (EFTA).
A given data center may only be part of one server farm.”

What Does this Mean? Basically you can’t cross an ocean for HA or DR and not pay for it.

SQL Server Developer Edition
SQL Server 2016 Developer Edition is a fully featured version of SQL Server software—including all of the
features and capabilities of Enterprise Edition—licensed for development, test and demonstration purposes
only. SQL Server Developer Edition may not be used in a production environment or with product data. Any
test data that was used for design, development or test purposes must be removed prior to deploying the
software for production use.
Customers may install and run the SQL Server Developer Edition software on any number of devices. This is
significant, because it allows customers to run the software on multiple devices (for testing purposes, for
example) without having to license each non-production server system.”

What does this mean? It basically means only your production environments need to be licensed as long as you are following Microsoft’s rules for not using production data. This is a huge benefit, note that you can’t just restore prod to dev, you need to create some testing data, which is best practice anyway.

“Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016

What does this mean? You need to have 2012 licenses (at least) to upgrade to SQL 2016. Basically Microsoft wants to make sure you went through the core conversion in 2012.

Version Upgrade Rights are offered as a Software Assurance (SA) benefit for qualified licenses and allow
customers access to upgrade their deployments at no additional cost. Existing SQL Server 2012 software
licenses covered by SA are automatically upgraded to licenses for the corresponding SQL Server 2016

What does this mean? If you had BI edition, your licenses will get converted to Enterprise Edition. If you read further into page 29, you will see that BI edition customers will be treated quite favorably. Any time there is a change like this, it’s usually a good time to negotiate with your Microsoft sales professional about getting a better deal.


There are no earth shattering changes in SQL Server 2016 licensing. The developer edition changes are probably the most significant, and can reduce your overall costs a great deal.

%d bloggers like this: