How To Assess Impact Of Changing Database Compatibility Level
Aug 28, 2006
I noticed that a database I am working with has a compatibility level set to SQL Server 2000. The instance is actually SQL Server 2005. I'm guessing that it was created like this because the database originally existed on 2000 and was created via backup/restore.
I'm trying to figure out if this needs to be changed and if so how to go about making the change in a non-disruptive manner. What features of 2005 are turned off as a reult of having a 2000 compatibility level?
View 4 Replies
ADVERTISEMENT
Dec 18, 2002
I have a problem where I have a software application that
needed to be updated to a newer version. In order for the
new version to work I needed to upgrade my SQL Server 7
database to SQL Server 2000. The upgrade went fine for
SQL Server 2000 and also with the application. The only
problem now is that the compatibility level for the
databases stays at 70. Even when I change the
compatibility level to 80 it will automatically go back to
80. What is wrong? Please help.
View 3 Replies
View Related
Jun 12, 2008
Dear All,
what are the advantages of changing compatibility level from 80 to 90? are there any disadvantages doing this on production machine? will it take any downtime?
i've searched the google but i didnt get the correct info.
Arnav
Even you learn 1%, Learn it with 100% confidence.
View 2 Replies
View Related
Jun 27, 2014
I have just upgraded a test server from sql server 2008 sp3 to sql server 2014 inplace upgrade. The compatability level of master database has not upgraded. It was showing 90 and the rest of system databases got updated to 120. Is it fine to update the compatibility level of master database ? Any precautions need to taken??
View 1 Replies
View Related
Feb 20, 2001
Hi!
After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65".
I checked that by executing sp_dbcmptlevel <database_name>.
I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
Thank you
Lena
View 2 Replies
View Related
Jun 28, 2006
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
View 3 Replies
View Related
Aug 4, 2015
We have a reasonably large (several TB) database that was recently migrated from 2008 to a new box running 2014. Before giving it back to the users we forgot to change the compatibility level of the DB to bring it up to date with it's new environment.
We want to do some testing with backup compression so want to change the compatibility level, but we are unsure whether making the change on such a large database would cause slowness or downtime for our users.
Does the process of changing the compatibility level simply allow options that are not available in the older version or does it make structural changes to the database that would cause the users to notice slowness or downtime?
View 7 Replies
View Related
Jul 13, 2007
I installed SQL Server 2005 Developer Edition. When i create a new database (using the "New Database" dialog) i cannot set the new database's compatibility level to "SQL Server 2005(90)" because this option is not in the "dropdown list". the only items shown are: "SQL Server 7.0(70)" and "SQL Server 2000(80)". I set the owner to "sa". How do i get "SQL Server 2005(90)" in my "compatibility level" drop down list? Is this an installation option that i missed? Thanks in advance for any assistance!
-chris
View 12 Replies
View Related
Jul 3, 2001
Hi All,
I want to know what will be the impact of changing the primarykey on a table which already has a lot of data.
For example, column A is unique, primary key. I want to make column B as unique, primary key.
Can I do that? What will be the impact on database performance?
Thanks
Sri
View 1 Replies
View Related
Oct 24, 2007
Hi Gurus,
I would like to know if I put the Compatibility Level in a SQL Server 2005 installation to 70 I can make afirmation that I have a full SQL Server 7.0. If the answer is "Yes" where I can find a documentation or a FAQ that explained this topic.
View 2 Replies
View Related
Dec 7, 2007
Has anyone changed compatibility level from 80 to 90? Did you have any problems?
View 8 Replies
View Related
Feb 3, 2008
I restored the database from SQL server 2000 to 2005.The database was restored with 80 compatibility.Can i change it to 90 and what are the effects? Coz my applications are pulling data from SQL server 2000. Does 80 work for database mirroring?
View 5 Replies
View Related
Feb 13, 2008
Hi there
We found interesting issue which is basically the app is being tested ok on SQL2005 by software vendor. Then we tested in our environment and we found it's not truly true. There are some compatibility issue on SQL syntax. Anyway ... the plan set the compatibility level back to 80 instead 90. Cause this thing for sure is working.
Now my question is do you know any other impacts that you know of if we are doing this setting (running SQLServer 2005 but the database set as 80)? I know that some inbuilt reporting only run 90 level but I can get around this. Performance or something? Is there any thing that I should to know?
Thanks
View 1 Replies
View Related
Jul 26, 2006
Having moved over to SQL 2k5, from SQL 7.0 we have now realised that the database's need to be set to comp level 9.0 before they are found in the maintence plan wizard, we currently still access the database using an Access 2000 front end, by changing the comp level will this cause us issues writing data, I'm sure it won't but want to make sure, I'm sure that the comp level just sets what options are available to use.
Thanks
View 1 Replies
View Related
Nov 27, 2001
We upgraded from SQL 6.5 to 2000 (.384) and just look at this T-SQL and maybe somebody can tell me what's wrong.
sp_dbcmptlevel appetserv1,80
update articleentrepot set usagermodification = 'MANON' where idarticle = 1
Here is the result:
Cannot use the column prefix 'E'. This must match the object in the UPDATE clause 'ArticleEntrepot'.
When I put the compatibility level to 65, it's works. But my problem is that I need to have the compatibility level to 80 because of the DataMirror replication software and if I do that, my house application doesn't work. I really don't know what to do, I'm in a deadlock. Can you help me!
Manon Tremblay
View 2 Replies
View Related
Mar 23, 1999
I installed SQL 7.0 on a test server a few days ago. I want to test an application that currently runs on SQL 6.5. I have run a test database through the upgrade wizard without errors. Is there any way to see the compatibility level that the db is running on. I know I can use the sp_dbcmptlevel procedure to make it run at the 6.5 level. I want to see if it is running at that level currently, or if it is at the 7.0 level.
Any help would be appreciated.
View 1 Replies
View Related
May 28, 2008
I installed SQL 2K5 (not upgrade from 2K) and just found that Master DB comp. level is 80. All other system DBs are 90.
1. How can I change master's to 90? Does the following work?
EXEC SP_DBCMPTLEVEL Master, 90;
go
2. Changing this, is there any possibility to impact my production?
Canada DBA
View 5 Replies
View Related
Mar 5, 2015
Problem: I want to set compatibility_level only when it is greater than 110.
Solution: Select the compatibility level and if it is greater than 110, I alter database set compatibility level=110
ISSUE Irrespective of IF Exist statement the alter database statement is executed all the time.
Here is the sql statement
IF EXISTS (
SELECT * FROM sys.databases where compatibility_level >110 AND name='mydatabase'
)
BEGIN
ALTER DATABASE mydatabase SET COMPATIBILITY_LEVEL = 110
END
What is that I am missing here ?
View 1 Replies
View Related
Jan 19, 2007
Does anyone know why Master is still at Compatibility Level 80 after an upgrade from SQL 2000 to SQL 2005?
View 8 Replies
View Related
Jun 26, 2007
Does anyone have any insight as to whether the new data encryption features of SQL Server 2005 work when the database is set to compatibility level 80?
View 1 Replies
View Related
Aug 23, 2006
I have a SQL 2005 server that has more than 90 databases, and I need to change the compatibality level to 9.0. Is there an easy way to do this in SQL?? any help is greatly appreciated.
Emad
View 1 Replies
View Related
May 22, 2007
I'd heard that upgrading SQL 05 to SP2 forced any databases at SQL 2000 compatibility (80) to SQL 2005 compatibility, but I can't find any documentation that says one way or the other.
Can anyone provide insight as to whether or not this is true?
Thanks,
Lenny
View 1 Replies
View Related
Oct 11, 2000
I can set the compatibility level by using :sp_dbcmptlevel
But before doing this - How can i view my current DB's compatibility level?
Any help appreciated
View 1 Replies
View Related
Jun 14, 2000
Hi All,
We were upgraded our databases from 6.5 to 7.0 .After upgrading
the Compatibility level was 65 .We changed to 70 for all databases using sp_dbcmptlevel system stored proc.We were having some problems with Ansi nulls if the compatablity level is 70,We changed back to 65 that works fine--OK with ANSI NULLS.While Upgading ANSI NULLS OFF after upgrading ANSI NULLS OFF.But from the front end side ODBC settings ANSI NULLS are ON.Is that some thing to do with compatablity level or with ANSI NULLS .
If you guys have any great ideas Please let me know.
Thanks A Lot,
VJ
View 1 Replies
View Related
Jan 6, 2006
Hallo Everyone,
I have an SQL database that I need to detach from an SQL2005 server and reattach to an SQL 2000 database. I tried to set the Compatibility level from SQL Server 2005 (90) to SQL Server 2000 (80). This did not work
Any ideas?
Nigel...
View 12 Replies
View Related
Jun 19, 2007
This may be very obvious and when I get to the first test server I will find out, but I only have a new installation of SQL2K5 so far to test with. I have several databases that need to be upgraded to run on 2K5, but the application requires them to run in 8.0 compatibility. Does this mean that an in-place upgrade is not an option?
I apologize if this is a silly question, but I have not yet found the answer specifically.
Thanks very much.
View 4 Replies
View Related
Nov 30, 2007
Hi All,
We have upgraded our database from SQL 2000 to SQL 2005 keeping the compatibility level 80.
Now the question is -
Would microsoft support compatibility level 80 in SQL Server 2005 even after the extended support date for SQL Server 2000?
OR
If Microsoft dropped the support for SQL 2000, then would Microsoft support compatibility level 80 in SQL Server 2005?
Thanks
View 5 Replies
View Related
Jan 5, 2001
I'm running SQL Server 7.0. I have a DB running with 6.5 compatibility mode.
Do INSERT, UPDATE or DELETE queries use row level locking in this DB ?
(I know if I set the db compatibility mode to 7.0 row level locking will be enabled)
Thanks in advance for your help.
View 1 Replies
View Related
Aug 6, 2007
Hi everybody:
We have recently migrated our DDBB from SQL 2000 to SQL 2005 in several Servers. We have 2 DDBB per Server and the size of mdf files are between 10 and 40 GB.
We put Compatibility Level in 90 in SQL 2005 but when we arrive at work we see that our Maintenace Plans failed because the Compatibility Level of one of Data Bases changed to 70.
We have a Trace executing the whole day registrying the execution of stored procedure 'sp_dbcmptlevel' but in despite of Compatibility Level changes, the Trace does not registry anything.
Has anyone passed before me for this situation? Thank you in advance and greetings,
Nuria
View 4 Replies
View Related
Oct 23, 2007
Hello All,
I am in the process of upgrading my current database in SQL Server 2000 to SQL Server 2005. I have finished my analysis using the upgrade advisor and have found a lot of SPs having upgrade related issues. From what I estimate I think it will take around 3-4 weeks to resolve all of those issues. I wanted a quicker way to upgrade my database since we have very little time for the final release. The main reason for upgrade to 2005 was use of new features like Database Mirroring and/or peer to peer replication alongwith the Partitioning features and the new T-SQL enhancements.
The question I wanted to ask was does the compatibility level 80 of SQL Server 2005 supports these new features.
From what I have read on the KB article at http://support.microsoft.com/kb/822400 is that mirroring is supported in any compatibility level, but there is no specific mention on the peer to peer thing.
Also when I personally tried using the New Partitioning Features of 2005 on the database having compatibility 80, it worked fine. Also other features like SQLCLR and TRY...CATCH works fine.
One thing I observed was that new T-SQL statements like PIVOT/UNPIVOT is not supported
It will be nice if someone can give out the entire list of features that will be supported in the compatibility Level 80 and also the list of the ones that are not supported.
Thanks in Advance,
Mitesh
View 4 Replies
View Related
Oct 11, 2007
Hi all
I cannot find this info anywhere. We have Hyperion 8.x running against SQL 2000. We will upgrade side-by-side in the coming weeks. It appears that Hyperion 8.x is assured to continue to work without issue with a 2K5 database in 80 compatibility level.
I have seen the BOL table on differences between 80 and 90 (specifically affects us - WITH on Index hints), but what I wonder are about 3 specific features.
1. Do you still get DMV capabilities in 80 mode? (I ran a test on sys.dm_db_index_usage_stats for an 80 compat DB, creating index and using query to hit index, and yes - it does look as if this DMV does get populated).
2. Can you still Table Partition in 80 mode? (Ran a test in DEV, and yes - this does look possible - any gotchas?)
3. Does the optimizer behave quite differently in 80 mode?
I have tested in DEV moving a DB from 80 to 90 and back, and this seems to work with no problems.
TIA from Grand Fenwick!! ;-)
View 1 Replies
View Related
Jul 20, 2015
I have a 2005 database sitting on a 2012 server, we're looking to change its compatibility mode from 90 to 110 so we can avail of what 2012 offers.I did a migration project a couple years ago for SQL Server 2000 databases changing to 2008 R2 and we ran into loads of problems where we left most databases in compatibility mode 80 as these were application databases that the owners couldn't stand over in terms of deprecated code / features no longer in use in 2008 R2.
From what I can see with changing from 90 to 110, there doesn't seem to be as many issues but I just want to double check if there's a way to confirm this. I know upgrade advisor is a handy tool but will it pick up database specific issues as opposed to database server compatibility issues? URLs...I know UA won't cover all the bases but it would look good when attached to the report I'm submitting recommending the change.
View 7 Replies
View Related
Feb 21, 2014
I have created a Transactional Replication Publication on my SQL 2012 server.When I log into another server on the domain running 2008R2 and try to subscribe to the 2012 Publication, I get the following error when clicking on "Add SQL Server Subscriber": "The selected Subscriber does not satisfy the minimum version compatibility level of the selected publication"
The 2012 DB is set as 2008 Compatibility Mode?Am I not able to Publish from 2012 to 2008?.I was using SSMS 2008 to connect to my 2012 Instance, thats why it didn't work...
View 0 Replies
View Related
