SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 SQL Server Administration (2008)
 Compatibility Mode
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

icw
Constraint Violating Yak Guru

378 Posts

Posted - 08/18/2010 :  16:24:28  Show Profile  Reply with Quote
Hi I don't really understand what Compatibility mode is for, can anyone point me to a simple explananation.

For example I restored a SQL 2000 database to a SQL server 2008.
Th ecompatibility mode was automatically set to sql 2000.
What difference would it make if I set it to SQL 2008 compatibility

icw
Constraint Violating Yak Guru

378 Posts

Posted - 08/18/2010 :  16:30:17  Show Profile  Reply with Quote
just done a bit of research. its so functions and queries that are not supported in sql 2008 can be supported ..right?
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/18/2010 :  17:47:17  Show Profile  Reply with Quote
Well .. kinda ...

You have an application that works fine in SQL2005.

You need to move it to a server running SQL2008. To minimise your migration effort you run it in "SQL2005 compatibility mode" on your shiny new SQL2008 server.

Now the clincher for me is "Why would you choose to do this?"

The obvious answer is because you are not planning to do any testing. Well good luck with that one!

If you move from SQL2005 to SQL2008 you had better test that your application works Just Fine. Maybe there will be fewer things to fix if you stick with the old Compatibility Mode. But ... my view is, if you have to do all that testing anyway why not also change to the latest compatibility mode? then you know that your application works fine with that mode, and you can then take advantage of any new features you choose to, and you've future-proofed yourself for a good while longer too ...
Go to Top of Page

russell
Pyro-ma-ni-yak

USA
5072 Posts

Posted - 08/18/2010 :  19:07:10  Show Profile  Visit russell's Homepage  Reply with Quote
The reason is that many features supported in earlier versions are no longer supported.

Start here: http://msdn.microsoft.com/en-us/library/ms143729(v=SQL.100).aspx

Then look here too:
http://blogs.technet.com/b/andrew/archive/2008/05/27/deprecated-features-in-sql-server-2008.aspx

if you flip the compatibility mode to 10, you might find things don't work.

As Kristen said...test, test, test. Then test some more.
Go to Top of Page

icw
Constraint Violating Yak Guru

378 Posts

Posted - 08/19/2010 :  04:39:47  Show Profile  Reply with Quote
thanks a lot
Go to Top of Page

GilaMonster
Flowing Fount of Yak Knowledge

South Africa
4507 Posts

Posted - 08/19/2010 :  10:32:48  Show Profile  Visit GilaMonster's Homepage  Reply with Quote
Do bear in mind that some behaviour changes apply regardless of the compat level. I don't have a handy example, but I do recall from when I was upgrading a DB to 2005 some queries worked on SQL 2000 but not on 2005, even in compat mode 80.

--
Gail Shaw
SQL Server MVP
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 08/19/2010 :  11:10:41  Show Profile  Reply with Quote
"some queries worked on SQL 2000 but not on 2005, even in compat mode 80."

That's my key point - as a full regression test is needed anyway then its no harder to change to latest Compatibility Mode (assuming the incompatibilities are not too onerous to fix!)

EDIT: @Gail: That doesn't read very well, sorry. I only mean that your point reinforces my earlier point.

Edited by - Kristen on 08/19/2010 11:15:04
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000