May 17, 2011 at 7:48 pm
Can someone comment cons of sql server replication? Obvious things to me are additional server hardware and administrative overhead, but I would like to hear from some who have worked with the technology.
what happens when more then 1 user is working on the same record?
Are there concerns about network latency over a WAN environment?
Also are there any ramifications administratively? For instance when creating new logins on one server does that information get replicated to the other server.
I appreciate the feedback.
May 17, 2011 at 10:04 pm
Additional server hardware is not necessary. You can use Publisher as the Distributor (if it can bear the load).
No logins are replicated. You have to create them yourself.
Few observations:
1) Primary key is required for transactional replication
2) Snapshot creation needs table locking
May 18, 2011 at 6:41 am
Suresh B. (5/17/2011)
Additional server hardware is not necessary. You can use Publisher as the Distributor (if it can bear the load).No logins are replicated. You have to create them yourself.
Few observations:
1) Primary key is required for transactional replication
2) Snapshot creation needs table locking
Suresh,
Thanks for the response.
If a primary key exists but is not the identity field does that matter?
is the snapshot only created once, during the initial stages of the database replication procedure?
Also does the front end application need any special tie in to the database engine such as a .net component in order to detect and remedy conflicts?
Thanks for your help.
May 18, 2011 at 7:44 am
kwoznica (5/18/2011)
Thanks for the response.
If a primary key exists but is not the identity field does that matter?
is the snapshot only created once, during the initial stages of the database replication procedure?
Also does the front end application need any special tie in to the database engine such as a .net component in order to detect and remedy conflicts?
Thanks for your help.
So long as the table contains a primary it doesnt matter.
A snapshot is only required to initialise a subscriber. After this you will only need to generate small snapshots when a publication is generated. You "could" get away without a snapshot but I wouldnt recommend this unless you have plenty of time to test in your case.
Assuming you are consigering merge replication, the front end does not need to be aware of conflicts. If this is going to work reliably and you need to understand the ins and outs of the system.
For merge to work well, the system ideally needs to be transactional and updates to existing records kept to a minimum. You will have to configure the publication retention period to retain row version history for the amount of time you expect a row to updated over before it becomes static. If a row is edited outside of this retention period it automatically becomes a conflict as SQL cannot be certain which row is more recent. You could get round this with a custom resolver in theory. This is a known feature to prevent uncontrolled, infinite distribution database growth.
May 18, 2011 at 8:13 am
MysteryJimbo (5/18/2011)
So long as the table contains a primary it doesnt matter.
A snapshot is only required to initialise a subscriber. After this you will only need to generate small snapshots when a publication is generated. You "could" get away without a snapshot but I wouldnt recommend this unless you have plenty of time to test in your case.
Assuming you are consigering merge replication, the front end does not need to be aware of conflicts. If this is going to work reliably and you need to understand the ins and outs of the system.
For merge to work well, the system ideally needs to be transactional and updates to existing records kept to a minimum. You will have to configure the publication retention period to retain row version history for the amount of time you expect a row to updated over before it becomes static. If a row is edited outside of this retention period it automatically becomes a conflict as SQL cannot be certain which row is more recent. You could get round this with a custom resolver in theory. This is a known feature to prevent uncontrolled, infinite distribution database growth.
If I look at profiler data I often see If @@trancount > 0 commit tran but I also see alot of exec sp_cursoropen then exec sp_cursorclose statements.
Such as the code below.
declare @p1 int
set @p1=180151269
declare @p3 int
set @p3=2
declare @p4 int
set @p4=1
declare @p5 int
set @p5=0
exec sp_cursoropen @p1 output,N'SELECT * FROM "GK50LIVE"."dbo"."GK Live$Sales Header" WITH (READUNCOMMITTED) WHERE (("Document Type"=@P1)) AND (("No_"=@P2)) AND "Document Type">@P3 ORDER BY "Document Type","No_" ',@p3 output,@p4 output,@p5 output,N'@P1 int,@P2 varchar(20),@P3 int',0,'SQ-470880',0
select @p1, @p3, @p4, @p5
Is this an indicator of the database being transactional?
I don't see the application code so its difficult for me to discern.
what if updates to existing records are frequent and what problems does that incur?
May 18, 2011 at 8:40 am
Can't tell by that that example as it is a select. Generally you can tell by the table structures and relationships or you will have to ask the application owner/developer.
Multiple updates arent a problem generally. The issues can occur if updates occur after a long period of time once the version history has been removed.
May 18, 2011 at 9:25 am
MysteryJimbo (5/18/2011)
Can't tell by that that example as it is a select. Generally you can tell by the table structures and relationships or you will have to ask the application owner/developer.Multiple updates arent a problem generally. The issues can occur if updates occur after a long period of time once the version history has been removed.
Many of the tables do not have relationships. It is a poorly developed database, not by me, and is not normalized.
If it is not transaction would just snapshot replication still be possible?
May 20, 2011 at 1:44 pm
Also I hate to ask a stupid question but if a database is using the full recovery model isnt it then transactional?
If it isn't then what determines if a database is transactional? code which explicitely states transacational isolation levels?
May 23, 2011 at 9:12 am
bump
May 23, 2011 at 9:39 am
I was implying transactional as a design concept which can help minimise the number of updates.
May 24, 2011 at 6:13 am
When u want to decide the type of replication,
1)go with snapshot replication when data changes are substantial and infrequent
2)Transactional replication can be useful in an environment where Subscribers must receive data changes as they occur with minimal latency
3)Merge replication - when multiple Subscribers need to update data at various times and propagate those changes to Publisher and to the other Subscribers.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply