December 21, 2009 at 2:52 pm
Hi,
I'm trying to set up transactional replication between 2 transactional database instances, both SS2005, but am new to this. If I understand correctly, I set up a distributor which manages the transactions from both databases. Do I then set up publishers on both database instances, and let the distributor handle them?
The 2 instances will handle the selling of theater tickets. It seems like a merge publication isn't right, because the timing of the updates don't occur continuously. So would this be a Transactional publication with updateable subscriptions, or a Transaction publication on each server? And if so, do all of these tables have to have primary keys? This is a 3rd party app, and many of them don't have PKs. It'll have the capability to update quickly, as one of the databases is from a POS terminal, and the other is from a web app. We obviously don't want to sell tickets on the web that have been sold at the POS terminal.
Thanks in advance for any suggestions,
Steve
December 21, 2009 at 4:04 pm
smithsp17 (12/21/2009)
Hi,I'm trying to set up transactional replication between 2 transactional database instances, both SS2005, but am new to this. If I understand correctly, I set up a distributor which manages the transactions from both databases. Do I then set up publishers on both database instances, and let the distributor handle them?
The 2 instances will handle the selling of theater tickets. It seems like a merge publication isn't right, because the timing of the updates don't occur continuously. So would this be a Transactional publication with updateable subscriptions, or a Transaction publication on each server? And if so, do all of these tables have to have primary keys? This is a 3rd party app, and many of them don't have PKs. It'll have the capability to update quickly, as one of the databases is from a POS terminal, and the other is from a web app. We obviously don't want to sell tickets on the web that have been sold at the POS terminal.
Thanks in advance for any suggestions,
Steve
It seems to me that a merge replication scenario would be more appropriate for this scenario. However, the updateable subscriptions would also work.
You will need to have primary keys. You will also need to have GUIDs. I would be most concerned about altering the 3rd party db however. Should they decide you need to upgrade the db, what will that do to any changes you make to the db?
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 21, 2009 at 7:42 pm
I would not use Merge Replication for the scenario you are describing because there is a very real chance that the same ticket can be sold since merge replication does introduce significant delays (by default 2 minutes). When things get busy I think that this will burn you.
Transaction replication with Immediate Updating Subscribers is a better choice in this scenario.
December 22, 2009 at 6:54 am
Am I correct in assuming that Transaction replication with Immediate Updating Subscribers also requires primary keys to be added to each table, or at least each table that gets updated during a transaction?
December 22, 2009 at 10:30 am
smithsp17 (12/22/2009)
Am I correct in assuming that Transaction replication with Immediate Updating Subscribers also requires primary keys to be added to each table, or at least each table that gets updated during a transaction?
Yes
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
December 23, 2009 at 2:40 am
For transaction replication with updateable subscriptions, your table must have a primary key
The tables will also need a uniqueidentifier column called "msrepl_tran_version" on each table. It may be the primary key but this is not necessary. However, this column must be there - when the publication is created, replication may add it to the table.
This may cause problems for the application using the tables being replicated especially if it does INSERTs without a column list. You will need to test your application(s) with replication to confirm whether there are problems because of this new column.
I haven't done a lot with updatable subscriptions but looking at the table definitions that replications left me with from a quick test, I think you would also need to
- consider an index on msrepl_tran_version
- review the UPDATE trigger it creates - I am not convinced that it really needs to exist but I haven't done much testing on it
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply