Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
 Posted - 2008-03-27 : 11:21:45
|
| Yesterday I started this topic and I came step by step higher and now I have finalized my steps to migrate.Seniors and Gurus and who has done this before, please review and correct me where I am wrong and/or add some tips for me to have this one going on successfully! Thanking all of you guys in advance! Here is what I have:Before Migrating:1. Install Service Packs, Security Patches and Hot Fixes 2. Install DTS Designer Tool from Microsoft to prevent conflict and failure of migrating current DTS packages to SQL Server 20053. Backup EACH and EVERY Database and double check the .bak files whether it exist and backup was successful4. Just before migrating, reboot the server to clarify that the SQL Server Services and SQL Server Agent is workingWhen you are ready to migrate: 5. Make sure there are no users trying to connect to the OLD AND the NEW SQL Server. Inform them well in advance about the downtime. Shutdown any applications or websites that might be accessing the SQL Server. 6. Stop the SQL Server, SQL Agent and MS Search Services on the NEW SQL ServerIf downtime is not possible:7. For ever changing OLTP database, by the time you restore the backup onto the new server, the old server would have received more changes. To avoid this problem, you need to resort to log shipping. Log shipping is nothing but an automated process of backup and restore. The following steps will explain the process of log shipping (Note that SQL Server 2000 Enterprise Edition provides inbuilt logs hipping. We don't need that for this purpose. We can simply create a job that backs up and restores the databases8. Create a job on the old server, with two steps. The first steps backs up the transaction log of the OLTP database. The second step restores this transaction log backup file onto the new server, using WITH STANDBY or WITH NORECOVERY option of the RESTORE command. Schedule this job to run every 10 minutes or 15 minutes, depending on the volume of transactions your OLTP database receives9. This job will take care of shipping all new transactions since the full database backup, to the database on the new server. Let this job run until you can afford some downtime on your production database (that is, mid-night, or early morning, or a planned maintenance window)10. Now to be doubly sure, no new transactions are coming in, set the database in 'single user' or 'dbo use only' mode. Take one last transaction log backup of the production OLTP database, and restore it onto the database on new server. This time use the WITH RECOVERY option of the RESTORE command11. Run UPDATE_STATISTIC on User Database to reorganize all indexesConsiderations:12. 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.13. Unless you have hundreds of them, it is always better to 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. Fortunate in this, we can script out the jobs from SQL Server 2000 and run it on the new SQL Server 200514. Typically you will have to re-create your replication too. Again, 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 publications15. For DTS packages, it is always recommended to use the Package Migration wizard to do this so that less or no problems will be encountered. Consider about the jobs previously set.16. Logins can be created by using sp_hexadecimal and sp_revlogin2005 |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-27 : 11:24:41
|
Write an article and post to Graz.
E 12°55'05.25"N 56°04'39.16" |
 |
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-27 : 11:33:42
|
Err...What is Graz? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2008-03-27 : 11:58:46
|
Graz is the founder of this site.He is always looking for articles. Write a nicelooking, indepth explanation of how to migrate well.Who knows? He might even pay you $25 for the article?
E 12°55'05.25"N 56°04'39.16" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-27 : 13:41:28
|
You have mentioned so many steps which are not required. |
|
|
|
LOOKUP_BI
Constraint Violating Yak Guru
295 Posts |
Posted - 2008-03-27 : 14:23:48
|
| Sodeep, I have been reading a lot of posts from you lately. Looks like you are really senior in this matter. Bravo!Please help me by pointing out which are the NOT required points. Maybe I can eliminate them out. Your help is much appreciated! |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2008-03-27 : 19:42:58
|
| Here are steps we do for Migrating:Before Production:1)Install SQL server ,security patches and services packs in SQL 2005(64-bit).It will give you whole Operating system memory.2)Migrate all DTS packages to SQL 2005(64-bit) with DTS migration wizard. Migrate all databases which are associated with DTS packages and Change connection strings and redevelop in Visual studio in 64-bit server. Make sure all packages are working fine in SQL server 64-bit server.If you have problems,let me know.After all packages are working fine,Delete databases which you have migrated.Ready for migration/Production:1) Backup all databases in SQL 2000 and copy all files to New server.2) Restore all databases in SQL 2005(64-bit) in NORECOVERY option3)You have to notify all clients of downtime while migration(Depends on bandwidth )4) Put your database in Readonly/Single-user mode and take final transactional backup of all databases in SQL 20005) Restore all tran backups to 64-bit server With Recovery option6) Migrate all logins to new server with :http://support.microsoft.com/kb/246133(Make sure choose : SQL 2000 to 2005). Migrate linked servers as well.7)Migrate all jobs to new server with Transfer logins task in Visual studio.Recreate all operators which are used in jobs.8)Stop SQL services and other services in old server9)Rename your new server to old server name (so apps can point to new server with same i.p address.)10) Reboot the new server.11)Create jobs for all the packages which you tested successfully in 64-bit server12)Change compatibility of all databases to 90( but there are some issues with it.Check BOL)13) SQL mail(xp_sendmail) doesn't work in 64-bit server.Database mail(sp_db_dbmail)is only option.Make sure you configure so that you get emails.14)check BOL for migrating reporting services if you have any. |
|
|
|
|
|
|
|
|
Migrating from 32 bit (2000) to 64 bit (2005)