Denali is coming, whether you like it or not. You may not be an early adopter and you may not have plans on your current calendar, but at some point you will need to move your apps and databases to this release – or one very much like it. There are a lot of great new features you will be able to take advantage of, but not everything is a double rainbow. There are some changes that will break your spirit if you let them. What does it mean?
I go over several breaking changes in my presentation that are well documented and have been announced for a long time, and what you can do to work around them. For example:
Those are the easy ones. There are some other changes that will break your code, but they may never show up on some discontinued features document or appear in a trace for deprecated events. Let me illustrate with an example that I came across recently.
If DBCC LOGINFO changes, you shouldn't expect it to show up on any document or in a trace, because it is, after all, undocumented. But like many other undocumented commands, you will find its use quite prevalent in systems out there — so even a slight change could have a substantial impact. It turns out that the output of DBCC LOGINFO has changed in Denali, and I'll show how this can break your existing code.
Let's say you are creating a #temp table to store DBCC LOGINFO results (maybe because you are looping through and collecting data for multiple databases). Perhaps you are using code like Leonardo Pasta's to automate VLF management. Your code would look something like this:
<pre style="padding: 10px 20px; background: none repeat scroll 0% 0% transparent; font-size: 12px; font-family: consolas,lucida console,courier new,courier; -moz-background-inline-policy: continuous;">CREATE TABLE #LI ( [FileId] INT, [FileSize] BIGINT, [StartOffset] BIGINT, [FSeqNo] INT, [Status] INT, [Parity] INT, [CreateLSN] DECIMAL(25, 0) ); INSERT #LI EXEC('DBCC LOGINFO(''msdb'');');
This code will work fine in SQL Server 2008 R2 and lower, but if you run this on Denali, you will receive this error:
Msg 213, Level 16, State 7, Line 1
Column name or number of supplied values does not match table definition.
This is because there is a new leading column in the DBCC output in Denali, RecoveryUnitID (INT). So you'll need to change your #temp table declaration as follows:
<div style="padding: 10px 20px; background: none repeat scroll 0% 0% transparent; font-size: 12px; font-family: consolas,lucida console,courier new,courier; -moz-background-inline-policy: continuous;">CREATE TABLE #LI ( <b>[RecoveryUnitId] INT</b>, [FileId] INT, [FileSize] BIGINT, [StartOffset] BIGINT, [FSeqNo] INT, [Status] INT, [Parity] INT, [CreateLSN] DECIMAL(25, 0) ); </div></td></tr></table>
Once that leading column is in place, the insert will work fine. What this means is that if you have code that needs to run against multiple versions of SQL Server, or at least needs to maintain compatibility with older versions, you'll need to build the #temp table conditionally. I'd suggest using something other than an undocumented command for this, but it seems that DBCC LOGINFO remains your only choice, as it is clear that Microsoft isn't yet interested in adding any features that will ease log management (see Connect item #322149 and vote if you agree).
Do your homework
If you think DBCC LOGINFO is the only breaking change that you might encounter, think again. You don't need to look much further than the changes to the memory manager in Denali to know that there are some other issues coming your way (for example, many of the memory-related DMVs have changed column names and different data behind those columns, and DBCC MEMORYSTATUS output has been revised as well).
As open as the CTP and beta program have been, and as much as folks like myself have been trying to push this information as early on as possible, if you install Denali after it is released and are surprised by breaking changes, you have nobody to blame but yourself.
Here is a relatively complete list of deprecated items, albeit currently out of date (it has a mix of info from CTP1 documentation, and the 2008 R2 doc it originated from):
You'll want to watch that page when it is updated for the next CTP. And if you're not already playing with Denali, please at least consider grabbing that CTP when it becomes available.
I will continue to post examples that I come across where code may break simply by upgrading to Denali. You can never be too prepared…