ABNORMAL Transactional Replication behaviour

Actually i have set up a transactional replication with one publisher
and 1 subscriber (planning, then, to add other 6 subscribers later on).
The point is that after setting everything up, the replication works
really fine for some minutes and then just stops without any warning or
error!! The system is composed by this replication and some others
applications that writes on some tables on the subscriber. This app
starts writting data... everything works fine and then, BANG!, without
any reason data is not sent to the publisher anymore! SQL server doesn't
seems to notice that!
The replication has itself some filters and some tables have numeric
counters with identity YES (also the one that is written by the
application i mentioned before) . But, when i set up the publication i
assign a range to this counters and the replication blocks itself before
reaching the 1 % of the range i assing to this field!!
Anyone has an idea of what is happening?
Please... somebody help me!!
F.P.
what does sp_browsereplcmds reveal? Run this in the distribution database to
see if commands are pooling there.
If they aren't run sp_repltrans in your publication database.
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
"newsgroup" <fabio.pliger@.siavr.it> wrote in message
news:nVn4f.10530$65.287427@.twister1.libero.it...
> Actually i have set up a transactional replication with one publisher and
> 1 subscriber (planning, then, to add other 6 subscribers later on). The
> point is that after setting everything up, the replication works really
> fine for some minutes and then just stops without any warning or error!!
> The system is composed by this replication and some others applications
> that writes on some tables on the subscriber. This app starts writting
> data... everything works fine and then, BANG!, without any reason data is
> not sent to the publisher anymore! SQL server doesn't seems to notice
> that!
> The replication has itself some filters and some tables have numeric
> counters with identity YES (also the one that is written by the
> application i mentioned before) . But, when i set up the publication i
> assign a range to this counters and the replication blocks itself before
> reaching the 1 % of the range i assing to this field!!
> Anyone has an idea of what is happening?
> Please... somebody help me!!
> F.P.
|||Thanks Hilary for the advice, but sp_browsereplcmds reveals only that
the last command was the one for the last record send from the
subscriber to the published database(no cmds for the records in the
subscriber and not replicated to the publisher). Then i tried to run
sp_repltrans on my publication db but it returns me no results... All
the agents continue "working" without any error, but no more records are
replicated (neither from pubisher to subscriber or vice-versa). Wherelse
can i search for the transactions failed? I mean...
Any other idea? The main thing that upsets me is that i'm not able to
see what's going wrong!! I'll try changing some configurations... I'm
using a push subscription... is it better to use a pull one?
I really need some help...
thanks...
Hilary Cotter ha scritto:
> what does ? Run this in the distribution database to
> see if commands are pooling there.
> If they aren't run sp_repltrans in your publication database.
>

A urgent Replication Issue

A very urgent solution needed I have one main feed Server "A" replicating data to two Servers "B" & "C"

The Distribution agent profiles of Both "B" and "C" are as follows

"B" Distribution Agent Profile

-BcpBatchSize 100000 (default)
-CommitBatchSize 100 (default)
-CommitBatchThreshold 1000 (default)
-HistoryVerboseLevel 1 (default)
-KeepAliveMessageInterval 300 (default)
-LoginTimeOut 15 (default)
-MaxBcpThreads 1 (default)
-MaxDeliveredTransactions 0 (default)
-PollingInterval 10 (default)
-QueryTimeOut 2000
-SkipErrors 2601:2627:20598
-TransactionsPerHistory 100 (default)

"C" Distribution Agent Profile

-BcpBatchSize 100000 (default)
-CommitBatchSize 100 (default)
-CommitBatchThreshold 1000 (default)
-HistoryVerboseLevel 1 (default)
-KeepAliveMessageInterval 300 (default)
-LoginTimeOut 15 (default)
-MaxBcpThreads 1 (default)
-MaxDeliveredTransactions 0 (default)
-PollingInterval 10 (default)
-QueryTimeOut 300
-SkipErrors 2601:2627:20598
-TransactionsPerHistory 100 (default)

The latency for the server "B" is around 500000 & for the Server "C" is around 3000

Can u suggest anything wrong in the agent profile which can cause the Server LAtency to Shoot Up. what is the QueryTimeOut property about

thanks in advance

JAcx

I would take a look at the resources on Server B. You have a bottleneck somewhere over there which is causing things to move much more slowly than to other subscribers.|||Network connectivity between the 2?

A transport-level error has occurred when receiving results from S

I am setting up a replication of a database which has 364 tables. The size of
this database is close to 150 GB.
I have one server as publisher.
I have another server which i am using for distributor as well as
subscriber. Both servers are seperate physical servers.
I tried replicating 2 tables, I don;' see any issue. They replicate with no
issue. However, when I try to replicate all tables I get this error on
snapshot agent.
Please advise if any one has already seen this problem. My both servers are
on SQL 2005 SP2.
Error messages:
Message: A transport-level error has occurred when receiving results from
the server. (provider: TCP Provider, error: 0 - The specified network name is
no longer available.)
Command Text:
set nocount on
declare @.source_object_id int
declare @.sync_object_id int
set @.source_object_id = object_id(quotename(@.source_object_schema) + N'.' +
quotename(@.source_object_name))
set @.sync_object_id = object_id(quotename(@.sync_object_schema) + N'.' +
quotename(@.sync_object_name))
declare @.partitioning_column sysname
declare @.partitioning_column_type sysname
declare @.qualified_source_object_name nvarchar(600)
declare @.partitioning_column_collation sysname
declare @.partitioning_index_name sysname
set @.qualified_source_object_name = quotename(@.source_object_schema) + N'.'
+ quotename(@.source_object_name)
set @.partitioning_column = null
set @.partitioning_column_type = null
set @.partitioning_index_name = null
select @.partitioning_column = sc.name,
@.partitioning_column_type = st.name,
@.partitioning_column_collation = sc.collation,
@.partitioning_index_name = si.name
from sysindexes si
inner join syscolumns sc
on index_col(@.qualified_source_object_name, si.indid, 1) = sc.name
inner join systypes st
on sc.xtype = st.xusertype
where si.id = @.source_object_id
and sc.id = @.sync_object_id
and si.indid = 1
and st.name in (N'uniqueidentifier', N'bit', N'tinyint', N'smallint',
N'int', N'smalldatetime', N'real', N'money', N'datetime', N'float', N'bit',
N'decimal', N'numeric', N'smallmoney', N'bigint', N'varbinary', N'varchar',
N'binary', N'char', N'timestamp', N'nvarchar', N'nchar')
and (@.use_primary_key_only = 0 or si.status & 2048 = 2048)
if @.partitioning_column is not null
begin
select @.partitioning_index_name, @.partitioning_column,
@.partitioning_column_type, @.partitioning_column_collation
dbcc show_statistics(@.qualified_source_object_name,
@.partitioning_index_name)
end
Parameters: @.source_object_name = E_QRTZ_TRIGGER_LISTENERS
@.source_object_schema = dbo
@.sync_object_name = syncobj_0x3238314643373630
@.sync_object_schema = dbo
@.use_primary_key_only = 1
Stack: at
Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException e,
SqlCommand command)
at
Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteReader(SqlCommand
command, Int32 queryTimeout, CommandBehavior commandBehavior)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32
queryTimeout, CommandBehavior commandBehavior)
at
Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.GatherBaseTableSta tistics()
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioningUsingKeyDistributionHistogram(BaseArtic leWrapper article, Boolean usePrimaryKeyOnly)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioning(BaseArticleWrapper
article, Boolean usePrimaryKeyOnly)
at
Microsoft.SqlServer.Replication.Snapshot.SqlServer .BcpLoadHintAndPartitioningResolutionWorkerThreadP rovider.DoWork(WorkItem workItem)
at
Microsoft.SqlServer.Replication.WorkerThread.NonEx ceptionBasedAgentThreadProc()
at Microsoft.SqlServer.Replication.WorkerThread.Agent ThreadProc()
at
Microsoft.SqlServer.Replication.AgentCore.BaseAgen tThread.AgentThreadProcWrapper() (Source: MSSQLServer, Error number: 64)
Get help: http://help/64
Server NTFWEPSQLT1, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - The specified network name is no longer
available.) (Source: MSSQLServer, Error number: 64)
Get help: http://help/64
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
You had a network failure in the middle of your snapshot distribution. This
should clear the next time it runs. You might want to run ping -t to watch
to see how lossy your link is.
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
"Moh" <mabbas@.Pier1.com> wrote in message
news:B877BD9F-EE3C-456E-87A7-64BB17FC5C11@.microsoft.com...
>I am setting up a replication of a database which has 364 tables. The size
>of
> this database is close to 150 GB.
> I have one server as publisher.
> I have another server which i am using for distributor as well as
> subscriber. Both servers are seperate physical servers.
> I tried replicating 2 tables, I don;' see any issue. They replicate with
> no
> issue. However, when I try to replicate all tables I get this error on
> snapshot agent.
> Please advise if any one has already seen this problem. My both servers
> are
> on SQL 2005 SP2.
> --
> Error messages:
> Message: A transport-level error has occurred when receiving results from
> the server. (provider: TCP Provider, error: 0 - The specified network name
> is
> no longer available.)
> Command Text:
> set nocount on
> declare @.source_object_id int
> declare @.sync_object_id int
> set @.source_object_id = object_id(quotename(@.source_object_schema) + N'.'
> +
> quotename(@.source_object_name))
> set @.sync_object_id = object_id(quotename(@.sync_object_schema) + N'.' +
> quotename(@.sync_object_name))
> declare @.partitioning_column sysname
> declare @.partitioning_column_type sysname
> declare @.qualified_source_object_name nvarchar(600)
> declare @.partitioning_column_collation sysname
> declare @.partitioning_index_name sysname
> set @.qualified_source_object_name = quotename(@.source_object_schema) +
> N'.'
> + quotename(@.source_object_name)
> set @.partitioning_column = null
> set @.partitioning_column_type = null
> set @.partitioning_index_name = null
> select @.partitioning_column = sc.name,
> @.partitioning_column_type = st.name,
> @.partitioning_column_collation = sc.collation,
> @.partitioning_index_name = si.name
> from sysindexes si
> inner join syscolumns sc
> on index_col(@.qualified_source_object_name, si.indid, 1) = sc.name
> inner join systypes st
> on sc.xtype = st.xusertype
> where si.id = @.source_object_id
> and sc.id = @.sync_object_id
> and si.indid = 1
> and st.name in (N'uniqueidentifier', N'bit', N'tinyint',
> N'smallint',
> N'int', N'smalldatetime', N'real', N'money', N'datetime', N'float',
> N'bit',
> N'decimal', N'numeric', N'smallmoney', N'bigint', N'varbinary',
> N'varchar',
> N'binary', N'char', N'timestamp', N'nvarchar', N'nchar')
> and (@.use_primary_key_only = 0 or si.status & 2048 = 2048)
> if @.partitioning_column is not null
> begin
> select @.partitioning_index_name, @.partitioning_column,
> @.partitioning_column_type, @.partitioning_column_collation
> dbcc show_statistics(@.qualified_source_object_name,
> @.partitioning_index_name)
> end
> Parameters: @.source_object_name = E_QRTZ_TRIGGER_LISTENERS
> @.source_object_schema = dbo
> @.sync_object_name = syncobj_0x3238314643373630
> @.sync_object_schema = dbo
> @.use_primary_key_only = 1
> Stack: at
> Microsoft.SqlServer.Replication.AgentCore.ReMapSql Exception(SqlException
> e,
> SqlCommand command)
> at
> Microsoft.SqlServer.Replication.AgentCore.AgentExe cuteReader(SqlCommand
> command, Int32 queryTimeout, CommandBehavior commandBehavior)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
> commandSetupDelegate, ProcessResultsDelegate processResultsDelegate, Int32
> queryTimeout, CommandBehavior commandBehavior)
> at
> Microsoft.SqlServer.Replication.AgentCore.ExecuteW ithOptionalResults(CommandSetupDelegate
> commandSetupDelegate, ProcessResultsDelegate processResultsDelegate)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.GatherBaseTableSta tistics()
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioningUsingKeyDistributionHistogram(BaseArtic leWrapper
> article, Boolean usePrimaryKeyOnly)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .ArticleBcpPartitioningResolver.ResolveArticleBcpP artitioning(BaseArticleWrapper
> article, Boolean usePrimaryKeyOnly)
> at
> Microsoft.SqlServer.Replication.Snapshot.SqlServer .BcpLoadHintAndPartitioningResolutionWorkerThreadP rovider.DoWork(WorkItem
> workItem)
> at
> Microsoft.SqlServer.Replication.WorkerThread.NonEx ceptionBasedAgentThreadProc()
> at Microsoft.SqlServer.Replication.WorkerThread.Agent ThreadProc()
> at
> Microsoft.SqlServer.Replication.AgentCore.BaseAgen tThread.AgentThreadProcWrapper()
> (Source: MSSQLServer, Error number: 64)
> Get help: http://help/64
> Server NTFWEPSQLT1, Level 20, State 0, Line 0
> A transport-level error has occurred when receiving results from the
> server.
> (provider: TCP Provider, error: 0 - The specified network name is no
> longer
> available.) (Source: MSSQLServer, Error number: 64)
> Get help: http://help/64
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
|||Hilary - Why then it always works when I replicate any small database or same
data base with few articles. I see no time outs when ping during replication.
Sr DBA
Pier 1 Imports
mabbas@.Pier1.com
"Hilary Cotter" wrote:

> You had a network failure in the middle of your snapshot distribution. This
> should clear the next time it runs. You might want to run ping -t to watch
> to see how lossy your link is.
> --
> 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
>
> "Moh" <mabbas@.Pier1.com> wrote in message
> news:B877BD9F-EE3C-456E-87A7-64BB17FC5C11@.microsoft.com...
>
>
|||It really looks like a network failure somewhere. The ping-t command should
reveal network hiccups occurring during the snapshot application.
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
"Moh" <mabbas@.Pier1.com> wrote in message
news:C3B863FA-723D-4781-9A99-99FCA2DDB364@.microsoft.com...[vbcol=seagreen]
> Hilary - Why then it always works when I replicate any small database or
> same
> data base with few articles. I see no time outs when ping during
> replication.
> --
> Sr DBA
> Pier 1 Imports
> mabbas@.Pier1.com
>
> "Hilary Cotter" wrote:
|||Actually, the SQL Server could think it's under a DOS attack and start
killing off connections.
Check out section 5.1 of
http://download.microsoft.com/download/f/1/0/f10c4f60-630e-4153-bd53-c3010e4c513b/ReadmeSQLEXP2005.htm#sse_dbengine
for more information.
On Mar 19, 1:02 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It really looks like a network failure somewhere. The ping-t command should
> reveal network hiccups occurring during the snapshot application.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Moh" <mab...@.Pier1.com> wrote in message
> news:C3B863FA-723D-4781-9A99-99FCA2DDB364@.microsoft.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -

A subscription has expired and has been dropped

I have two SQL 2000 Servers with Merge Replication. I had a problem on my
link for a month and the "Expired subscription clean up" job dropped the
subscription ! and now I don't know how to replicate the generated data at
the subscriptor.
I appreciate any help.
You will need to re-apply a snapshot to the subscriber...
"gus barraza" wrote:

> I have two SQL 2000 Servers with Merge Replication. I had a problem on my
> link for a month and the "Expired subscription clean up" job dropped the
> subscription ! and now I don't know how to replicate the generated data at
> the subscriptor.
> I appreciate any help.
>
|||Jim the subscription type is PUSH
re-apply a snapshot means that i should PUSH a new subscription again ?
Will I lost the data generated at the subscriber ?
"Jim Breffni" wrote:
[vbcol=seagreen]
> You will need to re-apply a snapshot to the subscriber...
>
> "gus barraza" wrote:
|||Interesting problem.
I don't know of any method of automatically re-syncing after the
subscription has been dropped other than to apply a new snapshot.
Once the subscription has been dropped SQL has no way of keeping track of
changes that need to be applied to the subscriber.
Sounds like you will need a manual process to bring the publisher up to date
with subscriber changes before creating a new snapshot. Not a pleasent
thought...
Redgate do a sql DATA compare tool that will generate sql scripts for you
that will update your tables.
Jim.
"gusbarraza" wrote:
[vbcol=seagreen]
> Jim the subscription type is PUSH
> re-apply a snapshot means that i should PUSH a new subscription again ?
> Will I lost the data generated at the subscriber ?
> "Jim Breffni" wrote:
|||Thank you Jim,
I have my own application to this cases, but I had been confirm my suspects,
that the only way to recover data on the subscriber is through this slow
process.
I should start to make this process...
"Jim Breffni" wrote:
[vbcol=seagreen]
> Interesting problem.
> I don't know of any method of automatically re-syncing after the
> subscription has been dropped other than to apply a new snapshot.
> Once the subscription has been dropped SQL has no way of keeping track of
> changes that need to be applied to the subscriber.
> Sounds like you will need a manual process to bring the publisher up to date
> with subscriber changes before creating a new snapshot. Not a pleasent
> thought...
> Redgate do a sql DATA compare tool that will generate sql scripts for you
> that will update your tables.
>
> Jim.
>
> "gusbarraza" wrote:

A simple question regarding SQL 2000 Transactional Replication

We have a database that we would like to replicate to another server.
Am i correct to asume that the database that is updated would be the
publisher and that the subscriber database would be the replicated copy?
Greg, in the case of plain transactional replication, yes, this is the case.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

A serious bug in SQL-2005/2008 Replication: Loosing @@IDENTITY value

Hello all!

There is a bug in SQL-2005/2008 Replication system, which may break data integrity, when using @.@.IDENTITY function to update FOREIGN KEY of some table.

When Merge replication is set up, and there is a table article with IDENTITY column in it, after inserting a new row in the table a value of @.@.IDENTITY function does not actually shows just inserted row's identity value.

This issue also generated when performing inserts via ADO.

For details, see my Feedback to Microsoft:

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=286165

Also, there are other comments on this problem:

http://www.microsoft.com/communities/newsgroups/list/en-us/default.aspx?dg=microsoft.public.data.ado&tid=dcb56477-15fe-413e-a90a-3e1816bc7375&p=1

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=281682

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=284124

SQL Server Katmai July CTP has been released. But the bug described above is not resolved there. It still can be generated the same way! However, the feedback is marked as "Resolved"...

|||

it looks like it was resolved as by design. do you have any of the feedback or reasons this was marked by design?

|||

You should not rely on @.@.identity and propgram your solution.

You should rather look at using scope_identity.

|||

Thanks, Greg and Mahesh!

Now I understand, why it market as "Resolved by Design". But there is another problem: ADO itself uses @.@.IDENTITY instead of SCOPE_IDENTITY when inserts a value to a table. So, it seems impossible to use SQL-clients based on ADO in Merge Replication!

a resourse good for problems replication in sql server 2000

i want a good resourse for to learn replication in sql 2000 inoder to
practical learn and implemetion replicatThorough explanation:

http://msdn.microsoft.com/library/d...y/en-us/replsql
/replover_694n.asp

HTH

Ray Higdon MCSE, MCDBA, CCNA

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

'A required privilege is not held by the client' when re-initalization

Hi all,
the sys admin has changed the service a/c to another admin. id. But now, i can't start the replication jobs even re-created the publication and subscription with the new admin. id. Any advise? Thanks in advance!

'Executed as user: test.com\admin. A required privilege is not held by the client. The step failed.'

Hi Stephaine,

How do your admin change the service account? He/she might use Windows Service Control Manager to change account. To solve the issue, please refer to this KB article (http://support.microsoft.com/kb/911305/en-us).

Thanks,

Peng

|||an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.

'A required privilege is not held by the client' when re-initalization

Hi all,
the sys admin has changed the service a/c to another admin. id. But now, i can't start the replication jobs even re-created the publication and subscription with the new admin. id. Any advise? Thanks in advance!

'Executed as user: test.com\admin. A required privilege is not held by the client. The step failed.'

Hi Stephaine,

How do your admin change the service account? He/she might use Windows Service Control Manager to change account. To solve the issue, please refer to this KB article (http://support.microsoft.com/kb/911305/en-us).

Thanks,

Peng

|||an issue i found was that sql server agent and ssis had been changed to not use the local system account. this caused me to continually get the dreaded --> "A required privilege is not held by the client" error. i fought with it all afternoon and eventually updated the services to use the local system account and it worked. perhaps this will save some other as much time as i wasted.

A Little Basic Help needed (SQLServer 2005) Merge Replication

Hi all,

We are using SQL Server 2005, on Windows server 2003 R2.

We Have Two Database Servers say DBServer1 and DBServer2, Now I wants to do Replication between these to servers, such that

1. The Changes at DBServer1 should be reflected at DBServer2
2. The Changes at DBServer2 should be reflected at DBServer1
3. Changes includes Data changes and Schema Changes
4. After every Synchronization Both Databases should be Identical

I tried doing so, what i did was
I cofigured Distribution at DBServer1, also Publisher and Publication at DBServer1
and Made a Subscription at DBServer2.

What I successfully done is
If Publisher means DBServer1 do some changes then it gets updated at DBServer2.
But New Rows added at DBServer2 doesn't gets added at DBServer1

Thanks in Advance,
Vishalgiri Goswami
Kalptaru Infosoft Pvt. Ltd.Did you start the merge agent job? The merge agent does the synchronization.|||Yes Sir, Merge Agent is Running, I have Sheduled it to Run at 5 min Interval, I am viewing it's status from View Synchronization Option.

Even My Snapshot Agent is running too.|||Did you define a subset filter clause? And did your subscriber even download the snapshot? The snapshot agent doesn't need to run every 5 minutes.|||DId your subscriber download the schema from the snapshot? Does your publication have filtering, if yes, what does the subset filtering clause look like?

A lil confused here from BOL

From BOL : How Transactional Replication Works
"After all Subscribers have received transactions, the Distribution Cleanup
Agent removes delivered transactions in the distribution database. Delivered
transactions are kept in the distribution database for a defined period
known as the retention period. Setting a retention period while scheduling
backups can ensure that information required to recover a destination
database automatically is available within the distribution database.
For example, if a Subscriber has scheduled a transaction log dump of a
destination database every 24 hours, you could set the retention period to
48 hours. Even if the Subscriber experiences a failure immediately before a
scheduled backup, all transactions necessary to restore the replicated
tables automatically will still be available to the distribution process of
the Distributor."
So im confused at the first 2 statements where it says that the cleanup
agent job removes the delivered transaction in the distrib database but then
it also states that those delivered transactions are kept in the distrib
database for a defined period and then gives an example that we could set
the retention period to 48 hrs.. So if the cleanup agent cleans them, how is
it retained ?
Hassan,
they're retained until all subscribers have subscribed or the retention
period is reached, whichever is shorter. In the case of anonymous
subscribers this changes and they're retained until the end of the retention
period even if subscribers have picked them up.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

A Guide to SQL Server Transactional and Snapshot Replication from Hilary Cotter

Hi all
Can tell me anyone, if the book
A Guide to SQL Server Transactional and Snapshot Replication from
Hilary Cotter, Publish Date June 15/2004
are available?
How and where can I order this book.
http://www.nwsu.com/0974973602.html
Thanks Thomas
It hasn't gone to print yet. It should be going to print early next month.
Then it will take about 25-35 days to print. I'll announce when you can
preorder the book here.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:41088b9f.6019281@.news.t-online.de...
> Hi all
> Can tell me anyone, if the book
> A Guide to SQL Server Transactional and Snapshot Replication from
> Hilary Cotter, Publish Date June 15/2004
> are available?
> How and where can I order this book.
> http://www.nwsu.com/0974973602.html
> Thanks Thomas
|||Hi Hillary,
thanks for fast reply. Currently I am working on an bidirectional
replication migration from 6.5 to 2k.
Additional information would be nice for me, but not absolute needed.
But 30 days are to late for me.
Is it possible to get an (beta) online issue?
Greetings from Germany
On Wed, 28 Jul 2004 08:12:58 -0400, "Hilary Cotter" <hilaryk@.att.net>
wrote:

>It hasn't gone to print yet. It should be going to print early next month.
>Then it will take about 25-35 days to print. I'll announce when you can
>preorder the book here.

A Guide to SQL Server Transactional and Snapshot Replication

Is it ready?
Last I heard it was at the printer, with a street date mid to late November.
It was stuck in typesetting for several months.
I'll let you know when I hear more.
"Thomas Hase" <tohas@.freenet.de> wrote in message
news:4171e9f5.245848500@.news.t-online.de...
> Is it ready?

a group of sp_MSins stored procs has 2 members?

I decide to check with the forum because I haven't got any lucky result.
Here is the transactional replication between two SQL 2000 standard edition
+ sp3 servers I inherited a few month ago. On the subscriber, when I pushed
a new subcription there was an error "Cannot insert duplicate key row in
object bla bla with unique index 'bla bla'". Well, the errors are easy to
understand and fix. To my surprise, I noticed that the replication insert
stored proc has two stored procs defined in it. It looks like this:
create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
AS
BEGIN
insert into "smfPrice"(
"PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
"AuditInformationId", "IsoCurrencyCode", "DataSourceId"
)
values (
@.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
)
END
GO
create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
as
if exists ( select * from "tablename"
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
)
begin
update "tablename" set "AdjustedPrice" = @.c4,"Price" =
@.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" = @.c8
where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
end
else
begin
insert into "tablename" (
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
ormationId","IsoCurrencyCode","DataSourceId" ) values (
@.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
end
GO
I think the second version is better writen than the first one and can
suppress the said error. But obviously SQL server replication is not able to
pick the right one. When I swaped the procs titles while leaving the
definitions unchanged, the error went away.
My puzzle is: What event triggers the generation of the second set of procs?
What internal mechanism guarantees replication to use the correct version?
I'm thinking of removing the first instance of procs because the second one
already covers it. But for the future, I want to find out why it happens and
potentially come up with a better solution. I hope this is not a bug.
I'd like to hear your thoughs.
Richard
the second proc is a custom proc generated by a user. I suspect each proc
has a different owner. You should be able to check this by doing an sp_help
sp_MSins_tablename
or by browsing them in EM,
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
>I decide to check with the forum because I haven't got any lucky result.
> Here is the transactional replication between two SQL 2000 standard
> edition
> + sp3 servers I inherited a few month ago. On the subscriber, when I
> pushed
> a new subcription there was an error "Cannot insert duplicate key row in
> object bla bla with unique index 'bla bla'". Well, the errors are easy to
> understand and fix. To my surprise, I noticed that the replication insert
> stored proc has two stored procs defined in it. It looks like this:
> create procedure "sp_MSins_tablename" @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> AS
> BEGIN
> insert into "smfPrice"(
> "PriceDate", "SecurityId", "SecurityVersion", "AdjustedPrice", "Price",
> "AuditInformationId", "IsoCurrencyCode", "DataSourceId"
> )
> values (
> @.c1, @.c2, @.c3, @.c4, @.c5, @.c6, @.c7, @.c8
> )
> END
> GO
> create procedure "sp_MSins_tablename";2 @.c1 smalldatetime,@.c2 int,@.c3
> smallint,@.c4 money,@.c5 money,@.c6 int,@.c7 char(3),@.c8 int
> as
> if exists ( select * from "tablename"
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> )
> begin
> update "tablename" set "AdjustedPrice" = @.c4,"Price" =
> @.c5,"AuditInformationId" = @.c6,"IsoCurrencyCode" = @.c7,"DataSourceId" =
> @.c8
> where "PriceDate" = @.c1 and "SecurityId" = @.c2 and "SecurityVersion" = @.c3
> end
> else
> begin
> insert into "tablename" (
> "PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf
> ormationId","IsoCurrencyCode","DataSourceId" ) values (
> @.c1,@.c2,@.c3,@.c4,@.c5,@.c6,@.c7,@.c8 )
> end
> GO
> I think the second version is better writen than the first one and can
> suppress the said error. But obviously SQL server replication is not able
> to
> pick the right one. When I swaped the procs titles while leaving the
> definitions unchanged, the error went away.
> My puzzle is: What event triggers the generation of the second set of
> procs?
> What internal mechanism guarantees replication to use the correct version?
> I'm thinking of removing the first instance of procs because the second
> one
> already covers it. But for the future, I want to find out why it happens
> and
> potentially come up with a better solution. I hope this is not a bug.
> I'd like to hear your thoughs.
>
> Richard
>
>
|||These two procs are actually in one definition, except they have a suffix.
And they all belong to dbo.
I am able to reproduce what has happened by checking the "do not block"
option on and off on the snapshot tab in publicatio properties, I could see
two different version of procs are generated. I can also see the diference
is with the sp_addpublication @.sync_method variable. One is 'native' that
creates one sp_MSins proc; When I check the said option, SQL uses
'concurrent' and creates two procs in one set.
To clarify this, the two procs in one set means two "create proc" statements
in the GUI when you double click on the proc, one of which is tagged with
";2" as in sp_MSinstablename;2
99% of my publication are created this way and they have been running well
with no problems. That means SQL Server replicaiton knows when to use which
proc to do the job. My current error indicates that replication somehow lost
its judgement like it used to. It takes the default proc (the top one)
instead of taking the second (bottom one). So far, I've no luck in finding
out any useful hints.
Microsoft developers, possible to shed some light on this?
Richard
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
> the second proc is a custom proc generated by a user. I suspect each proc
> has a different owner. You should be able to check this by doing an
sp_help[vbcol=seagreen]
> sp_MSins_tablename
> or by browsing them in EM,
> "Richard Ding" <rding@.acadian-asset.com> wrote in message
> news:u7IKTHttEHA.4044@.TK2MSFTNGP09.phx.gbl...
to[vbcol=seagreen]
insert[vbcol=seagreen]
@.c3[vbcol=seagreen]
@.c3[vbcol=seagreen]
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
able[vbcol=seagreen]
version?
>
|||I've made a bit more progress:
It appears sp_script_reconciliation_insproc created sp_MSins;2.
sp_scriptinsproc created sp_MSins.
sp_depends shows nobody depends on sp_script_reconciliation_insproc.
Looks like two separate processes triggered each stored proc individually
with sp_scriptinsproc doing it first.
Is this a normal behavior?
"Richard Ding" <rding@.acadian-asset.com> wrote in message
news:u9mlzW5tEHA.3200@.TK2MSFTNGP09.phx.gbl...
> These two procs are actually in one definition, except they have a suffix.
> And they all belong to dbo.
> I am able to reproduce what has happened by checking the "do not block"
> option on and off on the snapshot tab in publicatio properties, I could
see
> two different version of procs are generated. I can also see the diference
> is with the sp_addpublication @.sync_method variable. One is 'native' that
> creates one sp_MSins proc; When I check the said option, SQL uses
> 'concurrent' and creates two procs in one set.
> To clarify this, the two procs in one set means two "create proc"
statements
> in the GUI when you double click on the proc, one of which is tagged with
> ";2" as in sp_MSinstablename;2
> 99% of my publication are created this way and they have been running well
> with no problems. That means SQL Server replicaiton knows when to use
which
> proc to do the job. My current error indicates that replication somehow
lost[vbcol=seagreen]
> its judgement like it used to. It takes the default proc (the top one)
> instead of taking the second (bottom one). So far, I've no luck in finding
> out any useful hints.
> Microsoft developers, possible to shed some light on this?
>
> Richard
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:eEzksEutEHA.3200@.TK2MSFTNGP14.phx.gbl...
proc[vbcol=seagreen]
> sp_help
result.[vbcol=seagreen]
in[vbcol=seagreen]
> to
> insert
"Price",[vbcol=seagreen]
> @.c3
=
> @.c3
>
"PriceDate","SecurityId","SecurityVersion","Adjust edPrice","Price","AuditInf[vbcol=seagreen]
> able
> version?
second[vbcol=seagreen]
happens
>

A few questions: Clustering & Replication

Hi,
In SQL 2005, with SQL Clustering:
1. Can a THREE-node active/active/active cluster be setup? If yes, is
setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
that one needs to be aware of?
With Replication:
1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
subscriber subsribes to a publication from one publisher and then in turn
creates a similar publication, which is subscribed by another subscriber.
2. Can replication (immediate and/or queued updating) be configured between
THREE servers? i.e. each server is a publisher and subcriber to the other two
servers.
We are working on a global strategy to identify how best to keep data in
sync between three geographically located SQL servers, while maintaining
latency as low as possible.
Peer-to-Peer replication was investigated as well, but it did not fit with
our company's strategic plan as one of our requirement is that database
updates can be done at any server, at anytime. Peer-to-Peer allows updates
for a given row only at one database until it has been syncronized with its
peers, which can be done by directing updates for a set of rows to a
particular server.
Your guidance, thougths, and suggestions are very valued and appreciated.
TIA
Regards,<DIV>"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...</DIV>> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
Yes, although we don't really call it active/active anymore. It's just an
N-node cluster. It has a number of cluster nodes and a number of SQL
instances (virtual servers). The biggest problem with running SQL instances
on all your cluster nodes is that you can have performance problems when you
fail over. Often you add an extra passive node to hold enough capacity in
reserve for failover. This is generally referred to as an N+1 cluster (N
"active" nodes, 1 "passive").
> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
You've got it wrong with Peer-to-Peer. Peer-to-Peer allows update to any
server at anytime. It just has no mechanism to protect you if you make
conflicting changes at nearly the same time, one of the changes will be
lost. The situation you are describing with multiple publishers would
behave in a similar manner.
David|||1) Yes, the gotcha is that at any one time a node may have to support all
three instances which means it will have to be pretty beefy to offer optimal
performance to all three instances
Replication
1) Yes, its called republishing. It normally used to provide hierarchies in
merge replication to scale out.
2) No, but you might want to look at bi-directional transactional
replication which can be used to do this. It would provide bi-directional
replication between node a and node b, and node a and node c, but not
between node b and node c unless you implement some pretty complex
filtering.
When Peer to peer out of the box encounters an error its distribution agents
will stop until you fix the error. You can run it in the continue on data
consistency errors profile, but doing this will leave your databases in an
inconsistent state.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
> Your guidance, thougths, and suggestions are very valued and appreciated.
> TIA
> Regards,|||Hi Rob,
the only thing I'd add to the other comments is that merge replication
essentially covers point (2). The terminology is in conflict but the
functionality is the same ie there would only be need for one publisher, and
the pthers would be subscribers, but for the most part, there isn't a
distinction between these roles in merge replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks Hilary.
For replication Q. 2: You may have already answered this but I was wondering
if a bi-directional replication were to exists between node A and B, could
node B republish the data to node C unidirectionally?
Regards,
- Rob.
"Hilary Cotter" wrote:
> 1) Yes, the gotcha is that at any one time a node may have to support all
> three instances which means it will have to be pretty beefy to offer optimal
> performance to all three instances
> Replication
> 1) Yes, its called republishing. It normally used to provide hierarchies in
> merge replication to scale out.
> 2) No, but you might want to look at bi-directional transactional
> replication which can be used to do this. It would provide bi-directional
> replication between node a and node b, and node a and node c, but not
> between node b and node c unless you implement some pretty complex
> filtering.
> When Peer to peer out of the box encounters an error its distribution agents
> will stop until you fix the error. You can run it in the continue on data
> consistency errors profile, but doing this will leave your databases in an
> inconsistent state.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
> > Hi,
> >
> > In SQL 2005, with SQL Clustering:
> > 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> > setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> > that one needs to be aware of?
> >
> > With Replication:
> > 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> > subscriber subsribes to a publication from one publisher and then in turn
> > creates a similar publication, which is subscribed by another subscriber.
> > 2. Can replication (immediate and/or queued updating) be configured
> > between
> > THREE servers? i.e. each server is a publisher and subcriber to the other
> > two
> > servers.
> >
> > We are working on a global strategy to identify how best to keep data in
> > sync between three geographically located SQL servers, while maintaining
> > latency as low as possible.
> >
> > Peer-to-Peer replication was investigated as well, but it did not fit with
> > our company's strategic plan as one of our requirement is that database
> > updates can be done at any server, at anytime. Peer-to-Peer allows updates
> > for a given row only at one database until it has been syncronized with
> > its
> > peers, which can be done by directing updates for a set of rows to a
> > particular server.
> >
> > Your guidance, thougths, and suggestions are very valued and appreciated.
> >
> > TIA
> >
> > Regards,
>
>

A few questions: Clustering & Replication

Hi,
In SQL 2005, with SQL Clustering:
1. Can a THREE-node active/active/active cluster be setup? If yes, is
setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
that one needs to be aware of?
With Replication:
1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
subscriber subsribes to a publication from one publisher and then in turn
creates a similar publication, which is subscribed by another subscriber.
2. Can replication (immediate and/or queued updating) be configured between
THREE servers? i.e. each server is a publisher and subcriber to the other tw
o
servers.
We are working on a global strategy to identify how best to keep data in
sync between three geographically located SQL servers, while maintaining
latency as low as possible.
Peer-to-Peer replication was investigated as well, but it did not fit with
our company's strategic plan as one of our requirement is that database
updates can be done at any server, at anytime. Peer-to-Peer allows updates
for a given row only at one database until it has been syncronized with its
peers, which can be done by directing updates for a set of rows to a
particular server.
Your guidance, thougths, and suggestions are very valued and appreciated.
TIA
Regards,<DIV>"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...</DIV>> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
Yes, although we don't really call it active/active anymore. It's just an
N-node cluster. It has a number of cluster nodes and a number of SQL
instances (virtual servers). The biggest problem with running SQL instances
on all your cluster nodes is that you can have performance problems when you
fail over. Often you add an extra passive node to hold enough capacity in
reserve for failover. This is generally referred to as an N+1 cluster (N
"active" nodes, 1 "passive").

> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
You've got it wrong with Peer-to-Peer. Peer-to-Peer allows update to any
server at anytime. It just has no mechanism to protect you if you make
conflicting changes at nearly the same time, one of the changes will be
lost. The situation you are describing with multiple publishers would
behave in a similar manner.
David|||1) Yes, the gotcha is that at any one time a node may have to support all
three instances which means it will have to be pretty beefy to offer optimal
performance to all three instances
Replication
1) Yes, its called republishing. It normally used to provide hierarchies in
merge replication to scale out.
2) No, but you might want to look at bi-directional transactional
replication which can be used to do this. It would provide bi-directional
replication between node a and node b, and node a and node c, but not
between node b and node c unless you implement some pretty complex
filtering.
When Peer to peer out of the box encounters an error its distribution agents
will stop until you fix the error. You can run it in the continue on data
consistency errors profile, but doing this will leave your databases in an
inconsistent state.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Rob" <Rob@.discussions.microsoft.com> wrote in message
news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
> Hi,
> In SQL 2005, with SQL Clustering:
> 1. Can a THREE-node active/active/active cluster be setup? If yes, is
> setting up a three-node a/a/a cluster recommended? Any pitfalls or gotchas
> that one needs to be aware of?
> With Replication:
> 1. Can a subscriber in turn be a publisher to another subscriber? i.e. a
> subscriber subsribes to a publication from one publisher and then in turn
> creates a similar publication, which is subscribed by another subscriber.
> 2. Can replication (immediate and/or queued updating) be configured
> between
> THREE servers? i.e. each server is a publisher and subcriber to the other
> two
> servers.
> We are working on a global strategy to identify how best to keep data in
> sync between three geographically located SQL servers, while maintaining
> latency as low as possible.
> Peer-to-Peer replication was investigated as well, but it did not fit with
> our company's strategic plan as one of our requirement is that database
> updates can be done at any server, at anytime. Peer-to-Peer allows updates
> for a given row only at one database until it has been syncronized with
> its
> peers, which can be done by directing updates for a set of rows to a
> particular server.
> Your guidance, thougths, and suggestions are very valued and appreciated.
> TIA
> Regards,|||Hi Rob,
the only thing I'd add to the other comments is that merge replication
essentially covers point (2). The terminology is in conflict but the
functionality is the same ie there would only be need for one publisher, and
the pthers would be subscribers, but for the most part, there isn't a
distinction between these roles in merge replication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com|||Thanks Hilary.
For replication Q. 2: You may have already answered this but I was wondering
if a bi-directional replication were to exists between node A and B, could
node B republish the data to node C unidirectionally?
Regards,
- Rob.
"Hilary Cotter" wrote:

> 1) Yes, the gotcha is that at any one time a node may have to support all
> three instances which means it will have to be pretty beefy to offer optim
al
> performance to all three instances
> Replication
> 1) Yes, its called republishing. It normally used to provide hierarchies i
n
> merge replication to scale out.
> 2) No, but you might want to look at bi-directional transactional
> replication which can be used to do this. It would provide bi-directional
> replication between node a and node b, and node a and node c, but not
> between node b and node c unless you implement some pretty complex
> filtering.
> When Peer to peer out of the box encounters an error its distribution agen
ts
> will stop until you fix the error. You can run it in the continue on data
> consistency errors profile, but doing this will leave your databases in an
> inconsistent state.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Rob" <Rob@.discussions.microsoft.com> wrote in message
> news:7C72EA17-1D74-42FA-BA28-6A0CBDC5535C@.microsoft.com...
>
>