September 21, 2008 at 12:11 pm
At a company I recently went to work for, upgraded from SQL Server 2000 to SQL Server 2005 back some time in late 2006 to early 2007 and so they've been using 2005 for quite some time. The primary reason for SQL Server is to serve as the RDBMS for the accounting software the company uses which uses SQL Server to store the apps data. The software provider of this application does not fully support SQL Server2005 meaning you can run the application against a SQL Server2005 database but it mustbe set with a compatability level of 8 which is of course the compatability setting from SQL Server 2000.
It seems as time goes on that I find more and more feature and perks of SQK Server 2005 non-useable because the database is seto the compatability level of 8. When a database with a compatability level of 8 is running on SQL Server 2005 is it truely a database thathas beeen upgraded to SQL Server 2005 or is it really the same DB as it was under 2000, so much so that without any or at leastvery little change, you could movethe thing back to a SQL Server 2000 instance?
I read many postings about upgrading a database to SQL Server2005; would our database be classified as one of these upgraded databases or does ithaveto be set with a compatability level of 9 to meet that description?
Thanks in advance!
Kindest Regards,
Just say No to Facebook!September 21, 2008 at 1:26 pm
YSLGuru (9/21/2008)
When a database with a compatability level of 8 is running on SQL Server 2005 is it truely a database thathas beeen upgraded to SQL Server 2005 or is it really the same DB as it was under 2000, so much so that without any or at leastvery little change, you could movethe thing back to a SQL Server 2000 instance?
It's truly a 2005 database and cannot be easily moved back to SQL 2000. From the time a database is attached/restored to a 2005 instance, all the file structures and system objects are 2005. To revert that DB to a SQL 2000 instance, you'd have to script out the schema and bcp out the data as you cannot attach or restore a SQL 2005 DB onto SQL 2000
It's easy to see, go into that database (compat mode 80 on SQL 2005) and run any of the following:
SELECT * from sys.objects -- new system view
SELECT * FROM sys.dm_exec_requests -- Dynamic management view
You'll also notice that the SQL 2000 system tables are no longer tables. - sp_help 'sysobjects'
All the compat mode affects is whether certain features are available or not. Even running in compat mode 80 on 2005 you'll have all the advantages of a smarter query optimiser and various other optimisations that were added to the DB engine
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
September 21, 2008 at 1:33 pm
Gail,
Thank you for the quick reply. i did have a follow-up question about this.
I know that you can't make any guarantees about whether this should work or not but based on what you said about what the DB Compatability setting does, can you think of any good reason a software company would have for supporting their app on SQL 2005 but only when the compatability setting is set to 8; to the SQL Server 2000 setting?
If the setting enables or disables SQL Server 2005 fetures and because the database's structure and such has already been altered by 2005, I can't see why they'd be unwillingto allow for a compatabilitysetting of 9 except that maybe they have no one on staff familiar enough with SQL Server 2005 to confidentially support a client using their product with that version of SQL Server.
Thanks again!
Kindest Regards,
Just say No to Facebook!September 21, 2008 at 1:46 pm
Yeah, if the DB is using features that were deprecated in SQL 2000 and removed in SQL 2005. They'll still work in compat mode 80
Old style joins come to mind - table1.col1 *= table2.col2
There are other places where SQL 2005 is more picky on the sql syntax in mode 90 than in mode 80. The following works in compat mode 80 and fails in 90 and higher
select id, name, name from sysobjects order by name
Probably it's just a case that they haven't tested fully on SQL 2005 and hence won't support the product on that. Personally, I'd have some words with the company's support people on this. 2008 is out now, there's no excuse for been two versions behind. SQL 2000 isn't even supported any longer. They may lose customers over that. Or they may have a newer version that does work on SQL 2005 and they'll want you to upgrade to get the benefits.
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
September 21, 2008 at 2:03 pm
Probably it's just a case that they haven't tested fully on SQL 2005 and hence won't support the product on that. Personally, I'd have some words with the company's support people on this. 2008 is out now, there's no excuse for been two versions behind. SQL 2000 isn't even supported any longer. They may lose customers over that. Or they may have a newer version that does work on SQL 2005 and they'll want you to upgrade to get the benefits.
I wish it were a case of just needing to purchase a new version oftheir product. i worked for this company for several years and know first hand their attitude towards the database side of the application is lke that of the typical programmer who has never had any real SQL or Relational DB training. While they do use SP's for some of the more intense processes, the SP's are like spagheti code with each using a cursor even for the simplest of actions; all because they have VB programmers making DB design decissions.
Thanks again for the info.
Kindest Regards,
Just say No to Facebook!September 21, 2008 at 6:11 pm
Being that the company you're talking about appears to be nearly ignorant of SQL in general and probably would not have used any advanced features, my suggestion would be to make a new database from one of the backups, set it to version 9, and do some testing.
--Jeff Moden
Change is inevitable... Change for the better is not.
September 22, 2008 at 7:23 am
GilaMonster (9/21/2008)
YSLGuru (9/21/2008)
When a database with a compatability level of 8 is running on SQL Server 2005 is it truely a database thathas beeen upgraded to SQL Server 2005 or is it really the same DB as it was under 2000, so much so that without any or at leastvery little change, you could movethe thing back to a SQL Server 2000 instance?It's truly a 2005 database and cannot be easily moved back to SQL 2000. From the time a database is attached/restored to a 2005 instance, all the file structures and system objects are 2005. To revert that DB to a SQL 2000 instance, you'd have to script out the schema and bcp out the data as you cannot attach or restore a SQL 2005 DB onto SQL 2000
It's easy to see, go into that database (compat mode 80 on SQL 2005) and run any of the following:
SELECT * from sys.objects -- new system view
SELECT * FROM sys.dm_exec_requests -- Dynamic management view
You'll also notice that the SQL 2000 system tables are no longer tables. - sp_help 'sysobjects'
All the compat mode affects is whether certain features are available or not. Even running in compat mode 80 on 2005 you'll have all the advantages of a smarter query optimiser and various other optimisations that were added to the DB engine
I have a SQL 2000 database on a SQL 2005 server (still in 80 compatibility mode), and it seems that some standard reports (Index Physical Stats, etc.) don't run. It seems that these reports don't run in 80 mode, so at least in this respect the 2000 database is something not quite equal to a 2005 database.
Is there a way to get these reports to run or build queries with the same results? I'd really like to see the results without switching compatibility mode - which leads me to the question of how risky it is to switch from 80 to 90 mode, and what the considerations are.
Thanks for any help,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 22, 2008 at 7:34 am
webrunner (9/22/2008)
I have a SQL 2000 database on a SQL 2005 server (still in 80 compatibility mode), and it seems that some standard reports (Index Physical Stats, etc.) don't run. It seems that these reports don't run in 80 mode, so at least in this respect the 2000 database is something not quite equal to a 2005 database.
Probably because the queries use cross applies or CTEs, which don't work under 80 compat mode. Without seeing the queries been run, hard to say, but if you're running the queries from management studio, a trick is to run the queries in the master DB and reference the user database using 3 part naming for all objects.
Your DB is a SQL 2005 database. The fact that it's in 80 compat mode does not affect the version of the database. If it's attached to a SQL 2005 instance, it is a SQL 2005 database.
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
September 22, 2008 at 7:39 am
Once you switch to 90, you can't switch back to 80, except by restoring from a backup taken before the switch. (Which means, of course, that you'd lose any data or schema changes made after that backup.)
If you switch to 90 and then find out that there's code that won't run that way (which is possible), you'll have to rewrite that code. If that code isn't in procs, but instead is in the application, and you don't have the source code, then the application will be broken.
So, you might create a test database in compatibility 90, connect a test copy of the application to it, and see if it works as desired.
But really, till the company supports 2005, I would leave it alone.
If you really need 2005 features, consider what you need them for, and ask if having them in a separate database on the same server, with cross-database queries, would accomplish what you need.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
September 22, 2008 at 8:00 am
GSquared (9/22/2008)
Once you switch to 90, you can't switch back to 80, except by restoring from a backup taken before the switch. (Which means, of course, that you'd lose any data or schema changes made after that backup.)If you switch to 90 and then find out that there's code that won't run that way (which is possible), you'll have to rewrite that code. If that code isn't in procs, but instead is in the application, and you don't have the source code, then the application will be broken.
So, you might create a test database in compatibility 90, connect a test copy of the application to it, and see if it works as desired.
But really, till the company supports 2005, I would leave it alone.
If you really need 2005 features, consider what you need them for, and ask if having them in a separate database on the same server, with cross-database queries, would accomplish what you need.
Thanks to you and Gila for the advice. Keeping production in 80 but switching to 90 in test to see how it works sounds like the way to go.
I'll see if I can find the SQL for the standard performance reports to see if I can replicate some of that reporting with the 80 database, or find other ways using SQL Profiler or Performance Monitor.
Thanks again,
webrunner
-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html
September 22, 2008 at 8:00 am
GSquared (9/22/2008)
Once you switch to 90, you can't switch back to 80, except by restoring from a backup taken before the switch. (Which means, of course, that you'd lose any data or schema changes made after that backup.)
The change of compatibility mode can be made in any direction at any time. You can take a 2000 db, attach it to SQL 2005, change the compat mode to 90 and then down to 70 if you like.
I have a DB created on SQL 2008 that's running in compat mode 80 right now (down from 100)
It's the upgrade of database version (done upon first attaching/restoring to a higher version instance) that can't be downgraded.
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
September 22, 2008 at 9:57 am
Based on the numerus responses to my original post I have, I think, a better understanding of the compatability mode setting. As soon as my database was restored to and running on an instance of SQL 2005, it was in the format that all SQL 2005 DB's are as far as additional DB Objects (new as of 2005) and so on. The compatability setting doesn't determine the database's format so much as what features work for it.
The question(s) then are:
1) Are there some features or T-SQL syntax that could be used on/against the DB on SQL 2000 that can't be now that it is on SQL 2005 even though the compatability level is set to 8? If the answer is yes then does BOL or any other resource list all of these?
2) Are there features and or changes in T-SQL as of SQL 2005 that are not applicable to use against a database on SQL 2005 with a compatability level of 8? I'm sure the answer is yes so the next question would be is there any single resource like BOL 2005 that lists these?
It seems like when you have a database on SQL 2005 with a compatability level of 8, there are no simple answers. For example it is safe to say with confidence that a database running on SQL Server 2000 can not use the new T-SQL feature CTEs and that a database running on SQL Server 2005 with a compatability level of 9 can. But what about a database on SQL Server 2005 with a compatability level of 8?
From what I've read so far (mostly in BOL), it seems like some features/T-SQL new as of SQL 2005 can be used on this database and some can't. But what isn't clear is a complete listing of what can be used on/against this database and what can't. At least I have yet to find something like this.
Thoughts/Comments?
Thanks
Kindest Regards,
Just say No to Facebook!September 22, 2008 at 10:23 am
YSLGuru (9/22/2008)
2) Are there features and or changes in T-SQL as of SQL 2005 that are not applicable to use against a database on SQL 2005 with a compatability level of 8? I'm sure the answer is yes so the next question would be is there any single resource like BOL 2005 that lists these?
All of the new T-SQL syntax changes in 2005.
CTEs (WITH ...)
Cross Apply
Top in insert/update/delete statements
Some query hints (with recompile, with optimise for)
CLR objects
XML enhancements (FOR XML AUTO, TYPE)
Try..catch error handling
The ranking functions
DDL triggers
The Output clause
Pivot
etc
Basically, if it's new syntax in SQL 2005, it won't work in compat mode 80.
Look under 'sp_dbcmptlevel' in SQL 2005 books online.
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
September 22, 2008 at 10:27 am
YSLGuru (9/22/2008)
1) Are there some features or T-SQL syntax that could be used on/against the DB on SQL 2000 that can't be now that it is on SQL 2005 even though the compatability level is set to 8? If the answer is yes then does BOL or any other resource list all of these?
I don't know of a central reference for these. The query optimizer is fairly different (meaning - some things don't get optimized the same way as they used to).
As far as things flat out not working - the "deprecated features" article in BOL is a good place to start (although not comprehensive IMO).
2) Are there features and or changes in T-SQL as of SQL 2005 that are not applicable to use against a database on SQL 2005 with a compatability level of 8? I'm sure the answer is yes so the next question would be is there any single resource like BOL 2005 that lists these?
No central place to go.
That being said, form the top of the old peabrain, the following won't work in compat mode 80:
- CTE's
- anything CLR (functions, stored procs, aggregates, data types). You can't build them or call them within that database.
- the new OUTPUT clause on DML statements
- the new DDL triggers
- the Windowed functions (COUNT() OVER....)
- MERGE, INTERCEPT, EXCEPT statements
- the INCLUDE clause on indexes.
- the "MAX" character types (varchar(MAX) or nvarchar(MAX)).
- certain user-defined functions won't work (the rules about what is allowed in a UDF changed somewhat).
I'm sure there are others, but that's my running start.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
September 23, 2008 at 6:54 am
GilaMonster (9/22/2008)
GSquared (9/22/2008)
Once you switch to 90, you can't switch back to 80, except by restoring from a backup taken before the switch. (Which means, of course, that you'd lose any data or schema changes made after that backup.)The change of compatibility mode can be made in any direction at any time. You can take a 2000 db, attach it to SQL 2005, change the compat mode to 90 and then down to 70 if you like.
I have a DB created on SQL 2008 that's running in compat mode 80 right now (down from 100)
It's the upgrade of database version (done upon first attaching/restoring to a higher version instance) that can't be downgraded.
I thought I had read that you couldn't change it back, but I guess I was wrong about that. What you can't do is re-attach to a lower level server, like Gail said.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply