SQL 2016 Backup Compression with TDE Enabled Databases

The Background Before SQL Server 2016, businesses has to choose between securing their databases with Transparent Data Encryption (TDE) and database backup compression.  While you technically can backup a database that has TDE enabled and choose to compress the backup, the backup doesn’t really compress and it takes just as long to back up as…

0

DBCC CHECKDB Fails on a Database with files in Azure Blob Storage

Starting with SQL Server 2014 you can use SQL Server with Data Files in Microsoft Azure Blob Storage. This feature is very common when you work with SQL Server on Azure VM’s, the steps to accomplish this are: Create an Azure VM and then Install SQL Server 2014 or above. Create a Storage Account Create a…


CPU_ID Gaps in SQL Server sys.dm_os_schedulers

We all know (or should!) that SQL Server is NUMA aware. In fact, there are certain background processes that are created and run per NUMA node inside of SQL Server, which is why the soft-numa changes in 2016 can make a big performance gain. There was a need to affinitize certain cpus for a SQL…

0

Where is a record really located?

Recently I had a question regarding “What happens to records when they are deleted” – so I wanted to dig into what happens under the covers when just such a thing happens.  The answer is somewhat simple – but the explanation is somewhat more complicated.  So, I am breaking the answer up into 2 different…

0

Finding What Availability Group Listeners Applications Are Using To Connect

Background Information While on site helping with AlwaysOn Availability Groups, it was tasked to a DBA to meet with all of the application owners and figure out what listener they were currently using in their connection strings. The DBA looked over at me and said, “I wish there was a DMV that showed me connection…

3

Learn SQL 2016 Polybase with HDP – Part 1 – Build the Environment

I have been working with Polybase in Parallel Data Warehouse for a few years now, so I was quite excited to hear about this great feature making it into SQL 2016. It is a great was to use tools and a language you already know (TSQL) to be able to access data stored in Hadoop….

0

What Does Installing Polybase Add to My SQL Server?

In this post, I want to show you some of the components that are added to your SQL Server when you install the Polybase feature and where they reside. Introduction: Polybase is a new feature with SQL 2016 that allows you to query external Hadoop data and data in Azure Blob Storage. It has been…

0

Azure SQL Database Migration Performance

TLDR -> If you are like me, this section will be all your read: Use the P11 Service Tier or higher when you Migrate your database to Azure SQL DB. Once you have completed your migration, drop down the database to the tier you want to run at if you aren’t interested in running at…

0

How Many Tables Can I Have In SQL Azure DB & SQL Server

Hello Dear Reader!  This is my first full fledged blog for my friends here at the PFE blog.  I wanted to start off with something interesting.  A friend asked a very intriguing question yesterday.  “How many tables can a SQL Azure Database have?”.   The context was a migration from MySQL to SQL Server and they…