Microsoft SQL Server 2005 EOL and Upgrade Proposal

The estimated reading time for this post is 8 minutes

sql_s_2005_support

Microsoft SQL Server has rapidly become the database of choice for a large number of prepackaged and custom-build business applications due to its robust feature set, ease of use, and highly competitive pricing. In some cases, these applications are introduced to small groups within a company, and are not given the infrastructure support that is provided to larger projects. These applications that are initially small and non-business-critical can gradually grow into business-critical resources that require proper support. In other cases, the applications may be unknown outside the user community until there is a problem. At that time, the business must try to resolve an issue in an application that the IT department did not know about.

SQL Server 2005 End of Life

Microsoft SQL Server 2005 end of support is at the corner, Microsoft refuses any form of support for SQL Server 2005 from 12th April 2016 onward and end of support for SQL Server 2005 means no more security patches for the product. Microsoft is making the most of the remaining year to push hard for organizations to upgrade. Rather than bang on security alone as a motive, Microsoft devoted more space in the article to touting the performance improvements and high-availability features found in more recent versions of SQL Server. For those planning to upgrade SQL Server on-premises, Microsoft claims SQL Server 2005 can be upgraded to SQL Server 2014 — provided the former isn’t a 32-bit edition of the product.

What is new in SQL Server 2014?

Microsoft SQL Server 2014 has major improvements in each component of database engine, analysis services, and integration services and reporting services, there are few additional tools and components are added such as master data services and data quality services in the Microsoft BI stack. (Refer to Appendix A)

Importance of SQL Server Enterprise Edition Internal Features

Microsoft SQL Server Enterprise edition provides few additional internal features that provide better performance, manageability, hardware resource usage and recovery. What matters to your IT department? As mentioned features all comes together to reduce the TCO and increase the ROI of your company. (Refer to Appendix B)

Microsoft SQL Server 2014 on Production, UAT and Development Servers with Minimal TCO

SQL Server 2014 has three main editions that are closely aligned with how customers use SQL Server across workloads. SQL Server 2014 delivers licensing that enables you to more easily take advantage of the benefits of public and private cloud through virtualization and license mobility. Our new model provides simplified licensing, flexibility and innovation, and industry-leading total cost of ownership (TCO).

Microsoft allows customers to deploy SQL Server 2014 Developer edition on DEV and UAT environments with minimal total cost of ownership (TCO).

Please contact your account manager in Microsoft to know more about SQL Server 2014 licensing.

Appendix A

What’s New in SQL Server 2014 since SQL Server 2005?

PERFORMANCE & SCALE

  • In-Memory OLTP
  • Enhanced In-Memory ColumnStore for DW
  • Support for 640 logical proc. & 4 TB memory
  • Support to 15,000 partitions
  • Resource Governor IO governance
  • Buffer Pool Extension to SSDs
  • Query optimization enhancements
  • SysPrep at cluster level
  • Predictable performance with tiering of compute, network, and storage with Windows Server 2012 R2
  • Data Compression with USC-2 Unicode support
  • Backup Compression

HIGH AVAILABILITY

  • SQL Server AlwaysOn
  • Database Mirroring
  • Failover Clustering
  • Database Snapshots
  • Delayed Durability
  • Recovery Advisor
  • Windows Server Core
  • Live Migration
  • Online Operations
  • Clustered Shared Volume support, VHDX support (Windows Server 2012 R2)
  • Manage on-premises and cloud apps (System Center 2012 R2)

SECURITY

  • User-Defined Server Roles
  • Default Schema for Groups
  • SQL Server Audit
  • Transparent Data Encryption
  • Extensible Key Management
  • Standards-based Encryption
  • SQL Server Fine-grained Auditing
  • Enhanced separation of duty
  • CC certification at High Assurance Level
  • Backup encryption support

PROGRAMMABILITY

  • SQL Server Data Tools
  • Local DB runtime (Express)
  • Data-tier Application Component project template
  • Data-Tier Application Framework (DAC Fx)
  • Query optimization enhancements
  • Interoperability support (ADO.NET, ODBC, JDBC, PDO, ADO APIs and .NET C/C++, Java, Linux, and PHP platforms)

T-SQL ENHANCEMENTS

  • Enhanced support for ANSI SQL standards
  • Transact-SQL Static Code Analysis tools
  • Transact-SQL code snippets
  • Intellisense

DATA SUPPORT

  • FILESTREAM data type
  • FileTable built on FILESTREAM
  • Remote Blob Storage with SharePoint 2010
  • Spatial data support
  • Full Text Search for unstructured files
  • Statistical Semantic Search
  • Large user-defined data types

PROGRAMMABILITY SUPPORT

  • Support for LINQ and ADO.NET Entity Framework
  • CLR Integration and ADO.NET Object Services

MANAGEABILITY

  • Distributed Replay
  • Contained Database Authentication
  • System Center Management Pack for SQL Server 2012
  • Windows PowerShell 2.0 support
  • Multi-server Management with SQL Server Utility Control Point
  • Data-Tier Application Component
  • Multi-server Management with SQL Server Utility Control Point
  • Data-Tier Application Component
  • Policy-Based Management
  • SQL Server Performance Data Collector
  • Query enhancements
  • SMTP mail for secure DB email w/o Outlook

ACCESS ANY DATA

  • Power Query
  • Windows Azure HDInsight Service
  • Analytics Platform System (PDW V2)
  • Mash up data from different sources, such as Oracle & Hadoop

INSIGHTS WITH FAMILIAR TOOLS

  • Power BI in Office 365
  • Power Map for Excel
  • Mobile interfaces for Power BI

COMPLETE BI SOLUTION

  • SQL Server BI Edition
  • StreamInsight
  • BI Semantic Model
  • SQL Server Data Tools
  • BI Development Studio
  • Microsoft Visual Studio-based report dev tools
  • Change Data Capture for Oracle

ANALYSIS SERVICES

  • Import PowerPivot models into Analysis Services
  • Enhancements on productivity, performance
  • Cube design tools, block computations, and write-back to MOLAP

REPORTING SERVICES

  • Power View
  • Configurable reporting alerts
  • Reporting as SharePoint Shared Service
  • Report Builder 3.0
  • Report Designer
  • Report Manager

DATA QUALITY SERVICES

  • Build organizational knowledge base
  • Connect to 3rd party data cleansing providers

MASTER DATA SERVICES

  • Master Data Hub
  • Master Data Services Add-in for Microsoft Excel

INTEGRATION SERVICES

  • Graphical tools in SSIS
  • Extensible object model
  • SSIS as a Server
  • Broader data integration with more sources; DB vendors, cloud, Hadoop
  • Pipeline improvements
  • Persistent lookups
  • High-performance connectors
  • Data profiling tool

HYBRID CLOUD SOLUTIONS

  • Simplified backup to Windows Azure
  • Support for backup of previous versions of SQL Server to Windows Azure
  • Cloud back-up encryption support
  • Simplified cloud Disaster Recovery with AlwaysOn replicas in Windows Azure VMs

EASY ON-RAMP TO THE CLOUD

  • New Windows Azure Deployment UI for SQL Server
  • Larger SQL Server VMs and memory sizes now available in Windows Azure
  • DAC enhancements: Import/export with Windows Azure SQL Database

COMPLETE AND CONSISTENT FROM ON-PREM TO CLOUD

  • SQL Server Data Tools
  • License Mobility (with SA)
  • Resource Governor Enhancement
  • Snapshot backups to Windows Azure via SQL Server Management Studio

Appendix B

1- Advance Scan (Merry Go Round Algorithm): This feature allows multiple tasks to share full table scans. If the execution plan of a Transact-SQL statement requires a scan of the data pages in a table and the Database Engine detects that the table is already being scanned for another execution plan, the Database Engine joins the second scan to the first, at the current location of the second scan. The Database Engine reads each page one time and passes the rows from each page to both execution plans. This continues until the end of the table is reached. Without advanced scanning, each user would have to compete for buffer space and cause disk arm contention. The same pages would then be read once for each user, instead of read one time and shared by multiple users, slowing down performance and taxing resources.

2- Fast Recovery: Fast Recovery allows a database to come online as soon as the REDO phase completes, before UNDO is run. This is made possible by lock logging, which records the locks that were applied by a transaction in the associated log record.

3- Prefetching: The leaf rows of a non-clustered index contain pointers to the data rows that contain each specific key value. As the storage engine reads through the leaf pages of the non-clustered index, it also starts scheduling asynchronous reads for the data rows whose pointers have already been retrieved.

4- Single Scatter Scan: The SQL Server storage engine scans the intermediate index page and builds a list of the leaf pages that must be read. The storage engine then schedules all the reads in key order. It also recognizes that for instance pages 308/465 and 488/489 are contiguous and performs a single scatter read to retrieve the adjacent pages in a single operation.

5- Automatic Matching: The Query Optimizer automatically matches the computed column definition to an existing scalar expression in a query.

6- Role Reversal: In Hash Join, after the build input is hashed, the second table, called the probe input, will be read and compared to the hash table. If rows are matched they will be returned. On the execution plan, the table at the top will be used as the build input, and the table at the bottom as the probe input. If the Query Optimizer is not able to correctly estimate which of the two inputs is smaller, the build and probe roles may be reversed at execution time, and this will not be shown on the execution plan.

7- Partitioning: You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned. And it improve query performance, based on the types of queries you frequently run and on your hardware configuration. For example, the query optimizer can process equi-join queries between two or more partitioned tables faster when the partitioning columns in the tables are the same, because the partitions themselves can be joined.

8- Data Compression: This feature is to reduce the size of the database. In addition to saving space, data compression can help improve performance of I/O intensive workloads because the data is stored in fewer pages and queries need to read fewer pages from disk.

9- Deferred Transaction: A deferred transaction is a transaction that is uncommitted when the roll forward phase finishes and that has encountered an error that prevents it from being rolled back. Because the transaction cannot be rolled back, it is deferred. In other editions, a corrupted transaction causes database startup to fail.

 

Author: Hamid Jabarpour Fard

Hamid J. Fard

I am SQL Server Data Platform Expert with more than 9 years’ of professional experience, I am currently Microsoft Certified Master: SQL Server 2008, Microsoft Certified Solutions Master: Charter-Data Platform, Microsoft Data Platform MVP and CIW Database Design Specialist. I also do Pig, Hive and Sqoop development regarding Hadoop Big Data platform. After a few years of being a production database administrator I jumped into the role of Data Platform Expert. Being a consultant allows me to work directly with customers to help solve questions regarding database issues for SQL Server.

More Posts

Follow Me:
FacebookLinkedIn