November 2, 2009 at 2:20 pm
Hi,
I need to setup a replication between 2 SQL servers in 2 differents geographical locations (in Florida and in Montreal). Server in Florida will handle the production and all data from these tables have to be replicate in Montreal. I created a Merge Replication between those 2, using a tutorial I got from the internet, and it works so far. Data added/deleted/updated in Florida are immediatly replicated in Montreal. In the publication's setup, I checked all tables to be part of the replication.
But server in Montreal also have data that have to be replicated in Florida. Those data are in differents tables. Can I use the same replication to sync those data as well? Or I have to create a new replication? Because right now, when I changed data in Montreal, they are not replicated in Florida...
As a note, I can't create a transactional replication because of our SQLServer licensing issue.
thanks a lot for your time and help
November 2, 2009 at 7:37 pm
To recap your scenario, you have
- tables that are updated in Florida that are replicated to Montreal and are never updated in Montreal
- tables that are updated in Montreal that you wish to replicated to Florida and Florida will not be updating these tables.
If this is the case, then I would go with Transactional Replication instead of Merge Replication. This would be implemented as 2 publications - one from each server. I am not aware of any licensing issues with this - exactly what is the issue.
Assuming that you continue with Merge Replication, yes, you can do it with one publication but I probably would go with 2 in you case (assuming that my understanding as above is correct).
November 3, 2009 at 6:13 am
happycat59 (11/2/2009)
To recap your scenario, you have- tables that are updated in Florida that are replicated to Montreal and are never updated in Montreal
- tables that are updated in Montreal that you wish to replicated to Florida and Florida will not be updating these tables.
If this is the case, then I would go with Transactional Replication instead of Merge Replication. This would be implemented as 2 publications - one from each server. I am not aware of any licensing issues with this - exactly what is the issue.
Assuming that you continue with Merge Replication, yes, you can do it with one publication but I probably would go with 2 in you case (assuming that my understanding as above is correct).
thanks for the answer!
ok, here is the recap:
- tables in Florida that are updated and replicated in Montreal, are also updated in Montreal but doesn't need to be replicated in Florida. Those tables are production related (new lot, reception, expedition, transit, etc)
- tables that are updated in Montreal that you wish to replicated to Florida and Florida will not be updating these tables. that's exact. those tables contains data like clients, products, brands. they are managed from Montreal who is the main office. of course, those data needs to be replicated in Florida, but they are not updated in Florida.
regarding Transactional Replication, I don't know why, but I can't select it when setting up the replication, I only have the snapshot or merge option. It stated that my actual SQL Server license forbid the creation of a transactional replication, although it's a full SQL Server 2000 license (not a MSDE and a legal version).
will you still go with 2 distincts publications at the light of my scenario??
thanks for your time. I really appreciate.
November 3, 2009 at 8:05 pm
Sounds like you are running SQL 2000 Personal Edition - this does not support Transactional Replication (http://msdn.microsoft.com/en-us/library/aa175266(SQL.80).aspx has details of features supported by each edition).
I would be a little wary of using this edition in a production environment since it does have resource governor - if you have more than 5 concurrent users, the performance of the server gets throttled.
I would still go with 2 publications - this keeps the tables being published grouped together by usage which may help to keep management of the publication simpler.
The tables that are being published from Monteal are definite candidate for Transaction Replication. I would investigate why you are using Personal Edition and, ideally, change this to standard or enterprise.
November 4, 2009 at 7:59 am
happycat59 (11/3/2009)
Sounds like you are running SQL 2000 Personal Edition - this does not support Transactional Replication (http://msdn.microsoft.com/en-us/library/aa175266(SQL.80).aspx has details of features supported by each edition).I would be a little wary of using this edition in a production environment since it does have resource governor - if you have more than 5 concurrent users, the performance of the server gets throttled.
I would still go with 2 publications - this keeps the tables being published grouped together by usage which may help to keep management of the publication simpler.
The tables that are being published from Monteal are definite candidate for Transaction Replication. I would investigate why you are using Personal Edition and, ideally, change this to standard or enterprise.
That's what I figured out yesterday about our SQL Server license. I tried to install the standard edition, but it gave an error about my invalid operating system (use XP Pro). So I guess I have to stick with the personnal edition (which sucks for not permitting transactionnal replication!).
If I can't setup a transactionnal replication, I'll try using a snapshot for the tables in Montreal. They can be replicated to Florida once a day, it'd enough.
thanks a lot for you help and replies
regards,
Dominic
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply