Advantages of Keeping SS2000 Compatibility Mode

Question

Advantages of Keeping SS2000 Compatibility Mode

Tom Carnahan

SSCommitted

Points: 1788
More actions
December 6, 2011 at 7:44 am

#251604

I have a SS2000 database that we want to bring over to SS2008, however, there is pressure to keep the database in the SS2000 compatibility mode (80). The database has no DTS packages. Its not being used by SSAS or SSRS. There are few jobs that run against it.
I have been asked to justify keeping it in mode 80.
Can anyone think of a reason to keep it in compatibility mode 80 other than not having to look for incompatible features that would have to be found by regression testing of the only application that runs on it?
Thanks ahead of time for your help,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Just an FYI... Please ignore if it’s irrelevant.

COMPATIBILITY_LEVEL question: Upgraded to 2008 R2... Sort of... or "Is there a tool that will tell me what this upgrade broke?"

http://www.sqlservercentral.com/Forums/Topic1216340-1550-1.aspx#bm1217103

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 2

I think the relevant word is "compatibility", which I don't read as SQL 2000 mode, but more along the lines of SQL 2000 rules.

What I mean is that you are running SQL 2008, it doesn't have a SQL 2000 engine tucked away just in case. What it does mean is that some syntax is allowed that otherwise wouldn't be, the one that comes to mind is outer join syntax, the *= and =* are no longer allowed.. But you are still running the SQL 2008 engine..

As a side point SQL 2012 does not support SQL 2000 compatibility, or DTS.

CEWII

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Tom Carnahan (12/6/2011)
Can anyone think of a reason to keep it in compatibility mode 80 other than not having to look for incompatible features that would have to be found by regression testing of the only application that runs on it?

Nope. None whatsoever.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Steve Jones - SSC Editor SSC Guru Points: 736063 More actions · Answer 4

I would only use this mode if I needed to ensure the database functioned under SQL 2000 rules. No other reason to use this.

Note that I would also test it under SQL 2008 rules (compat mode 100) if I could and if it worked, run it in that mode.

Tom Carnahan SSCommitted Points: 1788 More actions · Answer 5

Those sound like very good reasons ... thanks everyone! 😀