July 2, 2009 at 6:49 am
Ok,
I am at a new company that has their entire transactional system on SQL 2000, the online database is also replicated to a couple of other 2k platforms for reporting purposes. I want to upgrade to 2008, I understand how to do this but am soliciting advice on best practices and things to watch out for from anybody who has been through this upgrade before. We do not have many DTS packages so that will make it a little easier. I am going to run the upgrade advisor today to see what kind of issues it comes up with. I am also collecting an inventory of stored procedures and in-line SQL to determine if many extended stored procs are used. If you guys can think of anything I am missing please feel free to advise.
Jeremy
July 2, 2009 at 7:15 am
Hi there,
It seems like you have considered important points that you need to while upgrading from SQL Server 2000 to SQL Server 2008.
However, i'd still share what i have figure out.
1. Run Upgrade Advisor and get all the recommendations to follow before upgrade and after upgrade.
2. DTS Packages: Its good thing that you dont have any DTS packages. DTS is depricated feature in SQL Server 2008 which means you can still use it in 2008 with backward compatibility but it wont be supported in the next version.
3. Database Mail setup: If you are using database mail featuer anywhere (e.g. stored procedure, trigger) the you may have to reconfigure DBMail and rewrite the code. Something that i had to do.
4. No way back: Once you have changed you db compatibility from sql 2000 to sql 2008 you will not be able to go back to previous compatibility modes.
5. Options: you do have couple of options here.
i) Install SQL 2008 on new machine and attach and detach database. (Easier Option)
ii) Upgrade your current server.
This is all i can think of. Please let me know if you have any more queries or need info into bit more detail.
Regards,
Vivek
Vivek Shukla - MCTS SQL Server 2008
July 2, 2009 at 7:22 am
Altogether I think that the major points are listed. I will address only one point then. You mentioned replication, replication is somewhat better in 2005 and 2008, where 2000 is substantially better than previous versions..
With all that said, I am not a fan of replication, too many bad experiences.. I would suggest you look at either log shipping or db mirroring..
CEWII
July 2, 2009 at 7:41 am
Hmm.... Interesting comments on log shipping and mirroring. I do not think mirroring would be ideal in this case as it requires a dual commit, if the reporting copy of the database is being banged on hard it could hold up the online systems and cause problems. I will look into log shipping though, might be a better solution. I inherited this sytem and it is really a mess so trying to get it to a better state right now.
July 2, 2009 at 10:28 am
I can't stress how much I don't like replication, I do however realize that it does have its place. It sounds like log shipping is a better fit of the two. How close to prod does reporting need to be, time wise? If it is too close then even log shipping may not work for you..
CEWII
July 2, 2009 at 12:29 pm
They actually want reporting to be immediate as the departments here run the business off reports. Any new work is distributed or obtained from a report. So delays are not going to work at all.
July 2, 2009 at 2:43 pm
Sounds like we are back to transactional replication again..
CEWII
July 3, 2009 at 1:55 am
humm... I do think transactional replication would be the best bet.
Vivek Shukla - MCTS SQL Server 2008
July 3, 2009 at 3:14 am
Hi
I have been running transaction replication for our sql server 2005 standard edition trouble free for almost 2yrs now and there have been only once a error was reported like distrib.exe error , i tried to find the cause but was not able to get the exact answer , the error came for 2 days and later it has never come up again.Thanks sandeep
July 6, 2009 at 9:03 am
Yeah, transactional replication is probably our only option until I get some real design time to re-engineer their systems. I am making a sandbox right now to test the ugrades in, I will let everybody know what kind of problems I encounter.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply