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.
Best Answer
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,
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.