Problem with central subscriber model

Hi,

I am trying to acheive the central subscriber model for Sql server 2005 replication. I have two publishers and the centrol subscriber. Both publishers are Sql server 2005 Workgroup edition. The following table shows the Software I have used for the test.

SQL version

Service pack

HotFix

Publishers

SQL Server 2005 Workgroup edition

2

1

Subscriber

SQL Server 2005 Enterprise edition

2

1

I have encountered an obstacle in the replication process to achieve the Central subscriber model. I have configuration table, It exists on servers TB1 and TB2 and want to amalgamate the data to a central subscriber. There are two duplicate primary key and overlapping values existing across the 2 publisher tables. These records need to be updated during the process of replication.

TB1

Guid Name CreatDate EditDate

34a.. TEST02F 3/6/2007 3/6/2007

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

TB2

Guid Name CreatDate EditDate

d008.. MS060F 7/20/2005 11/21/2006

775.. EP109F 1/29/2007 1/29/2007

ffe.. EP109G 1/26/2007 1/26/2007

Normally, the default Article properties settings are used 'DROP existing table and re-create it', however here we don't want this setting, because the one table records will then be removed, leaving just the data from the other table. So, we modify the article properties on the snapshot tab to 'Keep the existing table unchanged'’. As result we got the PK violation error.

I have tried using -SkipError option in distribution job Replication Agent Profiles, that did not work.

Thank you for your help

Where are you getting this error? Are you getting it while the snapshot is being applied?

If so you need to create a consistent subscription which represents the data in all publishers with duplicates removed. Most people will have a location identifier in all publishers and then filter their publication on this filter (the delete all data which matches the row filter option in the name conflicts section of the snapshot tab).

If you are getting this error during the application of new commands you need to consider how unique your primary key is. GUIDs are pretty well guaranteed to be unique so I don't really understand how this could happen. You could modify your insert proc to do an existence check before it inserts the values.

|||The snapshot is going to take the contents of publisher1 and stick them into the tables on the subscriber. It will then take the contenst of publisher2 and stick that data into the same set of tables. There isn't any error with replication in this. If you can't take a table from publisher1 and a table from publisher2, smash them into a single table, and not get a primary key conflict, replication can not overcome this. A central subscriber is designed to aggregate data from 1 - N publishers into a single set of tables. That data still needs to obey the rules you set up on the tables such as PK. So, the only way this works is to ensure that you aren't trying to send anything in that causes a PK violation.sql

Problem with a Replication with two publications

Hi.

I have a database with two publications.
One publication is used to replicate data among 4 SQL Servers, while the other is used for the replication with SQL Server CE clients.
The problem here is that the SQLCE receive a number of changes that it is not correct.
Before establishing the publication for SQL Servers the SQL CE clients were working great, but now i have this problem.

Is there any patch or fix I should install on the server?, or do you know how can I solve this?

Thank you.

Omar Rojascan you give more details about your setup? Is this tran or merge publication? what errors are you getting? are there tables belonging to both publications?|||Hi, Greg.

Both are Merge Publications. The tables are belonging to both pubs. The problem is that when I add a SQL 2K subscription and it synchronize, it takes (in this case) about 61000 changes, while the SQLCE client just needs about 100 or so. The SQLCE client is just collecting its 100 changes, but everytime it replicates, it receives a notification that got 61000 changes (even when just 100 are real) BUT its replication time lasts very much.
The difference between publications are the filters. The filters of the SQLCE are specific for one client, while the filters of SQL2K can give info about many clients. (Specified by columns in tables)

Regards!

Omar.|||

Hi Omar,

Help me understand the scenario.

1. Publication pub1 goes to SQL 2K subscriber has a filter that qualifies 61000 rows from a table.

2. Publication pub2 goes to SQL CE subscriber has a filter that qualifies only 100 rows from the very same table

3. The only changes are in the filters for the 2 publications

4. CE subscriber syncs to the publisher for publication pub2 and does get only the relevant 100 rows but it takes a long time and reports 61000 rows downloaded. You do not see 61000 rows at the subscriber and see only 100.

|||Mahesh:

That's it, exactly as you said it!, at the time I delete the SQL2K Subscription, everything on the SQLCE subscriber becomes normal.

Have you had any scenario like this?

Thank you for your reply!

Omar|||

No, I havent seen a scenario like this.

It will help if you can post your publication/article/filter scripts.

Also I assume your CE subscriber is 2.0 and not 3.0?

|||Hi, Mahesh.

I updated the SQL Server to SP3 and all seems to work fine. Either way, Some rows are not being replicated on some tables and I'm checking on it, but the problem about de 60k rows is gone (or at least it seems to).
Thank you for your help!
Regards!

Omarsql

Problem wiht merge replication using pull subscription across internet

Hi,
I want to use merge replication will pull subscription across
internet.
I have 2 computers with SQL Server 2005.
Computer A is the main computer that will have the publication.
Computer B will have a subscription to the Computer A publication.
The distributor will be in Computer B.
Computer A is behind a firewall. The firewall has the following rules
for incoming traffic:
everything denied for all IPs except for the following ports:
20 TCP
21 TCP
22 TCP
23 TCP
25 TCP
53 TCP, UDP
80 TCP
81 TCP
110 TCP
143 TCP
443 TCP
444 TCP
554 TCP
1433 TCP
1755 TCP,UDP
3306 TCP
3389 TCP
5432 TCP
5005 UDP
5800 TCP
5900 TCP
8443 TCP
10000 TCP
25112 TCP
And the following rules for outgoing traffic:
everything denied for all IPs except for the following ports:
20 TCP
21 TCP
22 TCP
23 TCP
25 TCP
53 TCP,UDP
80 TCP
110 TCP
123 UDP
143 TCP
443 TCP
1433 TCP
3306 TCP
5432 TCP
Before trying to set up replication I tried to connect from SSMS from
computer B to A and it worked.
However, trying to connect from SSMS in computer A to computer B
didn't worked.
If I try to connect from another computer (that is not behind the
firewall) to computer B it works.
My question is what outgoing ports need to be opened in the firewall
that protects computer A in order to connect to the SQL Server
instance in computer B?
Will I need to modify my firewall setup if I used push subscription
instead of pull subscription?
Thanks
You would need to open port 1433 for outbound to connect to server b. For a
push server A would connect to its own snapshot share, so this should work
over part 1433 as well.
I would recommend using pull however.
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
"Manu" <manunews@.gmail.com> wrote in message
news:1171023660.026202.308300@.a75g2000cwd.googlegr oups.com...
> Hi,
> I want to use merge replication will pull subscription across
> internet.
> I have 2 computers with SQL Server 2005.
> Computer A is the main computer that will have the publication.
> Computer B will have a subscription to the Computer A publication.
> The distributor will be in Computer B.
> Computer A is behind a firewall. The firewall has the following rules
> for incoming traffic:
> everything denied for all IPs except for the following ports:
> 20 TCP
> 21 TCP
> 22 TCP
> 23 TCP
> 25 TCP
> 53 TCP, UDP
> 80 TCP
> 81 TCP
> 110 TCP
> 143 TCP
> 443 TCP
> 444 TCP
> 554 TCP
> 1433 TCP
> 1755 TCP,UDP
> 3306 TCP
> 3389 TCP
> 5432 TCP
> 5005 UDP
> 5800 TCP
> 5900 TCP
> 8443 TCP
> 10000 TCP
> 25112 TCP
> And the following rules for outgoing traffic:
> everything denied for all IPs except for the following ports:
> 20 TCP
> 21 TCP
> 22 TCP
> 23 TCP
> 25 TCP
> 53 TCP,UDP
> 80 TCP
> 110 TCP
> 123 UDP
> 143 TCP
> 443 TCP
> 1433 TCP
> 3306 TCP
> 5432 TCP
> Before trying to set up replication I tried to connect from SSMS from
> computer B to A and it worked.
> However, trying to connect from SSMS in computer A to computer B
> didn't worked.
> If I try to connect from another computer (that is not behind the
> firewall) to computer B it works.
> My question is what outgoing ports need to be opened in the firewall
> that protects computer A in order to connect to the SQL Server
> instance in computer B?
> Will I need to modify my firewall setup if I used push subscription
> instead of pull subscription?
> Thanks
>
|||Hilary,
I had the port 1433 open for outbound connections in server A but it
wasn't working. I was using a named instance in server B with dynamic
ports so after setting the server B to use the port 1433 I can connect
from server A to server B.
Now I'm trying to configure the merge replication (fingers crossed).
Thanks for the help,
Manu
Hilary Cotter wrote:[vbcol=seagreen]
> You would need to open port 1433 for outbound to connect to server b. For a
> push server A would connect to its own snapshot share, so this should work
> over part 1433 as well.
> I would recommend using pull however.
> --
> 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
>
> "Manu" <manunews@.gmail.com> wrote in message
> news:1171023660.026202.308300@.a75g2000cwd.googlegr oups.com...

problem when try to config replication

Hi guys, i m just started to learn replication (and new to sql server as well). and got a problem when trying out instructions from MSDN, when i try to start new publication wizard. the following error message comes up. could anyone tell me what's going on?

===================================

Cannot connect to NATURE.

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'ComputerFans'. (Replication.Utilities)

Program Location:

in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.ValidateConnection(UIConnectionInfo connInfo, IServerType server)
in Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

is it some thing to do with my installation of sql server? btw, i m using sql server 2005

thanks a lot
In Management Studio, have you registered the server using "." or "local", if yes please specify the actual instance name ComputerFans

HTH
Vishal|||

thanks for ur reply.

it seems like its the problem with DB instance name, coz i tried one of my named instance of db engine, it works....

|||

You are partly correct. The problem is the SQL server name does not equal the Windows server name.Do a ‘select * from sysservers’ against the Master Db.The SVRName should be ServerName\InstanceName.If it is not, you’ll need to do an ‘SP_dropserver” followed by a ‘SP_addserver’ to correct it.Check the BOL for the proper parameters before running the SPs.

Odie.

problem when try to config replication

Hi guys, i m just started to learn replication (and new to sql server as well). and got a problem when trying out instructions from MSDN, when i try to start new publication wizard. the following error message comes up. could anyone tell me what's going on?

===================================

Cannot connect to NATURE.

===================================

SQL Server replication requires the actual server name to make a connection to the server. Connections through a server alias, IP address, or any other alternate name are not supported. Specify the actual server name, 'ComputerFans'. (Replication.Utilities)

Program Location:

in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.CheckServerAlias(ServerConnection conn)
in Microsoft.SqlServer.Management.UI.ReplicationSqlConnection.ValidateConnection(UIConnectionInfo connInfo, IServerType server)
in Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

is it some thing to do with my installation of sql server? btw, i m using sql server 2005

thanks a lot
In Management Studio, have you registered the server using "." or "local", if yes please specify the actual instance name ComputerFans

HTH
Vishal|||

thanks for ur reply.

it seems like its the problem with DB instance name, coz i tried one of my named instance of db engine, it works....

|||

You are partly correct. The problem is the SQL server name does not equal the Windows server name.Do a ‘select * from sysservers’ against the Master Db.The SVRName should be ServerName\InstanceName.If it is not, you’ll need to do an ‘SP_dropserver” followed by a ‘SP_addserver’ to correct it.Check the BOL for the proper parameters before running the SPs.

Odie.

problem when activating the replication

Hi,
as soon as I enable the replication, I get an red X on
replication Monitor\agents\log reader agents
replication Monitor\agents\distribution agents
publishers\machinename
I use to replicate on this machine but since I disabled it once, can't
re-enable it.
SQL 2000 sp3
thanks
Alex
Can you post any error details that show up in the agents to help us to
track this down?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alexandre" <nospam@.nospam.net> wrote in message
news:%23PdgoSC9EHA.960@.TK2MSFTNGP11.phx.gbl...
> Hi,
> as soon as I enable the replication, I get an red X on
> replication Monitor\agents\log reader agents
> replication Monitor\agents\distribution agents
> publishers\machinename
> I use to replicate on this machine but since I disabled it once, can't
> re-enable it.
> SQL 2000 sp3
> thanks
> Alex
>
|||There are no errors anywhere in the agents and now the replication works but
I still have the red X as mentionned below...
anywhere else I can look for errors?
Alex.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:e9fB6VC9EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Can you post any error details that show up in the agents to help us to
> track this down?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Alexandre" <nospam@.nospam.net> wrote in message
> news:%23PdgoSC9EHA.960@.TK2MSFTNGP11.phx.gbl...
>
|||you probably have a phantom error.
run sp_MSload_replication_status
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Alexandre" <nospam@.nospam.net> wrote in message
news:Oun0gpC9EHA.2180@.TK2MSFTNGP12.phx.gbl...
> There are no errors anywhere in the agents and now the replication works
but
> I still have the red X as mentionned below...
> anywhere else I can look for errors?
> Alex.
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:e9fB6VC9EHA.3416@.TK2MSFTNGP09.phx.gbl...
>

Problem w/2005 to 2000 replication

When I add the option to add extended properties to the publication, create
the snapshot and then distribute I get the following error in the
distribution agent task:An invalid parameter or option was specified for
procedure 'sp_addextendedproperty'
Any ideas?
Nothing in the documentation that I could find to explain this - it may be a
bug. I'll see if I can reproduce on Monday.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Mike,
While our documentation states that replication from SQL2005 to SQL2000
subscribers is supported, I will be the first to admit that the support is
by no means perfect as we don't really have a general schema translator that
can perform what would be considered reasonable mapping of SQL2005
syntax\functionalities to SQL2000 "equivalent". As such, I am not really
surprised that you are running into problems when replicating extended
properties from SQL2005 to SQL2000, but I would really appreciate if you can
use SQL profiler to capture the problematic 'sp_addextendedproperty' call
from the distribution agent and post [a sanitized version of] it here so we
can decide if we can do something about it or perhaps suggest a workaround
(one possibility is to disable extended property scripting for the
problematic article and put the extended property definitions in a
post-snapshot script). At this point, I would hazard a guess that the
problematic call probably contains @.level0type = 'SCHEMA' in it.
Hope that helps,
-Raymond
"MikeG" <mgagne11@.comcast.net> wrote in message
news:Od0CSc9HHHA.1816@.TK2MSFTNGP06.phx.gbl...
> When I add the option to add extended properties to the publication,
> create the snapshot and then distribute I get the following error in the
> distribution agent task:An invalid parameter or option was specified for
> procedure 'sp_addextendedproperty'
> Any ideas?
>