When adding articles to replication one of the most important things to consider are its properties. Get these wrong and your replication may not work as expected, but get them right and you’ll be plain sailing.
When adding articles you will see the following form:
Click on Tables and then Article Properties and you’ll see the following options:
Click on Stored Procedure and then Article Properties and you’ll see:
This works for all Article Types.
As you can see, there are 2 options and they do exactly as you would expect. One allows you to set generic options for all of your articles within that category, and the other is article specific. You’ll need a mixture of these to get the best out of your replication.
The options presented under the heading of “Highlighted” are a complete list and “All” are a subset, therefore I will go through the key options within the “Highlighted” banner for both tables and stored procedures respectively.
First I’ll do tables, therefore select a table and click “Set Properties of Highlighted Table Article”…
I’ve expanded my form to show all the options within the first section. These are the defaults that each table has as standard.
These are all pretty self-explanatory and should be selected or ignored based on your requirements. Generally I will set the top 7 options to true for all tables as I want all constraints, keys, and indexes to be copied from my published database.
However if, for example, you have a master database which is used for data imports and the subscribers are used for reporting then you would want different indexes on each set of tables, therefore you would not want replication to copy your nonclustered indexes etc.
If you re-initialise replication and you do not copy indexes to the subscriber then it will wipe all existing indexes (due to it performing a drop and re-create of the tables). Therefore you either have to script all your indexes and have them placed in a .sql file for replication to apply, or you can use a handy hint…
On your published database, create all subscriber indexes but then disable them. This takes up no room (well, apart from the few bytes for the definition itself), and yet when SQL re-initialises it will script the indexes as enabled… therefore ensuring you always have the correct indexes on your subscriber without having extra scripts and performing additional checks.
The next section to look at is Destination Object…
This is where we find out how the object on the subscriber will be created. There are numerous options here that could be relevant to your environment. For example, you can change the name of the destination object. Therefore you could be replicating a table called “salesImport” but on your subscriber you can change this to read “salesFigures” so that it’s more meaningful in a reporting environment. Generally I don’t touch this though.
It also explains how it will create the object on the subscriber. The default is shown here in that it will drop the object on the subscriber and then re-create the new one from scratch.
If you know that the table structure is the same and you don’t wish to drop and re-create a table, then you can select a different object and maybe just have the data deleted and re-inserted instead.
The other options are all in regard to the datatypes you wish to see on your subscriber and whether you want anything converting to an older or different format than on the publisher. These tend to remain unchanged in my publications.
Lastly we have Identity Range Management:
This is incredibly important if you have any tables that have an IDENTITY column in them. To cover this I have had to create an entire webpage on its own. You can find it here.
There are considerably fewer options for a stored procedure as you can see here:
Basically the only things we are interested in are the destination object name, the destination object owner, and how it is created. As with tables you can request that an existing object is dropped and then re-created, or that it does nothing if the name already exists.