The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

A few years ago, I developed a SQL Server database application that supported a web application. It was a huge database and quite a popular application that sadly no longer exists, because it didn't get the long-term funding. The reason I remember it so well now was that we built a good intrusion detection system to support it. We were able to detect every attempt at intrusion and examine the forensics in detail.

We were intrigued by the sort of attacks that could occur and, out of curiosity, created a 'tethered goat' database application to understand the predators out there (a tethered goat was once used as a lure to attract predators to where the hunter lay concealed).

This 'tethered goat' database, also known as a Honeypot, was deliberately left entirely detached from our intranet, with several vulnerabilities. We had a lot of fun with SQL Data Generator to create a database that was apparently full of personal information. We made it very plausible: I've since published some of the techniques we used. We introduced a great deal of logging and monitoring to examine what intruders did.

It brought home to us forcefully that it was a big bad world out there on the internet. There are a lot of systems out there probing for all sorts of potential vulnerabilities on public-facing servers, and it paid dividends to detect any attempt that penetrated the first line of defence. Even experienced developers can forget to nail down every potential vulnerability.

The 'tethered Goat' database was fascinating because we could follow intrusions through to table access. There is some clever but wasted talent out there. For us, it was a useful education to observe a range of attacks.

We adapted our nascent database application based on what we observed, and ended up with something so watertight that we could leave it running unattended for several years subsequently, without a hiccough. It is to my shame that before the production release, one clever attacker got through the second line of security but no further. He would have done had we not had an intrusion detection system that alerted us. Oops.

In the ten years since, I've been amazed that no company seems to have developed a database intrusion detection system that comes anywhere near the one that we devised to ensure the security of our system. If you're a data person and you haven't observed attempts to penetrate a database, it is well worth getting an opportunity to do so. It will give you a new and even greater respect for database and application security. It gave me an abiding interest in the topic.

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.

Virtualization and Containers

Running SQL Server in a Docker Container on a Mac - Kendra Little demonstrates how easy it is to rebuild your SQL Server on Docker once you have things configured....(more)

Automating installation of Docker & SQL command line tools on Linux - Automating the process of installing the Docker Community Edition, then the SQL Server command line tools,and then pulling the latest SQL Server on Linux image from the Docker Hub....(more)

Vendors/3rd Party Products

A whole new way to see differences in SQL Compare - In the latest release of SQL Compare, Redgate have added a brand new way to examine the differences between two objects in your database - here are the details....(more)

T-SQL

Using conditional COUNT(*)s - Whilst recently working with historic financial data, I ran across a situation that needed an aggregate view of transactional data grouped by a certain set of attributes in order to backfill some missing aggregate data sets....(more)

Challenging SQL Server precision with Pi calculations - Sometimes it is interesting just to play with SQL Server and hit its limits. This time I've had some fun with getting closer to "Pi"....(more)

Statistics in SQL: Student’s t-test - The t-test and t distribution were developed in 1908, as a way of making confident predictions from small sample sizes of normally-distributed variables....(more)

Generate TSQL time slices - Ho to bucket log data into 15 second time slices using the LEAD, TIMEFROMPARTS and ROW_NUMBER functions....(more)

Desktop Wallpapers and Webcasts on Isolation Levels and Memory - Kendra Little shares four new SQL Server desktop wallpapers, covering Top Queries in SQL Server, SSMS Shortcuts & Secrets, Partitioning Jargon and The Case of the Slow Temp Table Update....(more)

The COMPRESS() TSQL Function in SQL Server 2016+ - One cool little feature in SQL Server 2016 is COMPRESS(). It’s a TSQL function available in all editions that shrinks down data using the GZIP algorithm....(more)

Learning through gaming a SQL Server tale - How can I captivate the wondering mind of a 17 yr old who would probably rather be gaming on his phone than listening to dad about tech? Tech gaming. HAHA....(more)

Locks, Blocks, and Isolation Levels - Isolation levels work to manage how my transactions may be isolated from your transactions, to allow concurrency. They work by locking sections of the database until a transaction is committed or rolled back, which results in blocking other access to those sections of the database....(more)

SELECT…INTO in SQL Server 2017 - One of the new options available in SQL Server 2017 is the ability to specify a filegroup when using SELECT..INTO to create a table....(more)

The Basics of Good T-SQL Coding Style – Part 4: Performance - There are several obvious problems with poor SQL Coding habits. It can make code difficult to maintain, or can confuse your team colleagues. It can make refactoring a chore or make testing difficult. The most serious problem is poor performance. You can write SQL that looks beautiful but performs sluggishly, or interferes with other threads. A busy database developer adopts good habits so as to avoid staring at execution plans. Rob Sheldon gives some examples....(more)

Data in Motion and Data at Rest - Microsoft (StreamInsight), and Azure Stream Analytics represent a very different model for processing data. They are concerned with processing complex event streams of data (CEPs) from such things as sensors to deduce significant patterns and apply filters. Joe Celko discusses the background to an intriguing technology of complex event processing to establish the difference between data at rest, and data on the move....(more)

Representing a simple hierarchical list in SQL Server with JSON, YAML, XML and HTML - How difficult can it be to produce a simple hierarchical list in JSON, YAML, XML and HTML from a SQL Server table that represents a simple hierarchy within an organisation. Well once you know, it is easy and William Brewer is on a mission to tell you how....(more)

Tools

Automated Database Provisioning for Development and Testing - If your development team needs to work on anonymised copies of the current production database, and if changes are being delivered rapidly as well, that could mean a lot of time and routine DevOps work copying databases. SQL Clone was designed for tasks like this. Grant Fritchey investigates whether you save time, effort and scripting over the more traditional approach, and at what point it makes sense to use it....(more)

SQL Server News

SQL Updates Newsletter – July 2017 - Summarizing announcements, releases, issue alerts, recent articles and more....(more)

SQL Server 2017 Release Candidate 2 (RC2) is now available - The release candidate represents an important milestone for SQL Server. Development of the new version of SQL Server along most dimensions needed to bring the industry-leading performance and security of SQL Server to Windows, Linux, and Docker containers is complete. ...(more)

SQL Server Internals

In the footsteps of a cooperative wait - Today we reach a part of the OS thread stack trace where the thread was deep in thought, but stepped into the open manhole of a latch wait, which – as is typical for most waits – involves an EventInternal as underlying mechanism....(more)

Software Development

Thoughts on Building Community Tools - No.1: Build something that solves a real pain for you every day. If you’re feeling the pain, other people are feeling it too, and there’s nobody else who understands your pain the way you do....(more)

Replication

My three favorite transactional replication stored procedures - Through my time with replication, I’ve researched and used a lot of system tables, views, and stored procedures to diagnose or fix replication problems. Here then, in no particular order, are three system stored procedures that I feel any DBA that has to deal with transactional replication should at least be aware of....(more)

R Language

A modern database interface for R - The new odbc package is a from-the-ground-up implementation of an ODBC interface for R that provides native support for additional data types (including dates, timestamps, raw binary, and 64-bit integers) and parameterized queries. The odbc package provides connections with any ODBC-compliant database, and has been comprehensively tested on SQL Server, PostgreSQL and MySQL....(more)

PowerPivot/PowerQuery/PowerBI

New Series of Time Series: Part 1 - A new series looking at look at the time series models and how we able to use them for forecasting data....(more)

Fixing Power BI Quick Measures – Time calculations [video] - How to fix quick measures that break when you use dates with time calculations....(more)

Performance Tuning SQL Server

There is a magic button, a rant - Despite the fact of adaptive plans and automated regressions, you’re still writing code that NO amount of automation can fix. Queue a scarydba rant....(more)

SQL Server 2016, Minimal logging and Impact of the Batchsize in bulk load operations - Bulk load context (also referred as fast inserts or fast load context) and minimal logging are now "on by default" when performing bulk load operations on database with simple or bulk logged recovery model....(more)

Correlating xEvents with DMVs - Sometimes correlating data from xEvent collections and DMVs is not easy because the xEvent actions above are not the same data types as the respective columns in the DMVs....(more)

Are your indexes being thwarted by mismatched datatypes? - Have you ever encountered a query that runs slowly, even though you’ve created indexes for it?...(more)

Cardinality Estimation: Combining Density Statistics - This article shows how SQL Server combines density information from multiple single-column statistics, to produce a cardinality estimate for an aggregation over multiple columns....(more)

Microsoft Resources

Microsoft Centralizes Online Product Documentation - Tim Ford takes a tour of docs.microsoft.com, Microsoft's new portal for all their application/framework documentation....(more)

LINQ/Entity Framework

Using SQL Server Query Hints with Entity Framework - Entity Framework (EF) is designed to work with a variety of data sources. Although this presents many advantages, there is a downside that many of the special features of a data source such as SQL Server are off-limits. Query Hints are an example: though often misused, they are occasionally important. Dennes Torres shows how you can use these in EF, using a command interceptor that will allow you to use any query hint with SQL Server....(more)

Database Design, Theory and Development

Structure, Integrity, Manipulation: How to Compare Data Models - "Handling data in a different way" is so much handwaving. Data management IS (1) manipulation of some (2) data structure with 3) protection of data integrity, the three components of a data model -- i.e., there is no data management without one. ...(more)

SQLCLR in Practice: Creating a Better Way of Sending Email from SQL Server - SQLCLR is now considered a robust solution to the few niche requirements that can't be met by the built-in features of SQL Server. Amongst the legitimate reasons for avoiding SQLCLR, there is the fear of getting bogged down in code with special requirements that is difficult to debug. Darko takes a real example, extending the features of sp_send_dbmail, to demonstrate that there need be few terrors in SQLCLR....(more)

Data Privacy

Pseudonymization and the Inference Attack - It is surprising that so much can be identified by deduction from data. You may assume that you can safely distribute partially masked data for reporting, development or testing when the original data contains personal information. Without this sort of information, much medical or scientific research would be vastly more difficult. However, the more useful the data is, the easier it is to mount an inference attack on it to identify personal information. Phil Factor explains....(more)

Who was that masked man anyway? - The task of keeping data secure and protected within an organization seems a lot more challenging than one might imagine. Data masking is the correct way to ‘anonymize’ your data, but if you only mask some of the data you still need proper data security and access control. Otherwise, like chinks in a mask, it can reveal tell-tale signs of the owner’s identity....(more)

Career Growth

Learning designed for the data professional - PASS Summit offers five educationally-packed days lead by highly-rated, experienced speakers and sessions focused on the way you work with data. Speaker highlights include Bob Ward, and Sunil Agarwal from the Microsoft Tiger Team. Join PASS Oct 31 to Nov 3....(more)

Azure SQL Database

Migrate multiple SQL databases to Azure SQLDB - Kevin Hill pushes 4-5 small test databases to Azure SQLDB at the same time. In reality, this might be hundreds of databases, with the same schema but wildly varying sizes, but the basic process will be the same....(more)

MSBuild and Azure SQL Database - Steve Jones investigates a problem building a database with ReadyRoll in Azure SQL Database, which used a local Shadow database in LocalDB and targeted an Azure SQL Database....(more)

Azure SQL Data Warehouse and Data Lake

Handling Row Headers in U-SQL - A quick tip about the syntax for handling row headers in U-SQL, the data processing language of Azure Data Lake Analytics....(more)

Analysis Services / BI on the MS Stack

Creating Animated Reports In Power BI With The Drilldown Player Custom Visual - Last week I had the chance to do something I have not done before: build a Power BI report to be displayed on a big screen hanging on a wall. To make up for the loss of user interactivity, I used the new Drilldown Player custom visual to cycle through different selections and display a new slice of data every few seconds....(more)

New function and the importance of variables in dax - So, what is new in DAX? The SELECTEDVALUE function! It’s only syntax sugar, and my only concern is that this could increase the use of a bad practice, which is getting the value of the current row after a context transition. ...(more)

Administration of SQL Server

Upgrading SQL Server–Day 1 - Glenn Berry starts a new series of blog posts about upgrading and migrating to SQL Server 2016/2017 from an older version of SQL Server. ...(more)

Chaining Agent Tasks For Alerting The Right Way - When you think about setting up maintenance, a persistent fear is that tasks may step on each other. You probably don’t want backups and CHECKDB and statistics maintenance running at the same time as statistics maintenance....(more)

Why Missing Index Recommendations Aren’t Perfect - When you see a missing index request, don’t think of it as a request to create a specific index. Think of it as SQL Server tapping you on the shoulder, going, “Hey, friend, take a look at your indexes.”...(more)

What is the biggest mistake you made in production? - When you have sysadmin access, you are bound to make a big mistake in production at some point in your career. Not everyone has. Maybe they’re perfect, or maybe it just hasn’t happened yet. I’ve made two big mistakes in production....(more)

SQLskills SQL101: REBUILD vs. REORGANIZE - Often when discussing index fragmentation I find that people aren’t aware of all the differences between rebuilding an index (using ALTER INDEX … REBUILD) and reorganizing an index (using ALTER INDEX … REORGANIZE), so that’s the topic for this post. ...(more)

Upgrading to SQL Server 2016, Part Three - After your upgrade to SQL Server 2016 is complete, you’ll need to perform a series of tasks to verify that the database is ready to be handed over to end-users for further testing. ...(more)

Start Your Engines with Startup Procedures - Startup procedures automatically execute whenever SQL Server is started. Where would you use this? One example is if you had an application that required the use of a global temporary table, you could use a startup procedure to create it....(more)