question

Naina avatar image
Naina asked

sql server 2008 is backward compatible with sql server 2000 or not

Hello,

Please let me know if we found some issues with SQL SERVER 2000 while running the Upgrade Advisor, and we fix that....IS IT backward comaptible rite ??? like whatever we fix in SQL SERVER 2008 is backward comaptible with SQL SERVER 2000.

Kindly let me know about this.

Thanks Naina

sql-server-2008sql
1 comment
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Naina avatar image Naina commented ·
Thanks to all..... Thanks for your valuable comments..... Naina
0 Likes 0 ·

5 Answers

·
Grant Fritchey avatar image
Grant Fritchey answered

How do you mean backward compatible? If there were TSQL errors that you had to fix, such as a missing WITH clause, yes, those pieces of TSQL code are backwards compatible. But, when you transfer a database to 2008, it's a 2008 database and no longer a 2000 database, so it's not backwards compatible.

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Naina avatar image Naina commented ·
Hello Grant, Thanks for your reply... Im taling about TSQL errors , suppose if we modify any procedure in SQL SEREVR 2008 is compatible with SQL SERVER 2000. IS everything related to TSQL code in SQL SEREVR 2008 is compatible with SQL SERVER 2000. Thanks Naina
0 Likes 0 ·
Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Congrats on the 8K mate :)
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Thanks. 10k coming up for you shortly, probably this week.
0 Likes 0 ·
Tom Staab avatar image
Tom Staab answered

If you are upgrading to SQL Server 2008, you might be able to (temporarily) use a compatibility level of 80 (SQL Server 2000) while completing necessary application/database changes for full SQL Server 2008 compatibility.

For more information, read this MSDN article: http://msdn.microsoft.com/en-us/library/bb510680.aspx

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

It's not guaranteed, especially if your databases make reference to the system tables. The 2000 system tables are emulated in 2005+, irrespective of compatibility level. There are some columns where the emulation is not 100%.

And if you're saying 'will all the SQL i write in 2008 be compatible with 2000' then the answer is no - as Grant has made clear.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered

No, making something work in 2008 does not guarantee it working in 2000. The SQL Server product hass developed over time, gaining new features and losing old ones.

If a piece of TSQL causes errors in 2008 and you change it to run successfully, there is still a chance it will fail if you run it against a SQL 2000 database and it will need changes to become error free. This may then not work on 2008.

10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Grant Fritchey avatar image
Grant Fritchey answered

Based on the response to my original answer:

Absolutely not. TSQL has a very large number of changes between SQL Server 2000 and SQL Server 2008. There will be any number of things that you can do, CROSS APPLY, OVER, ROW_NUM, CTE's, just to name a few, that are absolutely non-functional in SQL Server 2000.

So, if you're trying to maintain a script to be completely compatible with SQL Server 2000, you'll need to write it and test in 2000 first, then apply it to 2008.

However, why would you maintain backwards compatibility like that. SQL SErver 2000 is over 10 years old at this point and will be completely unsupported by Microsoft in less than three years. The time to begin retiring those servers is now.

3 comments
10 |1200
Toggle Comment visibility. Current Visibility: Viewable by all users

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

CirqueDeSQLeil avatar image CirqueDeSQLeil commented ·
Just a small notation - I believe Grant is referring to SQL 2000 when he says they are absolutely non-functional in the first paragraph (rather than sql 2008).
0 Likes 0 ·
TimothyAWiseman avatar image TimothyAWiseman commented ·
Just to play devil's advocate, I have dealt with systems written in SQL Server 2000 that would not run outside of compatibility mode on 2008 where the ROI just did not exist to do a proper migration to 2008 because there were no changes desired to the functionality by anyone. I could have migrated it, but it would have been a project on my own without management support or any resources, so I just maintained the backups and basic maintenance until I left that job. With all that said, I would never do new development in 2000...
0 Likes 0 ·
Grant Fritchey avatar image Grant Fritchey ♦♦ commented ·
Oopsie. Fixed the typo. Thanks. Timothy, I didn't meant to suggest that you ought to run in compatibility mode. I don't trust that either. I'm with you. I just meant if they need to maintain 2000, build the scripts on a 2000 server first, then run it on 2008. Building it on 2008 first is too likely to cause issues.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.