June 25, 2009 at 3:32 pm
I'd like to setup transactional replication of a large database and use the initialize with backup option on the subscriber so that I can use a restore to build the subscriber instead of a snapshot.
The restore takes a while, so what I'd really like is to do is create the publication, restore a backup of the publisher to the subscriber but use the no recovery option to keep it pending, do a log backup of the publisher, create the subscriber via a sp_addsubscription using the sync_type initialize with backup and then apply the last publisher log to the subscriber with recovery. Does this work with non native SQL Server backup software or do I need to use SQL Server backups and restores as the synchronization process seems to look at LSN in the backup file (according to BOL if I'm reading it correctly).
Thanks,
Jen
July 2, 2009 at 1:28 pm
In case anyone else is trying to get this working with third party backup software, here's how we got the above to work. Our backup software has a conversion routine that converts the vendors' backup to a sql server backup. So I did the full database backup and restore using the vendor's tools, set up the publication, did the log backup using the vendor's tools, converted the transaction log backup to a sql server backup, applied the converted transaction log backup to the subscriber with recovery and added the subscription. Surprisingly this works.
November 17, 2009 at 2:24 pm
Hi Jen,
I have similar issues on Backup and Recovery of Snapshot and Transactional Replication.
The SQL Servers are located in non-trusted environment
-SQL Server 2000 as publishers
-SQL Server 2008 as a remote distributor
-SQL Server 2008 as subscribers
I wanted to use the backup from publisher database to replace the intial snapshot due to large database size.
Can you give me some light how to setup in step by step?
Thanks,
TJ
November 17, 2009 at 3:07 pm
Have you already configured your distributor for distribution of data by this publisher and configured distribution on the publisher with the appropriate windows accounts? This basically sets up the distribution database, linked servers, security and I think 2 agent jobs before you actually define what you'll publish, synchronize your subscriber and start publication. If you can get this far, I can help with the publish, synchronize and start publication steps.
Thanks,
Jen
November 17, 2009 at 3:43 pm
I all ready configured my remote distributor with SQL credential on my production environment.
Now, I am testing the database replication on my local and my virtual server environment.
Questions:
1. How did you apply the backup? after you all ready setup the replication or before?
November 17, 2009 at 4:05 pm
Since our backup restore takes a fair amount of time, I did a restore but left the database in norecovery mode and then started the publication steps. We couldn't stop the publisher database to do a simple restore either. So the following worked for us:
1. enable the primary database for publication
(exec sp_replicationdboption ...)
2. restored primary database backup to subscriber with norecovery option
3. added publication to publisher
(exec sp_addlogreader_agent ...
exec sp_addpublication ...
exec sp_addarticle ....)
4. disabled the distribution server "clean up: distribution" sql agent job
5. add a transaction on the publisher (we need to add a dummy transaction to be sure the log backup would have a synchronizing transaction)
6. do a log backup of the publisher and apply the log backup to the subscriber with recovery (we converted the vendor log back to a sql server native log backup for this step)
7. convert all the subscriber timestamp columns to varbinary
8. add the subscription to the publisher
(exec sp_addsubscription ...,
@sync_type = N'initialize with backup',
@backupdevicename=N'your last restored log filename' ...
9. check the distribution servers' new log reader and distribution jobs to see if they have an inprogress or error message in the job history
10. enable the clean up: distribution sql agent job
You may have other cleanup steps on your subscriber and have to consider distribution and subscriber restore scenarios, but if you can get the basic mechanics of the replication working, you'll be able to figure everything else out. Hope this helps,
Jen
November 17, 2009 at 4:57 pm
Hi Jen,
Many thanks for your advise and light up on these. I will try it out and update you shortly.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply