December 11, 2013 at 11:54 am
First I'll apologize for my lack of knowledge on the topic...
I recently started with a new employer and I have a request to create a copy of a table in a database on 1 server to a database on another server. I did that fine and dandy. Now I'm supposed to set up transactional replication from the original to the new copy.
Server 1: In SSMS, I connect to DEVSQL03.cdb.org with windows authentication
Server 2: In SSMS, I connect to DEVSQL04.cdb.org with windows authentication
On Server 1, there are already 2 replication publications set up, but I suspect they don't really work, because of the steps I've already attempted. I tried to look at the properties on each of the existing publications and in both cases, I got the following error:
An error occurred connecting to Publisher 'DEVSQL03.cdb.org'. (Subscription Properties)
SQL Serer 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, 'DEVSQL03'. (Replication.Utilities)
However, when I hover the cursor over the publication object in the object explorer, the following information is displayed:
[DEVSQL04].[ECOT_APPS]
Publisher: DEVSQL03
Publication Database: ECOT_APPS
Publication Name: ECOT_APPS_PUB
Publication Type: Transactional
Subscriber: DEVSQL04
Subscription Database: ECOT_APPS
Distribution Agent Location: Distributor
This is a subscription to a publication published by server DEVSQL03
If I try to connect to 'DEVSQL03' without the 'cdb.org' in SSMS, I get the following error:
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) (Microsoft SQL Server, Error: 53)
I opened the Replication Monitor and DEVSQL03.cdb.org is listed under My Publishers, but there's a red x across the connection. This is why I'm suspicious that the existing publication objects aren't really working.
When I try to add a publisher through the Monitor interface, I get the same errors - either the one about the name or the network-related one.
EXEC sys.sp_helpserver @server = 'DEVSQL03.cdb.org' returns:
name: DEVSQL03.CDB.ORG
network_name: DEVSQL03.CDB.ORG
status: data access,use remote collation
id: 7
collation_name: NULL
connect_timeout: 0
query_timeout:0
EXEC sys.sp_helpserver @server = 'DEVSQL03' returns:
name: DEVSQL03
network_name: DEVSQL03
status: rpc,rpc out,use remote collation
id: 0
collation_name: NULL
connect_timeout: 0
query_timeout:0
SELECT * FROM sysservers WHERE srvname LIKE '%dev%03%' returns 2 rows:
srvid: 0
srvstatus: 1089
srvname: DEVSQL03
srvproduct: SQL Server
providername: SQLOLEDB
datasource: DEVSQL03
location: NULL
providerstring: NULL
scemadate: 2011-10-19 11:21:20.913
topologyx: 0
topologyy: 0
catalog: NULL
srvcollation: NULL
connecttimeout: 0
querytimeout: 0
srvnetname: DEVSQL03
isremote: 1
rpc: 1
pub: 0
sub: 0
dist: 0
dpub: 0
rpcout: 1
dataaccess: 0
collationcompatible: 0
system: 0
useremotecollation: 1
lazyschemavalidation: 0
collation: NULL
nonsqlsub: 0
srvid: 7
srvstatus: 1184
srvname: DEVSQL03.CDB.ORG
srvproduct: SQL Server
providername: SQLOLEDB
datasource: DEVSQL03.CDB.ORG
location: NULL
providerstring: NULL
scemadate: 2012-10-21 09:45:29.747
topologyx: 0
topologyy: 0
catalog: NULL
srvcollation: NULL
connecttimeout: 0
querytimeout: 0
srvnetname: DEVSQL03.CDB.ORG
isremote: 0
rps: 0
pub: 0
sub: 0
dist: 0
dpub: 0
rpcout: 0
dataaccess: 1
collationcompatible: 0
system: 0
useremotecollation: 1
lazyschemavalidation: 0
collation: NULL
nonsqlsub: 0
SELECT @@SERVERNAME returns:
DEVSQL03
I did some research and turned up several articles involving using sp_dropserver and/or sp_addserver. My concern with doing anything like that is the effect that it will have on the developers. There are tons of applications they write/maintain/customize, that hit the databases on this server.
I know almost nothing about DEVSQL03.CDB.ORG is on a VM and is part of a clustered environment. I know pretty much nothing about it prior to that (back when it was just DEVSQL03).
Any advice?
December 11, 2013 at 1:57 pm
It appears the server was added using a fully qualified domain name like: server.domain.com. To be honest I have never considered the impact that might have on referencing database objects on linked servers where you need to fully qualify the object by: serverName.database.schema.objectName
I'll have to try it on my dev server, but really the serverName part should not have any dots in it or I would think it should always be enclosed in square brackets like [serverName.domain.org].
Anyway, I would think it is confusing two have to remote servers set up with different names that point to the same physical server. Also, while the datasrc can be a fully qualified name, I would avoid setting up a remote server with a svrname that has dots in it (fully qualified domain address) for reasons mentioned above.
The probability of survival is inversely proportional to the angle of arrival.
December 12, 2013 at 9:04 am
I have a lot of confusion around the fact that I have to use DEVSQL03.CDB.ORG to connect to the server, but SELECT @@SERVERNAME returns DEVSQL03. That has to cause some kind of conflict on some level.
December 12, 2013 at 9:23 am
robin.pryor (12/12/2013)
I have a lot of confusion around the fact that I have to use DEVSQL03.CDB.ORG to connect to the server, but SELECT @@SERVERNAME returns DEVSQL03. That has to cause some kind of conflict on some level.
in sysservers, srvname and datasource are not the same. srvname is what shows up in @@servername (for serverid 0). It's just a name, you could name it XXYYZZ if you wanted to. datasource is what you use in the connection string to actually connect to it (other than for shared memory). If you can only connect using a fully qualified domain name (like: svrname.domain.org) then you have DNS and/or routing issues. Talk to the IT guys.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply