January 23, 2008 at 3:38 pm
Does anyone know of a comprehensive list, or link, documenting most/all the features for SQL Server 2005 that cannot be used, or are not fully compatible, with databases set at compatibility level 80?
It is not finding discussions and documentation about the effects of upping the compatibility level 80 to 90 for databases that have been migrated to SQL Server 2005. It's well discussed in these forum ;), and fairly well documented in the BOL for sp_dbcmptlevel (ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/508c686d-2bd4-41ba-8602-48ebca266659.htm), as well as the Upgrade Advisor help file. But it is harder find a good list of the SQL 2005 features that cannot be used on level 80 databases, other than just a general statement about the new features (new T-SQL reserved words, etc).
My group has several databases migrated from SQL Server 2000 that are still on compat level 80, and now we want to "sell" the advantages of upping the level to 90. We would like to show a list of all the new features that can only be done with level 90 databases. Once the databases in production are set to 90, they would not react well to an issue that forces us back down to compat level 80. So to prove that the extra testing is worthwhile, we'd like to present the advantages in a full list.
Thanks in advance!
January 27, 2008 at 8:07 pm
I don't know of any comprehensive lists. In my book, the biggest feature you gain by moving to 90 compat mode is being able to use all the DMVs.
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 28, 2008 at 1:19 am
One important thing (which is documented in BOL) is that the "WITH" keyword for table and index hints is NOT optional in mode 90. The script generated by Enterprise Manager in SQL 2000 for table changes likes to omit the WITH keyword. 🙁
January 28, 2008 at 4:37 am
There's no comprehensive list, but ISTR that I had to change compatibility before I could use PIVOT / UNPIVOT.
I can't tell for sure now because all the DBs I use PIVOT on are set at 90, none at 80.
January 28, 2008 at 5:17 am
Reports via SSMS are only available in 90 mode.
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
January 29, 2008 at 1:24 pm
We had lot of trouble while migrating from 2k to 2k5. And our applciation is so big that it took us months to finaly come to 2005; but all the dbs are still at level 80. since, this took this long, there were many code changes that went into the system since the upgrade. Now, I'm planning to change the cmpatibility level to 90; but I'm stuck since, I cannot run the Upgrade Advisor on the new databases as they reside on 2005 instance. All my servers, includign Dev/QA are in 2005 now. since my dbs are big in size, I'm finding it difficult to install a new 2K instance and restore the dbs. Can anybody clear my doubts -
1) Can I restore the cmpt 80 db backups taken on a 2005 server on to a 2000 server instance?
2) Is there any way, I can make Upgrade advisor to run against cmpt 80 dbs attached to a 2005 instance?
3) Is there any scripts or so that will do the same purpose as Upgrade advisor.
Thanks,
--smk
January 29, 2008 at 4:29 pm
#1 - Absolutely not. Once a database is upgraded to the latest version, it can't be reverted to a down-level version
Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005
January 30, 2008 at 12:40 am
1,2,3: No
MCP ID# 1115468 Ceritified Since 1998
MCP, MCSE, MCP+I, MCSE+I, MCSA,
MCDBA SQL7.0 SQL 2000, MCTS SQL 2005
January 30, 2008 at 4:14 am
Actually, you have 2 options, but both are a LOT of hard work.
1) restore a pre-conversion backup to SQL 2000. Change the stuff you changed the first time around (without upgrading) and then run the Upgrade Advisor.
2) Script out your database and all its objects. Run the Create Statements against a brand new SQL 2000 database. As long as you don't have SQL 2k5 Only data types or other objects, this should work fine. Then use the Import/Export wizard in 2005 to send the top 500 rows or so of data from 2005 to the 2000 database. Then run the Upgrade Advisor.
You don't necessarily need the data to run the UA, but I'd advise having a little bit in your 2000 DB just in case. It won't hurt to have the data there and you might uncover something that you normally wouldn't see in the UA without the data.
Those are your only options, though. You can't restore a 2005 backup to a 2000 database or detach / attach from 2005 to 2000. Sorry.
January 30, 2008 at 10:21 am
Thanks for all your suggestion guys.
I will go for Brandie's second option. The first option is difficult especially because of the size of the dbs and it is difficult to trace all the changes that has been applied to the system.
--smk
January 30, 2008 at 10:27 am
Let us know how that works for you.
January 30, 2008 at 11:11 pm
Did this thread take a left turn without indicating ?
One really nice feature that 2005 has is the CLR. you can do some nice Visual Studio Managed Code function creation but it will not work below compatibility levl 90
The systems fine with no users loggged in. Can we keep it that way ?br>
January 31, 2008 at 4:11 am
Yes, this thread was hijacked by smk for different questions.
s.rich, did you get your question answered?
January 31, 2008 at 11:09 am
Yes, mostly. Obviously the bottom-line answer is that there is not a well documented list, like you get with the BOL with sp_dbcmptlevel for behavior differences coming up from lower compat levels.
I have already come across several of the incompatible features on such a list; we've probably all seen a few. The reports in SSMS, and some of the T-SQL features, like the PIVOT / UNPIVOT that you mentioned Brandie. CROSS APPLY also cannot be used, and there is documentation that EXTERNAL, REVERT, TABLESAMPLE are reserved words with level 90. I've tried creating stored procedures with AS EXTERNAL NAME for the methods in a CLR assembly, and found that the compat level has to be higher to do that. I don't see mention of other SQL features, like Service Broker, row versioning, MARS, etc, so I guess it'll be trial-and-error with those. 😉 Still, it would have been nice to have these differences already well documented.
For the DMVs however, if there is an issue using the DMV on a level 80 database, then there seemed to be a workaround, at least for the ones that I've used. For example, you cannot pass a DB_ID() to sys.dm_db_index_physical_stats, but you can set a @dbid variable with the database id you want to report, and pass the varible to sys.dm_db_index_physical_stats. Then it works great!
So, which DMV's you cannot use at all on a compat level 80 database?
March 8, 2008 at 6:35 pm
Sorry guys.. for not replying with my updates.. I got busy with my new born baby.. IT'S A GIRL!!!
I got the dbs scripted out using the Publishing Wizard and using them created new 2k dbs and ran Upgrade Advisor against them. It was a pain, even though I used the command line options for PW and Create DBs -I have more than 45 dbs in total. Also, had to go through the create script logs as there were lot of old orphaned procedures and views with referenced tables being deleted in earlier releases...( PW scripts them and when you fire the permission scripts in new db, they fail)
Anyway, the UA ran well and gave me predictable results with warning pointing to objects using old style OUTER JOINS and ORDER BY clause with aliased prefixes etc.
It will take months before we finish our testing and our changes go into prod; so I have to wait and see if there were many that UA would have missed to warn me against.
Thanks,
--Smk
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply