replication security

What’s the best way to secure merge replication over the internet? The cl
ient is running MSDE and could be a home user with broadband. If it’s usi
ng a VPN would this be SSL or IPSec?Either would secure the traffic from MSDE to the Publisher.
VPN would allow the client to use other corporate resources as well and
authenticate using Windows
Authentication.
At a minimum put a server certificates on the Publisher and update the
Trusted Root Authority
on the Subscriber (MSDE) and use SSL.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
If you have an ISA Server, you could publish the SQL resource as well.
Here's some docs on using ISA.
http://www.microsoft.com/technet/pr...tain/proxy.mspx
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Thanks Kevin - I left out some important details. Actually, the client side
would be the Publisher (MSDE). We would like to use replication to backup
the database with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:

> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||Thanks Kevin - I left out some important details. Actually, the client side
(MSDE) would be the publisher. We would like to use replication to backup
the databases with our application for our customers.
"Kevin McDonnell [MSFT]" wrote:

> Either would secure the traffic from MSDE to the Publisher.
> VPN would allow the client to use other corporate resources as well and
> authenticate using Windows
> Authentication.
> At a minimum put a server certificates on the Publisher and update the
> Trusted Root Authority
> on the Subscriber (MSDE) and use SSL.
> 316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
> http://support.microsoft.com/?id=316898
> 276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
> Server
> http://support.microsoft.com/?id=276553
> If you have an ISA Server, you could publish the SQL resource as well.
> Here's some docs on using ISA.
> http://www.microsoft.com/technet/pr...tain/proxy.mspx
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||OK. Then the MSDE machine (Publisher) would require the server certificate
then.
Also, SQL Replication is not really a backup strategy... Many customers use
Transactional Replication
to provide a warm standby for their Published database, but you should also
have a good backup strategy in place in case there
are hardware failures, that require complete recovery.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
I'm working on a similar situation (trying to implement SSL between 2
MSSQL 2000 boxes for replication).
Is there any way to implement the envryption (SSL) for the connections
used during the replication, other than the "force encryption" options
in the client and/or server network utility?
TIA.
Mike
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Mike,
In SQL 2000 we use protocol encryption to enable SSL. If you are using
Default instances of SQL for Replication you can also use the older network
library called MultiProtocol.
164667 INF: Replication Setup Over a Firewall
http://support.microsoft.com/?id=164667
Other options are: IPSec between the two machines or a VPN connection
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Replication Problem!

Hello Group,
I'm working on two SQL server databases on two different remote
servers. What client needs is that his database on one remote server
should be mirrored to the other one. The changed should be propogated at
regular intervals at the other server.

For now i'm quite sure that i'll have to use replication to resolve
this issue.

But which type of replication should i use? Transactional, Merge or
Snapshot?

Please note that the client is running his website from the main
server, which is using the source SQL Server database - the one i'll
have to replicate.

Last time when i tried to register the destination server, at my
source server Enterprise Manager, it gave me an error failing to
register the destination server. When i asked the client, all he could
tell me was that both the servers are firewalled and that might have
been the problem.

So just tell me how should i go for it?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***If you go the replicated route then your choice (of merge, snapshot or
transactional) is probably straight forward:

- Will they ever want to make changes on the remote server? If yes then you
must use merge replication.
- Otherwise got for Transactional.

Unless the database is small don't go for snapshot. Snapshot is fine for
just that, taking the odd snapshot, but isn't really suited if you want to
regularly propagate changes.

Regarding your problem connecting to the remove server. It is quite likely
that firewalls could be causing you a problem. I've not tried it, but
presumably the most secure way forward would be to create a vpn connection
between the two servers (firewalls may still be an issue) and then replicate
across the vpn link.

Hope this helps,

Brian.

www.cryer.co.uk/brian

"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:d5Sue.8$_r5.2081@.news.uswest.net...
> Hello Group,
> I'm working on two SQL server databases on two different remote
> servers. What client needs is that his database on one remote server
> should be mirrored to the other one. The changed should be propogated at
> regular intervals at the other server.
> For now i'm quite sure that i'll have to use replication to resolve
> this issue.
> But which type of replication should i use? Transactional, Merge or
> Snapshot?
> Please note that the client is running his website from the main
> server, which is using the source SQL Server database - the one i'll
> have to replicate.
> Last time when i tried to register the destination server, at my
> source server Enterprise Manager, it gave me an error failing to
> register the destination server. When i asked the client, all he could
> tell me was that both the servers are firewalled and that might have
> been the problem.
> So just tell me how should i go for it?
> Thanks in advance
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***|||Hi again!

The problem is that all the source tables dont have a pri key... and
i have learnt that Trans Rep doesnt support replicating tables without
Pri key. Besides there's no question of adding Pri Keys to the database
which is already live!

Second thing is that, the database size is in between 1 and 2GBs. That
means even Snapshot Rep is not suitable!

Shall i go for Merge Rep? I heard that there are conflicts in that?

So then what is the way out?

Thanks Brian, for replying!

Regards
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> The problem is that all the source tables dont have a pri key... and
> i have learnt that Trans Rep doesnt support replicating tables without
> Pri key. Besides there's no question of adding Pri Keys to the database
> which is already live!

Well, it want take long before the database is dead. Not having
primary keys is asking for serious problems.

> Second thing is that, the database size is in between 1 and 2GBs. That
> means even Snapshot Rep is not suitable!
> Shall i go for Merge Rep? I heard that there are conflicts in that?

I doubt that merge replication is possible without PKs. If rows can't
be identified, it's getting darn difficult to do replication.

Seems to me you have three options:

1) Add PKs to the database, and do transactional replication.
2) Regularly backup the database and restore on the other end.
3) Log shipping.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"Erland Sommarskog" <esquel@.sommarskog.se> wrote in message
news:Xns968035B96389Yazorman@.127.0.0.1...
> debian mojo (debian_mojo@.yahoo.com) writes:
>>
>> The problem is that all the source tables dont have a pri key... and
>> i have learnt that Trans Rep doesnt support replicating tables without
>> Pri key. Besides there's no question of adding Pri Keys to the database
>> which is already live!
> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.
>> Second thing is that, the database size is in between 1 and 2GBs. That
>> means even Snapshot Rep is not suitable!
>>
>> Shall i go for Merge Rep? I heard that there are conflicts in that?
> I doubt that merge replication is possible without PKs. If rows can't
> be identified, it's getting darn difficult to do replication.
> Seems to me you have three options:
> 1) Add PKs to the database, and do transactional replication.
> 2) Regularly backup the database and restore on the other end.
> 3) Log shipping.
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techin.../2000/books.asp

I fully agree with everything Erland has said.

You may not currently have primary keys, but surely there are fields that
you are treating as unique? How do you currently uniquely identify a record?
A primary key can be defined using more than one field.

Treat Erland's comment as a dire warning:

> Well, it want take long before the database is dead. Not having
> primary keys is asking for serious problems.

Its put very well, and spot on.

Brian.

www.cryer.co.uk/brian|||Hello Erland and Brian,
Thanks for the reply!

The problem is that there are around 36 user tables in the source
database and only 5 of them have pri keys, the rest are either using
foriegn keys or dont have any kinda keys at all!

Is it good to have pri keys on all tables in the database.

Please do note that the database is for a website and it is estimated
that it will hit 10GB in the first one month!

Is there any chance of replication without compromising the database
integrity or shall i look for any other options such as log shipping?

Please advise!

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Please do note that the database is for a website and it is estimated
> that it will hit 10GB in the first one month!

Of course with no Pkeys, the chances for duplicates increase, and so
will the database size.

> Is there any chance of replication without compromising the database
> integrity or shall i look for any other options such as log shipping?

If you don't want to add primary keys (and save the database from a
disaster furtther afield) log shipping or backup/restore is what you
have to look into.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks again, Erland,

Since the database has problems with Pri keys, i'm thinking about Log
Shipping as an alternative.

But as it is mentioned in BOL that for LS to work, it's necessary to
configure three servers:
1. Source Server
2. Monitor Server
3. Destination Server.

Now the destination and the source databases can be on the same
server, but it is mandatory to keep the monitor server separate.

The problem with this approach is that i've access to only two servers
- one source and one destination. I wont have the privilege of having
another one just for setting up the monitor server!

What should i do?

Thanks in advance!
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||debian mojo (debian_mojo@.yahoo.com) writes:
> Since the database has problems with Pri keys, i'm thinking about Log
> Shipping as an alternative.
> But as it is mentioned in BOL that for LS to work, it's necessary to
> configure three servers:
> 1. Source Server
> 2. Monitor Server
> 3. Destination Server.
> Now the destination and the source databases can be on the same
> server, but it is mandatory to keep the monitor server separate.
> The problem with this approach is that i've access to only two servers
> - one source and one destination. I wont have the privilege of having
> another one just for setting up the monitor server!

Looks like you are starting to get some good arguments: "Either we
fix primary keys to the database, or we go and buy some more hardware,
else we can't run this show".

I will have to admit that the need for third machine was news to me,
but I have never set up log shipping myself.

Actually, add PKeys to those tables does not have to be a killer
work. If there are unique indexes, just drop these and recreate
them as primary keys. If there are not any primary keys, just add
an uniqueidentifier with the default of NEWID() to the tables, and
make that the primary key (nonclustered!). That does not really help
to make the data model any better, but at least you can set up
replication over this dying grace.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland,
That's true that moving away from the problem is not a solution. But
you see that the database i'm talking about is a 24 x 7 database, and
i'm not pretty sure how apps that are using this db.

If i do pri keys to the tables, is there any possibility that the apps
may suffer is some way.

I do agree that in most of the cases there's no harm... but you see
the database is a production one and i'm really paranoid about it's
safety!

I hope you understand!

BTW, your advice was an eye-opener for me.

So what do you suggest? what are the other things i should take care
of before adding the pri keys?

Debian

*** Sent via Developersdex http://www.developersdex.com ***|||what are the other things i should take care of on the app side, before
adding the pri keys?

Thanks in advance
Debian

*** Sent via Developersdex http://www.developersdex.com ***|||"debian mojo" <debian_mojo@.yahoo.com> wrote in message
news:aeawe.4$jU.2182@.news.uswest.net...
> Thanks Erland,
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.
> I do agree that in most of the cases there's no harm... but you see
> the database is a production one and i'm really paranoid about it's
> safety!
> I hope you understand!
> BTW, your advice was an eye-opener for me.
> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?
> Debian
>
> *** Sent via Developersdex http://www.developersdex.com ***

Don't make changes to a live production database unless you have tested it
first and are comfortable about the changes you are going to make. You NEED
primary keys, but don't make the assumption that adding a new primary key
won't break one of your applications. If all you are doing is redefining an
existing unique index as a primary key then that shouldn't hurt anything,
but if you are adding a new field as a primary (default NEWID as per
Erland's suggestion [a very good suggestion by the way]) then that does have
the potential to break something - I think its only likely to break badly
written code, but the potential is there, so test it first.

Take a copy, add your primary keys to that and then run through the whole
range of applications and ensure that everything works as you expect. Only
then, once you are entirely satisfied, introduce the changes to the live
database and even then be sure that you can roll your changes back if it all
goes horribly wrong.

Brian.

www.cryer.co.uk/brian|||debian mojo (debian_mojo@.yahoo.com) writes:
> That's true that moving away from the problem is not a solution. But
> you see that the database i'm talking about is a 24 x 7 database, and
> i'm not pretty sure how apps that are using this db.
> If i do pri keys to the tables, is there any possibility that the apps
> may suffer is some way.

Sure. Apps that do SELECT * on the tables and then spit out all columns
somewhere, or assume that it has a certain width will be confused by an
extra column if you add one. If you add the new column anywhere but last,
apps that refers to columns by column number will croak. All this is
bad practice, but since this site already has proven a fondness for
bad practice...

As Brian says, you need to test any changes in a safe environment. Which
includes finding out how long time it takes to add the indexes and the new
columns, so you can determine the downtime.

I should have added that beside looking for unique indexes, also look
for existing IDENTITY columns and existing guid columns, as they can
be used for the task.

> So what do you suggest? what are the other things i should take care
> of before adding the pri keys?

Read Brian's article again. There was a lot of good advice there!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

Replication Problem

High I hope someone can help me.

I'm trying to set-up replication between us and a business client.
We have a VPN into their network on 1 account set against 1 userid.
We then have a SQL SERVER acocunt on their Sql Server 7 machine.

We are running Win2000 and SQL Server 2000 Enterprise Std.

The publication has been set-up and looks okay
so has the pull subscription on our server.

When running the replication though I'm getting the following error.

The process could not connect to Distributor 'NXTIMSVR'.

Any ideas would be appriciated.

CliveWhere is your Distributor reside at? how was the account setup in the Distributor?|||Originally posted by joejcheng
Where is your Distributor reside at? how was the account setup in the Distributor?
The distributer is a the remote client site, I do not know how the distributer was set-up for we have just been asked to look after a section of their data.
Is there something that I could ask them to check.|||The first thing you have to make sure is that your publisher has to be able to access the Distributor. The ID that you setup your publication is the one you need to check. Then your subscirber has to be able to pull in information from the Distirbutor, so it need to be able to access the Distributor also.|||The publisher can see the distributer, its the PULL subscriber that is not connecting to the distributer.

Clive

Replication Performance -- Many publishers, 1 subscriber

I have a situation where I need about 50 client machines that are
collecting data to synchronize with a network server on our local 100
Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
Have experimented with using MSDE on the client machines and using
merge replication with a push subscription to the server. Everything
seems to work good on a single client, but I'm wondering:
- How does this scale? Is it realistic to think I can do this with 50
machines?
- Locking implications. Since this is an automated process, The
ability to have real-time inserts needs to be always available
Typically happens 1 - 2 times per minute. (Updates can be handled
through standard error handling since a human operator is involved.)
- As time goes by and table sizes increase would this slow down, even
if the primary activity on the table is the addition of records?
Thanks in advance.
Mike
Mike,
I would say it is preferable to have a single publisher on the main server
and multiple push subscribers for this topology.
I'm not sure what your separation of inserts and updates is for - could you
explain a little further.
Inserts shouldn't cause any locking contention. For contention of resources,
you could stagger the synchronization, and limit the number of concurrent
merge processes.
Increasing table size for inserts should be no problem. You'll have a
gradual increase in metadata, which will be removed automatically though.
HTH,
Paul Ibison
|||do you need bi-directional replication? From what you describe you have
selected merge, where if you don't need bi-directional replication you can
get away with transactional which will offer better performance.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405070936.fcab9ad@.posting.google.co m...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||Paul-
Thanks for the info. It was helpful. The reason I was pursuing this
topology is because the clients are the ones collecting the data.
Which need to be warehoused in a central place. (There is also
static setup data that is coming one way from the server, which I'm
doing exactly what you describe.)
The inserts and updates separation issue is because some of the data
comes automatically from the machine while another, different type of
data, data comes from the operator. It's stored in separate tables in
MSDE. The person's data can be corrected by the person to fix
mistakes. The machine data "never makes mistakes" (or at least it
doesn't know it does) so it's only an insert operation.
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#vLI3YGNEHA.1616@.TK2MSFTNGP12.phx.gbl>...
> Mike,
> I would say it is preferable to have a single publisher on the main server
> and multiple push subscribers for this topology.
> I'm not sure what your separation of inserts and updates is for - could you
> explain a little further.
> Inserts shouldn't cause any locking contention. For contention of resources,
> you could stagger the synchronization, and limit the number of concurrent
> merge processes.
> Increasing table size for inserts should be no problem. You'll have a
> gradual increase in metadata, which will be removed automatically though.
> HTH,
> Paul Ibison
|||Hilary-
Thanks for the info. Not sure exactly what you mean by "selected
merge"? Like horizontal selection? You're right. Actually, I don't
need bi-directional replication in this case but, to my knowledge,
MSDE (which is running on the client) doesn't support transactional
and I thought snapshot would be inefficient.
Thanks again,
Mike
mvonstein@.yahoo.com (Mike Von Stein) wrote in message news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
> I have a situation where I need about 50 client machines that are
> collecting data to synchronize with a network server on our local 100
> Mbps LAN. Am running SQL Server Enterprise 2000 on the back end.
> Have experimented with using MSDE on the client machines and using
> merge replication with a push subscription to the server. Everything
> seems to work good on a single client, but I'm wondering:
> - How does this scale? Is it realistic to think I can do this with 50
> machines?
> - Locking implications. Since this is an automated process, The
> ability to have real-time inserts needs to be always available
> Typically happens 1 - 2 times per minute. (Updates can be handled
> through standard error handling since a human operator is involved.)
> - As time goes by and table sizes increase would this slow down, even
> if the primary activity on the table is the addition of records?
> Thanks in advance.
> Mike
|||I meant "selected" in the sense of "chosen".
MSDE can be a subscriber to a transactional publication, or a publisher for
merge or snapshot publications. On further reflection I guess this is why
you have selected merge.
I have worked on a topology that used over 60 merge subscribers to a single
publisher, so it is highly scalable.
I have heard about people using merge with over 100 subscribers with no real
problems.
Achieving real time replication with merge is a problem. Set your
pollinginterval to something low - perhaps 10s or so.
"Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
news:c92d1754.0405080525.16417da3@.posting.google.c om...
> Hilary-
> Thanks for the info. Not sure exactly what you mean by "selected
> merge"? Like horizontal selection? You're right. Actually, I don't
> need bi-directional replication in this case but, to my knowledge,
> MSDE (which is running on the client) doesn't support transactional
> and I thought snapshot would be inefficient.
> Thanks again,
> Mike
>
> mvonstein@.yahoo.com (Mike Von Stein) wrote in message
news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...[vbcol=seagreen]
|||Mike,
as you know either way works, but I'd still choose to use use a central
publisher and multiple subscribers, as the metadata and alerts etc are then
in a central place and maintenance is simpler. For merge, the distinction
between publisher and subscriber is not too relevant if you don't have
conflicts.
Cheers,
Paul
|||Paul-
Okay, I guess I was thinking since the clients would be the primary
information generators, they would be the "Publishers", but I guess
the names shouldn't be taken literally in this case. I think I see
the benefits you are suggesting.
One question though. How does this impact future schema changes?
Would you have to drop all subscriptions remotely at the clients and
recreate? (That's a maintenance concern, since there are so many.)
We're kind of in a "formative" phase here and I'm expecting to have to
do some tweaks...
Thanks,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#of5O$cNEHA.3328@.TK2MSFTNGP10.phx.gbl>...
> Mike,
> as you know either way works, but I'd still choose to use use a central
> publisher and multiple subscribers, as the metadata and alerts etc are then
> in a central place and maintenance is simpler. For merge, the distinction
> between publisher and subscriber is not too relevant if you don't have
> conflicts.
> Cheers,
> Paul
|||Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> MSDE can be a subscriber to a transactional publication, or a publisher for
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> I have worked on a topology that used over 60 merge subscribers to a single
> publisher, so it is highly scalable.
> I have heard about people using merge with over 100 subscribers with no real
> problems.
> Achieving real time replication with merge is a problem. Set your
> pollinginterval to something low - perhaps 10s or so.
> "Mike Von Stein" <mvonstein@.yahoo.com> wrote in message
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...
|||Transaction does not support bi-directional replication through the wizards. You can configure it for bi-directional replication using the replication stored procedures, or you could use queued replication (but it is tuned for less than 10 subscribers whi
ch rules it out in this case) or immediately updating susbcribers (only if your subscribers/publisher are always well connected).
Transactional does support the subscribers going offline, however you are updating the subscribers, so your best option in this case is merge.
Merge also supports having the publisher and susbcribers going offline.
The caveat in this is the more frequently your publisher/subscribers goes off line the greater the chance of having conflicts.
-- Mike Von Stein wrote: --
Hilary-
The performance info is interesting. Yes, that is why I was talking
about Merge, but maybe I should look at reversing the terminology like
Paul in this thread suggested. Then maybe I could use transactional
with MSDE?
One concern, though, is the whole reason we are doing this is to
buffer the system to handle things such as planned/unplanned,
server/network downtime, etc. (The clients have to be available 24/7,
but not necessarily the server, if that makes sense...) Read in BOL,
that transactional was for a constant connection. In general, this
would be more like a 99.9% constant connection... So not sure what to
think about that...
Thanks,
Mike
"Hilary Cotter" <hilaryk@.att.net> wrote in message news:<O1Ly#gXNEHA.2500@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
> I meant "selected" in the sense of "chosen".
> merge or snapshot publications. On further reflection I guess this is why
> you have selected merge.
> publisher, so it is highly scalable.
> problems.
> pollinginterval to something low - perhaps 10s or so.
> news:c92d1754.0405080525.16417da3@.posting.google.c om...
> news:<c92d1754.0405070936.fcab9ad@.posting.google.c om>...

Replication Options

Hi, we have an SQL Server on Domain A that need to replicate to a client on
Domain B. There is no trust between the two domains. What are the options
we have to have a replication setup between the 2 SQL?
Thanks in advance for any answer!!
I forgot to mention but the main server is using Standard 2005 and client is
using Workgroup 2005
"Jas" wrote:

> Hi, we have an SQL Server on Domain A that need to replicate to a client on
> Domain B. There is no trust between the two domains. What are the options
> we have to have a replication setup between the 2 SQL?
> Thanks in advance for any answer!!
|||replicating between member servers in a work group is difficult as there is
a 5 or 10 connection limitation per machine.
basically you can use SQL authentication, and set your SQL Server agent
accounts up on both machines to run under an account which has the same name
and password on both the publisher and subscriber.
Then ensure this account has read and list files and folders rights on the
directory which underlies your snapshot share.
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
"Jas" <Jas@.discussions.microsoft.com> wrote in message
news:8DEC33DF-A653-43AD-A507-D0693FA031E6@.microsoft.com...
> Hi, we have an SQL Server on Domain A that need to replicate to a client
> on
> Domain B. There is no trust between the two domains. What are the
> options
> we have to have a replication setup between the 2 SQL?
> Thanks in advance for any answer!!
|||Is replication via FTP still working in 2005 version? Because it maybe a
solution...
"Hilary Cotter" wrote:

> replicating between member servers in a work group is difficult as there is
> a 5 or 10 connection limitation per machine.
> basically you can use SQL authentication, and set your SQL Server agent
> accounts up on both machines to run under an account which has the same name
> and password on both the publisher and subscriber.
> Then ensure this account has read and list files and folders rights on the
> directory which underlies your snapshot share.
> --
> 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
> "Jas" <Jas@.discussions.microsoft.com> wrote in message
> news:8DEC33DF-A653-43AD-A507-D0693FA031E6@.microsoft.com...
>
>
|||yes!
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
"Jas" <Jas@.discussions.microsoft.com> wrote in message
news:89544D88-6314-42CC-893E-D24D0B4789CE@.microsoft.com...[vbcol=seagreen]
> Is replication via FTP still working in 2005 version? Because it maybe a
> solution...
> "Hilary Cotter" wrote:
|||Is there any documentation on how to setup a replication using FTP in SQL
Server 2005?
"Hilary Cotter" wrote:

> yes!
> --
> 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
> "Jas" <Jas@.discussions.microsoft.com> wrote in message
> news:89544D88-6314-42CC-893E-D24D0B4789CE@.microsoft.com...
>
>

Replication issues between SQL mobile and SQL2k?

I meant to cross-post this over here, but not having much response in SQLCE
forum ...
I'm in the process of a 'forced' upgrade (my client bought newer PocketPCs).
Soooo .. what I would like to know is whether there are any issues
connecting SQL Mobile to SQL 2000 for replication. Currently, replication
works "as advertised" between SQLCE and SQL2000 (enterprise).
The models seem quite symmetrical. I have not had any problems ...yet.
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
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:eQT3Y76BGHA.1032@.TK2MSFTNGP11.phx.gbl...
>I meant to cross-post this over here, but not having much response in SQLCE
>forum ...
> I'm in the process of a 'forced' upgrade (my client bought newer
> PocketPCs).
> Soooo .. what I would like to know is whether there are any issues
> connecting SQL Mobile to SQL 2000 for replication. Currently, replication
> works "as advertised" between SQLCE and SQL2000 (enterprise).
>
|||We're using this setup in a production environment and so far things work in
a similar fashion to our SQL CE to SQL 2000 replication. And...we get the
added benefit of background or async replication, which is very nice.
- will
"Earl" wrote:

> I meant to cross-post this over here, but not having much response in SQLCE
> forum ...
> I'm in the process of a 'forced' upgrade (my client bought newer PocketPCs).
> Soooo .. what I would like to know is whether there are any issues
> connecting SQL Mobile to SQL 2000 for replication. Currently, replication
> works "as advertised" between SQLCE and SQL2000 (enterprise).
>
>

Replication Issue With Cisco VPN Client

If anyone has run across this before, I'm having the same
issue with the same components.
If I figure anything out, I'll post findings here.
Thanks,
David
>--Original Message--
>Morning Guys,
>I really need you help, I am just about to seriously
pull my hair out. Here
>is the situation.
>I have 1 server running SQL Server 2000 sp3a with merge
replication setup,
>and 150 users on laptops running MSDE 2000 with the
latest service pack and
>dialling up to the server via a 56k modem and using a
Cisco VPN Client to
>gain access.
>If I communicate on the network, all the replication
works fine, if I
>communicate on a ADSL Line all the communicates is fine,
if I dialup,
>communication will just fail, it will process the
articles and then fail. I
>really need some help here. I have adjusted the MTU rate
on the server,
>Cisco router and the client. but I just can't get this
to work correctly and
>I have this working in many other companies.
>The dial up users are getting a good connection with 50k
>If you need anymore information give me a shout.
>Thanks Tim.
>
>.
>
I believe Paul Ibison has seen problems with this. If he doesn't chime in
here, contact him offline.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"David England" <anonymous@.discussions.microsoft.com> wrote in message
news:40e101c4900f$40544430$a301280a@.phx.gbl...[vbcol=seagreen]
> If anyone has run across this before, I'm having the same
> issue with the same components.
> If I figure anything out, I'll post findings here.
> Thanks,
> David
> pull my hair out. Here
> replication setup,
> latest service pack and
> Cisco VPN Client to
> works fine, if I
> if I dialup,
> articles and then fail. I
> on the server,
> to work correctly and

Replication Issue With Cisco VPN Client

Morning Guys,
I really need you help, I am just about to seriously pull my hair out. Here
is the situation.
I have 1 server running SQL Server 2000 sp3a with merge replication setup,
and 150 users on laptops running MSDE 2000 with the latest service pack and
dialling up to the server via a 56k modem and using a Cisco VPN Client to
gain access.
If I communicate on the network, all the replication works fine, if I
communicate on a ADSL Line all the communicates is fine, if I dialup,
communication will just fail, it will process the articles and then fail. I
really need some help here. I have adjusted the MTU rate on the server,
Cisco router and the client. but I just can't get this to work correctly and
I have this working in many other companies.
The dial up users are getting a good connection with 50k
If you need anymore information give me a shout.
Thanks Tim.
Tim,
have you tried following the recommendations in this article:
http://support.microsoft.com/default...49&Product=sql
It refers to a transactional setup but if you substitute 'merge agent' for
'distribution agent' it should apply eqally.
HTH,
Paul Ibison

Replication is renaming my Stored Procedures on Client only

I am new to replication but I have a question, I have my server which is using a transaction replication process and a client with using SQL Express with replication turned on. I have the identical DB on both nodes yet when I subsrcibe to the server from the client, data is transfer but for some reason, all of my stored procedures are renamed with sp_MSDel_DBOxxxx. How do I keep SQL from renaming these SP's? The server maintains the origial names to the SPs its just happening on my client.

Thanks

You can customize the names of these stored procs in the article properties.|||

If I understand correctly, the SPs you're seeing on client db are not the renamed copy of your original sps, those sp_MSDel_DBOxxxx sps are created and used exclusively by replication, they should not affect your existing sps in anyway. However, if you want to control how those replication sps are named you can use the approach Greg suggested.

Thanks,

Zhiqiang Feng

|||

My Server SP's are named properly, for example usp_AddActivityLog is the proper name of my custom SP. After replication occurs the name of the same SP on my client is now named sp_MSdel_dboActivityLog.

In the Article Property <Default is based on stored procedure name> is in the destination object name. And here is the code for SP:

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[usp_AddActivityLog]

@.activityLogID bigint,

@.agencyofficer_id bigint,

@.lookup_id int,

@.remarks varchar(200) = '',

@.caseLinkID bigint = null,

@.startTime dateTime = null,

@.endTime datetime = null,

@.location varchar(200) = '',

@.callForServiceID bigint = 0

AS

SET NOCOUNT ON

DECLARE @.ret int

IF (@.activityLogID <> 0)

BEGIN

UPDATE ActivityLog SET

EndTime = @.endTime,

fk_CaseLinkID = @.caseLinkID,

remarks = @.remarks,

location = @.location,

fk_CallForServiceID = @.callForServiceID

WHERE pk_activityID = @.activityLogID

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Update of ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.activityLogID

END

ELSE

BEGIN

INSERT INTO ActivityLog (fk_AgencyOfficersID, fk_ActivityLookupID, StartTime, EndTime,Remarks, fk_CaseLinkID, location, fk_CallForServiceID)

VALUES (@.agencyofficer_id, @.lookup_id, @.startTime, '', @.remarks, @.caseLinkID, @.location, @.callForServiceID)

SET @.ret = @.@.ERROR

IF @.ret <> 0

BEGIN

INSERT INTO ErrorLog VALUES ('Insert into ActivityLog failed.', GetDate())

RETURN -1

END

ELSE

RETURN @.@.identity

END

Any Idea why they are being renamed?

Thanks

|||

Distribution agent doesn't rename anything, it just applies what's in the snapshot folder. Look in your snapshot folder to see what stored procedures are being generated. If the proper names are there, then the procs you see must have previously existed. If the incorrect proc names are being generated, then you need to doublecheck the article properties to see what the problem could be.

I just tried your scenario on a SQL 2005 machine with different proc names, they were created properly at the subscriber. IF there's something else i missed, please let me know.

Replication in LAN

Can Replication works in share network, I mean if i make a client as
publisher and server as distributer and subscriber willbe on remote location,
then will it work? if yes then how will i define the subscriber?
Manish,
if the networks are trusted then it is very much as per usual. if not, then
this article should help:
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)