0

I'm setting up replication from a source server to a destination server using a third party distribution server. I've added the source server to the list of publishers on the distribution server (Distributor Properties => Add => Add SQL Server). When I go to add a new publication on the distribution server, I don't see the source server's databases as options to replicate to the destination server. Also, if I select the option "Publisher Properties" on the distribution server, to see a list of databases that are checked - either transactional or merge - none of the databases of the source server are listed.

Oddly enough, if the publisher was an Oracle server (it is not), I see the option to add that by entering the server on the distribution server, so I'm confused why I'm not seeing it for SQL Server.

Added (current answer is wrong)

If I do the steps suggested in the current answer, then look at the Job Agent step, what I get is:

-Publisher [SourceServer] -PublisherDB [DB] -Distributor [SourceServer] -Publication [NAME] [Security Related Info]

In addition sp_get_distributor also shows the wrong server as the distributor (SourceServer).

Let me explain again:

The publisher should be the source server. The distributor should be the distributor server. Therefore "I've added the source server to the list of publishers on the distribution server" and then creating the publication on the source server does not correctly set up the proper distributor server.

Also, to correct Microsoft's own website at this link, if I follow to the letter what they say, they are flat out wrong on step 4

Specify a Distributor if distribution has not been configured on the server. For more information about configuring distribution, see Configure Publishing and Distribution. If you specify on the Distributor page that the Publisher server will act as its own Distributor (a local Distributor), and the server is not configured as a Distributor, the New Publication Wizard will configure the server. You will specify a default snapshot folder for the Distributor on the Snapshot Folder page. The snapshot folder is simply a directory that you have designated as a share; agents that read from and write to this folder must have sufficient permissions to access it. For more information about securing the folder appropriately, see Secure the Snapshot Folder. If you specify that another server should act as the Distributor, you must enter a password on the Administrative Password page for connections made from the Publisher to the Distributor. This password must match the password specified when the Publisher was enabled at the remote Distributor. For more information, see Configure Distribution.

In 2012, there is no option to specify a distributor. So, if I go to their link to set one up:

Right-click the Replication folder, and then click Configure Distribution.

There is no Configure Distribution option.

Improve this question

2 Answers 2

Reset to default
1

I'm not sure if this helps you or not, but In SSMS in all versions (2005+), if you haven't configured Distribution, you'll see the option configure distribution,

enter image description here

but if you've configured a distribution or have one on the server, then you'll have the below menu and won't see configure distribution.

enter image description here

Improve this answer
0
0

Im not sure why you're able to see the databases when the publisher is an Oracle server, but for SQL Server you need to add the publication on the Publisher, not the distributor. This article will walk you through the process in SSMS: https://msdn.microsoft.com/en-us/library/ms151160(v=sql.105).aspx

Improve this answer
5
  • So it sounds like I need to go to Distributor Properties on the publisher server (2) and add the distributor server (1) as a Publisher. Yes, that makes perfect sense, lol.
    – guest125697
    Oct 15, 2015 at 19:20
  • No, you need connect to the Publisher (source server) in SSMS, expand the replication folder, right-click "Local Publications" and click "New Publication". You've already added the distributor, so you don't need to do anything else with that server.
    – Nabil Becker
    Oct 15, 2015 at 19:25
  • No, I haven't added the distribution server. That's what I'm trying to do.
    – guest125697
    Oct 15, 2015 at 19:32
  • "I've added the source server to the list of publishers on the distribution server (Distributor Properties => Add => Add SQL Server)." It sounds like you have... If not, follow those steps.
    – Nabil Becker
    Oct 15, 2015 at 19:36
  • Yes, and if I do that, and set up a publication from the source server, it creates the distribution database locally. That's not what I want. Everything related to distribution must be on the distribution server, not source server, including files, file location, etc. So, following those steps didn't accomplish that when I did set up a test publication - it created the distribution locally and that's not what I want at all.
    – guest125697
    Oct 15, 2015 at 19:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.