The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I live out in the country, with no houses near my home. If you follow me on Twitter, you might see pictures of my front yard, which shown the nearest house is quite far away. As a result, I haven't had any issues with package thieves. I'd be surprised if anyone had much trouble out in our area, as it's not easy to see packages on most houses from the street and more than a few of my neighbors are type that might respond to thievery with overwhelming force.

That's not the case for many people that live in suburban or urban areas, where package thievery is becoing a regular occurrence. I arn across a story of an engineer that created a glitter bomb to penalize and get information about some thieves. The video is interesting and it's fun to watch, although there are some questions as to whether this actually caught any thieves. Still, it's a good idea and likely was a fun project. Perhaps overkill as I've know some friends that caught thieves on video that the police used to capture the individuals.

It's the end of the year, and 2018 was a year rife with lots of data theft by hackers. As I think back on the year, and worry about what might be coming in addition to ransomware, malware, and ingenious social engineering, I wish there were a good way to seed data with a glitter bomb equivalent that might make it easy to track them down. I'd certainly love to know who has handled information, especially with regards to the methods used to gain access. I know there are forensic tools and methods that can determine how and when data was accessed, which might prove valuable in the aftermath of a data breach.

I'd like something else. Something more impactful. A data bomb that might embarress and humiiliate thieves, one that might expose them for the criminals they are. Perhaps a bomb that might get them to rethink their career choice. Spy thrillers are full of misinformation capers and that could be one way to get back at anyone that gets your data. It's unlikely that any such plot would really work or even have an impact on the number of data breaches taking place, but it might be fun setting one up.

I hope you've had a secure 2018 and haven't lost data and the same happens in 2019.

Steve Jones 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

Why you should consider VS Code for your Kubernetes/Docker work - Premier Developer consultant Julien Oudot spotlights VS Code for Kubernetes and Docker workloads. Visual Studio Code (VS Code) is sometimes considered as a slimmer and minimalist version of Visual Studio. However, depending on the technology stack used, ......(more)

Running Windows and Linux SQL Containers together - Just for fun I decided to spend Christmas Eve getting Windows and Linux SQL containers running together. WARNING This is NOT a production ready solution, in fact I would not even recommend that you try it.I definitely wouldn’t recommend it on any machine ......(more)

T-SQL

SQL SERVER – How to Count a Particular Value Across All Columns? - This question came up in the recent SQL Server Performance Tuning Practical Workshop. During the workshop, I have answer questions of users during lunch break. One of the users had a very interesting scenario where he had to change one of their employee’s ......(more)

Prevent Full Script Execution (Understanding and Using PARSEONLY and NOEXEC) - There are times when I am working on a SQL script that really shouldn’t be executed all at once. Sometimes it’s a series of examples / demos for a presentation or forum answer. Other times ......(more)

Testing SQL in the Advent of Code - I like participating in the Advent of Code each year, though my participation often varies wildly as life gets in the way. Still, trying to solve some programming challenges is a good way of practicing your skills. If you’re competitive, you can try ......(more)

Saving Data to Various Formats, Using SQL - I enjoy pulling the data out of AdventureWorks. It is a great test harness. What is the quickest way of doing it? Well, everyone knows it is native BCP, but how much faster is that format than tab-delimited or comma-delimited BCP? Can we quickly output ......(more)

SQL SERVER – Performing IF…THEN In T-SQL With IIF Expression - Today, we are going to see one of the most underutilized features of SQL Server IIF. In SQL Server we can performance IF…THEN logic in T-SQL with IIF Expression but I hardly see it being used at most of the organization. I believe there are two reasons ......(more)

SQL Server – Find Distinct Result Sets Using EXCEPT Operator - In my earlier post on “SQL SERVER – Remove Duplicate Rows Using UNION Operator“, I showed a different method of removing duplicate rows from the result set using a UNION operator. Here is another similar method using EXCEPT operator to find distinct ......(more)

Identity Gaps–#SQLNewBlogger - Another post for me that is simple and hopefully serves as an example for people trying to get blogging as #SQLNewBloggers. Many people think that that an identity property will ensure a consistent, increasing numerical value. I ran across this tweet ......(more)

The Lighter Side

Glitter Bomb against Package Thieves - Stealing packages from unattended porches is a rapidly rising crime, as more of us order more things by mail. One person hid a glitter bomb and a video recorder in a package, posting the results when thieves opened the box. At least, that's what might ......(more)

SQL Server Security

Picking over the Bones of a SQL Injection Attack - Some developers are like bikers. They think, or rather hope, that accidents, intrusions and disasters are what happen to other people. They aren’t. The Developer Joe De Vell sat blinking in some confusion at the unexpected errors scrolling suddenly down ......(more)

SQL Server News

Watch: The Best of 2018 and Predictions for 2019 (video) - I recently got together with fellow Microsoft Data Platform MVPs Steve Jones, Kathi Kellenberger and Grant Fritchey to discuss the highlights of innovations we’ve seen in 2018 and predictions for 2019. The 63 minute recording is available to watch anytime ......(more)

Security news and thoughts

Massive Ad Fraud Scheme Relied on BGP Hijacking - This is a really interesting story of an ad fraud scheme that relied on hijacking the Border Gateway Protocol: Members of 3ve (pronounced "eve") used their large reservoir of trusted IP addresses to conceal a fraud that otherwise would have been easy ......(more)

Pilot project demos credit cards with shifting CVV codes to stop fraud - Idemia US-based PNC Bank is in the middle of a pilot project that aims to test out credit cards with constantly changing card verification values (or CVVs) to reduce online credit card fraud. The dynamic CVV is displayed on the back of such a card in ......(more)

Start a Security To-Do List - Protecting an organization depends on deep knowledge and experience, but a security to-do list can go a long way toward locking things down. ...(more)

Recent Security Breaches, IoT Vulnerabilities Make Top Stories of 2018 - From the Spectre and Meltdown vulnerabilities to recent security breaches, 2018 was a year in cybersecurity that had its share of ups and downs. ...(more)

Replication

SQL Server Database Replication - Database replication is the technology to distribute data from the primary server to secondary servers. Replication works on Master-slave concept where Master database distributes data to one or multiple slave servers. Replication can be set up between ......(more)

R Language

Forecasting Kyle Kuzma’s Field Goal Percentage (FG%) - More and more tools are becoming available for data analysts and data scientists. These tools provide convenience and lower the bar of entry for aspiring data professional. I’ve been looking for an easy way to get to learning predictive analysis and ......(more)

Product Upgrades and Releases

Updated First Responder Kit for January 2019: Extraneous Syllables - Does anyone know what the date really is? I usually don’t, and I often consider how doomed humanity would be if I were the smartest person on the planet during any given era. Anyway, you should go hug someone smarter than you. Unless they have sensory ......(more)

Product Reviews and Articles

On Quickly Investigating a SQL Monitor Custom Security Alert - I recently wrote a series of article that described four SQL Monitor custom metrics, designed purely to monitor for the signs of intrusion, or other unauthorized database changes. These metrics allow you to implement separate strategies for: Detecting ......(more)

PowerShell

Using PowerShell to tidy up CSV files - I was recently fussing with a CSV file that had an inconsistent number of columns. UGH. There was a bunch of historical data that had 4 columns, and then more recent data had a new attribute added with 5 columns. I spent a few minutes cursing whoever ......(more)

Automation to list all TCP static and dynamic ports of SQL Server instances using PowerShell - I was asked by the reader on how to get all the port information of SQL Servers instances. As you all know that PowerShell is built on .Net automation framework. I would prefer PowerShell to design any such requirements than … Continue reading ? ...(more)

PowerPivot/PowerQuery/PowerBI

Data Privacy Settings And Data Refresh Performance In Excel And Power BI - I have known for a long time that data privacy checks can have an impact on the performance of data refresh in Power BI and Excel, but on a recent performance tuning engagement I had the chance to observe just how much of a difference changing these ......(more)

Performance Tuning SQL Server

Improve Performance of UDFs with NULL ON NULL INPUT - At PASS Summit a few weeks ago, Microsoft released CTP2.1 of SQL Server 2019, and one of the big feature enhancements that is included in the CTP is Scalar UDF Inlining. Prior to this release I wanted to play around with the performance difference between ......(more)

When Does SARGability Matter Most? - !erehT iH I know what you’re thinking. Another post about how you should just never do this one thing and all your queries will magically end up faster. Just watch the video.   For more reading, check out these posts: Sargability: Why %string% Is Slow Optional ......(more)

MDX/DAX

Creating Measures Using DAX - A measure is any formula which aggregates data, whether it be counting the number of transactions in a database, summing the sales figures for a region, or working ......(more)

HA/DR/Always On/Clustering

SQL SERVER – Cluster Install Failure – Code 0x84cf0003 – Updating Permission Setting for Folder Failed - There are various issues which I have seen SQL installation and most of the time they are intuitive. The error message is mostly helpful and provides the right direction. In this blog we would discuss error Updating permission setting for folder failed: Here ......(more)

20 Questions to Ask About Your Availability Group Design - Questions about the overall project: What are your RPO and RTO goals? Are there financial penalties if we miss the goals? (Like contracts, refunds to customers, etc) Does this app have regularly scheduled maintenance windows, or is it 24/7? What’s the ......(more)

Graph Databases

SQL Graph, part IV – Merge DML Support (aka MATCH support for the MERGE against the Edges) - This is the 4th blog post in the series of blogpost on the Graph features within SQL Server and Azure SQL Database. This particular post is dedicated to the support of the MERGE DML for Graph tables in Azure SQL Database, that Microsoft has announced ......(more)

Data Warehousing

Data Warehousing Tip #2 – What your data warehouse should be - I’ve seen data warehouses that nobody trusts, or are just too complex for end users to report on.  Users will voice their concerns (usually quite loudly).  Even worse, they will go back to using the relational source to get “accurate” data or because ......(more)

Data Science

Ten Myths About Data Science - Click to learn more about author Daniel Jebaraj. Introduction Data Science is now being used as a competitive weapon. As with other technologies and processes that can transform the way companies operate, there’s a lot of contradictory information about ......(more)

Data Privacy, Complianace, and GDPR

EU-U.S. Privacy Shield: Second Review Shows Improvements - A recent press release states, “Today the European Commission publishes its report on the second annual review of the functioning of the EU-U.S. Privacy Shield. This year’s report shows that the U.S. continues to ensure an adequate level of protection ......(more)

Computing in the Cloud (Azure, Google , AWS)

Azure – Creating a storage account - Azure offers a lot of features that enable IT professionals to really enhance their environment.  One feature that I really like about Azure is storage accounts.  Since disk is relatively cheap, this continues to hold true in the cloud.  For less than ......(more)

Azure SQL Database

A Look at Some of Azure SQL Database’s Intelligence Features - Today I’d like to tell you about some very cool intelligence features within the Azure SQL Database. Azure SQL Database technologies deliver intelligent capabilities through a range of built-in machine learning and adaptive technologies that monitor ......(more)

Administration of SQL Server

Shrinking Tempdb in SQL Server won’t work? - Problem statement When I was working DML operation on the database, Tempdb has grown  80 GB. After the completion, It is noted that the tempdb has grown significantly larger and I didn’t have much space on this Server. I tried … Continue reading ? ...(more)

SQL Servers Black Box Recorder – Def Trace - Across many professions and industries there is often a need to have some sort of device that “audits” everything that happens with a device or process. We are probably all quite familiar with the infamous black box recorders used by the passenger airline ......(more)

How to: File Target use in Extended Events - I have been doing a lot of posts of late about extended events but have failed to share some of the essential building blocks. I am not looking to do a series of definitions, but just share a couple of quick tips and examples here and there that will ......(more)

How to Track Autogrowth of Any Database? – Interview Question of the Week #205 - Question: How to Track Autogrowth of Any Database? Answer: Just the other day during the Comprehensive Database Performance Health Check, we identified that the SQL Server Performance problem of the user was because of the constant Autogrowth of their ......(more)

How To: XEvents as Profiler - A common excuse for not delving into extended events seems to be the jump from Profiler to extended events.  There appears to be an inherent fear with how to use extended events.  In addition to that fear, is the fear that extended events does not do ......(more)

Upgrading From SQL Server Profiler - I just shared an article describing how to use Extended Events to perform your SQL Server Profiler duties. If not, you can read all about it here. In that article, I showed one common use-case for SQL Server Profiler and how to achieve the same result ......(more)