SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Need to replicate multiple databases (publications) to one central subscriber


Need to replicate multiple databases (publications) to one central subscriber

Author
Message
Vijay Patel-478887
Vijay Patel-478887
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 66
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....
Chris Becker
Chris Becker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 279
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



Vijay Patel-478887
Vijay Patel-478887
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 66
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....
Chris Becker
Chris Becker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 279
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



Vijay Patel-478887
Vijay Patel-478887
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 66
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.....
Chris Becker
Chris Becker
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1279 Visits: 279
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



Vijay Patel-478887
Vijay Patel-478887
SSC Veteran
SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)SSC Veteran (287 reputation)

Group: General Forum Members
Points: 287 Visits: 66
Hey Chris, That worked great. Thanks for all your help.....
noeld
noeld
SSC-Forever
SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)SSC-Forever (43K reputation)

Group: General Forum Members
Points: 43720 Visits: 2052
Warning! you shoul make sure that :

change distribution profile to 'Continue on data consistency errors'


is what you really want ...


* Noel
alen teplitsky
alen teplitsky
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13973 Visits: 4693
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
Ed7
Ed7
Hall of Fame
Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)Hall of Fame (3.8K reputation)

Group: General Forum Members
Points: 3846 Visits: 940
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search