Upgrading SQL7 to SQL2000
-
Hi everyone, I am going to upgrade a SQL7 box to SQL2000 today and wanted to know if there any issues that I should watch out for, based on your experience. Thank you!
-
Haven't done it in awhile, but should be pretty smooth. Course I've got to do one next week, so if anything happens, post it here.
Steve Jones
-
I defintely will. Thank you.
-
Beside prepare according readme.txt, remember to stop DTC, SNMP, IIS services. Any open connection could lead to failure. It happenned twice to me. Other 3 upgrades were fine. I heard a folk even unplugged the network cable.
Edited by - hthan on 09/09/2003 11:43:00 AM
-
I have heard people complain about query/stored procedure behave slowness after the upgrade. You may perform a test upgrade to ensure application runs fine.
-
Thank you for your input everyone. I will be doing the upgrade after-hours pacific time. If I see anything interesting worth sharing, I will post it here. Thank you again.
-
Thank you for your input everyone. I will be doing the upgrade after-hours pacific time. If I see anything interesting worth sharing, I will post it here. Thank you again.
-
The only thing I remember I had a couple of sp's which did not work on 2000. This was where the developer had coded 'int(4)' into there declare statements. Changing them to 'int' got them working fine.
Steven
-
Ya, there are few constraints. You need to set the Quote Identifier on, by default it is off, which means that if u have an values within double quotes in your queries and SPs etc, then it will not execute.
-
I had same problem with double quotes in queries as described above. Also, a couple of sp's failed due to comparison of datetime values of different length.
The field was datetime type but variable - small datetime. The value was the same. In SQL 7.0 the comparison was evaluated to true, in 2000 to false.
Another comparison issue I had with varchar field and int variable. The majority of values in this field was numeric. So code looked like that: SELECT * FROM <TABLE> WHERE <FIELD>=1. SQL 7.0 returned a record set but SQL 2000 gave error not being able to convert character values in this field to numeric. It's not a good practice to query this way but sometimes we have to maintain what we have. Just another scenario...
-
Hi All,
We presently have a call open with Microsoft regarding a degradation in performance between SQL Server 7.0 and SQL Server 2000. While we have not completely identified the problem to everyones satisfaction the following are new guidelines for coding.
If variables are being declared locally within a SP one should avoid using them in any where clauses. The query optimiser builds its execution plan based on the parameters passed to the SP on the first time of execution (unless recompile is set or the SP is recreated). The optimiser has no knowledge of any internal variables within the SP and therefore if they are used within the SP the optimiser cannot handle them. Apparently SQL 2000 is worse than SQL 7 for handling these situations.
Local variables should not be defined as numeric. Please use tinyint or int instead. Change decimal to tinyint or int if there is no scale specified. This also applied to the database. These changes can be made without any recoding or software, but testing should still be done especially where decimal is changed. Again SQL 7 appears to be better handling these issues.
Apparently SQL Server 2000 is quite fast but you have to do things by the book to achieve this performance otherwise performance may be compromised. I will keep you posted on further developments.
-
Just wanted to say that the upgrade was done seamlessly. Everything seems to be working. Thanks for all of your inputs everyone.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply