Monday, 17 June 2013

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

Popular Posts