To setup SQL Server transactional replication, the publisher needs to be Standard or Enterprise edition. Subscriber can be any edition (except SQL Server Compact), but will require standard or Enterprise edition as well if configure merge (bi-directional) replication.
We will need to create some local Windows accounts to run replication agents:
| Agent | Location | Account name |
|---|---|---|
| Snapshot Agent | Publisher | <server name>\repl_snapshot |
| Log Reader Agnet | Publisher | <server name>\repl_logreader |
| Distribution Agnet | Publisher/subscriber | <server name>\repl_distribution |
| Merge Agnet | Publisher/subscriber | <server name>\repl_merge |
On Publisher machine, create a file share for the snapshot folder. Grant following permissions to the above accounts on Publisher:
| Account name | Permission |
|---|---|
| repl_snapshot | Full Control |
| repl_distribution | Read |
| repl_merge | Read |
Note: When using SSMS to connect to Publisher or Distributor, always using server name and do NOT use localhost or IP address. It’s also recommended always connect to Publisher/Distributor/Subscriber with administrative rights.
On Publisher, create a SQL login for the above 4 Windows accounts and grant it access to the distribution database and database to be replicated, with dbo permission.
References:
Tutorial: Prepare SQL Server for replication (publisher, distributor, subscriber)
Tutorial: Configure replication between a server and mobile clients (merge)