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.