I've been told that SQL Server replication is buggy and unreliable

Hi, I've recently been told that SQL Server replication is buggy and unreliable. I find this very hard to believe. I'm working with a subcontractor in another state that is hosting a large DB for us. I want to replicate a read-only copy of the DB over
the internetl to our local site. They also tell me that it is difficult to recover when there is a problem with replication. I again find this hard to believe. The worst case it to re-snapshot.
I've been working with SQL Server since 2002, but not replication. I've found SQL Server to be very reliable and nice to work with.
I would greatly appreciate honest feedback.
Thanks very much,
Griff
Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Griff,
I've used it for 4 years and found it to be robust. I teach replication to
financial staff in London and it is widely used by insurance companies and
banks, which might make you feel more confident about its usefulness.
To be honest, almost always what I initially thought was a limitation in the
technology actually turned out to be me using the wrong implementation, or
not being aware of existing workarounds.
For someone getting started, BOL (and Hilary's upcoming books) should
provide the foundation needed to make correct choices. For troubleshooting
I'd advise anyone starting out to implement replication in as many different
ways as possible, see when you've broken it and then research this newsgroup
thoroughly.
HTH,
Paul Ibison
Also, I keep a scratchpad of errors/solutions I've seen, or read about and
tested at www.replicationanswers.com.
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>
|||Replication is not buggy per se.
the problem with replication is that it is depenedent on often reliable
links. Sometimes a LAN connection which is good enough for day to day use,
will turn out to be too unreliable or unstable for replication.
Replication is resilient to many errors, but not a poor connection.
Its akin to someone taking a gravel road and complaining about the car.
Another problem with replication is that it is a complex product that few
dba's really understand well.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eI8AqChXEHA.384@.TK2MSFTNGP10.phx.gbl...
> Griff,
> I've used it for 4 years and found it to be robust. I teach replication to
> financial staff in London and it is widely used by insurance companies and
> banks, which might make you feel more confident about its usefulness.
> To be honest, almost always what I initially thought was a limitation in
the
> technology actually turned out to be me using the wrong implementation, or
> not being aware of existing workarounds.
> For someone getting started, BOL (and Hilary's upcoming books) should
> provide the foundation needed to make correct choices. For troubleshooting
> I'd advise anyone starting out to implement replication in as many
different
> ways as possible, see when you've broken it and then research this
newsgroup
> thoroughly.
> HTH,
> Paul Ibison
> Also, I keep a scratchpad of errors/solutions I've seen, or read about and
> tested at www.replicationanswers.com.
>

issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin

issues deploying snapshot for merge replication.

I'm having issues setting up merge replication and the errors I'm getting are inconsistent but lead me to believe there is a bigger underlying issue than what they actually indicate.

Issue: unable to initialise subscriber with snapshot for merge replication. Fails for various reasons regardless of environment and setup. Previously this has deployed fine but now it is not.

Publisher is SQL 2005 (9.0.2047), Subscribers are SQLExpress or SQL Dev

The various errors I get are as follows:

These come mixed together generally... Unable to INSERT ... cannot insert NULL into column rowguid. [This error comes up on different tables for different snapshots, including ones with no records] bcp (Bulk Copy) error 20253 batch send failed Unspecified error Failed to send batch after max errors end of file reached, terminator missing or field data incomplete

Actions I have taken so far, all to no effect:

Adjusted Agent Profile to increase timeout values and decrease packet sizes Took new snapshots (tables where errors occured changed but still ame errors) Created a local subscription database on the server to remove any network related issues (using merge sync over VPN) Deleted and recreated Publication Created second publication with different articles on same database to find same errors in different locations again

I've tried reinitializing subscriptions (before subscribers were dropped and I tried recreating) but the DROP command fails due to FK constraints so if I reinitialize any subscriptions they will fail to be applied. Have tried editing the properties to just delete data and not drop tables but that still failed.

I've tried setting up subscriber without snapshot - created publication and snapshot, backed up db, copied to subscriber and restored there. See this article: https://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=689428&SiteID=1 This bloke suffered same results and the response from MS was to change the help files, not functionality. Doesn't help us dealing with subscribers via satellite AND unable to deploy snapshots...

I've checked the service pack releases to see if any issues like this are addressed and found nothing relevent.

My gut feeling is that the snapshot is not being generated correctly for some reason - system stored procedure errors perhaps.

So far my experience with replication has been good, with the odd issue or frustrating limitation in older versions, but this is really killing me...

Barry

(Sorry that author is "Anonymous" - must have messed that up)

Hey Barry it sounds like you have some problems there.

I would check a few things, maybe you have already done it but perhaps not.

Check that the snapshot is not getting corrupted by something when it is getting transferred accross the network / internet. You could try copying it via FTP and applying manually to the subscriber and see if that helps. Could also look into compressed snapshots for that too. Do a CRC check on the snapshot file and make sure it matches on both ends. You can get a util for that here: http://www.kmrconsulting.com/free_download.html

For that table with the null problems check that the default value for rowguid is set to newid() or newsequential() even though there is no data in there i suspect replication is doing some testing when it sets up the subscription to make sure things are running.

It sounds like your failure is with the bcp so perhaps after the tables are extracted try adding a few triggers on the troublesome tables that will record what replication is trying to insert or update. That will at least give you more info on what it is trying to do on the table.

Also take a look at the procedures that get run when the subscriber is applying the snapshot. Try running them one by one manually and issolate the problem more.

Good Luck!

Martin