The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I've spent a couple of years working on IT systems for a large retail bank in the UK, so it is with great interest, and some incomprehension, that I've followed the unravelling of the UK's TSB Bank, now owned by the Spanish Sabadell Group. This organisation has achieved international notoriety, thanks a banking disaster that has cost it almost £200 million, according to its half-year financial results for the six months to 30 June 2018. It caused 12,500 customers to abandon ship, and 2,200 customers to be stung by fraud.

It all happened when it attempted to transfer its banking services from Lloyds to the Proteo4UK platform, used by Sabadell's IT services in Spain. The problems started on the 19-20th May, as the bank shut down services to migrate all customer data to the Proteo4UK platform, and have lasted for months, with issues still being reported, sporadically. Of course, they aren't the first retail bank to hit difficulties during a migration, but normally these last just hours, before a hurried rollback. Here, there was no rollback, despite Lloyds' evident readiness to do so.

There is a lot of public interest in, and a parliamentary investigation into, what happened, and how. IBM, who were called in to help, concluded to UK parliament that "a combination of new applications, advanced use of microservices, and use of active-active data centers, resulted in compounded risk in production".

A few of the statements in IBM's brief report are scary to read. This toxic mix of bleeding edge technologies and Agile processes required "extensive engineering, testing and proving, as well as significant mitigation strategies, including roll-back" and 'the complexity results in a broad range of technical and functional problems that are hard to diagnose'. The final sentence was chilling: "IBM has not seen evidence in the application of a rigorous set of go-live criteria to prove production readiness."

One must read between the lines of this highly guarded statement. Could it be that the bank had forgotten all its own internal IT rules? Had it failed to learn from other production disasters?

Before the launch of any major change to a banking system, there are extensive tests. On the system I worked on, these alone took six months. After that, there are several trial migrations, for a small controlled subset of the user base, usually the UAT team. Each migration is then rolled-back, to test and refine the rollback procedures, and any issues fixed. Finally comes the launch of the production system, but again this done over a period, firstly just to the IT and UAT team members only, then bank staff, followed by targeted customer groups, existing customers and finally a full launch to new customers.

It seems that none of this happened at TSB. Instead, there seems to have been a lot of attempts to 'roll forward'. The result was an unprecedented melt-down of a retail bank.

I'm not averse to unconventional data architectures, such as Microservices. I like new technologies and have often been considered a 'wild man' in IT. However, in banking processes, I'm a conservative who grips the department's computer manual with whited knuckles. The consequences of error are so dire, and the complexity is so great. That phrase about the TSB,'a broad range of technical and functional problems that are hard to diagnose', makes me wince. What works for one sector of commerce doesn't necessarily work in another, especially if the IT staff don't have the necessary training, discipline or experience. It's horses for courses.

Phil Factor.

Phil Factor from SQLServerCentral.com

» Join the debate, and respond to today's editorial on the forums

All the headlines and interesting SQL Server information that we've collected over the past week, and sometimes even a few repeats if we think they fit. These headlines are gathered throughout the week and are posted in real time at the website. Check there for information throughout the week or enjoy this weekly summary of the SQL Server world.

Webinars

Database DevOPs September Training Schedule - Do you need to do more with less as a Database professional? Why not start your journey to Database DevOps nirvana today with our September Training schedule. You will learn Source Control, Continuous Integration & Continuous Delivery for the Database....(more)

SQL in the City Streamed - The theme for September 2018's SQL in the City Streamed is Adapt and thrive as a data professional. Microsoft MVPs, Kathi Kellenberger, Grant Fritchey, Steve Jones and Rob Richardson will head up the speaker line-up, and will be joined by members of the team at Redgate and other technical experts. Wednesday 5 September, 12.00PM-5.40PM BST...(more)

Is HIPAA impacting your data delivery processes? - In this webinar Grant Fritchey will look at the paint points and common struggles when working under restrictive regulations and what steps can be taken to accelerate your data delivery whilst remaining compliant. Tuesday 21 August, 11.00-12.00 CDT / 9.00-10.00 PDT...(more)

SQL Server monitoring for a streamlined development process - So you want to streamline your development process? A cornerstone of successful DevOps integration is efficient, reliable SQL Server monitoring. Redgate’s James King will talk you through the process of monitoring your SQL Servers to ensure you are ready and able to efficiently keep track of your estate. Thursday 16 August, 17.00-18.00 BST...(more)

Virtualization and Containers

SSL Provider error 31 when connecting to SQL in a docker container - Andrew Pruski encounters an "error during the pre-login handshake" when trying to connect SQL Operations Studio (version 0.31.4) to SQL 2017 CU9, running in a docker container. ...(more)

