data synchronization accross multiple servers running replication

Hello,

We have an environment with 7 servers that are running replication with one another and I'm wondering if there are any tools or experiences that any of you might have that may assist in the auditing of these servers. The data should be in synch accross the boards for all tables, but sometimes problems can arise such as replication not being set up properly, stored procedure's being out of synch, or data gliches etc.

In dealing with these issues we have an in-house written program which analises each table on each server and takes a snapshot and does column by column compare. We also have another program that will synch the data up (basically a delete/insert statement on the publisher). This process can take up to 3 weeks for our quarterly update of every table. I'm wondering if anybody has used any tools such as in SSIS or a third party tool and has done or is doing something similar to what we are doing now. If so, are there any tips you wouldn't mind sharing on how our process might be sped up?

Thanks,

Phil

In SQL Server 2005 there is a tablediff tool which you can use to compare the tables. You can also use the validation options built in replication to verify if the data are in sync.

Here are the validation options:

In transactional replication: sp_publication_validation

In merge replication: use the -validate parameter for merge agent

|||

Li,

Thanks for the reply. I believe we can use this. Do you know how well it does as far as speed is concerned? I think that's our main concern right now. Also, I'm reading a bit on the uitlity and I'm wondering how does it take a "snapshot" of a table on multiple servers and compare that. Also you wouldn't happen to have any syntax examples would you?

Thanks,

Phil

|||

Also, does anyone know if there is a way to get the utility to use windows authentication instead of coding the sa password in the utility statement?

Thanks,

Phil

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file (create a new text document, then rename it to "test.udl"). Open the UDL file by double-clicking it, then try to establish a connection to your database using the same sorts of parameters (ie provider [SQL Server native / ODBC etc.], server name, login parameters). You can use this to narrow down the cause of the problem (eg. whether it's the DB provider, or a lack of connectivity which can be checked using other means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP? Also, it'd depend on whether you've got the IIS machine on the Publisher machine, the Subscriber machine, or another machine?
Also try playing around with the configuration of the Client/Server Network Protocol - if you're having problems using TCP/IP, then try using Named Pipes instead. Make sure to configure this on both the publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

sql

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data Source / ODBC error

Hi all,

My replication of those SQL 2000 servers gave errors: Data source (11): General Network Error. Check your network documentation... and ODBC (08S01): Communication link failure. The replication was across the WAN. I don't know where to start to troubleshoot this problem. Please help!

Thanks in advance.

John

Communication link failure means some network or communication problem between the Publisher/Distributor and Subscriber. Check the connection between them. Try connecting to the other servers from each of the servers and ensure that you are able to connect and then retry the Sync.

Also note for any firewalls that need to be taken care of or if the ports are different or enabled.

See if the domains are different and if so ensure trust or use SQL authentication.

Also is this the first time the sync ran or it previously used to succeed?

|||

Hi,

This is not the first time the replication failed. It worked perfect before.

DNS is fine since I did check.

Since this is a site to site interconnected (site2site VPN across internet). Therefore, the domains are different.

It has failed several times a day.

I don't know if we have any testing tools for replication available so I can narrow down the problems and troubleshoot more efficiently.

thanks much for your help!!!

|||This mostly has to do with network connectivity then. For merge agent to run successfully, the connection needs to be established. Try rerunning it and see if it succeeds. Also see if there are any other external factors affecting the connection. Peak load, network flakiness, disconnects or downtime during certain periods of time etc.|||On the machine where the failure occurred, try creating a UDL file

(create a new text document, then rename it to "test.udl"). Open the

UDL file by double-clicking it, then try to establish a connection to

your database using the same sorts of parameters (ie provider [SQL

Server native / ODBC etc.], server name, login parameters). You can use

this to narrow down the cause of the problem (eg. whether it's the DB

provider, or a lack of connectivity which can be checked using other

means such as a traceroute etc.).|||

Thanks much. I will implement this method. In the meantime, is the packet from replication UDP or TCP when it is sent to the subcribed machine from distributed machine. I have thought my network is being flooding at the switch before data reached the nodes of the cluster. Therefore, it is redirected elsewhere but the destination. What I did was trying to send data from host inside network through that switch to the gateway...and I was only successful 35%. This means packets drops no matter incoming or outgoing through that switch.

Having some methods to test the replication directly will help a lot.

Thanks for your helps, guys.

|||Doesn't UDP run on top of TCP, hence they're both going to be TCP?

Also, it'd depend on whether you've got the IIS machine on the

Publisher machine, the Subscriber machine, or another machine?

Also try playing around with the configuration of the Client/Server

Network Protocol - if you're having problems using TCP/IP, then try

using Named Pipes instead. Make sure to configure this on both the

publisher and subscriber machine.|||

Problem Fixed, Guys. It was just network problem. The Fixwall is misconfigured so that it operated half duplex instead of full. There Ain't nothin to do with the replication!!!

However, I have to deal with new problem......queue reader failed with the error message: Queue reader aborting. The steps failed. What does it mean? Where should I start to troubleshoot this error?

Thanks a lot!

|||Is there anything more helpful in the Event log?|||What I 've got is Failed while applying queued message to publisher!|||

I'm not an expert in SQL Server replication, so I can't help you more with your specific error (I've just recently spent a few days reading lots about SQL Mobile replication, hence the reason I came across your post).

If I was in your position, however, I'd start by getting everything working on on one box (even if it's just a staging box), and once it all works, you can move bit by bit elsewhere (eg. Publisher DB / IIS virtual dir / IIS server / Subscriber DB).

As you try to move things bit by bit you'll realise where the problem is. Sometimes the 'longest' way is the shortest because by being systematic, you don't end up pulling your hair out looking at something that 'should work', but doesn't!

|||

Oh, by the way, if you can't get it to work, I suggest starting a new thread, because some people will see this thread as answered, and not read it.

Cheers.

Data Replication performance.

I'm seearching for information regarding database replication
performance. We need to compare the performance of replication for SQL
Server and Oracle and it is urgent! Anyone who can describe the
performance bottlenecks for each database when performing replication,
or can point me to a white paper or webpage.Hi Tobias

please have a look at the following link,

http://www.akadia.com/services/sqls...l#_Toc513865493

thanks
Shehzad

toby_one_canoby@.hotmail.com (Tobias Johansson) wrote in message news:<a64d0b53.0410010417.386d1fb6@.posting.google.com>...
> I'm seearching for information regarding database replication
> performance. We need to compare the performance of replication for SQL
> Server and Oracle and it is urgent! Anyone who can describe the
> performance bottlenecks for each database when performing replication,
> or can point me to a white paper or webpage.

Data replication issues: Thanks Paul

Paul,
Thanks for your reply. My distribution agents schedule is
set to run at 7:00 am everyday (similar to my snapshot
agent). I found that the distribution agent gave me an
error and the details are as follows:
Error msg: The process could not access
database 'LETS30_Repl' on server 'FTANT001'
Error details: Could not locate entry in sysdatabases for
database 'LETS30_Repl'. No entry found with that name.
make sure that the name is entered correctly. [SOurce:
FTANT001(Data Source): Error Number:911]
One interesting thing to note is even though I had created
the database 'LETS30_Repl' it does not show up
under 'Databases' in the server. Why is it so? Thanks,
Cheers,
Sabuj

>Sabuj,
>what is the distribution agent's schedule? My guess is
>that it hasn't
>matched its schedule time yet. Try running it manually to
>check that
>everything is OK.
>HTH,
>Paul Ibison
Sabuj,
can you query the sysdatabases table on the subscriber (FTANT001) to verify
if it is there:
select * from master.dbo.sysdatabases
Or alternatively just run sp_helpdb.
If it is not there then recreating it should allow the distribution agent to
succeed.
Regards,
Paul Ibison

Data replication issues

Hi,
We have an SQL server (lets call it FTANT001) and a
datatbase (lets say LETS30). I want to replicate the
database in the same server under the name LETS30_Repl.
Naturally the publisher, distributer and subscriber is the
same server FTANT001. I am susing a 'snapshot agent' to
replicate the database. I have successfully created the
publication (named as LETS30_Article). I have set up the
snapshot to be taken everyday at 7:00am. My problem is the
distribution agent status always shows as 'Never started'.
What could be the problem. Thanks in advance.
Cheers,
Sabuj
Sabuj,
what is the distribution agent's schedule? My guess is that it hasn't
matched its schedule time yet. Try running it manually to check that
everything is OK.
HTH,
Paul Ibison

Data replication from MSSQL to MYSQL

Hi all.
I need to set a oneway replication of some data from MSSQL (running under
Windows 2000 server) to MYSQL (running under Linux).
Do you have some ideas to solve the problem?
Thank you
Luca"Luca" <NOSPAMprong2k@.hotmail.com> wrote in message
news:co4mja$df9$1@.atlantis.cu.mi.it...
> Hi all.
> I need to set a oneway replication of some data from MSSQL (running under
> Windows 2000 server) to MYSQL (running under Linux).
> Do you have some ideas to solve the problem?

You can probably do this via DTS

> Thank you
> Luca

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison
sql

Data replication betweeb different database vendors

Aloha!
On MSDN there are quite a few good patterns when it comes to data
replication between SQL Servers. However in my scenario, a
"Implementing Master-Slave Transactional Incremental Replication Using SQL
Server" or "Implementing Master-Slave Snapshot Replication Using SQL Server"
(MSDN patterns), we will be sitting on a Slave database (SQL Server 2000)
which is dependent upon several different Master databases (from different
vendors and different suppliers). My question is if there are some patterns
that describes this scenario, preferably a XML based solution which is
transparent when it comes to the Master source.
BR,
Max.
Max,
SQL Server can indeed be used as a subscriber, but this is currently just
implemented from the point of view of the vendor's databases ie from within
SQL Server you can't create a publication on another Database server eg DB2,
Sybase etc. Some third-party tools do this type of amalgamation, but it is
also possible to program integration using applications built with SQL-DMO
and the Replication Distributor Interface
Have a look at Heterogenous Publishers in BOL.
HTH,
Paul Ibison

data replication

To all,
I am totally new to database programming and SQLServer. I need to find out
how to "copy" the data in database 1 to the database 2. How, in general one
does this type of "copy" or "migration". Is there a SQL standard way to do
this?
Thanks in advance.
RezaReza,
there are numerous options depending on your requirements eg is this for
high-availability, reporting, load balancing etc.
Anyway, please have a look in BOL at Log Shipping, Replication and Database
Mirroring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This ultimately is to generate reports from the data in the database. Just
curious, what is BOL at Log Shipping?
Reza
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukK6DswYGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Reza,
> there are numerous options depending on your requirements eg is this for
> high-availability, reporting, load balancing etc.
> Anyway, please have a look in BOL at Log Shipping, Replication and
> Database Mirroring.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||BOL = Books On Line
Log Shipping is what you need to look up in the index.
"Reza Razavipour" <rrazavipour@.stbernard.com> wrote in message
news:eeP5PvwYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> This ultimately is to generate reports from the data in the database. Just
> curious, what is BOL at Log Shipping?
> Reza
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ukK6DswYGHA.3972@.TK2MSFTNGP04.phx.gbl...
>

data replicate incorret field (Urgent)

I'm using SQL 2000 with SP3 and made an replication link to another server.
A few weeks ago, I drop the replication and add a new column in both
publisher and subscriber server and made a new replication. Recently, I
found that a new column did not replicate a data. I check with
sp_browsereplcmds found that it update a another field. Why? How to fix it?
please help
RC
Firstly, it's worth mentioning that sp_repladdcolumn will do what you
require 'in-place' ie there's no need to set up the publication from
scratch. You may have had other reasons for doing this anyway. So, to find
out a little more, are the schemas on the publisher and subscriber now
identical? If not, do you have any filters in place? Did you create the
replication stored procedures yourself?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||the schema is the same on publisher and subscriber and no columns filtering
RC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> glsD:%23M9TOCQFGHA.1312@.TK2MSFTNGP09.phx .gbl...
> Firstly, it's worth mentioning that sp_repladdcolumn will do what you
> require 'in-place' ie there's no need to set up the publication from
> scratch. You may have had other reasons for doing this anyway. So, to find
> out a little more, are the schemas on the publisher and subscriber now
> identical? If not, do you have any filters in place? Did you create the
> replication stored procedures yourself?
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||OK. Perhaps you have changed the column order on the table at the
subscriber, or the subscriber and publisher schema are out of sync in some
way. If you run sp_scriptpublicationcustomprocs @.publication = 'yourpubname'
do you get the same stored proc syntax as the one currently existing on the
subscriber? Is this the same column order in the Insert Statement as that
existing on the subscriber?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Data Mirroring Vs. Log Shipping Vs. Replication

Hi everyboby,

Can anybody tell me the differences, advantages and disadvantages between these three solutions? When do I may use one or another?
Could you recommend me any documentation?

Thanks a lot,

Radamante71.

This article should get you started.

http://msdn2.microsoft.com/en-us/library/ms190202.aspx

Regards,

Gary

|||This white paper (http://www.microsoft.com/technet/prodtechnol/sql/2005/dbmirror.mspx#EVKAG) also has a section that compares different high availability options in SQL 2005.

Data Migration using Transactional Replication

I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.

I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.

Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.

Note: Migration is from SQL Server 2000 to SQL Server 2005.

Thanks in advance.

Riteshi do not think replication is going to be easy and effective solution for 200 DB with 2.5 TB. replication has its own set of rules, issues and overheads. better move a few db at a time (backup/restore or detach/attach) and then point the application using those databases to the new db server.

Data Lost-Merge Replication

We are using merge replication over internet to repliacte our data b/w 2 servers located at 2 different branch offices. But the strange thing is we are losing data i.e. data is being automatically deleted during replication.

any idea/help for fixing it would be hihgly appreciated

thank in advanceProbably failed insert at subscriber poss due to Referential Integrity Failure.

Because SQL Must make publisher & Subscriber with same data it will come back & delete publisher row

Keep an eye on the conflicts

data lost

Hi,

I have encountered problem in the merge replication with conflict caused by foreign key constraint. As I did some reseach that it can be avoided by increase the upload and download batch generations.

My question is how can I recover the data which has been lost?

Thanks

You can try the mscontents and the conflict tables to say if you can recover the data there if these table has not been cleaned up yet.

I am more interested in how the data was lost. is there any nonconvergence between publisher and subscriber data ?

are you using Sql2000 or SQL2005 ?

thanks

Yunwen

|||When you get a conflict, the info is stored in conflict tables, which you can see in the Replication Conflict Viewer. Conflicts are kept here until you pick which one wins.|||

Thank you for your response.

The conflict message says:"INSERT statement conflicted with COLUMN FOREIGN KEY constraint" so the record can not be inserted in the child table on the subscriber site.

Then somehow the record is also deleted in the child table on the publisher site.

I may be able to get the rowguid from the msmerge_delete_conflicts table, but no more the detail data like stored in the [conflict_replication name_table name]

It seems the data get lost permanently

|||I am using SQL2000|||

If you are using SQL Server 2000 SP4, then please look at the option of 'compensate_for_errors'. This is a paramtere to sp_addmergearticle/sp_changemergearticle.

More info in this article: http://support.microsoft.com/kb/828637

Data Loss with Merge Replication?

We had merge replication setup between 2 tables, Table A and Table B using SQL 2000. This was working 100%. The users asked to disable updated/deletes to both these tables if data existed on 2 other tables. Table AA and Table BB. We implemented it as follows:

1) Created Insert/Update/Delete triggers for Table A & B. It basically check for Table A is there a record in Table AA, if it exists, raise an error and don’t commit.

2) Removed all foreign constraints from Table AA and BB

