Solved

Linked server for replication

Posted on 2009-07-02
10
3,296 Views
Last Modified: 2012-05-07
In SQL Server 2088 I am trying to setup a linked server for 'transactional replication with updates'. I need to setup ServerB as publisher and ServerA as subscriber. Within SSMS, running on ServerB box, I am connected to both ServerA and ServerB, I can add or see any users on both SQL Servers.

In SMSS and on ServerA I want to create a Linked Server to ServerB. Createing a New Linked Server I have the following:
General:
  Linked Server = ServerB
  Type = SqlServer
Security:
  Local login=sa, remote user = sa, remote password=blue123
  'login not defined, connections will' = Not be Made
Server Options:
 Data Acess = True
 RPC = true
 RPC out = true
 Connection timeout =90
 Distributor=false
 Subscriber = false
 Enable Promotion of Distributed Transactions=True
 
When I do that I get the error:
The linked server has been created but failed a connection test. Do you want to keep the linked server?

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


Access to the remote server is denied because no login-mapping exists. (Microsoft SQL Server, Error: 7416)

Also what I don't understand is where to I specify the real server name?

If I left the Subscriptoin wizard take over and it creates a linked server for me it calls it something like
  'REPLINK_ServerB750965012...'
and for Security it uses:
  local login= repllinkproxy
  remoteuser = sa
  password = blue123

If I right click on the this llinked server and use Test Link I get the error message:

     The test connection to the linked server failed.

   An exception occurred while executing a Transact-SQL statement or batch.    (Microsoft.SqlServer.ConnectionInfo)

  Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)

Why, what should be changed ?  
0
Comment
Question by:LukeB
  • 5
  • 5
10 Comments
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24762753
hi,
Linked server mainly is used to access the the database placed on a different server.
I am bit confused with the question combining Linked Server & Replication.
For creating linked server please check below query...
exec sp_addlinkedserver 'remoteip\instance'   -- accept defaults for all other params
exec sp_addlinkedsrvlogin @rmtsrvname='remoteip\instance', @useself=false, @locallogin=null,
  @rmtuser='username', @rmtpassword='password'

And for creating replication we can directly create by reight clicking on the Replication folder.
For your reference  please check the attached doc.
please let me know if i there is any issue... or if i m wrong..
bye...

ReplicationAndDBM.doc
0
 
LVL 1

Author Comment

by:LukeB
ID: 24768595
Hi Jagdish

For "Transactional Replication with Subscriber updates" the SSMS adds a linked server .

See http://msdn.microsoft.com/en-us/library/ms152769(SQL.90).aspx:

7. The Login for Updatable Subscriptions page is displayed for subscriptions that use immediate updating or have update_mode set to queued failover. On the Login for Updatable Subscriptions page, specify a linked server over which connections to the Publisher are made for immediate updating subscriptions. Connections are used by the triggers that fire at the Subscriber and propagate changes to the Publisher. Select one of the following options: Create a linked server that connects using SQL Server Authentication. Select this option if you have not defined a remote server or linked server between the Subscriber and the Publisher. Replication creates a linked server for you. The account you specify must already exist at the Publisher.
Use a linked server or remote server that you have already defined. Select this option if you have defined a remote server or linked server between the Subscriber and the Publisher using sp_addserver (Transact-SQL), sp_addlinkedserver (Transact-SQL), SQL Server Management Studio, or another method.


0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24770061
hi Luke, thanks for the info...
regarding the error i found that creating linked with t-sql might solve this oiption.
after creating the linked server... select this option 'Use a linked server or remote server that you have already defined'
bye...
0
 
LVL 1

Author Comment

by:LukeB
ID: 24770822
Hi Jagdish
so:
    exec sp_addlinkedserver '10.0.1.16'

That works if I right click on the new Linked Server and use Test Connection.

But why not
   
    exec sp_addlinkedserver 'ServerB'

the actual UNC name of the server?  If I try that it creates the linked server but when I go to test the connection it fails - 'Unable to complete login process due to deay in opening server connection'

 Because when I use the transactional replication and it asks for 'a server you already defined' how is it going to know which linked server to use??
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24770884
hi,
by creating the alias for 10.0.1.16 as ServerB in SQL Server Configuration Manager.
exec sp_addlinkedserver 'ServerB' -> is possible.
--Because when I use the transactional replication and it asks for 'a server you already defined' how is it going to know which linked server to use??
wait for some time i will let you know.. i am configuring the same here.
bye.
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 1

Author Comment

by:LukeB
ID: 24771530
Hi Jagdish,
how in SSMS do I create an alias ?

Also, I do have under SS Config Mananger | Network Settings | Protocols both Named Pipes and TCPIP enabled so very confused as to why
   exec sp_addlinkedserver 'ServerB'
is not possible and have to create an alias?? Should not have to ?

(MS sure does not make this easy for linking servers by UNC !! A person can connect  server by UNC name with SSMS, why not this wizard, very strange...)
0
 
LVL 14

Accepted Solution

by:
Jagdish Devaku earned 500 total points
ID: 24771701
hi,
noramlly replication asks for named instance.
so we create an alias and we call it instead of an ip address...
one more thing--- named instance alias should be same as sql server instance name....
in ss configuration manager -> SQL Native Client Configuration -> Alias
Right click on Alias -> New Alias -> Enter instance name in Name & IP address for the Server option. if require give port. and click OK.
Thats it alias is created. you can use this alias for creating replication, linked server and connecting sql server through ssms.
coming protocols... tcp/ip,named pipers,shared pipes will be enabled.
i am still working with replication in our environment and will let you know the process once it is done.
bye.
0
 
LVL 1

Author Comment

by:LukeB
ID: 24778194
Hi Jagdish,

ok, I have created the alias and made the remote server conneciton and it now tests ok when I use SSMS | Linked Server | TestLink

Now when I go to do the replication wizard again for "transactional with subscriber updates" and it asks for  a remote server I use 'a server you already defined' (it does NOT ask for which remote that is ) see LoginForUpdatableSubscriptions.jpg

When I go to finish the wizard I get the error shown in NewSubscriptionWizard_err.jpg.
When I go to edit a record on ServerB it is ok and gets replicated so ServerA. But when I go to edit a record on ServerA I get the error shown in NoRowUpdated.jpg.

On the linked server I have the settings shown LinkedServerProperties.jpg



LoginForUpdatableSubscriptions.jpg
NewSubscriptionWizard-err.jpg
NoRowUpdated.jpg
LinkedServerProperties.jpg
0
 
LVL 14

Expert Comment

by:Jagdish Devaku
ID: 24791586
Hi Luke,
I tried the same but i am facing a different issue i.e. permissions.
But I am succussful when I have did this from subscriber machine. I configured the linked server on the subscriber machine and then configured the subscription from the subscriber end. It worked fine.
so i suggest you to configure the same from subscriber end.
bye.
0
 
LVL 1

Author Comment

by:LukeB
ID: 24799322
ok Jagdish, it is working now. I had to enable the RPC on the linked server I made on the subscriber.


Currently it is working , .So I will close this Q.

NEW issue : the replication that is working is Publisher PUSH, but I want to setup as Subscriber PULL to reduce load on publisher. When I follow thru the Wizard again, but this time specify PULL I get an error. But I will post another Q on that so comment there once you have tried the same

0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now