How to setup Transactional Replication to publish only filtered data on the Subscriber?
**Configuration/Setup:** PUBLISHER: SQL Server at Branch premises having only specific branch data (Ex: Branch ID: 135) SUBSCRIBER: SQL Server (live at data center) used by application having data for all the Branches. Publisher needs to send the data changes (only for Branch ID: 135) from Local Branch to Subscriber (data center SQL Server). Subscriber must store the recent data for Branch ID 135 received from Publisher. Subscriber should not loose any other branch data from the live tables I have configured Transactional Replication to replicate the changes performed on the Transaction tables on the Publisher which stores data only for 1 Branch with Branch_ID = 135. These changes from the Publisher are replicated to my Subscriber which is our Live DB Server storing all the Branch Data. Distributor is on the Publisher SQL Server. I have filtered the publication tables/articles to filter data using Branch_ID = 135 **Issue/Problem:** Subscriber data gets fully refreshed and all the branch id data gets deleted. The Subsriber remains with data only for Branch ID = 135, which is replicated from the Publisher. All other data is lost from the Subscriber SQL Server. I am performing this operation on a test Server. Can you please help me out where am I missing my Replication configuration? **OBJECTIVE** The SUBSCRIBER must store ALL the Branch Data without getting deleted during the Replication, while replicating the updates for branch id = 135 received from the Publisher. The Subscriber must get the latest updated data or snapshot from the Publisher only for Branch ID: 135. Please help me how can I achieve this objective performing Transactional Replication. Is there any other way to perform this operation. Thanks in advance for your assistance!!
First, the subscriber cannot be the master. The publisher is always the master. This is where all data manipulation takes place. If you were to delete a record at the subscriber, and the record is later manipulated at the publisher there will be an error because it can't find the record. Try using 2 publications with 2 different filters. One filter would be with no delete, pushing the 135 data. The other would be to push all other data, excluding 125. BUT - remember, whenever you do a snapshot or reinitialize the subscription that it will make the subscriber look like the publisher unless on reinitialization you tell it that there is already data in the subscriber table and to not do anything. This means that you would have to manually update the subscriber table with the data and turn on the subscription. One other thing that you could try would be to place a trigger on the table on the subscriber with your rules built in, and have a regular publication made, pushing everything over. Then have the trigger manage the data. This could cause a multitude of other problems further down the line, especially blocking and possibly deadlocking. Just depends upon how exotic you wish to be.
The problem is with the snapshot. A snapshot does exactly as the name implies, takes a snapshot of the table on the publisher and makes the subscriber table look like it. I would stay strictly with transactional replication in this scenario, working with only the records you wish in the filter.
The subscriber does not do anything except receive the data unless you have a SP or trigger on the table to do something. On the publication side you decide what to happen at the subscriber. In this case, the data is modified (Insert, update, delete) at the publisher. If it meets the filter or other rules, it is sent to the subscriber(s). If you do not want data deleted (transactional replication) there is a place at the bottom of the properties where you can describe the actions taken for Insert, Updates and Deletes. If you do not want to delete a record then select the Do Not Replicate Deletes, but this is a global setting for the package. Hope this helps.