March 26, 2008 at 8:25 am
Guys! Very very junior in this migration part. Hope the seniors can help out....PLEASE!!!!!! Don't get me wrong asking you guys to spoon feed me. I have read a lot of articles and even BOL and also not to forget read millions of books on this. But just wanted to make sure that I am in the right path and want to do this without any problem. That's why I have decided to ask the Gurus here who have done this in past.
I have this machine now:
Microsoft SQL Server 2000 - 8.00.2040 (Intel X86)
Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: )
Migrating to this machine:
Microsoft SQL Server 2005 - (X64)
Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
I have these in the SQL Server 2000 box:
1. Databases, tables (of course)
2. Views
3. Securities(Logins, Roles, Password)
4. DTS packages
5. Jobs
6. Replications
7. Linked Servers
8. Stored Procedures
9. Schemas(Very important)
Every article is different. I was confused at a point of time. Please show me the right way guys!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 26, 2008 at 11:09 am
1) Databases / tables - you should have no issues. Everything from 2000 is supported in 2005
2) Views - assuming you did not use some really old query syntax, you should be ok here also
3) Security - The security model changed along with object ownership, but users that own objects will be turned into schemas and all of the object and role permissions will come over. These you will want to look over afterwards, but they should be ok.
4) DTS packages - These don't "upgrade" well at all. If they are stored in meta data services, you have to move them out of meta data services before you upgrade or they will be lost completely. If you install the compatibility packs on the 2005 server, what will not upgrade will be turned into an execute DTS package component. I think the service pack on your 2000 server will be compatible with the 2005 compatibility packs, but test this all thoroughly.
5) Jobs - these will probably upgrade if you run an actual upgrade, but unless you have hundreds of them, I would suggest you re-create them after upgrading. You cannot detach an msdb database on a 2000 server and attach it on a 2005 server and have it work. So if you want to copy jobs, you have to actually do an in-place server upgrade which I would not recommend in most cases.
6) Replication - again, if you are doing an in-place upgrade, this "should" all translate, but I don't usually recommend an inplace upgrade. Typically you will have to re-create your replication. The good news is that you can generate the scripts for replication on a 2000 server and execute them on a 2005 server and it will create the appropriate articles and publications. If you end up replicating in a 2000 / 2005 mixed environment, there are some things to watch out for - search the forums for some tips.
7) Linked servers - these all still work ok. Again, unless doing an in-place upgrade you will have to re-create them. Make sure you have appropriate drivers for anything you link to. 64 bit OLEDB drivers may not exist for all data sources (old versions of Oracle, for instance).
8) Stored Procedures - there is an upgrade advisor for SQL 2005 that does a pretty good job of checking for bad syntax. Almost everything will work in compatibility mode. There are a few things to watch out for (assuming data will be returned in the order of a clustered index is one that comes to mind). If you set your database to compatibility 90, you may have some syntax issues, but the upgrade advisor can tell you most of them.
9) Schemas - since these were not supported in 2000, you should not have any of them. Any user that owns an object will be turned into a schema.
Every situation is different, you will have to do some testing to make sure everything will work correctly.
March 26, 2008 at 12:34 pm
Millions of thanks to you Micheal. But I am also wondering what will happen if something goes wrong? How and what are the practices I should take to roll back?
I know....:doze: I have to re-create ALL the Replication and I am also planning to re-create the jobs. But how do I do this safely?I also came to know after reading hundreds of articles that DTS migration sucks!:crazy: So I seriously don't really know what else to do to at least have it over in SSIS. Changes or whatever it takes to fix those packages is not a matter right NOW! I can do it slowly over time......
Also, what do you recommend using? Back up/ Restore or Copy Database Wizard for the regular things? Which one would do the job good for me. ( I am not lazy, I just want to do it the right way)
Also, is there a (Perfect) STEP by STEP migration article available around? (I have been looking and got confused of some of them) My problem is it is from a 32 bit (2000)to a 64 bit(2005), will that create any problem for me as well?
Please help pushing me a little bit more!!!
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
March 26, 2008 at 12:48 pm
Sorry to say that every migration is different. Even if you had a nice step-by-step example of a migration, it is going to be different from your environment and not a guide for what you need to do.
Plan it out and take it one piece at a time. Your first important step will be to set up your test envrionment so you can try a few options. Try the upgrade advisor. Try simply upgrading an instance of your entire server (in test obviously). Try copying your databases to another instance and re-wiring everything. See what happens when you upgrade your DTS packages.
I would like to tell you someone has already done the work for you, but that is simply not the case. You need to do some testing on your environment to know how to handle this correctly. If you are unsure, bring in a consultant that has done this before - experience will be helpful on this project.
All I can tell you is that depending on the situation, any of the possible upgrade paths may work.
March 26, 2008 at 1:02 pm
Thanks again. I will deeply look into what you have stated and do some thinking here. At the same time, I do respect your views. I will try my best to finish this.
Others, can still reply if you have something in mind and/or to warn me about anything.
[font="Verdana"]Imagination is more important than knowledge-Albert Einstein[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply