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.
- Data Consistency: Its helps in maintaining consistency in the data by allowing multiple sites to keep copies of the same data. This is useful when multiple sites need to read the same data or need separate servers for reporting applications.
- Load balancing: Replication allows you to separate the data to a number of servers and then distribute the query load among those servers.
- Reporting: Replication helps in reporting applications. Replication can increase the performance, speed and efficiency of reporting without impacting daily operations
- Database Security: Replication provides security, stability and availability while providing access to data located at distributed locations. Replication also distributes new data entered by external users and minimizes delays and data loss.
- High Availability: Replication allows multiple users and sites to make changes and then merge the data modifications together, identifying and resolving conflicts. Replication makes data available at all times.
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:
- Snapshot replication: Data on some server is simply copied to another server, or to another database on the same server. The Snapshot replication is time and resource consuming compared to the other two SQL replication types and is used when you need to fully overwrite the database at the subscriber or when you do an initial database replication update, which will be refreshed using one of the other two SQL replication types after the initial copy.
- Merging replication: Data from two or more databases is combined into a single database. Of course when using merging replication, you should be aware that there might be conflicts, eg duplicated primary keys. If there is a conflict, then the merge replication follows a predetermined conflict resolution plan to correct the issue.
- Transactional replication: Users receive full initial copies of the database and then receive periodic updates as data changes. Transactional replication is mostly used with databases where data changes frequently and there’s a need for constant refreshing of the data. The replication process watches the publisher’s database for changes. If there are changes, it distributes them over to the replication subscribers.
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
