Transactional Replication with Remote Publisher
-
Hi,
I have a situation where there is a Subscriber database in our secure zone, which needs to be replicated from a Publisher in a remote location.
I would like to do this by having a Distributor in a transient/trusted zone of our environment, which will connect to the remote Publisher over internet via VPN connection, while Subscriber remains in secure zone isolated from the internet and no direct connectivity to the Publisher. The below diagram helps demonstrate this architecture, where there is apparently no direct connection from Subscriber to Publisher.
Is this configuration possible? Because when setting up a Subscriber for example as seen here: The Subscriber, it looks like the Publisher server needs to be connected to (not the distributor) when setting up the subscription
Also, regarding snapshots, does both the Publisher and Distributor servers need access to the Snapshot folder? Because in our configuration the Snapshot folder will be accessible to only the Distributor and Subscriber servers (not Publisher)
Thanks for any helpCheers
-
In your scenario just Distributor need to have access to both Publisher and Subscriber. If you want to create subscription just connect remotely to Distributor, connect to publisher from there and create new subscription.
All agents LogREader, Snapshot and Distribution (for Push replication) are located on Distributor. Only Distributor need access to Snapshot folder as I said Snapshot agent will be connecting to that folder.
Snapshot agent (Distributor) will connect to Publisher take snapshot and put into snapshot folder then Distribution agent(if Push then located on Distributor) will take that snapshot and deliver it to Subscriber.If you want to use Pull Replication then Subscriber will need have access to snapshot folder.
There is also possibility to use FTP for Snapshot.
BartL
Replication Blog -
Ok so you create the subscription on the Distributor? How do you choose the target subscriber databases to be on subscriber server?
-
not exactly. In order to create publication and subscription u need to connect to Publisher and create it there. However if Publisher don't have an access to Subscriber creating subscription by using GUI directly from Publisher wont be possible. What Im doing in these cases is RDP to Distributor, connecting to Publisher from there using SSMS and create subscriptions using GUI.
You can go around this and us T-sql to create subscription as then access to Subscriber is not checked. With GUI u need to connect to Subscriber and pick the Subscriber database.
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addsubscription-transact-sqlWhen u setting up subscription (via GUI) one of the step is to connect to subscriber and point subscriber database. The question is if u sending data to database that already exists? or you will be creating new empty database and sending snapshot down to Subscriber.
BartL
Replication Blog -
Ok thanks for the help
You are saying to create the Subscription on the Publisher, but aren't you supposed to create the Publication on the Publisher and create Subscription on the Subscriber? Then choose the Publication on the Publisher server as the source?
Cheers
-
Nope. You creating publication and subscriptions on Publisher. Later on when Distribution Agent connects for the first time is creating msreplication_subscriptions table and all the replication procs (that is needed for INSERT/UPDATE/DELETE) and sending snapshot if u choose to do so. From now on replication start sending data down to Subscriber.
There is nothing to be done by you on Subscriber when configuring replication.BartL
Replication Blog
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply