3

I have 2TB of DB involved in SQL Server transactional replication. I want to move publisher DB files to a newer disk with minimum amount of downtime and without breaking replication. Please let me know if this is possible or not. My DB is on SQL Server 2008 R2 EE edtion. Please let me know if you need any further details.

Improve this question

2 Answers 2

Reset to default
2

If downtime needs to be minimized, then utilize native SQL full, differential and log backups and restores to the new disks, with NORECOVERY for the restores, and a different DB name. This is the same as moving the database to a new server (you just have to use an alternate temporary name for the db). During your eventual downtime, stop applications and all repl jobs, perform a tail-of-the-log backup of the existing DB, restore with recovery on the new DB. Dettach the old DB, rename the new db to the orignal db name, reset DBO (and trustworthy, clr and service broker if they were enabled on the original), restart the reply jobs.

Improve this answer
5
  • Will replication settings be intact after this?
    – SQLPRODDBA
    Commented Dec 14, 2015 at 5:02
  • Yes, the settings will be maintained if done on the same server. Reference the KB article below on the topic. You should of course not attempt the process (regardless of replication), without doing a run through in a non-production environment. msdn.microsoft.com/en-us/library/ms151152(v=sql.110).aspx
    – Andrew Loree
    Commented Dec 14, 2015 at 5:12
  • I should have also pointed out, recreation of replication articles, publications and subscriptions is secondary. You as part of planning for this type of change should script off all replication configuration as a backup - msdn.microsoft.com/en-us/library/ms152483(v=sql.110).aspx
    – Andrew Loree
    Commented Dec 14, 2015 at 5:17
  • Thanks! I'll try this out in my test environment to see how it goes.
    – SQLPRODDBA
    Commented Dec 14, 2015 at 5:18
  • Andrew Loree: I was testing this and found that we can't detach the DB involved in replication. It gives error. :(
    – SQLPRODDBA
    Commented Dec 21, 2015 at 8:22
0

I want to move publisher DB files to a newer disk with minimum amount of downtime and without breaking replication.

Since you want to move the database to a different disk, it should not be that big of a downtime.

Below are the steps that you should follow :

  • stop log reader agent/job and disable it. Below script will generate the commands for you.

    SELECT 'exec msdb..sp_stop_job N'''+ name+'''' + ';'+'exec msdb..sp_update_job @job_name = N'''+ name+''', @enabled = 0' as 'CommandToStop/Disable Job', [id]
  ,[name]
  ,[publisher_id]
  ,[publisher_db]
  ,[publication]
  ,[local_job]
  ,[job_id]
FROM [distribution].[dbo].[MSlogreader_agents]
where publisher_db = 'AdventureWorksDW2012' -- change here !

  • Modify the physical database file location using ALTER DATABASE .. MODIFY FILE. This will modify the name in the system tables.

  • Offline the database and physically move the database file to the new destination.

  • Online the database

  • Enable and start the log reader agent.

All the above steps you can script out and automate. You can refer to the script that I wrote to rename the database - both logical and physical and adapt as per your needs.

Improve this answer
3
  • I should I have mentioned this earlier. We already did try this approach. But due to size of the data file(2TB) copying takes > 8hrs and we cant afford to have this much downtime.
    – SQLPRODDBA
    Commented Dec 13, 2015 at 7:50
  • Is it on a different server than your source server?
    – Kin Shah
    Commented Dec 13, 2015 at 11:24
  • Its on the same server.
    – SQLPRODDBA
    Commented Dec 14, 2015 at 5:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.