• Home
  • About Us
  • Contact Us
  • Privacy Policy
  • Special Offers
Business Intelligence Info
  • Business Intelligence
    • BI News and Info
    • Big Data
    • Mobile and Cloud
    • Self-Service BI
  • CRM
    • CRM News and Info
    • InfusionSoft
    • Microsoft Dynamics CRM
    • NetSuite
    • OnContact
    • Salesforce
    • Workbooks
  • Data Mining
    • Pentaho
    • Sisense
    • Tableau
    • TIBCO Spotfire
  • Data Warehousing
    • DWH News and Info
    • IBM DB2
    • Microsoft SQL Server
    • Oracle
    • Teradata
  • Predictive Analytics
    • FICO
    • KNIME
    • Mathematica
    • Matlab
    • Minitab
    • RapidMiner
    • Revolution
    • SAP
    • SAS/SPSS
  • Humor

How to Set Up Transactional Replication

April 17, 2020   BI News and Info

How many of you hear the word replication and shudder? There are indeed a lot of moving parts to it, but it is a valuable tool, and it is worth learning how to use. In this article, I’ll show you how to set it up.

For those of you who are new to the world of replication, let me briefly explain how it works from a 10,000-foot view. Replication is nothing more or less than copying data from Point A to Point B. SQL Server’s vernacular lends itself to comparison with magazine publication.

An author writes a magazine article. They want the world to read it. They send it off to a publisher. The publisher has the means to include it in a magazine that they own and send copies of that magazine out en masse. When the magazine is ready, it is packaged and mailed to a distribution center. The distribution center sends the magazines out to the subscribers.

It’s pretty much the same with SQL Server. You may have a table, a view or a stored procedure that you want to copy over somewhere else. Why? (HINT: NOT FOR BACKUP. Never use replication for backup).

One of the more common reasons I see for using replication is that you are using third-party vendor software that doesn’t allow indexing, so performance would grind to a stop if you used it for anything but straight data entry. To get around this, you copy over the data somewhere else where you can optimize (i.e., index) it and use the copy for reporting data.

To copy the object(s), you create a publication. Within that publication, you logically group tables, views, sprocs…whatever you need to replicate. These objects are your articles. I say that you logically group them because doing it that way keeps the publications manageable and easier to track. You don’t want one giant publication. Instead, group them in a way that makes sense to you and your end-users.

Once your publication is ready to go, SQL Server takes a snapshot of your data to send to a distribution database on your distributor server. The distributor is just what you probably think it is. It is the mechanism that sends your data to the subscribers (or the point(s) you are copying your data to). Once the snapshot is complete, the SQL Server begins replicating the data.

So now that you have an idea of how it works, let’s start the data flowing! This can be done by using the GUI, T-SQL or Replication Management Objects (RMOs). I am going to stay in my comfort zone – SQL Server.

Configuring the Distributor

Begin by ensuring you have replication installed. If you don’t have the replication feature installed, you should do it now. Just go to the installation for your version of SQL Server, select the “add features to an existing installation” option and install replication. If you aren’t sure if it’s installed, you can run EXEC sp_MS_replication_installed. If it runs successfully, you should be good to go. If it fails, it tells you to install the feature.

If you know you have replication installed, the next step is to configure distribution if you haven’t already. Go to the intended distributor server and right-click on the Replication folder in SSMS. Select Configure Distribution.

I am using SSMS v 18.4 for this walkthrough. For the purposes of this demo, I am choosing to use my own server as both distributor and publisher. The wizard will guide you through it – and so will I. You must decide if you want the server you are on to be the distributor or designate another server to be the distributor. Since this is a demo, my local instance will be its own distributor. If SQL Server Agent is not set to start automatically, you will also be prompted to configure it.

word image 5 How to Set Up Transactional Replication

Choose where you want the replication snapshot to be stored. The amount of space you need depends on the amount of data you will be snapshotting. You can choose a network share (recommended) or a folder on the server. Just ensure that the appropriate permissions are there. In this case, you’ll see further down that I am using SQL Agent to run replication, so, Agent needs permissions on the snapshot folder. It will need to read/write/modify/list folder contents/execute.

word image 6 How to Set Up Transactional Replication

Next, name your distribution database and tell SQL Server where to store the data and log files.

word image 7 How to Set Up Transactional Replication

If you need other servers to use this distributor, here is where you add them. I don’t need another one, so I am leaving it as is.

word image 8 How to Set Up Transactional Replication

The wizard asks you if you would like it to do the magic directly by running the scripts, or if you would prefer to wave the wand and have the wizard complete the steps.

word image 9 How to Set Up Transactional Replication

