Replication Error while Configuring Distributor

Hi,
I am trying to setup transactional replication on a database within 1
sql instance. When I try to configure the pulisher/distributor, it errors out
with this message.
"SQL Server Enterprise Manager could not configure 'DZDEV007' as the
distributor for 'DZDEV007'.
Error 14234 : The specified '@.server' in invalid (valid values are returned
by sp_helpserver"
What's going on ? Please help.
TIA
Mohan
Hi Mohan,
The following KB seems to match most of your symptoms although your server
name appear to be too short for the problem occur:
http://support.microsoft.com/default...b;en-us;310639
You may also want to consider some other possibilities such as subtle
comparison issues related to the collation that you are using for your server.
-Raymond
"MO" wrote:

> Hi,
> I am trying to setup transactional replication on a database within 1
> sql instance. When I try to configure the pulisher/distributor, it errors out
> with this message.
> "SQL Server Enterprise Manager could not configure 'DZDEV007' as the
> distributor for 'DZDEV007'.
> Error 14234 : The specified '@.server' in invalid (valid values are returned
> by sp_helpserver"
> What's going on ? Please help.
> TIA
> Mohan

Replication Error after Upgrade (7.0-2000)

Scenario: 3 servers - one Subscriber, one Distributor, one Publisher. Transactional replication; immediate updating subscribers disallowed. Each article in its own publication... independent agents.
Subscriber & Publisher = SQL 7.0
Distributor recently upgraded to 2000, SP3a.
Issue: new publications created at publisher fail to initialize snapshot agent properly. Error: "Another snapshot agent for the publication is running". Agent then fails after total retries (default is 10... does not matter what I set it to).
All existing publications work fine; all existing subscriptions work fine.
So what's the deal?
It appears that once you upgrade the Distributor(s), you have to upgrade the Publisher(s) immediately thereafter if you wish to create new publications. I have not seen that documented however... anyone know?
Thanks!
X
This is documented somewhat. Check out
http://msdn.microsoft.com/library/de...grade_45ir.asp
It doesn't say do this immediately though.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Scenario: 3 servers - one Subscriber, one Distributor, one Publisher.
Transactional replication; immediate updating subscribers disallowed. Each
article in its own publication... independent agents.
> Subscriber & Publisher = SQL 7.0
> Distributor recently upgraded to 2000, SP3a.
> Issue: new publications created at publisher fail to initialize snapshot
agent properly. Error: "Another snapshot agent for the publication is
running". Agent then fails after total retries (default is 10... does not
matter what I set it to).
> All existing publications work fine; all existing subscriptions work fine.
> So what's the deal?
> It appears that once you upgrade the Distributor(s), you have to upgrade
the Publisher(s) immediately thereafter if you wish to create new
publications. I have not seen that documented however... anyone know?
> Thanks!
> X
|||Thanks for the response Hilary.
I read that article; it doesn't specify that publishers MUST be upgraded to 2000 once the Distributor has been upgraded. That's the issue here: we have an upgraded Distributor, but we cannot upgrade the Publisher for some time. In the meantime, we need to
create new publications. We are unable to do so however, due to the error I outlined. It APPEARS that once you upgrade your Distributor, you have to upgrade your Publisher before you can create new publications. That doesn't seem right, but I have yet to
find evidence of its validity.
FWIW: one of the DBAs here reported the same error after applying SPs/hotfixes on the Distributor w/o applying same to Publisher.
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||FYI: the following URL shows the configuration I outlined as a 'valid replication topology' http://msdn.microsoft.com/library/de...limpl_4joy.asp
However, it doe not indicate that new publications created on the 7.0 Publisher w/ 2k Distributor and 7.0 Subscriber(s) will fail. On the contrary - it leads one to believe that the new publications will be in 70 compatability mode, as the replication top
ology defaults to the lowest level in a mixed environment.
Odd then, that I get this error. (btw, we have recycled the servers)
"Hilary Cotter" wrote:

> This is documented somewhat. Check out
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> It doesn't say do this immediately though.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:D8EF5EEF-A0D7-4F69-B519-FD970A02F509@.microsoft.com...
> Transactional replication; immediate updating subscribers disallowed. Each
> article in its own publication... independent agents.
> agent properly. Error: "Another snapshot agent for the publication is
> running". Agent then fails after total retries (default is 10... does not
> matter what I set it to).
> the Publisher(s) immediately thereafter if you wish to create new
> publications. I have not seen that documented however... anyone know?
>
>
|||Let me try to repro this tonight.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> Thanks for the response Hilary.
> I read that article; it doesn't specify that publishers MUST be upgraded
to 2000 once the Distributor has been upgraded. That's the issue here: we
have an upgraded Distributor, but we cannot upgrade the Publisher for some
time. In the meantime, we need to create new publications. We are unable to
do so however, due to the error I outlined. It APPEARS that once you upgrade
your Distributor, you have to upgrade your Publisher before you can create
new publications. That doesn't seem right, but I have yet to find evidence
of its validity.
> FWIW: one of the DBAs here reported the same error after applying
SPs/hotfixes on the Distributor w/o applying same to Publisher.[vbcol=seagreen]
> "Hilary Cotter" wrote:
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Each[vbcol=seagreen]
snapshot[vbcol=seagreen]
not[vbcol=seagreen]
fine.[vbcol=seagreen]
upgrade[vbcol=seagreen]
|||Were you able to reproduce the error?
Many thanks!!
X
"Hilary Cotter" wrote:

> Let me try to repro this tonight.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
> news:9034767C-3D75-4C7B-B975-510CC8AFDFDD@.microsoft.com...
> to 2000 once the Distributor has been upgraded. That's the issue here: we
> have an upgraded Distributor, but we cannot upgrade the Publisher for some
> time. In the meantime, we need to create new publications. We are unable to
> do so however, due to the error I outlined. It APPEARS that once you upgrade
> your Distributor, you have to upgrade your Publisher before you can create
> new publications. That doesn't seem right, but I have yet to find evidence
> of its validity.
> SPs/hotfixes on the Distributor w/o applying same to Publisher.
> http://msdn.microsoft.com/library/de...grade_45ir.asp
> Each
> snapshot
> not
> fine.
> upgrade
>
>
|||No, I was unable to get the error. What error did you get, and what service
packs were you running?
Where you using an Administrative Link password?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Xfonhe" <Xfonhe@.discussions.microsoft.com> wrote in message
news:C359EA3E-0ED4-417C-B1A9-F4F015253BD0@.microsoft.com...[vbcol=seagreen]
> Were you able to reproduce the error?
> Many thanks!!
> X
>
> "Hilary Cotter" wrote:
upgraded[vbcol=seagreen]
we[vbcol=seagreen]
some[vbcol=seagreen]
to[vbcol=seagreen]
upgrade[vbcol=seagreen]
create[vbcol=seagreen]
evidence[vbcol=seagreen]
http://msdn.microsoft.com/library/de...grade_45ir.asp[vbcol=seagreen]
Publisher.[vbcol=seagreen]
disallowed.[vbcol=seagreen]
is[vbcol=seagreen]
does[vbcol=seagreen]
work[vbcol=seagreen]
know?[vbcol=seagreen]
sql

replication error

i have a problem

when i select distributor and click next

"SQL server agent on 'PC-...' currently uses the system account, which causes replication between servers to fail. In te following dialog box, specify another account for the Service startup account"

and

"SQL server enterprise manager couldn not configure 'PC-...' as the distributor for 'PC-...'

Error 18483: Could not connect to server 'PC-...' because 'distributor admin' is not defined as a remote login at the server."

what can i do?I would start by setting up the sql server agent to use a domain user account. I am no replication expert. Not yet anyways. That project is coming soon.|||My suggestions are:

1. Create a domain user specifically for SQL replication, and no other purpose
2. Before you get a new machine involved with replication, make that domain user a local administrator on the new machine.
3. Start the SQL Agent on the machine that is the distributor using the domain accout from step 1.

Depending on how far you got with the replication installation, you may want to reinstall Replication. I know that I would!

-PatP

Replication Distributor Version

What is the minimum SQL Server version I can use on my replication distributor server? My publisher and subscriber(s) will all be Enterprise Edition (64 bit). However, it seems awefully wasteful to put Enterprise Edition on a box that is just used for distribution. That's another $15K for a server that doesn't do much. Can I use a lower version (Express, Developer, Standard)?

Thank you,

Ben Wolf

This page (http://msdn2.microsoft.com/en-us/library/ms143761.aspx) lists the features (including replication) that are supported by various editions of SQL server. (Go all the way to the bottom for replication)

Basically, Express is not supproted for distributor. Developer edition includes all the functionalities of Enterprise edition SQL server, but it is only licensed for development and/or test purpose, not as a production server.

Peng

|||

Thank you, Peng. This is exactly what I needed. Looks like SE is the one for my distributor.

Thanks again,

Ben

sql

Replication Distributor Version

What is the minimum SQL Server version I can use on my replication distributor server? My publisher and subscriber(s) will all be Enterprise Edition (64 bit). However, it seems awefully wasteful to put Enterprise Edition on a box that is just used for distribution. That's another $15K for a server that doesn't do much. Can I use a lower version (Express, Developer, Standard)?

Thank you,

Ben Wolf

This page (http://msdn2.microsoft.com/en-us/library/ms143761.aspx) lists the features (including replication) that are supported by various editions of SQL server. (Go all the way to the bottom for replication)

Basically, Express is not supproted for distributor. Developer edition includes all the functionalities of Enterprise edition SQL server, but it is only licensed for development and/or test purpose, not as a production server.

Peng

|||

Thank you, Peng. This is exactly what I needed. Looks like SE is the one for my distributor.

Thanks again,

Ben

Replication Distributor Job History is shocking!

Sorry for the odd format but that is what happens when you paste here! This is data from our Job History for a "Replication Distributor" Job Step Type. Here is how I interprete it: The job step started on the 19th. It restarted itself twice on the 20th. Then it restarted itself twice on the 21st. Then it restarted itself 3 times on the 22nd. And finally it restarted itself on the 23rd. Notice the progression of Retries_Attempted. When Retries_Attempted exceeds 10, the step will fail and the service will shut-down, which is what I have seen in the past. I conclude the Advanced Settings of Retry Attempts is not per "Event" but "total tries no matter how many successful retries are encountered." I thought a successful retry would reset the Retries_Attempted. But it does not. Is this correct? This implies that to keep the distributor up, we should set the Retry Attempts" setting to its max!

Status Run_Date Run_Time Run_Duration Retries_attempted

1

1/19/2007

44025

0

0

2

1/20/2007

31200

1429

1

2

1/20/2007

32729

234250

2

2

1/21/2007

31119

1558

3

2

1/21/2007

32817

234225

4

2

1/22/2007

31142

1513

5

2

1/22/2007

32755

508

6

2

1/22/2007

33403

233615

7

2

1/23/2007

31118

1503

8

2

1/23/2007

32721

501

9

Topping this one so I can remember it, will look into it.

Replication Distributor

I was wondering if you could set up multiple distributors for one database?
For example:
Server A needs to publish data to server B so I set up server A's distributor as server B.
Later Server A needs to publish data to server C so I want to set up the distributor for this replication to use server C.
I don't think this is possible as you need to set up the distributor for the entire DB when its created not for each individual replication job.
Any help is appreciated.
NickTry the link below for some replication info. Hope this helps.
http://www.replicationanswers.com/

Replication between SQL2000 and SQL2005

I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:

Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802

Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.

The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment. This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005. I have been diligently comparing our production environment to my other environment and have yet to find differences.

Has anyone else seen an error similiar to this? Any insight would be appreciated.

Thanks in advance,

Max

http://support.microsoft.com/kb/295027

HTH

Vishal

|||I'm having the same problem, and it's persisting even after setting the 8599 trace on. Were you able to get yours to work? If so, how?
sql

Replication Between Different Versions of SQL Server causes access denied error.

I am getting the following error:
SQL Server does not exist or access denied. Error number 17.
The publication and distributor is SQL Server 7.0 from internal lan.
The subscriber is SQL Server 2000 from external wan outside of the firewall.
Using SQL Server Enterprise Manager 7.0 on the publication server will not
allow me to register the subscriber as it says "You must upgrade your SQL
Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
connecti to this server". But if I use a non participanting SQL Server
Enterprise Manager 2000 within the internal lan it will allow me to register
and connect to the browser and I am able to view the database and its
tables.
In addition I can connect to the subscriber using Query Analyzer in version
7.0.
According to the MSDN under the section Replication Between Different
Versions of SQL Server, I fit the same criteria as Combination 2 for
snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
7.0/Subscriber SQL Server 2000.
So why does the push subscription fail?
Regards
Peter
Hello Peter,
This is expected behavior when trying to access SQL Server 2000 from SQL
7.0 version of SQL DMO. You will be able to connect to SQL Server 2000 from
SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
resolution is to upgrade the client tools to SQL 2000.
Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
on a different server to work around this issue.
Combination 2 you referred to does not indicate that you could configure
this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
you could only configure this from a SQL 2000 Enterprise Manager.
Hope this helps.
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/tec...rview/40010469
Others: https://partner.microsoft.com/US/tec...pportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/default...national.aspx.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>Subject: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Mon, 31 Oct 2005 16:30:49 +1000
>Lines: 32
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17479
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I am getting the following error:
>SQL Server does not exist or access denied. Error number 17.
>The publication and distributor is SQL Server 7.0 from internal lan.
>The subscriber is SQL Server 2000 from external wan outside of the
firewall.
>Using SQL Server Enterprise Manager 7.0 on the publication server will not
>allow me to register the subscriber as it says "You must upgrade your SQL
>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
>connecti to this server". But if I use a non participanting SQL Server
>Enterprise Manager 2000 within the internal lan it will allow me to
register
>and connect to the browser and I am able to view the database and its
>tables.
>In addition I can connect to the subscriber using Query Analyzer in
version
>7.0.
>According to the MSDN under the section Replication Between Different
>Versions of SQL Server, I fit the same criteria as Combination 2 for
>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL Server
>7.0/Subscriber SQL Server 2000.
>So why does the push subscription fail?
>Regards
>Peter
>
>
>
>
|||I have created the publication on SQL Server 7.0 server via SQL Server 2000
Enterprise and created a push subscription. As soon as the snapshoot was
ready to send I get the same error "The process could not connect to
Subscriber" (SQL Server does not exist or access denied. Error number: 17)
even though I could setup the subscriber and select the destination
database.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This is expected behavior when trying to access SQL Server 2000 from SQL
> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
> from
> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
> resolution is to upgrade the client tools to SQL 2000.
> Also, as you have mentioned, you could use a SQL 2000 Eneterprise Manager
> on a different server to work around this issue.
> Combination 2 you referred to does not indicate that you could configure
> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL, actually
> you could only configure this from a SQL 2000 Enterprise Manager.
> Hope this helps.
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
> Business-Critical Phone Support (BCPS) provides you with technical phone
> support at no charge during critical LAN outages or "business down"
> situations. This benefit is available 24 hours a day, 7 days a week to all
> Microsoft technology partners in the United States and Canada.
> This and other support options are available here:
> BCPS:
> https://partner.microsoft.com/US/tec...rview/40010469
> Others: https://partner.microsoft.com/US/tec...pportoverview/
> If you are outside the United States, please visit our International
> Support page:
> http://support.microsoft.com/default...national.aspx.
> ================================================== ===
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> access denied error.
> firewall.
> register
> version
>
|||Hello Peter,
This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
SP1 if it is on Win2000 server.
899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
http://support.microsoft.com/?id=899456
Also, please use SQL client network utility to make sure TCPIP and named
pipes are enabled. Also, you may want to create an Alais for SQL 2000
(subscriber) to test the situation.
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
--
>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
>Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
>Date: Tue, 1 Nov 2005 10:09:26 +1000
>Lines: 117
>X-Priority: 3
>X-MSMail-Priority: Normal
>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>X-RFC2646: Format=Flowed; Original
>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
>Newsgroups: microsoft.public.sqlserver.replication
>NNTP-Posting-Host: 61.88.17.230
>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
>X-Tomcat-NG: microsoft.public.sqlserver.replication
>I have created the publication on SQL Server 7.0 server via SQL Server
2000[vbcol=seagreen]
>Enterprise and created a push subscription. As soon as the snapshoot was
>ready to send I get the same error "The process could not connect to
>Subscriber" (SQL Server does not exist or access denied. Error number: 17)
>even though I could setup the subscriber and select the destination
>database.
>Regards
>Peter
>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
all[vbcol=seagreen]
https://partner.microsoft.com/US/tec...rview/40010469[vbcol=seagreen]
https://partner.microsoft.com/US/tec...pportoverview/[vbcol=seagreen]
not
>
>
|||The penny dropped!
Whilst I did configure SQL client network on my development pc to browse the
external database I didn't realise I had to configure it again on the
distributor server. The replication wizard sort of misled me as part of it
process what to nonimate the subscriber which it found on my development pc
whereas it should have failed it the distributor couldn't recognise it.
Regards
Peter
"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
> Hello Peter,
> This seems to be a MDAC issue. I suggest that you install latest MDAC 2.8
> SP1 if it is on Win2000 server.
> 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
> http://support.microsoft.com/?id=899456
> Also, please use SQL client network utility to make sure TCPIP and named
> pipes are enabled. Also, you may want to create an Alais for SQL 2000
> (subscriber) to test the situation.
> Regards,
> Peter Yang
> MCSE2000/2003, MCSA, MCDBA
> Microsoft Online Partner Support
> When responding to posts, please "Reply to Group" via your newsreader so
> that others may learn and benefit from your issue.
> ================================================== ===
>
> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>
> --
> <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
> access denied error.
> 2000
> all
> https://partner.microsoft.com/US/tec...rview/40010469
> https://partner.microsoft.com/US/tec...pportoverview/
> not
>
|||Hello Peter,
Glad to hear the issue is resolved. :-)
Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.
| From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
<exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
<uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
<bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl>
| Subject: Re: Replication Between Different Versions of SQL Server causes
access denied error.
| Date: Thu, 3 Nov 2005 10:00:07 +1000
| Lines: 190
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| X-RFC2646: Format=Flowed; Original
| Message-ID: <Ow70RmA4FHA.3540@.TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.replication
| NNTP-Posting-Host: 61.88.17.230
| Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP10.phx.gbl
| Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17574
| X-Tomcat-NG: microsoft.public.sqlserver.replication
|
| The penny dropped!
| Whilst I did configure SQL client network on my development pc to browse
the
| external database I didn't realise I had to configure it again on the
| distributor server. The replication wizard sort of misled me as part of
it
| process what to nonimate the subscriber which it found on my development
pc
| whereas it should have failed it the distributor couldn't recognise it.
|
| Regards
| Peter
|
| "Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| news:bCGcbYo3FHA.2984@.TK2MSFTNGXA01.phx.gbl...
| > Hello Peter,
| >
| > This seems to be a MDAC issue. I suggest that you install latest MDAC
2.8
| > SP1 if it is on Win2000 server.
| >
| > 899456 Release manifest for MDAC 2.8 Service Pack 1 (2.81.1117.6)
| > http://support.microsoft.com/?id=899456
| >
| > Also, please use SQL client network utility to make sure TCPIP and named
| > pipes are enabled. Also, you may want to create an Alais for SQL 2000
| > (subscriber) to test the situation.
| >
| > Regards,
| >
| > Peter Yang
| > MCSE2000/2003, MCSA, MCDBA
| > Microsoft Online Partner Support
| >
| > When responding to posts, please "Reply to Group" via your newsreader so
| > that others may learn and benefit from your issue.
| >
| > ================================================== ===
| >
| >
| >
| > This posting is provided "AS IS" with no warranties, and confers no
| > rights.
| >
| >
| > --
| >>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>References: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| > <exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl>
| >>Subject: Re: Replication Between Different Versions of SQL Server causes
| > access denied error.
| >>Date: Tue, 1 Nov 2005 10:09:26 +1000
| >>Lines: 117
| >>X-Priority: 3
| >>X-MSMail-Priority: Normal
| >>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>X-RFC2646: Format=Flowed; Original
| >>Message-ID: <uaV8Pin3FHA.700@.TK2MSFTNGP15.phx.gbl>
| >>Newsgroups: microsoft.public.sqlserver.replication
| >>NNTP-Posting-Host: 61.88.17.230
| >>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP15.phx.gbl
| >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.replication:17507
| >>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>
| >>I have created the publication on SQL Server 7.0 server via SQL Server
| > 2000
| >>Enterprise and created a push subscription. As soon as the snapshoot was
| >>ready to send I get the same error "The process could not connect to
| >>Subscriber" (SQL Server does not exist or access denied. Error number:
17)
| >>even though I could setup the subscriber and select the destination
| >>database.
| >>
| >>Regards
| >>Peter
| >>
| >>"Peter Yang [MSFT]" <petery@.online.microsoft.com> wrote in message
| >>news:exdZMBf3FHA.3220@.TK2MSFTNGXA01.phx.gbl...
| >>> Hello Peter,
| >>>
| >>> This is expected behavior when trying to access SQL Server 2000 from
SQL
| >>> 7.0 version of SQL DMO. You will be able to connect to SQL Server 2000
| >>> from
| >>> SQL 7.0 version of Query Analyzer, but not Enterprise Manager. The
| >>> resolution is to upgrade the client tools to SQL 2000.
| >>>
| >>> Also, as you have mentioned, you could use a SQL 2000 Eneterprise
| >>> Manager
| >>> on a different server to work around this issue.
| >>>
| >>> Combination 2 you referred to does not indicate that you could
configure
| >>> this in SQL 7 Enterprise Manager. Since it is from SQL 2000 BOL,
| >>> actually
| >>> you could only configure this from a SQL 2000 Enterprise Manager.
| >>>
| >>> Hope this helps.
| >>>
| >>> Peter Yang
| >>> MCSE2000/2003, MCSA, MCDBA
| >>> Microsoft Online Partner Support
| >>>
| >>> When responding to posts, please "Reply to Group" via your newsreader
so
| >>> that others may learn and benefit from your issue.
| >>>
| >>> ================================================== ===
| >>>
| >>> Business-Critical Phone Support (BCPS) provides you with technical
phone
| >>> support at no charge during critical LAN outages or "business down"
| >>> situations. This benefit is available 24 hours a day, 7 days a week to
| > all
| >>> Microsoft technology partners in the United States and Canada.
| >>>
| >>> This and other support options are available here:
| >>>
| >>> BCPS:
| >>>
| >
https://partner.microsoft.com/US/tec...rview/40010469
| >>>
| >>> Others:
| > https://partner.microsoft.com/US/tec...pportoverview/
| >>>
| >>> If you are outside the United States, please visit our International
| >>> Support page:
| >>> http://support.microsoft.com/default...national.aspx.
| >>>
| >>> ================================================== ===
| >>>
| >>> This posting is provided "AS IS" with no warranties, and confers no
| >>> rights.
| >>>
| >>>
| >>> --
| >>>>From: "Peter Lapic" <plapic@.shop-a-docket.com.au>
| >>>>Subject: Replication Between Different Versions of SQL Server causes
| >>> access denied error.
| >>>>Date: Mon, 31 Oct 2005 16:30:49 +1000
| >>>>Lines: 32
| >>>>X-Priority: 3
| >>>>X-MSMail-Priority: Normal
| >>>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
| >>>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
| >>>>X-RFC2646: Format=Flowed; Original
| >>>>Message-ID: <u130mSe3FHA.3136@.TK2MSFTNGP09.phx.gbl>
| >>>>Newsgroups: microsoft.public.sqlserver.replication
| >>>>NNTP-Posting-Host: 61.88.17.230
| >>>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFT NGP09.phx.gbl
| >>>>Xref: TK2MSFTNGXA01.phx.gbl
microsoft.public.sqlserver.replication:17479
| >>>>X-Tomcat-NG: microsoft.public.sqlserver.replication
| >>>>
| >>>>I am getting the following error:
| >>>>SQL Server does not exist or access denied. Error number 17.
| >>>>
| >>>>The publication and distributor is SQL Server 7.0 from internal lan.
| >>>>The subscriber is SQL Server 2000 from external wan outside of the
| >>> firewall.
| >>>>
| >>>>Using SQL Server Enterprise Manager 7.0 on the publication server will
| > not
| >>>>allow me to register the subscriber as it says "You must upgrade your
| >>>>SQL
| >>>>Enterprise Manager and SQL-DMO (SQLOLE) to SQL Server 2000(SQLDMO) to
| >>>>connecti to this server". But if I use a non participanting SQL Server
| >>>>Enterprise Manager 2000 within the internal lan it will allow me to
| >>> register
| >>>>and connect to the browser and I am able to view the database and its
| >>>>tables.
| >>>>
| >>>>In addition I can connect to the subscriber using Query Analyzer in
| >>> version
| >>>>7.0.
| >>>>
| >>>>According to the MSDN under the section Replication Between Different
| >>>>Versions of SQL Server, I fit the same criteria as Combination 2 for
| >>>>snapshot replication ie. Publisher SQL Server 7.0/Distributor SQL
Server
| >>>>7.0/Subscriber SQL Server 2000.
| >>>>
| >>>>So why does the push subscription fail?
| >>>>
| >>>>Regards
| >>>>Peter
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>>
| >>>
| >>
| >>
| >>
| >
|
|
|

Replication between 7.0 and 2005

Is it true that merge replication between SQL7.0 and 2005 is not supported?
I would like to setup SQL2005 as a publisher and distributor and SQL7.0 as a
subscriber.
Can someone confirm that please...
Other options in that case would be to use transactional replication or to
upgrade 7.0 to 2000 and then use merge replication.
Tom
Hi Tom,
Merge Replication in SQL Server 2005 does indeed support SQL 2005
distributor and SQL 2005 publisher with SQL 70 subscribers.
You'll want to check out the following topic in SQL Server 2005 BOL:
"Using Multiple Versions of SQL Server in a Replication Topology"
Hope this helps,
Tom
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tom" wrote:

> Is it true that merge replication between SQL7.0 and 2005 is not supported?
> I would like to setup SQL2005 as a publisher and distributor and SQL7.0 as a
> subscriber.
> Can someone confirm that please...
> Other options in that case would be to use transactional replication or to
> upgrade 7.0 to 2000 and then use merge replication.
> Tom
>
>

Replication between 2 SBS (small business server) Trusts

I have SQL running on SBS Server A. It is a publisher and a distributor of a
database that has 1 subscriber a SQL Server running on SBS Server 2. How does
one replicate in a untrusted enviroment.
there was also a free ware dll that allowed sql to invoke the ras dialer..
does anyone have the valid url to this?
-- Current Standings (taken from my post on the SBS Site) --
I posted on the SQL server site and was referred to Q321822. This Q article
had only 2 paragraphs that mentioned connection between 2 non-trusted
domains. essentially it suggested creating a server agent account on both
machines. it says you 'must set up pass through authentication'. There is was
some data missing from the article.
1) How do you set up 'pass through authentication" via a SQL Agent Account?
2) Assuming 'Pass Through' is enabled, how does one reference the SBS Server?
a) 'Local Area Connection' IP Address (not likely) 192.168.0.2 (private)
b) Local Server Name. MyServer
c) FQDN MyCompany.MyServer.
d) Public Resolvable Domain Name MyCompany.com
i) With extension .com
ii) Without extension
e) Network Connection IP Address of Server 68.XXX.XXX.217 (Public)
None of the above options worked for me, response "Server doesn't exsist or
Access denied" (which is it? MDAC doesn't even know if the Server Exists?
What’s with that?)
I found the information sources lacking. None seemed to illustrate a Point A
to Point Z scenario on SBS. This is not a Version 1 product and being that
trusts are not allowed it's conceivable that many companies would be
attempting non-trusted replication.
PS, I've checked Kbase, SQL BOL, 4 MVP sites, Newsgroups, Online Concierge
and SSWUG. I was exposed to answer bits, in addition to the above mentioned
options, there was mentioned RPC over TCP, RPC over UDP, Linked server
options, Remote server options, SSL options, replication via Backup and
Restore, replication via output files, Replication via terminal services
etc..
Now, I'm a person of reasonable intelligence, willing to the put the time
and effort into research, but am I wrong here when I suggest that the
documentation is all over the map? I will be posting this reply in both the
SQL Connectivity and SQL Replication newsgroups. In my opinion Microsoft does
a great job of documenting the scope and detail of it's technologies but
often falls short when it comes to implementation documentation. They are 2
distinct types of technical writing. I love Microsoft products and take the
time to scribe my opinion in hopes that it will yield stronger product line.
With that said, I ask again for a -concise, thorough, list of steps on how
to set up a push transaction based SQL replication subscription between 2
Non-trusted Small Business Servers?
"Mario Michela" wrote:

> I have SQL running on SBS Server A. It is a publisher and a distributor of a
> database that has 1 subscriber a SQL Server running on SBS Server 2. How does
> one replicate in a untrusted enviroment.
> there was also a free ware dll that allowed sql to invoke the ras dialer..
> does anyone have the valid url to this?

Replication between 2 SBS (small business server) Trusts

I have SQL running on SBS Server A. It is a publisher and a distributor of a
database that has 1 subscriber a SQL Server running on SBS Server 2. How does
one replicate in a untrusted enviroment.
there was also a free ware dll that allowed sql to invoke the ras dialer..
does anyone have the valid url to this?
Mario,
have a look at this article: http://support.microsoft.com/?id=321822
(HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
Domains or Across the Internet).
HTH,
Paul Ibison
|||ooops i spoke too soon..
This Q article had only 2 paragraphs that mentioned connection between 2
non-trusted domains. essentially it suggested creating a server agent account
on both machines. it says you 'must set up pass through authentication'.
There is was some data missing from the article.
1) How do you set up 'pass through authentication" via a SQL Agent Account?
2) Assuming 'Pass Through' is enabled, how does one reference the SBS Server?
a) 'Local Area Connection' IP Address (not likely) 192.168.0.2 (private)
b) Local Server Name. MyServer
c) FQDN MyCompany.MyServer.
d) Public Resolvable Domain Name MyCompany.com
i) With extension .com
ii) Without extension
e) Network Connection IP Address of Server 68.XXX.XXX.217 (Public)
None of the above options worked for me, response "Server doesn't exsist or
Access denied" (which is it? MDAC doesn't even know if the Server Exists?
What’s with that?)
I found the information sources lacking. None seemed to illustrate a Point A
to Point Z scenario on SBS. This is not a Version 1 product and being that
trusts are not allowed it's conceivable that many companies would be
attempting non-trusted replication.
PS, I've checked Kbase, SQL BOL, 4 MVP sites, Newsgroups, Online Concierge
and SSWUG. I was exposed to answer bits, in addition to the above mentioned
options, there was mentioned RPC over TCP, RPC over UDP, Linked server
options, Remote server options, SSL options, replication via Backup and
Restore, replication via output files, Replication via terminal services
etc..
Now, I'm a person of reasonable intelligence, willing to the put the time
and effort into research, but am I wrong here when I suggest that the
documentation is all over the map? I will be posting this reply in both the
SQL Connectivity and SQL Replication newsgroups. In my opinion Microsoft does
a great job of documenting the scope and detail of it's technologies but
often falls short when it comes to implementation documentation. They are 2
distinct types of technical writing. I love Microsoft products and take the
time to scribe my opinion in hopes that it will yield stronger product line.
With that said, I ask again for a -concise, thorough, list of steps on how
to set up a push transaction based SQL replication subscription between 2
Non-trusted Small Business Servers?
"Paul Ibison" wrote:

> Mario,
> have a look at this article: http://support.microsoft.com/?id=321822
> (HOW TO: Replicate Between Computers Running SQL Server in Non-Trusted
> Domains or Across the Internet).
> HTH,
> Paul Ibison
>
>
|||Mario,
much of this article applies to your situation, although admittedly it's not
immediately obvious
Remember to set up the alias using the client network utility, rather than
the IP address.
Try using sql server authentication rather than pass-through.
Set up FTP forthe initial snapshot if it isn't a nosync one.
If you want things a little easier, you can use a VPN.
HTH,
Paul Ibison
"Mario Michela" <MarioMichela@.discussions.microsoft.com> wrote in message
news:75DB0BE3-9379-4C57-BC35-3E62104060E7@.microsoft.com...
> ooops i spoke too soon..
> This Q article had only 2 paragraphs that mentioned connection between 2
> non-trusted domains. essentially it suggested creating a server agent
account
> on both machines. it says you 'must set up pass through authentication'.
> There is was some data missing from the article.
> 1) How do you set up 'pass through authentication" via a SQL Agent
Account?
> 2) Assuming 'Pass Through' is enabled, how does one reference the SBS
Server?
> a) 'Local Area Connection' IP Address (not likely) 192.168.0.2
(private)
> b) Local Server Name. MyServer
> c) FQDN MyCompany.MyServer.
> d) Public Resolvable Domain Name MyCompany.com
> i) With extension .com
> ii) Without extension
> e) Network Connection IP Address of Server 68.XXX.XXX.217 (Public)
> None of the above options worked for me, response "Server doesn't exsist
or
> Access denied" (which is it? MDAC doesn't even know if the Server Exists?
> What's with that?)
> I found the information sources lacking. None seemed to illustrate a Point
A
> to Point Z scenario on SBS. This is not a Version 1 product and being that
> trusts are not allowed it's conceivable that many companies would be
> attempting non-trusted replication.
> PS, I've checked Kbase, SQL BOL, 4 MVP sites, Newsgroups, Online Concierge
> and SSWUG. I was exposed to answer bits, in addition to the above
mentioned
> options, there was mentioned RPC over TCP, RPC over UDP, Linked server
> options, Remote server options, SSL options, replication via Backup and
> Restore, replication via output files, Replication via terminal services
> etc..
> Now, I'm a person of reasonable intelligence, willing to the put the time
> and effort into research, but am I wrong here when I suggest that the
> documentation is all over the map? I will be posting this reply in both
the
> SQL Connectivity and SQL Replication newsgroups. In my opinion Microsoft
does
> a great job of documenting the scope and detail of it's technologies but
> often falls short when it comes to implementation documentation. They are
2
> distinct types of technical writing. I love Microsoft products and take
the
> time to scribe my opinion in hopes that it will yield stronger product
line.[vbcol=seagreen]
> With that said, I ask again for a -concise, thorough, list of steps on how
> to set up a push transaction based SQL replication subscription between 2
> Non-trusted Small Business Servers?
>
>
> "Paul Ibison" wrote:
|||Yes the article is good in many respects, i learned a lot, perhaps a bit too
much in that it introduced several differernt approches. ok, now you mention
alaising using the client utility? there are only 3 boxes, can you give me an
example of what to put in them. remember this is a push from the publisher.
so wouldn't the publisher connect to the client server? MS is mixing
mediphores. Am i wrong to assume that data flows from a server (publisher)
using a distributer to a client (subscriber). This can't be that difficult.
currently i have created a domain user (admin rights) on both servers, both
have the same username and password. I"ve also gone into routing/ip
routing/nat/basic firewall and in the public ip address added the following:
Desc of Svc: SQLServer
On public Interface
Using TCP
Incoming Port: 1433
Priv IP:192.168.10.2
OutGoing port: 1433
Desc of Svc: SQLServer UDP
On public Interface
Using UDP
Incoming Port: 1434
Priv IP:192.168.10.2
OutGoing port: 1434
I've been trying to test my creating a UDL. (both servers have an Admin
account, both have the same password -so windows authenticed).
now because the documentation can't seem to use consistant mediphoores. i've
done every on both server in a despirate attempt to shotgun my way into
success (not the best way to go about things but look at what i've got to
work with.)
ok now on to ailases. (again i will build identicle alaises on both machines
untill advised otherwise).
Server Alais: aawwserver (machine/server name)
Server Name: aawwserver
Dynamically Determine port.
is there a wizard or some sort of tool i should be using for this?
i can't believe that this entire process up till this point is covered in a
single paragraph.
At one point I had the whole set up using a vpn (not practicle) but the
transaction agent failed because it couldn't drop a function because it was
being used by replication(funny i never dropped it? why should the
replication agent) so i removed replication and attempted to start from
scratch. I've been at this for aprox 40 hours non-stop not including the 4
days prior. if this is what is concidered a reasonable implimemtation I have
to question the product. So for the sake of my sanity Please Please, provide
an example. using the following 'aliases' (sorry i couldn't resist)
-Example Name-
Publisher:
Machine Name: srvPub
Public IP: 0.0.0.1
Private IP: 0.0.0.2
database: DBPub
Domain: DomPub.com
sql Agent Acct: agtRep
sql Agent Password: agt@.Rep
Subscriber:
Machine Name: srvSub
Public IP: 0.0.0.3
Private IP: 0.0.0.4
database: DBSub
Domain: DomSub.com
sql Agent Acct: agtRep
sql Agent Password: agt@.Rep
The Challange:
Can anyone take the variables I've listed and form them into a coherent set
of instructions.
This has been more stressfull then having to sing the national anthem naked
at the olympics..
Thanks you so very much, for your assistance. .
"Paul Ibison" wrote:

> Mario,
> much of this article applies to your situation, although admittedly it's not
> immediately obvious
> Remember to set up the alias using the client network utility, rather than
> the IP address.
> Try using sql server authentication rather than pass-through.
> Set up FTP forthe initial snapshot if it isn't a nosync one.
> If you want things a little easier, you can use a VPN.
> HTH,
> Paul Ibison
>
> "Mario Michela" <MarioMichela@.discussions.microsoft.com> wrote in message
> news:75DB0BE3-9379-4C57-BC35-3E62104060E7@.microsoft.com...
> account
> Account?
> Server?
> (private)
> or
> A
> mentioned
> the
> does
> 2
> the
> line.
>
>
sql

Replication between 2 SBS (small business server) Trusts

I have SQL running on SBS Server A. It is a publisher and a distributor of a
database that has 1 subscriber a SQL Server running on SBS Server 2. How doe
s
one replicate in a untrusted enviroment.
there was also a free ware dll that allowed sql to invoke the ras dialer..
does anyone have the valid url to this?-- Current Standings (taken from my post on the SBS Site) --
I posted on the SQL server site and was referred to Q321822. This Q article
had only 2 paragraphs that mentioned connection between 2 non-trusted
domains. essentially it suggested creating a server agent account on both
machines. it says you 'must set up pass through authentication'. There is wa
s
some data missing from the article.
1) How do you set up 'pass through authentication" via a SQL Agent Account?
2) Assuming 'Pass Through' is enabled, how does one reference the SBS Server
?
a) 'Local Area Connection' IP Address (not likely) 192.168.0.2 (private)
b) Local Server Name. MyServer
c) FQDN MyCompany.MyServer.
d) Public Resolvable Domain Name MyCompany.com
i) With extension .com
ii) Without extension
e) Network Connection IP Address of Server 68.XXX.XXX.217 (Public)
None of the above options worked for me, response "Server doesn't exsist or
Access denied" (which is it? MDAC doesn't even know if the Server Exists?
What’s with that?)
I found the information sources lacking. None seemed to illustrate a Point A
to Point Z scenario on SBS. This is not a Version 1 product and being that
trusts are not allowed it's conceivable that many companies would be
attempting non-trusted replication.
PS, I've checked Kbase, SQL BOL, 4 MVP sites, Newsgroups, Online Concierge
and SSWUG. I was exposed to answer bits, in addition to the above mentioned
options, there was mentioned RPC over TCP, RPC over UDP, Linked server
options, Remote server options, SSL options, replication via Backup and
Restore, replication via output files, Replication via terminal services
etc..
Now, I'm a person of reasonable intelligence, willing to the put the time
and effort into research, but am I wrong here when I suggest that the
documentation is all over the map? I will be posting this reply in both the
SQL Connectivity and SQL Replication newsgroups. In my opinion Microsoft doe
s
a great job of documenting the scope and detail of it's technologies but
often falls short when it comes to implementation documentation. They are 2
distinct types of technical writing. I love Microsoft products and take the
time to scribe my opinion in hopes that it will yield stronger product line.
With that said, I ask again for a -concise, thorough, list of steps on how
to set up a push transaction based SQL replication subscription between 2
Non-trusted Small Business Servers?
"Mario Michela" wrote:

