Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

We are investigating replacing an existing SQL2000 server on Windows2003 (32Bit) with new hardware and a newer version.

We are looking at an application that requires at least SQL2005, but will run on SQL2008 as well. Our other DB's should run on either as well.

Has anyone had any experiences good or bad with either version?

The Hardware we are likely to get will be 64bit capable, so running SQL2008 64 bit is an option as well.

Thanks

share|improve this question
add comment

10 Answers

up vote 7 down vote accepted

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.

share|improve this answer
add comment

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.

share|improve this answer
    
+1 for the test recommendation and the specific scenario. We always test as thoroughly as possible, but not as thoroughly as we'd like. –  BillN Jun 23 '09 at 19:00
add comment

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.)

share|improve this answer
    
I think you missed the middle paragraph ;-) "We are looking at an application that requires at least SQL2005, but will run on SQL2008 as well. Our other DB's should run on either as well." –  Aaron Alton Jun 23 '09 at 19:10
    
Gee, don't I feel like an idiot. In that case, go for SQL Server 2008. SQL Server 2005 is a great product, but SQL Server 2008 has 2+ more years of enhancements. –  Evan Anderson Jun 23 '09 at 19:23
    
Hahaha...that's hardly cause to feel like an idiot :) Valid point for many application vendors though - how long does it really take to certify some vendor's applications on the latest release of SQL Server? –  Aaron Alton Jun 23 '09 at 20:02
add comment

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.

share|improve this answer
    
Thanks, and I don't even want to ask what a sheep simulator is! –  BillN Jun 24 '09 at 15:00
    
Too late, I'll tell you anyway: When I first starting writing computer programs, I tried writing a program that simulated herds of sheep. I had to pick internet screennames about that time, and the name was open, so I used that. You may call me Sheep; all my friends do. –  J. Polfer Jun 24 '09 at 20:16
add comment

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

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

share|improve this answer
    
Håkan, good points. We DO use ODBC to connect from Excel/Access, but not on the Server, so we'd still use the 32 Bit Driver on the Workstations, right? –  BillN Jun 24 '09 at 15:01
    
Correct, the limitation only exists if you are trying to integrate excel into the server FROM the server. I'm pretty sure (80%ish) that running excel on a server OS got unsupported when windows 2003 came out. –  Jim B Jun 24 '09 at 17:18
    
The Jet driver became unsupported with 64 bit version of Windows 2003 as you pointed out. It does not exists any 64 bit jet driver atl all, not even for Vista. I have heard some rumor about that MS ran in to trouble trying to convert Excel to 64 bit, right or wrong, i do not know. –  Hakan Winther Jun 24 '09 at 20:23
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

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).

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.