In August we started taking a look at replication. We learned some of the terms used, and set up a (probably) pretty basic example of replication. Now it’s time to work with it a bit more.

• Open the Replication Monitor.
  • Right click on different entries. See what options you have.
  • Left click on them and see what shows up.
  • Insert a tracer. What does the tracer do? What does the output mean?
  • Configure an alert. There are a fair number of options here but hopefully you’ve created an alert before so this should be pretty familiar.
  • If you have the option connect to a listener for an instance that has replication and open the replication monitor. What was different?
• Go to the subscriber of an publication. Insert a row into one of the replicated tables. To make this easier make sure that table has a primary key. Then go back to the publisher and insert the same row into the same table. Give it a few minutes and check the replication monitor again. You should see that this is a particularly bad idea. How do you fix it? How do you prevent it?
• Add a new table to the subscriber database. Insert some data into that table. Did that cause a problem with replication? What are some reasons you might do this? What are some reasons why you shouldn’t? How do you avoid problems?
• Modify a replicated stored procedure. Modify a replicated table. Add a column, or change a column’s datatype. Take a look at the subscriber. Did the changes make it there? How about if you add an index?
• Add a second subscriber to a publication. Insert some rows into a replicated table (to be clear add it on the publisher). Take a look at both subscribers. Did the data make it? Why might you want multiple subscribers?

You’ll hear a lot of people complain about replication. Including me. That said, replication is an amazing tool. There really isn’t anything quite like it. Availability Groups, Mirroring, and tools like that will let you create a copy of a database and keep it up to date. But what if you have a terrabyte+ sized database and only want to copy a couple of small(ish) tables? That’s where replication comes into play.

There are things that replication can do that HA (high availability) tools just can’t. Copy just specific tables or code (stored procedures, functions, etc), copy a portion of a table (only the rows where the last names are between F and H), create multiple updatable copies of a database that all update a central repository, or even update each other so that all copies contain the changes from all of the other copies. That’s some really useful stuff.

So let’s get started with replication. We’ll be working on it for a few months so fair warning.

• Learn the terms of replication. For example subscriber, distributor, article, and any others that you can find.
• Set up replication on at least one of your lab instances.
  • Set up a distributor. Make a note of some of the options here.
  • Set up a publication.
    • Select multiple tables, but not all.
    • For at least one table do not include all of the indexes in the replication.
      • You are going to want to know what this looks like when one of your indexes doesn’t show up in your replicated copy.
    • Select multiple stored procedures, views, and functions, but again not all.
  • Set up a subscription. It can be on the same instance, or a different one.
  • In your new database (the subscriber) set up the permissions so that users can only read from the replicated tables.
  • In your new database create a new table and give at least one user access to both read and write to it.

I did a little searching around and came across this post in StackExchange. It has a nice little query to produce the data I needed and I wanted to share. I’ve made a few changes to the queries the poster used because of a few issues I have in my system. As time goes by I may add to these, we will see.

First things first, a quick query to get all of the databases on your server that have a publication.

SELECT * FROM sys.databases 
WHERE is_published = 1 or is_merge_published = 1;

Next, for the databases with transactional replication (is_published = 1) use the following query.

USE publisherDB; /* This is the database where the publication exists. */
SELECT  
	DB_Name() PublicationDB 
	, sp.name AS PublicationName
	, sp.status AS PublicationActive
	, sa.name AS ArticleName 
	, o.name AS ObjectName
	, srv.srvname AS SubscriberServerName 
	, s.dest_db AS SubscriberDBName
FROM dbo.syspublications sp  
JOIN dbo.sysarticles sa ON sp.pubid = sa.pubid 
LEFT OUTER JOIN dbo.syssubscriptions s ON sa.artid = s.artid 
LEFT OUTER JOIN master.dbo.sysservers srv ON s.srvid = srv.srvid
JOIN sys.objects o ON sa.objid = o.object_id;

You’ll notice the left outer joins on the subscription section. That’s because we are using AWS DMS in my office, which requires a publication, but doesn’t actually have a subscription.

For the databases with merge replication (is_merge_published = 1) use the following query.

USE publisherDB; /* This is the database where the publication exists. */
SELECT DISTINCT 
    sp.publisher AS PublisherServer
    , sp.publisher_db AS PublicationDB
	, sp.name AS PublicationName
    , sa.name AS ArticleName
	, o.name AS ObjectName
    , ss.subscriber_server AS SubscriberServerName 
	, s.dest_db AS SubscriberDBName
FROM dbo.sysmergearticles sa
JOIN dbo.sysmergepublications sp on sa.pubid = sp.pubid
LEFT OUTER JOIN dbo.sysmergesubscriptions ss on ss.pubid = sa.pubid
JOIN sys.objects o ON sa.objid = o.object_id;