SQL 2000 db on SQL 2005 in compatibility mode 80

  • I have new servers coming and I'd like to not install SQL 2000 but put all of them on a SQL 2005 box in compatibility mode 80. Will this work 100% of the time or is there a chance that some existing code will not work. If I do this I will not have a fallback position and need to know for an absolute fact beforehand.

    Thanks!

  • With 90% certainty, no you cannot be garanteed that it'll work without retesting the whole thing (or getting approval letters from the vendors).

    Another question here... why not go to sql 2008 R2 since you're already going through all the trouble of migrating?

  • I am going to SQL 2008 but not R2. I am putting together an Active-Active-Passive cluster and if I could have gotten 100% certainty I could have done 2005 on one node and 2008 on another but your answer is what I thought. I had read on another forum where a Microsoft MVP said otherwise but I was skeptical.

    'nix

  • One fallback position could be to take backups when they are still on the 2000 server, just before restoring to 2005. I assume you will do this anyway. In an emergency, you could install a 2000 instance on your new servers, and restore your 2000 backups to that instance.

  • Correct, but I have to allocate a set number of SQL Licenses and I would have used the remaining Licenses on 2005. If only some of them worked and I had to have a 2000 instance then i'd have no more licenses and my Bosses would not likely be pleased.

    'nix

  • another concern I have would be that putting the 2000 database on a 2005 instance and some time passing before it was apparent that I had a problem and had to put it back on a 2000 instance by restoring my backup. Any information entered in that time would be lost.

    'nix

  • In ur case sure, you will get compatibility level issues, if you want to use 200DB in 2005, u have to change from 80 to 90

  • hemadribabu (11/11/2010)


    In ur case sure, you will get compatibility level issues, if you want to use 200DB in 2005, u have to change from 80 to 90

    no you don't

  • No, you can absolutely run in 80 compatibility mode on a 2005 box. I've done that many times and never had an issue. I'm pretty sure you can do it on a 2008 box as well but have not actually done that and dont know if it is as compatible as running on a 2005 box in 80 compatibility mode. If you guys have experience in using an 2000 db on 2008 in 80 mode I'd love to hear about that too.

    'nix

  • There will be no issues till you run in older compatibility mode

    ex: Running in higher version box with no change of compatibility mode

    like 2008 box but db compt is 80 should work fine.

    [font="Tahoma"]
    --SQLFRNDZ[/url]
    [/font]

  • pavan_srirangam (11/12/2010)


    There will be no issues till you run in older compatibility mode

    ex: Running in higher version box with no change of compatibility mode

    like 2008 box but db compt is 80 should work fine.

    Should be or definitely 100% will be fine?

    I agree with #1 but I sure as hell don't with #2.

    One more thing to think about is that you seem to have a lot of databases, did you check to make sure you don't have anything running in compatibility mode 65 or 70?

  • with all that's mentioned here, this is why you test before migrating Live. Check with internal devs\vendors whether they support the RDBMS platform or not!

    One thing that can issues between legacy apps using SQL Server 2000 and switching to SQL Server 2005 is the new password complexity, in some cases code changes may be necessary!

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Great! I'm now leaning heavily toward putting all of my old 80comp databases on SQL2k8 in compatibility mode 80. Any that will not work in that way will either be retired or I'll have to stand up a 2k instance BUT after doing my research on clustering I really do not want to put any more than one version on each node nor do I want to do as I originally planned on doing and running multiple instances on each node of a given SQL version as each instance will need it's own disk resources and I'll have to set Min/Max server memory which will get increasingly difficult as I add instances and versions of SQL.

    I'll have to do the work of managing permissions granularly to limit the ability of each login to access only the database resources that it needs. Not THAT hard to do but I'm the only DBA for a very large local/state govt organization and we are doing more with less. I have to keep it as manageable as possible. We will have all of our SQL eggs in one clustered basket.

    It's going to be FUN! Which one of us wouldn't love to get three brand new servers to build an Active/Active/Passive cluster and a nice shiny new SAN to run it from? I love the smell of a brand new CPU breaking in in the morning. I'll also have some other boxes to re-task for Mirroring and replicating all databases to for reporting purposes.

    I know I'm going off topic now but what is the BEST architecture for creating a copy of all databases to another machine for reporting? I need as close to real-time reporting as possible. I think replicating will be best but I'd like the experts' opinion/divergent ideas.

    Thanks to everyone posting in this thread! I hope to have excellent research to build more confidence in my design. The hardware will probably be in the office this week.

    Busy holiday season for me and the Systems Team!

    'nix

    'nix

  • phoenixrising1599 (11/14/2010)


    I'll have to stand up a 2k instance

    That's exactly what I do, any that just can't be moved for whatever reason, I consolidate to a centralised instance. Make 2 instances available, one at SP3a and one at SP4 (unless of course you want to force SP4).

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • Hell, I dont want to support SQL2k at all much less different SPs! Force SP4 definitely. What could go wrong? 😀

    I love when management wants an absolute answer when dealing with this stuff. There are over 250 databases in this organization and I can promise them this: Everything will not go PERFECTLY smooth and be done by this Tuesday! LOL but it will get done and the end-users will be able to access their data. Its like making Sausage, they REALLY dont want to see all of the details.

    When DBAs speak, Managers eyes glaze over. I dont think they understand 1/3rd of what I say.

    Funny Story: New administration takes over and we meet the new boss. Going around the room introducing who we are and our titles the new guy asks, "What is a DBA" :w00t:

    he knows now. The very next day someone entered a bunch of erroneous information that was publicized on the web. I was gone for the day, a reporter is calling about it and he found out quickly that there was no one else that could fix it...He knows my name, address, cell number, home number, my wife's name and her cell number now! Hahahaha hell, he knows my kids names too.

    'nix

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply