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

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

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!

Continuing our series on replication, today we look at Snapshot Replication.  When is the best time to use snapshot replication?

1. Your data changes infrequently or you have a set of static tables such as lookup tables.
2. You have a small amount of data with which to replicate.
3. You make a great deal of changes over short periods of time.  For example, you have a database that only gets updated once a year like for open enrollment.

One of the caveats to snapshot replication is the snapshot folder permissions.  The replication agents must have the appropriate permissions to read and write to the snapshot folder.

Another caveat is that you can only compress the snapshot if it is less than 2GB in size.

Stay tuned and enjoy tomorrow as we configure the snapshot replication!

Continuing our series on replication, today we look at the steps to prepare for replication found within SQL Server.

The first step is to ensure that you have replication installed on each of the nodes that you wish to use replication.  One good way to determine if you have it installed is to run the Installed SQL Server features discovery report from the tools menu of the SQL Server Installation Center.  If you do not see the feature installed then you will need to install it.

Figure 1 – Discovery Report

Stay tuned and enjoy!

Continuing our series on replication, today we look at the different types of replication found within SQL Server.

Within the replication realm we have four different forms of replication:

1. Transactional replication: transactions are updated as soon as they happen (near real time) between the publisher and subscriber.
2. Snapshot replication: replicates data at a point-in-time, typically used for data that does not change often.  Completes refreshes of data entirely instead of incrementally.
3. Peer-to-Peer replication: similar to transactional replication, replicates data to multiple server instances or nodes.  Changes occur as they happen and allows for data redundancy.
4. Merge replication: similar to transactional replication, but changes are made via synchronization triggers when the nodes are reconnected.

Stay tuned and enjoy!

Fresh back from a week of intense training in the SQLskills.com Immersion Event Two, I thought I would start a new learning series on replication.  Today we will start with the basics, definitions.

1. Publisher: A database instance that publishes data to other instances.
2. Subscriber: A database instance that receives publications.
3. Article: A published object which can be a table, view, user-defined function, indexed view, or stored procedure.
4. Publication: A collection of articles to be published.
5. Distributor: A database instance that manages the distribution between publisher and subscriber.
6. Local Distributor: A distributor located on the same LAN as the publisher.
7. Remote Distributor: A distributor located on a remote network from the publisher.
8. Agent: A service enabling replication tasks such as publishing, distributing, and subscribing.

Stay tuned and enjoy!