Pitfalls of SQL Server Replication

If you are planning Replication for one of your SQL Server databases there are some pitfalls you will encounter and need to solve. There are some little things you need to take care of, which can be hard to find since some error messages SQL Server gives maybe describe the error but don’t help at all in finding the source or how to resolve it.

1. Inform yourself about replication.
Before you even start planning Replication read up on the principal concepts: Snapshot replication, Transactional Replication, Merge Replication. How they work. What resources they need.

2. Plan your strategy for identity columns.
You need to plan your Replication especially careful if you plan to let users update the replicated data. Then you need to take special care how both databases – master data and replicated data – handle identity columns.

3. Only replicate the data you really need replicated.
Don’t just replicate your whole database. This might seem the easiest solution, especially if your database is small. But databases tend to grow, and what might be a small database in one customer instance, is a huge database at another customer’s instance. Always plan your databases (and therefore also replication) scalable, so they can become bigger and bigger without performance loss.
This said, replicate only the tables, views, functions and stored procedures you need. Replication adds additional stress to the system, so minimize that stress to the absolutely necessary.

4. Check also what the application needs.
Depending on how you plan to use the replicated data, what you actually need to replicate will differ a lot. It is not only necessary to check all the dependencies within the database and maybe even with other databases, but also to check the applications which you are planning to use on the replicated data.
For instance the database and the views, procedures and functions don’t show any indication of any dependency on a user table or a lookup table, but the application you are trying to run on this data absolutely needs this data to work properly (or at all).
So always check with application developers what data apart from the obvious database dependencies you also will need to replicate (or substitute in some other way).

5. Check your objects for their “replication-fitness”.
There are some (simple) prerequisites to take care of before replicating a table:

a. It needs a Primary Key (for transactional replication).
You might shrug at this point, but there are databases out there, especially legacy databases, where not every table has a primary key. If you need to replicate such a table, you need to create a primary key. In many cases you will find unique columns on such tables, either defined by a unique constraint or index or undefined but clearly detectable. Talk to the application developers, what might be unique, if adding a column might work, what consequences a primary key might have.
If there is no way of adding a Primary Key, you cannot replicate that table and you might need to find another way to copy the data – some automated ETL process maybe.
b. Take care of identity columns
When you add a table with an identity column to the replicated articles, SQL Server automatically alters the schema of that column to . This is sufficient if you don’t let the users update the replicated data (read-only database). If you do, you need to take special care of the identity columns (see above).
c. Check for triggers.
Everyone always forgets about triggers. An enabled trigger on the target database can fail the whole replication process. Check the triggers, see what they do, and if you need them on the target database. You even might need to alter them because you need only parts of it.
Tip: If you have a trigger that on insert into Table1 generates data for Table2, there are two ways of handling this:
i. Replicate Table1 and Table2, disable the trigger on the target database.
ii. Replicate only Table1 and leave the trigger enabled on the target database. So every replicated data in Table1 fires the trigger and fills Table2.
Method (i) let’s all the resources be used on the master database, first the trigger fires, then both tables need to be distributed. Method (ii) balances the load by letting the target database do some of the work. Chose for yourself, which to use. It just goes to show, there is no straight answer on what to do with triggers. Each one has to be checked on its own.

6. You need sufficient rights.
When setting up replication you need to have Windows users available that have sufficient rights. We are talking Windows/Domain users here, not SQL Server users:
a. Your user needs rights to set the executing user context of a service, create a Network share, maybe install components on the servers.
b. A user for each database that can reach the database snapshot share.
c. A user for each database under which the SQL Agent job can run.
d. A user with SQL Server sysadmin rights.

7. SQL Server Replication needs to be installed on both servers.
Don’t laugh! This happens more often than you think. Everything is prepared, all the objects and user rights checked and you want to start setting up the replication and the first message you get is

So get your Setup files ready and install SQL Server Replication. It is a straight forward process and shouldn’t present any problems, as long as you have sufficient rights to do so (and the Setup files handy).
Important: even though setup doesn’t tell you to, after the setup is finished you must restart SQL Agent service. Otherwise replication won’t work. And SQL Server will never tell you why, but throw cryptic error messages that some settings are missing or incorrect.

8. Run SQL Agent with a valid user context.
Database snapshots are distributed via a network share that both servers can access. Depending on your security configuration (and best practice implementations) you might need to set SQL Agent service to run under a specific user context.
Important: Don’t change the service’s user context in the computer management screen. Even though this screen automatically gives the user account the Logon as Service right, SQL Server and SQL Agent need some additional rights to work correctly.
Always use SQL Configuration Manager to change the service’s user context. This will set all the rights that SQL Server needs.

9. Replication needs TCP/IP name resolution.
The replication set up screen lets you connect to the distribution server via its IP address. No problem. You can set up everything, set the user rights and so on. Only when replication tries to start its SQL Agent jobs it will tell you, it can’t reach the distribution server because it has no name. So always use the server name instead of the IP address.
Tip: Depending on your network configuration and name resolution configuration it might not be possible to reach the server by name. In such a case you can edit the hosts file in %WINDOWSDIR%\system32\drivers\etc . Here enter the IP and the name and fool SQL Server into having a name to use.

10. The owner of the database has to be valid.
This issue only happens if you have an environment with, let’s say, test and production servers with different SQL users and databases get copied across different environments. It is a quite obvious and rare issue, but it happens, so I mention it.
Even though everything else works and the users seem to have sufficient rights, if the owner’s SID is not recognized, replication won’t work. This can be solved simply by resetting the ownership of the database:

alter authorization on database::test to mySAUser;

Even though the user name might be the same, the SID would be different and therefore we need to make this change.

One Response to Pitfalls of SQL Server Replication

Leave a Reply

Your email address will not be published. Required fields are marked *

%d bloggers like this: