The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

I'm sure that many data professionals recognize the importance of having backups in the event of some issue. Many of you will schedule these as soon as you create a database, and then not think much of the process, ignoring it as long as the backup job continues to run.

As you gain experience, you may even set up backups on new instances and versions without much thought, assuming you know how the process works. That's probably true of SQL Server, as the backup system and structure hasn't changed for years. However, do you think backups work the same on other platforms, such as Oracle, PostgreSQL, or MySQL? What about MongoDB or Neo4j? Don't we just run the backup command to a file and the restore from file sequence if there is an issue?

Backups aren't the same on all platforms, and they may function differently enough that you can find you aren't prepared for a disaster situation. I was considering this over the last week as I read about the challenges of restoring from Azure SQL Database and Amazon RDS. With the popularity of both cloud computing and SQL Server, we have a few different flavors of platforms between on-premises installs, AWS, Azure, and likely other hosted solutions.

Whenever you work on a new platform, even if it's a variant of one you have experience working with, there is a chance that the techniques and commands you run to manage backup or restore will change. Since a restore situation is often a stressful event, with the pressure of an RTO upon you, this isn't the time to learn that your backup preparations are inadequate.

As much as I might joke about ensuring your resume is up to date in case of a restore problem, I would emphasize that your resume isn't going to be as helpful if you are negligent and unprepared because you haven't done everything you could to be ready for a disaster. While your employer might not allocate enough disk space or other resources to ensure their RTO and RPO can be met, you can certainly ensure that your skills, knowledge, and recommendations aren't the reason that data is lost.

I periodically practice restores, I'll take a tail log backup, I'll ensure that I understand how to export a BACPAC, all to be sure that my skills are up to date. I don't want to encounter a situation where I need a filegroup, point in time, or striped backup restore and not know what I'm doing. You might want to do the same, taking time every month to ensure that you perform some different restore technique. Just in case disaster strikes next week.

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

Persisting data in docker containers – Part Two - Last week in Part One I went through how to mount directories from the host server into a docker container in order to persist data. However, you don’t have to do this in order to share volumes between containers. Another method is to create named volumes ......(more)

T-SQL

Rename default constraints - This week I'm dealing with synchronizing tables between environments and it seems that regardless of what tool I'm using for schema compare, it still gets hung up on the differences in default names for constraints. Rather than fight that battle, I figured ......(more)

What’s wrong with DELETE EXISTS? - I loved playing Number Munchers on my Apple IIc This morning, when I logged into Twitter, the tweet at the top of my timeline was from Shane O’Neill (blog|twitter). It was to his blog post, Shane, What’s wrong with DELETE EXISTS?. It’s not that unusual ......(more)

How to calculate barcode check-digit using T-SQL language - IntroductionIn order to scale, it's imperative that companies stay ahead of competition. So how can they identify, store, manage and deliver goods to customers efficiently? The answer is: Barcode! A barcode is the graphical representation of a sequence ......(more)

The Basics of Good T-SQL Coding Style – Part 3: Querying and Manipulating Data - The series so far: The Basics of Good T-SQL Coding Style The Basics of Good T-SQL Coding Style – Part 2: Defining Database Objects The Basics of Good T-SQL Coding Style – Part 3: Querying and Manipulating Data If you’ve been around SQL Server for a ......(more)

New T-SQL Functions in SQL Server 2017 - SQL Server 2017 brings some new T-SQL functions for us. They are very simple and can help us simplifying our T-SQL code. Let’s talk about them. String_AGG This new function solves an old and very interesting problem: How to concatenate several records ......(more)

SQL SERVER – Puzzle – Incorrect Results with Decimal - It has been a while we have seen a puzzle on this blog and I keep on receiving emails and comments that you all would love to see one more puzzle. Let us see a new puzzle “Incorrect Results with Decimal”. So the question is very simple and I expect everyone ......(more)

There’s Something Funny About Variable Assignment - Guess who I’m going to give you three queries, and you have to guess what the output will be before you run them. Here they are: DECLARE @DatabaseName NVARCHAR(256) = N'' SELECT @DatabaseName = d.name FROM sys.databases d WHERE d.name = 'master' AND ......(more)

Is It Time To Stop Using IsNumeric()? - The old system function IsNumeric() often causes exasperation to a developer who is unfamiliar with the quirks of Transact SQL. It seems to think a comma or a number with a 'D' in the midde of it is a number. Phil Factor explains that though IsNumeric has its bugs, it real vice is that it doesn't tell you which of the numeric datatypes the string parameter can be coerced into, and because it doesn't check for overflow. Phil comes to the rescue with a couple of useful alternatives, one of which works whatever version of SQL Server you have, and which tell you what datatype the string can be converted to....(more)

The Lighter Side

HASSP Hardware: What’s in the box?! - Ever wanted to know what a 5oz SQL Server looks like? Curious about how we're going to power it? What about sensors? Let's take a tour of the HASSP hardware! ...(more)

SQL Crossword : T-SQL Keywords : June 2017 - I did a SQL crossword last month (not my first one either) and it was pretty popular so I asked around and I’ve decided to make this a regular thing (end of the month or so) for a little while (till I get bored with it). I’m going to aim at a different ......(more)

HASSP Testing Diary #1 - Drones, knot tying 101, and streaming. Here's what we learned from our first HASSP testing session. ...(more)

Testing Software

How to unit test the Bot Framework - Today, let’s test the Microsoft Bot Framework. One of the first things I like to do when attempting to grasp a new framework (well, new to me) is to attempt to unit test the “Hello world” version of the framework. This can give a vague level of complexity ......(more)

How we approach testing VSTS to enable continuous delivery - I like to write, from time to time about our experiences, successes, failures and learnings from delivering Visual Studio Team Services (VSTS), a large scale service, on a cloud delivery cadence. My most recent post reflected on how cool it is to be ......(more)

Visual Studio Toolbox: Modern Dev Practices: Unit Testing - In this episode, I am joined by Phil Japikse, who explores how in modern development practices, unit testing is part of the development process, not a chore to be tackled after you write your code. Phil spends most of the time on Test Driven Development ......(more)

SQL Server Security and Auditing

Security Basics: The Principle of Least Privilege - Whenever I’m asked about creating a security model for an application or database, I tell folks to follow the Principle of Least Privilege. There are several definitions out there, some wordier than others. Here’s mine: Give the permissions necessary ......(more)

Example Python Program Reading SQL Azure Blob Auditing Data - I recently had a case that a customer needed a way to read the blob auditing data from Linux. This was the quickest and easiest way I could think of. First install msodbcsql following the instructions here https://docs.microsoft.com/en-us/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server You will ......(more)

The Three A’s: Authorization - Having covered authentication yesterday, let’s move on the second A, authorization. Authentication was about proving identity. Now that we know that identity, we can determine permissions. Just because we have authentication doesn’t mean we have authorization. ...(more)

The Three A’s: Auditing - Authentication and Authorization, the first two of the three A’s of security, control who gets access to what. However, at some point we’ll need to do who is accessing that what and when it happened. That brings us to the third A: Auditing. Auditing ......(more)

SQL Server on Linux

Reading the log on SQL for Linux - The SQL Server errorlog is a really helpful place to find all sorts of fun facts about your SQL Server instance. As last checked today (6/29/2017) the latest CTP build seems to have problems reading the log. This can be a problem when trying to diagnose ......(more)

SQL Server News

Released: Public Preview for SQL Server 2017 and Replication Management Pack (CTP4) - We are happy to announce SQL Server 2017 Management Pack CTP4 bits with new features are ready (6.7.60.0). Please install and use this public preview and send us your feedback ([email protected]). Please download the public preview bits at: Microsoft ......(more)

Servicing Update for ODBC Driver 13.1 for Linux and macOS Released - Hi all, we are delighted to share the servicing update of the Microsoft ODBC Driver 13.1 for Linux and macOS. The new driver enables access to SQL Server, Azure SQL Database and Azure SQL DW from any C/C++ application on Linux. Added Ubuntu 14.04, ......(more)

Release Candidate for Microsoft Drivers v4.3.0 for PHP for SQL Server Released! - Hi all, We are excited to announce the Release Candidate for SQLSRV and PDO_SQLSRV drivers. The drivers now support Debian Jessie. In addition, starting with this release, we support sql_variant type with limitations. Notable items about 4.3.0-preview ......(more)

Released: System Center Management Pack for SQL Server, Replication, AS, RS, Dashboards (6.7.31.0) - We are happy to announce new updates to SQL Server Management Pack family! Microsoft System Center Management Pack for SQL Server enables the discovery and monitoring of SQL Server Database Engines, Databases, SQL Server Agents, and other related components. Microsoft ......(more)

SQL Server Diagnostics (Preview) - Today, Microsoft is releasing the SQL Server Diagnostics (Preview) extension within SQL Server Management Studio and Developer APIs to empower SQL Server customers to achieve more through a variety of offerings to self-resolve SQL Server issues. What ......(more)

Software Development

Fighting Evil in Your Code: Comments on Comments - One of the most glib generalisations you can make about development work is to say that code should be liberally commented, or conversely that it should never be commented. As always, the truth is more complicated. There are many different types of comment and some types are best treated firmly with the delete key, where others are to be cherished and maintained assiduously. Even though it is hard to find two developers who agree on the topic of commenting, Michael Sorens warily sketches out the issues and the battleground....(more)

Security news and thoughts

Let’s Encrypt Issues 100 Million Certificates To Help Secure The Web - Let's Encrypt Certificate Authority has now issued more than 100 million certificates and has helped encrypt 18% of the web in less than two years. ...(more)

What Is Ransomware? And What To Do When You're a Victim - Computers around the world were locked up and users' files held for ransom in a cyberattack Tuesday that paralyzed some hospitals, government offices and major multinational corporations. Here's a look at how malware and ransomware work and what people ......(more)

The FAA Is Arguing for Security by Obscurity - In a proposed rule by the FAA, it argues that software in an Embraer S.A. Model ERJ 190-300 airplane is secure because it's proprietary: In addition, the operating systems for current airplane systems are usually and historically proprietary. Therefore, ......(more)

CIA Exploits Against Wireless Routers - WikiLeaks has published CherryBlossom, the CIA's program to hack into wireless routers. The program is about a decade old. Four good news articles. Five. And a list of vulnerable routers. ...(more)

New Cyberattack Causes Mass Disruption in Europe - A new and highly virulent outbreak of malicious data-scrambling software appears to be causing mass disruption across the world, hitting companies and governments in Europe especially hard. Officials in Ukraine reported serious intrusions of the country's ......(more)

Machine Learning Incident-Response Platform Unveiled - CUPERTINO, Calif., June 28, 2017 -- Demisto, Inc., an innovator in Security Automation and Orchestration technology, today introduced the industry?EU?s first Security Operations Platform that learns from analysts?EU? actions used to resolve incidents ......(more)

R Language

Useful tricks when including images in Rmarkdown documents - Rmarkdown is an enormously useful system for combining text, output and graphics generated by R into a single document. Images, in particular, are a powerful means of communication in a report, whether they be data visualizations, diagrams, or pictures. ...(more)

PowerShell

Terminating Errors In Powershell - Adam Bertram explains terminating versus non-terminating errors in Powershell: Non-terminating errors are still “errors” in PowerShell but not quite as severe as terminating ones. Non-terminating errors aren’t as serious because they do not halt script ......(more)

Creating a PowerShell Module and TDD for Get-SQLDiagRecommendations - Yesterday I introduced the first command in the SQLDiagAPI module. A module to consume the SQL Diagnostics API. I have been asked a few times what the process is for creating a module, using Github and developing with Pester and whilst this is not a ......(more)

PowerPivot/PowerQuery/PowerBI

Power BI Custom Visuals Class (Module 57 – Infographic Designer) - In this module you will learn how to use the Infographic Designer Custom Visual. The Infographic Designer give you the ability to make your reports tell a story and even theme the visuals to be more relevant to the topic of your data. Module 57 – Infographic ......(more)

Data Privacy Settings In Power BI/Power Query, Part 3: The Formula.Firewall Error - In the first two parts of this series (see here and here) I showed how Power BI/Power Query/Excel Get & Transform’s data privacy settings can influence whether query folding takes place or even whether a query is able to run or not. In this post I’m ......(more)

Neural Network Concepts Part 1 - In this and next one, I will share my understanding on Neural Network and how to write the related R code inside the Power BI. First, in this post I am going to explain what is main concept behind the Neural Network and How it works .The video https://www.youtube.com/watch?v=DG5-UyRBQD4&spfreload=10 ......(more)

Integrate Power BI into Your Application: Part 3 – Embed Content - In the part 1, you learned how to register an application to be able to interact with Power BI service and Azure environment. In the part 2, you learned how the authentication process works and how you can get the access token. In this part, I’m going ......(more)

How to purchase Power BI Premium - In this video, Adam walks through how to purchase Power BI Premium for your organization. This is done within the Office 365 Admin Center and you have to be a Global Admin or a Billing Admin to make the purchase. Adam talks about what a capacity node ......(more)

Power BI and Microsoft Flow – Monitoring your data - In this video, Patrick shows how you can use Microsoft Flow, combined with Power BI, to monitor data and alert you when something is available. This takes advantage of a streaming dataset and using the schedule recurrence in Microsoft Flow to trigger ......(more)

Performance Tuning SQL Server

Introducing Open Query Store - Many of us may have heard about the amazing new feature in SQL Server 2016 called Query Store. However, there are a lot of SQL Servers out there that are neither 2016, nor will they ever be upgraded to 2016....(more)

Testing DML Statements for In-Memory OLTP - SQL Server introduced In-Memory OLTP objects in SQL Server 2014. There were many limitations in the initial release; some have been addressed in SQL Server 2016, and it’s expected that more will be addressed in the next release as the feature continues ......(more)

Row-count Estimates when there are no Statistics - I was discussing Cardinality Estimation with a colleague recently and the question came up, what cardinality does SQL Server use if you’re selecting from a column where there are no statistics available? I’ve discovered there are a few algorithms in ......(more)

Microsoft News

Microsoft's Nano Server: What to expect this fall - Microsoft is changing the positioning and feature set of Nano Server with the coming fall feature release of Windows Server 2016. Here's what to expect. ...(more)

Hardware News

SQL101: AMD EYPC 7000 Series Processors - As Kimberly blogged about earlier this year, SQLskills has an ongoing initiative to blog about basic topics, which we’re calling SQL101. We’re all blogging about things that we often see done incorrectly, technologies used the wrong way, or where there ......(more)

Western Digital Launches New My Passport Ultra HDDs: New Enclosure, Up to 4 TB - Western Digital this week refreshed its My Passport Ultra lineup of small form-factor external HDDs. The new hard drives use a new enclosure and come with software that backs up data not only from local PCs, but also from social networks and cloud services. ...(more)

Western Digital My Passport SSD Mini-Review - Flash-based external direct-attached storage (DAS) devices have evolved rapidly over the last few years. Starting with simple thumb drives that could barely saturate USB 2.0 bandwidth, we now see high-performance external SSDs. The full performance from ......(more)

Western Digital Announce BiCS4 3D NAND: 96 Layers, TLC & QLC, Up to 1 Tb per Chip - Western Digital on Tuesday formally announced its fourth-generation 3D NAND memory, developed as part of the Western Digital/Toshiba joint venture. The fourth-generation BiCS NAND flash chips from Western Digital feature 96 layers and will include several ......(more)

Why the Recent Reported Intel HT Bug is Not in Your Data Center - Yesterday I tweeted out the warning message about the HT bug of Skylake and Kaby Lake processors posted on debian.org. https://lists.debian.org/debian-devel/2017/06/msg00308.html My tweet got a LOT of retweets. A lot replied with concerns about their ......(more)

HA/DR/Always On/Clustering

Introducing sp_AllNightLog: Log Shipping at Scale, Open Source - In our Faux PaaS project, we need a backup plan – or rather, a restore plan. On each SQL Server instance, clients can create as many databases as they want, anytime they want, with no human intervention. We need those databases covered by disaster recovery ......(more)

SQL Server Workgroup Cluster FCM Errors - Background One of the new features of SQL Server 2016 is the ability to use SQL Server with Failover Cluster in a workgroup rather than joined to Active Directory. When working with SQL Server and Failover Clustering in a workgroup, many of the abilities ......(more)

Graph Databases

Graph Database with T-SQL - SQL Server 2017 will bring users graph database. Graph database is type of database that uses graph attributes – edges (or relationships) and nodes (or vertices) and features, to store data. Relationship between nodes are through edges and features (also ......(more)

DevOps and Continuous Delivery (CI/CD)

Extending DevOps practices to SQL Server databases – Branching & Merging with VSTS - Join Microsoft Data Platform MVP Steve Jones for the next Database DevOps demo webinar. He’ll be plugging Redgate’s tools into Visual Studio Team Services, and showing how you can branch and merge your database changes. Sign up here....(more)

Top 10 DevOps operational requirements - One of the key tenets in DevOps is to involve the Operations team in the full software development lifecycle and, in particular, to ensure that operational requirements are incorporated into the design and build phases....(more)

Database Design, Theory and Development

Unmuddling Modeling Part I: Relations & Relationships - This is a 06/18/17 rewrite of a 04/14/13 to bring it in line with the McGoveran interpretation of Codd's RDM[1].Similarly, as we have explained, some object group properties arise from relationships among individual properties of its members and/or among ......(more)

Data Science

General Electric Builds an AI Workforce - As part of its shift toward high-tech businesses, the 125-year-old company is threading artificial intelligence throughout its operations, starting with its scientists. ...(more)

Data Analytics Education and Data Science: The University of Maryland’s Ten-Year Plan - On May 1, 2015, the University of Maryland’s Department of Computer Science presented a 10-year plan, placing a focus on expanding two areas of computer science: a comprehensive Data Analytics education and Data Science program, that also included a ......(more)

Conferences and Events

Announcing Expert SSIS Training with Andy Leonard - SQL Server Integration Services has changed a lot over the last decade, but one thing has remained the same: Andy Leonard has been the go-to name in the business. I’m excited to announce that we’ve partnered with Andy for online delivery of his master-level ......(more)

Power BI, DAX, Data Science & DevOps Training In London This Autumn - If you’re looking for classroom-based training on Microsoft BI, data science or SQL Server then check out the list of courses I have coming up at Technitrain this autumn: Introduction to Power BI, taught by me, September 25th-27th – a three day course ......(more)

Computing in the Cloud (Azure, Google , AWS)

Naming Conventions in Azure - I must admit right up front: I'm more than a little obsessed with naming conventions. Prefixes...suffixes...I really enjoy coming up with the optimal convention to use so that a name is at least somewhat self-documenting without being horribly long.In ......(more)

Career Growth

The Freelance Calculator - If you’re thinking about going freelance, here’s a handy Freelance Calculator by Chelsea Shaw (@chelshaw). See the Pen Freelance Calculator by Chelsea (@chelshaw) on CodePen....(more)

Backup and Recovery

Why Is Tracking Restores So Hard? - Phantom of the Nopera Let’s say you have a server. Let’s go one step further and say SQL Server is running on it. Crazy, I know. But hey, we specialize in crazy, here. If you want to track CREATE, ALTER, or DROP DATABASE commands, that’s easy enough ......(more)

Azure CosmosDB

Cosmos DB References - Here is a list of links to get you started in understanding Cosmos DB, Microsoft’s new cloud based globally distributed multi-model database. Cosmos DB development started in 2010 as project Florence. When it was initially released to the public as part ......(more)

Analysis Services / BI on the MS Stack

Tabular Editor - Tabular Editor is an editor alternative to SSDT for authoring Tabular models for Analysis Services even without a workspace server. The Tabular Editor is an open source project that can edit a BIM file without accessing to any data. This offline capability ......(more)

AI/Machine Learning/Cognitive Services

Machine Learning Comes to Tour De France - by Angela Guess A new press release reports, “Amaury Sport Organisation (A.S.O.), organisers of the Tour de France, and Dimension Data, the Official Technology Partner of the Tour de France, today announced the introduction of machine learning technologies ......(more)

Machine Learning Will Do Auto-Programming’s Heavy Lifting - Programming an information system can be strenuous labor. If you’ve ever spent hours intently producing some intricately detailed textual composition, you know what I mean. And if you’re typing out mind-numbing technical verbiage every single work day, ......(more)

Administration of SQL Server

SQL Server 2017: New ‘Queries with high variation’ Graph - A few weeks ago I published an interesting article about how to use query store to identify parameterization problems. At that point I hadn’t played with the new SQL Server 2017. While later, when I did, I got surprised by the new ‘Queries with High ......(more)

SQL SERVER – MSDB Database Uncontrolled Growth Due to Queue_messages. How to Clear All Messages From a Queue? - Received an email “Need your urgent help On Demand, our MSDB Database has grown too big and we need help to check our MSDB.” I have been an independent consultant for a while and one of the services I provide is “On Demand (50 minutes)” service. This ......(more)

Finding When Someone Last Ran a DBCC - The other day someone on Twitter asked how to find out when or who last ran a DBCC FREEPROCCACHE command on their SQL Server instance. Since this is not an unusual request I figured I would post a few examples of how to find DBCC commands or even...(read ......(more)

SQL Server : large RAM and DB Checkpointing - Hi everyone, This post’s purpose is to establish a summary of the specific behaviors with relation to DB Checkpoint that may happen within SQL Server when running with a large quantity of allocated memory ......(more)

Don’t Forget the Keys - I was recently given the nod to upgrade my monitoring server from SQL Server 2012 (SP 3) to SQL Server 2016. This came none too soon as SQL Server 2012 (SP3) goes out of mainstream support on July 11, 2017. We decided to go with a brand new box since ......(more)

Handling SQL Server Deadlocks With Event Notifications - I've seen a few different methods for handling deadlocks in SQL Server. I don't notice the use of event notifications very often, so I thought I'd share my approach. For my purposes, I want SQL to notify me when a deadlock occurs. I also want an easily ......(more)

SQL SERVER – FIX: Msg 3009, Level 16 – Could not Insert a Backup or Restore History Detail Record in the msdb Database - As most of my blogs, this blog is also an outcome of an interesting engagement with a client. While trying to help one of my clients to recover from a hardware failure, I learned something about a trace flag. Let us learn about backup or restore history. My ......(more)

Does Separating Data and Log Files Make Your Server More Reliable? - The old advice went something like this: “Put your data and log files on separate drives and your server will be more reliable. If you lose the data drive, you can still do a tail-of-the-log backup, and you won’t lose any data.” It’s advice. But is it ......(more)

Adding Performance Counters back for SQL Server - I had a strange situation the other day, where a number of things went wrong with my instance. First, I lost permissions to detached databases. The SID was listed in the file permissions, but apparently unlinked to an account. Next, I went to add an ......(more)

Using sp_WhoIsActive with Temp Tables to Limit Data Collected - “Who issssssss active?” The free sp_WhoIsActive procedure by Adam Machanic can be very useful for monitoring SQL Server: I’m a big fan. But I’ve learned the hard way that it’s easy to collect too much information with sp_WhoIsActive, particularly if ......(more)

Indirect Checkpoint and tempdb – the good, the bad and the non-yielding scheduler - Indirect checkpoint was first introduced in SQL Server 2012 to provide predictable database recovery governed by target_recovery_time option set at the database level. In SQL Server 2016, indirect checkpoint is ON by default with target_recovery_time ......(more)

Automating the Synchronization of RDS SQL Server Agent Jobs in a Multi-AZ Environment - Although Azure is the obvious Cloud service to host SQL Server, Amazon Relational Database Service (RDS) for SQL Server is a good choice when your organisation uses AWS. RDS deals with maintenance and monitoring, and supports the use of PowerShell to automate routine tasks. What if a script needs to be triggered by an unscheduled event? Even in this case, RDS can be configured to run scripts to react when something like a failover happens. Laerte Junior shows how easy it is to set up Lambda functions and some PowerShell scripts to automatically synchronise agent jobs after a failover....(more)