There are soomany instances u might end up with a question if you really need to decide for an upgrade. However the question arises "Do we really need to upgrade and to what version should I upgrade"
The journey of SQL Server .initially started in 1988. Since the start of the SQL-Server there were many changes and enhancements took place.
However SQL-Server got its importance and was recognized world wide after the release of SQL-Server 2000 at a whole new level. After the release of SQL-Server 2000 it was widely introduced and used by business .After the year 2000 the E-commerce boom took place and since then because of increase of the need for new features, Microsoft started started fixing bugs by releasing new patches for existing sql-server 2000.
After the release of 2000 microsoft did not release another until 2005. however Microsoft wanted to really make a difference at what they are really Good at which is User friendlyness .So microsoft wanted to intorduce SSMS an all In one tool to do most of the complicated regular work they integrated Query-Analyser into the Enterprise Manager and released the new Sqlserver 2005 with all new enhanced features and new GUI. Since then SSMS (Sequel Server Management Studio) Still exists.
From SQL-Server 2000 to todays SQL-Server 2017 microsoft kept adding new features along with new market standards and with the Competition. Many new Security features, Enhancements and possibilities started to show up in every new edition and version of SQL-server.
If at all you are Currintly Running on MS-SQL-Server 2008 and looking at Feature Comparison's for upgrading Pleas see SQL-Server Feature Comparision from 2008 to 2016
Below given are the differences between each version from 2000, 2005, 2008, 2008R2, 2012, 2014
Please Note: I will keep updating this page as soon as I come across anything new
| 2000 | 2005 |
|---|---|
| No Schema | Schema |
| No reporting services | Reporting services |
| No XML | Inroduced XML |
| No Compression | Compression |
| Query analyzer | Query editor |
| No Varchar, Varbinary | Varchar, Varbinary |
| No encryption | Encryption of Tables |
| MAX 16 instance | max 50 instances |
| 65535 Max DB's allowed | 1048575 Max DB's |
| No SSMS | SSMS |
| No Mirroring | Mirroring |
| No Snapshotisolation | Snapshotisolation |
| No SSIS | SSIS |
| 2005 | 2008 |
|---|---|
| Tabular level Encryption | Full DB Encryption |
| Encrypted backup | |
| 64 Logical Processe | 256 logical process |
| File Streaming | |
| Merge Statement | |
| only Datetime | DATE, time , DATETIMEOFFSET, DATETIME2 |
| Policy based Management (facets) |
|
| data compression | |
| backup compression | |
| GUI DIFFERENCES |
| 2008 | 2008R2 |
|---|---|
| 64 Logical Process | 256 logical process |
| Power PIVOT Introduction | |
| Data shapes like Mapping, routing, &custom shapes | |
| 2008 | 2012 | TRY….CATCH | THROW |
|---|---|
| Log Shipping, Replication, Mirroring & Clustering | ALWAYS ON |
| no supportfor Windows Server Core. | Support for Windows Server Core |
| Columnstore Indexes not supported. | New Columnstore Indexes feature. |
| PowerShell Supported | Enhanced PowerShell Supported |
| Distributed replay features not available. | Distributed replay Feature. |
| PowerView not available in BI features | PowerView Introduction. |
| EXECUTE … with RECOMPLIE feature | Enhanced EXECUTE with many option like WITH RESULT SET…. |
| Maximum numbers of connections is 32767 | unlimited concurrent connections. |
| 27 bit bit precision for spatial calculations. | 48 bit precision for spatial calculations |
| TRY_CONVERT() and FORMAT() functions are not available | TRY_CONVERT() and FORMAT() functions are newly included |
| ORDER BY Clause does not have OFFSET / FETCH options for paging | ORDER BY Clause have OFFSET / FETCH options for paging |
| In SQL Server 2008, audit is an Enterprise-only feature. Only available in Enterprise, Evaluation, and Developer Edition. | In SQL Server 2012,support for server auditing is expanded to include all editions of SQL Server. |
| Sequence is not available in SQL Server 2008 | Sequence is included in SQL Server 2012. |
| The Full Text Search | Enhanced Full Text Search |
| Analysis Services in SQL Server does not have BI Semantic Model | Analysis Services will include a new BI Semantic Model (BISM). BISM is a |
| (BISM) concept. | |
| The BCP not support –K Option. | The BCP Utility and SQLCMD Utility utilities enhanced with -K option, which allows to specify read-only access to a secondary replica in an AlwaysOn availability group. |
| sys.dm_exec_query_stats |
sys.dm_exec_query_stats added four columns to help troubleshoot long running queries. new function concat to strings |
| 2012 | 2014 | IN-MEMORY OLTP |
|---|---|
| ony resource governer | IO Governance |
| VDHX | VDHX Support |
| allways on | Enhanced ALlways on |
| Query optimization Enhancements | |
| power Query | |
| Windows Azure HDInsight Service | |
| Analytics Platform System (PDW V2) | |
| power BI, Power Map, Mobile interfaces | |
| AZURE backup | |
| Cloud-Backup encryption and support | |
| ALWAYS ON Replicas for cloud DR | |
| Windows Azure Deployment UI | |
| Larger SQL Server VMs and memory sizes now available in Windows Azure | |