2

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

10 Answers 10

7

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.

6

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.

1
  • +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, 2009 at 19:00
3

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

3
  • 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." Jun 23, 2009 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. Jun 23, 2009 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? Jun 23, 2009 at 20:02
2

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.

2
  • Thanks, and I don't even want to ask what a sheep simulator is!
    – BillN
    Jun 24, 2009 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, 2009 at 20:16
1

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.

1

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.

1

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

3
  • 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, 2009 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, 2009 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. Jun 24, 2009 at 20:23
1

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

1

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

0

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

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .