pros and cons of using transaction replication doing initialization from database backups

I am using transaction replication between a transaction and reporting database server. When I use a snapshot to initialize my subscribers, I currently get a lot of deadlocks during the snapshot creation. I am considering using a database backup instead. Can anyone tells me how to reduce the table locks that I am getting during snapshot creation or advice on using database backups?

Hi,

Transactional replication provides several different sync method to help alleviate locking issue on publication DB. You can take a look at @.sync_method in sp_addpublication (http://msdn2.microsoft.com/en-us/library/ms188738.aspx).

You can try with concurrent/concurrent_c. And if you are using SQL 2005 SP2 and you are using enterprise edition, you can also use "database snapshot/database snapshot character". Snapshot agent creates a DB snapshot on the publication DB and then generates replication snapshot based on the DB snapshot. So it significantly reduces the possiblities of deadlocking on the publication DB.

Peng

Propagate NEW Table to Anon Merge Replicas

How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
Add, Update, Delete Triggers?
I created a new Table (by running a SQL Script created on a Developer
machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to Subscribers,
OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
dbo.upd_ ... , dbo.del_ ...
What did I miss, and Where? when I created the Prototype?
Aubrey Kelley
It sounds like merge replication has not placed all of the object it
requires on the subscriber. Are all objects owned by dbo? There were some
problems on pre SP 4 service packs with this.
How did you create the new table and add it to the merge publication? How
did you deploy it to your subscribers?
The best way to fix this is to reinitialize, generate a new snapshot and
send it to the subscribers. Any other approach may fix this particular
problem but there could be further missing objects and the merge replication
metadata may be in an inconsistent state.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Aubrey" <miscuates@.online.nospam> wrote in message
news:B1E8B402-7B5B-4DD3-8389-9BBC7D99F109@.microsoft.com...
> How do I get a New Table to propagate to Anonymous Merge Subscribers, WITH
> Add, Update, Delete Triggers?
> I created a new Table (by running a SQL Script created on a Developer
> machine) on a Remote SBS 2003 Server (SQL 2K). It Propagated to
> Subscribers,
> OK. But it is useless, because is missing the Triggers: dbo.ins_ ... ,
> dbo.upd_ ... , dbo.del_ ...
> What did I miss, and Where? when I created the Prototype?
> --
> Aubrey Kelley

Programming continuous merge replication

Hello,
This projects needs to setup automatically a few MSDE subscribers to a main
Distributor/Publisher.
I am using the ActiveX SQL Merge object to set up the merge replication, but
there are 3 problems:
1) There is not a property to set Continuous mode
2) If I set the ExchangeType to upload, when I browse for the properties
using Enterprise Manager, the “ExchangeType 1” is not present in the Agent
step
3) (Project killer) Assume I manage to set Continuous… After running the
SqlMerge object, the status shows “initializing in progress” but it never
finishes and I have to start again the agent from Enterprise Manager… after
selecting Start (from Enterprise Manager) the synchronization runs as
expected… but I cannot start it by program.
Here are the properties and methods that I am calling after everything is
already registered and ready:
//set up the publisher
m_oSqlMerge.Publisher = m_sDistServerName;
m_oSqlMerge.PublisherSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
m_oSqlMerge.PublisherDatabase = m_sDbName;
m_oSqlMerge.PublisherPassword = m_sDistPassword;
m_oSqlMerge.PublisherLogin = "sa";
m_oSqlMerge.Publication = m_sDbName;
//set up the distributor
m_oSqlMerge.Distributor = m_sDistServerName;
m_oSqlMerge.DistributorSecurityMode =
SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
//set up the subscriber
m_oSqlMerge.Subscriber = oSub.ServerName;
m_oSqlMerge.SubscriberDatabase = oSub.DatabaseName;
m_oSqlMerge.SubscriberDatasourceType =
SQLMERGXLib.DATASOURCE_TYPE.SQL_SERVER;
m_oSqlMerge.SubscriberSecurityMode =
SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
m_oSqlMerge.SubscriberLogin = "sa";
m_oSqlMerge.SubscriberPassword = sPassword;
//set up the subscription
m_oSqlMerge.SubscriptionType = SQLMERGXLib.SUBSCRIPTION_TYPE.PULL;
m_oSqlMerge.SynchronizationType =
SQLMERGXLib.SYNCHRONIZATION_TYPE.AUTOMATIC;
m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD;
ProgressValue = 0;
//Initialize
m_oSqlMerge.Initialize();
m_oSqlMerge.Run();
//m_oSqlMerge.Terminate(); //No need to terminate if continuous
When you create a subscription to a merge publication using Enterprise
Manager, a SQL Server Agent job is created that, when run, synchronizes the
subscription. This job is used whenever you synchronize the subscription from
Enterprise Manager. However, when you start the synchronization
programmatically this agent job is not used and the Merge Agent is started
with the properties you set for the object instance. This is why setting
m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD doesn’t affect
what you see in Enterprise Manager (which is the agent job). There is no
continuous mode for the Merge Agent, however, you can easily use a timer
control or program a method that calls the Run method at regular intervals to
synchronize the subscription. You should do some inserts at the Subscriber
and run the Merge Agent using your code to see if they make it to the
Publisher. Handling the Status event is a good way to get all of the agent
status and messages. For a good example of how to implement the Status event,
see http://www.winnetmag.com/Article/Art...79/39079.html. (Note you
need to be on at least SP3.)
Best Wishes,
Glenn Gailey [MS]
SQL Server User Education
"This posting is provided "AS IS" with no warranties, and confers no rights"
"uk" wrote:

> Hello,
> This projects needs to setup automatically a few MSDE subscribers to a main
> Distributor/Publisher.
> I am using the ActiveX SQL Merge object to set up the merge replication, but
> there are 3 problems:
> 1) There is not a property to set Continuous mode
> 2) If I set the ExchangeType to upload, when I browse for the properties
> using Enterprise Manager, the “ExchangeType 1” is not present in the Agent
> step
> 3) (Project killer) Assume I manage to set Continuous… After running the
> SqlMerge object, the status shows “initializing in progress” but it never
> finishes and I have to start again the agent from Enterprise Manager… after
> selecting Start (from Enterprise Manager) the synchronization runs as
> expected… but I cannot start it by program.
> Here are the properties and methods that I am calling after everything is
> already registered and ready:
> //set up the publisher
> m_oSqlMerge.Publisher = m_sDistServerName;
> m_oSqlMerge.PublisherSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
> m_oSqlMerge.PublisherDatabase = m_sDbName;
> m_oSqlMerge.PublisherPassword = m_sDistPassword;
> m_oSqlMerge.PublisherLogin = "sa";
> m_oSqlMerge.Publication = m_sDbName;
> //set up the distributor
> m_oSqlMerge.Distributor = m_sDistServerName;
> m_oSqlMerge.DistributorSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.NT_AUTHENTICATION;
>
> //set up the subscriber
> m_oSqlMerge.Subscriber = oSub.ServerName;
> m_oSqlMerge.SubscriberDatabase = oSub.DatabaseName;
> m_oSqlMerge.SubscriberDatasourceType =
> SQLMERGXLib.DATASOURCE_TYPE.SQL_SERVER;
> m_oSqlMerge.SubscriberSecurityMode =
> SQLMERGXLib.SECURITY_TYPE.DB_AUTHENTICATION;
> m_oSqlMerge.SubscriberLogin = "sa";
> m_oSqlMerge.SubscriberPassword = sPassword;
> //set up the subscription
> m_oSqlMerge.SubscriptionType = SQLMERGXLib.SUBSCRIPTION_TYPE.PULL;
> m_oSqlMerge.SynchronizationType =
> SQLMERGXLib.SYNCHRONIZATION_TYPE.AUTOMATIC;
> m_oSqlMerge.ExchangeType = SQLMERGXLib.EXCHANGE_TYPE.UPLOAD;
> ProgressValue = 0;
> //Initialize
> m_oSqlMerge.Initialize();
> m_oSqlMerge.Run();
> //m_oSqlMerge.Terminate(); //No need to terminate if continuous
>