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