Combining Peer-to-Peer Transactional Replication with Standard Transactional Replication

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

Peer-to-peer would be ideal for your scenario if all your subscribers work primarily on partition data, meaning the chance of two subscribers access the same piece of data is usually pretty low and stable network connection.

If you foresee lots of coflicts situation where different subscribers will access the same data, then you may want to consider merge replication. Merge replication handles conflicts better and does not require dedicated live connection from node to node.

Gary

|||If our network is not stable, we would prefer to use Merge Replication.
However, if there are at least 10 sites for replication, then is each site need to have 9 connections to the other sites?
That's our main concern.
Thanks a lot.|||

I believe the changes are replicated to publisher first then to other subscribers.

http://msdn2.microsoft.com/en-us/library/ms152746.aspx

Therefore, each site only needs to maintain one connection (to the publisher).

Gary

Combining Peer-to-Peer Transactional Replication with Standard Transactional Replication

Hello,

I'm interested in combining the Peer-to-Peer Transactional Replication and Standard Transactional Replication to provide a scale out solution of SQL Server 2005. The condition is as follows:

We may have 10 SQL Server 2005 (1 Publisher + 9 Subscriber) running transactional replication in the production environment and allow updates in subscribers. To offload the loading of the publisher, we plan to have 2 Publisher (PubNode1 and PubNode2) using Peer-to-Peer Transaction Replication and the rest 8 subscribers will be divided into 2 groups. The subscribers 1-4 (SubNode1, SubNode2, SubNode3, and SubNode4) will be set to be standard transactional replication subscribers of PubNode1, and the rest 4 subscribers (SubNode5, ..., SubNode8) will be set to be standard transactional replication subscribers of PubNode2.

Is it possible to setup above 2 Publisher + 8 Subscriber topology?
Also, could we set the 8 subscribers with updatable subscriptions to achieve each node is updatable?

We do not plan to set all the 10 nodes using Peer-to-Peer Transactional Replication as it is necessary to make sure n*(n-1)/2 (i.e. 45) peer-to-peer connections is reliable. It seems that the maintenance cost is high if the servers are not in a LAN and the topology is very high coupling. So we prefer to divide the 10 nodes into 2 groups and reduce the cost of each node to maintain the connections to all other sites.

That's the scenario.

Any feedback is welcome and appreciated.

Thanks,
Terence

Peer-to-peer would be ideal for your scenario if all your subscribers work primarily on partition data, meaning the chance of two subscribers access the same piece of data is usually pretty low and stable network connection.

If you foresee lots of coflicts situation where different subscribers will access the same data, then you may want to consider merge replication. Merge replication handles conflicts better and does not require dedicated live connection from node to node.

Gary

|||If our network is not stable, we would prefer to use Merge Replication.
However, if there are at least 10 sites for replication, then is each site need to have 9 connections to the other sites?
That's our main concern.
Thanks a lot.|||

I believe the changes are replicated to publisher first then to other subscribers.

http://msdn2.microsoft.com/en-us/library/ms152746.aspx

Therefore, each site only needs to maintain one connection (to the publisher).

Gary

com plus catalog requirement?

Hello, this is Subhani .

I am installing Sql 2005 standard version.

The problem is that while I am installing Sql 2005 the two warning messages are given that “com plus catalog requirement” & “minimum hardware requirement”So please suggest me, what I have to do to install successfully

Hi Subhani, see this article below:

http://msdn2.microsoft.com/en-us/library/ms143690.aspx

|||

..Or this other one:

http://msdn2.microsoft.com/en-us/library/ms143698.aspx

column update function

This is probably a common problem with a standard design pattern, but
I'm having trouble finding the solution.

I have a table with a lot of columns, for this example I'll just use
three but in reality its more like 20.

Create Table myTable (int col_one primary key, int col_two,
varchar(20) col_three) etc...

I want to write a sproc that allows updating of this column. Say I
have a sproc

create sproc myUpdate int @.col_one, int @.col_two, varchar(20)
col_three

as

update myTable col_two = @.col_two, col_three = @.col_three
where col_one = @.col_one

then if I only want to update col_two I have to pass in the current
value of col_three so that it remains the same, which seems pretty
inefficient. so I could change it to:

as

update myTable col_two = coalasce(@.col_two, col_two)
, col_three = coalasce(@.col_three, col_three)
where col_one = @.col_one

and then if I wanted to leave col_three the way it is then I could
just do

exec myUpdate 1, 2, NULL

the only problem here is that what if the value of col_three is
currently 3, and I want to set it to NULL? Under the current method,
setting someting to NULL is impossible

finally, I'd like to use parameter naming in my exec calls. that way
I can just say someting like

exec myUpdate 1, col_three=3

this would update col_three to 3 and leave the rest of the fields
untouched. you can see how handy this would be if you just want to
change a few of the fields in a table with a large number of columns.

I'm sure this has been done before, can somebody point me in the right
direction?

Thanks,

Benben (santoshamb@.yahoo.com) writes:
> update myTable col_two = coalasce(@.col_two, col_two)
> , col_three = coalasce(@.col_three, col_three)
> where col_one = @.col_one
> and then if I wanted to leave col_three the way it is then I could
> just do
> exec myUpdate 1, 2, NULL
> the only problem here is that what if the value of col_three is
> currently 3, and I want to set it to NULL? Under the current method,
> setting someting to NULL is impossible
> finally, I'd like to use parameter naming in my exec calls. that way
> I can just say someting like
> exec myUpdate 1, col_three=3
> this would update col_three to 3 and leave the rest of the fields
> untouched. you can see how handy this would be if you just want to
> change a few of the fields in a table with a large number of columns.

T-SQL is not a language that lends itself to this sort of thing. There
is no way to tell whether a parameter was passed explicitly or not. You
can of course test for NULL, but it may have been an explicit NULL.

One alternative would be to have extra marker variables to tell whether
a parameter applies or not. It quickly gets bulky. It can be reduced to
a single parameter which is a bitmask, but that is cryptic and error-prone.

What we do in our update procedures is to pass all column values. But
then we typically have read all to the GUI and now we are sending them
back. If some operation updates only affects a few columns, that is
typically an individual UPDATE statement in a different procedure.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Column Headings in Result Sets

Does the ANSI SQL Standard specify how columns are named in result sets?

I ask this because I just came across some behavior that surprised me, or went against my expectations.

Given the table: t1 (c1 int)
and this query: select t1.c1 from t1

the results are displayed as follows on all databases I tried it on (SQL Server, Oracle, DB2, mySQL and Sybase):

c1
--
1
2
..etc

I would have expected the column heading to be different, as follows:

t1.c1
--
1
2
...etc

So, if you say 't1.c1' in the select list, you should see t1.c1 in the column heading in the result set.

Given another table, t2 (c1 int), the column headings in the result set of the query 'select t1.c1, t2.c1 from t1,t2' were the same on all database platforms:
c1 | c1
----
1 | 2
2 | 3

This strikes me as ambiguous, because how do you know which result column came from which table ? I know that you can use column aliases if you want unique names in your column headings, but is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?

Thanks,
Colm.... is there a good reason why the column headings 't1.c1' and 't2.c1' wouldn't be used by default if you specify 't1.c1' and 't2.c1' as items in your select list?yes -- because by the time the result set is constructed, the database has forgotten which table each column came from

:)

more accurately, a column name is an identifier, whereas "t1.c1" is a string

you could always do this, if you really need it --

select t1.c1 as "t1.c1", ...

Collation setup for SQLServer2000 Standard Edition

After installing sp1 on my Windows 2003 Server I was able to install SQLServer2000, but, during installation I couldn't set up collation options for SQLServer.

I heve seen many screenshots where they say after asked for instance name I should be asked for collation setup options but I can't see them.

I don't know what should I do because my ERP needs to have the SQL_Latin1_General_CP1_C1_AS setup. Can I change this after initial installation?

Thanks in advance...

Quote:

Originally Posted by Marisol

After installing sp1 on my Windows 2003 Server I was able to install SQLServer2000, but, during installation I couldn't set up collation options for SQLServer.

I heve seen many screenshots where they say after asked for instance name I should be asked for collation setup options but I can't see them.

I don't know what should I do because my ERP needs to have the SQL_Latin1_General_CP1_C1_AS setup. Can I change this after initial installation?

Thanks in advance...

If you want to change the Server Collation, please start the installation in below manner (replacing desired Collation name). All The Best !!!

start /wait setup.exe /qb INSTANCENAME=MSSQLSERVER REINSTALL=SQL_Engine REBUILDDATABASE=1 SAPWD=test SQLCOLLATION=Latin1_General_CI_AI

Collation problem with Oracle as linked server

I have the following configuration of the servers:
1.Oracle 9i (database codepage is CL8MSWIN1251)
2. MS SQL Server 2000 Standard Edition (server codepage is
Cyrillic_General_CI_AS)
Oracle is the linked server in the SQL. When I use
query "Select * from openquery(ORASRV, 'select field1,
field2 from table1')" I receive question symbols ("?")
instead of russian letters.
What should I do to correct this situation?Sergey,
I don't know Oracle (sorry) but the COLLATE command is used by SQL Server to
move characters into a particular collation.
I see that Cyrillic_General is considered to be in code page 1251, which is
what Oracle is using, so I don't know why you would be getting back only
?.
Try:
'select field1 COLLATE Cyrillic_General_CI_AS,
field2 COLLATE Cyrillic_General_CI_AS from table1')
See if that resolves the problem.
Russell Fields
"Sergey Khanzhin" <skhan@.mail.ru> wrote in message
news:0d7d01c36177$e7ba61e0$a101280a@.phx.gbl...
> I have the following configuration of the servers:
> 1.Oracle 9i (database codepage is CL8MSWIN1251)
> 2. MS SQL Server 2000 Standard Edition (server codepage is
> Cyrillic_General_CI_AS)
>
> Oracle is the linked server in the SQL. When I use
> query "Select * from openquery(ORASRV, 'select field1,
> field2 from table1')" I receive question symbols ("?")
> instead of russian letters.
>
> What should I do to correct this situation?
>