All we need is an easy explanation of the problem, so here it is.
I have a customer that is looking for a very unusual replication configuration. I am hoping for some insight on why this is a bad idea, or even the possibility that it can be done.
There are two systems where replication will be deployed, Publisher and Distributor will replicate to Subscriber on a local LAN. Need to populate a cloud server for web site.
Question1: Could this subscriber become a publisher to another cloud based subscriber? Before you point out that the original publisher can update both local and cloud subscribers, customer doesn’t want the work load on publisher system.
Question 2: Customer suggested sending backup of local subscriber to cloud and restoring on cloud DB server at least daily. I don’t believe permissions are replicated, along with other issues. Comments?
I could go on and on, but I will wait on any feedback before going further.
How to solve :
I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.
You can use a Subscriber to re-publish data to other Subscribers, as briefly mentioned in Replication Publishing Model Overview – Subscribers. I’m not sure under what scenarios this is applicable or not.
Though one thing your customer should keep in mind is just because they are not putting the publishing workload on the original Publisher, doesn’t mean there can’t be contention caused from the Subscriber doing the re-publishing. Depending on the timing of things, and if other changes are actively being made to the tables at the Subscriber concurrent to changes being replicated from the original Publisher, there could be lock contention that affects the original Publisher’s server in some capacity.
Server level settings, such as Logins, their database mappings, and server level permissions won’t be included in the database backup, but database level settings such as Users will be. Though most times this will result in orphaned accounts, even if the Login is manually created at the destination server where the backup is restored. This is because each Login has a unique
SIDwhich correlates to the User.
The Troubleshoot orphaned users (SQL Server) documentation goes into good detail on identifying and resolving this issue.
Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