How to Create a Publication
Published: Oct 21, 2013
This is generic guide as, to be honest, setting up all replication is pretty much the same. There are a couple of extra screens for Merge replication and I’ll highlight those where necessary. Otherwise it’s all the same.

Obviously all replication can be set up in code, but this is one of those cases in which the Wizard is far simpler, incredibly effective, and therefore that’s what I’ll be using.

First, head to SSMS, expand Replication, right click “Local Publications”, and click “New Publication…”:

Note: If you see a form headed Distributor, please refer to my article here.

You will see a list of databases on your server. In my case there’s only the one. Select the database you wish to replicate and click “Next”…

Choose the replication you require. In this case I happen to be choosing Snapshot. Click Next…

If you are using Merge then you will see the following:

Please select the option that reflects the lowest version of SQL Server partaking in the merge replication. Click Next…

You will then be presented with an Articles form. In here we can select the tables, views, and procedures which are available for replication.

I’m going to select just one table and one procedure (I have blanked out my table and proc names for security):

At this point I would advise reading my webpage concerning Article Properties here to make sure that you have got the right options selected as for the sake of this guide I will be using the defaults which are unlikely to be the best for your situation.

Click Next and you may find this warning… if you do then please take note of it as it is warning you that you are replicating something that has links to another object not in replication. Therefore this could cause you problems in the future.

In my case I’m going to ignore it as this is a demo and I picked this proc purely to cause the error.

For Merge you will also be informed that the Merge process will append a rowguid column to each table you have selected…

The next screen gives you the option of filtering your publication. I’m not going to cover this here, but feel free to look around this screen and see what filtering can offer you. For the moment we’re going to skip ahead, so click Next…

On this next screen you will be given the chance to create a snapshot and specify a schedule if applicable.

In general I only create schedules for Merge replication, but not for Transactional and Snapshot…

However, one word of caution I will specify is with regard to the top option. It looks very appealing to have SQL create a snapshot and then leave it for you to initialise subscriptions. But please don’t tick this in Transactional Replication. It can come back to haunt you. Please refer to my article here for details. Only tick this in Merge.

So I’m going to untick everything and click Next…

Within this screen we need to set the security under which the Snapshot Agent runs. Click Security Settings:

I tend to go with the option I have selected above which is the non recommended approach. However, if you have a fixed domain account under which you can run your SQL Server processes, please use that as it is the recommended option.

The same goes for the security surrounding Connecting to the Publisher. Once set, Click OK, the Next…

Click Next again (and tick the box if you wish to generate scripts)…

Name your publication and you’re done.

Hit finish and your publication will be created:

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

Your SQL Trainer
Kevin Urquhart

I am a SQL Server DBA, Architect, Developer, and Trainer for SQL Training. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Kevin Urquhart
SQL Server Consultant, London

Categories


Archives


Copyright © 2018 London SQL Server Training Courses @ SQL Training | Website Design From Scott Heron