Showing posts with label mergereplication. Show all posts
Showing posts with label mergereplication. Show all posts

Friday, March 23, 2012

replication cause blocking or deadlock?

Hi all,
I have a question about the replication that induce any blocking or deadlock in SQL? the scenario is server A will have merge
replication with server B on database D1 with table T1, concurrently with a transactional replication with server B on database D2
with T2 (push subscription). Since i found the merge replication is running ok, but when the distribution agent running for
trasaactional replication, a blocking is occur. Any idea of this issues? thanks
Hi,
Thanks for your post.
From your descriptions, I understood that your SQL Agent will raise a
blocking while your merge replication and transactional replication happens
at the same time . Have I understood you? Correct me if I was wrong.
First of all, please know that replication issues tend to be very complex
and hard to troubleshoot in newsgroups. If you need further assistance, I
recommend that you open a Support incident with Microsoft Product Support
Services (PSS) so that a dedicated Support Professional can work with you
in a more timely and efficient manner. If you need any help in this regard,
please let me know.
For a complete list of Microsoft Product Support Services phone numbers,
please go to the following address on the World Wide Web:
http://support.microsoft.com/directory/overview.asp
For now, If you'd still like to continue working via the newsgroup, I want
to set your expectations that the issue might take a long time to narrow
down. During the course of troubleshooting, we may redirect you to PSS if
required.
Generally speaking, blocking or deadlocking occurs when the users on the
system were not freeing up resources, perhaps because they were holding
long running transactions. The user transactions were getting locks on
resources (rows, pages, and/or tables) that the snapshot agent needed
access to and the snapshot agent could not continue without those locks,
and vice versa. The snapshot agent was chosen as a deadlock victim. When
you tried the snapshot agent on a less busy system it was being blocked
instead of deadlocked but this still resulted in the snapshot agent failing
as it now timed out.
So here are some suggestions prevent from the deadlock
1. Running the snapshot agent during a time when few or no users are on the
system. This will reduce but not eliminate the change of a deadlock or
blocking. You can change the QueryTimeout to something higher than the
default of 300 seconds also.
2. Running the snapshot agent with the concurrent snapshot option. This
will reduce but not eliminate the change of a deadlock or blocking. The
StartQueueTimeout can be set to 0 when using this option for an indefinite
wait.
3. Manually synchronizing the subscription during a time when no users are
on the system. You ended up putting the publication database into single
user mode then executing the snapshot agent. This prevented both
deadlocking and blocking.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||HI Mingqing Cheng,
thanks for your reply, actually the snapshot agent is ok when running but when i start to synchronizing manually, the blocking
occur. It is quite strange that the merge replication is work on Table T1 and transactional replication is work on another table T2
will induce the Blocking.
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:2eX0rUDrEHA.752@.cpmsftngxa06.phx.gbl...
> Hi,
> Thanks for your post.
> From your descriptions, I understood that your SQL Agent will raise a
> blocking while your merge replication and transactional replication happens
> at the same time . Have I understood you? Correct me if I was wrong.
> First of all, please know that replication issues tend to be very complex
> and hard to troubleshoot in newsgroups. If you need further assistance, I
> recommend that you open a Support incident with Microsoft Product Support
> Services (PSS) so that a dedicated Support Professional can work with you
> in a more timely and efficient manner. If you need any help in this regard,
> please let me know.
> For a complete list of Microsoft Product Support Services phone numbers,
> please go to the following address on the World Wide Web:
> http://support.microsoft.com/directory/overview.asp
> For now, If you'd still like to continue working via the newsgroup, I want
> to set your expectations that the issue might take a long time to narrow
> down. During the course of troubleshooting, we may redirect you to PSS if
> required.
> Generally speaking, blocking or deadlocking occurs when the users on the
> system were not freeing up resources, perhaps because they were holding
> long running transactions. The user transactions were getting locks on
> resources (rows, pages, and/or tables) that the snapshot agent needed
> access to and the snapshot agent could not continue without those locks,
> and vice versa. The snapshot agent was chosen as a deadlock victim. When
> you tried the snapshot agent on a less busy system it was being blocked
> instead of deadlocked but this still resulted in the snapshot agent failing
> as it now timed out.
> So here are some suggestions prevent from the deadlock
> 1. Running the snapshot agent during a time when few or no users are on the
> system. This will reduce but not eliminate the change of a deadlock or
> blocking. You can change the QueryTimeout to something higher than the
> default of 300 seconds also.
> 2. Running the snapshot agent with the concurrent snapshot option. This
> will reduce but not eliminate the change of a deadlock or blocking. The
> StartQueueTimeout can be set to 0 when using this option for an indefinite
> wait.
> 3. Manually synchronizing the subscription during a time when no users are
> on the system. You ended up putting the publication database into single
> user mode then executing the snapshot agent. This prevented both
> deadlocking and blocking.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi Cweb,
Thanks for your further explaination!
To get a better picture of the problem we would like to reproduct the
issue on our end. To expedite the process, please provide us the detailed
reproduce steps, which, I believe, will make us closer and quicker to the
resolution.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
|||HI Mingqing Cheng,
thanks for ur help, the problem seems resolved. Just know that the distribution agent running on transactional replication
scheduled with continuously, when i ask the colleague change it to run hourly, then the blocking seems disappeared. I don't know the
agent schedule will induce the blocking? Anyway, thanks for ur help
""Mingqing Cheng [MSFT]"" <v-mingqc@.online.microsoft.com> wrote in message news:wy6GdYTrEHA.1344@.cpmsftngxa06.phx.gbl...
> Hi Cweb,
> Thanks for your further explaination!
> To get a better picture of the problem we would like to reproduct the
> issue on our end. To expedite the process, please provide us the detailed
> reproduce steps, which, I believe, will make us closer and quicker to the
> resolution.
> Thank you for your patience and corperation. If you have any questions or
> concerns, don't hesitate to let me know. We are here to be of assistance!
>
> Sincerely yours,
> Mingqing Cheng
> Online Partner Support Specialist
> Partner Support Group
> Microsoft Global Technical Support Center
> Introduction to Yukon! - http://www.microsoft.com/sql/yukon
> This posting is provided "as is" with no warranties and confers no rights.
> Please reply to newsgroups only, many thanks!
>
|||Hi CWeb,
Thanks for your prompt updates letting me know the status of this issue.
Running agent hourly may have opportunity to release the resource so that
the block was also released or reduce the conflict of resource scrambling,
however, I am afraid it is just a workaround but we still cannot define the
root cause.
Anyway, it's great to hear it works fine for you and if you would love to
find the root cause of it, help me reproduce it on my side with your
scripts. Moreover, you could re-open this thread or make a new one whenever
you would like to further help on this issue. We are here to be of
assistance!
Sincerely yours,
Mingqing Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Wednesday, March 21, 2012

Replication between 2 SQL from 2 Windows SBS 2003?

Replication between 2 SQL from 2 Windows SBS 2003?
I have 2 SBS servers with SQL 2000. I would like to do a Merge
replication, I did it using wizard and everywhere I choose not trusted
connection but SQL login and password:
Configure '192.168.10.3' as a Publisher. Use '192.168.10.3' as the
Distributor.
Use '\\192.168.10.3\f$\Microsoft SQL
Server\MSSQL\ReplData' as the root snapshot folder for
Publishers using this Distributor.
Store the distribution database 'distribution' in 'f:\Microsoft
SQL Server\MSSQL\Data'.
Store the distribution database log file in 'f:\Microsoft SQL
Server\MSSQL\Data'.
Enable the following databases on '192.168.10.3' for merge
replication:
SampleDataBase
Enable the following servers as Subscribers to
publications on '192.168.10.3':
10.9.0.1
But I receive:
SQL Server Enterprise Manager could not configure '192.168.10.3' as the
Distributor for '192.168.10.3'.
Error 18483: Could not connect to server ‘NSS’ because
‘distributor_admin’ is not defined as a remote login at the server.
But:
SELECT @.@.SERVERNAME, SERVERPROPERTY('ServerName') returns
NSS NSS
So it seems properly.
Ping nss working properly..
I need help!!! Any concepts? Maybe something connected with
distributor_admin and trusted connection – I heard somewhere that it is
possible to disable trusted connection for distributor_admin but I don’t
know how to do it?Hello,
I understand that when you try to create a merge replication, you receive
the error message "Error 18483: Could not connect to server "NSS" because
"distributor_admin" is not defined as a remote login at the server."
However, the servername is correct and it is the same as
SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
This issue could occur when the Machine name at windows level (Hostname) is
different from SQL Server Name (Select @.@.servername).
And this happens when the machine on which SQL Server is installed is
renamed after SQL Server installation. If it is so you will have to do the
following using Query Analyzer:
Sp_dropserver <server>
(Replace <server> with the name returned by Select @.@.servername query)
Sp_addserver <server>, local
(Replace <server> with the machine name (Hostname) if it is default
instance otherwise <server>\instancename for named instance)
Also, did you try to use servername "NSS" when configure merge replication
and not using IP adress directly? I suspect this issue may also related to
network name resolution.
More related information:
818334 Replication setup is not successful when SQL Server is deployed by
using a disk image
http://support.microsoft.com/defaul...kb;EN-US;818334
321822 HOW TO: Replicate Between Computers Running SQL Server in
Non-Trusted Domains or Across the Internet
http://support.microsoft.com/defaul...kb;EN-US;321822
If you have any update, please let's know. Thank you.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Community Support
========================================
==========
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscript...ault.aspx#notif
ications
<http://msdn.microsoft.com/subscript...ps/default.aspx>.
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/subscript...rt/default.aspx>.
========================================
==========
This posting is provided "AS IS" with no warranties, and confers no rights.|||Peter Yang [MSFT] napisa?(a):
> Hello,
> I understand that when you try to create a merge replication, you receive
> the error message "Error 18483: Could not connect to server "NSS" because
> "distributor_admin" is not defined as a remote login at the server."
> However, the servername is correct and it is the same as
> SERVERPROPERTY('ServerName'). If I'm off-base, please let me know.
yes its true!

> This issue could occur when the Machine name at windows level (Hostname) i
s
> different from SQL Server Name (Select @.@.servername).
> And this happens when the machine on which SQL Server is installed is
> renamed after SQL Server installation. If it is so you will have to do the
> following using Query Analyzer:
Select @.@.servername
returns
NSS
and full computer name is:
nss.domain.local
it is a domain server (SBS)
so computer name is nss and never been changed!

> Sp_dropserver ?<server>?
> (Replace <server> with the name returned by Select @.@.servername query)
>
> Sp_addserver ?<server>?, ?local?
> (Replace <server> with the machine name (Hostname) if it is default
> instance otherwise <server>\instancename for named instance)
> Also, did you try to use servername "NSS" when configure merge replication
> and not using IP adress directly? I suspect this issue may also related to
> network name resolution.
> More related information:
>
> 818334 Replication setup is not successful when SQL Server is deployed by
> using a disk image
> http://support.microsoft.com/defaul...kb;EN-US;818334
>
> 321822 HOW TO: Replicate Between Computers Running SQL Server in
> Non-Trusted Domains or Across the Internet
> http://support.microsoft.com/defaul...kb;EN-US;321822
I will try it this...