Showing posts with label device. Show all posts
Showing posts with label device. Show all posts

Wednesday, March 28, 2012

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.
|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.
|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.sql

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Merge Replication with SQL Mobile 2005

We are using SQL 2005 (SP1) and mobile agents with SQL Mobile. We are seeing an excessive number of updates after a device's database is reinitialized. My understanding was that if I generate a new snapshot or reinitialize all subscriptions (from the server), the devices would only get what the database looks like right now (inserts only) after syncing and/or reinitializing, but they are actually getting the right number of inserts and a huge number of updates in Replication Monitor. Any ideas?

Thanks!

Mike

Update: Found a weird thing with the updates: On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts. On production, the number of updates being transferred is 6 times the number of inserts on every article, so it the mobile database should get 10 rows for an article (based on filters), it will show 10 inserts and 60 updates.

Hope this helps, although it seems pretty strange!

Mike

|||

are the any configuration difference or data load difference between the test server and the production server ?

and also, by saying "On our test server, the number of updates, after generating a snapshot and reinitializing the mobile db, exactly matches the number of inserts", you meant to say the "updates on the subscriber is the same number as the inserts on the subscriber", right ?

what is you publication looks like ? e.g. number of articles, RIs between them ? etc.

that'll help us to identify the issue.

thanks

Yunwen

|||

First of all, I have an update. We recreated the publication on our test server as well as our production server (causing outages to our customers). After doing so, everything went back to normal and we were only seeing inserts (no updates) after a subscription is reinitialized. Now, just this morning, our production server started showing duplicates again, right now the number of inserts is exactly the same as the number of updates. Another thing to note is that right before we recreated the publication in production, the ratio was up to 7 to 1 (updates to inserts), meaning that it went up again.

As to your question, we have 23 articles. All of them have filters based on HOST_NAME(), some have even more complex filters that use subqueries based on HOST_NAME as a simple join will not work. All tables have PK/FK relationships with the exception of several tables that have auditing fields (created by, modified by, etc). We do not have referential integrity as the assosiated data is not always transferred to the device, nor do we use these fields on the device except to set or update them. The only exception to this is a LOG table that contains a trigger to move the logs into a historical table so that we can gather information from the device. This produces an insert during the upload and a delete during the download, removing the log from the device during a sync.

BTW, you are correct, the number of updates to the subscriber matches (or is some multiple of) the number of inserts on the subscriber. So far, we do not see this with normal syncs, only reinitializations.

One other thing to note is that we recreated the publication yesterday at around 7AM CST. This morning is the first time we are seeing the updates again, and they started sometime between yesterday evening (when the last reinitialization was recorded; worked fine) and this morning at 9:30AM (the first reinit that showed the duplicates). That being said, it took over a month for the previous publication to reach a multiple of 7.

Hope this all makes sense! This is getting more and more urgent as we send nearly 20,000 rows to some devices, meaning that their reinit is starting to take a larger and larger amount of time as the multiples go up.

Thanks!

Mike

|||

Thanks for the update.

do you generate a new snapshot prior reinitializing the subscriptions ? if the snapshot is old one and the subscription was reinitizlied. it will apply the snapshot and then apply the data in the mscontent table and if there are more and more updates/inserts on the publisher, this will give you this impression that subscriber was getting some unnecessary data.

also, you mentioned that there is a trigger on one of the published tables. is it possible you provide more details about it, or event better, send us the the publication script and the creation scripts for all the tables ?

Thanks

Yunwen

|||

First, yet another update: We removed and recreated the distribution database and publication after doing so on our test server and seeing the problem go away (usually we see some sort of duplication before a week has passed). So far so good, although it has not yet been 24 hours on the production side.

At the time of recreation in production, we were up to 5 times the number of updates as inserts, which means that it was going up by a multiple of 1 almost every 24 hours (although some days it skipped and the time the updates increased moved around; hopefully this removes scheduled jobs as a possible cause). I copied the distmdl and distribution files out before we recreated the distribution DB so that we can review a few of the critical system stored_procedures for differences, just in case the service pack did not update existing distribution databases correctly.

As to your questions...

Yes, we have tried generating a new snapshot to see if that would fix the issue. It had no effect. I agree that we may see some updates in there, but to have an exact multiple on every table would still not seem right to me. I would expect to see "random" updates throughout the articles.

I will look at scripting out the rest of the publication, but here is the trigger:

CREATE TRIGGER [dbo].[MoveLog]
ON [dbo].[LOG]
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRANSACTION

INSERT INTO LOG_HISTORY SELECT * FROM [LOG]
DELETE FROM [LOG]
IF @.@.error <> 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END

SET NOCOUNT ON;

END

Thanks,

Mike

|||

Almost forgot, we have a fax table that functions the same way as the log table. Also, both the log and fax table have no filters (as they are emptied after each sync;have yet to see them leave data in the table on the server or in any of our test SQL Mobile databases).

The triggers are identical to the point that you could just replace "LOG" with "FAX" and you would have it (in fact, that's how the fax trigger was created!).

Thanks again!

Mike

|||

Thanks for th update for this issue. yes, seeing update is exact X * insert number does indicate there could be issues.

so is both Log and log_history was published ?

Please keep us updated on the issue.

thanks

Yunwen

|||

Only LOG is published. Basically, the device logs to the log table. During a sync, the log entries are uploaded, the trigger fired (to move it to the history table), then the log entry is deleted off the device, during the download phase.

I am now working with MS support on the issue, so hopefully we can figure something out. So far, I am just gathering logs for review.

Thanks,

Mike

|||

Thanks Mike.

Working with MS support probably a good way to pursure this issue. they can instruct you how to trouble shooting this issue.

Thanks

Yunwen

|||

Hello,

we are running into the same issue (multiplied insert, updates, initial synchronization taking up to 10 times longer than on identical publication on sql2000). I am therefore very concerned if you finally solved the case?

Thanks in advance

Kuba

|||We are running into this same issue as well. It started out as updates matching the number of inserts, and has now gone to updates doubling the number of inserts. If anyone has any solutions or feedback on this issue, it'd be greatly appreciated!

Thanks,
Adrien.|||I have contacted Microsoft developer support and resolved this issue with them. It is a known issue and they have a hot fix for it. However, the fix won't be available publicly until the next service pack. If you want to resolve this issue sooner though, developer support will refund the cost of an incident since it is a known issue.

Adrien.|||

Hello,

are you sure whether the hotfix to the discussed problem is going to be available in SP2? I've seen the list of bugs fixed by SP2CPT and I can't find anything that seems to be related to the case. How do I conctact developer support, is it possible to obtain the single hotfix from them or from anybody else?

Thanks in advance

Kuba

|||Hello,

My contact at developer support told me that the hotfix would be available in the next service pack. Whether he meant for SP2, or a future service pack, who knows? If you can't wait for it to be ready in a service pack, yes you can get it from developer support. To contact them, browse to the MSDN home page while logged into MSDN, then click on "Support Incidents".

Adrien.

Monday, March 26, 2012

Merge Replication when connecting from Mobile Device

Hi,
I'm trying to set up a merge replication application on a production server
and I have run into problems.
I'm trying to use SQL server Mobile to connect to a SQL Server 2000
publisher but I get errors when I try to syncronize.
I've tested the application in my development enviroment where the security
settings are much more open ant there everything works fine so I suspect the
error is connected to the higher security in the production enviroment.
I've setup Merge replication on the server box and configured IIS and when I
test the connection to the server agent from IE (both from my desktop and
mobile device) by using the URL:
https://myserver/myvirualdirectory/sqlcesa30.dll and supply my credentials I
get the "agent ok" message.
The problem occurs when I try to syncronize my mobile database to the
publication. I get the following error message:
A request to send data to the computer running IIS has failed. For more
information, see HRESULT.
When I check the HRESULT value in the exception it is: -2146233087
What does this error message mean?
How can I parse this value?
Thank you for any suggestions
Regards
Henrik
That error message is 2146233087 (80131501)': No server registered for
CLSID. It sounds like one of your replication components or replisapi dll's
is not registered correctly.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Henrik" <henrik_@.community.nospam> wrote in message
news:C5F499A3-8965-4D80-AA3B-EB9CFD79861A@.microsoft.com...
> Hi,
> I'm trying to set up a merge replication application on a production
> server
> and I have run into problems.
> I'm trying to use SQL server Mobile to connect to a SQL Server 2000
> publisher but I get errors when I try to syncronize.
> I've tested the application in my development enviroment where the
> security
> settings are much more open ant there everything works fine so I suspect
> the
> error is connected to the higher security in the production enviroment.
> I've setup Merge replication on the server box and configured IIS and when
> I
> test the connection to the server agent from IE (both from my desktop and
> mobile device) by using the URL:
> https://myserver/myvirualdirectory/sqlcesa30.dll and supply my credentials
> I
> get the "agent ok" message.
> The problem occurs when I try to syncronize my mobile database to the
> publication. I get the following error message:
> A request to send data to the computer running IIS has failed. For more
> information, see HRESULT.
> When I check the HRESULT value in the exception it is: -2146233087
> What does this error message mean?
> How can I parse this value?
> Thank you for any suggestions
> Regards
> Henrik
|||Thank you for your reply
Could you please explain further what you mean. Is there a problem with the
installation of the different tools?
Where can I find a list of error messages to map a HRESULT to an error
description?
Thank you
/ Henrik
"Hilary Cotter" wrote:

> That error message is 2146233087 (80131501)': No server registered for
> CLSID. It sounds like one of your replication components or replisapi dll's
> is not registered correctly.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "Henrik" <henrik_@.community.nospam> wrote in message
> news:C5F499A3-8965-4D80-AA3B-EB9CFD79861A@.microsoft.com...
>
>
|||Hello Henrik,
I would like to suggest you to check whether you have use any firewall in
the production server.
842907SQL Server CE 1.0 and later version client programs may not be able
to connect to IIS on computers that are running Windows XP Service Pack 2
(SP2)
http://support.microsoft.com/default.aspx?scid=kb;EN-US;842907
Since this issue is related with SQL Server Mobile which is not supported
in the SQL newsgroup, I would like to suggest you to post it in the
microsoft.public.sqlserver.ce newsgroup.
Thank you!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
|||Thank you for your reply.
I've been trying to find a SQL Server CE newsgroup but there is none!?
Or am I more than semi-blind?
I've searched the list at:
http://msdn2.microsoft.com/en-us/subscriptions/aa974230.aspx
Could you please give me directions
"Wei Lu [MSFT]" wrote:

> Hello Henrik,
> I would like to suggest you to check whether you have use any firewall in
> the production server.
> 842907SQL Server CE 1.0 and later version client programs may not be able
> to connect to IIS on computers that are running Windows XP Service Pack 2
> (SP2)
> http://support.microsoft.com/default.aspx?scid=kb;EN-US;842907
> Since this issue is related with SQL Server Mobile which is not supported
> in the SQL newsgroup, I would like to suggest you to post it in the
> microsoft.public.sqlserver.ce newsgroup.
> Thank you!
> Sincerely,
> Wei Lu
> Microsoft Online Community Support
> ==================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
> ications.
> Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
> where an initial response from the community or a Microsoft Support
> Engineer within 1 business day is acceptable. Please note that each follow
> up response may take approximately 2 business days as the support
> professional working with you may need further investigation to reach the
> most efficient resolution. The offering is not appropriate for situations
> that require urgent, real-time or phone-based interactions or complex
> project analysis and dump analysis issues. Issues of this nature are best
> handled working with a dedicated Microsoft Support Engineer by contacting
> Microsoft Customer Support Services (CSS) at
> http://msdn.microsoft.com/subscriptions/support/default.aspx.
> ==================================================
> (This posting is provided "AS IS", with no warranties, and confers no
> rights.)
>
|||Hello Henrik,
The SQL CE newsgroup is not a managed newsgroup. You could access from this
url:
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.sqlser
ver.ce&lang=en&cr=US
Thanks!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

Merge Replication using SSL on a Windows Mobile 5.0 device

Dear ppl,

I have a windows mobile 5.0 application that syncronises with SQL Sever 2005 (Merge Replication). The replication works fine without SSL. But when using SSL it doesn't work .On the device i get an error

28037, A request to send data to the computer running IIS has failed.

I have created a a Wild Card Certificate (*.domain-name.net) using Microsoft Certificate Services (Stand Alone) and setup the replication website with replication virtual directory under it , in IIS, to use this certificate. The host header for the replication site is set to "sql.domain-name.net". I have binded the host headers to the port as well.

I can access the https://sql.domain-name.net/replication/sqlcesa30.dll from remote computer and device and it says Microsoft SQL Server Compact Edition Server Agent, that means it is setup correctly.

I have installed the certificate authority and its wild card certificate on the device Root Certificates. Now when I call repl.syncrhonise() from device .. it throws exception with error 28037, A request to send data to the computer running IIS has failed.

which i think means, it can't find the service url.

I am not sure what is the problem..something to do with Trust - SSL certificates... Is there any thing i am missing ?

Awaiting,

Just try to rule out the obvious - you have specified "https://" instead of "http:" in the application source code, right?

Did you run the sync app as the same window's credential as running "https://sql.domain-name.net/replication/sqlcesa30.dll" from IE?

Thanks.

|||

thanx for the response Zhou... well i m not using any authentication..I have setup the replication website Directory Security in IIS as Anonymous Access... Is it necessary to use credentials (any authentication windows,basic etc) ?

Awaiting,

|||

It would be nice to try different credentials, at least the basic authentication, Windows username and password.

However, you need to specify these information in your application code.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Nabeel -

I'm interested in what you find out.

I've been using SSL (with a certificate I created using Windows Server Certification Authority) with merge replication to SQL Server 2000 for a few years now. I'm in the process of trying to figure out how to migrate to SQL Server 2005 and SQL Server CE 2005. But I'm also noticing that Internet Explorer 7.0 can throw some very very nasty message to users browsing to my web site -- apparently, IE 7 sees these non-trusted certificates as a major security threat). So I'm now exploring how to deploy with a trusted certificate.

I'll keep an eye on how this thread develops and I'll post as I learn more information. Hope you will do the same.

Larry

|||

Nabeel -

Doing some research, you might want to take a look at this:

http://support.microsoft.com/kb/266695/EN-US/

Larry

|||

well if u take a look at this http://download.microsoft.com/download/6/d/9/6d9ab586-5bed-4cfc-b28b-d83dadb85b42/Windows_Mobile_Devices_and_Security_Proecting_Sensitive_Business_Information.pdf

It says that The Internet Explorer Mobile Web browser included in Pocket PC and Smartphone software uses 128-bit SSL (https) and PCT encryption technologies to access secured Web sites. In earlier versions, the High Encryption Pack may be required to upgrade from the native 40-bit encryption to 128-bit encryption. So i think it is not required to install the high encyption pack. However i tried to install it on a couple of devices WM5.0 and after installing and restarting the devices, the devices stopped working for some reason :) ... so I had to do for factory restart

|||

hi,

your device MUST recognize the certificate's CA. you should export your CA's root certificate into .cer file (via http://myca/certsrv/), copy it to the device, and import it into trusted root CA store there (just tap it and confirm). AFAIR you also need to do a soft-reset.

you can test if your device recognizes the site's certificate by launching IE on the device and going to the merge agent address. for merge replication over SSL to work you can not have any warnings about the certificate when opening the site using device's browser.

hth

kamil nowicki

|||

thanx for the response Kamil ... I have already installed the CA certificate on the device... the problem is when i access the merge replication address through IE on the device... it gives me the warning that "The name of the certificate is invalid or does not match the name of the site"

It also says that the certificate is from a trusted certifying authority and the certificate date is valid.

The certificate I am using for the merge replication website is a Wild Card Certificate (i.e. *.domnin-name.net) and the host header is "sql.domain-net.net"

If i install the certificate on a desktop PC and access the merge replication address through IE, it works fine without any warning. It says that the root CA has identified the site as sql.domain-net . However on the device it can not resolve the host header for some reason. I tried installing both Root CA and Wild card certificate on the device , but no use.

Any idea what could be the problem?

Awaiting,

|||

oops, looks like i did not read the entire message and wrongly assumed what the error was... sorry

i am not certain if PPC devices understand what a wildcard certificate is... maybe thats the problem ?

also, from my experience, if mobile IE gives you even a minor warning when accessing the agent hosted on IIS then the replication won't work.

could you try using a non-wildcard certificate and post the results ?

Merge Replication using SSL on a Windows Mobile 5.0 device

Dear ppl,

I have a windows mobile 5.0 application that syncronises with SQL Sever 2005 (Merge Replication). The replication works fine without SSL. But when using SSL it doesn't work .On the device i get an error

28037, A request to send data to the computer running IIS has failed.

I have created a a Wild Card Certificate (*.domain-name.net) using Microsoft Certificate Services (Stand Alone) and setup the replication website with replication virtual directory under it , in IIS, to use this certificate. The host header for the replication site is set to "sql.domain-name.net". I have binded the host headers to the port as well.

I can access the https://sql.domain-name.net/replication/sqlcesa30.dll from remote computer and device and it says Microsoft SQL Server Compact Edition Server Agent, that means it is setup correctly.

I have installed the certificate authority and its wild card certificate on the device Root Certificates. Now when I call repl.syncrhonise() from device .. it throws exception with error 28037, A request to send data to the computer running IIS has failed.

which i think means, it can't find the service url.

I am not sure what is the problem..something to do with Trust - SSL certificates... Is there any thing i am missing ?

Awaiting,

Just try to rule out the obvious - you have specified "https://" instead of "http:" in the application source code, right?

Did you run the sync app as the same window's credential as running "https://sql.domain-name.net/replication/sqlcesa30.dll" from IE?

Thanks.

|||

thanx for the response Zhou... well i m not using any authentication..I have setup the replication website Directory Security in IIS as Anonymous Access... Is it necessary to use credentials (any authentication windows,basic etc) ?

Awaiting,

|||

It would be nice to try different credentials, at least the basic authentication, Windows username and password.

However, you need to specify these information in your application code.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Nabeel -

I'm interested in what you find out.

I've been using SSL (with a certificate I created using Windows Server Certification Authority) with merge replication to SQL Server 2000 for a few years now. I'm in the process of trying to figure out how to migrate to SQL Server 2005 and SQL Server CE 2005. But I'm also noticing that Internet Explorer 7.0 can throw some very very nasty message to users browsing to my web site -- apparently, IE 7 sees these non-trusted certificates as a major security threat). So I'm now exploring how to deploy with a trusted certificate.

I'll keep an eye on how this thread develops and I'll post as I learn more information. Hope you will do the same.

Larry

|||

Nabeel -

Doing some research, you might want to take a look at this:

http://support.microsoft.com/kb/266695/EN-US/

Larry

|||

well if u take a look at this http://download.microsoft.com/download/6/d/9/6d9ab586-5bed-4cfc-b28b-d83dadb85b42/Windows_Mobile_Devices_and_Security_Proecting_Sensitive_Business_Information.pdf

It says that The Internet Explorer Mobile Web browser included in Pocket PC and Smartphone software uses 128-bit SSL (https) and PCT encryption technologies to access secured Web sites. In earlier versions, the High Encryption Pack may be required to upgrade from the native 40-bit encryption to 128-bit encryption. So i think it is not required to install the high encyption pack. However i tried to install it on a couple of devices WM5.0 and after installing and restarting the devices, the devices stopped working for some reason :) ... so I had to do for factory restart

|||

hi,

your device MUST recognize the certificate's CA. you should export your CA's root certificate into .cer file (via http://myca/certsrv/), copy it to the device, and import it into trusted root CA store there (just tap it and confirm). AFAIR you also need to do a soft-reset.

you can test if your device recognizes the site's certificate by launching IE on the device and going to the merge agent address. for merge replication over SSL to work you can not have any warnings about the certificate when opening the site using device's browser.

hth

kamil nowicki

|||

thanx for the response Kamil ... I have already installed the CA certificate on the device... the problem is when i access the merge replication address through IE on the device... it gives me the warning that "The name of the certificate is invalid or does not match the name of the site"

It also says that the certificate is from a trusted certifying authority and the certificate date is valid.

The certificate I am using for the merge replication website is a Wild Card Certificate (i.e. *.domnin-name.net) and the host header is "sql.domain-net.net"

If i install the certificate on a desktop PC and access the merge replication address through IE, it works fine without any warning. It says that the root CA has identified the site as sql.domain-net . However on the device it can not resolve the host header for some reason. I tried installing both Root CA and Wild card certificate on the device , but no use.

Any idea what could be the problem?

Awaiting,

|||

oops, looks like i did not read the entire message and wrongly assumed what the error was... sorry

i am not certain if PPC devices understand what a wildcard certificate is... maybe thats the problem ?

also, from my experience, if mobile IE gives you even a minor warning when accessing the agent hosted on IIS then the replication won't work.

could you try using a non-wildcard certificate and post the results ?

Merge Replication using SSL on a Windows Mobile 5.0 device

Dear ppl,

I have a windows mobile 5.0 application that syncronises with SQL Sever 2005 (Merge Replication). The replication works fine without SSL. But when using SSL it doesn't work .On the device i get an error

28037, A request to send data to the computer running IIS has failed.

I have created a a Wild Card Certificate (*.domain-name.net) using Microsoft Certificate Services (Stand Alone) and setup the replication website with replication virtual directory under it , in IIS, to use this certificate. The host header for the replication site is set to "sql.domain-name.net". I have binded the host headers to the port as well.

I can access the https://sql.domain-name.net/replication/sqlcesa30.dll from remote computer and device and it says Microsoft SQL Server Compact Edition Server Agent, that means it is setup correctly.

I have installed the certificate authority and its wild card certificate on the device Root Certificates. Now when I call repl.syncrhonise() from device .. it throws exception with error 28037, A request to send data to the computer running IIS has failed.

which i think means, it can't find the service url.

I am not sure what is the problem..something to do with Trust - SSL certificates... Is there any thing i am missing ?

Awaiting,

Just try to rule out the obvious - you have specified "https://" instead of "http:" in the application source code, right?

Did you run the sync app as the same window's credential as running "https://sql.domain-name.net/replication/sqlcesa30.dll" from IE?

Thanks.

|||

thanx for the response Zhou... well i m not using any authentication..I have setup the replication website Directory Security in IIS as Anonymous Access... Is it necessary to use credentials (any authentication windows,basic etc) ?

Awaiting,

|||

It would be nice to try different credentials, at least the basic authentication, Windows username and password.

However, you need to specify these information in your application code.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Nabeel -

I'm interested in what you find out.

I've been using SSL (with a certificate I created using Windows Server Certification Authority) with merge replication to SQL Server 2000 for a few years now. I'm in the process of trying to figure out how to migrate to SQL Server 2005 and SQL Server CE 2005. But I'm also noticing that Internet Explorer 7.0 can throw some very very nasty message to users browsing to my web site -- apparently, IE 7 sees these non-trusted certificates as a major security threat). So I'm now exploring how to deploy with a trusted certificate.

I'll keep an eye on how this thread develops and I'll post as I learn more information. Hope you will do the same.

Larry

|||

Nabeel -

Doing some research, you might want to take a look at this:

http://support.microsoft.com/kb/266695/EN-US/

Larry

|||

well if u take a look at this http://download.microsoft.com/download/6/d/9/6d9ab586-5bed-4cfc-b28b-d83dadb85b42/Windows_Mobile_Devices_and_Security_Proecting_Sensitive_Business_Information.pdf

It says that The Internet Explorer Mobile Web browser included in Pocket PC and Smartphone software uses 128-bit SSL (https) and PCT encryption technologies to access secured Web sites. In earlier versions, the High Encryption Pack may be required to upgrade from the native 40-bit encryption to 128-bit encryption. So i think it is not required to install the high encyption pack. However i tried to install it on a couple of devices WM5.0 and after installing and restarting the devices, the devices stopped working for some reason :) ... so I had to do for factory restart

|||

hi,

your device MUST recognize the certificate's CA. you should export your CA's root certificate into .cer file (via http://myca/certsrv/), copy it to the device, and import it into trusted root CA store there (just tap it and confirm). AFAIR you also need to do a soft-reset.

you can test if your device recognizes the site's certificate by launching IE on the device and going to the merge agent address. for merge replication over SSL to work you can not have any warnings about the certificate when opening the site using device's browser.

hth

kamil nowicki

|||

thanx for the response Kamil ... I have already installed the CA certificate on the device... the problem is when i access the merge replication address through IE on the device... it gives me the warning that "The name of the certificate is invalid or does not match the name of the site"

It also says that the certificate is from a trusted certifying authority and the certificate date is valid.

The certificate I am using for the merge replication website is a Wild Card Certificate (i.e. *.domnin-name.net) and the host header is "sql.domain-net.net"

If i install the certificate on a desktop PC and access the merge replication address through IE, it works fine without any warning. It says that the root CA has identified the site as sql.domain-net . However on the device it can not resolve the host header for some reason. I tried installing both Root CA and Wild card certificate on the device , but no use.

Any idea what could be the problem?

Awaiting,

|||

oops, looks like i did not read the entire message and wrongly assumed what the error was... sorry

i am not certain if PPC devices understand what a wildcard certificate is... maybe thats the problem ?

also, from my experience, if mobile IE gives you even a minor warning when accessing the agent hosted on IIS then the replication won't work.

could you try using a non-wildcard certificate and post the results ?

sql

Merge Replication using SSL on a Windows Mobile 5.0 device

Dear ppl,

I have a windows mobile 5.0 application that syncronises with SQL Sever 2005 (Merge Replication). The replication works fine without SSL. But when using SSL it doesn't work .On the device i get an error

28037, A request to send data to the computer running IIS has failed.

I have created a a Wild Card Certificate (*.domain-name.net) using Microsoft Certificate Services (Stand Alone) and setup the replication website with replication virtual directory under it , in IIS, to use this certificate. The host header for the replication site is set to "sql.domain-name.net". I have binded the host headers to the port as well.

I can access the https://sql.domain-name.net/replication/sqlcesa30.dll from remote computer and device and it says Microsoft SQL Server Compact Edition Server Agent, that means it is setup correctly.

I have installed the certificate authority and its wild card certificate on the device Root Certificates. Now when I call repl.syncrhonise() from device .. it throws exception with error 28037, A request to send data to the computer running IIS has failed.

which i think means, it can't find the service url.

I am not sure what is the problem..something to do with Trust - SSL certificates... Is there any thing i am missing ?

Awaiting,

Just try to rule out the obvious - you have specified "https://" instead of "http:" in the application source code, right?

Did you run the sync app as the same window's credential as running "https://sql.domain-name.net/replication/sqlcesa30.dll" from IE?

Thanks.

|||

thanx for the response Zhou... well i m not using any authentication..I have setup the replication website Directory Security in IIS as Anonymous Access... Is it necessary to use credentials (any authentication windows,basic etc) ?

Awaiting,

|||

It would be nice to try different credentials, at least the basic authentication, Windows username and password.

However, you need to specify these information in your application code.

This posting is provided AS IS with no warranties, and confers no rights.

|||

Nabeel -

I'm interested in what you find out.

I've been using SSL (with a certificate I created using Windows Server Certification Authority) with merge replication to SQL Server 2000 for a few years now. I'm in the process of trying to figure out how to migrate to SQL Server 2005 and SQL Server CE 2005. But I'm also noticing that Internet Explorer 7.0 can throw some very very nasty message to users browsing to my web site -- apparently, IE 7 sees these non-trusted certificates as a major security threat). So I'm now exploring how to deploy with a trusted certificate.

I'll keep an eye on how this thread develops and I'll post as I learn more information. Hope you will do the same.

Larry

|||

Nabeel -

Doing some research, you might want to take a look at this:

http://support.microsoft.com/kb/266695/EN-US/

Larry

|||

well if u take a look at this http://download.microsoft.com/download/6/d/9/6d9ab586-5bed-4cfc-b28b-d83dadb85b42/Windows_Mobile_Devices_and_Security_Proecting_Sensitive_Business_Information.pdf

It says that The Internet Explorer Mobile Web browser included in Pocket PC and Smartphone software uses 128-bit SSL (https) and PCT encryption technologies to access secured Web sites. In earlier versions, the High Encryption Pack may be required to upgrade from the native 40-bit encryption to 128-bit encryption. So i think it is not required to install the high encyption pack. However i tried to install it on a couple of devices WM5.0 and after installing and restarting the devices, the devices stopped working for some reason :) ... so I had to do for factory restart

|||

hi,

your device MUST recognize the certificate's CA. you should export your CA's root certificate into .cer file (via http://myca/certsrv/), copy it to the device, and import it into trusted root CA store there (just tap it and confirm). AFAIR you also need to do a soft-reset.

you can test if your device recognizes the site's certificate by launching IE on the device and going to the merge agent address. for merge replication over SSL to work you can not have any warnings about the certificate when opening the site using device's browser.

hth

kamil nowicki

|||

thanx for the response Kamil ... I have already installed the CA certificate on the device... the problem is when i access the merge replication address through IE on the device... it gives me the warning that "The name of the certificate is invalid or does not match the name of the site"

It also says that the certificate is from a trusted certifying authority and the certificate date is valid.

The certificate I am using for the merge replication website is a Wild Card Certificate (i.e. *.domnin-name.net) and the host header is "sql.domain-net.net"

If i install the certificate on a desktop PC and access the merge replication address through IE, it works fine without any warning. It says that the root CA has identified the site as sql.domain-net . However on the device it can not resolve the host header for some reason. I tried installing both Root CA and Wild card certificate on the device , but no use.

Any idea what could be the problem?

Awaiting,

|||

oops, looks like i did not read the entire message and wrongly assumed what the error was... sorry

i am not certain if PPC devices understand what a wildcard certificate is... maybe thats the problem ?

also, from my experience, if mobile IE gives you even a minor warning when accessing the agent hosted on IIS then the replication won't work.

could you try using a non-wildcard certificate and post the results ?

Merge Replication Updates on Initialise (SQLServer 2005)

I have a merge replication scenario where the technicians are filtered by their individual codes. Each technician has his own mobile device for collecting and synchronising data.

On initialisation of the subscription, the user should only get the data that is relevant to their code.

The client is staging the rollout to all of their technicians and as the system has grown, we have noticed that there are a number (getting larger every day) of updates that are creeping into the initialisation of each subscription.

My understanding is that an initialisation will only ever apply the inserts necessary to create the subscriber database.

Is my understadning incorrect?

Is the some parameter in the creation of the publication that we have set incorrectly?

Is there something wrong with SQLServer 2005 itself?

I am very interested to hear anyones comments or advice around this issue.

Thanks
Steve

Hi Steve,

When you initialize the client, you are probably using dynamic snapshot, and when those dynamic snapshots become old the initializing will also need to get the incremental changes which happened after the old dynamic snapshot generated.

You can choose to have the snapshots refreshed on a schedule so new Subscribers that subscribe to a partition for which a snapshot has been created will receive an up-to-date snapshot.

For more information, please refer to Books online topic: Snapshots for Merge Publications with Parameterized Filters

Hope it helps.

Wanwen

|||I assume you mean the snapshot agent running regularly - which it does every night.

Seems to be something else - I'll just have to keep looking|||

If you are using dynamic filtering, there are 2 snapshots to consider:

1. Regular snapshots

2. Dynamic snapshots

The regular snapshot do not have data in them.

The dynamic snapshots are the ones that have data in them.

You need to make sure that both are run before your subscriber syncs so that they can use the bcp files from the dynamic snapshot to complete the sync process in a much more timely manner (faster).

sql

Monday, March 12, 2012

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

Merge replication losing changes.

Hi,

I am facing a wierd problem while sync'ing. When I am do a synchonize from a mobile device to the server DB, one of my table column fails to update while all other columns get updated.

The table uses row level tracking (tried making it column level too) and the field which fails to update is a date field (Nullable).

This happens in case of syncing after updating >1 records on device. But syncing after updating just 1 record on device, this date field gets updated as expected.

Note: I have filters set for this table. The filter downloads fields only with this date field=null.

I need to downlaod all records meeting this filter condition but at the same time, this filter should not be applied while uploading changes as I think this is what the problem is.

If anyones has faced this and got a solution, please let me know.

Thanks.

What exactly does your filter look like?

|||

Do you have any default constraints on the date column or any triggers that could be affecting this column and hence affecting your expected output?

Also are you using vertical partitioning where some of the columns in the table do not go to the subbscriber? And is it that this date column is positioned after a column that is vertically filtered out?

It would be great if you can narrow down the problem and send the publication/article creation scripts.

Typically this is how a filter works:

Lets say you have a filter subset_filterclause= 'age > 20'

Now initially all the rows at the publisher that match this criteria will be downloaded, others that dont qualify will not be downloaded.

Furthermore, inserts/updates at the publisher which make rows qualify this filter will be downloaded to the subscriber.

Finally, all inserts/updates at the subscriber whether they qualify the filter or not will be uploaded to the publisher. Now all the rows that did not qualify the filter will be downloaded as deletes to the subscriber and will be delete at the subscriber.

I am trying to understand you scenario and here is what I understand:

you are saying that if you update 1 row from age=40 to age=20, you will see this update go to the publisher. But if you update 10 rows from age=40 to age=20, they will not go to the publisher?

Monday, February 20, 2012

Merge replication 32-bit IIS - 64-bit SQL Server 2005 -Windows Mobile 5 device

We have setup a 32-bit IIS server on a 32-bit machine. Installed necessary SQL Server components (32-bit replication components). We have installed SQL Server 2005 64-bit SP1 with latest hotfixes on a 64-bit machine.

On the Mobile device we installed SQL Server 2005 Mobile.

When we start mergereplication the problem we have is error 28009 (An instance of the SQL Server Reconciler cannot be created) on the mobile device. I read allot about it, but still have a questetion.

Should this work?

I found the doc: SQL Server 2005 Upgrade Technical Resource Guide:

Note: If your upgrade involves SQL Server CE subscribers, you must perform additional actions on the IIS server. You will need to install SQL Server 2005 client connectivity components along with SQL Server Mobile Edition components on the IIS server. You will also have to replace sqlcesa30.dlll, sqlcerp30.dll, and all the replication components on the IIS server.

From this I conclude it should work or is the doc in error?

If it does not, what can we do?

32-bit SQL Server 2005 on the 64-bit machine should work. (Can a 64-bit SQL Server 2005 run alongside 32-bit SQL Server 2005?).

Beste regards,
Hans Mank

The SQL Server CE components are also needed on the IIS server. Please install those and try again.