In the current digital world, users are now located all over the world, and developers must manage the need for users to interact from any location, execute an action, and keep the databases synced across different locations.
Let’s imagine a scenario where we’ve been working on a global, multi-location application. Users from various locations visit the application which subsequently redirects them to the correct country’s page. Application is connected to a local database, but all country databases must be in sync. Also a request should immediately be routed to another country server, with no loss of data or connectivity, if the country server user is connected to fails.
By storing copies of data across many server, often known as nodes, Peer-to-Peer replication provides a scale-out and high-availability solution. Peer-to-peer replication, which is built on transactional replication, propagates transactionally consistent updates in near real-time. This allows programs that require read operations to scale out to distribute client reads over several nodes. Peer-to-peer replication provides data redundancy and enhances data availability since data is preserved across nodes in near real-time.
As seen in the topology illustration, the application server is sending queries to both database servers. Both country nodes are duplicating each other’s data. Because it is bi-directional replication, each database acts as both a publisher and a subscriber, and they are referred to as nodes, and replication is referred to as peer to peer replication.
Below, I will show you how to replicate databases synchronously using SQL. Here you can find a sample SQL Database.
First, we indicate the operation we want to perform and name it accordingly:
use [AdventureWorks2017]
exec sp_replicationdboption @dbname = N'AdventureWorks2017', @optname = N'publish', @value = N'true'
GO
Then, we create a publisher windows account for reading the logs:
use [AdventureWorks2017]
exec [AdventureWorks2017].sys.sp_addlogreader_agent @job_login = N'SQL\repl_logreader', @job_password = null,
@publisher_security_mode = 1, @job_name = null
GO
Next, we add our publication and give the necessary settings depending on how you would like to use your transaction (Modifiable according to your needs):
use [AdventureWorks2017]
exec sp_addpublication @publication = N'AdvWorksProductTrans', @description = N'Transactional publication of database ''AdventureWorks2017'' from Publisher ''SQL''.', @sync_method = N'concurrent', @retention = 0, @allow_push = N'true', @allow_pull = N'true', @allow_anonymous = N'true', @enabled_for_internet = N'false', @snapshot_in_defaultfolder = N'true', @compress_snapshot = N'false', @ftp_port = 21, @ftp_login = N'anonymous', @allow_subscription_copy = N'false', @add_to_active_directory = N'false', @repl_freq = N'continuous', @status = N'active', @independent_agent = N'true', @immediate_sync = N'true', @allow_sync_tran = N'false', @autogen_sync_procs = N'false', @allow_queued_tran = N'false', @allow_dts = N'false', @replicate_ddl = 1, @allow_initialize_from_backup = N'false', @enabled_for_p2p = N'false', @enabled_for_het_sub = N'false'
GO
Then we add our publication snapshot (the information of what we will publish and how for other subscribers to see):
exec sp_addpublication_snapshot @publication = N'AdvWorksProductTrans', @frequency_type = 1, @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 = 0, @active_end_date = 0, @job_login = N'SQL\repl_snapshot', @job_password = null, @publisher_security_mode = 1
GO
Next, we start adding the articles (database items):
use [AdventureWorks2017]
exec sp_addarticle @publication = N'AdvWorksProductTrans', @article = N'Product', @source_owner = N'Production', @source_object = N'Product', @type = N'logbased', @description = null, @creation_script = null, @pre_creation_cmd = N'drop', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Product', @destination_owner = N'Production', @vertical_partition = N'true', @ins_cmd = N'CALL sp_MSins_ProductionProduct', @del_cmd = N'CALL sp_MSdel_ProductionProduct', @upd_cmd = N'SCALL sp_MSupd_ProductionProduct', @filter_clause = N'[SafetyStockLevel] < 500'
GO
We add the desired columns of data for our rows of articles:
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ProductID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Name', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ProductNumber', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'MakeFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'FinishedGoodsFlag', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Color', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'SafetyStockLevel', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ReorderPoint', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Size', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'SizeUnitMeasureCode', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'WeightUnitMeasureCode', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Weight', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'DaysToManufacture', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ProductLine', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Class', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'Style', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ProductSubcategoryID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ProductModelID', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'SellStartDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'SellEndDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'DiscontinuedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'rowguid', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
exec sp_articlecolumn @publication = N'AdvWorksProductTrans', @article = N'Product', @column = N'ModifiedDate', @operation = N'add', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
Next, we add a filter for our article (if required):
exec sp_articlefilter @publication = N'AdvWorksProductTrans', @article = N'Product', @filter_name = N'FLTR_Product_1__52', @filter_clause = N'[SafetyStockLevel] < 500', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
And finally, we create the synchronization object:
exec sp_articleview @publication = N'AdvWorksProductTrans', @article = N'Product', @view_name = N'SYNC_Product_1__52', @filter_clause = N'[SafetyStockLevel] < 500', @force_invalidate_snapshot = 1, @force_reinit_subscription = 1
GO
Monitoring
One of the crucial parts of the replication process is monitoring. Due to the dispersed nature of replication, it is critical to keep track of activity and status across all machines participating. Many common questions can be answered with the help of different monitoring tools:
- Is my replication system healthy?
- Which subscriptions are slow?
- How far behind is my transactional subscription?
- How long will it take a transaction committed now to reach a Subscriber in transactional replication?
- Why is my merge subscription slow?
- Why is an agent not running?
The following tools can be utilized for monitoring:
SQL Server Replication Monitor
An essential tool for monitoring replication, provides a publisher-focused view of all replication activities.
SQL Server Management Studio
Replication Monitor is accessible through SQL Server Management Studio. It also lets you see the current state and the most recent message logged by the following agents, as well as start and stop them: Log Reader Agent, Snapshot Agent, Merge Agent, and Distribution Agent.
T-SQL and Replication Management Objects (RMO)
Both are two interfaces that allow you to monitor all forms of replication from the Distributor. Merge replication also allows the Subscriber to keep track of replication.
Alerts for replication agent events
Replication comes with a set of pre-defined alerts for replication agent events, and you may add more if needed. Using alerts, you may have an automatic response to an occurrence and/or inform an administrator.
System Check
Provides a number of counters for replication and may be used to monitor performance.