Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filter data back to publisher

Hi all,

We have merge replication set up between 2 instances of SQL 2005 through web synchronization. I was wondering is it possible if the subscriber adds data at their end can we selectively edit what data gets uploaded back to the publisher? Will this work if I add a filter to the appropriate article at the publisher or will that only limit what goes down to the subscriber rather than vice-versa?

Also, could we set up web sychronization if the publisher had no direct access to the IIS server (ie. was not on the same network, available only through port 80, 443)?

Thanks,

Iain

Hey lain,

You can't selectively edit what data gets uploaded back to the publisher. There's a parameter that you can use in sp_addmergearticle @.subscriber_upload_options which you can set to (0,1,2) NO restrictions, changes are allowed at sub but not uploaded to the publisher , and last option doesn't allow any changed at the subscriber respectively. So you can either upload all the changes or none of the changes, but you can't filter out the data to be uploaded.

There's a workaround but that's not recommended. You can enable the insert triggers on the data you want to upload to the publisher, and then disable the insert triggers on the data you want to filter out. By doing that merge agent won’t detect those changes and they won't be uploaded to the publisher.

hope that helps

bishoyy

|||

Ok, thanks. Looks like if they want to implement their requirement they will just have to become the publisher. I think messing the the triggers will just lead to all sorts of issues and problems that I could well do without!

Thanks for the help.

Filegroup restore Issue

I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

|||Hi Jag,
oh I didnt try with no_truncate thing. what If I dont have a transaction log and I didnt get a chance to take the tail log backup. Wont the filegroup restore work? Thanks for your reply Jag.|||

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag

Filegroup restore Issue

I restored a file group of a database. The restore came back saying success. But when I looked at the state of that particular filegroup using select * from Sys.database_Files it says "restoring" any thoughts on this? I restored that file group with full recovery did I do any thing special for a file group restore?

with smiles
santhoshDoes any one face this issue yet? Any resolution.|||

Hi Santosh,

You need to perform recovery using transaction log backups taken after the backup you used to restore the filegroup and then finally recover the last log backup taken using No_truncate.

I was wondering if you did a backup log with no_truncate before restoring the filegroup.

regards

Jag

|||Hi Jag,
oh I didnt try with no_truncate thing. what If I dont have a transaction log and I didnt get a chance to take the tail log backup. Wont the filegroup restore work? Thanks for your reply Jag.|||

Hi Santosh,

You need to do a tail-log back up and recover the database using the all the log backups (including tail backup in the end.) taken after database backup, that used for restoring the filegroup.

This is because you are performing partial restore and for this to you need to perform complete recovery.

if the complete recovery is not performed the LSNs of restored filegroup are different from rest of the database.

regards

Jag