SQL Homework – August 2022 – Replication part 1

Leave a comment

August 2, 2022 by Kenneth Fisher

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.

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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,704 other followers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: