SQL Replication demystified

re: SQL Replication demystified

Kalen Delaney — Wed, 26 Dec 2012 23:38:55 GMT

SQL Server Central has a StairWay tutorial on replication, you might want to point that out people also.

http://www.sqlservercentral.com/stairway/72401/

~Kalen

re: SQL Replication demystified

Rob Farley — Wed, 26 Dec 2012 23:52:46 GMT

Thanks Kalen.

Readers: Sebastian Meine has written a series on replication that you can see over at SQLServerCentral.com. Check out the link in Kalen's comment for more.

re: SQL Replication demystified

Chris Adkin — Thu, 27 Dec 2012 12:42:10 GMT

Hi Rob,

There is absolutely nothing wrong with a 101 level article. I think the thing that confuses people the most with replication is the fact that there is only so much you can get out of management studio, when things go awry. The other main stumbling block is with conflicts encountered with merge replication, particularly when identities are used to seed primary keys, Hillary Cotter's "Identity crisis" article which can be found on simple-talk is an excellant reference for dealing with this.

I hope you and your family are having a happy Christmas.

Chris

re: SQL Replication demystified

Rob Farley — Thu, 27 Dec 2012 12:50:41 GMT

Yes, I think there are a number of things which people struggle with around replication, and hopefully this can help people grasp the basic concepts. Maybe I'll do a post on conflict resolution soon.

re: SQL Replication demystified

Francesco Quaratino — Fri, 28 Dec 2012 10:54:51 GMT

I Rob, I agree with you: SQL Replication is nothing to worry about.

On the other hand, I think it's quite complex to implement in a big production environment. I've got some experience of Transactional Replication but, apart from the key concepts, all I know about it I've learned on the job (mistakes included of course).

Can you suggest any good advanced self-training book about SQL replications?

Thanks

re: SQL Replication demystified

Rob Farley — Fri, 28 Dec 2012 12:00:10 GMT

Hi Francesco - I've never read a book on replication, so I can't really recommend one. Sorry.

re: SQL Replication demystified

Francesco Quaratino — Fri, 28 Dec 2012 13:34:40 GMT

Bad luck. Someone should start thinking of writing a good book about that subject.

re: SQL Replication demystified

Scott Crosby — Sat, 29 Dec 2012 16:18:52 GMT

For more SQL replication info checkout Paul Ibison's excellent site: www.replicationanswers.com

Hilary Cotter's SQL 2000 replication book was invaluable to me, a possible update required?

Rgds,

Scott

re: SQL Replication demystified

John Sansom — Mon, 31 Dec 2012 10:28:22 GMT

@Francesco - There's an excellent chapter, by Hilary Cotter in the first SQL Server MVP Deep Dives book, that looks at performance tuning Transactional Replication topologies. It contains the best (IMHO) explanation of how to tune the performance of the various Replication agents. (http://www.manning.com/nielsen/)

For general replication performance and tuning knowledge, see the REPLTLK blog on MSDN (http://blogs.msdn.com/b/repltalk/)

There are some replication performance tuning guides out there too, buried in various blogs however, I don't have the references to hand. I'll see what I can dig up....

re: SQL Replication demystified

Francesco Quaratino — Wed, 02 Jan 2013 10:12:06 GMT

Thanks @Scott & @John.

I've never read that book but I knew already it was a really good one :)

about guides, I've found this quite usefull "Achieving Excellence in Designing and Maintaining SQL Server Transactional Replication Environments" (http://technet.microsoft.com/en-us/library/jj215849.aspx)

re: SQL Replication demystified

Brandon Williams — Tue, 08 Jan 2013 17:23:47 GMT

This one is good for beginners to intermediate: Pro SQL Server 2008 Replication (http://www.amazon.com/Server-2008-Replication-Experts-Voice/dp/143021807X)

re: SQL Replication demystified

dave pint — Fri, 08 Feb 2013 20:30:10 GMT

thanks all for the great info.

hope this is the place for my question.

should peer to peer transactional replication ( with updates from peers enabled ) withstand network disconnects easily ?

we are using it in an application, set up sqlprime as the primary and sqlsecond as the failover server

all goes well until we test the redundancy by disconnecting the network on sqlprime .

the app find the failover, writes data but when sqlprime ( primary server) comes back, all replication is stopped with strange error ( mainly directing to login failures ( which are bogus as passwords did not change. )

so , are we testing something this setup should not stand ? ( network interruptions ? )

thanks

dave.

re: SQL Replication demystified

Rob Farley — Fri, 08 Feb 2013 23:35:49 GMT

Hi Dave,

It should be able to resume. It will be helpful to see the actual errors.

Maybe drop me an email and I'll see what I can do. rob at lobsterpot.com.au is best.

Rob