Meditation: Replicate Data from Multiple Instances into a Single Reporting Database

This post has been republished via RSS; it originally appeared at: SQL Server Support articles.

First published on MSDN on Apr 24, 2013

 

 

Somebody approached me recently with a question like this:

 

Question : We’ll be hosting our existing application in one data center, but we’ll be setting up 2 more instances in two different data centers. Is possible to use replication to move all the data into an single database so that we can report on all the instance data from one place? We use identity values, so I’m not sure how that would be possible unless we do something like configure Site A at 0, Site B at 1 billion, and Site C at 2 billion or something like that.

 

 

 

Answer:

 

Here are 2 suggestions on how to do this (there may be more options):

 

Option 1. Replicate all 3 tables into a single table on the subscriber (see setup below)

 

Option 2. Replicate each table to its own replica on the subscriber and then use a UNION ALL VIEW  - or a Partitioned View against which the Reporting application will query. You will get additional performance benefits because a partitioned view will direct the query to only the table that you care about.

 

Option 1: You can do what you are trying to accomplish with Transactional Replication. Not a common scenario but the technology allows you to do it. The basic idea is that you configure the Publication article with the option to



    • Keep existing object unchanged – this ensures that none of the publications drops the destination table when each is created

 

    • Identity Management – Manual – no identity management. I personally set my seed and increment to 1 and 1 on the Subscriber side.



 

 



    • The other thing to consider is simplify identity management by choosing a seed and increment that will ensure each instance gets unique values. Since we have 3 instances, we can use a 3-based increment.




