November 16, 2007 at 9:15 am
I was wondering if there is anyway to replicate mutiple databases (database with the same schema on different servers) to one central server. In otherword create multiple snapshots to point to one database without overriding or deleting any data. Thanks in advance....
November 19, 2007 at 12:50 pm
Snapshot repl will be tricky. I suggest using transactional replication - then only changes are delivered individually - you can even set distribution agent schedule to run once per day similar to snapshot schedule.
Apply snapshot initially w/ nosync - perhaps easier to control initially if you manually syncronize. Then setup pull subscriptions - this way all you repl agents will run on your 1 subscriber for easier mgt.
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 20, 2007 at 7:44 am
Yes i am using Transactional Replication using an initial snapshot to create the sync. The issue im having problems with is that i have 2 databases which have the same tables and schema but have different data. Some primary keys will also be the same in both databases. How can i replicate both of these at the same time to one database without overriding each others data. I created 2 publications on both databases and pointed it to the same subscriber but only the last publication will replicate, not the first. Is there a way for both to point to the same subsciber. Thanks in advance....
November 20, 2007 at 8:23 am
Yes you can do this.
When you configure - do not allow snapshot agent to initialize. Select subscriber already has schema & data - do this for both publishers and manually initialize data yourself.
Why is second not replicating? Do you have an error message? Do the same primary keys exist on both servers?
ChrisB
Chris Becker bcsdata.net
November 20, 2007 at 9:52 am
No error show up. And yes both databases may have the same primary key in some tables. I dont know what im missing here or just taking the wrong approach. This is the process i have used to replicate
------------------------------------------------------------------------------
1. Created a publication for DB1 (I created a snapshot to set up the initial sync)
2. Created a subscribtion for DB1 to point to ReplicationDB.
---Tables replicated just fine-------------------------------------
---Now need to replicate another similar database to the ReplicationDB----------
3. Created another publication for DB2 (I did not create a snap shot for this as i have already done that)
4. Created another subscription for DB2 to point to ReplicationDB.
When i leave it like this, DB2 will not replicate as it is still waiting for an initial snapshot or something to get it going. I could not find the a place to select a setting where the subscriber already had data and schema.
If i create a snapshot for DB2 it will replace all the data in ReplicatationDB and DB2 will replicate fine. But not both
I am also testing this on one server. You think that would cause any issues?.....
Again thanks in adavance for your time.....
November 20, 2007 at 12:16 pm
No worries. You are almost there. I don't know if you are SQL2000 or 2005 - so I will include both scripts to create a no-sync subscription (see below). Kill any existing subscription from DB2 to central subscriber.
If you have PK values that are the same you can do 1 of the following:
1) drop PK contraints at subscriber
2) change distribution profile to 'Continue on data consistency errors'
- this will ignore PK violation errors and skip, meaning command will not replicate.
However, this may not be a problem if you let snapshot agent initialize from db1 - it probably didn't create pk's and other contraints...
-- SQL2000 syntax
exec sp_addsubscription @publication = 'PUBNAMEHERE'
, @article = 'all'
, @subscriber = 'SUBSCRSERVERNAME'
, @destination_db = 'DBNAME'
, @sync_type = 'none' -- no snapshot necessary
GO
-- SQL2005 syntax
exec sp_addsubscription @publication = 'PUBNAMEHERE'
, @article = 'all'
, @subscriber = 'SUBSCRSERVERNAME'
, @destination_db = 'DBNAME'
, @sync_type = 'none' -- no snapshot necessary
, @reserved='internal'
GO
Fire up your distribution agent (if it is not already) - and enjoy...
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 20, 2007 at 2:16 pm
Hey Chris, That worked great. Thanks for all your help.....
November 20, 2007 at 3:14 pm
Warning! you shoul make sure that :
change distribution profile to 'Continue on data consistency errors'
is what you really want ...
* Noel
November 21, 2007 at 7:05 am
you can also use different table names on the subscriber
we have an old legacy db from a business partner that is slowly going away with the same db/table names as our main database and we replicate it to the same subscribers. we just change the destination table on the publication properties to be a different name
November 26, 2007 at 3:20 pm
Hi Christ,
Looking at your script
---SQL2000 syntax
exec sp_addsubscription @publication = 'PUBNAMEHERE'
, @article = 'all'
, @subscriber = 'SUBSCRSERVERNAME'
, @destination_db = 'DBNAME'
, @sync_type = 'none' -- no snapshot necessary
GO
-- SQL2005 syntax
exec sp_addsubscription @publication = 'PUBNAMEHERE'
, @article = 'all'
, @subscriber = 'SUBSCRSERVERNAME'
, @destination_db = 'DBNAME'
, @sync_type = 'none' -- no snapshot necessary
, @reserved='internal'
GO
I am very interested and have some questions.
Sorry my English not so good but I will try to explains as clear as I can.
1. I setup Transaction replication between sql server 2005 (Subscriber) and sql server 2000 (Publisher)
2. It works fine for about 1 years.
3. Running into space issue on Subscriber database server.
4. We decided to wipe out and install a new O/S by adding addition RAID 1 + 0
5. If I am going to restore the old databases on Subscriber and re-setup the transactional replication without initial snapshoot.
Questions.
--- Can your script apply to my case.?
Thank you for your help and kind advise.
TJ
November 26, 2007 at 3:27 pm
Certainly - make sure all you repl stored procedures are in subscriber db. If lost you can recreate by executing the following on publisher, then take output and exec on subscriber: exec sp_scriptpublicationcustomprocs @publication = 'PubNameHere'
Also - good idea to initially set distribution agent profile to 'continue on data consistency errors..' - you will need to perform manual sync to make sure subscriber in sync w/ publisher.
Good luck.
ChrisB MCDBA
MSSQLConsulting.com
Chris Becker bcsdata.net
November 26, 2007 at 3:33 pm
Chris,
Apologies for misspelled your name!!!
Here is my database replication scripts:
use master
go
--- Create an entry on the sysserver tables, mark it as the Distributor
--- and stores property information.
--- heartbeat_interval in minutes
EXEC sp_adddistributor
@distributor = 'DistributorServerName',
@heartbeat_interval = 20, --- 20 minutes
@password = 'Password'
go
--- Create a new distribution database and install the Distribution schema
exec sp_adddistributiondb
@database = 'Tobe_Named_Distribution_Center',
@data_folder = 'D:\BnB_ReplData\sql_data',
@data_file = 'Tobe_Named_Distribution_Center.MDF',
@data_file_size = 100,
@log_folder = 'D:\BnB_ReplData\sql_log',
@log_file = 'Tobe_Named_Distribution_Center.LDF',
@log_file_size = 25,
@min_distretention = 24,
@max_distretention = 72,
@history_retention = 72,
@security_mode = 0,
@login = 'LoginID',
@password = 'Password'
GO
--- Configure a Publisher to use a specified distribution database
exec sp_adddistpublisher
@publisher = 'PublisherServerName',
@distribution_db = 'Tobe_Named_Distribution_Center',
@security_mode = 0,
@login = 'LoginID',
@password = 'Password',
@working_directory = N'\\MahcineName\D$\BnB_ReplData',
@trusted = 'true',
@thirdparty_flag = 0
GO
-- Add a subscriber server
EXEC sp_addsubscriber @subscriber = 'Subcriber Server Name'
,@type = 0
,@login = 'LoginID'
,@password = 'Password'
,@description = 'Subcriber Server Subscription'
,@security_mode = 0
GO
sp_addsubscriber_schedule @subscriber = 'Subcriber Server Name'
,@agent_type = 0
,@frequency_type = 4
,@frequency_interval = 1
,@frequency_relative_interval =1
,@frequency_recurrence_factor = 0
,@frequency_subday = 4
,@frequency_subday_interval = 10
,@active_start_time_of_day = 000700
,@active_end_time_of_day = 235959
,@active_start_date = 20071114
,@active_end_date = 99991231
GO
----- Need to switch the database
USE [SiteData]
GO
--- STEP 1 Configure the server/database for Snapshot replication
EXEC sp_replicationdboption @dbname = 'DabaseSiteData',
@optname = 'publish', @value = 'True'
GO
--- STEP 2 Creating and configuring the Snapshot Agen
--- Set a replication database option for the current database
-- Adding the transactional publication
exec sp_addpublication @publication = N'DatabaseSiteData',
@restricted = N'false',
@sync_method = N'native',
@repl_freq = N'continuous',
@description = N'Transactional publication of SiteData database from Publisher GHS0017RPT.',
@status = N'active',
@allow_push = N'true',
@allow_pull = N'true', @allow_anonymous = N'false',
@enabled_for_internet = N'false',
@independent_agent = N'false',
@immediate_sync = N'false',
@allow_sync_tran = N'false',
@autogen_sync_procs = N'false',
@retention = 336,
@allow_queued_tran = N'false',
@snapshot_in_defaultfolder = N'true',
@compress_snapshot = N'false',
@ftp_port = 21, @ftp_login = N'anonymous',
@allow_dts = N'false',
@allow_subscription_copy = N'false',
@add_to_active_directory = N'false',
@logreader_job_name = N'GHS0017RPT-SiteData-1'
GO
exec sp_addpublication_snapshot
@publication = N'DatabaseSiteData',
@frequency_type = 4,
@frequency_interval = 1,
@frequency_relative_interval = 1,
@frequency_recurrence_factor = 0,
@frequency_subday = 8,
@frequency_subday_interval = 1,
@active_start_date = 0,
@active_end_date = 0,
@active_start_time_of_day = 0,
@active_end_time_of_day = 235959,
@snapshot_job_name = N'BlaBlaBla'
GO
--- Adds a login to the publication's access list
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'LoginID'
GO
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'BUILTIN\Administrators'
GO
exec sp_grant_publication_access
@publication = N'DabaseSiteData',
@login = N'distributor_admin'
GO
---- Stop di sini
-- Adding the transactional articles
exec sp_addarticle @publication = N'DabaseSiteData',
@article = N't_EditTags',
@source_owner = N'dbo',
@source_object = N't_EditTags',
@destination_table = N't_EditTags',
@type = N'logbased',
@creation_script = null,
@description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x000000000000CEF3,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_t_EditTags',
@del_cmd = N'CALL sp_MSdel_t_EditTags',
@upd_cmd = N'MCALL sp_MSupd_t_EditTags',
@filter = null, @sync_object = null,
@auto_identity_range = N'false'
GO
August 27, 2010 at 1:24 am
Thanks a million ChrisB !! You are awesome.. I had the similar issue and got resolved...
August 27, 2010 at 5:10 am
Chris if you can solve this..
http://www.sqlservercentral.com/Forums/Topic976280-291-1.aspx
August 27, 2010 at 10:22 am
Whenever you have a central subscriber topology I recommend changing the default behavior for what to do if the table articles already exist at the subscriber. To do this open the publication's properties, select the articles page, right click on each article and choose set properties of this table article (or choose set properties of all table articles, but be careful as this applies to all articles and may change custom settings you've already set up for other table articles). In the Destination Object section change the property for Action if name is in use to Keep existing object unchanged.
Setting this property will allow you to apply the snapshot from each publisher as its added to the replication topology. Of course that assumes that you won't generate PK errors (which the OP suggested may happen). One way to avoid this in a central subscriber topology is to put a composite PK on each replicated table which includes a sysname column for the server name (with a default of @@servername). The added benefit is that you can tell where the row originated from when you're looking at the data on the central subscriber.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply