Transactional Replication 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…

Check Distributor property most of the details available there. Transaction and history retention of replication object how long to store.   Publication Retention Change each publication from 336 hours to never expire. Transaction Retention Period The first setting is the Transaction Retention Period. This is defined on the distribution database. It specifies how long the transaction information is kept in the distribution database. So that subscriber can read it. History Retention setting This defines how long information about the health of the replication setup is kept in the distribution database. This setting does not affect the subscribers.   Profile Profile is much important to validate the data and pass throw…

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…

When you have a replication in your environment what pre requesting we need to do. 1. Generate script out of subscriber database, include Trigger, indexes etc, useful for snapshot reapply. 2. Find which server is Distributor (Local same server or remote / different server), Publisher (All publication) And subscriber (All subscription) 3. Find out how replication topology designed. Ex: How many distributor, publisher and subscriber, one publication with many subscriptions or one publication with one subscription etc. 4. Script out all distributor, publication and subscription Go to replication folder in SSMS -> generate script of Distributor alone and save it Then choose generate script out of publication property and save…

Replication Technology Replication is a set of SQL agent job which will be created while configuring replication. This Replication SQL Agent job will copy and send data and database objects (Tables i.e Articles, SP, Views & UDF etc) from one database to another database. There will be three roles in replication i.e. Source DB server is publisher and destination/Target DB server is subscriber. In between there will be distributor DB server. All three will communicate with each other. We can have all 3 in the same servers or 2 severs or each one will be in dedicated server to configure replication. It is based on client requirement and DBA needs we choose…