locked
change subscriber instance RRS feed

  • Question

  • Is it possible to change the instance of a subscriber? I have a subcription for which the instance cannot be reached by the server\instance, it needs to have the port information appeded or it will not connect
    Friday, September 10, 2010 8:06 PM

Answers

  • My solution was to add an alias using the server name\instance name with the port that was reported by portqry and my problem is patched.

    I thought replication does not support aliases, possibly just when creating the subscription? Still, I should not have to have done this ...

    • Marked as answer by Branch Monday, September 13, 2010 2:57 PM
    Monday, September 13, 2010 2:57 PM

All replies

  • Hi Branch,

    I was reading your other post here:

    http://social.msdn.microsoft.com/Forums/en-US/sqldataaccess/thread/6baf47f8-b963-4cc2-b7d3-5edbe64e1c36/#9849aace-5b56-466b-b8d3-9bdd2fa50db7

    And I was wondering exactly what error you are getting when trying to connect with 'server\instance'?  Can you post the exact error here please?

    Friday, September 10, 2010 9:52 PM
  • I can connect to an instance by specifying the port but not by the instance name. Becuase of this, no replication is occuring. Please see porqry and sqlcmd output below ...

     

    d:\>portqry -n MDLEXPSV02 -p udp -e 1434

    Querying target system called:

     MDLEXPSV02

    Attempting to resolve name to IP address...


    Name resolved to 172.16.75.101

    querying...

    UDP port 1434 (ms-sql-m service): LISTENING or FILTERED

    Sending SQL Server query to UDP port 1434...

    Server's response:

    ServerName MDLEXPSV02
    InstanceName SQLEXPRESS
    IsClustered No
    Version 9.00.4035.00
    tcp 1184
    np \\MDLEXPSV02\pipe\MSSQL$SQLEXPRESS\sql\query

         ☻  ☻P'   %

    ==== End of SQL Server query response ====

    UDP port 1434 is LISTENING

    d:\>sqlcmd -U sa -S MDLEXPSV02\SQLEXPRESS -P sapwd
    HResult 0xFFFFFFFF, Level 16, State 1
    SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
    Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
    shing a connection to the server. When connecting to SQL Server 2005, this failu
    re may be caused by the fact that under the default settings SQL Server does not
     allow remote connections..
    Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

    d:\>sqlcmd -U sa -S MDLEXPSV02\SQLEXPRESS,1184 -P sapwd
    1> exit

    d:\>

    Saturday, September 11, 2010 10:50 AM
  • Is the browser service running on the subscribe? It must be if you want it to work.

    There is no supported way to change the instance name of a subscriber leaving replication intact.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Saturday, September 11, 2010 11:34 AM
    Answerer
  • yes, I can use portqry and it returns the instance name and port as in my post above

    would it be possible to just run the merge agent at the subscriber instead of the publisher?

    looking at the merge agent job on the publisher, it only shows the parameters  

     

    Saturday, September 11, 2010 12:00 PM
  • Branch:

    You can't rename a subscription instance in SQL Server.

    To have teh merge agent job run at the subscriber you will need to create a subscription of type: pull instead of push.

    Can you try to login through port 1433 for the instance name? the default is 1433 unless otherwise you specified differently.

    Also if you create an alias for the instance name in sql server configuration manager and specify the port 1434, can't you still connect?

    Sunday, September 12, 2010 2:44 AM
  • yes, I can use portqry and it returns the instance name and port as in my post above


    Hi,

    Typically, to access an instance, we provide the server name, <ComputerName>\<InstanceName>for a named instance, <ComputerName> for a default instance. This is enough for server name and worked well if the SQL Server is listening on the default TCP port 1433. However, if the SQL Server is not listening on the default TCP port 1433, we may get the login failed error message.

    To avoid this issue, we have two means:

    1.        Specify the TCP port in the server name, <ComputerName>\<InstanceName>, <TCP port>for a named instance, <ComputerName>,<TCP port> for a default instance.

    2.        Enable SQL Server Browser Services on the server where SQL Server is running, which will help resolve SQL Server listening TCP port issues.

    In this case, it works expectedly by specifying the TCP port which uses the means 1 above but does not work using the second means. If you don’t want to specify TCP port in the server name which will use means 2. Firstly, SQL Server Browser services should be running on the server where SQL Server is running. Secondly, please add the following to the Windows Firewall which may block SQL Server Browser Services and ports on the server:

    1.        Add SQL Server Browser Services (sqlbrowser.exe) and UDP port 1434 to the Windows Firewall inbound exceptions.

    2.        Add SQL Server listening TCP port to the Windows Firewall inbound exceptions (in your case, it seems to be TCP 1184). If SQL Server is listening on static TCP port, you can find this port in the SQL Server Configuration Manager and add to Windows Firewall. If SQL Server is listening on dynamic TCP port, which will be assigned a TCP port when SQL Server starts, you need to add this port to Windows Firewall.

    So, the error you get is probably due to Windows Firewall, please configure Windows Firewall as I mentioned above. If you have more questions, please let me know.

    Thanks,
    Chunsong


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Monday, September 13, 2010 9:08 AM
  • Thanks Chunsong,

    The Windows firewall is disabled at both the server and the client, I can connect if I specify the port number and. The SQL browser is responding correctly to the portqry test.

    Of course replication does not have the port number so it is not working.

    • Marked as answer by Branch Monday, September 13, 2010 2:54 PM
    • Unmarked as answer by Branch Monday, September 13, 2010 2:54 PM
    Monday, September 13, 2010 12:44 PM
  • Thanks Wissam,

    Is it possible to modify the subscription to pull?

    Monday, September 13, 2010 12:48 PM
  • My solution was to add an alias using the server name\instance name with the port that was reported by portqry and my problem is patched.

    I thought replication does not support aliases, possibly just when creating the subscription? Still, I should not have to have done this ...

    • Marked as answer by Branch Monday, September 13, 2010 2:57 PM
    Monday, September 13, 2010 2:57 PM
  • Hi All!

    When I try to estabilish a connection for replication , from subscriber server to publisher server, I receive the message below :

    An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

    I am able to connect the publisher from subscriber,typing the port 1433,using Sql Server Management Studio Tool

    When I try to create the subscriber passing the port or using an alias , received the following message:  

    SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name.

    Please,tell me how can I bypass the error.

    Thursday, December 23, 2010 7:47 PM