SQL2005 vs SQL2008

They're both rock-solid. If you have the choice, go straight to SQL Server 2008 - there are improvements with each release, and even if you're one of the sort of folks that like to wait until a service pack is released before installing an application, SQL Server 2008 SP1 has been out for over a month now.

Here is a directory of the new features in SQL Server 2008. Also inherent in every release (but harder to spot) are improvements to the storage engine that enable SQL Server to perform more optimally and reliably.

You should have no trouble transferring databases from SQL2000 to 2005 or 2008, but you will be wanting to do a proper test of the applications before doing so in a live environment (just in case).

I have no experience of SQL2008 yet, but we have moved many DBs from 2000 to 2005 with only one small issue with some code in an old app. That issue was due to how some views were defined/called in that project. If a view defines a field like "SELECT [somefield]=NULL, [and], [the], [rest] FROM [sometable]" then the app running "SELECT * FROM [theview] WHERE [somefield]='value'" would cause a cast-of-varchar-to-int error sometimes (but not in all cases) in SQL2005 where it never did in SQL2000. The simple solution was to change the view to [somefield]=CAST(NULL AS NVARCHAR). You probably won't run into this particular issue, and we've had no others, but it proves the point that you need to do a full application test in a dev/test environment before making the jump for a live service.

We have moved DBs from 32 bit SQL2005 to 64 bit SQL2005 servers without any issues at all, and I expect going from 64 back to 32 would work just as well.

Take note though that once moved to SQL2005 moving a database back to SQL2000 is a considerable pain so to labour my main point make sure you test thoroughly before moving your own apps. Also make sure you get clear written word from any external vendors involved that their DBs/code have been tested against the version of SQL Server you select.

Edit: one other relevant point: our apps we have moved this way do not make use of extra services like full-text indexing, reporting services, and so on - just the main SQL database service - so I can't comment on whether apps that use such extra features migrate smoothly or not.

Talk to your application vendor. If they can't tell you, I'd do a pilot test on SQL Server 2008 and see if it works. Odds are good it will. (And say nasty things to your vendor for not know what platforms their application will work on.)

SQL Server 2008 also supports newer SQL features that some of your database users might appreciate. If it's production only, maybe not, but if you'll have developers connecting using a query tool, they might appreciate those additional features.

And they might think you rock as a sysadmin, and give you cookies or a hug, or a third monitor or something equally awesome.

At least, if you were my sysadmin, I would.

You should run sql 2008 64 bit. If the application supports 2008 you should be on the latest version. Note that you can't buy 2005 you'll either buy 2008 and downgrade or buy 2008 and use it.

I believe the biggest advantage of 2k8 over 2k5 is data compression. You have page compression than means (far less) I/O, smaller databases to manage, smaller backups and so on and so forth.

I would say, use SQL server 2008 but beware of the 64 bit version. I have been using the 64 bit version SQL server 2008 for half a year, and i think it has some great improvements over SQL 2005, but you should know that there are no 64 bit Jet driver for Excel/Access. If you need to do some integration with Excel then you are stuck with SSIS in 32 bit mode. ( you can still use the 64 bit SQL 2008, but you cannot do OPENQUERY to Excel/Access.

If you also need access to Sybase, then you have to open your wallet and pay a lot for a 64 bit driver from OpenLinc. Remember, you need the OLEDB and the ODBC driver and it is limited to x concurrent connections and limited to y CPU cores. Lets say you have (like in my case) a server with 4 Quad processors and need no more then 5 connections, it is like $20 000 or something like that. And then you need a test server with OpenLinc!

/Håkan Winther

One of the features I like in SQL 2008 is like some people say Datacompression, but the feature I like the most is filtered index.

With this feature you can build covering indexes on a small subset of data for specialized queries. This will improve the performance a lot for the queries that will use the filtered index.

Lets say you have a table like this

CREATE TABLE [DWH].[contract](
    [ID] int IDENTITY(100000000,1) NOT NULL,
    [reportDate] [datetime] NOT NULL,
    [contractnumber] [varchar](15) NOT NULL,
    [_instrument_ID] [int] NULL,
    [_package_ID] [int] NULL,
    [_portfolio_ID] [int] NULL,
    [_counterpart_ID] [int] NULL,
    [ValueX] [datetime] NULL,
    [ValueY] [datetime] NULL,
    [ValueZ] [varchar](20) NULL,
    [Status] int not null,
 CONSTRAINT [PK_contract] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [IX_contract_1] ON [DWH].[contract] 
(
    [reportDate] ASC
)
INCLUDE ( [ValueX],[ValueY],[ValueZ]
) 
WHERE  STATUS=10
GO

When you use the following query the engine does not have to do an index seek and a clustered index lookup:

SELECT ValueX, ValueY, ValueZ FROM dwh.contract WHERE reportdate=GETDATE() AND Status=10

SQL will solve the query with only an index seek, since all the data that is needed for the query exists in the index. And furhtermore, the index only contains records with Status=10, this will affect the INSERT/UPDATE/DELETE performance.

/Håkan Winther

Whatever you decide on don't buy a SQL Server 2005 license when you can buy a SQL Server 2008 license and down grade to run SQL Server 2005. This way if you ever decide to run 2008 you already have a license. I strongly recommend running SQL Server 2008

Overall 2008 is an awesome product, but there are some discontinued features from 2000->2005 and also discontinued features from 2005->2008 (I'm only allowed 1 link because of rep, but search "Discontinued Database Engine Functionality in SQL Server 2008" in Books Online). You should watch out for those as they could be gotchas for your existing apps/databases. There are also some precision issues with the way datetimes are handled that you might run into (2008 has higher precision and that might cause some queries to stop working if they assume .000 for fractions of a second).