Replication Series, Part Five: Creating a Snapshot Replication

NewPub

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.

ReplArticles

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!

Advertisements

About SQLGator

Florida Gator and Microsoft Data Platform MVP, Star Wars fanatic and is there anything else...oh yeah PS4! I am a geek and SQL Server DBA who dabbles with VMWare, 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:

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

%d bloggers like this: