Dynamic filters Snap shot - merger subscirption web synchronizati

Hi Prafull,
I understand that you had a large amount of data to be replicated to your new subscriber with merge
replication. You would like to avoid busy network impact. You would like to know how to synchronize
data with dynamic filter in this case.
If I have misunderstood, please let me know.

For this special requirement, one method I can think is that manually synchronizing new replication
subscriptions by using backup and restore. The following KB article addresses this issue dedicately:
How to manually synchronize replication subscriptions by using backup or restore
http://support.microsoft.com/kb/320499

As the KB article mentioned, it is mainly used for the following three reasons:
1. You have large tables that you have to transfer to the subscriber.
2. Network bandwidth can only handle incremental changes; therefore, large BCPs may time out.
3. The publisher is a production server; therefore, business needs require the down time to be
minimized.

Please feel free to let me know if you have any other questions or concerns.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...#notifications.

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/subscripti...t/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Hi Charles,

Thanks for your reply.

Theoritically backup or restore will not work for me because of following
reasons:
1. We have to provide option of data selection to user.
2. Based on user selection, using RMO subscription will be created.
3. Even within subscription user has option to select set of data.
4. Based on this set user should get the data.

Based on my little knowledge, if I use Backup / restore user will have all
the data.

Please let me know if my requirements can be met using backup/restore.

What i thought was i will create the snapshot and send that via CD and then
based on user selection i will overwrite HOST_NAME() and synchronization will
take this value to populate subscriber's database.

Moreover i once again went through the article you mentioned and have
following queries: (I went through "Merge replication" section of the
article. Please take a note that i have to use web synchronization)
1. "When the Merge Agent runs, it first uses the snapshot to create the
merge replication tables. All the changes that were made since the snapshot
was generated are applied to the subscriber:" -- Merge replication tables
have already been created by the restore then why these tables will be
created? Which snapshot be used one that is on server? or i need to ship
snapshot along with the backup database.
2. "If you deleted any rows between step 1 and step 2 in this procedure, the
Merge Agent reports that no changes must be made because the rows do not
exist on the subscriber. The backup or restore was performed after the rows
were deleted on the publisher." -- Can you explain this with example.

I have asked many questions in my mail. So please do reply them separately.
Based on my business need filtering of data is must. If that is not possible
then i have present different approach to them.

Please note that filtering will work for normal sync where data volume is
less. I have tested it and it works. Only problem i have is with the initial
data setup.

--
Thanks and Regards,
Prafull


""Charles Wang [MSFT]"" wrote:

> Hi Prafull,
> I understand that you had a large amount of data to be replicated to your new subscriber with merge
> replication. You would like to avoid busy network impact. You would like to know how to synchronize
> data with dynamic filter in this case.
> If I have misunderstood, please let me know.
>
> For this special requirement, one method I can think is that manually synchronizing new replication
> subscriptions by using backup and restore. The following KB article addresses this issue dedicately:
> How to manually synchronize replication subscriptions by using backup or restore
> http://support.microsoft.com/kb/320499
>
> As the KB article mentioned, it is mainly used for the following three reasons:
> 1. You have large tables that you have to transfer to the subscriber.
> 2. Network bandwidth can only handle incremental changes; therefore, large BCPs may time out.
> 3. The publisher is a production server; therefore, business needs require the down time to be
> minimized.
>
> Please feel free to let me know if you have any other questions or concerns.
>
> Best regards,
> Charles Wang
> Microsoft Online Community Support
> ===========================================================
> Delighting our customers is our #1 priority. We welcome your
> comments and suggestions about how we can improve the
> support we provide to you. Please feel free to let my manager
> know what you think of the level of service provided. You can
> send feedback directly to my manager at: msdnmg@microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscripti...#notifications.
>
> 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/subscripti...t/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>

Hi Hilary,

No.

In my scenario, I have to overwrite HOST_NAME() function as user has ability
to select the data he wants. Thats why i cannot create the susbsciber
specific snapshot and put that in dynamicsnapshotlocation.

Please let me know if there is any other way. Or am i missing any thing?

--
Thanks and Regards,
Prafull


"Hilary Cotter" wrote:

> Have you tried using the dynamicsnapshotlocation parameter and then putting
> the snapshto in place in the subscriber in the location specified in the
> dynamicsnapshotlocation parameter?
>
> "PrafullSurana" wrote in message
> news:9FB1EE13-0775-4BCF-84CA-A62D7A999E1C@microsoft.com...
> > Hi,
> >
> > I am having following setup.
> > 1. SQL Express -- Merge pull subscription
> > 2. SQL Server 2005 -- Publication with dynamic filter
> > 3. Using RMO have overwritten HOST_NAME and passing the values required
> > for
> > the filter.
> >
> > This works file when i use web synchronization for applying initial
> > snapshot
> > and further synchronization. BUT
> >
> > For new subscription I want to apply the snapshot from the UNC path or CD
> > as
> > the data size is huge and it takes very long time to apply the snapshot.
> > When i create the snapshot on server for this publication file there are
> > no
> > bcp file. I can understand this as HOST_NAME is going to have value at run
> > time.
> > as there is no bcp file when i apply the snapshot at subscriber there is
> > no
> > data in table.
> >
> > How do i implement this? So that i can use the dynamic filter as well
> > snapshot from the UNC / CD?
> >
> > --
> > Thanks and Regards,
> > Prafull
>

Hi Prafull,
Regarding your 1st question, the KB article looks a little confused and I could not find any document clearly talking about it. Currently I am trying to consult our product team, however I have not
got their confirmation now. I will continue following up them.

Regarding your 2nd question, the KB article may not explain it clearly. Let me try clarifying it again. After you create a publication for the database, try deleting some rows in a table in the
publication database and the deleted rows will be recorded into the merge replication system tables now, then you backup the database and then restore it to the subscriber, though those
deleted rows are recorded on the publisher, there will not be any synchronization (DELETE on subscription) happening because the merge agent could not find the deleted records in the table in
the subscription database. For example, after step1, you execute a delete statement "DELETE FROM TABLE1 WHERE ID IN (1,2,3)"; then you perform step2, the restored database on the
subscriber does not contain the records (ID=1,2,3) now. When synchronization happens, merge agent first check the merge replication system tables and found there are some records deleted,
and then it will check the subscription, but it could not find the records yet, so it thinks that the records (ID=1,2,3) have been deleted and so it reports that no changes must be made.

Look back to your original requirement, I think that you still need to consider using those dynamic functions like HOST_NAME or USER_NAME since there is no other way to implement dynamic
filters in current editions of SQL Server 2005 Replication. Consider the complex scenario of this issue and our managed newsgroup is focused on break/fix issues that are neither urgent nor
complex. To get an effective resolution, I recommended that you contact Microsoft Customer Support Services (CSS) via telephone so that a dedicated Support Professional can assist you in a
more efficient manner. Please be advised that contacting phone support will be a charged call.

To obtain the phone numbers for specific technology request please take a look at the web site listed below.
http://support.microsoft.com/default...S;PHONENUMBERS

If you are outside the US please see http://support.microsoft.com for regional support phone numbers.

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

Can somebody help me to resolve this issue?

--
Thanks and Regards,
Prafull


"PrafullSurana" wrote:

> Hi Hilary,
>
> No.
>
> In my scenario, I have to overwrite HOST_NAME() function as user has ability
> to select the data he wants. Thats why i cannot create the susbsciber
> specific snapshot and put that in dynamicsnapshotlocation.
>
> Please let me know if there is any other way. Or am i missing any thing?
>
> --
> Thanks and Regards,
> Prafull
>
>
> "Hilary Cotter" wrote:
>
> > Have you tried using the dynamicsnapshotlocation parameter and then putting
> > the snapshto in place in the subscriber in the location specified in the
> > dynamicsnapshotlocation parameter?
> >
> > "PrafullSurana" wrote in message
> > news:9FB1EE13-0775-4BCF-84CA-A62D7A999E1C@microsoft.com...
> > > Hi,
> > >
> > > I am having following setup.
> > > 1. SQL Express -- Merge pull subscription
> > > 2. SQL Server 2005 -- Publication with dynamic filter
> > > 3. Using RMO have overwritten HOST_NAME and passing the values required
> > > for
> > > the filter.
> > >
> > > This works file when i use web synchronization for applying initial
> > > snapshot
> > > and further synchronization. BUT
> > >
> > > For new subscription I want to apply the snapshot from the UNC path or CD
> > > as
> > > the data size is huge and it takes very long time to apply the snapshot.
> > > When i create the snapshot on server for this publication file there are
> > > no
> > > bcp file. I can understand this as HOST_NAME is going to have value at run
> > > time.
> > > as there is no bcp file when i apply the snapshot at subscriber there is
> > > no
> > > data in table.
> > >
> > > How do i implement this? So that i can use the dynamic filter as well
> > > snapshot from the UNC / CD?
> > >
> > > --
> > > Thanks and Regards,
> > > Prafull
> >

Hi Prafull,
Appreciate your understanding that for such complex advisory questions, they are out of our MSDN Managed Newsgroup support umbrella. Though we will try to give you some general
suggestions, if they are not helpful to you, to get effective resolution, please contact CSS or Microsoft Advisory Services (http://support.microsoft.com/gp/advisoryservice) for more dedicated
support.

Thanks!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: msdnmg@microsoft.com.
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================