none
how to configure replication in sql server 2008?

    Question

  • hi guys,

    I have two server with valid Ip address.both have sql server 2008 installed.they are all named instance that is for coneecting to sql server 2008 in the server name i should write:local\sql2008.So the instance name is sql2008.

    i have created a user in both sql servers and have given sysadmin role to that user.Both servers can see each other,no problem in connecting to each other.

    suppose server A want to send its sql server database data to server B sql server.so i have to use replication in  sql server for this kind of work.in server A i have create a "publisher" and it was OK and created with no problem.

    When i tried to create subscriber in server B,It gives me the ugly error:

    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, 'M1\SQL2008'. (Replication.Utilities)

    Now i really went on searching in google,but the answers were not accurate and usefull.If somebody knows what to do please try to answer step by step.I know many people have this problem and not yet answered correctly.

    Oh i forgot to say the sql server are standard edittion.and windows in server B is windows server 2003 and in server A windows XP.

    Thanks

     


    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    • Edited by simosi Tuesday, February 15, 2011 2:02 PM f
    Tuesday, February 15, 2011 2:00 PM

Answers

  • dear Kashif,
    can you answer me clearly:
    1.should i create alias in server 91.99.100.170(going to become subscriber) or server 78.38.56.127(publisher)?
    2.when i select new alias in sql configuration manager(on server 78.38.56.127,should i choose this server for creating alias?) it needs me to fill:
    Alias Name:
    Port No:
    Protocol:
    Server:
    What should i write in fields above?
    Thanks
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.

    1. depends on its push or pull, or shall I say where are those agents? if distributor is on publisher, and push mode, then alias on publisher. But you still can always setup both sides. on publisher set up M1\SQL2008, on subscriber set up Local\sql2008

    2. alias name: M1\SQL2008

       port: your M1 server sql port: 1433 or something else you can find out ( dont use dynamic port for your replication in this case)

       protocol: TCP/IP

       Server: M1 IP


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Thursday, February 17, 2011 4:01 PM

All replies

  • Have you validated connectivity outside of Replication, between server A and server B?

    For example, can you connect to Server B via SQL Server Management Studio from an RDP session on Server A?


    John Sansom | SQL Server DBA Blog | Twitter
    Tuesday, February 15, 2011 4:01 PM
  • Simosi,

     I had this issue earlier...

    fix is u have to create alias to subscriber on your distributor or publisher....

    Tuesday, February 15, 2011 4:15 PM
  • kashif,

    Can you guide me how to make alias on server A and B.

    does it need to make alias in both server A , B?

    What are the steps? thanks.


    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Tuesday, February 15, 2011 4:38 PM
  • are they on same domain...

    you should be good creating alias on server A

    Tuesday, February 15, 2011 4:43 PM
  • I really dont understand what do you mean by "are they on same domain".There is two computers one located at ip address 91.99.100.170 and the another is located at ipadress 78.38.56.127.

    the one located at 78.38.56.127 is publication.and 91.99.100.170 is going to become subscriber.both of the computers have MS SQL SERVER standard edittion 2008 with named instance installed.the named instance is "sql2008" that is if you try to connect to sql server on both computers you should write: local\sql2008 and then click connect.both SQL server have a same login(user) in sql server (suppose the login name is "abc" and the password is also same) with "sysadmin" granted.The user that i connect to windows in server 78.38.56.127 is named "M1".and this user is administrator user in my window.When i tried to create subscriber on server 91.99.100.170 it says:

    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, 'M1\SQL2008'. (Replication.Utilities

     

    Now you said that i have to create Alias on server 78.38.56.127 right?How i have to do this.and solve my problem.Thanks very much.


    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    • Edited by simosi Tuesday, February 15, 2011 5:40 PM f
    Tuesday, February 15, 2011 5:38 PM
  • To configure alias

    go to sql server configuration manager

    click on sql native client configuration then on aliases.....

    Tuesday, February 15, 2011 5:57 PM
  • simosi,

    If this doesnt work for you....try to add subscription using t-sql sp_addmergesubscription after creating alias name...this should work for you

    Wednesday, February 16, 2011 2:14 AM
  • dear Kashif,
    can you answer me clearly:
    1.should i create alias in server 91.99.100.170(going to become subscriber) or server 78.38.56.127(publisher)?
    2.when i select new alias in sql configuration manager(on server 78.38.56.127,should i choose this server for creating alias?) it needs me to fill:
    Alias Name:
    Port No:
    Protocol:
    Server:
    What should i write in fields above?
    Thanks
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Wednesday, February 16, 2011 7:16 AM
  • please help...
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.
    Thursday, February 17, 2011 3:23 PM
  • dear Kashif,
    can you answer me clearly:
    1.should i create alias in server 91.99.100.170(going to become subscriber) or server 78.38.56.127(publisher)?
    2.when i select new alias in sql configuration manager(on server 78.38.56.127,should i choose this server for creating alias?) it needs me to fill:
    Alias Name:
    Port No:
    Protocol:
    Server:
    What should i write in fields above?
    Thanks
    Brainstorm your Brain and find solution,if no result stuck to Brainstormer.

    1. depends on its push or pull, or shall I say where are those agents? if distributor is on publisher, and push mode, then alias on publisher. But you still can always setup both sides. on publisher set up M1\SQL2008, on subscriber set up Local\sql2008

    2. alias name: M1\SQL2008

       port: your M1 server sql port: 1433 or something else you can find out ( dont use dynamic port for your replication in this case)

       protocol: TCP/IP

       Server: M1 IP


    If you think my suggestion is useful, please rate it as helpful.
    If it has helped you to resolve the problem, please Mark it as Answer.

    Sevengiants.com
    Thursday, February 17, 2011 4:01 PM
  • We also were getting this 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"

    We had to make sure that both the windows server doing the publishing (the one doing the push - the distributor was on this local machine) had an entry in its hosts file for the ip address of the remote subscribing machine AND the exact same name in the hosts file of the subscribing machine.   SQL Server is doing some sort of check (like select @@SERVERNAME or something) when it connects for setting up replication.

    It wants to see that the server name in the publishing server's hosts file matches the servername of the subscriber it connects to.   This is presumably some sort of attempt at security.   After we made sure they were the same, the publisher was allowed to replicate to the remote machine.

    The remote machine was also listening on a non-standard port, and an alias had been made for it, as well, to handle that issue.

    (C:\windows\system32\drivers\etc\ or %systemroot%\system32\drivers\etc\ )


    Wednesday, August 21, 2013 12:23 AM
  • Also be careful of the length of the publisher/distributor name. If it is longer than 16 characters the system functions used to obtain the name will truncate it to 16 characters and you will be unable to configure your publisher or distributor correctly.

    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

    Wednesday, August 21, 2013 3:43 PM
    Moderator