Replication Series, Part Five: Creating a Snapshot Replication


Figure 1 – New Publication

Continuing our series on replication, today we look a little deeper at Snapshot Replication.  After you have ensured that replication is installed on both nodes, open the replication node in SQL Server management studio on the node that you want to be the publisher and right-click Local Publisher and select New Publication (See Figure 1).

This will open the New Publication Wizard.  Select Next if you are presented with a starting page.  Choose the defaulted server which should be your local server and select Next.

Now we are prompted for the snapshot folder.  This is the network share that needs to have permissions enabled for the publisher and subscriber.

Next we will choose the database that we wish to replicate and then select Next.  Then we will select the Snapshot publication type as shown in Figure 2.

Figure 2 - Replication Type

Figure 2 – Replication Type

Then we will select the articles or tables and views that we wish to replicate.  Here, I have selected all tables and views in Figure 3, but you can drill down and select only certain ones if you prefer.  The next screen indicates if there are any issues and how to correct them.


Figure 3 – Select Articles

Now we must setup the agent and create the snapshot so that we may initialize the subscription as shown in Figure 4.  Select the Change button to set up your scheduled agent times to generate your snapshot.

Figure 4  - Snapshot Agent

Figure 4 – Snapshot Agent

On the final page of the wizard, we must select an account that will run the agent. You can run this under a Windows account or the SQL Server Agent service but I would not recommend that as it is not best practices.  After you name the publication you will see a status screen as your publication is set up by SQL Server.  You’re done!

Stay tuned and enjoy!


About SQLGator

Microsoft Data Platform MVP, Florida Gator, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server Business Intelligence Consultant, there are other technologies greater than these? Not so fast my friend! I also love to travel to new and exotic places.

Posted on February 26, 2013, in AlwaysOn and tagged . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: