Transactional Replication Without the Snapshot Agent

When we configuring subscriber in transnational replication it is initialised with snapshot is generated by Snapshot Agent and applied by the Distribution agent. In couple of scenarios like replicating large databases generating & applying Snapshot will time consuming process. Here we will "initialise subscription using backup". In this method we can initialise subscriber database quickly. 

In this method you need to restore the recent full backup taken after the publication was enabled with option of initialise replication with backup below are the steps to configure replication without snapshot using GUI 

Step 1: Configure Distribution 

• Start SSMS and connect to publisher server 
• Right click on Replication folder and select Configure Distribution .. as shown in below fig

• In configure Distribution wizard select the distribution server information & Click Next 

Provide the Snapshot folder and click on Next 

In Distribution Database properties page please provide and name and location of distribution database data & log files. Click on NEXT

• In Next page select the Publishers to use this distribution 

• In final screen of wizard check Configure distribution check box and click Next and in next screen click on as shown in below two screens 

                    

• it will create a distribution and enable the publisher for this distributer.

Step 2: Configure Publisher 

• Open SSMS and connect to publisher server 
• Expand the server, Navigate to Replication folder and expand in Object explorer and right click on Local Publisher and select New Publication ...

• in New Publication Wizard Select the database which you want to publish & click Next 

• Select the publication type as Transnational Publication & Click on Next

• Select the Articles (Tables & other objects) which you want to publish and also select columns to filter tables

• In Snapshot Agent wizard leave the check boxes un-ticked (In this you are configuring replication without snapshot) 

• In Agent Security Dialog box Click on Security Settings button to provide credentials to connect Snapshot agent and LogReader Agent 

• In below image i selected SQL Server Agent Service account but as per MS it is best practice it should have separate windows credentials  

• After provide appropriate credentials it looks like below image 

• Click on next. In next wizard Create the publication check box must need to select 

• Provide Publication name in Final Wizard. and Click on Finish

• It creates and Publication. 

• After publication created open properties of publication by right click on the publication 

• In publication properties window goto Subscriber Option 

• In Subscriber options screen Change "Allow initialization from backup files" value to "true" from false

Step 3: Backup Published Database

• In this table full backup of Published database 

• Right click on database goto Task and select Backup ...

• In Backup database wizard select the Database, Backup type to FULL & backup location in Destination part 

Step 4: Restore Published Database in Subscriber server

• Copy the backup file from Publisher server to Subscriber server and restore it in Subscriber server. Right click on Databases and select Restore Database ... 

• In Restore Database wizard select to restore database from device option and provide the backup file name along with location 

• Provide the database name and click on Restore option 

• It restores the database with given name in subscriber server 

Step 5: Add Subscriber information 

Connect to publisher server goto New Query and run below SQL Statement on Publisher Database with appropriate values 

sp_addsubscription 

@publication ='PublicationName', -- Provide Publisher name 

@subscriber='Subscriber Server', -- Provide Subscriber Server instance name 

@destination_db='TranRepl_WOSnapShotTest',  -- Provide Destination Server 

@sync_type = 'initialize with backup', -- With this it recognise that subscriber will initiate using backup file 

@backupdevicetype = 'disk', -- 

@backupdevicename = 'd:\TranRepl_WOSnapShotTest.bak' -- Provide backup file location 

go

http://sql2005ted.blogspot.in/2010/12/setting-up-transactional-replication.html