March 11, 2004 at 6:58 am
Question: What will happen with replication if I make copies of the user database .mdf and .ldf files?
I am planning to move databases from one (old) clustered server to another (new) clustered server. To make the downtime as short as possible, I plan on shutting down the user databases and copying the the user database files (.mdf and .ldf) to the new server. The following is a more detailed plan:
1) The master and msdb database will be created upon installing SQL Server. I have a script to capture logins and user databases from the old server into a create script, which I will run on the new server to create the logins and user databases.
2) Shutdown both server instances.
3) Copy the user database .mdf and .ldf files from the old server to the new server.
4) Start up the new server instance.
5) UPDATE msdb..sysjobs SET originating_server = newservername.
What is going to happen to replication in this case? I am leaning towards removing replication before shutting down the servers and copying the user database files and then build out replciation on the new server. However, if I don't have to do remove replication, the transfer will be easier and quicker.
March 11, 2004 at 4:22 pm
March 12, 2004 at 5:18 am
Snapshot and Merge (continuous update) Replication.
March 12, 2004 at 9:18 am
I have been working with replication for 2 and a half years. You can't successfully move replication from one server to another, unless you have a few (2,000,000) brain cells you can afford to loose. I recomend totally removing replication and re-creating it on the new server. This will save you a lot of debuggin time later..
back up the database.
script all replication.
disable and remove replication.
run "dbcc checkdb" and "dbcc checlalloc" -- resolve any issues.
backup(2) the database again.
restore backup(2) to the new server.
re-boot SQL Server (just a precaution).
on the new server run "dbcc checkdb" and "dbcc checlalloc" -- resolve any issues.
rebuild replication using the saved scripts (modify the server name = newserver)
re-apply the snapshots
start your replication agents.
>>>>>have one or more beer(s).
>>this is a general overview<<
>> develop, detail and TEST, TEST, TEST your own process again.<<
good luck..
March 14, 2004 at 5:02 pm
I agree with SQL Draggon!
Just becareful of 1 thing! Hence my question about which Replication Model are you using.
When you remove replication from a replicated Database where MERGE Replication was implemented, it does NOT remove the UniqueIdentifier Columns on all tables that were part of any publication!
March 15, 2004 at 5:24 am
That's ok if the ROWGUID column remains. We will be re-implementing replication upon successful move to the new server.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply