Press "Enter" to skip to content

Author: Kevin Feasel

Bugs With Backup Compression And TDE

Published 2017-09-25 by Kevin Feasel

Parikshit Savjani provides recommendations on combining backup compression with Transparent Data Encryption:

In past months, we discovered some edge scenarios related to backup compression for TDE databases causing backups or restores to fail, hence our recommendations have been

  • Avoid using striped backups with TDE and backup compression.

  • If your database has virtual log files (VLFs) larger than 4GB then do not use backup compression with TDE for your log backups. If you don’t know what a VLF is, start here.

  • Avoid using WITH INIT for now when working with TDE and backup compression. Instead, use WITH FORMAT.

  • Avoid using backup checksum with TDE and backup compression

Brent Ozar explains the risk:

When you install a new version of SQL Server, you get new features – and sometimes, you’re not told about them. For example, when 2016’s TDE compression came out, nobody told you, “If you back up across multiple files, your backups might suddenly be compressed.” You didn’t know that you had a new thing to test – after all, I don’t know a lot of DBAs who have the time to test that the new version of SQL Server successfully performs restores. They restore their production databases into the new version, test a few things, and declare victory – but testing restores FROM the new version’s backups isn’t usually on that list.

Keep up to date on those patches.

Comments closed

The Predicate For SQL On Linux: SQLPAL

Published 2017-09-25 by Kevin Feasel

Slava Oks explains the importance of the SQL Platform Abstraction Layer:

In addition to business matters, Hal’s article highlights key drawbacks which need to be addressed around engineering as well as  product supportability for the endeavor to be successful.   The article affirms that bringing SQL Server to *nix platform is not the hardest task compare to the additional work that the journey would require.  Indeed, in order for the project to be successful the team would have to:

  • Bring other SQL Server inbox products such as SSAS along
  • Implement platform specific features such as CLR, AGs, and much more
  • Guarantee adequate performance and scalability
  • Create new ecosystem around product support, engineering systems  and more.

So throughout the course of the work, I have continued to question the path we have been on and if we succeed at the end or not.  Every time I would go back, reread the article and every time I would come to the same favorable conclusion.  But why?

Slava links to this article from December from the data platform team explaining what SQLPAL is.  In the end, I think the benefits of this model will be much larger than SQL on Linux (which is itself large).

Comments closed

Joins With Kafka

Published 2017-09-22 by Kevin Feasel

Florian Trossbach and Matthias J Sax show the various sorts of joins offered in Kafka, both streams and tables:

Apache Kafka’s Streams API provides a very sophisticated API for joins that can handle many use cases in a scalable way. However, some join semantics might be surprising to developers as streaming join semantics differ from SQL semantics. Furthermore, the semantics of changelog streams and tombstone messages (that are used for deletes) are a new concept in stream processing.

Kafka’s journey from Pub/Sub broker to distributed streaming platform is well underway, and our times as engineers are very exciting!

I didn’t know you could join streams together in Kafka, so that’s really cool.

Comments closed

Clearing Cached Credentials With Powershell

Published 2017-09-22 by Kevin Feasel

Adam Bertram shows how to use Powershell and cmdkey to clear out cached credentials:

It is even easier to use cmdkey with PowerShell. IT can build a small wrapper script that can manage cached credentials on one remote computer at a time and perform the action just as quickly on multiple computers at once.

The following example uses a PowerShell module called PSCredentialManager. IT pros can download the module from the PowerShell Gallery by running Install-Module.

Read the whole thing.

Comments closed

Rounding Up The Usual Suspects

Published 2017-09-22 by Kevin Feasel

Arun Sirpal shows us the suspect pages table in msdb:

Did you know that SQL Server tracks suspect pages and puts the details into the msdb database in a table called suspect_pages? Well it does, but the question I want answering is what happens if the page within the suspect pages table is fixed? Does it automatically get removed/updated or do we as the administrators have to do anything manually?

Let’s find out.

It’s a useful table to monitor.

Comments closed

Transactional Replication And SQL On Linux

Published 2017-09-22 by Kevin Feasel

Phil Grayson shows a way to get transactional replication working on Linux:

Microsoft have stated that transactional replication isn’t supported on Linux and we’re not sure if they intend to in the future. This means that if you try to add the server to a publisher, you get the following message. So you can’t use the GUI and it also means that you can’t use pull as the necessary files won’t be there.

Despite that warning, there is a way to set up a push subscription; click through for that way.

2 Comments

Polybase And RPC Protection

Published 2017-09-22 by Kevin Feasel

Casey Karst announces that Polybase supports Hadoop RPC protection:

Supporting this configuration allows PolyBase to connect and query Hadoop clusters that have wire encryption turned on. This enables a secure connection between Hadoop and SQL Server; as well as, among the Hadoop Data Nodes.

To connect to a Hadoop cluster with the hadoop.rpc.protection set to privacy or integrity, you will need to alter the core-site.xml file that is installed with PolyBase. This file is generally found at C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn\Polybase\Hadoop\conf.

That’s good news for Polybase users.

Comments closed

Regular Expression Cheat Sheets

Published 2017-09-21 by Kevin Feasel

Mara Averick shows off a collection of regular expression guides:

There are helpful string-related R packages 📦, stringr (which is built on top of the more comprehensive stringi package) comes to mind. But, at some point in your computing life, you’re gonna need to get down with regular expressions.

And so, here’s a collection of some of the Regex-related links I’ve tweeted 🐦:

Click through for links to regular expression resources.

Comments closed

Visualizing A Single Number

Published 2017-09-21 by Kevin Feasel

Tim Bock shows a dozen methods for visualizing a single number:

There are a number of situations in which it can be advantageous to create a visualization to represent a single number:

  • To communicate with less numerate viewers/readers;

  • Infographics and dashboards commonly use one important number;

  • To attract the attention of distracted or busy viewers/readers;

  • To add some humanity or “color”, to create an emotional connection;

  • Or to increase the redundancy of the presentation (see Improve the Quality of Data Visualizations Using Redundancy).

To a great extent, my favorite is the first.  There are good cases for many of the others—primarily the shock value of the uncountable pictogram—but typically, the best visualization is simple.

Comments closed

Date Correlation Optimization

Published 2017-09-21 by Kevin Feasel

Monica Rathbun explains another quasi-hidden SQL Server configuration option:

According to MSDN – The DATE_CORRELATION_OPTIMIZATION database SET option improves the performance of queries that perform an equi-join between two tables whose date or datetime columns are correlated, and which specify a date restriction in the query predicate.

How many of you read what MSDN says and thinks “wuuuuuttt, English please”? I do.

Read on for the English translation.

Comments closed