The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

There was a time, in the late seventies, when we jeered at Unix users. In CP/M, we had a modern operating system. It couldn't do that much, but it would run the payroll and accounting systems of a business, do stock control and a lot of other commercial tasks. I wrote many database-driven applications using it for City-of-London stock-brokers (KSAM/ISAM in those days).

Unix, by contrast, had problems that limited its commercial appeal. The licensing problems were never fixed until Linus and his team rewrote it. Unix was originally written by US university geeks who had little idea of the complexities of nationalization. It had a binary collation because that was easiest with ASCII. This meant that a frog was a different thing to a Frog. A Unix geek could, we suspected, call his three sons john, John and jOhn, and be confident that they were unique identifiers. The idea of producing a commercial software product that could be supplied to all cultures, nationalities and languages never occurred to them.

The CP/M operating system was also written from scratch by a university lecturer and some of his friends and students, but the problem of accommodating the most common languages and cultures was fixed for the in the early Eighties, mostly funded by Xerox who wanted to introduce a range of CP/M-based word processors around Europe. The experience and the solutions soon spread to the new MSDOS.

While doing some Linux-based development work, recently, I was taken aback by hitting that same old 1970s US-Academic-geek culture. It was like coming face-to-face with a velociraptor. The extinct lives. What is the virtue of a binary collation? Capital and lowercase are just two ways of writing the same character. To say they are different is as fat-headed as saying that italic or bold makes them different. What is the reason for saying that an accented character is necessarily different? In some cultures, they are, and in some countries they aren't. The French seem to apply them nowadays with the same abandon as salad dressing. Nationalisation is a messy problem. I remember once doing a big nationalisation project and sitting back in my chair with satisfaction, only to be informed that the Semitic-based Middle-Eastern countries wrote backwards from right to left.

While we're on the topic of Unix nonsenses, what about the bizarre idea that indices start at zero rather than one? This is a ghastly error that makes a mockery of the zero concept, and of the vernacular understanding of sequence. Ah, here is john, my zero'th son. John, my son number 1, was born a year later.

What about databases? Fortunately, in SQL Server they pretty-well nailed the collation problem. However, MongoDB still ships with a binary collation, though now you can impose something more sensible on the data. I still get tripped up with Regular Expressions though, which ignore collation and so are case sensitive, by default.

It seems that anything originating in Unix/Linux is infected with this silliness of binary collation and the zero first index. It is so entrenched that people think that there is method in the madness. Actually, not: it is just madness.

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

Don’t just think DevOps. Think Compliant Database DevOps - How can the promise of releasing changes to the database faster and easier be balanced with the need to keep data safe and remain compliant with legislation? Join this webinar to discover how the answer lies in in going one step further than database DevOps and thinking about Compliant Database DevOps....(more)

The importance of monitoring your Azure SQL Database - Monitoring Azure SQL Database is still a necessary part of understanding how your system is behaving and ensures that you have the information needed to make necessary decisions about your databases in a timely and accurate manner. ...(more)

The 2019 State of Database DevOps results, live with Donovan Brown - Donovan Brown, Principal DevOps Manager at Microsoft, joins Steve Jones, Microsoft Data Platform MVP to discuss the latest in all things Database DevOps. They offer a closer look at the key findings in the 2019 State of Database DevOps Report, and investigate the growing importance of the database in successful DevOps and IT performance....(more)

Vendors/3rd Party Products

Monitoring the Application with SQL Monitor: Website Activity - Monitoring application activity, alongside the database, is a good example of how development and operations people can share their skills to get a better understanding of what is happening with an application....(more)

Getting Started with Database development using SQL Provision - Create a quick, consistent and repeatable process for setting up development and test environments. In this article, Steve Jones walks us through how SQL Provision can migrate your existing development databases into clones that allow you to tear down and recreate on demand for agile testing. ...(more)

Monitoring Azure SQL Database with SQL Monitor - SQL Monitor provides a full suite of monitoring specific to Azure SQL Database and SQL Elastic Pools. You can therefore use it to monitor all your databases, regardless of whether they are on local physical servers or virtual machines, or in the Azure cloud....(more)

T-SQL

ALTER TABLE Fails on Replicated Tables With Isolation Level SERIALIZABLE or READ UNCOMMITTED on SQL2012 and Earlier - It’s 4am in the morning and my phone starts ringing. A blurry eyed, me picks it up to hear the voice of one of our application guys at the other end saying something about an upgrade he was trying to carry out failing. So it turns out that he was trying to run an ALTER TABLE statement to add a new column but it was failing......(more)

Plansplaining, part 10. Just passing through - Hugo takes a look at a query and execution plan that may appear perfectly normal and expected at first sight, but that has some confusing execution counts....(more)

