In order to get up and running creating Publications on your server, you first need to either set up a Distributor, or register a pre-existing remote Distributor. To do this we’ll use the Wizard.
In SSMS, right click Replication and select “Configure Distribution…”:
This will open the following form:
At this point the guide splits into two:
Click Next and you’ll see the following form:
Notice that the form defaults to a local drive as the snapshot folder. We don’t want this as subscriptions will not work (I’ll do a piece on this later).
Anyway, as the form implies, we want a network drive. Therefore enter either a network folder or, if you want to use local drives to store your snapshots, just replace the “C:\” with the syntax “\\C$\”. Then hit Next…
Here we get to set up the distribution database. Now, if you want it called something random, here’s your chance, but to be honest I’ve only ever seen one company use anything other than Distribution and that was because they already had a user database with that name (!!!).
Anyway, set the name and the locations for the data and log files and press Next…
You’ll see your server in the list of Publishers… you can also add others if you wish them to use this as well, but we’ll just hit Next for now…
These can be left as defaults. Hit Next…
And you’re done. Just hit Finish and that’ll complete the task.
Register Pre-existing Distributor
To do this, you need to register your server as a Publisher at the Distributor server.
Connect to your Distributor, right click Replication, and select “Distributor Properties…”:
You should see a form similar to this:
Select “Publishers” from the tab on the left and you will see the following:
Click Add, then “SQL Server Publisher…”:
Connect to your Publisher using the correct credentials and you should see the following message:
Click OK and your server should appear in the Publisher list, however, the password fields will be blank:
Enter a password and click OK.
Now, go back to your Publisher and the Configure Distribution Wizard.
Select “Use the following server as the Distributor” and click “Add”:
Enter the name of your Distributor and whatever credentials you have, then click “Connect”.
This will add the Distributor name in the window and allow you to press “Next->”
Enter the password you specified on the Distributor and press Next…
Leave this on the defaults (as above) and click Next…
That’s it. Press Finish and you’re done.