September 6, 2017 at 2:54 pm
Hi,
I am maintaining a 4 TB database configured as a subscriber(pull) with transaction replication. Db has more than 70 data files spreaded across multiple drives. I am in position to move all these data and log files to different drives. Anyone has experience on the same to move data files without re-initializing?
Thanks in Advance.
---------------------------------------------------
Thanks,
Satheesh.
September 6, 2017 at 4:08 pm
Satheesh E.P. - Wednesday, September 6, 2017 2:54 PMHi,I am maintaining a 4 TB database configured as a subscriber(pull) with transaction replication. Db has more than 70 data files spreaded across multiple drives. I am in position to move all these data and log files to different drives. Anyone has experience on the same to move data files without re-initializing?
Thanks in Advance.
Did it with starting and stopping the replication jobs. Stop the distribution job and the distribution clean up job. After your are done, start the distribution job and wait for the transactions to go across before starting the clean up job. If you have a high volume of data during that time frame, the distribution database will grow so you'd want to prepare for that if needed.
Sue
September 7, 2017 at 11:16 am
Thanks for the update Sue_H. Could you please confirm that you had done it with Pull subscription?
---------------------------------------------------
Thanks,
Satheesh.
September 7, 2017 at 11:53 am
Satheesh E.P. - Thursday, September 7, 2017 11:16 AMThanks for the update Sue_H. Could you please confirm that you had done it with Pull subscription?
Can't remember if it was with push or pull which doesn't matter. The primary difference between the two is where the distribution agent runs. Otherwise it's the performance impacts - running all the agents on the distributor vs spread out distribution agents for different subscribers. And pull being generally faster over a WAN.
So you would "pause" replication in the same way. The agents are run via a job which is why you disable the jobs.
Sue
September 7, 2017 at 11:57 am
Use the ALTER DATABASE ... MODIFY FILE option for moving the files, and note that once you take the DB offline you need to move the files manually, SQL won't do it.
The detach/attach method of moving files breaks replication afaik.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply