Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: changing compatability from 65 to 80

    Finally, I may have a good enough excuse to justify changing the compatabilty level of one of the biggies I look after!

    Before I go ahead with the change I just wanted to run a couple of questions passed the community.
    1. I know this is very vague, but what sort of problems should I expect? I know there are a few syntax changes, but I don't imagine I will have a problem going up in compatability level!
    2. What is the best method to perform this?
      A very simple
      Code:
      EXEC sp_dbcmptlevel 'playdb', 80
      Or
      Code:
      ALTER DATABASE SET SINGLE_USER
      EXEC sp_dbcmptlevel 'playdb', 80
      ALTER DATABASE SET MULTI_USER
    3. How long does this process take; are we talking seconds, minutes, hours? I imagine it's seconds, but I pose this question because a colleague reckons it will take longer because of the database size. I argue that because it's a db configuration setting, it will make no difference...

    Thanks a bunch,
    George
    Last edited by gvee; 04-21-08 at 10:47. Reason: spelling mistrakes
    George
    Home | Blog

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Just to clarify, you have a 6.5 database sitting in a 2000 instance?

    I helped move a shop from SQL 7 to 2000 but that was years ago and I have a hard time remembering 3 days ago.

    I am thinking you may want to change the compatibility to 70, fix everything that breaks and make sure your app still works, and then change it to 80 and repeat, but I am just a nervous nilly.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Cheers Sean,
    Yes, 6.5 on 2000; the only good thing about supporting that old lump is that I can put "have worked with SQL Server from version 6.5" on my CV

    I might just change the compatibility, run the processes I need to and change it back - all done out of hours to minimise disruption.

    *shrug* any other ideas/opinions?
    George
    Home | Blog

  4. #4
    Join Date
    May 2004
    Location
    Seattle
    Posts
    1,313
    I have no experience with 6.5 or 7.0. so it qualifies me to attempt an answer right?

    1. script out all objects from the 6.5 db to files.
    2. bcp out all tables.
    3. run the 6.5 scripts against the 2000 instance. should work since things are [wrong]forward[/wrong] backward compatable?
    4. bcp data in. here you'll have to pay attention to order if there are FKs.
    Last edited by jezemine; 04-21-08 at 11:58.

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    well you are going to need to take in account time to rebuild indexes and update statistics because I imagine those will got shot to poop (rated G) in all likelihood.

    do you have a QA team? Are your applications setup to log\ or notify you of crashes? If yes to both, my inclination would be to do it, throw it at QA for testing and fix all of the crash notifcations you get.

    Another thing I have been doing lately, and I keep all of my code in SQL Server objects, is to have a test script (or unit test like thingy) for all of objects as part of my testing\deployment strategy, because you know, just because it compiles does not mean that it works. I use an osql script batch file that cranks out a happy little log.

    If you are that organized, you could change compaibility, run your unit tests and see what breaks or sends back weird results.
    Last edited by Thrasymachus; 04-21-08 at 11:13.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I am just as highly qualified as Jesse on this

    Quote Originally Posted by Thrasymachus
    well you are going to need to take in account time to rebuild indexes and update statistics because I imagine those will got shot to poop (rated G) in all likelihood.
    Now - since this is a 6.5 db on a 2k instance, the shooting to pooping would have happened on the initial restore. Hopefully it was sorted out then. The change of compatibility level won't change the contents of the system tables nor the statistics SQL Server keeps - right?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    I am not sure to tell you the truth. I do not have a 6.5 database laying around. I just remember this being on a post upgrade checklist.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Some of the more painful changes that I remember in going from 6.5 to 2000 were treatment of nulls changed subtly. If you have this in your code anywhere;
    Code:
    something = NULL
    It will break in SQL 2000. 6.5 allowed a thing to equal NULL. 2000 requires you to specify IS NULL or IS NOT NULL.

    Also, there was the old outer join syntax '*=". If you have those little gems floating about in the code, get to fixing them.

    Since you are already on a SQL 2000 server, there should be no moving of data and objects, which was one of the more painful points in my experience. Lots of stopping and restarting of services that required me to do this kind of thing over a weekend.

    As always, you have a test server, right?

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I agree with Poots logic on this one; all the crap that comes with upgrading happened when it was initially moved on to the 2000 instance; I don't predict this being a big issue; I think I'll just go ahead and steal a backup and restore it locally to give it a try.

    Cheers peoples
    George
    Home | Blog

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes - upgrading from a 2k to a 2k5 instance requires the same. What I mean is the instance is not changing as it would in an upgrade (just the compatibility), therefore the system tables are not changing either.


    TBH it has been a while since I played around with compatibility modes. I am fairly confident it is instantaneous and only affects code but I wouldn't stake George's career on it. Or maybe....

    EDIT - slow, slow fingers
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    I agree with Poots logic on this one
    <faints />
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Stopping and starting services is going to be an ache, but it will be worth it just for the TOP keyword!

    Well, I've supported this system for over a year and not stumbled across either of the syntax issues you mentioned MC; if testing goes well I might just stick it out there and pick up the odd bits of fall out later.
    George
    Home | Blog

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I look forward to the "Changing compatibility from 80 to 90" thread in 2017
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The stopping and starting of services only applied to the upgrade wizard, which you will not need here. You will issue the command, andf it will take less than a minute to complete.

    I suppose I should have kept some of my notes from that conversion, Poots.

  15. #15
    Join Date
    Feb 2004
    Posts
    88
    If you look up sp_dbcmptlevel in books online in SQL 2000 it gives you a pretty good summary of the differences in behaviours between 6.5 and 8.0 compatibility. having done a lot of conversion of old Sybase and SQL 6.5 code to SQL 2000 I can give the following advice on some unexpected problems you may have.

    1) Table aliases are not accepted in the SET clause of an UPDATE statement. This may mean you have to change a lot of code - its not difficult, but very boring.

    2) The different behaviours of many string handling functions. with 6.5 these will return NULL in some circumstances. with 8.0 they return an empty string. If you have any code that does ISNULL(LTRIM(...), ...) for example, it may behave differently.

    3) The default setting CONCAT_NULL_YIELDS_NULL changes with 8.0. beware of concatentaions that rely on the old behaviour...

    HTH,

    Bill

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •