You are the database administrator of your company. You have two SQL Server 2008 servers in a peer-to-peertransactional replication topology. You are in the process of adding a new node to the peer-to-peer transactionalreplication. You want to ensure that all relevant transactions are replicated to the new node.
Which value should you configure for the @sync_type parameter of the sp_addsubscription system storedprocedure?
A.
initialize from lsn
B.
initialize with backup
C.
replication support only
D.
automatic
Explanation:
You should configure the initialize from lsn value for the @sync_type parameter of the sp_addsubscription system stored procedure. Peer-to-peer replication provides high-availability by maintaining copies of data onmultiple instances of SQL Server. Each server in a peer-to-peer replication is referred to as a node. After youhave configured a replication topology, you can add multiple nodes to it. To add a node to an existing peer-to-peer replication topology, you can use the
Publication page of the Configure Peer-to-Peer Topology Wizard .To open the Configure Peer-to-Peer Topology Wizard , you should open SQL Server Management Studio andexpand the Replication node. You should then expand the Local Publications node under the Replication node, right-click the publication to which you want to add a node, and select the Configure Peer-to-PeerTopology option. To ensure that all relevant transactions are replicated to the new node, you should configurethe initialize from lsn
value for the @sync_type parameter of the sp_addsubscription system storedprocedure. You should not configure the initialize with backup ,
replication support only , or automatic value for the @sync_type parameter. These values are not used to replicate all relevant transactions to a new node that isadded to a peer-to-peer transactional replication. The initialize with backup value obtains the schema and initialdata for published tables from a backup of the publication database. The
replication support only valueprovides automatic generation of article custom stored procedures at the Subscriber and triggers that supportupdating subscriptions. The
automatic value ensures that schema and initial data for published tables aretransferred to the Subscriber first.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 > Technical Reference > Replication StoredProcedures (Transact-SQL) > sp_addsubscription (Transact-SQL) MSDN > MSDN Library > Servers and Enterprise Development > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Replication > Development > Designing and Implementing:Walkthroughs > How to: Configure Peer-to-Peer Transactional Replication (Replication Transact-SQLProgramming)