SQL Server Performance

Transactional Replication

Discussion in 'SQL Server 2005 Replication' started by tarekj, Feb 14, 2007.

  1. tarekj Member

    Hello all,

    I am making transactional replication between 2 servers through IP VPN (Server A and Server B), one of them is publisher and the other one is subscriber and vice versa (I send master data from A to B and send transactions from B to A), it used to be working fine for years, but now it is working one way only, it says


    The process could not connect to server 'Server A'. (Source: MSSQL_REPL, Error number: MSSQL_REPL0)
    Unable to complete login process due to delay in opening server connection (Source: MSSQLServer, Error number: 08001),
    My question is, is there a way to check the connection and the open ports to make sure the connection is ok?
  2. satya Moderator

  3. ndinakar Member

  4. MohammedU New Member

  5. tarekj Member

    Hello all,

    I could not find a solution for my problem, I want to add that am able to send master data from A to B if I am in Server A, and able to send transactions from B to A if I am in Server B, so I can not get the data from A if I am in B and can not get the data from B if I am in A.

    Please help
  6. MohammedU New Member

    Use sp_testlinkedserver procedure to test linked server connectivity..

    are you able to run a query with four part name?
    What is the network protocol used/configured?


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  7. tarekj Member

    When I am in ServerA and run
    sp_testlinkedserver ServerB
    I get:

    OLE DB provider "SQLNCLI" for linked server "RUHMISSERVER" returned message "Unable to complete login process due to delay in opening server connection".
    Msg 7303, Level 16, State 1, Procedure sp_testlinkedserver, Line 1
    Cannot initialize the data source object of OLE DB provider "SQLNCLI" for linked server "RUHMISSERVER".


    although I run EXEC sp_serveroption 'ServerA', 'DATA ACCESS', TRUE

    What do you suggest?
  8. MohammedU New Member

    Run the profiler on source as well as destination server and make sure you included error events to check what is going on exactly...


    MohammedU.
    Moderator
    SQL-Server-Performance.com
  9. tarekj Member

    it gave me the following error:

    exec dbo.sp_MSupdatelastsyncinfo N'ServerA',N'MISDB', N'JED_PUB', 1, 6, N'The process could not connect to
    server ''ServerA''.'

    Do you have any suggestion on how to included error events?

    Thank you for your help
  10. tarekj Member

    also when I use osql -S"ServerA" -U"sa"
    when I am in ServerB I get the the same error
    [SQL Native Client]Unable to complete login process due to delay in opening
    server connection

    Here is a list of the profiler errors:

    exec dbo.sp_MSupdatelastsyncinfo N'JEDMISSERVER',N'MISDB', N'JED_PUB', 1, 6, N'The process could not connect to
    server ''JEDMISSERVER''.'
  11. tarekj Member

    <b>I've found the solution.....[<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br />the problem was in the DNS, SQL server had a problem to resolve the name of other server IP, so I used the HOSTS file located in:<br /><br />C:WINDOWSsystem32driversetc<br /><br />to map the IP, it is working fine now, even WITHOUT USING VPN [<img src='/community/emoticons/emotion-1.gif' alt=':)' />]<br /><br />Thank you all for your help<br />Many thanks to MohammedU</b>
  12. MohammedU New Member

    Thanks for sharing your solution...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page