The Complete Weekly Roundup of SQL Server News

Hand-picked content to sharpen your professional edge

This week's editorial is a guest post from Kathi Kellenberger.

SQL Server had been on a two-year release cycle for some time, but SQL Server 2017 arrived less than 18 months after SQL Server 2016 became available. Each release of SQL Server brings exciting new features and improvements to existing capabilities. Many organizations are running instances that are several versions of SQL Server behind. While keeping up with the latest SQL Server versions is challenging, staying behind puts you at risk of losing mainstream support and missing out on beneficial features. Often database administrators must support multiple versions at once, and consultants face an even greater range of versions among their customers.

While the release cycle has been shrinking, Microsoft has not committed to any specific frequency for releasing new versions of SQL Server. They do, however, want to make innovations available on a timely basis for those customers who want to use them. No one expects Microsoft’s customers to upgrade every time that at new version is available, and skipping a version or two is an option for shops that want to move at a slower pace.

During presentations, I often poll the audience about the versions they use. The number of hands being raised for 2000 and 2005 has diminished over the years. Quite a few of my clients, mostly small businesses, are running SQL Server 2008 R2.

So many organizations are running 2008 R2, one might think it is the best version of SQL Server ever produced. The actual truth is not so simple. Some companies are hesitant to make the move off 2008 R2 because of the change to per core licensing. Others are happily hosting their databases on older versions and see no reason to change and possibly break something that is working for them. The effort involved with testing and upgrading is discouraging, and some companies are running legacy applications that work only on ancient versions, SQL Server 2000 for example, and do not have a choice.

Memories of the painful experience of upgrading from SQL Server 2000 are still fresh for some shops, but the compatibility gap between versions is much narrower once past 2005. To make upgrading easier, Microsoft generally provides a tool called The Upgrade Advisor for each new version that will spot issues and provide a chance to resolve them before starting the upgrade process. Virtualization also makes setting up testing environments much simpler and quicker.

Sometimes a fantastic feature will motivate an upgrade. With each new version there are enhancements to T-SQL, improved availability and disaster recovery functionality, more security options, and additional ways to get better performance. With 2016 service pack 1, many previously Enterprise only features were ported down to more affordable editions. This is a game changer for many organizations and makes the upgrade to 2016 more attractive.

Another consideration is support. Time passes quickly, and it doesn’t take long to reach the end of mainstream support. SQL Server 2008 R2, for example, has been out of mainstream support since 2014. While it’s still in extended support, which will ensure security hotfixes, other support features are available only on a paid basis.

Costs, effort, and possible dizziness due to the pace that new versions are released are holding back some companies from upgrading. What is keeping your shop from upgrading SQL Server?

Kathi Kellenberger 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.

Vendors/3rd Party Products

How to automatically provision sanitized data using SQL Clone, Data Masker, and PowerShell - This article describes a strategy, using data masking, cloned databases, and PowerShell, which will allow you to sanitize data before it is copied or moved, for example from a production environment into testing or development environments....(more)

How SQL Monitor Monitors Azure-based SQL Server Failover Cluster Instances - This article will show you how to set up SQL Monitor to monitor Azure-based clustered instances right alongside your on-premise clusters....(more)

Why your Development team needs SQL Prompt - SQL Prompt is the industry-leading SQL IntelliSense and code-formatting tool, and is an investment in developer versatility and productivity. It will both increase personal coding productivity and improve team-based development practices, leading to higher quality, more consistent and more reliable code....(more)

Keeping track of history: SQL Compare and Temporal Tables - SQL Compare 13 introduces support for deploying changes to temporal tables, which were introduced in SQL Server 2016. This means that any changes made to the definition of a temporal table in a source database, including to the retention policy for that table, will be included automatically in any deployment script that SQL Compare generates for that database....(more)

Tools

Introducing sp_BlitzLock: For Troubleshooting SQL Server Deadlocks - Brent Ozar introduces a new SP for helping you handle deadlocks....(more)

The Lighter Side

Database History: The Inventor of the Data Page - Do you ever wonder about the history of the systems you rely on every day? They didn’t teach us database history in school. Instead, we got calculus and art and interpretive dance. Fat lot of good that did us, right?...(more)

Tech News : General Interest

Industry Experts Predict Progress in Integrating AI, Big Data in 2018 - The most important big data trend in 2018 will involve greater integration with artificial intelligence (AI), data science and machine learning, but metadata management and global data fabrics will also play a key role. ...(more)

Predictions 2018: How GDPR is Forcing Big Changes in Storage - Experts believe we might see things like machine-learning-assisted storage and de-archiving becoming commonplace during the coming calendar year....(more)

SQL Server Internals

Free eBook: SQL Server Internals: In-Memory OLTP - In this book Kalen Delaney introduces and explains how the 2016 In-Memory OLTP engine works. The Hekaton internals knowledge offered in this book will help you migrate existing tables or databases to Hekaton, and get faster performance from your SQL Server applications than you ever thought possible....(more)

Software Development

Building SQL REST API back-end for Angular apps - JSON support in SQL Server/Azure SQL is designed to make integration between the relational database models and the modern single-page JavaScript apps easier than ever....(more)

Security news and thoughts

Fixing Data Breaches Part 1: Education - Troy Hunt kicks off a series on addressing the root causes of data breaches....(more)

Performance Tuning SQL Server

Hidden Performance and Manageability Improvements in SQL Server 2012 and 2014 - Microsoft has back ported a number of very useful performance improvements from SQL Server 2016 into to both SQL Server 2012 and SQL Server 2014, as long as you are on a new enough build of either of these older versions of SQL Server. ...(more)

Capture Index Maintenance Operations - One good database maintenance practice is to keep the indexes in good working order. This is typically done via index defragmentation routines....(more)

Rebuilding Indexes Can Slow Queries Down - Brent Ozar demonstrates how fragmentation can, in some cases, lead to faster queries....(more)

Impact of Fragmentation on Execution Plans - Index fragmentation removal and prevention has long been a part of normal database maintenance operations, not only in SQL Server, but across many platforms. Index fragmentation affects performance for a lot of reasons, and most people talk about the effects of random small blocks of I/O that can happen physically to disk based storage as something to be avoided....(more)

Impact of Fragmentation on Execution Plans - The idea that index fragmentation doesn’t matter with Solid State Storage devices has started circulating recently, but this is not the case for a number of reasons....(more)

Microsoft News : General Interest

Connect is dead! Long live feedback.azure.com! - After years of having to deal with Connect – the feedback platform of Microsoft – it is announced a successor has been found: feedback.azure.com....(more)

Machine Learning

The Trouble with Bias [Video] - Bias is a major issue in machine learning. But can we develop a system to "un-bias" the results?...(more)

SQL Server Machine Learning Services – Part 2: Python Data Frames - If you’ve spent any time with the R language in SQL Server R Services or Machine Learning Services you’re no doubt aware of the important role that data frames can play in your scripts. The same is true for Python. You use data frames when passing data sets into and out of a script as well as when manipulating and analyzing data within that script....(more)

Graph Databases

Transitive closure clustering with CLR and JSON - Transitive closure is a graph algorithm that tries to follow paths in graph edges and tries to find all elements that can be reached from some element, or groups of elements that are mutually reachable. Although SQL Server still don’t provides native function for transitive closure, this algorithm can be implemented using CLR aggregates that can be placed in SQL database....(more)

DevOps and Continuous Delivery (CI/CD)

If you hear these phrases, you might need Microsoft Visual Studio Team Services - To help make the abstract features of VSTS a bit more real, I put together some phrases that you might hear that VSTS can help eliminate. When combined with DevOps and agile development practices, VSTS can remove these phrases from your office vocabulary and let everyone focus on the fun part: making awesome software....(more)

