SQL Server Replication – Can destination db have stored procedures that source db lacks?

Posted on

Question :

I’m a developer with a very basic question, but I can’t see anything conclusive on MSDN. I’m developing a web application that uses a SQL server 2008 R2 back end. According to this MSDN page, SQL replication can be set up to replicate only certain database objects.

If stored procedures, functions, etc. are created on the destination database (the subscriber in replication parlance) will they survive future replications from the source database? I only need the data from the source system, so the replication need not publish anything but tables.

Answer :

Yes, absolutely.

You would typically use transactional replication to move data between the publisher and subscriber(s) (although you can include other objects as well). Your subscriber (destination) database is fully writeable, and you can create stored procedures there, add indexes to replicated tables, and even create entirely new tables.

Be aware, you can also delete data from the replicated tables on the subscriber, which could lead to replication breaking and you having to re-snapshot.

Yes, it is very common (and sometimes very useful) to have different stuff in the subscriber database than the publisher.

To be clear, though, SQL replication is normally used to only push data (contents of tables and/or views), not to push functions and stored procedures.

I’m not even sure it’s possible to use Replication to keep functions/stored procedures in sync between databases, you’d probably have to use database-level triggers or something, or just manually script them out and create them on the other side.

A few tips:

  1. You can create different indexes on the destination tables, if necessary (this is very common when the subscriber is used for reporting). These will be dropped if replication is ever re-initialized, though, so you’ll need to script them out beforehand.
  2. Don’t ever try to change columns or structure of a replicated destination table, you’ll break things. If you need extra columns, make a new table with a 1-1 relationship. If you need different data types, fewer columns, or other changes, make a view.
  3. If you need to change the data in the destination tables, and views aren’t cutting it, then make a scheduled process that copies/transforms the data into other tables in the destination database.

Leave a Reply

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