Kyle Kingsbury looks at VoltDB:
Unlike most SQL databases, which default to weaker isolation levels for performance reasons, VoltDB chooses to provide strong serializable isolation by default: the combination of serializability’s multi-object atomicity, and linearizability’s real-time constraints.
Serializability is the strongest of the four ANSI SQL isolation levels: transactions must appear to execute in some order, one at a time. It prohibits a number of consistency anomalies, including lost updates, dirty reads, fuzzy reads, and phantoms.
If you use VoltDB, it sounds like upgrading to 6.4 is a good idea.
Mike Fal casts out SQLPS and looks at new and updated Powershell cmdlets:
An important facet of the provider is that it behaves like a file system. How many of us have deleted files from the command line? Have you also used a PowerShell one-liner to delete old files, like backups? If you have, you might be familiar with two parameters: -WhatIf and -Confirm. These two switch parameters are extremely helpful because they can keep you from cutting yourself with that sharp PowerShell knife.
With the July 2016 update, the SQL Server provider now supports the use of these two switches. While using them may not be a common situation, it’s good to know that they are there. After all, it could be handy if you wanted to clean up some items from the command line, like maybe a junk database. Now you can both check what you’re going to do before you do it, along with getting a confirmation question when you go for the actual delete:
I am a huge fan of the -WhatIf switch, so that gets a thumbs up from me.
Rob Farley reports that we can now use Management Studio to connect to Azure SQL Data Warehouse:
One of the biggest frustrations that people find with SQL DW is that you need (or rather, needed) to use SSDT to connect to it. You couldn’t use SSMS. And let’s face it – while the ‘recommended’ approach may be to use SSDT for all database development, most people I come across tend to use SSMS.
But now with the July 2016 update of SSMS, you can finally connect to SQL DW using SQL Server Management Studio. Hurrah!
…except that it’s perhaps not quite that easy. There’s a few gotchas to be conscious of, plus a couple of things that caused me frustrations perhaps more than I’d’ve liked.
Yes, it’s never quite that easy… Read the whole thing.
Now what?
Well obviously something somewhere is different. Start by comparing everything on both servers and both databases down to… hang on, here, we’ll write a PowerShell script and then….
Wait, wait, wait!
You have the execution plans? Before we start digging through all the properties everywhere and comparing everything to everything, updating statistics 14 times, and all the rest, what if we look at the execution plans. They’re different, so we should start looking at scans & indexes & statistics &….
Wait!
Sometimes, it’s the little things that matter.
Dave Mason wants shutdown procedures like how we can have startup procedures:
If ‘shutdown’ was valid for the @OptionName parameter, I know exactly what I’d do: configure sp_procoption to execute a stored proc that sends an email alert. It would be great to know when a SQL instance goes down, wouldn’t it? Looking at the log file, we can see entries indicating the instance was shut down:
Dave does mention potential failure scenarios, but I agree: it’d be nice to have the ability to run procedures at shutdown to perform certain actions.
Ben Weissman shows how to use Biml to load data from XML files:
To make use of flat files, Biml will require one or more flat file formats as well as the corresponding flat file connections. A flat file connection is nothing but a link between a flat file format and the path to a flat file. While in real life, you might rather want to loop across multiple files using the same format, we’ll keep it simple for now and assume that we’ll have one file per format (which is the case here anyway due to our AdventureWorks sample data).
For reasons of readability and also the ability to easily reuse our code, we’ll make use of a new extension method called GetFlatFileFormatfromXML. If you have not read our post on creating your own extension methods, you may want to do that first.
Getting the extension method in place is about 90% of the solution. Read on to see the whole thing.
Matt Gordon walks through changing endpoint ports:
Since a communication failure within an AG is usually a “hair on fire” kind of event, a quick fix may be desired. The quickest fix I’ve found is to change the port on which the AG endpoint is listening. While the Microsoft help pages on this contain enough information to put together the right script eventually, the first time this happened to me I really would have liked a blog post specific to this issue that gave me the right script to use.
In keeping with this T-SQL Tuesday’s theme, below is my SQL Server present. Please note that I used 5023 as an example but your choice can be flexible depending on what else is consuming ports on your machine. Hopefully this helps somebody (or me if I manage to travel back in time and encounter this same issue)
In an ideal world, you’d want a bit more control over what’s running on various ports to prevent this scenario, but that’s probably wishful thinking.
Jakub Szymaszek notes that Azure SQL Database can now support Always Encrypted:
I’m happy to announce Always Encrypted in Azure SQL Database is now generally available!
Always Encrypted is a feature designed to ensure sensitive data and its corresponding encryption keys are never revealed in plaintext to the database system. With Always Encrypted enabled, a SQL client driver encrypts and decrypts sensitive data inside client applications or application servers, by using keys stored in a trusted key store, such as Azure Key Vault or Windows Certificate Store on a client machine. As a result, even database administrators, other high privilege users, or attackers gaining illegal access to Azure SQL Database, cannot access the data.
To be honest, I’d much rather try Always Encrypted against an Azure SQL Database instance than an on-premise instance, mostly because if I hose Azure SQL Database that badly or the company decides that Always Encrypted isn’t a good fit, I can grab the data and dump the instance. It’s a little harder to do that with physical hardware or even an on-prem VM.
Lukas Eder discusses the ALL, SOME, and ANY predicates:
Ultimately, you should always choose performance first, and then – most certainly – intuitiveness second (because some poor soul might need to maintain your query). But personally, I find these quantifiers quite elegant for three reasons:
They express the quantification right where it belongs. With the comparison operator. Compare this with the solution using LIMIT, which may be far away, visually, from the greater-than operator. Quantifiers are much more concise, even than when using MAX() (in my opinion)
They’re very set oriented. I like thinking in terms of sets when I work with SQL. Whenever I can omit the
ORDER BYclause, I will. If only to avoid potentially slow operations (in case the database doesn’t optimise this, and a fullO(N log N)sort operation is invoked)
Quantified comparison predicates work on rows too, not just on single values.
I’ve known about these, but could probably count on one hand the number of times I’ve ever used one.
Aaron Bertrand discusses drift between estimates and actuals in execution plans:
Now, inaccurate estimates won’t always be a problem, but it can cause issues with inefficient plan choices at the two extremes. A single plan might not be optimal when the chosen range will yield a very small or very large percentage of the table or index, and this can get very hard for SQL Server to predict when the data distribution is uneven. Joseph Sack outlined the more typical things bad estimates can affect in his post, “Ten Common Threats to Execution Plan Quality:”
Read the whole thing.
Kevin Feasel
2016-07-13
Architecture