4

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.

Improve this question

2 Answers 2

Reset to default
4

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.

Improve this answer
1
  • To add to that slightly, be careful about creating objects (i.e. views or functions) with the SCHEMABINDING option. This can prevent the subscriber table from being dropped and recreated when reinitializing the subscription. Your options there are to either drop and recreate the schema-bound objects, or tweak the publication options to reinitialize without dropping (which may not work if you're pushing schema changes to the subscriber).
    – db2
    Commented Jan 23, 2017 at 17:19
0

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.
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.