7 Reasons Why Migrating Off SQL 2005 is a Good Idea

  • Article

While end of support for SQL Server 2005 SP4 came on April 12th 2011, end of extended support for SQL Server 2005 happens on 12 April 2016. That means no help and no more hotfixes from Microsoft in regards to any data still living in SQL 2005 databases. Further details in regards to this can be viewed below: 

The hope is that your organization has already or is already upgrading and migrating data & applications to newer versions of SQL Server.  No matter which version you will be migrating to, some customers with early access are already using SQL Server 2016, the following is a list of features and perks you’ll have available to you now that you are moving away from SQL 2005. While there have been hundreds of enhancements made available since 2005, this post will focus on reasons that a data architect would want to use in a database design for enhanced performance, security and data quality.

The 7 reasons include:

  1. Columnstore Indexes - This is the closest thing made available as a "turbo switch" for SQL Server when designing for data warehouse type solutions. Columnstore Indexes, introduced in SQL Server 2012, achieve high compression rates since they store columns together instead of storing rows together. They also support much faster query performance for batch and aggregation queries. They typically achieve 10x performance increases, sometimes even more. 
       
  2. SEQUENCEs – Introduced in SQL Server 2012, these special objects work much like IDENTITY columns, but offer use cases and additional flexibility.  The main advantage is the ability to grab a sequence, or many sequences, before inserting a row.  Many developers use GUIDs for similar reasons, but GUIDs are much longer and therefore had performance downsides. SEQUENCEs are integer types.
     
  3. New Data Types - Many new data types have been introduced since SQL Server 2005, but the ones of importance to data architects are DATE, TIME, DATETIMEOFFSET, the geospatial types, and the deprecation of timestamp. SQL Server 2008 introduced access to data types comprised of only the DATE or TIME portion of a point in time. All kinds of conversions were required to strip out unwanted data (00:00:00). Room also had to be made to store unwanted precision. Storing millions of rows of unneeded zeros hurts performance, both operationally and for backup and recovery. SQL Server 2008 also introduced DATETIMEOFFSET, which allowing the ability to track data in context of its time zone.
     
    The spatial data types GEOGRAPHY and GEOMETRY added a new feature-rich way of tracking places.  Their geometry plus special features that make it much easier to answer questions like "which is the closest" or "is this address located in this neighborhood".
     
    SQL Server was always an oddball when it came to the data type TIMESTAMP. In other DBMSs, this data type was one that included date and time, to a very large precision. In SQL Server, TIMESTAMP is a type of row version identifier that has nothing to do with TIME. Data architects migrating from other DBMSs were often bitten when they used the wrong data type. Microsoft announced in 2008 that it was depreciating TIMESTAMP and recommending the use of ROWVERSION, which is similar, yet not the same, in regards to functionality.
     
  4. Encryption - Always Encrypted, currently supported in SQL Server 2016, supports the encryption of data from application to database and back. This enables a better encryption solution than solutions that encrypt data once it is written to the database thus protecting the data while in transit and are most vulnerable to attacks.
     

     
  5. Data Archiving - As data quickly accumulates, the size of said databases also increase in size and in turn performance takes a hit. Developers want IT professionals to take shortcuts on data quality to improve performance as size matters. One of the ways to help manage data volumes is to move cold or unused data to other storage locations. Database Architects can stretch a database to Azure in SQL Server 2016 thus enabling data that isn’t accessed as often to be stored in the cloud and retrieved when needed. Thus allowing frequently used or hot data to be local, cold data is economically stored and both are still seamlessly accessible by the application that requires the data.
     
  6. JSON Support – While not the same as a JSON data type found in XML, JSON processing support that is being made available in SQL Server 2016 adds a set of import and export features to relational data as JSON documents and in turn add JSON data into SQL Server. 
     
  7. 3rd Party Vendor Support - Third party tool makers also stop supporting products that the main vendors retire. Supporting older, out of support versions of databases effects data modeling and data quality. Data integration tools that address these issues on now retired databased solutions are also dropped. Don’t be left supporting database systems without vendor support and without professional enterprise class modeling and design tools.