Failure Modes In Event-Based Systems

Dave Copeland has an interesting article on understanding how message- and event-based systems can fail:

The system trigger (1) initiates everything. Common failures here are timeouts inside Application. This is particularly insidious because when this happens, the System Trigger may retry the operation. Think about a user on a webpage getting a 500 error. They will likely retry what they were doing until it succeeds.

This means that the entire workflow could be triggered multiple times, and it could be done in a way that is not programmatically obvious. Imagine our Merchandise buyer marking down an item’s price, and the entire operation succeeds but at the last minute their Internet connection dies and they get an error. They will repeat the markdown action and now there will be two messages about the inventory price being sent.

This is an interesting read.  Also, definitely check out Dave’s earlier post on how there is no happy path; it seems that most developers only code for a chimera, as there is so much code that assumes everything will work perfectly.

Building A Tally Table With U-SQL

Kevin Feasel

2017-08-23

U-SQL

Saveen Reddy shows how to create a tally (or numbers) table in U-SQL:

Many common scenarios for U-SQL developers require constructing a RowSet made up of a simple range of numbers or dates, for example the integers from 1 to 10. In this blog post we’ll take a look at options for doing this in U-SQL. In the process, we’ll get a chance to learn how to use some common U-SQL features:

  • Creating RowSets from constant values

  • Using CROSS JOIN

  • Using SELECT to map integers to DateTimes

  • Using CREATE TABLE to create a table directly from a RowSet. This is sometimes called “CREATE TABLE AS SELECT” and often abbreviated as “CTAS“.

Click through to learn more.

Handling Sensitive Data With Powershell

Adam Bertram shows off the data protection API in Powershell:

We need to automatically read this information without our intervention. This is where you sometimes see people get lazy and add passwords in plain text into the script. That’s a big no-no, and there’s a better way.

 PowerShell has native support for something called the data protection API (DPAPI). DPAPI is a built-in way Windows users can use certificates to encrypt and decrypt information on the fly which is perfect for PowerShell scripting. No username and password required. We simply need a certificate installed which can be self-signed.

This is about much more than storing and decoding passwords, so check it out.

Enabling SQL Server SSL Encryption With Powershell

Tracy Boggiano shows how to create and put into place SSL encryption certificates on SQL Server instances using Powershell:

Next we take the exported pfx file and copy it locally to the temp folder of each machine and import into the local certificate store.  Then we edit the registry with the thumbprint of the certificate.  After that you will have to restart SQL Server to get the changes to take effect.  We also clean up after ourselves and delete the pfx from the temp folder.

Note: To make this safe for production I commented out the restart of SQL Server.  Also, Get-CmsHosts cmdlet can found here.

If you’re dealing with sensitive information, enabling (and forcing!) SSL encryption is one of the easiest effective ways of securing an instance; in this case, it’s securing data in transit from SQL Server to and from the client.

SQL Server Features Forgotten But Not Gone

Robert Sheldon looks at five cobweb-ravaged parts of SQL Server:

Policy-Based Management (PBM) has a PR problem, even worse that DQS and MDS. A lot of people don’t know what it is, or if they do, are not sure if it’s still supported. Microsoft doesn’t even mention the feature in its list of Editions and supported features of SQL Server 2016. The PR problem is so bad, in fact, that I’ve come across community forum postings asking whether PBM still exists.

Despite its silent role, PBM is still a supported feature and appears to be available to all SQL Server editions, although I suspect it caters primarily to organizations with a large number of similar SQL Server implementations.

Microsoft introduced PBM in SQL Server 2008 to provide a policy-based system for managing one or more SQL Server instances. You can, for example, use policies to grant permissions on user databases, disable lightweight pooling, or choose an authentication mode. You can also target policies at specific SQL Server instances, databases, tables, or indexes.

Quick hits:  Data Quality Services and Master Data Services have been lapped by the competition and aren’t really competitive products anymore.  Policy-Based Management is still useful, and I wish it’d get some internal love to extend roles & features.  Management Data Warehouse is a tire fire that you should avoid at all costs.  Service Broker is useful in the right circumstances, but it needs a few big changes to become a great broker.  Unfortunately, I don’t see big changes happening and so there are superior alternatives for data flow (like Apache Kafka).  But read Robert’s take on these features.

Synchronizing Logins And Jobs

Ryan Adams shows five methods for synchronizing SQL logins and a couple ways of synchronizing SQL Agent jobs between instances of SQL Server:

Robert Davis wrote a great script back when he published his Mirroring book.  I started to write my own and was almost done when I contacted Robert and asked if he had dealt with SQL logins since the script only handled Windows logins.  His reply was something along the lines of, “What are you talking about? Of course it handles SQL logins”.  It turns out that the publisher didn’t get the right script version published with the book.  That’s when this post from Robert with the full script was born…

Transferring Logins

I also wrote about it HERE.

This script creates a stored procedure to handle the move and also uses Linked Servers.  If you can’t have linked servers in your environment this is not a good choice for you.  However, you can create the linked server in a SQL Agent job step prior to the step for transfer and then remove it in a job step after the transfer.  It breaks the rule but it does it fast enough maybe no one will notice.

Read the whole thing.

Custom SQL Server Docker Images

James Anderson shows an easy way of creating a custom Docker image which relies on some other image:

The FROM statement declares that we want to lay some instructions on top of the microsoft/mssql-server-windows image. The beauty of this approach is that when I pull down a new version of the microsoft/mssql-server-windows image, my image will be updated too. The microsoft/mssql-server-windows Dockerfile does the same thing with the microsoft/windowsservercore image.

The rest of the Dockerfile sets some meta data, downloads the installer and adds the Advanced Analytics feature.

SSIS, SSAS, SSRS or any other SQL Server feature could be added to a containerised SQL Server deployment in the same way.

With this approach, you do run the risk that upstream changes will break your image, but for something like this, it’s a very useful approach.

Building Temp Tables From Queries

Kevin Feasel

2017-08-23

T-SQL

David Fowler shows how to use dm_exec_describe_first_result_set to generate a temp table schema:

Have you ever needed to store the results of a complex query in a temp table?  How did you go about working out what the definition for that temp table should be, the columns and their data types?

It can be a bit of a pain, not to mention time consuming to have to go figuring out what all datatypes of the base tables are.

I got fed up with all that hunting around as well so as a quick blog I thought I’d share a little script that will take your query in a variable and print out a temp table definition for its result set.

Click through for the script, as well as an important comment by frequent curatee Shane O’Neill.

The Pain Of Multi-Statement TVFs

Andy Mallon walks through a multi-statement table-valued function in Microsoft Dynamics CRM:

Look at all those table-valued function calls! Followed immediately by a really expensive hash match. My Spidey Sense started to tingle. What is fn_GetMaxPrivilegeDepthMask, and why is it being called 30 times? I bet this is a problem. When you see “Table-valued function” as an operator in a plan, that actually means it’s a multi-statement table-valued function. If it were an inline table-valued function, it would get incorporated into the larger plan, and not be a black box. Multi-statement table-valued functions are evil. Don’t use them. The Cardinality Estimator isn’t able to make accurate estimates. The Query Optimizer isn’t able to optimize them in the context of the larger query. From a performance perspective, they don’t scale.

Even though this TVF is an out-of-the-box piece of code from Dynamics CRM, my Spidey Sense tells me that it’s the problem.

That said, Joe Sack and team are working on making multi-statement TVFs faster in SQL Server 2017.  Whether it will move the needle from Andy’s excellent advice, we’ll have to wait and see.

Categories

August 2017
MTWTFSS
« Jul Sep »
 123456
78910111213
14151617181920
21222324252627
28293031