Vendors/3rd Party Products

Spoofing Realistic Credit Card Data for your Test Systems using Data Masker - Grant Fritchey shows how to use a tool like Data Masker to create fake credit card data that not only looks like the real thing, but also has the right distribution, so will act like it too, when we query it....(more)

T-SQL

Spoofing Data Convincingly: Credit Cards - Generating credit cards in a way that conforms to a particular distribution is reasonably easy in SQL Server, though. The only difficult bit is the fact that there is a validation checksum. Otherwise it is a good example of how to solve the problem of spoofing the more specialist types of data....(more)

Table Value Constructors in TSQL - Essentially, table value constructors let you create a dataset on the fly. These can occasionally be useful in writing queries, but I think playing with them has another benefit: they provide a simple, lightweight framework to let you develop your ability to think in sets....(more)

Two Important Differences Between SQL Server and PostgreSQL - Brent Ozar has spent a lot of time writing Postgres queries lately and has noticed some things that are different....(more)

Analyze Actual Execution Plan - Grant Fritchey explains an new ShowPlan Analysis feature of SSMS 17, which highlights operators with discrepancies between actual and estimated row counts....(more)

Displaying Long Values in SSMS - If you write a lot of dynamic SQL, you probably frequently encounter variables that contain many characters,and stumble into problems when trying to inspect the text of the entire @LongValue variable....(more)

Plansplaining, part 8. To join the impossible join - In this post we look at a query that, given the known limitations of the available join operators, appears to be impossible to execute. But it’s a legal query so it should run. And it does. But how?...(more)

Filling empty values with last nonNull value using T-SQL - Say you have NULL values in your SQL Server table and you want to populate each one with the last non-NULL value, based on a particular order. If you have only one NULL value encapsulated between two populated values, there are quick and fast solutions. But what if you find a larger block of NULL values and you want to populate these values as well?...(more)

Finding duplicated data in a case insensitive column - In a case insensitive database, in a table’s column that was case insensitive, the customer was using the data as case sensitive, “active customer” and “Active Customer” meant different things!...(more)

Optimization Thresholds – Grouping and Aggregating Data, Part 5 - If you can figure out a strategy that under certain conditions is more optimal than the ones that the optimizer supports, you cannot enhance the optimizer to support it, and the optimizer cannot learn to use it. However, what you can do, is rewrite the query using alternative query elements that can be optimized with the strategy that you have in mind....(more)

SQL Server Security

SQL Server Encryption, What’s The Key Hierarchy All About? - SQL Server has the Service Master Key, Database Master Keys, Symmetric Keys, Asymmetric Keys and Certificates. These keys can be used to encrypt data but they can also be used to encrypt other keys and this is where the key hierarchy comes in....(more)

SQL Server on Linux

SQL Server on Linux or in Docker plus cross-platform SQL Operations Studio - I recently met some folks that didn't know that SQL Server 2017 also runs on Linux but they really needed to know. They had long-been a Linux shop and was now fully containerized...except for this machine under Anna's desk that they were keeping around to run SQL Server....(more)

Python in SQL Server

SQLCLR vs SQL Server 2017, Part 8: Is SQLCLR Deprecated in Favor of Python or R (sp_execute_external_script)? - The only real change to SQLCLR since SQL Server 2012 has been adding the annoying configuration step required to get SQLCLR Assemblies to load, in SQL Server 2017. Is SQLCLR is being deprecated (i.e. phased-out) in favor of new languages such as R and Python?...(more)

PowerShell

A PowerShell Conference In A Book - Over thirty subject matter experts have teamed up to bring you the ultimate collection of PowerShell topics that's designed to be like a conference in a book....(more)

PowerPivot/PowerQuery/PowerBI

Understanding Power BI Dual Storage - The July release of Power BI Desktop introduced composite models, which make data acquisition much more flexible but also more complex. ...(more)

Performance Tuning SQL Server

Blocking Monitoring Framework: Capture and Analyze SQL Server Blocking and Deadlock Information With Event Notifications - Neither blocked process report nor deadlock graph provide you execution plans of the statements. Also, SQL Server may generate enormous number of blocked process reports in cases of prolonged blocking and complex blocking chains, which complicates the analysis. Dmitri Korotkevitch shares his Blocking Monitoring Framework, which might help simplify things....(more)

How to Check for Non-Existence of Rows - Two ways to write a query to check for non-existence, ending with some good tuning advice: write your queries in whatever way feels the most intuitively readable to you and your coworkers. If you can understand what’s going on easily, then the engine is likely to, as well. Later, if there’s a performance problem, then you can go back and examine the different tuning options....(more)

Exporting Extended Events Session Data to a Table - If you’re a long time Profiler user like me then you probably often take the option of saving (or loading) your trace results to a table for easy analysis. Well, with Extended Events (XE) it’s easy to do that too....(more)

Operations that need a serial plan - Daniel Hutmacher sets up a a 16-core Azure VM, 12 versions of SQL Server, and runs a script on each that will force a parallel execution plan - all to work out which features and operations require a serial plan, or at least a serial zone....(more)

NoSQL

The Crimes of Chicago: Working with Data in MongoDB - NoSQL databases like MongoDB are gaining popularity, but using the right tools for the job at hand is most important. In this article, Phil Factor demonstrates how to work with a MongoDB database and how to use PowerShell with MongoDB so that the process can be automated...(more)

DevOps and Continuous Delivery (CI/CD)

Why code quality is vital in the world of database DevOps - Everyone understands the importance of code quality for applications, particularly when DevOps results in releases becoming faster and faster, reducing the room for error. The same issues increasingly apply to databases, which are a vital part of DevOps workflows. Fail to integrate the database into DevOps and you’ll face bottlenecks that slow down your processes and undermine your efforts....(more)

Database Design, Theory and Development

A Visual Guide to Choosing an Index Type - Brent Ozar oversimplifies a whole lot of topics to make things easy, and give you a good starting point for your index design journey. ...(more)

Data Access / ORMs

Very Simple Data Entry with C# Winforms & Datasets - Peter Schott shares his quick and dirty app for data entry into a normalized database. ...(more)

Conferences, Classes, and Events

SQL in the City Summits - New York, London & Chicago - Are you interested in learning how your business can benefit from implementing Compliant Database DevOps? This October Redgate are inviting you to attend one of their SQL in the City Summits. If you manage SQL Server databases, or manage a team of people who do so, Redgate’s SQL in the City Summit is the conference for you. Find out who’s presenting and register for a Summit near you today....(more)

Big Data

The Cold Start Problem - John Cook discusses how big data experts can begin to solve problems before they even had data to work with!...(more)

Backup and Recovery

Your DR Plan isn’t a Plan - You don’t have a Disaster Recovery Plan. You have a Disaster Recovery Hope. If I’m wrong (and I hope I am), its because you are in the 10% (optimistically) of companies that actually test their DR plans and document the results....(more)

The Mystery of the Exploding T-Log - An instructive "case study" involving a massive DELETE operation that caused the transaction log to fill the disk and SQL Server to report what looked like a corruption error, but wasn't....(more)

Log Backups, Auto Growth Settings & Alerts (Part 2 – Missing Log Backup Alert) - The primary reason most transaction logs start to (unexpectedly) grow is that the transaction log hasn’t been backed up. This is usually caused by one of two situations: the log backup job failed, or the log backup job didn’t start. ...(more)

How I resolved “Recovery_Pending" State when moving SQL Server files - How to avoid the "recovery pending" issue when moving SQL Server files....(more)

Fixing Data Loss Using Log Shipping with Delayed Recovery - How to set up log shipping with a 30 min delay in restoring to the secondary. Say a user inadvertently drops a table. As long as the mistake is spotted immediately, we can recover the data from the Secondary database. It also gives us a brief window in which to run reports on the secondary....(more)

Azure SQL Database

Understanding your Azure EA Billing Data and Building a Centralized Data Storage Solution - Many organizations are moving to Azure and other cloud providers. Understanding how resources are being used and what is spent is very important. In this article, Feodor explains his solution for automating the collection of the Enterprise Agreement billing from Azure into an Azure SQL Database where it can be analyzed....(more)

Analysis Services / BI on the MS Stack

The hidden secrets of TOTALYTD - DAX has many time intelligence functions that are often redundant, offering different shorter syntaxes for longer more generic functions. However, sometimes the shorter syntax could be dangerous, as I explain in this blog post where I suggest using CALCULATE and DATESYTD instead of TOTALYTD....(more)

Administration of SQL Server

SQLCMD mode in SSMS - It is pretty neat to use the built-int terminal to do stuff without leaving your development environment. This functionality is also available on SSMS, to some extent, in the form of SQLCMD mode. This feature allows you to interact directly with Windows command line without leaving your SSMS environment....(more)

The SQL Server Fill Factor Setting that Should Always Be Followed (and How to Do It) - Most databases are created well before any real usage occurs, increasing the chances of an inaccurate SQL Server Fill Factor setting....(more)

The Top Five Things That DBAs Need to Monitor - Being a database administrator is much more than knowing how to install SQL Server and set up a database. One of the most important responsibilities is being proactive by monitoring the instances in their care. But, what should be monitored? Here are the top five things to monitor when you are a SQL Server DBA...(more)