November 11, 2010 at 1:50 pm
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!
November 11, 2010 at 2:14 pm
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?
November 11, 2010 at 2:48 pm
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
November 11, 2010 at 2:49 pm
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.
November 11, 2010 at 3:01 pm
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
November 11, 2010 at 7:05 pm
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
November 11, 2010 at 11:18 pm
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
November 12, 2010 at 5:50 am
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
November 12, 2010 at 8:09 am
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
November 12, 2010 at 3:33 pm
November 13, 2010 at 7:12 am
pavan_srirangam (11/12/2010)
There will be no issues till you run in older compatibility modeex: 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?
November 13, 2010 at 7:15 am
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" 😉
November 14, 2010 at 2:50 pm
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
November 14, 2010 at 3:06 pm
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" 😉
November 14, 2010 at 3:55 pm
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