> I have SQL running on SBS Server A. It is a publisher and a distributor of
a
> database that has 1 subscriber a SQL Server running on SBS Server 2. How d
oes
> one replicate in a untrusted enviroment.
> there was also a free ware dll that allowed sql to invoke the ras dialer..
> does anyone have the valid url to this?

Replication based on subscribers informations

Hello all,
I want to use replication between one main (publisher/distributor) SQL server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over the internet.
1)
There is some tables on the main SQL server that need to be propagated to the other servers based on (Insert/Update/Delete) transactions. I called that kind static tables.
Example: there is a table called 'Events' wich have to propagate its data to ALL the subscribers.
2)
And there is another tables on the main SQL server that have data that must be propagated to clients (subscribers) based on their informations. I called that kind dynamic tables.
Example: there is table called 'Operations' wich have a column called 'Entreprise_ID', that have data about all operations concerning some entreprises. Those real entreprises have also SQL servers (wich shall be used as subscribers) to get data from the m
ain server, but Entreprise X must receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
I'm new into replication, i've searched over the web and understood it a little bit, so i thought that in my case i should use Transactional Replication if we suppose that data is a bit critical and must be refreshed on all the servers.
i configured the main SQL server with a subsriber to test transactional replication over the internet concerning what i called 'static tables' , it works very well.
But for applying filters on the 'dynamic tables' i don't know how to do it, i've seen that i can apply 'vertical filters' on those tables, but i don't know how to associate it with subscribers informations in order to send only appropriate data to each su
bscriber.
Thanx in advance for any comment.
a vertical filter is where you replicate a subset of the columns, a
horizontal filter is a where you replicate a subset of the rows in the
table.
I take it you want to do horizontal filtering or partitioning. To do this
right click on your publication and select publication properties, click on
the filter rows tab. locate the tables you wish to filter, and click on the
browse button to the right of the table/article. You will get the specify
filter dialog. Enter your filter there.
After your have made your changes, regenerate your snapshot, and distribute
it to your subscribers.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> Hello all,
> I want to use replication between one main (publisher/distributor) SQL
server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
the internet.
> 1)
> There is some tables on the main SQL server that need to be propagated to
the other servers based on (Insert/Update/Delete) transactions. I called
that kind static tables.
> Example: there is a table called 'Events' wich have to propagate its data
to ALL the subscribers.
> 2)
> And there is another tables on the main SQL server that have data that
must be propagated to clients (subscribers) based on their informations. I
called that kind dynamic tables.
> Example: there is table called 'Operations' wich have a column called
'Entreprise_ID', that have data about all operations concerning some
entreprises. Those real entreprises have also SQL servers (wich shall be
used as subscribers) to get data from the main server, but Entreprise X must
receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
>
> I'm new into replication, i've searched over the web and understood it a
little bit, so i thought that in my case i should use Transactional
Replication if we suppose that data is a bit critical and must be refreshed
on all the servers.
> i configured the main SQL server with a subsriber to test transactional
replication over the internet concerning what i called 'static tables' , it
works very well.
> But for applying filters on the 'dynamic tables' i don't know how to do
it, i've seen that i can apply 'vertical filters' on those tables, but i
don't know how to associate it with subscribers informations in order to
send only appropriate data to each subscriber.
> Thanx in advance for any comment.
|||Hello,
Yes Hilary, indeed, i made a mistake talking about 'vertical filtering', i must use 'horizontal filtering' but, the problem is not how to do it, by the wizard or something like that, i already know how to do it, my problem is a conception problem. How to
define an 'dynamic horizontal filter' for an article of a Transactional Replication, and this filter must propagate data dynamically based on subscribers informations.
For example:
Let's suppose that i have a table called 'Operations' that have 3 columns.
Op_ID--Date--Entreprise
1--01/05/2004--1
2--02/05/2004--2
3--15/05/2004--1
4--15/06/2004--3
5--19/06/2004--3
6--01/07/2004--2
so in this case let's suppose that we have 3 subscribers, hence 3 SQL servers each server in one Entreprise.
My need is, if a transaction happened in the table 'Operations' it have to be automatically propagated to the concerned entreprise. eg: there is one new row added '7--04/07/2004--2', this row have to be sent only to Subsriber number 2 because the o
ther subscribers don't need this data.
then, we'll have as a result, after synchronisation:
At Subscriber #1:
1--01/05/2004--1
3--15/05/2004--1
At Subscriber #2:
2--02/05/2004--2
6--01/07/2004--2
7--04/07/2004--2 (the new row)
And at Subscriber #3:
4--15/06/2004--3
5--19/06/2004--3
If anyone can tell me how to manage that problem, it will be a really Great help for me.
Sorry about my english level, it's my third language.
Thanks in advance for your comments.
"Hilary Cotter" wrote:

> a vertical filter is where you replicate a subset of the columns, a
> horizontal filter is a where you replicate a subset of the rows in the
> table.
> I take it you want to do horizontal filtering or partitioning. To do this
> right click on your publication and select publication properties, click on
> the filter rows tab. locate the tables you wish to filter, and click on the
> browse button to the right of the table/article. You will get the specify
> filter dialog. Enter your filter there.
> After your have made your changes, regenerate your snapshot, and distribute
> it to your subscribers.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "The_Flanker" <The_Flanker@.discussions.microsoft.com> wrote in message
> news:54A173EE-B094-46BA-B841-A57F95ADE772@.microsoft.com...
> server 2000 on a Windows 2003 Server, and a group of SQL server 2000 over
> the internet.
> the other servers based on (Insert/Update/Delete) transactions. I called
> that kind static tables.
> to ALL the subscribers.
> must be propagated to clients (subscribers) based on their informations. I
> called that kind dynamic tables.
> 'Entreprise_ID', that have data about all operations concerning some
> entreprises. Those real entreprises have also SQL servers (wich shall be
> used as subscribers) to get data from the main server, but Entreprise X must
> receive only data based on 'SELECT * from Operations WHERE Entreprise_ID=X'
> little bit, so i thought that in my case i should use Transactional
> Replication if we suppose that data is a bit critical and must be refreshed
> on all the servers.
> replication over the internet concerning what i called 'static tables' , it
> works very well.
> it, i've seen that i can apply 'vertical filters' on those tables, but i
> don't know how to associate it with subscribers informations in order to
> send only appropriate data to each subscriber.
>
>

Replication Automation...

My Scenarion is like this.

I have configured replication with a publication database, a distributor database and a few subscriber databases.
The replication model is snapshot + transactional replciation.

Now i want to check using system stored procedures as to whether the replication is configured and running properly.

What are the system stored procedures which can be used and how do use them??

Basically i wanted to automate the check of replciation. How can this be done?

--Sandu.Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.|||Originally posted by pushistik
Try MSdistribution_history table in he Distribution database. The "comments" and the "runstatus" columns will give you the status of your replication agents.

Is there any other tables or system stored procedures which i can check to see the replciation details?

--Sandu.sql

Replication Architecture question - Pull subscriptions on a SQL cluster...

Greetings:

I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.

Any ideas on what we did wrong in the setup?

Thanks,

Lee Everest

www.texas2oo.com/sqlblog

can you check the c:\program files\Microsoft Sql Server\80\com folder to see if the dll's exist there? If not try to copy and register them on the failing node.|||

We could do that, but i'm afraid that after I try to install one, several (or dozens) of others might show up missing, meaning that we didn't install it properly in the first place. The specific error that we get is:

Cannot load the DLL replsetoriginator extended procedure, or one of the DLLs it references. Reason: 126(The specified module could not be found.). The step failed.

I have no idea which DLL this function is located but could probably find out.

|||This is a missing or corrupt dll. When I have encountered these in SQL FTS clusters I have had to copy the dll's to the failed not and reregister them.|||So you are saying that the setup is correct, and that if the box fails over replication should pick right back up?|||

A friend of mine responded, below. Apparently replication shouldn’t miss a beat if failover occurs:

Never had this issue before. We are running 4 node clusters (active/active/active/passive) here and we have never had that issue. I'm going to guess it goes back to the install of SQL. I would just copy any missing dll from one node to the other. It sounds like SQL is looking for them so I think you should be safe just to copy them over.

Any thoughts?

|||That depends on the programming mechanism. Sometimes they need to be registered and sometimes they don't. It these dll's contain com compoents they will be, if not a simple file copy will work.

Replication Architecture question - Pull subscriptions on a SQL cluster...

Greetings:

I am working on a replication setup using transactional replication using with pull subscriptions and a separate distributor. The pull subscriptions are located on a SQL cluster using the virtual SQL Server name as the subscriber; when the box fails over, we get an error of missing replication.dlls. Researching further, we found that replication only works on one node of the subscriber.

Any ideas on what we did wrong in the setup?

Thanks,

Lee Everest

www.texas2oo.com/sqlblog

can you check the c:\program files\Microsoft Sql Server\80\com folder to see if the dll's exist there? If not try to copy and register them on the failing node.|||

We could do that, but i'm afraid that after I try to install one, several (or dozens) of others might show up missing, meaning that we didn't install it properly in the first place. The specific error that we get is:

Cannot load the DLL replsetoriginator extended procedure, or one of the DLLs it references. Reason: 126(The specified module could not be found.). The step failed.

I have no idea which DLL this function is located but could probably find out.

|||This is a missing or corrupt dll. When I have encountered these in SQL FTS clusters I have had to copy the dll's to the failed not and reregister them.|||So you are saying that the setup is correct, and that if the box fails over replication should pick right back up?|||

A friend of mine responded, below. Apparently replication shouldn’t miss a beat if failover occurs:

Never had this issue before. We are running 4 node clusters (active/active/active/passive) here and we have never had that issue. I'm going to guess it goes back to the install of SQL. I would just copy any missing dll from one node to the other. It sounds like SQL is looking for them so I think you should be safe just to copy them over.

Any thoughts?

|||That depends on the programming mechanism. Sometimes they need to be registered and sometimes they don't. It these dll's contain com compoents they will be, if not a simple file copy will work.

Replication and Full Text Indexes.

SQL 2000 SP 3a running in an active passive cluster on Windows 2003.
This is the publisher and distributor of several publication in
transaction replication. Subscriptions are pulled anonymously. I plan
on setting up Full Text indexes/search on the cluster, on several of
the replicated tables, and was wondering if anyone has had any
experiences with this or can find any reason it would not work. Since
it's SQL 2000 and timestamp values can be replicated I can't see that
this would be an issue (as with SQL 7) as I will be using Change
Tracking to keep the indexes up to date.
I'm not necessarily concerned about replicating the full text indexes,
although it may be of use later. My major concern is can I replicate
the tables that have full text indexes with out issue? Also, since the
tables are already published can I just add the full text indexes
without worry?
Charlie
Hi Charlie,
Sorry for the late response.
[vbcol=seagreen]
Yes, you could make the replication for a Replicated database/table
successfully.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
================================================== ===
This posting is provided "AS IS" with no warranties, and confers no rights.

Replication and backups

Hi All,
Recently one of our servers went down and I need to restore a database
from backup. This database was the main publisher and distributor for a
two server replication scheme. My question is if I restore the main
publisher's table from a backup need I do anything to the subscriber?
Will I have to restore the subscriber from the backup as well? Thanks
for any help with this!
Regards,
Troy
the distribution database contains the commands which should be applied on
the subscriber to keep it synchronized with the publisher. There is a
tracking column on the distributor which is compared with the same tracking
column on the subscriber. The distribution agent compares the value of this
column to determine what commands to apply on the subscriber.
If you restore an earlier version of the distribution database to the
subscriber and the commands are still in the distribution database
everything will be fine. If they have been removed you will have to
re-initialize your subscriber.
"Troy" <poster[no-spam]@.designguide.com> wrote in message
news:MPG.1b0469879be23858989687@.msnews.microsoft.c om...
> Hi All,
> Recently one of our servers went down and I need to restore a database
> from backup. This database was the main publisher and distributor for a
> two server replication scheme. My question is if I restore the main
> publisher's table from a backup need I do anything to the subscriber?
> Will I have to restore the subscriber from the backup as well? Thanks
> for any help with this!
> --
> Regards,
> Troy

Replication across different networks

Hi all,
I would be grateful if you all could help.
I have one server acting as publisher and distributor in one data center
and the other one center acting as subscriber in another data center.
I would like to perform replication between these two computers.
In order to perform replication, these two computers must be in the same
domain.
Are there any feasible solutions that i can perform replication betwen
these two computers across different networks?
Thx so much!!!
On 2004-12-06, Eric <Eric@.discussions.microsoft.com> wrote:
> Hi all,
> I would be grateful if you all could help.
> I have one server acting as publisher and distributor in one data center
> and the other one center acting as subscriber in another data center.
> I would like to perform replication between these two computers.
> In order to perform replication, these two computers must be in the same
> domain.
> Are there any feasible solutions that i can perform replication betwen
> these two computers across different networks?
If it's all TCP/IP, you don't have a problem. You don't have to have your
servers in same domain. I'm even doing replication over internet trough SSH
tunneling, via two unix machines, it works perfectly fine.
What you need to do is make sure you have TCP/IP connection to the remote
machine. You could test this by issuing this from command prompt:
telnet <remote.machine.ip> 1043
If you get connected (the screen goes blank), it means you're ok. If you get
'connection refused', 'connection closed by remote host', 'no route to
host', or similair, or you just kepp hanging at 'connecting...', it means
you don't have connection.
Once you established connection you should add the clients server ip to the
SQL Client Network utility with apropriate alias. Then you use that alias in
EM to establish replication between these two servers. Replication scripts
will use IP address stored in the client network utility when translating
the alias name you specified.
Mike
"I can do it quick. I can do it cheap. I can do it well. Pick any two."
Mario Splivalo
msplival@.jagor.srce.hr