Configuring SQL server replication isn’t something I do every day, so I thought I might enjoy some breadcrumbs to follow the next time I travel down this path.

My client needed to have a number of tables and views replicated to a reporting server. The reporting server needed near real-time data, as small a deployment downtime window as possible, and minimal regular performance degradation.

For these reasons I selected MS SQL server’s transactional replication. This would provide the near real-time data. With a few configuration choices it has a small deployment downtime window. Finally performance degradation is manageable, with few locks, and plenty of knobs to adjust to fix any issues.

I had the replication components installed on sql servers during day.
https://msdn.microsoft.com/en-us/library/ms143677.aspx

Then I created destination database in subscribing server. I gave it the same name as the publisher so existing scripts should work without many modifications.

Next I created new publication using a deprecated table. I choose the publishing server to act as the distributor. In the future, a separate server can be designated as the distributor if additional publications degrade performance. There is an option to use the SQL server agent’s security settings, but it is not recommended. I ran the snapshot immediately because I wanted to see any potential issues.
https://msdn.microsoft.com/en-us/library/ms151160.aspx

I immediately discovered a wrinkle. The compatibility levels were not the same, since the existing database had been moved from older versions of SQL server. We could have lowered the compatibility level on the reporting server to match, but the error messages didn’t seem to indicate that would have solved the problem. Our errors were around datatypes which were not supported in older versions. For this reason, and because of the possible performance improvements with updated compatibility levels, we moved forward with updating the publisher database.

In order to discover any issues with upgrading the compatibility level, we did several things. First, we tried to use SQL upgrade advisor, but could not get it to work with the already upgraded server.
https://msdn.microsoft.com/en-us/library/ms144256(v=sql.120).aspx
Second, we went through major issues on compatibility level checklist, spot checking the databse for potential issues.
https://msdn.microsoft.com/en-us/library/bb510680.aspx
Finally, we updated compatibility level in test environment. After several hours of running at the new compatibility level, we then updated the compatibility level in production after hours.

Returning to replication, we created a new subscription on the reporting server.
https://msdn.microsoft.com/en-us/library/ms152566.aspx
We exclusively chose push subscriptions because of reported lock behavior.
http://dba.stackexchange.com/questions/73629/how-to-generate-replication-snapshot-without-locking-tables
Pull subscription reduces demand on publisher, so we’ll switch to using it later if performance requirements demand.

A linked server had to be added on the reporting server for a view, since all the components of a view must be available for replication to occur.

After all of these changes, the replication monitor showed success, the event viewer showed no additional errors, and the databases were populated on the reporting server.

Even after all the compatibility level checks, we still encountered timeout issues later that week that appeared to be compatibility level related. New indexes had to be created to alleviate these issues. Also, additional tables have been identified that will have to replicated for reporting, which should also continue to reduce load on the server.

If you have any additions, thoughts, or comments about SQL replication or updating compatibility levels, please add them below. I’d love to hear from you.