SourceDB---->Distributor---------->TargetDB(internet)
The server SourceDB and Distributor are local LAN servers
1 open port 443, 1443 on firewall for the the Internet SQL Server (eg, A EC2 SQL server)
2 add real host/ip pare for the Internet server, use IP is not allowed
edit C:\Windows\System32\drivers\etc\hosts
3 add a SQL Server account for each agent on each node, use the same name and password
USE [master]
GO
CREATE LOGIN [repadmin] WITH PASSWORD=N'password123', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF
GO
EXEC master..sp_addsrvrolemember @loginame = N'repadmin', @rolename = N'sysadmin'
GO
configure and start the SQL Server Agent on the distributor server
4 create distributor database on a independent server
We use push subscription only, so we can just use local path for the snapshot folder instead of a network path and sharing
remember to add sourceDB as its publisher
5 configure distributor on the publisher side
use the independent server as its distributor
6 new publication on the publisher server
during [Security Settings...]
check:
a Run under the SQL Server Agent Service account
b Using the following SQL Server login:
7 new subscription from the publisher side
choose [push subscriptions]
add the targetDB
during [Distribution Agent Security]
check:
a Run under the SQL Server Agent Service account
b Using the following SQL Server login:
8 start the snapshot agent to create the snapshot
9 start the Log Reader Agent to replicate the data