I am testing replication (Transactional with Queued Updating) between SQL
Server 2005 Developer Edition (which is on a domain) and SQL Express (which
is on a machine on same LAN but in a workgroup). I have configured the
publisher as its own distributor and created a publication consisting of 2
views (1 indexed, 1 regular). My problem is that when I run the New
Subscription Wizard, after clicking Finish the process hangs forever on the
first step ("Creating Subscription For ....."). No errors appear, nothing.
Clicking Stop has no effect.
Both views are tiny, only returning half a dozen rows. I have also tried
with a publication containing only one small table, and the same thing
occurs.
The publisher is clearly able to connect to the subscriber because the
linked server is created on the subscriber successfully. There is no sign of
a subscription however on the subscriber.
Here is the configuration for the subscription (following steps in New
Subscription Wizard):
Push subscription
Subscription database is new blank database
Distribution Agent Security: Run under SQL Agent service account, Connects
to distributor by impersonation, Connects to subscriber using a SysAdmin SQL
login
Schedule: runs continuously
Queued updating
Create new linked server using same SysAdmin SQL login as above
Initialize subscription immediately
What could be wrong?
Can you script out your publication creation steps and then run it manually
through Query Analyzer or the SSMS and see where it hangs. This should give
you some indication of where the problem is.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||The step that hangs is the last step - the execution of sp_link_publication
on the subscriber:
exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db =
N'RMIS_QA', @.publication = N'test views', @.distributor =
N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
N'xxxx'
In the Application Event log the following error appears:
MS DTC is unable to communicate with MS DTC on a remote system. No common
RPC protocol is supported between the two systems. Please ensure that one
or more of the following RPC protocols are common to both systems: TCP/IP,
SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904, CmdLine:
C:\WINNT\System32\msdtc.exe
I have checked that the MSDTC service is running on publisher and
subscriber. The publisher server is Windows XP SP2 and from googling it
looks like DTC is not given network access by default, however I have
enabled the required settings and the error persists:
In Admin tools.Component Services or via Component Services MSC Snap-in
- right-click the appropriate computer node and select properties
- on the MSDTC tab click Security Configuration button
- in addition to the Network DTC Access being ticked, tick Allow Remote
Clients, Allow Inbound, Allow Outbound
- restart MSDTC
The subscriber server is Windows 2000 Workstation, and it doesn't look like
there are any DTC settings to configure.
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>I am testing replication (Transactional with Queued Updating) between SQL
>Server 2005 Developer Edition (which is on a domain) and SQL Express (which
>is on a machine on same LAN but in a workgroup). I have configured the
>publisher as its own distributor and created a publication consisting of 2
>views (1 indexed, 1 regular). My problem is that when I run the New
>Subscription Wizard, after clicking Finish the process hangs forever on the
>first step ("Creating Subscription For ....."). No errors appear, nothing.
>Clicking Stop has no effect.
> Both views are tiny, only returning half a dozen rows. I have also tried
> with a publication containing only one small table, and the same thing
> occurs.
> The publisher is clearly able to connect to the subscriber because the
> linked server is created on the subscriber successfully. There is no sign
> of a subscription however on the subscriber.
> Here is the configuration for the subscription (following steps in New
> Subscription Wizard):
> Push subscription
> Subscription database is new blank database
> Distribution Agent Security: Run under SQL Agent service account, Connects
> to distributor by impersonation, Connects to subscriber using a SysAdmin
> SQL login
> Schedule: runs continuously
> Queued updating
> Create new linked server using same SysAdmin SQL login as above
> Initialize subscription immediately
> What could be wrong?
>
|||Is this an upgrade from SQL 2000? There are some issues with a remote
distributor in an upgrade. Basically the repl_distributor remote server is
modified to a linked server and there have been cases where you have to
update the passwords to get this to work. AFAIK - MSDTC is not involved in
queued replication.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
> The step that hangs is the last step - the execution of
> sp_link_publication on the subscriber:
> exec sp_link_publication @.publisher = N'LAURENCEN\SQL2005', @.publisher_db
> = N'RMIS_QA', @.publication = N'test views', @.distributor =
> N'LAURENCEN\SQL2005', @.security_mode = 0, @.login = N'xxxxx', @.password =
> N'xxxx'
> In the Application Event log the following error appears:
> MS DTC is unable to communicate with MS DTC on a remote system. No common
> RPC protocol is supported between the two systems. Please ensure that one
> or more of the following RPC protocols are common to both systems:
> TCP/IP, SPX, or NetBEUI. Error Specifics: .\iomgrclt.cpp:204, Pid: 904,
> CmdLine: C:\WINNT\System32\msdtc.exe
> I have checked that the MSDTC service is running on publisher and
> subscriber. The publisher server is Windows XP SP2 and from googling it
> looks like DTC is not given network access by default, however I have
> enabled the required settings and the error persists:
> In Admin tools.Component Services or via Component Services MSC Snap-in
> - right-click the appropriate computer node and select properties
> - on the MSDTC tab click Security Configuration button
> - in addition to the Network DTC Access being ticked, tick Allow Remote
> Clients, Allow Inbound, Allow Outbound
> - restart MSDTC
> The subscriber server is Windows 2000 Workstation, and it doesn't look
> like there are any DTC settings to configure.
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:eyE7utcAHHA.3396@.TK2MSFTNGP02.phx.gbl...
>
|||I figured it out after a long search.
MSDTC is involved, at least to set up the subscription. MSDTC in Windows XP
(and Win 2003 also I think) has some network access settings that are set to
prevent any access from outside by default. I mentioned these before.
However, in addition to setting these settings to allow network access,
there is a security setting for RPC, which I missed. This is the registry
key:
HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Microsoft\Win dows
NT\RPC\RestrictRemoteClients
This needs to exist and have a value of 0 (DWORD) if the subscriber is
Windows 2000.
For more info read:
http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
http://www.microsoft.com/technet/prodtechnol/winxppro/maintain/sp2netwk.mspx#XSLTsection128121120120
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23tmOUdqAHHA.3536@.TK2MSFTNGP03.phx.gbl...
> Is this an upgrade from SQL 2000? There are some issues with a remote
> distributor in an upgrade. Basically the repl_distributor remote server is
> modified to a linked server and there have been cases where you have to
> update the passwords to get this to work. AFAIK - MSDTC is not involved in
> queued replication.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Laurence Neville" <laurenceneville@.hotmail.com> wrote in message
> news:O%237HO7oAHHA.5068@.TK2MSFTNGP02.phx.gbl...
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment