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.

clip_image002

 

  • 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