0

We are currently in the process of setting this up, but my boss has asked whether a restore of any of the 13 databases will affect the local publications/ Subscriptions which have been set on the local SQL server. Our database team have a routine where they routinely restore databases for testing by deleting the old database and restoring from backup the database using the same name.

We are unsure whether we will need to setup the publication/ subscription again or whether the distribution database will automatically pick up the restored database as the name remains consistent.

1
  • Little confused on how replication ties in here. Are you restoring backups of the databases that are part of a replication topology? If so, are they being restored on the Publisher side or Subscriber side? Are you moving your entire replication topology to Azure SQL or will one side be in a local SQL Server instance and the other side in Azure SQL?...if so, which side will be which?
    – J.D.
    Jul 24, 2023 at 17:00

1 Answer 1

0

First of all let's double check what do you mean as Azure SQL Database:

  1. VM in Azure
  2. Managed instances
  3. Azure SQL db

Anyways just restoring a copy there won't affect your replication.

We are unsure whether we will need to setup the publication/ subscription again

https://learn.microsoft.com/en-us/azure/azure-sql/database/replication-to-sql-database?view=azuresql-db

the distribution database will automatically pick up the restored database as the name remains consistent.

Nope. You have to configure it.

4
  • Hi Sergey, The replication is to a basic database hosted on Azure using 5 DTU's and a 2Gb limit as this is to allow an external agency access to create various reports which they extract from the database using a third party application. The developer specifically requested that this be setup as they do not want to have direct access to the live databases on premise.
    – delmc18
    Jul 25, 2023 at 15:30
  • Did I understand you correct that you want to have another copy of your database in Azure SQL DB to be subscribed to replication of your on-premise publisher? Yes it's possible as push subscription but it's not Azure migration at all, your distributor should be either on-premise or Managed instance or Azure VM. I am not sure why do you mention backup.
    – SergeyA
    Jul 25, 2023 at 15:40
  • Apologies Sergey, I am relatively new to this as I come from an AD/ Networking background and have been asked to look at this issue due to a specific client migrating to a new privately hosted platform and their previous supplier had the Transactional replication setup on their old SQL server. We have no access to that server, so are working blind through the setup
    – delmc18
    Jul 25, 2023 at 16:24
  • I am still confused. Are you migrating 13 databases to Azure SQL DB? Some of them are configured as publisher and you want to keep it as publisher or afraid something will break after migration because of transaction replication. Do you have replication in new Azure design? Could you update your question to put more details like what are you planning to do, what are your concerns and errors?
    – SergeyA
    Jul 25, 2023 at 16:44

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.