SQLServerCentral - www.sqlservercentral.com

A community of more than 1,600,000 database professionals and growing

Featured Contents

The Voice of the DBA

No More Service Packs

The new servicing model for SQL Server is out, with a great explanation from Glenn Berry (there's also a webcast). The summary is that going forward with SQL Server 2017, we won't get Service Packs anymore. I'm somewhat sad, as I was always looking for SPs as a once a year patch if I didn't have any major issues with the platform. This allowed me to keep up to date, but not constantly test smaller patches. I liked that, partly because I've been worried about the quality of CUs in the past. 

We've had Cumulative Updates (CU) for quite some time. While I've seen a few service packs pulled and re-released, that's been very rare with CUs. I do find that SPs might have more issues because building one is an out of process, disruptive project, whereas CUs have become a regular, repeatable part of the development process. That's not to say that the CUs are bug free. A few of them have caused issues, and that's a problem that I hope improves over time. However, I also can't imagine what it's like working on such a large codebase, for installation in a large number of environments. It has to be a tremendous challenge to test all cases and remove bugs. Not that there shouldn't be fewer issues, especially in some areas, but I'm not sure there will ever be a complete absence of bugs.

I've changed my mind on CUs, and I like the new servicing model for it's simplicity as well as the automation and deep testing that CUs get. There is no confusion, no resources at Microsoft maintaining an RTM and SP1 branch with patches. I dislike code merges, and I suspect they're often a source of unintended bugs, so the fewer that are done the better. Now we'll have SQL Server 2017 essentially with one branch of code and patches. We'll have CU1-11 in the first year and then CU 12-15 the next year. Across fix years, I'd expect that we'd go from roughly 16 CUs per SP + SP to potentially 28 CUs in a single stream across the five year mainstream support of the product.

I know there are potentially more issues discovered early in a version's life, and hopefully fewer over time as developers write more tests and learn to cover edge cases better. I suspect that we'll see fewer and fewer items in each patch over time. Maybe not right away in SQL 2017, but it seems as though the number of issues corrected in each CU declines over time, and I'd expect that testing and better coverage of feature corner cases will come in the future. Perhaps SQL 2019 or 2020 will have fewer issues in the first year with even more testing.

I know some people see this as a message that the product isn't well tested. That's a fair view, after all, doubling the number of patches in the first year could be taken either way. I prefer to view this as the new way software is being developed. Build a pipeline, including lots of testing and feedback, and be ready to respond quickly if things are broken. I hope that's what Microsoft is thinking, and based on their push for better security and quality, it's the view I'll adopt.

Steve Jones from SQLServerCentral.com

Join the debate, and respond to today's editorial on the forums

The Voice of the DBA Podcast

Listen to the MP3 Audio ( 5.5MB) podcast or subscribe to the feed at iTunes and Libsyn. feed

The Voice of the DBA podcast features music by Everyday Jones. No relation, but I stumbled on to them and really like the music.

SQL Source Control

How to track every change to your SQL Server database

See who’s changing your database, alongside affected objects, date, time, and reason for the change with SQL Source Control. Get a full change history in your source control system. Learn more


Using SQL Census to audit SQL Server permissions

Redgate have just released SQL Census, a prototype tool that makes auditing SQL Server user access permissions much easier. In this post, Ally Parker shows how it works, tells us what's up next in the tool's development, and explains how you can download it for free. Try the free prototype

Featured Contents


Azure DWH part 17: ADF:Import Data from SQL Server on-premises to ASDW

Daniel Calbimonte from SQLServerCentral.com

In this article we will learn to use Data Factory to import table from SQL Server to Azure SQL Data Warehouse. More »


Data in Motion and Data at Rest

Additional Articles from SimpleTalk

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 »


So, what is GDPR and why should Database Administrators care?

You’ve no doubt heard at least something about the GDPR, the EU’s new privacy and Data Management law with its greatly increased maximum fines for non-compliance and tighter definitions for acceptable use of personal information. If you’ve continued reading past paragraph one of any of the many articles, you’ll be aware that the law applies globally, to all organizations holding EU citizens’ data – it’s not bounded by geography or jurisdiction. More »


From the SQLServerCentral Blogs - Power BI Custom Visuals Class (Module 70 – Custom Calendar By Akvelon)

Devin Knight from SQLServerCentral Blogs

In this module you will learn how to use the Custom Calendar by Akvelon.  The Custom Calendar by Akvelon is... More »


From the SQLServerCentral Blogs - Box-and-whisker plot and data patterns with R and T-SQL

Mala Mahadevan from SQLServerCentral Blogs

R is particularly good with drawing graphs with data. Some graphs are familiar to most DBAs as it has been... More »

Question of the Day

Today's Question (by Steve Jones):

What does this code do?

DECLARE @d1 DATETIME = '20170101 09:42'
DECLARE @d2 DATETIME = '20170301 22:22'


Think you know the answer? Click here, and find out if you are right.

We keep track of your score to give you bragging rights against your peers.
This question is worth 1 point in this category: Datetime Manipulation.

We'd love to give you credit for your own question and answer.
To submit a QOTD, simply log in to the Contribution Center.


Expert T-SQL Window Functions in SQL Server

Expert T-SQL Window Functions in SQL Server takes you from any level of knowledge of windowing functions and turns you into an expert who can use these powerful functions to solve many T-SQL queries. Replace slow cursors and self-joins with queries that are easy to write and fantastically better performing, all through the magic of window functions. Get your copy from Amazon today.

Yesterday's Question of the Day

Yesterday's Question (by Steve Jones):

I run this code:

USE [master]

I get this error:

Msg 12824, Level 16, State 1, Line 3
The sp_configure value 'contained database authentication' must be set to 1 in order to alter a contained database.  You may need to use RECONFIGURE to set the value_in_use.

What is wrong?

Answer: The instance needs the setting "Contained database authentication" turned on


There is an instance level setting that allows contained database authentication. This needs to be run:

EXEC sys.sp_configure N'contained database authentication', N'1'

Once this is done, the database can be set to contained.

Ref: Migrate to Partially Contained Database - click here

» Discuss this question and answer on the forums

Database Pros Who Need Your Help

Here's a few of the new posts today on the forums. To see more, visit the forums.

SQL Server 2016 : SQL Server 2016 - Administration

SQL Compare Red Gate - Hello, Does SQL Compare by Red-Gate  can show me if the syntax of Stored Procedure at one instance is different from...

sys.dm_os_tasks - Hello everyone, some of the tasks from this dmv are appearing with session_id null which are connected with workers. Can someone...

sa password fault - Possibly OK after restart - All, Setup is as follows; Two Server 2012 Datacenter servers Availability group running on SQL 2016 Standard. The first server started to refuse the...

ToolBox missing for Maintenance task - This morning I logged into SSMS SQL2016 to edit a maintenance plan. I would add a cleanup task. But I noticed...

SQL Server 2016 : SQL Server 2016 - Development and T-SQL

Running balance calculation - Hi all, I'm interested in creating a query that would calculate the balance of customers with an account open on the...

Current month to yesterday's date - Hi Guys, I am using the following code to pull back data based on the start of the month and yesterdays...

Strange COALESCE issue - All, I think it's correct to provide a table structure for any questions so I'll include that first: /****** Object: Table...

Object Dependency - Hi All, I am using SQL below to get the object dependency. select Obj.name,dep.referenced_entity_name from sys.sql_modules as Module,sys.objects AS Obj ,sys.sql_expression_dependencies as...

SQL Server 2014 : Administration - SQL Server 2014

Restore MySQL dump? help - How to get mysql to comply with all instructions from a file? Ransacked the entire forum, tried everything I could...

SQL Server 2014 : Development - SQL Server 2014

select from flat file - How do select from a flat file? SELECT Field1 ,Field2 ,Field3   FROM OPENROWSET(BULK 'C:\Test.txt' , FORMATFILE='C:\Test.txt') AS Test WHERE Field1 <> 'N/A' This...

How can this query be rewitten? - Hi Experts, I have written a query based on our requirement using co-related sub-query. Checking if this can re-written using...

Since looping is bad, how should I accomplish what I am trying to do? -

SQL Server 2012 : SQL Server 2012 - T-SQL

Compare 2 rows using tsql code - Hi All, A questions which is there with me a long long time. Comparing 2 rows in a table. How...

data insering from staging table to final table how to avoid duplicates? please - I have two tables Table1(has 10 columns), Table2(has 22 columns). every time I insert data from table1(fresh load everyday kidn of...

SQL Server vNext : SQL Server 14 - Administration

SharePoint permissions for DBA - Does anyone have any guidelines or best practices regarding SharePoint admins access level to the SQL Server? I looking at...

SQL Server 2008 : SQL Server 2008 High Availability

will cluster failover if the nodes are on different service pack levels? - Hi, Since I don't have a testing environment, I am seeking help on this. In a 2-node SQL Cluster, if Node-1 is...

Reporting Services : Reporting Services

Add 3 blank rows/boxes based on first letter of last name - Hello, I'm new to this forum and it seems awesome, so here's my first post/topic to test the waters. ;-) I’m working...

Reporting Services : SSRS 2012

Filter a Matrix - Is it possible to filter a matrix so that if a value is over a certain amount the whole row...

Data Warehousing : Integration Services

Data Flow Task Error: The version of ODBC Source is not compatible with this version of the DataFlow - So, that is the error I am getting when I attempt to execute my extremely simple SSIS package.  How simple?...

SQL Server 2005 : T-SQL (SS2K5)

Inserting rows into remote server with identity column - I am having troubles trying to copy some rows from a table on my local computer to a table on...

This email has been sent to {user_email}. To be removed from this list, please click here.
If you have any problems leaving the list, please contact the webmaster@sqlservercentral.com.
This newsletter was sent to you because you signed up at SQLServerCentral.com.
Feel free to forward this to any colleagues that you think might be interested.
If you have received this email from a colleague, you can register to receive it here.
This transmission is ©2017 Redgate Software Ltd, Newnham House, Cambridge Business Park, Cambridge, CB4 0WZ, United Kingdom. All rights reserved.
Contact: webmaster@sqlservercentral.com