2PC and Replication 2005

We are having a symmetry issue between rep over a LAN and over the WAN. I want to know the roll of the MSDTC and possible two-phase commits (2PC) that may be occurring with certain subscription types. It didn't seem the replication stream scales well over the WAN and 2PC are not the thing to do over longer distances due to the excessive amount of handshaking needed in 2PCs (reflectivity issues).

If 2PCs and the MSDTC are used, are certain setups or types of replication in SQL 2005 available that would be more efficient over a long distance WAN? Can 2PCs be avoided?

The setup that was built with the issues is not a typically built replication topology (by my book). By my own rule, I never place the distributor on the publisher, ever. Apparently, when setup this way, I didn't have any issue replicating over a similar WAN. Does the distributor work differently when running on a dedicated server?

What's the best replication topology when attempting to replicate over a long distance WAN?

Thanks.

Transactional replication works well over a wan. If you are replicating DML which modifies large numbers of rows consider replicating the execution of stored procedures.

Immediate updating does use MSDTC when transactions originate on the subscriber. Other replication types do not.

2PC and Replication 2005

We are having a symmetry issue between rep over a LAN and over the WAN. I want to know the roll of the MSDTC and possible two-phase commits (2PC) that may be occurring with certain subscription types. It didn't seem the replication stream scales well over the WAN and 2PC are not the thing to do over longer distances due to the excessive amount of handshaking needed in 2PCs (reflectivity issues).

If 2PCs and the MSDTC are used, are certain setups or types of replication in SQL 2005 available that would be more efficient over a long distance WAN? Can 2PCs be avoided?

The setup that was built with the issues is not a typically built replication topology (by my book). By my own rule, I never place the distributor on the publisher, ever. Apparently, when setup this way, I didn't have any issue replicating over a similar WAN. Does the distributor work differently when running on a dedicated server?

What's the best replication topology when attempting to replicate over a long distance WAN?

Thanks.

Transactional replication works well over a wan. If you are replicating DML which modifies large numbers of rows consider replicating the execution of stored procedures.

Immediate updating does use MSDTC when transactions originate on the subscriber. Other replication types do not.

2005 Replication with SCHEMABINDING Subscriber Views

Hi Folks

Is there an easy way around this ?

One Way Transactional Rep Subscriber needs SCHEMABINDING on the majority of their Views (require View Indexes) which read from Replicated Tables.

Main table has 4 Million Rows

ReInitialize Subscription Errors with Cannot Drop Table because it is being referenced By Object .... [schemaBound View]

GWmmmm - not much interest in this thread :eek:

well for info I'm trying to backup any schemabound objects with the following code (Showing the OBJECT_DEFINITION Usage).


ALTER PROCEDURE [dbo].[usp_SchemaBoundObjects_Backup]
AS
SET NOCOUNT ON
IF(SELECT OBJECT_ID('SchemaBoundObjects')) IS NULL
BEGIN
CREATE TABLE SchemaBoundObjects
(ObjName SysName,ObjType VarChar(50),ObjText nVarChar(4000))
END
ELSE
BEGIN
TRUNCATE TABLE SchemaBoundObjects
END
INSERT INTO SchemaBoundObjects
SELECT DISTINCT OBJECT_NAME(o.object_id) ObjName
,o.type_desc ObjType
,OBJECT_DEFINITION (o.object_id) ObjText
FROM sys.sql_dependenciesAS d
JOIN sys.objectsAS o
ON d.object_id= o.object_id
AND o.type IN('FN','IF','TF','V','P')
WHERE d.class = 1
INSERT INTO SchemaBoundObjects

-- Does'nt work
SELECT si.name,'INDEX',OBJECT_DEFINITION (OBJECT_ID(si.name)) ObjText
FROM sys.indexes si
JOIN sys.views sv
ON sv.object_id= si.object_id
WHERE sv.name IN (SELECT ObjName FROM SchemaBoundObjects)


Programatically ReInitialize Subscription, spike the Distributor - Then Recreate the Objects (which I have the Code for).

Now if I can just sort out the sodding View Indexes which don't work Grrr :(

C'mon folks Join in anytime :p

GW