The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

Those of us who have worked in IT for retail banking will be looking on in horror at the catastrophic attempt by the Spanish-owned TSB to introduce a new IT platform for their UK customers. TSB, formerly the Trustee Savings Bank, was spun out of Lloyds Bank after the EU ruled that it was a monopoly, because of the state aid it had received at the time of the banking crisis. For a while, the TSB used its existing Lloyds IT at a cost of £220 million a year, but decided to transfer to the Proteo platform, which is used by its new owners, Sabadell.

The Proteo system was designed in 2000 specifically for mergers, and was the key to the successful integration of the four Spanish banks it acquired between 2003 and 2007. It is based on Accenture's Cobol-based Alnova system, but customized, installed and managed by TSBs staff. It runs on Amazon Cloud. At the launch of Proteo4UK, Paul Pester, CEO of TSB, boasted that they had "created a more digital, agile and flexible TSB". Carlos Abarca, the CIO, agreed, "It's the technology journey that we are on together with our customers!"

This "customer-centric by design" platform was supposed to "enable the open banking revolution". Instead, on 21st April, it caused an unprecedented banking disaster, locking nearly two million banking customers out of their accounts for up to ten days, and over a month-end, when businesses particularly rely on access to their accounts. As problems continued, TSB appointed IBM, in a systems integration role, to get the system under control and "to help identify and resolve performance issues in the platform".

Customers were experiencing zero balances, incorrect currencies, massively inflated mortgage amounts, and e-mails saying that there are no records of recent direct debits. There were many initial reports of users seeing information on-screen that looked like other people's bank accounts, but these reports ceased after a few hours.

Bank customers puzzled over on-screen messages, such as:

'BeanCreationNotAllowedException exception: Error creating bean with name 'contextManagerPostController': Singleton bean creation not allowed while the singletons of this factory are in destruction (Do not request a bean from a BeanFactory in a destroy method implementation!)'

Other customers tried to make transfers while getting 'ArrayIndexOutofBounds' and java.lang.NullPointerException errors. Branches report the systems spewing out error messages in Spanish. As I write this, on 4th May, problems with internet banking are still being reported by customers.

Instead of saving TSB over £100 million a year, it has greatly reduced public confidence in the bank. TSB are likely to be fined more than the predicted saving by the Financial Conduct Authority (FCA) and the Information Commissioner's Office (ICO), but the loss in reputation is incalculable, and the law firms representing customers are busy sharpening their pens for the fray.

It is far too soon to work out what went wrong. However, the whole industry will be keen to find out, as soon as possible. The worst thing that can happen is for a delay in the forensic examination of the disaster. We need to learn the lessons as soon as possible, not from schadenfreude, but because there is no better spur to improving the quality of software than learning from failure.

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

Minimizing the impact of data breaches in dev and test databases - Data privacy regulations make it essential to implement controls and processes that protect personal data and guard against data breaches. But you don’t need to lock away your data or slow down your development process to comply. In this webinar, Microsoft Data Platform MVP Steve Jones will discuss how to put in place reliable and repeatable processes for protecting sensitive information in SQL Server....(more)

Vendors/3rd Party Products

Pseudonymizing your data with SQL Data Generator - SQL Data Generator was, as its name suggests, intended to generate the data for databases, as required for testing, development work and training. However, it is perfectly at home with using the original data as the default source of the new data, and then substituting in fake data for any column that you want to mask - here's how....(more)

Automatic Provisioning of Developer Databases with SQL Provision - This article shows a simple proof-of-concept workflow that allows an organization to provision full size databases for developers, using production like data that has been masked automatically as part of the provisioning process....(more)

SQL Compare Snapshots: a lightweight database version control and rollback mechanism - SQL Compare snapshots provide an easy way to keep track of changes during development work, whether they are small changes between iterations, or significant changes between releases. Snapshots can be used to quickly generate rollback scripts during releases even in environments which are isolated by infrastructure design for security reasons....(more)

T-SQL

Shortcut: Dependency tracking in SQL Server Management Studio - In early versions of SQL Server, the only way to try to track dependencies between tables, procedures, functions, etc. was to use the sp_depends stored procedure. And everyone thought it lied. SSMS now tracks those dependencies using far superior dependency views....(more)

SQL Server: Quick Space & File Layout Analysis With PowerShell and PowerBI - Jana Sattainathan adds some pretty Power BI visuals to a useful chunk of TSQL that reports space and file layout information at the Database/File/Filegroup level....(more)

#TSQL2SDAY #101 Round-Up : My Essential SQL Server Tools - Jens Vestergaard rounds up 22 suggestions for your essential SQL Server toolbelt....(more)

Operator Precedence versus the Confusing Constraint Translation - Louis Davidson offers a good reminder of operator precedence and how SQL Server applies it to the saving and scripting of constraints....(more)

Dates and Times in SQL Server: What about TIMESTAMP? - Much like DECIMAL is a synonym of NUMERIC, so too is TIMESTAMP a synonym of ROWVERSION. Please call it a ROWVERSION and pretend that TIMESTAMP doesn’t exist. Microsoft is deeply sorry for the confusion....(more)

SQL Server Security and Auditing

SQL Audit not showing full SQL Statement - Tom LaRock clears up the confusion around large SQL statements and how they appear in SQL Audit....(more)

Rotating encryption keys for Always Encrypted - Roatating encryption keys is one of those routine maintenance tasks that need to be done from time to time in order to keep your encryption strong for the long haul. But how does this rotation work when you're using Always Encrypted?...(more)

Digitally Signing a Stored Procedure To Allow It To Run With Elevated Permissions - Have you ever had the need to give elevated permissions via a stored procedure above that what the user calling the procedure might have?...(more)

SQL Server News

SQL Updates Newsletter – April 2018 - SQL announcements, guidance, and lessons learned from the field....(more)

Product Reviews and Articles

Expert SQL Server In-Memory OLTP (2nd Ed) - This book aims to explain SQL Server 2016 In-Memory OLTP, which can dramatically improve performance, how does it fare?...(more)

PowerShell

Create Azure VMs with PowerShell Part 2 - The ability to quickly spin up a virtual machine is one of the great things about Azure. In this article, Robert Cain describes the steps to set up a VM using PowerShell using his PSAzure module....(more)

PowerShell Day-to-Day Admin Tasks – Part 6: Real-Time IT Dashboard - Creating a real-time IT dashboard is essential for IT professionals for managing their environments and to be proactive. A Few months ago, a great tool called ‘PowerShell Universal Dashboard’ or ‘PoshUD’ was officially released. In this article, Nicolas Prigent describes PoshUD, and how to use it to monitor your infrastructure using PowerShell....(more)

PowerPivot/PowerQuery/PowerBI

Troubleshooting Data Refresh Performance Issues With OData Data Sources In Power BI And Excel Using Fiddler - A lot of people have problems with the performance of OData data sources when loading data into Power BI and Excel. One possible cause is query folding, where the Power Query engine requests raw rather than filtered data from the data source....(more)

Power BI Architecture Guidelines - Reza Rad explains some of the most common architectures to use Power BI, and how to use Power BI correctly, within each different architecture....(more)

Show Me All Invoices Containing this Product - The user wants to select a product from a list. When that product is selected, they want to see a list of all invoices that contain that product AND all other products on those same invoices. It's a trickier problem than it sounds......(more)

Performance Tuning SQL Server

Parameter Fluid Optimization - Parametric query optimization (PQO) deals with the problem of finding and reusing a relatively small number of plans that can achieve good plan quality across multiple instances of a parameterized query. ...(more)

Building SQL ConstantCare®: Refining Our Recommendations - Brent Ozar on the SQL Server health and performance recommendations people are struggling to implement....(more)

Hey, That’s Not My Sort! - When reading query plans, you may sometimes see a sort when you didn’t explicitly ask for data to be sorted. Sometimes they show up to support order-preserving operators like stream aggregates, merge joins, or segments. Other times, they may show up to help optimize an operation that would otherwise rely on random I/O....(more)

Replication Enhancement – Improved Distribution Database Cleanup - The new implementation of the cleanup procedure allows users to parameterize the batch sizes inside the cleanup stored procedure and introduces a new adaptive approach to determine the batch sizes for each iteration. ...(more)

Book Review: Inside SQL Server 6.5 - Brent Ozar takes a nostalgia trip back to the days when you server had to have "enough memory to give SQL Server at least a 10-MB cache"....(more)

5 Ways to Examine Cardinality Metrics - The optimizer uses cardinality estimates to help it find an efficient execution plan that fulfills a query request. If t appears to have erred in its plan choice, you may need a way to see the metrics it's using....(more)

HA/DR/Always On/Clustering

A Problem with Storage Spaces, Failover Clustering, and Always On Availability Groups in Azure - This is quite possibly my longest blog post title, ever, however it is pretty important for anyone who is building SQL Server configurations using Azure virtual machines....(more)

Graph Databases

Graph Algorithms in Neo4j: 15 Different Graph Algorithms and What They Do - If you're using Neo4j, then you need to be familiar with these powerful graph algorithms. Who doesn't want to make their jobs easier?...(more)

SQL Server Graph Databases – Part 5: Importing Relational Data into a Graph Database - In the final article of this series, Robert Sheldon shows how to move from a relational structure to a graph structure using the Graph Database feature....(more)

ETL/SSIS/ELT

Database testing and ETL processes - Unit testing using tSQLt is really great, you call FakeTable on any tables your code touches and then insert the columns and rows your test actually need, nothing more, nothing less but what happens when you have more complicated processes like an ETL process?...(more)

Conferences, Classes, and Events

Join the leading experts in data governance and SQL Server at Redgate’s SQL Privacy Summit - The GDPR is almost here and, just one week before it’s launch, Redgate is hosting the first SQL Privacy Summit in London. The schedule of presentations, panel discussions and workshops has been created to help SQL Server professionals ensure their business meets the new data privacy and protection regulations....(more)

Computing in the Cloud (Azure, Google , AWS)

Dedicated Hardware in a Public Cloud World - To be short and sweet, VMware Cloud on AWS runs on physical hardware, it is not virtualized and running inside EC2 instances!...(more)

Career Growth

Dear SQL DBA: Do DBAs Need a College Degree? - Will a lack of college degree hold you back if you want to be a DBA? Kendra Little's investigation suggests that it depends where in the USA you're looking for a job....(more)

Backup and Recovery

Differential Backup Woes - A real head in the hands moment recently when I was restoring some databases manually. I wasted a few hours unnecessarily restoring all of the differential backups since the last full backup. ...(more)

Azure SQL Database

Automating Automatic Indexing in Azure SQL Database - With Azure SQL Database, routine tasks such as backups, consistency checks, can be automated very easily, as can some, not all, but some, index management. ...(more)

Analysis Services / BI on the MS Stack

R Chart into SSRS (get data from SQL and create ggplot chart in R)-Part 1 - Leila Etaati shows how to get data from SQL Server, create a table chart using the ggplot2 package inside SQL Server 2017, then create a chart in SSRS....(more)

Administration of SQL Server

Do NOT delete files from the Windows Installer folder - The Windows Installer Cache is used to store important files for applications installed using the Windows Installer technology including SQL Server. Without them the already installed applications like SQL Server may continue to run fine. However, the existing patches and applications cannot be uninstalled cleanly nor updates applied properly. ...(more)

Fill Factor and Data Compression - If you’re using fill factor then enable data compression, is the fill factor still enforced? If you aren’t using fill factor and enable data compression does it create free space on the original pages? SQL Server PFE Susan Van Eyck has the answers....(more)

Windows Disk Cleanup - One very useful tool for freeing up disk space is the built-in Disk Cleanup application in Windows. Glenn Berry explains how to reclaim space....(more)