Setup a Simple Transactional Replication lab

At work we lost our super star DBA some months back, and though he is sorely missed, this has created opportunities for me to grow in my SQL Server knowledge. One of the key components of our Business Intelligence environment (my world!) is our replication between PROD and the Data Warehouse. Because this replication is critical to our reporting, not to mention my livelihood, I have been digging in to how transactional replication works.  So the first step on this journey is to get a playground setup on my personal laptop and let the learning begin. I’m glad you are here to follow along.

We are going to replicate the dbo.Badges table from the StackOverflow database to small database called SO_small, using transactional replication in SQL Server 2016.

image

Setup the Distributor and Publisher

For the lab, we will set up all replication components on the same instance. In particular we’ll run a local distributor, which is different from most production environments, as I found out. However the constraints of my home lab dictate this setup.

All our configuration can be done with the wizards.  First, select “Replication” folder, and choose “Configure Distribution”:

image

Now you’ll get the “distributor” screen of the wizard. As mentioned above, choose to have all replication components on the same instance for our walk through:

image

We’ll choose to have our SQL Server Agent start automatically.

image

We now need to create the Snapshot Folder for initial seeding of table:

Create folder on DB server

We need a folder to hold the snapshot that will be created.  I called mine “sql_replic_share”. Super creative!

image

For purposes of the demo, just grant folder permissions to the “Authenticated Users” group to get going. NOT RECOMMENDED IN PRODUCTION!

image

Setup folder sharing on the sql_replic_share. Again for demo, grant “Everyone” read control. NOT RECOMMENDED FOR PRODUCTION!

image

Now populate “Snapshot Folder” with the correct path to the folder that will hold our initial snapshot and continue:

image

Configure the Distribution DB:

A distribution database will be created as part of this process and will be visible in the System databases folder. Be sure to choose the location that has the storage you are wanting to use:

image

As noted, this will create a System Database called “distDB”

image

Configure publishers. The local instance is Auto filled since we are setting up as a Distributor locally:

image

Let SQL Server do the distribution configuration:

image

Verify configuration. If it all looks good, hit Finish:

image

The wizard should finish successfully:

image

 

Setup Publication

We are wanting to replicate the dbo.Badges table FROM StackOverflow to the SO_small database on the same SQL Server instance.

Right click the Local Publications folder under the Replication folder, and choose New Publication:

image

We want to publish dbo.Badges from the StackOverflow database, so that is our Publication database for this dialog:

image

We are setting up Transactional Replication, so we will use that type for our Publication:

image

Our goal is to publish StackOverflow.dbo.Badges, so that is the Article we care about to publish. We are taking all the columns, though we do not have to:

image

For kicks, we’ll add a filter to our Article, so that we only get the Badges since 1/1/2016. In our case this will speed our queries and conserve space:

image

image

Our filter is now populated on the dialog:

image

Snapshot:

We are now at the step where the initial snapshot of our data will be created. We’ll create ours right away. This is the initial “seeding” of our replicated table in our subscriber database:

image

For our demo, we are going to run the Snapshot Agent under the SQL Server Agent service account, though usually you want to have a separate account to run each agent:

image

We’ll create our publication straight away, though you could also have SQL Server create a script for later use:

image

After hitting next, you’ll be presented with a chance to name your Publication. I chose something descriptive, including the fact that my publication contains a filter:

image

Hit finish, and if all goes well you’ll be presented with a dialog that reflects success:

image

Setup the Subscription

Now we’ll setup our subscription. In most real-world environments this subscription would live in a different instance from the Publisher. But this is a demo… so let’s keep it easy:

Expand “Replication”, and then right-click Local Publication and choose “New Subscriptions…”

image

Since we have but one publication, it is the only one to show in our window. Hit Next to be taken to the Distribution Agent Location dialog:

image

We will be creating a “push subscription” which is the default. Hit next.

image

On the “Subscribers” page, we choose our server and the database that will be our subscription database (SO_small):

Now we come the Distribution Agent Security Window. The distribution agent will be responsible for moving data from the distribution to the subscriber databases. So the accounts specified must be able to connect to each

image

We’ll click the … ellipsis next to the Connection to Subscriber box, and then we will specify the account that will run the Distributor Agent process. Since this is non-production, we’ll go ahead with “Run Under the SQL Server Agent server account”

image

Takes us back to the screen

image

We will choose the schedule that the agent will run. We choose “Run Continuously”

image

Now let’s initialize our subscription with a snapshot. Keep in mind this can take many hours/days depending on the size of the object being replicated. Our table is small, so it takes no time at all.  But a table at work took over a day, because it was rather wide and contained several billion rows. So beware of this.

image

We’ll let the wizard do its work, though you could also generate a script if so desired:

image

image

And this is what success looks like:

image

Or so it seems… however

Gotchas!

I ran into a problem after initially setting up my lab. You might find this useful. After waiting awhile and looking at our SO_small database, no dbo.Badges table showed up in our Subscription database (SO_small)

image

WTH? Fortunately we have a tool for monitoring replication that is accessible through SSMS. Not surprisingly it is called Replication Monitor (who would have guessed?). Go to the Replication folder in SSMS, right click and choose “Launch Replication Monitor”

image

We are presented with an application designed to help us figure out what is happening in replication at a given moment:

image

As we see, there is no shortage of red x’s, an almost universal sign of trouble. Let’s click on that “Agents” tab with the big red X:

Upon clicking the first error in the list on that tab, we see our problem has to do with access to our share that we created to hold our Snapshot:

image

First thing I’ll try is granting “Full Control” to the Everyone group on the share (again non for production!)

After making the permissions change, right click the line that shows the Error and list Snapshot Agent in the job column.  Choose “Start Agent”

image

When all is well, our replication monitor view will be gleefully free of the red x’s:

image

And sure enough, our table is now replicated in the SO_small database!

image

Add another article to an existing publication

The beauty of this is that it does not require another full snapshot, as long as you don’t change any other article’s properties in these steps. A mini-snapshot will be taken just for the object you are adding to replication in the current publication.

Right click the local publication we created above (BadgesFilteredPublication) and choose Properties:

image

On the Publication properties page, go to the Articles tab, uncheck “Show only checked articles in the list” and check the box for “Posts (dbo)” under the Objects to publish part of the dialog. Uncheck the “Body” column, as we don’t wish to replicate this NVARCHAR(MAX) column in our demo. Also, note we are leaving “Badges (dbo)” checked, as it is the existing article that is part of this publication. Hit “OK”.

image

At this point the snapshot agent will run, and generate the needed snapshot into the snapshot shared folder:

image

When the snapshot is applied, your replicated table will be available in the subscriber database:

image

At this point you have all the components of replication in place in your environment, and can see data flowing between the StackOverflow.dbo.Badges table and your SO_small.dbo.Badges table.  Next post we’ll put this to use! Hope to see you then.

 

2 thoughts on “Setup a Simple Transactional Replication lab

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