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

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » SS2K5 Replication » Need to replicate multiple databases...

16 posts, Page 1 of 2

1 2 »»

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

Rate Topic

Display Mode

Topic Options

Author

Message

Vijay Patel-478887

Posted Friday, November 16, 2007 9:15 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 1:43 PM
Points: 17, 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....

Post #423112

Chris Becker

Posted Monday, November 19, 2007 12:50 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #423783

Vijay Patel-478887

Posted Tuesday, November 20, 2007 7:44 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 1:43 PM
Points: 17, 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....

Post #424113

Chris Becker

Posted Tuesday, November 20, 2007 8:23 AM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #424149

Vijay Patel-478887

Posted Tuesday, November 20, 2007 9:52 AM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 1:43 PM
Points: 17, 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.....

Post #424212

Chris Becker

Posted Tuesday, November 20, 2007 12:16 PM

SSC Veteran

Group: General Forum Members
Last Login: Friday, February 22, 2013 11:49 AM
Points: 220, Visits: 216

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

Post #424285

Vijay Patel-478887

Posted Tuesday, November 20, 2007 2:16 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, May 19, 2009 1:43 PM
Points: 17, Visits: 66

Hey Chris, That worked great. Thanks for all your help.....

Post #424328

noeld

Posted Tuesday, November 20, 2007 3:14 PM

SSCertifiable

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 4:39 PM
Points: 6,260, Visits: 1,977

Warning! you shoul make sure that :

change distribution profile to 'Continue on data consistency errors'

is what you really want ...

* Noel

Post #424337

alen teplitsky

Posted Wednesday, November 21, 2007 7:05 AM

Ten Centuries

Group: General Forum Members
Last Login: Today @ 1:53 PM
Points: 1,409, Visits: 4,508

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

https://plus.google.com/100125998302068852885/posts?hl=en
http://twitter.com/alent1234
x-box live gamertag: i am null
[url=http://live.xbox.com/en-US/MyXbox/Profile[/url]

Post #424583

Ed7

Posted Monday, November 26, 2007 3:20 PM

Old Hand

Group: General Forum Members
Last Login: 2 days ago @ 10:00 PM
Points: 347, Visits: 888

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

Post #426066

« Prev Topic | Next Topic »

16 posts, Page 1 of 2

1 2 »»

Permissions