Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Need to replicate multiple databases (publications) to one central subscriber Expand / Collapse
Author
Message
Posted Friday, November 16, 2007 9:15 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Monday, November 19, 2007 12:50 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:22 PM
Points: 222, Visits: 246

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
Posted Tuesday, November 20, 2007 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 20, 2007 8:23 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:22 PM
Points: 222, Visits: 246

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
Posted Tuesday, November 20, 2007 9:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 20, 2007 12:16 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:22 PM
Points: 222, Visits: 246

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
Posted Tuesday, November 20, 2007 2:16 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Posted Tuesday, November 20, 2007 3:14 PM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,266, Visits: 2,029
Warning! you shoul make sure that :

change distribution profile to 'Continue on data consistency errors'


is what you really want ...



* Noel
Post #424337
Posted Wednesday, November 21, 2007 7:05 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 12:08 PM
Points: 1,414, Visits: 4,540
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
Posted Monday, November 26, 2007 3:20 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, May 21, 2014 3:22 PM
Points: 354, Visits: 915
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 »

Add to briefcase 12»»

Permissions Expand / Collapse