SQL Server Replication Setup

---

Jobs

Agent History clean up: distribution

Use Distribution

EXEC dbo.sp_MShistory_cleanup @history_retention = 48

Internally for this job,Cleanup procedure is running. Removes replication agent history from the distribution database.

Created Snapshot Replication



---

Distribution clean up: distribution

USE Distribution.

EXEC dbo.sp_MSdistribution_cleanup @min_distretention = 0, @max_distretention = 72

Removes replicated transactions from the distribution database.

Expired subscription clean up

USE Master

EXEC sys.sp_expired_subscription_cleanup

Detects and removes expired subscriptions from published databases.

Reinitialize subscriptions having data validation failures

USE Master

exec sys.sp_MSreinit_failed_subscriptions @failure_level = 1

Reinitializes all subscriptions that have data validation failures.

Replication agents checkup

Use Master

sys.sp_replication_agent_checkup @heartbeat_interval = 10

Detects replication agents that are not logging history actively.

Replication monitoring refresher for distribution.

Use Distribution

exec dbo.sp_replmonitorrefreshjob

Replication monitoring refresher for distribution. By Default it’s in Disable State.

SARATH\DEVEL01-publish-BSC_DEPT_SNAPSHOTMODEL-1

This is Publisher table needs to replicate. This is snapshot replication model.

Steps

1) Snapshot Agent startup message.

Use Distribution

sp_MSadd_snapshot_history @perfmon_increment = 0, @agent_id = 1, @runstatus = 1,

@comments = 'Starting agent.'



---

2) Run agent.

Run as Agent Service account login

-Publisher [SARATH\DEVEL01] -PublisherDB [publish] -Distributor [SARATH\DEVEL01] -Publication [BSC_DEPT_SNAPSHOTMODEL] -DistributorSecurityMode 1

3) Detect nonlogged agent shutdown.

Use Distribution

sp_MSdetect_nonlogged_shutdown @subsystem = 'Snapshot', @agent_id = 1

Creating subscription

Next



---

Next

Next



---

Completion Wizard

Finally jobs are created



---

Transactional publication

On Primary Key, need to create Transactional replication

Then Next ... Two Agents will require Transactional

1) Snapshop Agent 2) Log Reader Agent



---

Then Next..Next and ...Finish

Then need to create subscription

Start go forward..

Go next

Then next..next & Finish.



---

Transactional publication with updatable subscriptions:

The Publisher streams transactions to SQL Server Subscribers after they receive an initial snapshot of the published data. Transactions originating at the Subscriber are applied at the Publisher.

Right click Publisher and Next

Three Agents will require Txnl updateable

1) Snapshop Agent 2) Log Reader Agent 3) Queue Reader Agent Then creating

Then Next ..Next as usual.



---

Need to restarts if MSDTC is stopped, it’s in services.msc

Then Three jobs are created in Publisher as mentioned below.

Then need to create subscription

Start go forward..

Next >>>



---

Finish

Then Job is created, we can rename as well.

Creating Merge Replication

The Publisher and Subscribers can update the published data independently after the Subscribers receive an initial snapshot of the published data. Changes are merged periodically. Microsoft SQL Server Compact Edition can only subscribe to merge publications

.

While creating Merge