April 16, 2012 at 8:18 am
Just wondered what would suit a rather common scenario, if I had multiple publishers and one main subscriber - and no bi-directional requirement, would transactional replication be the best route to look at?
Edit: The publishers will be identical databases (tables, etc etc ) but across seperate servers.
I had in my mind to use merge (the destination will most likely be read only) but I dont think it fully suits.
'Only he who wanders finds new paths'
April 16, 2012 at 8:32 am
Wow, I don't know how to answer this question. There really isn't enough information in order to provide you anything useful.
April 16, 2012 at 8:43 am
I know, appreciate the response - I am after the basic guidelines really of having multiple publishers. Essentially a number of servers will have their own version of a database all of which are required to be replicated to one central db.
I had in mind merge replication but to my understanding, that is best suited for multiple subscribers who would replicate back all updates to the original publisher.
If that is the case then is it feasible to have (in transactional repl) multiple publications of the same structure (but different data) to one central subscriber, with no bi-directional requirement? I guess each table would need a priomary key but distinct from all other primary keys on the other servers?
'Only he who wanders finds new paths'
April 16, 2012 at 10:05 am
My feelings are this:
1. Merge is best for keeping data "synced" between multiple database instances when connections are an issue; i.e. Sales people on tablets that do not have a constant connection to the network can sync up when they do have a connection.
2. Transactional replication is best when you ant to approximate real time transfer of data from multiple publishers to 1 or more subscribers AND can support the load of this during all business hours; i.e. 3 satellite sites that handle incoming data and all replicate to a central reporting database.
3. Snapshot is the same use as transactional, but you don't need close to real time data and you can schedule the snapshots at off-peak hours.
4. Transactional with Updateable Subscriptions work good for 1 publisher and 1 subscriber that need to keep in sync with close to real time data transfer and you can afford the load on the servers.
Transactional and Transactional with updateable subscriptions are based on my experience and usage in my company. My comments on merge and snapshot are based solely on my "understanding" of how they work and how they are typically used.
Jared
CE - Microsoft
April 16, 2012 at 11:40 am
Theres a couple key statements that caught my eye. So going on this limited information i'll try to help.
david.alcock (4/16/2012)
Just wondered what would suit a rather common scenario, if I had multiple publishers and one main subscriber
david.alcock (4/16/2012)
Edit: The publishers will be identical databases (tables, etc etc ) but across seperate servers.
If the databases are identical, wont you potentially get primary key violations on the single subscriber?
Have you considered swinging this around and have a single merge publisher and many subscribers? You can control write access on the publisher with relative ease.
April 16, 2012 at 11:48 am
david.alcock (4/16/2012)
Edit: The publishers will be identical databases (tables, etc etc ) but across seperate servers.If the databases are identical, wont you potentially get primary key violations on the single subscriber?
Have you considered swinging this around and have a single merge publisher and many subscribers? You can control write access on the publisher with relative ease.
My guess is that they are identical in structure, but not in terms of data; i.e. rules for primary keys can be managed by (just as an example) IDENTITY(1,3) on ServerA.database..table, IDENTITY(2,3) on ServerB.database..table, and IDENTITY(3,3) on ServerC.database..table. This is just one of many ways.
To the OP, we really need a business case here to help you out. What is the purpose of replication for your business? Reporting? Syncing? Failover (not recommended, but useful in certain cases)?
Jared
CE - Microsoft
April 16, 2012 at 12:15 pm
SQLKnowItAll (4/16/2012)
My guess.....
Exactly. 🙂
April 16, 2012 at 12:24 pm
SQLKnowItAll (4/16/2012)
My guess is that they are identical in structure, but not in terms of data; i.e. rules for primary keys can be managed by (just as an example) IDENTITY(1,3) on ServerA.database..table, IDENTITY(2,3) on ServerB.database..table, and IDENTITY(3,3) on ServerC.database..table. This is just one of many ways.
Personally, I think this adds too much management overhead on multiple servers. A few maybe it'll work. Much simpler to use a NEWSEQUENTIALID() if you must go this way. Not necessarily nice but easier to manage.
There are other factors to consider. Multiple publications to manage and keep in sync. Multiple places to change schemas (be careful of DDL replication, you cant replicate the same column twice to the same subscriber).
I'd much prefer a single merge publisher. One publication with articles ensuring all servers get the same replication. A single place to manage schema and publication changes and no messing around with avoiding data conflicts. You can filter the subscribers using hostname() and a column in one or more tables, then use Merge joins to enforce relational integrity.
Same result, less overhead in my opinion. K.I.S.S.
April 16, 2012 at 12:36 pm
Appreciated everyone.
The structures will be identical and yes, the data will be different. Key conflicts will be avoided by having unique key fields across all the servers. The design is very much theory at the moment...in case you hadnt noticed 🙂 Essentially we are replicting data out to a centralised db for further data procesing - reporting.
I do have a fair handle on replication, just havent implemented anything more than one way transactional replication really and my knowledge of merge isnt that particulary strong. I have always seen merge as best served for when data is replicated to for example mobile devices and then synced back again after update.
I wasnt sure if a merge solution would be more suitable than a multiple publisher one...I wasnt quite sure if a multi publisher scenario was possible really.
Regards
D
'Only he who wanders finds new paths'
April 16, 2012 at 12:47 pm
There are other factors to consider. Multiple publications to manage and keep in sync.
In my situation, and now that I know the OPs situation... This will still have to be managed separately, and data is not synced. So, I still think transactional.
Multiple places to change schemas (be careful of DDL replication, you cant replicate the same column twice to the same subscriber).
If these are 3 production servers the schema changes have to be coordinated any way. I wouldn't want the schema changes to be managed by replication in this scenario.
I'm not saying you are "wrong", of course. I just wanted to be specific on my thoughts for each point. I just think that transactional is easier to manage if you have multiple production servers that are fundamentally independent of the subscriber and each other (i.e. 1 site does not depend on the other for production). If they needed to be related/synced, I would think merge is more appropriate.
Jared
CE - Microsoft
April 16, 2012 at 11:20 pm
This sounds like transactional replication is the best option provided that you have a scheme in mind for ensuring any unique constraints (including primary keys) do not cause any conflicts on the subscriber. This could be as simple as managing identity ranges or including some form of location identifier in the key.
April 17, 2012 at 3:16 am
Here is my point of view on when to use which Replication Topology:
Snapshot Replication
Use snapshot replication when you want an occasional data push done. Because all data is moved each time the snapshot is pushed, it uses a good amount of bandwidth. Only use snapshot replication over a slow WAN if the amount of data changes to be replicated is greater than the size of the initial data set. In other words, if a large portion of the data is being updated over and over, snapshot will probably be the right technique. If that's not the case, then it won't be.
Merge Replication
Merge replication should be used when you need to transmit changes from the publisher to the subscriber, as well as from the subscriber back to the publisher. When you have multiple subscribers, in time the changes are replicated out from the publisher to all the subscribers in the network.
Transactional Replication
The transactional technique is probably the most common form of replication. It is used to transmit data changes in near real time (or on a schedule) to one or more subscribers. Transactional replication is most often used to move data from server to server in real time.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply