Notes on SQL

By far the easiest way to create the Distributor is via the Wizard and examples abound of that – one such example is at SQL Server Central http://www.sqlservercentral.com/articles/Stairway+Series/72282/ .

So why use scripts?

Mainly for Disaster Recovery and consistency. Should you have to rebuild the server then running the scripts is easier and more reliable than re-running the Wizard – there’s less chance somebody can enter different values and therefore cause issues with the Replication.

So, let’s have a look at the scripts behind this wizard.

Firstly – I have three servers, installed as separate instances on the same machine:
Publisher-A, as the Publisher
Distributor-B as the Distributor and
Subscriber-C as the Subscriber.
Currently none of these servers are configured for Replication. All instances were installed to cater for Replication but that is all.

Firstly, make sure the Agent Services are running – it makes life easier and you will need them to actual perform the replication tasks. SQL Configuration Manager can be used for this.

There are four scripts in the standard Distributor creation, gleaned from scripting the Wizard. All of these script are being executed on ‘Distributor-B’ and of course I’ve replaced the server name – I may well be working in a secure environment but there’s no need to tempt anybody.

1. Designate the Distributor server:

use master
exec sp_adddistributor 
     @distributor = N'9999-PC\Distributor-B', 
     @password = N'Spr0uts' 
GO 

Having executed this code you will see an additional entry in sys.sysservers, giving ‘srvname’ of ‘repl_distributor’ and showing ‘dist’ as 1. An associated entries under ‘Linked Servers’ within SSMS will also be visible.

Next, create the Distibutor database:

exec sp_adddistributiondb @database = N'distribution', 
			@data_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.Distributor-B\MSSQL\DATA', 
			@log_folder = N'C:\Program Files\Microsoft SQL Server\MSSQL11.Distributor-B\MSSQL\DATA', 
			@log_file_size = 2, 
			@min_distretention = 0, 
			@max_distretention = 72, 
			@history_retention = 48, 
			@security_mode = 0, 
			@login = N'sa',--login used when connecting to the distributor to create the distribution database 
			@password='Spr0uts' 
GO 

I’ve left the default name for the DB ‘distributor’ and in this case have left the other defaults too (retention periods and login).
As this code executes it displays various messages relating to the creation of the DB, as well as adding the roles ‘guest’ and ‘replmonitor’. Once completed the database is visible under ‘System Databases’ as a distributor:

The next command runs against this new distributor database and is used to record the location of the snapshot folder. Now as I don’t intend to use snapshots I’m only including this because all scripts appear to include it and I can’t find a decent explanation for it. When I have the time I’m going to try excluding this command and see what fun it creates. For now though I’ll just play safe:

use [distribution] 
if (not exists (select * from sysobjects where name = 'UIProperties' and type = 'U ')) 
	create table UIProperties(id int) 
if (exists (select * from ::fn_listextendedproperty('SnapshotFolder', 'user', 'dbo', 'table', 'UIProperties', null, null))) 
	EXEC sp_updateextendedproperty N'SnapshotFolder', N'\\9999-PC\C$\Replication\Snapshot', 'user', dbo, 'table', 'UIProperties' 
else 
	EXEC sp_addextendedproperty N'SnapshotFolder', N'\\9999-PC\C$\Replication\Snapshot', 'user', dbo, 'table', 'UIProperties' 
GO 

Notice that the locations needs a fully-qualified name.

The final command registers a Publisher that will use this Distributor:

exec sp_adddistpublisher @publisher = N'9999-PC\Publisher-A', 
		@distribution_db = N'distribution', 
		@security_mode = 1, 
		@working_directory = N'\\9999-PC\C$\Replication\Snapshot', 
		@trusted = N'false', 
		@thirdparty_flag = 0, 
		@publisher_type = N'MSSQLSERVER'
GO

This will create an entry under ‘Linked Servers’ for the Publisher. The only response from this code is the usual ‘Command completed successfully’ message. Trying it a second time will tell you that the server is already listed as a publisher.

Note: If you’ve used the ‘Configure Distribution Wizard’ to create the scripts, you’ll notice that the Distributor is also created as a Publisher. This step isn’t necessary for the configuration I’m using here.

Now we have a Distributor created and can use that to build upon in following articles.