What Queries Does Microsoft’s Telemetry Service Run On Your SQL Server? - Brent Ozar was working in his lab and seeing some odd blocking waits He fired up sp_WhoIsActive to see what queries were running, and got a rather odd surprise....(more)

SQL Server T-SQL Alerts for Standard and Custom Errors - Our support engineers and junior DBAs are currently overwhelmed by a volume of alerts from errors each day. Most of these errors are not something they can take action on immediately and some require further analysis as to whether the error is something we expect, or something that may be a warning sign. How can we organize our alerts to help our support engineers and junior DBAs?...(more)

Regex and SQL Server: A Poor Man’s Quick Formatter - If you need standard formatting on queries, and I assure you that you do, consider getting a tool that will do a standard formatting for you. On top of that, Regex is a tool, just like any other. Add it to your belt but know when you can and should not use it....(more)

The Not Very Mighty IF Branch - Okay, so like, an IF branch in a stored proc can be helpful to control logic, but not to control performance. That’s the most important line in the blog post, now lemme show you why....(more)

How many plans are in the plan cache for a stored procedure? - It depends on where you’re looking and how many statements are in the stored procedure. Let’s take a look at some demos!...(more)

Scale SQL Server Bulk Loading On a Budget - Part 1 - During the early phases of an application, inserts perform just fine. Over time, they progress from singleton inserts, to stored procedures performing inserts, to stored procedure calls using table-valued parameters to handle multiple rows, and finally, to some sort of bulk insert or BCP process. As the application matures, and volume increases, that single table accepting this bulk data quickly becomes your most painful bottleneck....(more)

SQL Server Security and Auditing

Finding Host Names for Failed login attempts - If you manage a lot of SQL Server instances, you likely run into failed login attempts quite often. Perhaps you’re even wondering what client machine is causing all those failures. Since most environments run over TCP/IP; SQL Server helpfully logs the IP address of the client machine that made these failed login attempts to the SQL Server Error Log....(more)

SQL Server Security

Use PWDCOMPARE() to Find SQL Logins with Weak Passwords - SQL Server ships with an internal system function, PWDCOMPARE(), that we can use to find SQL logins with weak passwords. We can combine this function, along with a list of weak passwords, and some PowerShell to do a quick check....(more)

SQL injection - Hugo Kornelis bravely attempts to provide the ultimate explanation of SQL injection, in simple, non-technical terms to a twelve-year-old grandmother, and then explains how to prevent it....(more)

PowerShell

Tidier Powershell Scripts with Default Parameter Values - If you’re already splatting parameters, then default parameter values can tidy up your scripts and making them easier to read and manage....(more)

PowerPivot/PowerQuery/PowerBI

Basics of Time Intelligence in DAX for Power BI; Year to Date, Quarter to Date, Month to Date - Reza Rad explains what time intelligence is, the requirements for setting up time intelligence calculations, and the DAX functions and expression that offer insights such as year to date, year over year comparison and etc. ...(more)

How to find a Dataset ID in Power BI - Today, I had to get a single dataset ID from a report I had deployed to the Power BI Service. I quickly realized I had no idea where or how to get it! Turns out, it’s super easy to find – if you know where to look......(more)

Hiding future dates for calculations in DAX - This article describes how to write DAX measures that compute aggregations or comparisons with past dates without showing or comparing future dates....(more)

Screenshot tutorial: Add a column with custom function code in Power Query - The following steps show how to create a new column in a table using existing custom function code. This works in Power BI as well as in Power Query in Excel....(more)

Performance Tuning SQL Server

When the buffer pool isn’t just in memory - On paper Buffer Pool Extension, introduced in SQL Server 2014, is a great feature.By making use of locally-attached solid state storage, the buffer pool can extend past the physical limit of how much main memory is available to SQL Server, theoretically improving performance because that data is considered “warm.” Unfortunately there are some practical problems with the Buffer Pool Extension in 2019....(more)

Combine Extended Events and Tagwith to Monitor Entity Framework - One nit that I’ve always had with Entity Framework is that it’s very difficult to tell what part of the code the call was coming from. So what would be the best way to monitor TagWith queries in Entity Framework? Well, first, I had to go look up what TagWith was, then I got real excited, because, hey, here’s a solution....(more)

Bad indexing can show up in wait statistics - Once you start collecting wait statistics, you’ll have a lot of data to sort through. You might find waits like CX_PACKET, CX_CONSUMER, and PAGEIOLATCH. Surprisingly, these could mean that your databases aren’t well indexed....(more)

HA/DR/Always On/Clustering

Simplify Always On availability group deployments on Azure VM with SQL VM CLI - Manually deploying an availability group for SQL Server on Azure Virtual Machines (VM) is a complex process that requires understanding of Azure’s infrastructure, but new enhancements have greatly simplified the process....(more)

Index Tuning In Availability Groups Is, Like, Hard - If you use SQL Server’s DMVs for index tuning (and really, why wouldn’t you?), you need to take other copies of the data into account. This isn’t just for AGs, either. You can offload reads to a log shipped secondary or a mirroring partner, too....(more)

SQL Server Database Mirroring Status Check and Manual Failover PowerShell Scripts - PowerShell scripts that can be used to failover databases that are using database mirroring. ...(more)

DevOps and Continuous Delivery (CI/CD)

DevOps Without the Database: A Cautionary Tale - Communication is the foundation of DevOps. Grant Fritchey tells the tale of a project that failed because of previous communication and trust issues between the DBA and development teams....(more)

Reset your development database in seconds using cloning technology - When developing software, it is essential to be able to iterate quickly. The shorter the time it takes to validate an idea, the better. Some processes can become the bottleneck, limiting the effectiveness of a developer's time. This article demonstrates how SQL Provisions cloning technology can minimize the ‘drag’ on your dev and test cycles, even when working with large databases. ...(more)

Find out the real state of database DevOps - What kind of companies and organizations are introducing DevOps for the database, why are they doing it, and how are they doing it? It’s time to find out because Redgate’s third State of Database DevOps Report has just been published....(more)

Computing in the Cloud (Azure, Google , AWS)

Azure Virtual Machine Boot Diagnostics - If you have ever rebooted a virtual machine and feel like you are in the dark to it’s current state, you aren’t alone. Thankfully, Azure provides a number of tools to aid in your experience in the cloud when it comes to “reboot darkness”....(more)

Azure Data Studio – Setting up your environment - Azure Data Studio is a new tool that you can use to work with SQL Server. You can connect to multiple data systems, not just SQL Server, like Apache Hadoop HDFS, Apache Spark and others. And if you don’t find what you need, you can make more....(more)

Azure Data Factory integration with GitHub - Working with Azure Data Factory (ADF) enables me to build and monitor my Extract Transform Load (ETL) workflows in Azure. My ADF pipelines is a cloud version of previously used ETL projects in SQL Server SSIS....(more)

Applications to install locally to manage SQL Server databases in Azure - Aim of this post is to make everybody aware of what applications are available to manage databases in Azure. ...(more)

Big Data

Building A Kubernetes Cluster For SQL Server 2019 Big Data Clusters, Part 3: Big Data Cluster Creation - This post will focus on creating a big data cluster so that you can get up and running as fast as possible....(more)

Backup and Recovery

Ask A Prospective DBA This One Question - Tell them you’re setting up a brand new server, and you don’t wanna lose more than 5 minutes of data. Ask them how they’d set up backups for that server....(more)

SQL Server Looking into Differential Backups - In SQL Server your differential backup is cumulative and NOT incremental and a differential will contain the data that has changed since the last full backup. Let’s dig in using DBCC PAGE....(more)

Azure SQL Database

Classify your Azure SQL Database - There are 2 attributes to classification which are important components. These are labels and information types. Labels are used to define the sensitivity level of the data stored in the column and information types being the type of data stored in the column....(more)

Automating T-SQL for Azure SQL Database via Logic Apps - Have you ever wanted to capture the T-SQL, waits, sessions IDs (etc) at a specific time for Azure SQL Database? Sure there are a few ways to do this. Extended Events comes to mind but I wanted to do something different....(more)

Azure SQL Data Warehouse and Data Lake

Integration Testing a Data Platform with Pester - A practical example of developing and performing integration tests with the Pester framework for PowerShell. With a data platform, especially one hosted in Azure, it’s important to test that the Azure resources in your environment have been deployed and configured correctly. After we’ve done this, we can test the integration points on the platform, confident that all the components have been deployed....(more)

Azure DevOps

Reverse Engineer SQL Server Databases with Visual Studio - Azure DevOps is the next generation of Visual Studio Team Services. This product combines sprint planning, task assignment, version control, testing, continuous deployment and continuous integration into one service. The first requirement of this software is to have your schema in a Visual Studio database project. How can we transform an existing SQL Server database into a Visual Studio 2017 project?...(more)

Administration of SQL Server

Avoiding SQL Server Upgrade Performance Issues - Glenn Berry has seen many cases where organizations have migrated from a legacy version of SQL Server to a modern version of SQL Server on new hardware and a new operating system, and then be unpleasantly surprised by performance regressions once they are in Production. How can these performance regressions be occurring, and what steps can you take to help prevent them?...(more)

Agent Properties - A script that compiles the many sources of properties that affect SQL Server Agent into a single output table, which can be used to compare settings across multiple instances....(more)

List Failed SQL Server Agent Jobs, with Restart Command - If you run backups to a file share, but the file server is restarted during your backups, every running backup job will fail. Failed SQL Server Agent jobs can be really, really painful, if you have to point-and-click on every failed job!...(more)