switching to compatibility level 90

Question

switching to compatibility level 90

KATHLEEN Y ZHANG

Hall of Fame

Points: 3534
More actions
April 30, 2007 at 12:48 pm

#119845

Hi, just curious if anyone have experienced anything weird when switching database compatibility level from 80 to 90? We upgraded our server to 2005 a month ago but put the database level to 80. Now we are ready to switch to 90 mode. The steps I am thinking are:
1. turn database into single user mode
2. run sp_dbcmptlevel to change to 90
3. turn database into multi-user mode
Is this good enough? As far as replication, etc goes, is there any caveat?
Thanks for any input.
Kathleen

Viewing 15 posts - 1 through 14 (of 14 total)

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

David.Poole SSC Guru Points: 76000 More actions · Answer 1

I take it that you have a separate distributor in your replication set up?

When you switch to compatibility level 90 you may notice that it takes a while as SQL Server various processes. It's been a while since I did it but I think it runs a DBCC CHECKTABLE on every table so if you may want to negotiate a window for down-time.

LinkedIn Profile

KATHLEEN Y ZHANG Hall of Fame Points: 3534 More actions · Answer 2

Thanks for the reply.

We have transactional replication here. The distribution database has already been set to 90 mode.

Is there anything else I should do, like update statistics once the database is in 90 mode?

David.Poole SSC Guru Points: 76000 More actions · Answer 3

If you have a development server I would test your system thoroughly on that.

I took the opportunity to run a DBCC DBREINDEX when I upgraded some databases to SQL2005.

SQL2005 is much less lenient in what it will let you get away with in terms of security. For example in SQL2000 anyone with EXECUTE permissions can see the source of a stored procedure. In SQL2005 they need to be granted VIEW DEFINITION privileges.

There are various tricks to speed up SQL2000 that have no benefit in SQL2005. For example, if you had a clustered index on an integer and you put WHERE clusteredfield BETWEEN -2147483648 AND 2147483647 then SQL2000 could be tricked into doing a CLUSTERED INDEX SEEK. In SQL2005 any gains you may make in getting a CLUSTERED INDEX SEEK tend to be lost by an extra filter step in the execution plan.

Anything you have querying system tables needs to be checked carefully. What was a system table in SQL2000 is now a view simulating the same thing in SQL2005 but is much more security concious.

Really, for such a major upgrade the attitude should be TEST, TEST and TEST again.

LinkedIn Profile

Rick Heiges SSChasing Mays Points: 660 More actions · Answer 4

I second the opinion that you should test it on the test server first. BTW, there is a tool called Upgrade Assistant (not Upgrade Advisor) that allows you to compare "Behavior" differences between 2000 and 2005. It is available as a free download from http://www.scalabilityexperts.com.

Paul Rubel Ten Centuries Points: 1047 More actions · Answer 5

You probably already did this as part of the db migration. Microsoft says to run DBCC UPDATE USAGE after migrating from 2000 to 2005. It's just a one time deal.

Bob Fazio SSChampion Points: 10794 More actions · Answer 6

Biggest impact for me was code that used "SET ROWCOUNT"

Below is the version I use for 2000

SET ROWCOUNT @NumDelete

delete -- TOP (@NumDelete)

FROM myBigTable

where dtime < dateadd(year,-1,getdate())

in 2005 the SET ROWCOUNT doesn't stop the query until the end of a full scan, then it goes back and retrieves the first @NumDelete rows. TOP(@NumDelete) however works just like SET ROWCOUNT does in 2000. Unfortunately TOP isn't valid in 2000

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 7

Actually, TOP is valid in SQL Server 2000 as in TOP 100 -- first 100 records or TOP 10 PERCENT -- top 10 percent of all rows). What isn't valid in SQL Server 2000 is TOP @variablename. Being able to put a variable in the TOP is new to SQL Server 2005.

Luke79 SSCrazy Points: 2571 More actions · Answer 8

Also make sure that you use new joins as in LEFT and RIGHT and not *=, =*. Old style joins don't work with db90. So if you have a lot of views and procs, it's time to review those. We had that issue and had to convert them.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 9

BOL said that the only thing changes from 80 to 90 is that with 90 you can do pivot table and a few other 2005 only commands as well as run things like the disk usage report

all the other issues you will get when upgrading and it doesn't matter what you set your compatibility level to

David.Poole SSC Guru Points: 76000 More actions · Answer 10

BOL isn't the most accurate set of documentation on the planet!

LinkedIn Profile

noeld SSC Guru Points: 96590 More actions · Answer 11

Tell me about it!!!

BOL 2000 vs. BOL 2005 seems like the Bible vs Mickey Mouse adventures

* Noel

Todd Townley Ten Centuries Points: 1320 More actions · Answer 12

Here is another one we ran into just yesterday.

(DB compatability = 80) IsNumeric('\') = 0

(DB compatability = 90) IsNumeric('\') = 1

It seems that the backslash is a money character in Japanese and Korean currency.

So you will not only need to make sure that your databases work, but that your applications also continue to function as expected.

alen teplitsky SSC-Dedicated Points: 30014 More actions · Answer 13

come to think of it, i did find a few syntax and spelling errors in BOL

David.Poole SSC Guru Points: 76000 More actions · Answer 14

Which is which?

Thhe version of BOL I mean not the Bible. I'm not opening up that can of worms!

LinkedIn Profile