Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

Out of Partition Deletions

Hi,

I have merge replication working between SQL Server 2005 and SQL Server 2005 Mobile Edition. The subscriber data partitions are determined by device serial number, which is being passed in as the HOST_NAME() parameter for filtering. Lately I've received complaints of slow synchronizations, and a quick inspection showed an unexpectedly high number of deletions.

Further investigation has revealed that subscribers are being passed deletions for data which is outside of their data partitions. I found that inserts and updates were well behaved - if a row was inserted or updated outside of the partition, nothing was replicated. However, if an out of partition deletion was performed, it was in fact replicated - to every subscriber.

So, why is it that replication knows the row doesn't belong to a data partition for inserts and updates, but not deletes? I've been unable to find either an explanation or workaround for this problem.

Any insight would be greatly appreciated!

Thanks,
Adrien.

If i had to give a rough guess i think what it is doing is realigning the partitions. Your deletes must be deleting records from the filtering hiherarchy which change a partition. So those are being sent everywhere to make sure the subscribers have the correct partition of data.

Take a look at parameter @.allow_partition_realignment

http://technet.microsoft.com/en-us/library/ms176103.aspx

Unfortunately if you are using dynamic filtering it will not allow you to turn this off.

Martin

|||That's an interesting setting, but it doesn't seem to apply. I'll explain things with an example:

Users
user_id
warehouse_id
user_name
serial_number

Warehouses
warehouse_id
warehouse_name

Orders
order_id
warehouse_id
order_date

OrderDetail
detail_id
order_id
item_name
item_quantity

serial_number is being passed for HOST_NAME() dynamic filtering. A user should only see orders belonging to his assigned warehouse (and obviously only order details linked to orders belonging to that warehouse). For the purpose of this example, say we have two users, User1 and User2, and two warehouses, Warehouse1 and Warehouse2. Each warehouse has only one order, and each order has two order details.

The problem I'm having is that deleting (at the publisher) an order detail for an order belonging to Warehouse2 would cause a deletion not just for User2, but for User1 as well. I don't see how this is causing partition changes (unless moving from a partition to no partition counts as a partition change).

Hopefully this helps clarify my problem somewhat. This is still causing me no end of problems, and any advice would be greatly appreciated.

Thanks,
Adrien.

OS error 53 for SQL servers VPN replication?

I've got this error message when trying to create a subscriber from a
publisher through VPN in a non-trusted domain, I am using SQL server
authentication:
The schema script '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' could not be propagated to the subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
----
--
The process could not read file '\\MERCURY\G$\Program Files\Microsoft SQL
Server\MSSQL$MERCURY\ReplData\unc\MERCUR
Y$MERCURY_Northwind_NorthwindMercury
Pub1\20040221225704\snapshot.pre' due to OS error 53.
(Source: ECSWEB (Agent); Error number: 0)
----
--
The network path was not found.
(Source: (OS); Error number: 53)
What is OS error 53 and how can it be fixed?This error is generally caused by the fact that the SQL Server Agent
startup account on either the publisher or subscriber is not a local
administrator on one or both of the machines.
The default location for the snapshot folder is an administrative share and
as such the SQL Server Agent acount must be a local adminsitrator to access
it. From the error I am going to presume that you are creating a pull
subscription so the SQL Server Agent startup account at the subscriber must
be a local admin on the publisher. You can verify if this is the problem by
logging on to the subscriber using the SQL Server Agent startup account and
try to map a drive to the snap shot folder on the publisher:
\\MERCURY\G$\
If that fails with the same error then you have your reason for the failure.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Thanks Rand,
Yes that's right the two SQL servers are in different
non-trusted domains connected through VPN. The Distributor and Publisher SQL
Server is in the NARC domain and Agent login account as NARC\Administrator.
And the subscriber is in the HOT domain and Agent Login account is
HOT\Administrator. No trusted can be established between them. What must I
do to achieved successful pull subscription? How do I allow permissions in
the NARC domain for HOT\Administrator if there's no trusted' What about
independent SQL server logins to access shares in NARC domain(How do I set
it up if possible)?
Thanks
"Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> This error is generally caused by the fact that the SQL Server Agent
> startup account on either the publisher or subscriber is not a local
> administrator on one or both of the machines.
> The default location for the snapshot folder is an administrative share
and
> as such the SQL Server Agent acount must be a local adminsitrator to
access
> it. From the error I am going to presume that you are creating a pull
> subscription so the SQL Server Agent startup account at the subscriber
must
> be a local admin on the publisher. You can verify if this is the problem
by
> logging on to the subscriber using the SQL Server Agent startup account
and
> try to map a drive to the snap shot folder on the publisher:
> \\MERCURY\G$\
> If that fails with the same error then you have your reason for the
failure.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Joe,
I haven't got Rand's post available here, but I would advise to either use
SQL security or use pass-through authentication. Both are described in the
link in my earlier post.
Regards,
Paul Ibison
"Joe Mine" <huytuanattpgdotcomdotau> wrote in message
news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
> Thanks Rand,
> Yes that's right the two SQL servers are in different
> non-trusted domains connected through VPN. The Distributor and Publisher
SQL
> Server is in the NARC domain and Agent login account as
NARC\Administrator.
> And the subscriber is in the HOT domain and Agent Login account is
> HOT\Administrator. No trusted can be established between them. What must I
> do to achieved successful pull subscription? How do I allow permissions
in
> the NARC domain for HOT\Administrator if there's no trusted' What about
> independent SQL server logins to access shares in NARC domain(How do I set
> it up if possible)?
> Thanks
>
> "Rand Boyd [MSFT]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:fjJVvoV$DHA.3712@.cpmsftngxa06.phx.gbl...
> and
> access
> must
> by
> and
> failure.
rights.
>|||Hi Paul,
Can you please give me the links again. I cannot locate your
previous post. The only problem I am having is I cannot from the Subcriber
SQL Server access the Snapshot Folder in the Distributor SQL Server because
this is two different non-trusted domains.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eLNYLS8$DHA.2212@.TK2MSFTNGP10.phx.gbl...
> Joe,
> I haven't got Rand's post available here, but I would advise to either use
> SQL security or use pass-through authentication. Both are described in the
> link in my earlier post.
> Regards,
> Paul Ibison
>
> "Joe Mine" <huytuanattpgdotcomdotau> wrote in message
> news:u9ThB04$DHA.2216@.TK2MSFTNGP10.phx.gbl...
different
> SQL
> NARC\Administrator.
I
permissions
> in
about
set
share
problem
account
> rights.
>|||Try this article:
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted http://support.microsoft.com/?id=321822
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.