3) Added Table AA and BB to the current replication.

Then all hell broke loose, we got conflicts all other the place saying that Table AA cannot be updated because records does not exist in Table X. To our surprise we found triggers generated by Erwin in 1998 – that check for “foreign contsraints” and removed them immediately.

We continued to get conflicts but could see from the error messages it was generated by the triggers in point 1. We added the NOT FOR REPLICATION clause and everything has been running smoothly or so we thought…..

After 2 months we got a call that data is missing. It’s random data and the only explanation I have is that replication caused that. My biggest reason for saying this is tracking the application audit trail I’ve found that all the data missing was added during the period we had all the conflicts.

I need a solid explanation for this and can anyone confirm that this is possible?

Yes it's possible for merge replication to lose changes, but these types of scenarios are rare, and even if this was the case it most likely cannot be investigated two months after the fact, especially if metadata has been cleaned up. However simply blaming replication is not fair, you really need to investigate what changes were made, what conflicts were occuring, what conflicts were and weren't resolved.

You should also make it a point to run validation every so often to catch nonconvergence issues earlier, this way when you find you're missing data, it can be investigated with all the necessary information available.

|||Thanks for your quick response Greg. I was unfortunatly not involved when the conflicts were resolved and have no idea how it was done. My knowledge of replication is also verly limited. Does SQL keep a history of replication and can you give me some guidelines to resolve this issue. The client is very unhappy as you can imagine and I need to give an explanation. We have already put measures in place to do validation but it's a case of a little to late.......|||In these kinds of scenarios where you experience data loss and believe it's replication, you need to call CSS (Microsoft customer support) and open a case with them, they have engineers who are experts in investigating problems like this.

data loss when reinitializing subscription

we had setup merge replication on 2 db servers. For some reason, the subscription started failing a month back with the error " invalid object sysmergexxxx on the subscriber. I did a reinitialize and now all the changes on the subscriber which werent synced got deleted. I have tried all 3 log recovery tools with no luck. Is there any hope of recovering data. the last backup on the subscriber was a month ago.

I believe when you reinitialized the subscriber, you did not chose to upload the unsent changes from subscriber and hence you lost your subscriber data. Sorry to say, but there is no way to recover this data now and is overwritten by the publisher's data.

You should try to either upload the changes next time this occurs or save a backup copy of the subscriber database, then reinitialize (without upload changes) and then diff between the current subscriber and the backup and redo the DML that are different.

Hope that helps next time.

I am also moving this thread to the replication forums.

Data loss in Transactional Replication

We have setup transactional replication across several databases using SQL Server 2000 spread across multiple sites in a fully connected network. There is one main table from which data is replicated from the publisher to the destination. Horizontal filtering is being used on this table to enable sending/routing of the records to the correct DB(site). It has been observed that the documents/records are getting lost between some sites. Say 10 documents are being sent fron the publishing database but only 5 are being received at the destination database although the sent history for all the 10 documents is available at the publishing database.

Can anyone guide on how to analyse and resolve this problem? Can unreliable network be the issue, If the network is not reliable and the connection is lost during replication how does replication ensure that no data is lost?

Hi,

If you didn't see replication reports error, unreliable network should not be the cause of data loss. Replication delivers the transaction with order. If it can't deliver it for whatever reason, it will keep retry or report error unless you explicitly specify skiperror.

To troubleshoot, you first need to see if your horizontal filter actually filters the data at publisher. You can also use sp_browsereplcmds to see if the transactions is in distribution DB. From here, you can determine if it is a logreader issue or distribution agnet issue.

Please let me know if you need more assistance.

Peng

Data loss in replication

hi,

Im using merge replication.I have created one publisher with dynamic filtering and i have creted two subscribers from it(pull subscription).It was working fine.but after sysnchronizing 2-3 times data from some tables went missing

can any one help me....

thanks and regards

Dhanya

Can you provide more detail? If you don't mind, can you post your table schema and filter information or your replication setup script and indicate what was missing? Data from the publisher table or from the subscriber table?

Regards,

Gary

|||

It may be because your DML caused data to go out of partition from the subscribers.

Is this SQL 2000? Is the setting compensate_for_errors=true for the article you are experiencing data loss?

And by data loss, do you mean an insert done at publisher after synchronizing, does not exist anymore at the publisher and subscriber?