Transactional Replication
Important note:
If you have replication in your environment. First script out distributor and publication including property. Script out each publication.
List out how many tables / objects are added into replication with table name, any filter applied etc.
Also, generate script out of subscriptions database, in case if we reapply the snapshot the existing object will drop and created. If we have any triggers, additional index that won’t come. We need to create those after reapply.
Design:
Node1 – distributor & Publisher. One publisher with many subscribers.
Pre request:
- Repl_Dist_Pub_connection — sp_adddistpublisher@login mainly used to connect distributor and publisher initial configuration. Remember or store the password for adding another publisher for this remote distributor will need this password.
- Create new threeAD service account or SQL login for replication job agents run i.e. @job_login. Some prefer to run all 3 in single account as well.
Muthu\Svc_repl_snapshot , Muthu\Svc_repl_logreader & Muthu\Svc_repl_distributor
- We need 4 SQL or windows login for connect publisher and subscriber to do data movement.
Ex: 4 login are SQL login names:
Repl_Connect_Snap_Agent — sp_addpublication_snapshot @publisher_login
Repl_Connect_Log_Agent — sp_addlogreader_agent @publisher_login
Repl_Connect_Dist_Agent — sp_addpushsubscription_agent @subscriber_login
Total we need 7 accounts and it is always better to have dedicated 7 account. Still you can do all in single login as well. In case of troubleshooting it hard to find in single login.
- Create share path with R/W permission to Svc_repl_snapshot for snapshot files BCP i.e DDL & DML of replicated objects
Better create share path on distributor server and add permission at security of folder and advanced security of permission. Svc_repl_snapshot (Full control) , Svc_repl_logreader (Only read) & Svc_repl_distributor (Only read)
Better create all logins into all SQL servers and grant all necessary permission, before configure replication.
All logins should have db owners to replicated DB, for distributor DB give DB owner as well to the logins.
- Create DB on subscriber and grant permission to login accounts.
For any issue in access we can grant sysadmin to all logins for testing.
We can configure replication by GUI and TSQL .Overview what it is doing from GUI or T-SQL.
- ConfigureDistribution DB sp_adddistributor, sp_adddistributiondb, sp_adddistpublisher
- Add Log reader agentsp_addlogreader_agent
- Add publicationsp_addpublication
- Add snapshot agentsp_addpublication_snapshot
- Add articlesp_addarticle
- Add columns sp_articlecolumn– Optinoal
- Add sp_articleview — Optinoal
- Add subscriptionsp_addsubscription
- Add Distribution agentsp_addpushsubscription_agent
Configuration
On distributor server Create and configure
Choose Node1 à Right click Replication in SSMS à Configure distributor à Next à It will ask whether you can have same distributor and publisher in node1 or you wanted to configure remote dedicated distributor in different server à Next àpass snapshot share path \\NODE1\Repl_Snap_files àNext–> Point the distributor DB name and change MDF and LDF file path à Next à Choose Publisher àSelect or Add Node1 à Click the distribution DB three dot and pass distributor credential “Repl_Dist_Pub_connection” which is used to add and connect sp_adddistpublisher à Pass the password and remember or store it for adding another publisher for this remote distributor NextàConfigureàFinish
- Grant permission for all three logins to Distribution DB.
Svc_repl_snapshot (DB_Owner) , Svc_repl_logreader (DB_Owner) & Svc_repl_distributor (DB_Owner)
After creation from GUI – 2 LS on distributor to 1 Pub and other DIST & 7 common Jobs created:
You will see 1 linked servers. i.e named as repl_distributor and 7 common jobs.
On Publisher server Create Publication – You need to pass Snapshot &Log reader agent account
Before creating publisher, create DBs on Subscriber server on Node2. You can have same name or different name. Better have same name.
- Grant permission for all three logins to DB.
Grant DB_Owner only for Svc_repl_distributor to the Subscriber DB.
- Create Publisher
- Grant permission to the logins in publisher side
Svc_repl_snapshot (DB_Owner) , Svc_repl_logreader (DB_Owner) & Svc_repl_distributor (DB_Owner)
- Go to replication àLocal publication àRight click New publication àSelect DB name àNext àchoose Transaction replication à Choose articles (Tables, SP, Views / Indexed View, UDF). You can configure the article properties (FK, Constraint and index to replicate etc. For SP you can have both schema and execution to be replicated etc.) à Next à Select check box Create snapshot immediately or schedule it in the agent , mostly we will not schedule thisàUnselect use snapshot account for log reader check box àpass snapshot agent credential & log reader credential and For Connect publisher pass credential login — tncreplicator1 & tncreplicator2à Create publication à Pass Publication Name Muthu_Replica àFinish
- Check snapshot agent status and make sure the data is replicated.
If the snapshot agent is says starting more than X time then there might be issue with password or permission etc. You can check that in Distributor DB Exec sp_helpdistpublisher àCheck column called active is 1. If it is 0 you need to enable to active.
sp_helpdistpublisher
sp_changedistpublisher @publisher = ‘node1’, @property = ‘active’ , @value = ‘true’
You can get error like – string or binary data would be truncated transactional replication
- Right click on publication àProperties àpublication access list à add distributor agent (Svc_repl_distributor)
After creation from GUI: 2 Jobs created
Two jobs are created on distributor One is snapshot agent and other is log reader agent.
1 is for snapshot agent and other is for log reader agent.
On Publisher server Create subscription – You need to pass distributor agent account
Right click on publication and new subscription
Choose publication à choose push or pullàpushà add subscriber to Node2 àchoose DB ànext àPass credential for distributor agent (Svc_repl_distributor) and pass connect the subscriber login credential tncreplicator1 àRun continuously or in schedule ànextà Initialize and immediately àFinish
After creation from GUI: 1 LS to SUB & 1 Job
There is one linked server created in publisher side which is having connection to subscriber Node2. 1 new job been created in distributor server which is distributor push agent
That’s all done. Do some changes in the table and make sure the changes are replicating.
Repeat and Create 3 more publication with subscriber Muthu-BI, Muthu-DR,Muthu_Local.
Common Notes
The agent jobs or replication agent will be created based on replication topology design.
Ex: 3 separate server design as dedicated to each.
All three agents are created in distributor itself for push subscription.
Ex: 2 server design as both publisher and distributor on same server and subscriber server as different server.
All three agents will be created in Publisher itself, since distributor is on same server for push subscription.
Test: Create Transactional replication
Run some DML
Alter table
–By default following will not replicate, you can change copy to in article property from false to true
Create & Alter Default and Check Constraints
Create & Alter Non-Clustered index
Create & Alter Foreign key Constraints
Create & Alter trigger
Here is the list:
