How to apply the snapshot manually to the subscrib

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Replication (2005)

How to apply the snapshot manually to the subscrib

Author

Topic

m_z_iqbal
Starting Member

28 Posts

Posted - 2009-04-01 : 03:19:56

Hi,

i have a more than 900MB of Database size, and i need to synchronize two database servers over the internet using Merge Replication. when i try to send the snapshot over internet, it is failing to initialize.
Is there way, that i could take the backup of publisher DB , and restore it on the Subscriber Server, and then start synchronizing, instead of sending the snapshot again ?

any help will be appreciated ?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-04-01 : 04:00:01

Yes, if you do it at a time of minimal writing to the database, you can backup and restore, then when you set up replication, there is an option to tell it the subscriber already has the data.

Look here: http://technet.microsoft.com/en-us/library/ms152560.aspx

under the heading: Publication Database: Read-Only Transactional Replication

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-01 : 10:32:54

yes, this is what we use for bigger database. This is what you need:

http://technet.microsoft.com/en-us/library/ms151705.aspx

m_z_iqbal
Starting Member

28 Posts

Posted - 2009-04-04 : 07:37:02

i had done the following to setup the merge replication with backup & restore

1) set up merge publication
2) then, back up the publication database
3) then transfered the backup to the subscriber database, and restore there
4) setup a subscription between these two databases/servers but specify NOT to initialize immediately

Everything was looking "successful" at this point.

but, when I 'View the Synchronization Status', I get this message:

"The server 'SubscriberMachineName' is not a Subscriber. (.Net SqlClient Data Provider)"

When I view the Replication Monitor, I see the following the details:

Command attempted:
{call sp_MSsetconflicttable (N'Bg_Awards', N'MSmerge_conflict_PUBLISH db_Mrg - Merge_Bg_Awards', N'CAT', N'db_Mrg', N'PUBLISH db_Mrg - Merge')}

Error messages:
Incorrect syntax near 'award_id'. (Source: MSSQLServer, Error number: 102)
Get help: http://help/102
The merge process was unable to deliver the snapshot to the Subscriber. If using Web synchronization, the merge process may have been unable to create or write to the message file. When troubleshooting, restart the synchronization with verbose history logging and specify an output file to which to write. (Source: MSSQL_REPL, Error number: MSSQL_REPL-2147201001)
Get help: http://help/MSSQL_REPL-2147201001

I was thinking that backup & restore was basically a manual way to completely Merge Replication for the first initial stage, but i am thinking , that i am missing something or it can't be done with backup/restore strategy.

Can anyone help me out regarding this ?

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-04-04 : 11:10:57

Donot check snapshot option while setting up publisher.
Change to publisher properties - initialize from backup to true.
then backup database and restore in subscriber with REcovery mode.
Use sp_addsubscriber with commands. Make sure you are using Real Server name(not I.P) for subscribers and it can ping publishers as well.Services accounts should be running in domain account.

m_z_iqbal
Starting Member

28 Posts

Posted - 2009-04-05 : 06:26:37

Change to publisher properties - initialize from backup to true.

If i am not wrong then this option is available in Transactions Replication not in Merge Replication

I am doing Merge Replication,
How this can be done in Merge Replication (using Backup/Restore)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources