More DBA job interview questions and answers at
http://dba.fyicenter.com/Interview-Questions/
(Continued from previous question...)
How to add new articles to existing publications and make sure the subscribers receive the new article?
Enterprise Manager will not let you add articles to publications for which subscriptions exist. So, you have to do it the hard way, using replication system stored procedures.
To add an article to an existing publication, run sp_addarticle on the publishing database. Here's an example:
EXEC sp_addarticle
@publication = N'PublicationONE',
@article = N'repl_test2',
@source_owner = N'dbo',
@source_object = N'repl_test2',
@destination_table = N'repl_test2',
@type = N'logbased',
@creation_script = null, @description = null,
@pre_creation_cmd = N'drop',
@schema_option = 0x0000000000000073,
@status = 16,
@vertical_partition = N'false',
@ins_cmd = N'CALL sp_MSins_repl_test2',
@del_cmd = N'CALL sp_MSdel_repl_test2',
@upd_cmd = N'MCALL sp_MSupd_repl_test2',
@filter = null,
@sync_object = null
GO
Once, the article is added, you have to make sure the existing subscribers to this publication receive it.
Here's what you should do if you have PUSH subscriptions:
Execute sp_addsubscription as shown in the following example on the publishing database:
exec sp_addsubscription
@publication = N'PublicationONE', @article = N'repl_test2',
@subscriber = N'MITRA',
@destination_db = N'Northwind',
@sync_type = N'automatic',
@update_mode = N'read only'
GO
Here's what you should do if you have PULL subscriptions:
Execute sp_refreshsubscriptions on the publishing database as shown in the following example:
sp_refreshsubscriptions 'publicationone'
GO
(Continued on next question...)
