Replication to a web server in DMZ

Hello,
We have active/active SQL2000 SP3a on a Win2k3 cluster in our domain. We
need to copy some tables from a
database out to our web server.
The web server, is SQL2000 SP3a on Windows 2000, SP4. It is a stand-alone
server out in the DMZ.
We get the following error when trying to do this:
SubSystem Message - Job 'VIRTUALSERVERNAME\INSTANCENAME-WEBSERVER-13'
(0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
'(null)'. Reason: Not associated with a trusted SQL Server connection. The
process could not connect to Subscriber 'WEBSERVER'.
Here are some thoughts we have. The SQL server in the domain runs on port
1303. The SQL server in the DMZ is running on a different port. Do the
ports need to match for the replication to occur?
We are not sure why this isn't working. Anyone have some input or some
documentation on how to set up replication to a web server in a DMZ. I am
sure this is something that is done at most companies, but we are missing
something.
Thanks in advance.
Hopefully this article will help out:
http://www.replicationanswers.com/InternetArticle.asp
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I would try SQL Authentication.
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
"JJP" <anonymous@.discussions.microsoft.com> wrote in message
news:%23%23dsWsu6FHA.1276@.TK2MSFTNGP09.phx.gbl...
> Hello,
> We have active/active SQL2000 SP3a on a Win2k3 cluster in our domain. We
> need to copy some tables from a
> database out to our web server.
> The web server, is SQL2000 SP3a on Windows 2000, SP4. It is a stand-alone
> server out in the DMZ.
> We get the following error when trying to do this:
> --
> SubSystem Message - Job 'VIRTUALSERVERNAME\INSTANCENAME-WEBSERVER-13'
> (0x5F1388610429AD4B8860D51DF8504198), step 2 - Login failed for user
> '(null)'. Reason: Not associated with a trusted SQL Server connection. The
> process could not connect to Subscriber 'WEBSERVER'.
> --
> Here are some thoughts we have. The SQL server in the domain runs on port
> 1303. The SQL server in the DMZ is running on a different port. Do the
> ports need to match for the replication to occur?
> We are not sure why this isn't working. Anyone have some input or some
> documentation on how to set up replication to a web server in a DMZ. I am
> sure this is something that is done at most companies, but we are missing
> something.
> Thanks in advance.
>
|||Thanks Paul, I am looking that over.
Hilary, the web server is using Windows and SQL, is that what you are
referring to?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ORJz63u6FHA.3416@.TK2MSFTNGP15.phx.gbl...
>I would try SQL Authentication.
> --
> 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
> "JJP" <anonymous@.discussions.microsoft.com> wrote in message
> news:%23%23dsWsu6FHA.1276@.TK2MSFTNGP09.phx.gbl...
>
sql

Replication target table shrinks to zero

I want to replicate a database to a subscriber that will be used as a read
only copy. The data has to be replicated as close to instantly as possible.
To do this I set up a database export of objects and data to populate the
subscriber, then I set up transactional replication. To verify that
replication is working successfully, I count the rows in each table, there
are 3 tables in total. For one of the tables, the replication completes but
almost immediately afterward, the table starts to shrink, and after several
hours the record count is zero. This isn't happening to the other two
tables, and I can't figure out why.

If you have no idea what might be causing this, perhaps you can suggest
some places to start looking. This is Win2k SP4 with SQL 2000 SP3.

Thanks much."someguy" <nospam@.thanks.com> wrote in message
news:Xns945451BFDB728nospamthankscom@.207.35.177.13 5...
> I want to replicate a database to a subscriber that will be used as a read
> only copy. The data has to be replicated as close to instantly as
possible.
> To do this I set up a database export of objects and data to populate the
> subscriber, then I set up transactional replication. To verify that
> replication is working successfully, I count the rows in each table, there
> are 3 tables in total. For one of the tables, the replication completes
but
> almost immediately afterward, the table starts to shrink, and after
several
> hours the record count is zero. This isn't happening to the other two
> tables, and I can't figure out why.

Sounds like there's a trigger or another user operating on that table.

Replication itself should not cause a table to shrink (unless of course the
original source table is shrinking.)

> If you have no idea what might be causing this, perhaps you can suggest
> some places to start looking. This is Win2k SP4 with SQL 2000 SP3.
> Thanks much.

Replication taking longer

I've got once a day snapshot replication working between 2 servers. I
take a copy of most of a database for MI and adhoc query processing
purposes. This has been working okay for several months now but I've
noticed that the time to load the database is gradually taking longer.
I don't think this is a product of the source database growing, because
whilst it has grown by 2-3%, the time taken to load the target database
is taking 80-90% longer than it did a month ago. I thought this may be
due to some physical file structure problem and I reindexed all tables
with no effect. I've also noticed that a DTS package that I'm using to
copy a much smaller database across to the same MI server has also
increased in duration over the same time period.
Any ideas to solve this would be welcome.
TIA
Laurence Breeze
Laurence,
indexing the tables wouldn't speed up the snapshot application at the
subscriber - actually quite the opposite . Perhaps you can monitor which
part of the process is taking more time than expected. You could just
monitor it using dbcc inputbuffer at the subscriber while it processes to
get a feel for it. If the DTS task has increased in time then it could be
network issues or the hardware on the subscriber. Loads of possibilities
here. You could copy a file to the server to test the network transfer
speed. I'd also monitor the disk writes/sec as maybe the problem lies there.
Disk fragmentation could be worth looking at, but I wouldn't expect this to
be such a major factor. Finally, check the general performance of the server
outside of data transfers - just adding data to a dummy table - and compare
to another (similar) server.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Replication snapshot error

The process could not bulk copy out of table '[dbo].[syncobj_0x4333454238413435]'.

Under Error Information, Full Message:
ODBCBCP/Driver version mismatch

This was working fine, until we had to drop the publication to make a change to the table. Now we get this, after creating the publication and subscriptions
We did apply Service Pack 3 recently (running SQL Server 2000).I suspect the .dll used in MDAC has the problem, refer to MS Data (http://www.microsoft.com/data/) link and download the compnent checker tool and check for any inconsistencies.

replication question

What is the thought on replicating over WAN links.
I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
like a local copy of the DB. So I would like to set up one-way replication.
The ISP says that replication is resource intensive. We have a 768K
Fractional T-1 they tell me that replication would suck up all that
bandwidth? There aren't a lot of updates that go on the DB in a day over
time there are a lot of updates but on a typical day there aren't a terrible
lot.
So I'm trying to gauge whether the ISP is right or if they aren't really
sure in what they are telling me. I would just
like to have a local copy of our DB on-site in-case the ISP's site goes down
for whatever reason.
Thanks,
Geo
Hi Marc
If you are applying normal transactional replication and don't have a lot of
transactions then your line would be more than adequate. The only bandwidth
intensive process would be when you initialize and apply a 1.2 GB snapshot
accross the line. Then SQL will use as much bandwidth as possible.
We've run transactional replication successfully on 256k lines without it
having a huge impact on the line. Again depending on the transaction volumes.
Regards
Cube
http://www.sqlserver.co.za
"marc" wrote:

> What is the thought on replicating over WAN links.
> I have a DB hosted at an off-site ISP it is about 1.2 GB in size. I would
> like a local copy of the DB. So I would like to set up one-way replication.
> The ISP says that replication is resource intensive. We have a 768K
> Fractional T-1 they tell me that replication would suck up all that
> bandwidth? There aren't a lot of updates that go on the DB in a day over
> time there are a lot of updates but on a typical day there aren't a terrible
> lot.
> So I'm trying to gauge whether the ISP is right or if they aren't really
> sure in what they are telling me. I would just
> like to have a local copy of our DB on-site in-case the ISP's site goes down
> for whatever reason.
> Thanks,
> Geo
>
>

replication question

Hi,
We basically want to have a second hot copy of our database and keep it
up-to-date via transactional replication (non-updating subsrcriber) We are
using SQL Server 7.0.
If we restore the latest backup of the published database onto the
subscriber so we have everything in sync to start, manual inserts on the
publisher database fail to replicate because of identity column insert
problems. When it trys to replicate the insert to our subsriber database, it
seems to blowup because the table on the subsriber has identity ids as well.
How do we get around this? We only want the identity columns on the
subsriber to function when we make the subscriber the live database in the
case that the publisher has problems. Otherwise during replication, we just
want the IDs origianlly issued on the publisher to replicate to the
subscriber as intended.
thanksRead BOL for details on this topic:
Managing Identity Values
You can manage identity values by:
a.. Allowing Microsoft® SQL ServerT 2000 replication to automatically
manage identity columns by dynamically allocating ranges of identity values
to the Publisher and all the Subscribers.
b.. Using the Transact-SQL NOT FOR REPLICATION option when defining the
identity column.
c.. Using a primary key other than the identity column (for example, a
composite key or a rowguid column), if an identity column is not necessary.
This strategy eliminates the overhead of managing identity columns on the
replicated data.
You have a few options as stated above. I'd suggest to use rowguid column if
you can, avoiding identity column in general, if possible, when it comes to
any type of replication. Otherwise, "NOT FOR REPLICATION" identity column
would be my 2nd pick. This will preserve the identity values coming from
Publisher, and Subscriber's indentity values won't get incremented via
replication.
However, once your production server goes down and your backup server goes
live, the Subscriber's ID values will increment because of INSERTS. Now, if
this column is your primary key, then there's a potential that ID values
from Subscriber's and Publisher's will collide at some point, unless you
plan ahead. Also, you have to think about sync this data back to the
Publisher once the machine is back online.
Hope it helps.
HH
"aaz" <aaz@.webcapacity.com> wrote in message
news:edCboTVjDHA.1740@.TK2MSFTNGP12.phx.gbl...
> Hi,
> We basically want to have a second hot copy of our database and keep it
> up-to-date via transactional replication (non-updating subsrcriber) We are
> using SQL Server 7.0.
> If we restore the latest backup of the published database onto the
> subscriber so we have everything in sync to start, manual inserts on the
> publisher database fail to replicate because of identity column insert
> problems. When it trys to replicate the insert to our subsriber database,
it
> seems to blowup because the table on the subsriber has identity ids as
well.
> How do we get around this? We only want the identity columns on the
> subsriber to function when we make the subscriber the live database in the
> case that the publisher has problems. Otherwise during replication, we
just
> want the IDs origianlly issued on the publisher to replicate to the
> subscriber as intended.
> thanks
>
>|||On the other hand, you could use log shipping instead,
problem solved.
Regards
John