SQL Server replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases for consistency. A distributed database management system (DDBMS) ensures that changes like addition, modification and deletion performed on the data at any given location are automatically reflected in the data stored at all the other locations.
Advantages of Replication
Replication provides various benefits depending on the type of replication and the options you choose. The main benefit of SQL Server replication is the availability of data as required.
There are many more advantages of replications. I have mentioned the more important ones above.
Replication Model:
In any replication scenario, there are two main components:
Publishers: The Publisher is a type of source database server that makes data available to other servers through replication. The publisher can have one or more publications where each has a logically related set of objects and data to replicate.
Distributor: The Distributor is the database server that contains the set of jobs responsible for queuing replicated data from the publisher. The role of the distributor varies depending on the type of replication you implement. Database replication can be done in at least three different ways:
Subscribers: Subscribers are the destination database for data coming from the single or multiple publishers and publications. Subscribers are database servers that receive updates from the publisher when data is added, modified or deleted.
Now let’s look at the implementation of SQL Replication:
Step 1: Create the database with the table as MyMasterTable having primary key with identity column “ON” and Not for Replication contraint “ON”.
CREATE DATABASE MyReplicationMaster
GO
Use MyReplicationMaster
Create Table MyMasterTable
(
MasterID int NOT NULL IDENTITY(1,1) NOT FOR REPLICATION CONSTRAINT MyMasterTablePK PRIMARY KEY,
Name varchar(100) NOT NULL,
City varchar(100) NOT NULL,
)
GO
Insert into MyMasterTable(Name, City)
VALUES('Sample1', 'Delhi'),
('Sample2', 'Mumbai'),
('Sample3', 'Pune'),
('Sample4', 'Hyderabad')
Step 2: Configure the Distributer:
use master
exec sp_adddistributor @distributor = 'Servername' (Please add your sql server name), @password = N''
GO
exec sp_adddistributiondb @database = N'distribution',
@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data',
@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Data',
@log_file_size = 2,
@min_distretention = 0,
@max_distretention = 72,
@history_retention = 48,
@security_mode = 1
GO
use [distribution]
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U '))
create table UIProperties(id int)
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null)))
EXEC sp_updateextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
else
EXEC sp_addextendedproperty N'SnapshotFolder', N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData', 'user', dbo, 'table', 'UIProperties'
GO
exec sp_adddistpublisher @publisher = 'Servername' (Please add your
server name),
@distribution_db = N'distribution',
@security_mode = 1,
@working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\ReplData',
@trusted = N'false',
@thirdparty_flag = 0,
@publisher_type = N'MSSQLSERVER'
GO
Step 3: Next Configure the Publisher:
use [MyReplicationMaster] exec sp_replicationdboption @dbname = N'MyReplicationMaster', @optname = N'publish', @value = N'true' GO use [MyReplicationMaster] exec sp_addlogreader_agent @job_login = N'Username'(Database user who have admin access), @job_password = N'Password' (Database user password who have admin access), @publisher_security_mode = 1, @job_name = null GO -- Adding the transactional publication use [MyReplicationMaster] exec sp_addpublication @publication = N'MyLocalPublisher', @description = N'Transactional publication of database ''MyReplicationMaster'' from Publisher, @retention = 0, @allow_push = N'true', @allow_pull = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'true'
Step 3: Now add Article:
use [MyReplicationMaster] exec sp_addarticle @publication = N'MyLocalPublisher', @article = N'MyMasterTable', @source_owner = N'dbo', @source_object = N'MyMasterTable', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'MyMasterTable', @destination_owner = N'dbo', @vertical_partition = N'false', @ins_cmd = N'CALL sp_MSins_dboMyMasterTable', @del_cmd = N'CALL sp_MSdel_dboMyMasterTable', @upd_cmd = N'SCALL sp_MSupd_dboMyMasterTable' GO
Step 4: Take backup and restore DB. Also, take Log backup:
USE [master]
backup database MyReplicationMaster to disk = 'C:\Personal\Shared\MyReplicationMaster.bak'
USE [master]
RESTORE DATABASE [MyReplicationMasterARC] FROM
DISK = N'C:\Personal\Shared\MyReplicationMaster.bak' WITH FILE = 1,
MOVE N'MyReplicationMaster' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyReplicationMasterARC.mdf',
MOVE N'MyReplicationMaster_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\MyReplicationMaster_logARC.ldf',
NOUNLOAD, STATS = 5
GO
backup LOG MyReplicationMaster to disk ='\\SHARED\MyReplicationMasterLOG.bak'
<pre>
<strong>Step 4:</strong> Finally add Subscription:
<pre lang="sql">
use [MyReplicationMaster]
exec sp_addsubscription @publication = N'MyLocalPublisher',
@subscriber = 'Servername' (Please add your sql
server name),
@destination_db = N'MyReplicationMasterARC',
@subscription_type = N'Push',
@sync_type=N'initialize with backup',
@backupdevicetype=N'disk',
@backupdevicename='\\SHARED\MyReplicationMasterLOG.bak',
@article = N'all',
@update_mode = N'read only',
@subscriber_type = 0
exec sp_addpushsubscription_agent @publication = N'MyLocalPublisher',
@subscriber = 'Servername' (Please add your sql server \ name),
@subscriber_db = N'MyReplicationMasterARC',
@job_login = N'Username'(Database user who have admin
access),
@job_password = = N'Password' (Database user password who
have admin access),
@subscriber_security_mode = 1,
@frequency_type = 64,
@frequency_interval = 0,
@frequency_relative_interval = 0,
@frequency_recurrence_factor = 0,
@frequency_subday = 0,
@frequency_subday_interval = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@active_start_date = 20140824,
@active_end_date = 99991231,
@enabled_for_syncmgr = N'False',
@dts_package_location = N'Distributor'
GO
To monitor the Replication, right click Replication -> Open Launch Replication moniter
You can monitor the errors, traces, job agent status and warnings.
I hope the steps mentioned above will guide the readers through a smooth implementation process. Would like to hear your thoughts in the comments section.
References:
http://www.sql-tutorial.net/sql-replication.asp