Press "Enter" to skip to content

Category: Versions

Upgrade To 2016?

Published 2016-11-18 by Kevin Feasel

Kendra Little explains why upgrading to 2016 is a good idea:

I know most of y’all care about Standard Edition.

With SP1, you can use all sorts of (formerly) super-expensive features like data compression, partitioning, Columnstore, Change Data Capture, Polybase, and more in Standard Edition.

A few features have scalability caps in “lower” editions. There’s a memory limit for In-Memory OLTP (aka Hekaton), and Columnstore. Columnstore also has restrictions on parallelism.

I’ll also throw out my experience as an anecdote:  2016 has been more stable than 2014 in our environment.  I don’t regret going to 2014, but we’re better off on 2016.

Comments closed

New Features In 2016 SP1

Published 2016-11-17 by Kevin Feasel

Niko Neugebauer looks at new functionality released as part of SQL Server 2016 SP1:

– CREATE OR ALTER. I almost cried when I found out that it was implemented. I was asking, begging, threatening, crying for years to get this in the SQL Server. Now, I can finally have future project deployments of those who are not using SSDT running with much less problems.
Now we can modify and deploy objects like Stored Procedures, Triggers, User-Defined Functions, and Views without any fear. Just “Make it so!”

Yeah, they’re not as big as “every Enterprise Edition development feature over the past decade is now available to anybody” but there are some nice additions here.

Comments closed

SQL Server 2016 SP1

Published 2016-11-16 by Kevin Feasel

Parikshit Savjani notes that SQL Server 2016 SP1 is available:

The following table compares the list of features which were only available in Enterprise edition which are now enabled in Standard, Web, Express, and LocalDB editions with SQL Server 2016 SP1. This consistent programmatically surface area allows developers and ISVs to develop and build applications leveraging the following features which can be deployed against any edition of SQL Server installed in the customer environment. The scale and high availability limits do not change, and remain as–is for lower editions as documented in this MSDN article.

This is huge.  With SQL Server 2016 SP1, you can get data compression, In-Memory OLTP, partitioning, database snapshots, Polybase, Always Encrypted, and a lot more in Standard edition.  If you’re on Standard Edition today, this is a must-upgrade—some of these have been Enterprise-only features for nearly a decade and they were a huge part of the appeal for paying for Enterprise.  My question is, what are they going to announce to make people want to keep buying Enterprise Edition?

Comments closed

Upgrading SSMS

Published 2016-10-24 by Kevin Feasel

Melissa Coates argues that you should upgrade to the latest version of Management Studio:

SSMS is supported for managing SQL Server 2008 through 2016 (except for SSIS instances which sadly still require a version-specific SSMS at the time of this writing). If you manage numerous servers on different versions, this unification is fantastic. There is partial support for managing pre-2008 instances. And, of course as you’d expect, the newest SSMS release supports various new features in SQL Server 2016 such as Query Statistics, Live Query Plans, Compare Showplan, Security Policies for row-level security, and so on with all the new 2016 goodies we have.

SSMS also supports managing Azure components such as Azure SQL Database and Azure SQL Data Warehouse, as well as hybrid cloud features such as StretchDB and Backup to URL. This additional unification is really, really handy.

I have a copy of SSMS 16 for reading Query Store, but not all of my plugins have been updated yet, so I’m still living in SSMS 2014 for now.

Comments closed

Attaching Databases With CDC Enabled

Published 2016-10-12 by Kevin Feasel

Amit Banerjee notes an issue with detaching a database with Change Data Capture enabled and moving it to a new version of SQL Server:

When you detach a database with Change Data Capture enabled on SQL Server 2014 and below and attach it to a SQL Server 2016 instance, you could run into the error mentioned below while execute Change Data Capture (CDC) related procedures.

Error:

Msg 22832, Level 16, State 1, Procedure sp_cdc_enable_table_internal, Line 639 [Batch Start Line 0]

Could not update the metadata that indicates table [<schema name>].[<object name>] is enabled for Change Data Capture. The failure occurred when executing the command ‘insert into [cdc].[captured_columns]’. The error returned was 213: ‘Column name or number of supplied values does not match table definition.’. Use the action and error to determine the cause of the failure and resubmit the request.

Read on to learn more, including how to fix the issue.

Comments closed

Datetime Conversion Change

Published 2016-10-03 by Kevin Feasel

Dan Guzman notes a change in behavior in how datetime fields are upconverted:

SQL Server 2016 and Azure SQL Database V12 use the raw datetime internal value without rounding during conversion to another temporal type. The value is rounded only once during conversion, to the target type precision. The end result will be the same as before SQL Server 2016 when the target type precision is 3 or less. However, the converted value will be different when the target type precision is greater than 3 and the internal time unit interval is not evenly divisible by 3 (i.e. rounded source datetime millisecond value is 3 or 7). Note the non-zero microseconds and nanoseconds in the script results below and that rounding is based on the target type precision rather than the source.

This is a good thing on net, but be aware of this if you try to compare datetime versus datetime2 values.

Comments closed

SQL Server 2016 CU2 And Spinlocks

Published 2016-09-30 by Kevin Feasel

Arvind Shyamsundar has a nice post on SQL Server 2016’s Cumulative Update 2 and how it reduces spinlock contention:

The developers then took a long hard look at how to make this more efficient on such large systems. As described before, since operations on the cache are read-intensive, there was a thought to leverage reader-writer primitives to optimize locking. However, any changes to this spinlock had to be validated extensively before releasing publicly as they may have a drastic impact if incorrectly implemented.

The implementation of the reader / writer version of this spinlock was an intricate effort and was done carefully to ensure that we do not accidentally affect any other functionality. We are glad to say that the final outcome, of what started as a late night investigation in the SQLCAT lab, has finally landed as an improvement which you can use! If you download and install Cumulative Update 2 for SQL Server 2016 RTM, you will observe two new spinlocks in the sys.dm_os_spinlock_stats view:

  • LOCK_RW_CMED_HASH_SET
  • LOCK_RW_SECURITY_CACHE

These are improved reader/writer versions of the original spinlocks. For example, LOCK_RW_CMED_HASH_SET is basically the replacement for CMED_HASH_SET, the spinlock which was the bottleneck in the above case.

Click through for the full story.

Comments closed

Finding Cumulative Updates

Published 2016-09-14 by Kevin Feasel

Jonathan Kehayias shows where to find older cumulative updates:

However, to my dismay that link took me to the download link for SQL Server 2014 SP1 CU8 and not to CU7 as I was expecting. So I tried other SP1 CU links and they all redirect to the CU8 download page. Slightly irritated I decided to send a Skype IM to Glenn (@GlennAlanBerry|Blog) what was going on because he keeps up with every update Microsoft publishes for SQL Server. Glenn told me that Microsoft’s recommendation and preference is for people to install the latest CU so the KB articles now link to the latest CU download page. That doesn’t really help me with adding a failed FCI node back into a FCI that is on CU7, so Glenn offered to share the CU7 file with me by Dropbox.

It turns out that I didn’t need Glenn to share the file with me, I needed to read the information on the KB article closer and pay attention.

Read on to find out where you can find the CU archives.

Comments closed

Hortonworks HDP 2.5 Available

Published 2016-08-31 by Kevin Feasel

Hortonworks has a new version of their data platform, 2.5:

We are very pleased to announce that the Hortonworks Data Platform (HDP) Version 2.5 is now generally available for download. As part of a Open and Connected Data Platforms offering from Hortonworks, HDP 2.5 brings a variety of enhancements across all elements of the platform spanning data science, data access to security to governance.

At Hadoop Summit 2016 San Jose on 06/28/2016, we unveiled the latest innovation package within Hortonworks Data Platform 2.5.

The top points of interest:  Spark 2, Kafka 0.10.0, Ambari 2.4, and Storm 1.0.1.  These are four big projects with major improvements.  Looks like I’ve got something to do this weekend…

Comments closed

Powershell Step Type Bug

Published 2016-07-29 by Kevin Feasel

Derik Hammer notes that there is a bug in 2016 with SQL Agent jobs which have Powershell step types:

When executed through the SQL Agent, the SQLPS.exe mini-shell is called and the current working directory is switched to the SQLSERVER:\ provider. When you call a cmdlet that uses the FILESYSTEM provider under the context of the SQLSERVER provider the cmdlet will fail.

Derik has a Connect ticket open for this bug as well.

Comments closed