You are the database administrator of your company. You have configured merge replication on an instance of SQL Server 2008.
You configure the Publisher and Distributor for error reporting and monitoring purposes.
You create a publicationwith the default retention period. Users want to be able to subscribe to the publication for a longer period of time.
You want to modify the expiration period for the subscription to the publication.
Which system stored procedure should you use?
A.
sp_changesubscription
B.
sp_changemergepublication
C.
sp_changesubscriptiondtsinfo
D.
sp_change_subscription_properties
Explanation:
You should use the sp_changemergepublication system stored procedure. A subscription is a request to obtaina copy of the data and database objects in a publication. While creating a publication, you can specify theretention period for subscriptions. The retention period or the expiration period specifies the period of time afterwhich the subscription is expired and removed. When a subscription to a merge replication is expired, it must bereinitialized because its metadata is removed when the subscription expires. When a subscription to a transactionreplication is expired, it must be re-created and synchronized because this type of subscription is dropped by the
Expired subscription clean up job on the Publisher. To change the expiration period for a subscription to amerge replication, you should execute the
sp_changemergepublication system stored procedure specifying retention for the @property parameter and the desired number of days for the new subscription expiration periodfor the @value parameter. You should not use the sp_changesubscription , sp_changesubscriptiondtsinfo , or sp_change_subscription_properties
system stored procedures because none of these stored procedures canbe used to modify the expiration period for the subscription to a merge replication. The
sp_changesubscription system stored procedure modifies the properties of a snapshot or transactional push subscription or a pullsubscription that is involved in queued updating transactional replication. The sp_changesubscriptiondtsinfo system stored procedure modifies the Data Transformation Services (DTS) package properties of a subscription.The
sp_change_subscription_properties system stored procedure updates information for pull subscriptions.Objective:
Implementing High AvailabilitySub-Objective:
Implement replication.References:
MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Replication > Development > Designing and Implementing:How-to Topics > Creating, Modifying, and Deleting Publications and Articles > How to: Set the Expiration Periodfor Subscriptions (Replication Transact-SQL Programming) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Replication > Development > Designing and Implementing >Subscribing to Publications > Subscription Expiration and Deactivation