More DBA job interview questions and answers at
(Continued from previous question...)
A non-dbo owned table when merge replicated to the subscriber, is being owned by dbo. Is there a way to maintain table ownership in merge replication?
GUI permits to specify a destination table owner in case of transactional and snapshot publications. Through GUI we can not specify the destination table owner in case of merge replication.
Here is a workaround:
Create the preferred login and user on the subscribing SQL Server.
Before the merge agent runs for the first time, right click on the merge agent, select ‘Agent properties’. Go to ‘Steps’ tab. Double click on the ‘Run Agent’ step. In this dialog box you will see a ‘Command’ text box. Append the following to the command string.
-SubscriberLogin Destination_owner_name –SubscriberPassword Password –SubscriberSecurityMode 0
Run the merge agent. This should create the destination tables with the specified user as the owner.
When an UPDATE on the publisher gets replicated as a DELETE/INSERT pair instead of direct UPDATE?
An UPDATE gets replicated as a DELETE/INSERT pair when a user updates:
- any column which is a part of the primary key
- any column on which there is a filter clause
- any column on which there is a clustered index.
(Continued on next question...)