Sql-server – Keeping data in old Database (SQL 2008) identical to new Database (SQL 2016)

Everybody's excited about AlwaysOn and ColumnStore, but many of the benefits of SQL Server 2012 are not exclusive to high-end editions. I don't want to sound like a spokesperson, but I've given plenty of presentations on SQL Server 2012 and I think it has a lot to offer at whatever edition suits you.

• Partially Contained Databases which allow you to move databases between servers or environments with a few less shackles (namely server-level logins and server collation dependencies - future versions will handle thornier items like linked servers and Agent jobs).

• Management Studio is now a much better tool, aligned with Visual Studio. IntelliSense is better and a whole bunch of other features make editing easier. Now of course you can have 2008 R2 on your server and use the 2012 version of SSMS, but I'm not sure how that works licensing-wise, and some shops don't want mixed versions (I prefer having the most recent tools on my workstation even to manage downlevel servers). I blogged about the changes early on, when there were still bugs, so please ignore the negatives since most or all are fixed as of RTM. I shudder now when I have to use an earlier version of SSMS.

• Metadata enhancements allow you to inspect resultsets of objects and ad hoc queries, and also allow you to better shape the output of queries.

• Custom Server Roles allow you to define a much more granular set of permissions for users at a role level instead of granting/revoking one by one, or just giving in to complexity and giving them sysadmin.

• FileTable lets you manage a folder like a table of documents, but still have external control over the contents (so imagine being able to do this with T-SQL, and imagine how hard it would be to do in cmd or PowerShell: UPDATE C:\Docs\*.* SET ReadOnly = 1 WHERE Author = 'Bob' AND Created < '20100101';)... think FileStream meets WinFS and gets some usability to boot.

• T-SQL Enhancements allow you to do many things that were a pain in previous versions:

  • THROW (think of it as re-raise)
  • OFFSET / FETCH (simpler, ANSI-standard paging - also see this post)
  • SEQUENCE (centralized IDENTITY mechanism, like in Oracle)
  • Windowing/Framing enhancements (various things here, such as awesome running totals performance)
  • IIF() / CHOOSE() / CONCAT() / EOMONTH()
  • Date/Time Constructors (e.g. DATEFROMPARTS) similar to DateSerial in VB
  • PARSE() / FORMAT() - like their .NET counterparts (but see this post about the latter)
  • TRY_CONVERT() / TRY_PARSE() - return NULL if CONVERT / PARSE fail

• Extended Events has an enhanced UI for configuration / viewing, and finally completely covers trace / audit functionality (including much better causality tracking).

• Lots of new DMVs, system procedures and ShowPlan enhancements for diagnostics and performance troubleshooting. Also take a look at what CSS is calling "The Black Box Recorder."

• Server Core allows you to run on a bare-minimum server without all the UI components (smaller surface area means it is more secure, and reduced maintenance since less parts of OS are subject to Windows Update).

• Full-Text Search gets some important underlying performance enhancements, as well as semantic search (think keywords) and customizable proximity / NEAR.

• AWE is no longer supported, meaning your SQL Server instance on x86 with 32GB of RAM is only going to be able to use 4GB - so you will finally have motivation to get off your old 32-bit hardware.

Microsoft has recently revealed (without a lot of fanfare) that they will be investing in TLS 1.2 and phasing out SSL. It should be relevant to all editions of SQL Server.

UPDATE 2016-01-29 : Microsoft has announced official support for TLS 1.2 in 2008, 2008 R2, 2012, & 2014. Downloads and other info can be found in KB #3135244.

I blogged about a few of the issues that have been mentioned, as well as a warning if you are using encrypted endpoints in 2014:

• SQL Server support for TLS 1.2 – Read This First!

The post also points to the correct build to download (or other action) depending on @@version.

Whether this move will affect all existing versions, just 2014 and above, or just 2016, remains to be seen. The quote below seems to imply at least 2014 will be part of the work - and I suspect much of the investment will be in the client libraries, not in the engine, so it is feasible that it will work for any version that the next release of the ODBC/Native Client drivers will support.

I got this from a PowerPoint deck by Kevin Farlee of Microsoft, and was given permission to share the information, though I don't know how much of it has been redistributed at this point. Here is the exact quote from the deck:

Encryption in flight: Protects data between client and server against snooping & man-in-the-middle attacks. Upgrading to TLS 1.2 in CY 15, phasing out SSL.

Also if you look at KB #3052404, it seems there are patches to make it work with 2012 SP+ and 2014 (patches won't be required for 2016), but no indication there will be any back-porting to SQL Server 2005, 2008, or 2008 R2 (and frankly, I'd be quite surprised).