Linked server for replication
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.Conne
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.Conne
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Why, what should be changed ?
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.Conne
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.Conne
Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'. (Microsoft SQL Server, Error: 18456)
Why, what should be changed ?
ASKER
Hi Jagdish
For "Transactional Replication with Subscriber updates" the SSMS adds a linked server .
See http://msdn.microsoft.com/
For "Transactional Replication with Subscriber updates" the SSMS adds a linked server .
See http://msdn.microsoft.com/
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.
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...
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...
ASKER
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??
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??
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.
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.
ASKER
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...)
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...)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 LoginForUpdatableSubscript
When I go to finish the wizard I get the error shown in NewSubscriptionWizard_err.
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
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 LoginForUpdatableSubscript
When I go to finish the wizard I get the error shown in NewSubscriptionWizard_err.
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
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.
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.
ASKER
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
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
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\inst
@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