SQL Server 2008: moving forward

SQL Server 2008 has been available since Aug 6, 2008. This does not include CTP (Feb 2008) time-frame.
We are close to the year milestone now.

To my surprise there are still questions around if moving to a new version is necessary.

This is especially difficult question to discuss/suggest when SQL Server 2005 adoption cycle is just about being complete.

• We all know about “have to wait until first SP is out” rule.
• What is good about it to justify spending another chunk of IT budget?

To answer first question, there is a cumulative update 1 available since Nov, 2008. One could count it as SP1.

To answer second question could be more difficult.

There is Product Overview White Paper available along with Data sheet where main differences between 2005 and 2008 versions are highlighted.

As you could see many features are enterprise level, but many are to make life regular developers easier as well.

To summarise, while SQL Server 2008 could be considered just an extension to 2005, major enhancements are:

1. Transparent Data Encryption – add protection to your data storage without custom and usually process extensive solutions
2. External Key Management – integrate 3rd party security keys and hardware
3. Enhanced Auditing – monitor data access and modification
4. Advanced Database Mirroring – data corruption protection, improved performance, new performance counters
5. Hot CPU management – change hardware configurations without downtime (for supported hardware platforms)
6. Performance improvements, Service Broker Scalability, Integrated Full-Text Search, Sparse Columns (“zero storage”), no 8,000 byte limit – manage allocated resources and priorities, query plans optimizations, data compression, backup compression (finally 🙂 ), extended performance monitoring tools, 64 bit platform optimization (RAM, hardware, etc)
7. SQL Server Integration Services (SSIS) improvements
8. Policy-Based Management
9. Installation improvements
10. ADO.Net Entity Framework and CLR Integration – Object-Oriented style support instead of Table/Field approach
11. LINQ – even though some pieces (?temporary solutions?) were available before, now LINQ becomes first-class citizen for SQL Server. Forget your T-SQL and program using LINQ syntax.
12. T-SQL improvements – even though LINQ may be a way to go, T-SQL still is not forgotten with added support for Table Value Parameters (TVP), Object Dependencies Views and Functions, new data types: DATE, TIME, DATETIMEOFFSET, DATETIME2 allowing conserve storage space and improve time precision, FILESTREAM, GEOGRAPHY and GEOMETRY  datatypes, and HIERARCHYID for better support of tree based data structures
    Compound operators – lovely “+=” syntax and in-line variable assignment DECLARE @myVar int = 5
    MERGE support – manage data inserts/updates in one SQL statement
13. SQL Management Studio gets IntelliSense and other improvements and better Source Control integration
14. SQL Management Studio T-SQL debugging – not as robust as it is in Visual Studio, but sufficient in many situations.
15. Table Partitioning management
16. Hot-swap data connectors – easier support for disconnected data applications
17. Improved MS Reporting Services and report builder– MS Word/Excel/SharePoint integration improved
18. OLAP support improvements

Even if you decided to stay with 2000/2005 version, keep in mind a few things which will make your future upgrades easier.

The list of features dropped/changed in SQL Server 2008.

1. Deprecated SQL Server Features in SQL Server 2008
   • DTS support (link) is replaced with SSIS (migration)
   • ADHelper service
   • SOAP/HTTP endpoints, sys.http_endpoints, sys.endpoint_webmethods (Transact-SQL)
2. Discontinued SQL Server Features in SQL Server 2008
   • Some SMO Classes has been discontinued
   • Surface Area Configuration Tool (SAC)
   • Discontinued Command Prompt Parameters for SQL Server Setup
3. SQL Server Native Client has been upgraded with some behavior changes

How about some technical data and stats? To begin with, Microsoft team is no longer publicly presents any data which will show some benchmarks between versions, editions or between different database server platforms, so we have to relay on someone outside do the job.

Yes, comming with mathematical background I can agree with some arguments that this data is not accurate and always represent just what was tested in each particular case in each particular hardware configuration. With statistics being used by marketing department it can be very interesting.

Therefore, we have to be very sceptical about any stats presented anywhere. Nevertheless:

• Microsoft:
  • Microsoft SQL Server 2008: Compare or count your money
  • Competitive Comparison of SQL Server 2008 Integration Services by SSIS Team
• Some 3rd party data:
  • Count your time or Large Query Performance from SQL Server 2000 to 2008, 32 & 64-bit
  • Compare features or Cross Compare SQL Server 2008 Spatial, PostgreSQL/PostGIS 1.3-1.4, MySQL 5-6

As far as my own experience with a new version – it has been positive and I would highly recommend to go with this upgrade.