Instance 1: (c1 int identity ( 1, 3 )


Instance 2: (c1 int identity ( 2, 3 )


Instance 3: (c1 int identity ( 3, 3 )

 

Here is what sample data would look like on each server with these identity values.

 


 

 


c1          c2
----------- ----------------
1           TabA
4           TabA
7           TabA


c1          c2
----------- ----------------
2           TabB
5           TabB
8           TabB


c1          c2
----------- ----------------
3           TabC
6           TabC
9           TabC

 

 

 

 

 

Below is a prototype solution you can build on top. This was all created on a single server, but the same applies to going across  multiple servers.

 


use master


 


go


 


--create 3 publishers and 1 subscriber (imagine they are on different servers, but for repl it does not matter)


 


 


create database ReplPubA


create databaseReplPubB


 


create databaseReplPubC


 


create databaseReplSubscriber


 


go


 


 


--create the table on PubA


 


useReplPubA


 


go


 


drop tableTab


 


create table Tab(c1 int identity (1, 3) primary key nonclustered, c2 varchar(16))


 


insert into Tab(c2) values ('TabA')


 


insert into Tab(c2) values ('TabA')


 


insert into Tab(c2) values ('TabA')


 


 


 


 


--create the table on PubB


 


useReplPubB


 


go


 


drop tableTab


 


create table Tab(c1 int identity (2, 3) primary key nonclustered, c2 varchar(16))


 


insert into Tab(c2) values ('TabB')


 


insert into Tab(c2) values ('TabB')


 


insert into Tab(c2) values ('TabB')


 


go


 


 


 


--create the table on PubC


 


useReplPubC


 


go


 


drop tableTab


 


create table Tab(c1 int identity (3, 3) primary key nonclustered, c2 varchar(16))


 


insert into Tab(c2) values ('TabC')


 


insert into Tab(c2) values ('TabC')


 


insert into Tab(c2) values ('TabC')


 


 


 


--create the schema on the subscriber. Keep Identity (1,1)


 


useReplSubscriber


 


drop tableTab


 


create table Tab(c1 int identity (1, 1) primary key nonclustered, c2 varchar(16))


 


 


 


--check the values in each db


 


select * from ReplPubA..Tab


 


select * from ReplPubB..Tab


 


select * from ReplPubC..Tab


 


select * from ReplSubscriber..Tab


 


 


 


Here is the entire replication setup script. You will have to change


 


 


/****** Scripting replication configuration. Script Date: 4/22/2013 3:29:08 PM ******/


 


/****** Please Note: For security reasons, all password parameters were scripted with either NULL or an empty string. ******/


 


 


/****** Begin: Script to be run at Publisher ******/


 


 


/****** Installing the server as a Distributor. Script Date: 4/22/2013 3:29:08 PM ******/


 


use master


 


exec sp_adddistributor@distributor = N'JPSQLMachine\SQL2008R2', @password = N''


 


GO


 


 


-- Adding the agent profiles


 


-- Updating the agent profile defaults


 


exec sp_MSupdate_agenttype_default@profile_id =1


 


GO


 


exec sp_MSupdate_agenttype_default@profile_id =2


 


GO


 


exec sp_MSupdate_agenttype_default@profile_id =4


 


GO


 


exec sp_MSupdate_agenttype_default@profile_id =6


 


GO


 


exec sp_MSupdate_agenttype_default@profile_id =11


 


GO


 


 


-- Adding the distribution databases


 


use master


 


exec sp_adddistributiondb@database = N'distribution', @data_folder = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA', @data_file = N'distribution.MDF', @data_file_size = 5, @log_folder = N'D:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\DATA', @log_file = N'distribution.LDF', @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode =1


 


GO


 


 


-- Adding the distribution publishers


 


exec sp_adddistpublisher@publisher = N'JPSQLMachine\SQL2008R2', @distribution_db = N'distribution', @security_mode = 1, @working_directory = N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL2008R2\MSSQL\ReplData', @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'


 


GO


 


 


exec sp_addsubscriber@subscriber = N'JPSQLMachine\sql2008r2', @type = 0, @description = N''


 


GO


 


 


 


/****** End: Script to be run at Publisher ******/


 


 


 


-- Enabling the replication database


 


use master


 


exec sp_replicationdboption@dbname = N'ReplPubA', @optname = N'publish', @value = N'true'


 


GO


 


 


exec [ReplPubA].sys.sp_addlogreader_agent@job_login = null, @job_password = null, @publisher_security_mode =1


 


GO


 


exec [ReplPubA].sys.sp_addqreader_agent@job_login = null, @job_password = null, @frompublisher =1


 


GO


 


-- Adding the transactional publication


 


use[ReplPubA]


 


exec sp_addpublication@publication = N'PubA', @description = N'Transactional publication of database ''ReplPubA'' from Publisher ''JPSQLMachine\SQL2008R2''.', @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


 


 


 


exec sp_addpublication_snapshot@publication = N'PubA', @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 = null, @job_password = null, @publisher_security_mode =1


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'sa'


 


GO


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'NT AUTHORITY\SYSTEM'


 


GO


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'MyDomain\Joseph'


 


GO


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'NT SERVICE\MSSQL$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'NT SERVICE\SQLAgent$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubA', @login = N'distributor_admin'


 


GO


 


 


-- Adding the transactional articles


 


use[ReplPubA]


 


exec sp_addarticle@publication = N'PubA', @article = N'Tab', @source_owner = N'dbo', @source_object = N'Tab', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Tab', @destination_owner = N'dbo', @status = 8, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTab]', @del_cmd = N'CALL [sp_MSdel_dboTab]', @upd_cmd = N'SCALL [sp_MSupd_dboTab]'


 


GO


 


 


-- Adding the transactional subscriptions


 


use[ReplPubA]


 


exec sp_addsubscription@publication = N'PubA', @subscriber = N'JPSQLMachine\SQL2008R2', @destination_db = N'ReplSubscriber', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type =0


 


exec sp_addpushsubscription_agent@publication = N'PubA', @subscriber = N'JPSQLMachine\SQL2008R2', @subscriber_db = N'ReplSubscriber', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'


 


GO


 


 


 


 


-- Enabling the replication database


 


use master


 


exec sp_replicationdboption@dbname = N'ReplPubB', @optname = N'publish', @value = N'true'


 


GO


 


 


exec [ReplPubB].sys.sp_addlogreader_agent@job_login = null, @job_password = null, @publisher_security_mode =1


 


GO


 


exec [ReplPubB].sys.sp_addqreader_agent@job_login = null, @job_password = null, @frompublisher =1


 


GO


 


-- Adding the transactional publication


 


use[ReplPubB]


 


exec sp_addpublication@publication = N'PubB', @description = N'Transactional publication of database ''ReplPubB'' from Publisher ''JPSQLMachine\SQL2008R2''.', @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


 


 


 


exec sp_addpublication_snapshot@publication = N'PubB', @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 = null, @job_password = null, @publisher_security_mode =1


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'sa'


 


GO


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'NT AUTHORITY\SYSTEM'


 


GO


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'MyDomain\Joseph'


 


GO


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'NT SERVICE\MSSQL$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'NT SERVICE\SQLAgent$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubB', @login = N'distributor_admin'


 


GO


 


 


-- Adding the transactional articles


 


use[ReplPubB]


 


exec sp_addarticle@publication = N'PubB', @article = N'Tab', @source_owner = N'dbo', @source_object = N'Tab', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Tab', @destination_owner = N'dbo', @status = 8, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTab]', @del_cmd = N'CALL [sp_MSdel_dboTab]', @upd_cmd = N'SCALL [sp_MSupd_dboTab]'


 


GO


 


 


-- Adding the transactional subscriptions


 


use[ReplPubB]


 


exec sp_addsubscription@publication = N'PubB', @subscriber = N'JPSQLMachine\SQL2008R2', @destination_db = N'ReplSubscriber', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type =0


 


exec sp_addpushsubscription_agent@publication = N'PubB', @subscriber = N'JPSQLMachine\SQL2008R2', @subscriber_db = N'ReplSubscriber', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'


 


GO


 


 


 


 


-- Enabling the replication database


 


use master


 


exec sp_replicationdboption@dbname = N'ReplPubC', @optname = N'publish', @value = N'true'


 


GO


 


 


exec [ReplPubC].sys.sp_addlogreader_agent@job_login = null, @job_password = null, @publisher_security_mode =1


 


GO


 


exec [ReplPubC].sys.sp_addqreader_agent@job_login = null, @job_password = null, @frompublisher =1


 


GO


 


-- Adding the transactional publication


 


use[ReplPubC]


 


exec sp_addpublication@publication = N'PubC', @description = N'Transactional publication of database ''ReplPubC'' from Publisher ''JPSQLMachine\SQL2008R2''.', @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


 


 


exec sp_addpublication_snapshot@publication = N'PubC', @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 = null, @job_password = null, @publisher_security_mode =1


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'sa'


 


GO


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'NT AUTHORITY\SYSTEM'


 


GO


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'MyDomain\Joseph'


 


GO


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'NT SERVICE\MSSQL$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'NT SERVICE\SQLAgent$SQL2008R2'


 


GO


 


exec sp_grant_publication_access@publication = N'PubC', @login = N'distributor_admin'


 


GO


 


 


-- Adding the transactional articles


 


use[ReplPubC]


 


exec sp_addarticle@publication = N'PubC', @article = N'Tab', @source_owner = N'dbo', @source_object = N'Tab', @type = N'logbased', @description = N'', @creation_script = N'', @pre_creation_cmd = N'none', @schema_option = 0x000000000803509F, @identityrangemanagementoption = N'manual', @destination_table = N'Tab', @destination_owner = N'dbo', @status = 8, @vertical_partition = N'false', @ins_cmd = N'CALL [sp_MSins_dboTab]', @del_cmd = N'CALL [sp_MSdel_dboTab]', @upd_cmd = N'SCALL [sp_MSupd_dboTab]'


 


GO


 


 


-- Adding the transactional subscriptions


 


use[ReplPubC]


 


exec sp_addsubscription@publication = N'PubC', @subscriber = N'JPSQLMachine\SQL2008R2', @destination_db = N'ReplSubscriber', @subscription_type = N'Push', @sync_type = N'automatic', @article = N'all', @update_mode = N'read only', @subscriber_type =0


 


exec sp_addpushsubscription_agent@publication = N'PubC', @subscriber = N'JPSQLMachine\SQL2008R2', @subscriber_db = N'ReplSubscriber', @job_login = null, @job_password = null, @subscriber_security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 0, @dts_package_location = N'Distributor'


 


GO


 

 

 

 

 

 

Option 2: You can simply replicate each table individually from each of the instances into a single subscriber database. Then you can build a View using UNION ALL and if you would like add CHECK constraints to make it a partitioned view. The reporting application will simply query the view rather than the underlying tables. The partitioned view will also provide performance benefits because it will direct the optimizer to only query the table that contains the needed rows.

 

 

 

Namaste!

 

Joseph

Leave a Reply

Your email address will not be published. Required fields are marked *

*

This site uses Akismet to reduce spam. Learn how your comment data is processed.