1

I seem to have big difficulties setting up the first replication ever between two SQL2000 instances.

They both run in seperate WAN's (IP with port assigned for SQL-remote) but I have been able to connection them through remote-SQL so they appear in Enterprise Manager in both places.

I cant figure out how to do this correctly, as I have tried many different approaches and got a lot of errors trying to do it.

I dont know if its a faulty installation (with SP4) or some rights I need to ajust or what I am trying to do isnt possible.

My idea is to take the primary server (lets call it MASTER or #5) and then PUBLISH some of its databases with replication to the secondary/backup server (lets call it SLAVE or #4)

I've added a "sync account login" on both, with same name and password to see if that would make things easier. I have FULL administrative access, so any needed changes can be made to this setup - as long as I get it fixed soon. (Have been starting to consider leaving SQL-server completely if this has to take so long time to fix, and check out MySQL or something else)...

BUT... to the current task/situation:

We have two servers, one in production and one in development, and I would REALLY like to have changes MERGE between these two "single instances". There is NO ActiveDirectory mechanism near them, so they are 100% stand-alone machines.

When I try to configure the master as "Distributor/Publisher" the Wizard halts with error of something being "(null)" ??? I get some weird error codes that tells me nothing usefull.

So I was wondering, is there a "guided tour" somewhere on HOW to do this in the right order, so that I can check if I have missed something before trying to set it up. Some rights, some flags, some patches or extra firewall ports or something weird needed for replication.

It looks so easy in every example I have found so far, but it just doesnt work ...

HELP!... please :-)

3
  • wondering if I need to explain it better or I have asked this quuestion wrong? What can I do better, anyone? Aug 29, 2010 at 21:08
  • 1
    More information about the error you are getting would be helpful.
    – mrdenny
    Aug 30, 2010 at 22:37
  • okay, provided the ERROR below the first answer now + my steps to reproduce it as I tested the suggestion. "SQL Server Enterprise Manager could not configure '.... SQL2000' as the Distributor for '.... SQL2000'. Error 14114: (null) is not configured as a Distributor" Sep 1, 2010 at 11:30

1 Answer 1

0

To get replication running between two servers which aren't on the same domain, you'll need to do one of the following.

  1. Add the remote server name to the other servers host file.
  2. Add the remote server to the local domains DNS/WINS servers.

After that each machine should be able to access the other via the local name which is required for replication to work. Once name resolution is working ok you should be able to just run through the wizard to get things setup.

Now keep in mind that it is usually recommended to not replicate data between production and dev, especially with merge replication as any changes made in your development environment will be replicated to the production database.

Also keep in mind that once you replicate a table you can't make any schema changes to those tables using the normal ALTER TABLE commands. You'll have to use the replication procedures to add columns. Another catch is that SQL 2000 replication will be adding a guid column to every table that you are replicating.

Where is the process breaking? Are you able to get the distributor setup?

9
  • AHA! so the HOSTS file might be the trouble as they run without ActiveDirectory and Domaincontrollers. I've only added them into "client network configuration" as "alias" and through that I am able to bind them as "linked" (have removed them again though). I cannot setup the distributor, it halts already there telling me something about "(null)"... cant recall the specific error as I have been trying other solutions meanwhile (eg. installing SQL-2008 Express on other dev-servers to see if that was easier or same problem - Express doenst have replication though...) Sep 1, 2010 at 10:57
  • okay, I will try your idea and log my progress here: First I have added both servers IP in c:\windows\system32\drivers\etc\hosts (Its windows 2003 on both machines with SQL2000 SP4) The IP's are also know by normal domains, we have some special domains we use to keep track of server-IPs from any site, so the HOSTS shouldnt be neccessary - but who knows where SQL2000 look for such details. Sep 1, 2010 at 11:22
  • ** Configure Publishing and Distrbution Wizard for ... SQL-2000 *** 1. step: "Make SQL-2000 its own Distributor" 2. step: snapshot folder: <choosing default location for a start> 2b. step: "are you sure you wanna use this snapshot folder path?" (ehm yes!) Sep 1, 2010 at 11:25
  • 3. step: "do you want to customize publishing and distribution settings?" (no, use the following default settings...) just want to make it work... Sep 1, 2010 at 11:26
  • 1
    Are you using Push or Pull replication? If push you can stick with the local path for the snapshot folder. If pull you'll need to change that to a UNC Path. Have you changed the name of the server which will be the distributor/publisher? Run "SELECT @@SERVERNAME" and make sure what comes back is correct. If it is wrong or blank I can give you the T/SQL to fix it.
    – mrdenny
    Sep 1, 2010 at 18:13

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .