March 19, 2014 at 5:49 am
Hi all, I am not a DBA but I have been tasked to investigate and setup bi-directional transactional replication. This will only involve 2 servers. I am struggling to know where to start and need to know the answer to a very troubling question. All of the tables have a primary key that is of GUID data type. These GUID's are only generated from .NET code. Is this likely to be an issue? The reason for needing replication is because there is considerable and resource intensive batch processing required that would have performance issues for the end facing website but the website also allows for some data input that would be required for the continual data processing hence why I need to replicate both ways. Does anyway have any thoughts on the feasibility of this setup or can point to any solid tutorials?
March 19, 2014 at 2:56 pm
I'm not sure how you're application architecture is arranged. The issue that you could see is if there's a duplicate GUID in both locations. If your .net app only generates random and unique keys that are impossible to duplicate, you should be fine. If there's a chance of duplication, you're going to have issues and you'll see errors related to duplicate values in that field when the replication occurs.
Here's a great article on Transactional Replication written by Hilary Cotter.
March 20, 2014 at 3:34 am
This depends on your definition of bi-directional. If the website is writing to unique tables you can publish a subset of the db in each direction making for what is effectively two one way publications. If the data modifications are to the same data on both side then you need bi-directional (peer-to-peer transactional replication).
As you are using GUIDs these are almost certainly unique, not guaranteed but very close, this wont be a problem for replication. I do suggest you research the appropriateness of GUIDs in DB design as these can have a serious negative impact.
Have you considered if merge replication is better suited or is near real time the goal?
Replication can be a bit of a dark art to do right and it isnt something you should do without doing any research. You need to know how to fix problems efficiently after all and have a good understanding as to how it works.
March 20, 2014 at 3:36 am
http://www.mssqltips.com/sqlservertip/2494/overview-of-peertopeer-transactional-replication-in-sql-server-2008/[/url]
http://technet.microsoft.com/en-us/library/ms152536(v=sql.105).aspx
http://www.sqlskills.com/blogs/paul/sql-server-2008-configuring-peer-to-peer-replication/[/url]
March 20, 2014 at 4:24 am
This sounds like something I had to do many years ago. Back then I used Transactional Replication with Updateable Subscriptions. Might be worth a look.
March 20, 2014 at 4:30 am
humbleDBA (3/20/2014)
This sounds like something I had to do many years ago. Back then I used Transactional Replication with Updateable Subscriptions. Might be worth a look.
I've used this before as well. I'm not a fan really as its not true replication and has its issues. It makes extensive use of linked servers and add potential problems.
March 20, 2014 at 8:42 am
MysteryJimbo (3/20/2014)
humbleDBA (3/20/2014)
This sounds like something I had to do many years ago. Back then I used Transactional Replication with Updateable Subscriptions. Might be worth a look.I've used this before as well. I'm not a fan really as its not true replication and has its issues. It makes extensive use of linked servers and add potential problems.
It is 'true' replication as it uses Transactional Replication. The main difference is that when the subscriber updates, it makes use mechanism to re-direct the subscriber update to the publisher DB (-it may also make use of a queuing mechanism too), which then pushes out the update to the subscriber's DB, and any other subscribers, as per a transaction. Yes it can use Linked-Servers, it can also use Romote Servers (tho can't remember if this has been depricated). As for potential problems...it would not surprise me that whatever mechanism the OP ends up using, that mechanism will have potential problems.
As for when I last used T-Replication with Subscriber Updating and Queued Updating - which, granted, was a few years ago - it was for a world-wide payments transactional system for an online video games company and allowed separation from the main Player account system for the continent-based game shards and it worked very well 'for our needs', and it was robust and solved a problem for us.
At the end of the day, the OP has asked for thoughts and I think this method is worth looking at, as one of several possible solutions, to solve their problem.
March 20, 2014 at 9:13 am
Thanks for all your input, guess I have a lot of homework to do. I think I will setup a couple of labs and start practicing, may as well give SQL 2014 CTP2 a try.
March 20, 2014 at 9:27 am
humbleDBA (3/20/2014)
It is 'true' replication as it uses Transactional Replication. The main difference is that when the subscriber updates, it makes use mechanism to re-direct the subscriber update to the publisher DB (-it may also make use of a queuing mechanism too), which then pushes out the update to the subscriber's DB, and any other subscribers, as per a transaction. Yes it can use Linked-Servers, it can also use Romote Servers (tho can't remember if this has been depricated). As for potential problems...it would not surprise me that whatever mechanism the OP ends up using, that mechanism will have potential problems.
It does not require or used a logreader or distribution agent on the subscriber, therefore it is not 'true' bi-directional replication in my opinion.
The two phase commit with updateable subscriptions uses linked servers for immediate and/or queued updating. This is the mechanism it uses. If i remember rightly, they are marked as is_system in sys.servers and cannot be used for anything else.
http://technet.microsoft.com/en-us/library/ms146922(v=sql.105).aspx
-- Add a Windows Authentication-based linked server that enables the
-- Subscriber-side triggers to make updates at the Publisher.
EXEC sp_link_publication
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@security_mode = 0,
@password = @password;
GO
The documentation on the updateable subscriptions "mechanism" is pretty vague but with my testing and investigation this was true. This was implemented across around 300+ finance sites, some globally. Yes it works for the most part. Would I use it again? I'm not sure. Given that its marked for deprecation, I wouldn't recommend it.
March 20, 2014 at 9:33 am
Also worth pointing out that I remember switching to queued updating because of performance problems when using updateable subscriptions. The cross network traffic wasn't keeping up.
March 21, 2014 at 4:03 am
MysteryJimbo (3/20/2014)
humbleDBA (3/20/2014)
It is 'true' replication as it uses Transactional Replication. The main difference is that when the subscriber updates, it makes use mechanism to re-direct the subscriber update to the publisher DB (-it may also make use of a queuing mechanism too), which then pushes out the update to the subscriber's DB, and any other subscribers, as per a transaction. Yes it can use Linked-Servers, it can also use Romote Servers (tho can't remember if this has been depricated). As for potential problems...it would not surprise me that whatever mechanism the OP ends up using, that mechanism will have potential problems.
It does not require or used a logreader or distribution agent on the subscriber, therefore it is not 'true' bi-directional replication in my opinion.
The two phase commit with updateable subscriptions uses linked servers for immediate and/or queued updating. This is the mechanism it uses. If i remember rightly, they are marked as is_system in sys.servers and cannot be used for anything else.
http://technet.microsoft.com/en-us/library/ms146922(v=sql.105).aspx
-- Add a Windows Authentication-based linked server that enables the-- Subscriber-side triggers to make updates at the Publisher.
EXEC sp_link_publication
@publisher = @publisher,
@publication = @publication,
@publisher_db = @publicationDB,
@security_mode = 0,
@password = @password;
GO
The documentation on the updateable subscriptions "mechanism" is pretty vague but with my testing and investigation this was true. This was implemented across around 300+ finance sites, some globally. Yes it works for the most part. Would I use it again? I'm not sure. Given that its marked for deprecation, I wouldn't recommend it.
OK, I'm not intending to get into an arguement about this. When I say it is 'true' replication I mean it in the sense that the change is actually done at the publisher and pushed out to the subscribers, including the subscriber where the change took place. The difference is, and I agree with you on this, is that the mechanism of getting the data to the Publisher is not true bi-directional Transactional Replication - but then I never said it was 'true' Bi-Directional Transaction Replication. But the fact that the Publisher is the mechanism for the actual updating of the table(s) and and Transactional Replication is the mechanism for getting the all Subscriber tables all updated with the change is, imho, 'true' Transactional Replication.
As for deprication, yes, that would be a concern that would have to be considered, but the OP has not mentioned what SQL version they intend to go for, and as the OP has placed the thread in the SQL 2008 - General thread group, then I still think this would be something worth considering as it is still valid in SQL2008.
It would be fair to say that the OPs question is very open and they were asking for options. I haven't said 'you must use this', I've simply given an option for the OP to explore, along with other suggestions, against their more detailed list of requirements will be. And I'm sure that the OP will come across T-Replication with Subscriber Updates as they look further into what may be the best option.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply