SQL Server Replication on Multiple Domains

The company I work for is in the process of creating collaboration among 3 domains. I have been tasked with setting up snapshot replication between SQL Servers on 2 of these domains from a 3rd. Furthermore the distributor is SQL Server 2000 and the subscriber is SQL Server 2014. I am finding that this may be more complicated than I originally thought.

First of all I don't seem to be able to even get started with setting up the distribution. When I click to configure distributions I  get the following error:

     SQL Server is unable to connect to server 'sqlserver1.companyname.com'.
     Additional information
     SQL Server replication requires the actual server name to make a connection to the server. Specify the actual server name, 'sqlserver1'.

I am thinking this may be related to the fact that I am having to connect using the FQDN of the server instead of the server name, but I am stumped after considerable research as to where to go from here. There also may be permission issues as I am logged in using credentials provided to me from admins on the other domain.

Supposedly all three domains have trusts enabled and we do have users logging in between domains.

Any insights would be greatly appreciated.

Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Your problem is that you're working with very old SQL Server versions.
With that said, you can't put a SQL Server 2000 database replicate to a SQL Server 2014 or vice-versa. For your information, SQL Server 2000 is not supported anymore, as well his successor SQL Server 2005. The minimum SQL Server version supported actually is SQL Server 2008 but even that one will have it supports ending this year.
So first things, first:
- Update your SQL Server instances from SQL Server 2000 to SQL Server 2012 (minimum) or SQL Server 2014 so you can level all versions to the same one.
Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence AnalystAuthor Commented:
I know that SQL Server is old and not supported. That is part of the issue. We inherited this from one of the other companies merging with us. They are in the process of building a 2014 server, but while they build the new server they need to still use the old one. But they can't use the old server directly because of some other obsolescence issue with a third party product that gets reports from it. So in the meantime we are trying to set up snapshot reports to another server that the third party product can connect to as a stop gap. BTW the SQL 2000 is sitting on a windows 2003 box. Feel like I stepped into a time machine. We get what we get.

So what I need in the meantime is a way to distribute these snapshots from the SQL 2000 database (which is at SP4 so I know it will do these) to a SQL 2012 box. I don't think that is the big issue here. What complicates it is that they are on 2 different domains. And I am accessing them from a third.

Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence AnalystAuthor Commented:
I actually got this figured out. The problem was that replication does not want me to set up things when logged in using the FQDN. It just wants the server name. So I added an entries in my local system's lmhosts file so that I could access the servers via SSMS using only the server names. Worked! Thanks Vitor for your attention.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Russell Scheinberg, MCSE Business IntelligenceSr. Business Intelligence AnalystAuthor Commented:
Figured it out using lmhosts file entry.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.