Database Design, Theory and Development

Maintenance Plans Enable Your Disabled Indexes - One of Brent’s students in the Performance Tuning class, Jason M., told Brent that maintenance plans enable your disabled indexes. What the what?...(more)

Data Privacy

SQL Server 2016 – How to use Dynamic Data Masking (DDM)? - Dynamic Data Masking (DDM) is new feature of SQL Server 2016. DDM helps in preventing unauthorized access to sensitive data. DDM enables only privilege users to have complete data. Un-privilege users will have access of MASKED data only....(more)

Computing in the Cloud (Azure, Google , AWS)

Microsoft Releases Lower-Cost Cloud Storage for Rarely-Accessed Data - The new Azure Archive Blob Storage option allows businesses to store their infrequently accessed data on Microsoft's cloud for less than a penny per gigabyte per month....(more)

Career Growth

Introduction to Agile with SQL Server - Working with SQL Server in Agile software development can be difficult. Ben Brumm provides an overview of Agile, specifically Scrum, and what to expect when SQL Server, or really any SQL platform, is involved. ...(more)

Thoughts On Technical Interviews - Interviewing can be an emotional (and frustrating) process, and technical interviews in particular can be rife with mismatched expectations....(more)

2018 Data Professional Salary Survey: Early Results Thoughts - Brent Ozar's 2018 Data Professional Salary Survey is open, and you can analyze the results as they’re coming in. As of this writing, they've just crossed 1,000 results (last year there were around 3,000)....(more)

Azure SQL Database

Azure ARM template function internals - If you haven't used an ARM template, ARM templates are JSON documents which are effectively a whole load of properties made up of a name and a value. The value can either just be a value or it can include arm template functions....(more)

Query big data, stored in a csv or parquet files, from any stored procedure - This article will show you how you can use Management Studio or any stored procedure to query the data, stored in a csv file, located on S3 storage....(more)

Analysis Services / BI on the MS Stack

Computing same product sales in DAX - This article shows a technique in DAX to compute the sales volume of products that were available right from the beginning of a selected time period, ignoring products introduced afterwards....(more)

Dashboard Sharing, and Manage Permissions in Power BI; Simple, but Useful? - Power BI provides multiple ways of sharing the content with users. Each sharing method has pros and cons and can be used for specific scenarios....(more)

Administration of SQL Server

When Measuring Timespans, try DATEADD instead of DATEDIFF - If DATEDIFF is updating too frequently, switching to DATEADD may give you more fine-grained control....(more)

Why do we have repeatable read and serializable? [Video] - Serializable and Repeatable Read isolation levels offer protections so your users won’t see weird or incorrect data — but there are tradeoffs for those protections....(more)

When do usernames and passwords belong in connection strings? - In SQL Server there are two types of ids. Windows authenticated and SQL Server authenticated. Typically you can tell the difference because windows authenticated ids have a domain attached....(more)

Query Store Wait Statistics with sys.query_store_wait_stats - Grant Fritchey explains how sys.query_store_wait_stats lets you see wait statistics along with our runtime statistics and the query plans inside of Query Store....(more)

A Walk Around SQL Operations Studio - Microsoft has just released SQLOPS – SQL Operations Studio – in preview mode. Built on top of VS Code, it is designed to manage your SQL Server from multiple platforms. SQLOPS runs on Windows, macOS, and Linux. Best of all, it is absolutely free to use....(more)

.NET Related Articles

Adapting Images to Websites - Creating web pages with images that look good on all devices and screen sizes is not an easy task. Dino Esposito describes how to manually prepare images and explains the currently available technology for automatic resizing....(more)

To Learn, Teach - Watching someone present on a deeply technical topic or reading an article about a complex concept always made Tim Mitchell wonder what chain of experiences would give the writer or author that type of encyclopedic knowledge about a topic....(more)