Here is your sanity check:

word image 10 How to Set Up Transactional Replication

And….poof! You have a distributor. If you want to “know that you know” that distribution is in place, there are several ways to do so. The first is to look for the distribution database, which is located in the system databases on your distributor server. The second is to go to your Replication tab in the Object Explorer tab of SSMS beneath the Server Objects folder. Right-click and choose to Launch Replication Monitor. You should see the distributor, but no publications yet.

Configuring the Publication

Now, set up replication. The goal is to replicate the AdventureWorks2012 (or any version of AdventureWorks that you have) tables in the Person and HumanResources schema to a database called AdventureWorksRepl.

word image 11 How to Set Up Transactional Replication

AdventureWorksRepl doesn’t exist yet. But it will.

Before starting on this step, make sure that the database has a valid owner. You can see this in the Files page of the Database Properties dialog.

word image 12 How to Set Up Transactional Replication

First, create the publication. Go to the Replication folder and right-click on the Local Publications folder. Select New Publication. After the Welcome screen, your next window should look like this:

word image 13 How to Set Up Transactional Replication

Make sure that you have selected the database you intend to be the publisher before clicking Next.

In the picture below, you’ll see that you have several replication options from which to choose. To quickly explain the difference, snapshot replication fires off on a specified schedule. It is a good choice for publications with data that only infrequently and predictably changes. Transactional replication is used primarily for real-time data. Peer-to-Peer replication is much more complicated. Publications can stream back and forth to each other. Conflict resolution is minimal, and it’s recommended that the application deal with the insert/update/delete behavior itself. Finally, there is Merge replication. This is primarily used when there isn’t a primary key on a table, or when there is a need to detect and resolve conflicts more robustly, when multiple subscribers are updating data offline and synchronizing after the fact, etc. In this case, the goal is real-time data transfer, so transactional publication it is!

word image 14 How to Set Up Transactional Replication

The goal is to copy tables in the Person and HumanResources schema. You could do these in separate publications, but for demo purposes, just do it in one. Check off the needed tables: all of the tables in the HumanResources and Person schemas.

word image 15 How to Set Up Transactional Replication

It will ask you next if you want to filter your articles. Trust me, you probably do. However, if your business users disagree and want all the data from the beginning of time, remember that the customer is always right. Just warn them not to be surprised if these publications take some time to sync the data whenever replication needs to be restarted.

For today’s purposes, though, filtering is not necessary.

word image 16 How to Set Up Transactional Replication

Run the snapshot immediately. This is going to create the snapshot to send the data to the distributor.

word image 17 How to Set Up Transactional Replication

Security often turns into a religious debate, but I’ll show you a simple way to configure it. Be sure to take time in your own environment to configure according to your company standards. Click Security Settings.

word image 18 How to Set Up Transactional Replication

I am choosing to use SQL Server Agent. However, you could create a special service account for this sole purpose. Ensure that password complexity is high and that only the DBAs have access to the password since this account must be a sysadmin to run replication. My SQL Agent already meets those requirements, which is why I am using it instead.

word image 19 How to Set Up Transactional Replication

Don’t judge me.

It will look like this after you click OK

word image 20 How to Set Up Transactional Replication

You’re almost done with the publication creation. To immediately create the publication, make sure that Create the publication is checked. You could also script out the script if you want to learn more or run in an automated process, for example.

word image 21 How to Set Up Transactional Replication

Now, give it a name!

word image 22 How to Set Up Transactional Replication

Once it’s complete, you’ll see this:

word image 23 How to Set Up Transactional Replication

Victory!

Configuring the Subscription

You’ve told SQL Server that you want to copy data – and what data to copy. Now, you have to tell it where to copy the data. Locate the new subscription by refreshing the Local Publications folder. Right-click the new publication and choose New Subscriptions. After clicking through the welcome page of the wizard, you should see this screen:

word image 24 How to Set Up Transactional Replication

Select your publication and click Next.

You now must choose between a push or a pull subscription. For this demo, you want a push subscription. Push subscriptions run from the distributor, which pushes out any changes in the data as soon as the log reader agent registers one. Pull subscriptions run from the subscriber, who “pulls” the data as it determines it needs to.

word image 25 How to Set Up Transactional Replication

Select the same server as the Subscriber. Click on the right-hand side of the empty box of the Subscription Database column. A pull-down menu will appear, giving you some databases from which to choose. The desired database doesn’t exist (yet), so choose <New database…>.

word image 26 How to Set Up Transactional Replication

Name your new database. Configure your size, autogrowth, and any other options as needed.

word image 27 How to Set Up Transactional Replication

When you click OK, it will look something like this:

word image 28 How to Set Up Transactional Replication

On the next screen, you will configure security. Click the ellipsis (….)

word image 29 How to Set Up Transactional Replication

And once again, I choose the following options. You may choose not to be like me. It’s OK; we can agree to disagree.

word image 30 How to Set Up Transactional Replication

After you do this, you’ll get the following screen:

word image 31 How to Set Up Transactional Replication

On the next screen, you will configure the schedule. In this case, the SQL Agent job will run continuously.

word image 32 How to Set Up Transactional Replication

Now, tell replication to initialize the publication right away.

word image 33 How to Set Up Transactional Replication

Make it official and tell replication to create the subscription.

word image 34 How to Set Up Transactional Replication

Here is your last-chance-sanity-check before creating the subscription.

word image 35 How to Set Up Transactional Replication

Click Finish. If the subscription was a success, you’ll see this:

word image 36 How to Set Up Transactional Replication

Verification

Now that it’s complete, verify that everything went across as expected. Right-click your publication and choose View Snapshot Agent Status and ensure that the snapshot completed.

word image 37 How to Set Up Transactional Replication

Yay!

Now, if you right-click the Replication folder and click Launch Replication Monitor, you’ll get a whole new view of things. I prefer the distributor view, so I’m choosing that option. It allows me a more global view.

word image 38 How to Set Up Transactional Replication

Select your publication on the left. This is a view of the snapshot agent. It shows that the replication snapshot has completed and has been sent to the distributor.

word image 39 How to Set Up Transactional Replication

This is the distributor agent, which shows the data being sent from the distributor to the tables.

word image 40 How to Set Up Transactional Replication

If you right-click the publication in the tree on the left and click View Details, you can get a look under the covers. This is helpful for troubleshooting or checking progress.

word image 41 How to Set Up Transactional Replication

Now, when you check SSMS, you should see this!

word image 42 How to Set Up Transactional Replication

Finally, make sure that the data all went over. Go back to Replication Monitor and right-click on your publication. Choose Validate Subscriptions…. Choose your publication.

word image 43 How to Set Up Transactional Replication

Click Validation Options. Check the button for a fast row count and uncheck the Compare checksums to verify row data…. Really, trust me. You’ll be glad you did because they warn that the process can take a long time, and depending on the size of the table, it really can. Click OK.

word image 44 How to Set Up Transactional Replication

Go back to Replication Monitor. Go to your inner window and right-click the publication. Click View Details.

word image 45 How to Set Up Transactional Replication

When it’s done, you’ll see something like this:

word image 46 How to Set Up Transactional Replication

That rowcount validation does just what you might think it does. It compares the rows of data between the publisher and the subscriber to confirm that it is all there. And it is!

Conclusion

You did it! It wasn’t awful, was it? You now have another tool in your toolbelt for managing data (especially reporting data), and an availability alternative. Well done!

Let’s block ads! (Why?)

SQL – Simple Talk

Replication, Transactional
  • Recent Posts

    • PUNNIES
    • Cashierless tech could detect shoplifting, but bias concerns abound
    • Misunderstood Loyalty
    • Pearl with a girl earring
    • Dynamics 365 Monthly Update-January 2021
  • Categories

  • Archives

    • January 2021
    • December 2020
    • November 2020
    • October 2020
    • September 2020
    • August 2020
    • July 2020
    • June 2020
    • May 2020
    • April 2020
    • March 2020
    • February 2020
    • January 2020
    • December 2019
    • November 2019
    • October 2019
    • September 2019
    • August 2019
    • July 2019
    • June 2019
    • May 2019
    • April 2019
    • March 2019
    • February 2019
    • January 2019
    • December 2018
    • November 2018
    • October 2018
    • September 2018
    • August 2018
    • July 2018
    • June 2018
    • May 2018
    • April 2018
    • March 2018
    • February 2018
    • January 2018
    • December 2017
    • November 2017
    • October 2017
    • September 2017
    • August 2017
    • July 2017
    • June 2017
    • May 2017
    • April 2017
    • March 2017
    • February 2017
    • January 2017
    • December 2016
    • November 2016
    • October 2016
    • September 2016
    • August 2016
    • July 2016
    • June 2016
    • May 2016
    • April 2016
    • March 2016
    • February 2016
    • January 2016
    • December 2015
    • November 2015
    • October 2015
    • September 2015
    • August 2015
    • July 2015
    • June 2015
    • May 2015
    • April 2015
    • March 2015
    • February 2015
    • January 2015
    • December 2014
    • November 2014
© 2021 Business Intelligence Info
Power BI Training | G Com Solutions Limited