Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Background

I have two instances of SQL server running on two different machines:

One is a publisher, the other is a subscriber for a single (large) database. The replication type is transactional.

They both start off with the same snapshot of my replicated database.

I have a utility which runs on the publisher machine which executes the SQL commands against it (all are inserts, updates, deletes). These changes are then pushed out to the subscriber.

I want to know if I am able to TRUNCATE the data in the publisher database so that it has a lower overhead (all I want is the replication ability, I couldn't care less about the actual data in my replication publisher as long as it makes it to the subscribers).

If I do the above and also modify my utility which executes commands to turn INSERTS and UPDATES into a MERGE command, will the data be properly replicated to all subscribers?


Example scenario:

The original command that my utility will execute:

UPDATE calendar SET active = 0 WHERE id = 1932

  1. The publisher database doesn't have a calendar record with id of 1932, because I truncated it, so an update will affect zero records.

  2. But the subscriber database DOES have a calendar record with id 1932.

  3. If my utility translates the UPDATE above into a MERGE, will the change be pushed out to the subscriber database even though it executes as an INSERT on the publisher, but an UPDATE on the subscriber?


Thanks in advance.

share|improve this question

1 Answer 1

up vote 4 down vote accepted

If you truncated the table on the publisher the record in the transaction log would be an insert, and therefor an insert would be executed against the subscriber.

Now what you can do is modify the stored procedures that replication uses at the subscriber so that when the insert procedure fires it first looks to see if the record exists and if it does it does an update, and if it doesn't it does an insert.

share|improve this answer
    
Thanks. That makes a lot of sense. In your opinion, is this approach worth it (modifying the subscriber's stored procedures)? Or should I just eat the overhead of maintaining the large database on my publisher instance? –  Ryan Weir Sep 29 '11 at 20:46
1  
How large is large? –  mrdenny Sep 29 '11 at 22:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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