Merger Replication - SQL2000
in [Replication]
|
Prev: SQL 2000 (the process is running and wating for a response from one of the backend connections)
Next: syncobj_* views?
From: Des Norton on 27 Nov 2007 02:39 Hi NG Please forgive the length of this post, but I feel the background might have some bearing on a possible solution. We had a server running Win2003 + SQL 2000 SP3a that was acting as a publisher and distributor for a single database. We lost the server, and had it rebuilt as a Win2003R2 + SQL 2005 SP1. We then attached a backup of the database, and set up the server as a publisher and distributor. Due to the size of the actual database, we decided to use a test database with a single table and 5 records. The subscriber is a client of ours running Win2000Server (SP4) + SQL2000 SP3a. We are unable to upgrade their SQL or install anything on their server. Their view is that it was working before, it must now continue to work. We have a Win2003Server + SQL2000 SP3a box in our office (not the same location as the publisher/distributor). Both us and our client use a 512ADSL line for connectivity. After 2 weeks of failure, we were unable to get merger replication to work with as SQL2005 publisher/distributor and a SQL2000 subscriber. Both push and pull options were unsuccessful. A decision was then made to revert the publisher/distributor to SQL2000 (SP3a). The database was restored and the publication successfully set up. On the publisher/distributor server, we installed a second instance of SQL2000(SP3a), and set up a pull subscription via FTP to the first instance. This replication kept failing. We then moved the contents of the single largest table (4.5GB/11.8mil records) into a separate database, and were able to successfully set up a pull subscription to the publisher. We then copied all the records back to the publisher, and synchronised the 2 instances on the same server. This took 45.5 hours. The synchronised subscriber was then backed up, zipped, and FTPd to our offices, were we restored it onto our Win2003Server + SQL2000 SP3a box, with the option "NO, Subscriber already has schema and data" option selected. We then modified the agent to add verbose logging, and proceeded to start synchronising to ensure that the replication works. However, the agent indicates a successful sync, but the log indicates errors. We then changed the merge agent profile to "slow" and set the QueryTimeout to 600. Log start time: [11/26/2007 3:46:04 PM] Again the logs indicate error: [11/26/2007 4:55:27 PM]GENHS001.MFP_Collections: {call sp_MSgetversion } Percent Complete: 33 Processing article 'dataRecordField' The process could not query row metadata at the 'Publisher'. The process could not enumerate changes at the 'Subscriber'. The merge process encountered an unexpected network error. The connection to Publisher 'GENHS001' is no longer available. Percent Complete: 0 The process could not query row metadata at the 'Publisher'. Percent Complete: 0 Category:NULL Source: Merge Replication Provider Number: -2147200996 Message: The process could not query row metadata at the 'Publisher'. Percent Complete: 0 Category:SQLSERVER Source: GENHS001 Number: 11 Message: General network error. Check your network documentation. Percent Complete: 0 Category:NULL Source: Merge Replication Provider Number: -2147199469 Message: The process was successfully stopped. On the publisher, the replication monitor indicates that the agent is suspect as it has not had a response in the last 10 minutes. The log indicates a connection issue. Is there any way to know if there was a definite connection issue, or if the replication simply timed out? Is there any other possibility? Any help will be greatly appreciated. Des Norton
From: Chris on 28 Nov 2007 18:05 A few things - you cannot use SQL2000 subscriber to merge when publisher is SQL2005. - how often do you run the merge snapshot agent? if you use backup as snapshot, and use nosync option when creating subscription, the merge agent will try to deliver all changes since last snapshot. Meaning, this could be a lot of changes going across wire if snapshot run weekly - or if disabled. - General network error - typically means connectivity is getting interrupted. ChrisB MCDBA MSSQLConsulting.com "Des Norton" wrote: > Hi NG > > Please forgive the length of this post, but I feel the background might have > some bearing on a possible solution. > > We had a server running Win2003 + SQL 2000 SP3a that was acting as a > publisher and distributor for a single database. We lost the server, and > had it rebuilt as a Win2003R2 + SQL 2005 SP1. We then attached a backup of > the database, and set up the server as a publisher and distributor. Due to > the size of the actual database, we decided to use a test database with a > single table and 5 records. > > The subscriber is a client of ours running Win2000Server (SP4) + SQL2000 > SP3a. We are unable to upgrade their SQL or install anything on their > server. Their view is that it was working before, it must now continue to > work. We have a Win2003Server + SQL2000 SP3a box in our office (not the > same location as the publisher/distributor). Both us and our client use a > 512ADSL line for connectivity. > > After 2 weeks of failure, we were unable to get merger replication to work > with as SQL2005 publisher/distributor and a SQL2000 subscriber. Both push > and pull options were unsuccessful. A decision was then made to revert the > publisher/distributor to SQL2000 (SP3a). The database was restored and the > publication successfully set up. > > On the publisher/distributor server, we installed a second instance of > SQL2000(SP3a), and set up a pull subscription via FTP to the first instance. > This replication kept failing. We then moved the contents of the single > largest table (4.5GB/11.8mil records) into a separate database, and were > able to successfully set up a pull subscription to the publisher. We then > copied all the records back to the publisher, and synchronised the 2 > instances on the same server. This took 45.5 hours. The synchronised > subscriber was then backed up, zipped, and FTPd to our offices, were we > restored it onto our Win2003Server + SQL2000 SP3a box, with the option "NO, > Subscriber already has schema and data" option selected. We then modified > the agent to add verbose logging, and proceeded to start synchronising to > ensure that the replication works. > > However, the agent indicates a successful sync, but the log indicates > errors. We then changed the merge agent profile to "slow" and set the > QueryTimeout to 600. > > Log start time: [11/26/2007 3:46:04 PM] > Again the logs indicate error: > > [11/26/2007 4:55:27 PM]GENHS001.MFP_Collections: {call sp_MSgetversion } > Percent Complete: 33 > Processing article 'dataRecordField' > The process could not query row metadata at the 'Publisher'. > The process could not enumerate changes at the 'Subscriber'. > The merge process encountered an unexpected network error. The > connection to Publisher 'GENHS001' is no longer available. > Percent Complete: 0 > The process could not query row metadata at the 'Publisher'. > Percent Complete: 0 > Category:NULL > Source: Merge Replication Provider > Number: -2147200996 > Message: The process could not query row metadata at the 'Publisher'. > Percent Complete: 0 > Category:SQLSERVER > Source: GENHS001 > Number: 11 > Message: General network error. Check your network documentation. > Percent Complete: 0 > Category:NULL > Source: Merge Replication Provider > Number: -2147199469 > Message: The process was successfully stopped. > > On the publisher, the replication monitor indicates that the agent is > suspect as it has not had a response in the last 10 minutes. > > > The log indicates a connection issue. > Is there any way to know if there was a definite connection issue, or if the > replication simply timed out? > Is there any other possibility? > > Any help will be greatly appreciated. > > Des Norton > > >
From: Des Norton on 30 Nov 2007 02:53
Thanks Chris We have reverted to SQL2000 publ/distr in order to satisfy the specific requirements of this client. Snapshots are run weekly, with a second intsnace of SQL on the publ/distr server which is a subsriber. When needed we make a backup of the subscriber DB, and install a copy at the cliet with the nosync option. However, even when nosync is used, there is still a larege amount of data that needs to be sync'd (I assume its indexes, tombstones, etc). Due to poor network connectivity, we are unable to get a new subscriber up and running. To solve the problem, we have * Manually removed the subscription from the subscriber and the publisher. * Manually removed the publication from the publisher and distributor. * Created a second instance of the published DB * Deleted the 11.8mil records from the offending table * Republished the (now empty) DB, with new snapshot * Subscribed and syn'd the new publication * Set up a continuous job to continuously load 100K records and sync. So far this is working OK, with �5mil records and counting. Thanks again for the help. Des Norton "Chris" <Chris(a)discussions.microsoft.com> wrote in message news:6FAA3A5E-30E3-4793-A38F-EC0AA1E40267(a)microsoft.com... > > A few things > > - you cannot use SQL2000 subscriber to merge when publisher is SQL2005. > > - how often do you run the merge snapshot agent? if you use backup as > snapshot, and use nosync option when creating subscription, the merge > agent > will try to deliver all changes since last snapshot. Meaning, this could > be a > lot of changes going across wire if snapshot run weekly - or if disabled. > > - General network error - typically means connectivity is getting > interrupted. > > ChrisB MCDBA > MSSQLConsulting.com > > "Des Norton" wrote: > >> Hi NG >> >> Please forgive the length of this post, but I feel the background might >> have >> some bearing on a possible solution. >> >> We had a server running Win2003 + SQL 2000 SP3a that was acting as a >> publisher and distributor for a single database. We lost the server, and >> had it rebuilt as a Win2003R2 + SQL 2005 SP1. We then attached a backup >> of >> the database, and set up the server as a publisher and distributor. Due >> to >> the size of the actual database, we decided to use a test database with a >> single table and 5 records. >> >> The subscriber is a client of ours running Win2000Server (SP4) + SQL2000 >> SP3a. We are unable to upgrade their SQL or install anything on their >> server. Their view is that it was working before, it must now continue >> to >> work. We have a Win2003Server + SQL2000 SP3a box in our office (not the >> same location as the publisher/distributor). Both us and our client use >> a >> 512ADSL line for connectivity. >> >> After 2 weeks of failure, we were unable to get merger replication to >> work >> with as SQL2005 publisher/distributor and a SQL2000 subscriber. Both >> push >> and pull options were unsuccessful. A decision was then made to revert >> the >> publisher/distributor to SQL2000 (SP3a). The database was restored and >> the >> publication successfully set up. >> >> On the publisher/distributor server, we installed a second instance of >> SQL2000(SP3a), and set up a pull subscription via FTP to the first >> instance. >> This replication kept failing. We then moved the contents of the single >> largest table (4.5GB/11.8mil records) into a separate database, and were >> able to successfully set up a pull subscription to the publisher. We >> then >> copied all the records back to the publisher, and synchronised the 2 >> instances on the same server. This took 45.5 hours. The synchronised >> subscriber was then backed up, zipped, and FTPd to our offices, were we >> restored it onto our Win2003Server + SQL2000 SP3a box, with the option >> "NO, >> Subscriber already has schema and data" option selected. We then >> modified >> the agent to add verbose logging, and proceeded to start synchronising to >> ensure that the replication works. >> >> However, the agent indicates a successful sync, but the log indicates >> errors. We then changed the merge agent profile to "slow" and set the >> QueryTimeout to 600. >> >> Log start time: [11/26/2007 3:46:04 PM] >> Again the logs indicate error: >> >> [11/26/2007 4:55:27 PM]GENHS001.MFP_Collections: {call >> sp_MSgetversion } >> Percent Complete: 33 >> Processing article 'dataRecordField' >> The process could not query row metadata at the 'Publisher'. >> The process could not enumerate changes at the 'Subscriber'. >> The merge process encountered an unexpected network error. The >> connection to Publisher 'GENHS001' is no longer available. >> Percent Complete: 0 >> The process could not query row metadata at the 'Publisher'. >> Percent Complete: 0 >> Category:NULL >> Source: Merge Replication Provider >> Number: -2147200996 >> Message: The process could not query row metadata at the 'Publisher'. >> Percent Complete: 0 >> Category:SQLSERVER >> Source: GENHS001 >> Number: 11 >> Message: General network error. Check your network documentation. >> Percent Complete: 0 >> Category:NULL >> Source: Merge Replication Provider >> Number: -2147199469 >> Message: The process was successfully stopped. >> >> On the publisher, the replication monitor indicates that the agent is >> suspect as it has not had a response in the last 10 minutes. >> >> >> The log indicates a connection issue. >> Is there any way to know if there was a definite connection issue, or if >> the >> replication simply timed out? >> Is there any other possibility? >> >> Any help will be greatly appreciated. >> >> Des Norton >> >> >> |