Initializing a Transactional Subscription Without a Snapshot

Question

Initializing a Transactional Subscription Without a Snapshot

Andris Marte

Mr or Mrs. 500

Points: 532
More actions
November 14, 2011 at 11:51 am

#247802

Hi all, I newbie.
I have the following stage.
I initializing a subscription with a backup using the storeprocedure "sp_addsubscription" with the following properties :
use 'DBPublicationXX'
go
sp_addsubscription
@publication = 'DBPublicationXX'
, @subscriber = 'MYPC\SQLSVRSTD2005'
, @destination_db = 'DBdestination'
, @sync_type = 'initialize with backup'
, @status = 'Active'
, @subscription_type = 'Push'
, @update_mode = 'Read Only'
, @backupdevicetype = 'Disk'
, @backupdevicename = 'C:\Documents and Settings\MYPC\Desktop\DBPublicationXX-Full.bak'
But i need now synchronize automatic all time from my distribution server to suscriptor server. This is possible?
Please help me.
Thanks a Millions.

Viewing 15 posts - 1 through 15 (of 19 total)

You must be logged in to reply to this topic. Login to reply

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 1

Hi Andris,

Can you clarify something for me? What do you mean by "But i need now synchronize automatic all time from my distribution server to suscriptor server. " ? I am not sure what you intent to do.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 2

Hi dear Roy,

Really, I need initialize a database with backup, and can be it!! and not yet able to update my subscriber with the new data that I have in the Distribuitor \ Publisher server.

I'm can update automatically the subscriber after I use initialize with backup?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 3

If it is a transactional replication and you have the option set Sync with back up (IsSyncWithBackup), the data will be replicated to your subscriber only when you do a transaction log backup.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 4

Thank your Roy, and yes I will use transactional replication, but then which would be the process if I have my backup strategy which make Sunday a full backup, a differential every 3 hours and a transaction log every 15 minutes.

I have to take that backups, sent to subscriber server and restore to the database subscriber?

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 5

Keep in mind that the data will be replicated only once every 15 minutes when the log back up is set for 15 minutes interval. I would recommend a smaller time frame for that.

Take a look at the help from MSDN to help with setting up replication using Back up.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 6

OK thanks Roy, you can say more or less any steps. with this environment.

I have to copy the files manually Transaction Logs to the subscriber server or using some store procedure is automatically applied to my subscriber database?

Please see attachment.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 7

I am not sure I understand. You just need to have a transaction log back up. You do not have to restore the log to your subscriber DB. When you run a transaction log back up on the publisher, at that time the log reader for the Replication will also run and move the changes to the Subscriber through your distributor.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 8

OK Thanks a Million Roy... your response save my life..

I Like this forum (www.sqlservercentral.com) and your mentors..:-):-D:-P;-):cool:

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 9

This is just replication part you are doing. But I would advice you to take regular back ups (Once a day) and transaction log back ups. Your back up is not good till you know for certain it is good. The only way to know if the back up is good is to restore it in another server. Also apply the transaction log.

You can automate this pretty easily by using the Maintenance plan option provided by SQL Server.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 10

Hi Roy here again.... lolz.

I did two tests:

Test # 1: Insert a new row at publish \ distribution server and the new row to WAS Sent successfully.

Test # 2: I Deleted a row in the subscriber DB server, and I Thought That must-have this row Appeared After an update of the subscription.

Please let me know how I can replace That line I removed the Subscriber and is on the distribution server.

Again Thanks a million!:-)

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 11

The best way is to insert back the row you deleted in the subscriber.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 12

Roy, but this opcion " insert back the row you deleted in the subscriber" is well use in a replication bidirectional ?

I only use replication Transaction ReadOnly.

Roy Ernest SSC-Dedicated Points: 38780 More actions · Answer 13

When you have a read only Transactional replication, you are not supposed to do any DML operation on the Subscriber.

From what I understood from your post, you deleted a row from the subscriber. After that in the publisher you did an update for the row you deleted from subscriber. Now your replication is RED. It is throwing error that it could not find the specified row.

The easiest way to fix this is to manually insert back the row you deleted. Once that is done, the next time it tries to synch the data, it will be able to update the row.

-Roy

Andris Marte Mr or Mrs. 500 Points: 532 More actions · Answer 14

OK Roy, i understand, thankyour again!!... you have all reason.