November 19, 2009 at 4:35 am
What is the easiest and quickest way to migrate a publisher instance to new hardware?
I am planning to:
1) Install SQL Server 2005 on the new hardware
2) Backup all the database on the older server
3) Shutdown old server
4) Restore all the user databases to the new server
5) Restore the msdb and master database to the new server
6) Rename the new server as old server
Is this OK? Is there any other better alternative? Will transactional replication continue without reinitialization?
Old server is 32bit and the new server is 64bit.
Thanks
November 19, 2009 at 7:03 am
OS changing to 64 bit or is OS and SQL?
you should get away with that as long as you install SQL EXACTLY as on the old server, version, service accounts, directory structure, everything.
the replication will only work if you pause it before hand and the server is renamed.
I would also be tempted here to copy over the actual database files and slide them into place after the SQL install rather than use restores.
Make sure you backup your service master key as well and have that available to you.
Expect the local accounts that SQL creates to be orphaned so be prepared to drop and recreate them, you will also have permissions problems on the SQL directories because of this unless the service account has admin rights.
If you have more than the database engine, i.e. also SSRS and SSAS I would prefer to go back to the traditional method of only copying user databases and scripting out system objects. SSIS is the only component that really benefits from this style of migration as it is so separated from the database engine yet still dependant on msdb (presuming thats where packages are stored).
If you do this do a dry run first and make sure the original server is intact to backout to!
---------------------------------------------------------------------
November 19, 2009 at 7:19 am
Thanks George for your quick reply.
Old server has 32 bit OS and SQL. New server has 64 bit OS and SQL.
Thanks for your help.
Regards,
Suresh
November 19, 2009 at 8:13 am
then there is the question of can you restore 32bit system database to 64 bit, that I am not 100% suer about................
---------------------------------------------------------------------
November 19, 2009 at 9:02 am
you can restore 32 bit databases to 64 bit servers. every day we have a process to copy data on our SAN to volumes that are used by a 32bit SQL server just to have a copy for the day before. the production databases are x64 OS and SQL.
every time we moved hardware we reinitialized replication. I detached subscriber databases a few times to move them to other disks and reattached them without running a new snapshot.
x64 hardware + gig-e ethernet = amazingly fast snapshot times. we have tables with 300 million rows where the snapshot takes 5 minutes to run and dumping to the subscriber will run in 60-90 minutes and another 60-90 minutes to create the indexes
November 19, 2009 at 9:17 am
If you are going with the same version of SQL then you might be ok. I will say that if you are going to use restore then you will need to use the KEEP_REPLICATION option with the restore. I will also say, make sure that you script out all the replication publications, subscriptions as there is a GREAT potential that something will go wrong.
As the previous poster stated, you can drop replication and recreate without initializing which honestly will probably be the safest way to go. I have just gone through several where we went 2005 - 2008 and the cleanest was the drop / recreate method. Tried the restore and we were successful but we ended up having a couple issues with metadata that required us to drop / recreate anyway.
Hope this helps a bit anyway.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
November 19, 2009 at 9:23 am
SQL Noob (11/19/2009)
you can restore 32 bit databases to 64 bit servers. every day we have a process to copy data on our SAN to volumes that are used by a 32bit SQL server just to have a copy for the day before. the production databases are x64 OS and SQL.every time we moved hardware we reinitialized replication. I detached subscriber databases a few times to move them to other disks and reattached them without running a new snapshot.
x64 hardware + gig-e ethernet = amazingly fast snapshot times. we have tables with 300 million rows where the snapshot takes 5 minutes to run and dumping to the subscriber will run in 60-90 minutes and another 60-90 minutes to create the indexes
system databases as well?
---------------------------------------------------------------------
November 19, 2009 at 9:37 am
i think you're safe with msdb, master i'm not 100% sure.
we went from 32 bit SQL 2005 to 64 bit a few years ago. all databases are on a SAN. we detached the databases, uninstalled SQL from old hardware. installed SQL on new cluster with the same name and IP as the old one. attached the databases.
we ended up creating jobs and logins manually but I think it would have worked by attaching msdb as long as the name is the same. with master there is a special process to do it and i've never done it
November 19, 2009 at 9:39 am
SQL Noob (11/19/2009)
i think you're safe with msdb, master i'm not 100% sure.we went from 32 bit SQL 2005 to 64 bit a few years ago. all databases are on a SAN. we detached the databases, uninstalled SQL from old hardware. installed SQL on new cluster with the same name and IP as the old one. attached the databases.
we ended up creating jobs and logins manually but I think it would have worked by attaching msdb as long as the name is the same. with master there is a special process to do it and i've never done it
Have to be the same version, same patch level, etc.
Best bet, tear down, and rebuild. MS recommends... 😀
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply