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 on MSDE/Win2000 Pro?

I only have MSDE installed on Win2000 pro machines. Can I still set up the
database replication, such as "transaction replication"? The SQL Server 2000
Standard edition can not run on Win2000 Pro, right?
Thanks a lot,
FL
No, but you can set up snapshot and merge replication. MSDE databases can
subscribe to transactional publications.
SQL Server 2000 standard cannot run on Win2k Pro - check out
http://www.microsoft.com/sql/evaluat...00/default.asp for more
info.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"FLX" <nospam@.hotmail.com> wrote in message
news:uhzhST19EHA.2700@.TK2MSFTNGP14.phx.gbl...
> I only have MSDE installed on Win2000 pro machines. Can I still set up the
> database replication, such as "transaction replication"? The SQL Server
2000
> Standard edition can not run on Win2000 Pro, right?
> Thanks a lot,
> FL
>
|||Thanks a lot.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OhHCtf19EHA.1084@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> No, but you can set up snapshot and merge replication. MSDE databases can
> subscribe to transactional publications.
> SQL Server 2000 standard cannot run on Win2k Pro - check out
> http://www.microsoft.com/sql/evaluat...00/default.asp for more
> info.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "FLX" <nospam@.hotmail.com> wrote in message
> news:uhzhST19EHA.2700@.TK2MSFTNGP14.phx.gbl...
the
> 2000
>

Replication Limit?

Is there a limit to how many databases can be replicated in a MSDE 2000
environment? We have several servers with several replicated MSDE 2000
databases. It seems that we are hitting a limit because we cannot replicate
any more databases. Any ideas?
What kind of problems are you having?
Are you not able to setup anymore databases for replication or does the
replication just fail.
How many databases are you replicating on each server?
Have you off-loaded the distributor database to another server or does each
server act as its own distributor?
Jim
"VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
news:euv7a7jHEHA.2876@.TK2MSFTNGP09.phx.gbl...
> Is there a limit to how many databases can be replicated in a MSDE 2000
> environment? We have several servers with several replicated MSDE 2000
> databases. It seems that we are hitting a limit because we cannot
replicate
> any more databases. Any ideas?
>

Replication limit?

Is there a limit to how many databases can be replicated in a MSDE 2000
environment? We have several servers with several replicated MSDE 2000
databases. It seems that we are hitting a limit because we cannot replicate
any more databases. Any ideas?
there is a connection limit. MSDE is throttled to 8 simultaneous
connections. This might be what you are bumping into.
"VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
news:es6cv7jHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> Is there a limit to how many databases can be replicated in a MSDE 2000
> environment? We have several servers with several replicated MSDE 2000
> databases. It seems that we are hitting a limit because we cannot
replicate
> any more databases. Any ideas?
>
|||Any way around that?
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23GwkWlkHEHA.3528@.TK2MSFTNGP09.phx.gbl...
> there is a connection limit. MSDE is throttled to 8 simultaneous
> connections. This might be what you are bumping into.
> "VB Programmer" <growNO-SPAM@.go-intech.com> wrote in message
> news:es6cv7jHEHA.3576@.TK2MSFTNGP10.phx.gbl...
> replicate
>
|||As Hilary mentionned, the limit is 8 simultaneous connections, and that
means CONCURRENT connections. So, by implementing schedules so that the
connections do not occur at the same time, I suppose you might work your way
out.
Raj Moloye

Replication keeps failing after one failure

Hi,
We are running Merge Replication on Sql Server 2K with multiples MSDE 2K subsciber.The Replication is getting done through a Visual Basic Application and it works fine as long as it does not fail.Once the replication fails on the Clients machine then we always need to blow the Local Database on the Client's machine and then start the Merge Replication process again.I wonder Why?Any help is really appriciated.. :(It's necessary adjust the retry count for the merge agent, in the event it fails (e.g. remote computer is turned off, network failure etc.). Expand Replication monitor, expand agents, click on merge agents. A list of merge agents, corresponding to each location is listed on the right. To change retry count for a merge agent, highlight the merge agent, right click and select agent properties. Click on tab "Steps", Highlight ID 2 (step 2 - run agent), click on edit. Step name should say "Run agent". Click on Advanced tab, and adjust the retry count and retry interval.
:D|||Thanks for your reply and I will increase the Number of Retry but my question is still unanswered and that's Why the Replication keeps failing after one failure?Is this turning on some flag somewhere in the Sql Server after a failure?|||Ok, I'm confused.

Replication is done via a Visual Basic program?

Replication is a configuration of Sql Server. Has nothing to do with Visual Basic.

What kind of Merge replication is setup? Merge continuosly, or scheduled merge?

If Merge continuously, then the retry count and schedule HAS to be changed. The defaults will cause the agent to fail if the connection is down for just 10 minutes. (10 retries, at 1 minute intervals).

Also, when you say replication fails, there are error messages that can be viewed via replication monitor. The agent may try 10 times, and on failure display 1 message. That doesn't mean there were no retries.|||It's a Merge Replication which runs on Demand through Visual Basic Application.The process works fine 9/10 times but if its fails then it keeps failing and the error log says "The Process was Successfully Stopped".I also don't think that it involves any network issue as the subscriber is connected to the network all the time.
Also once the Synchronization fails then we need to set the Initial database at the subscriber(which involves defining the subscriber,creating the local DB,Copying Roles and creating a dynamic Merge Replication job) and then do the Regular Synchronization,which works fine thereafter.

I don't know Y?:confused:

Replication issues with MSDE and SQL Server Enterprise

Hello--
I'm am having difficulty setting up what I thought would
be something relatively simple to accomplish. I have two
client computers running XP, each using a local copy of
MSDE2000 to store data. I also have a server running
Windows 2000 AS and SQL Server Enterprise.
What I'm trying to do is have the data that's logged on
the client computers replicate to the server. I have run
into a variety of issues, most seem to be related to
security problems. I have all SQL Server instances set up
to use TCP/IP and named pipes, and all set for mixed mode
authentication. I am not using the server as a domain
controller, but I have created matching user accouts with
administrator priviliges on each of the three computers in
question.
When I create a publication on one of the two client
computers, sometimes a snaphot is generated immediately,
sometimes it isn't. When I try to create a pull
subscription on the server, I typically get an error about
being unable to connect to the distributor (I presume
security-releated problems).
I feel as if I'm missing something obvious, because this
seem like a very straightforward setup. What am I doing
wrong?
Thanks in advance for any help.
Chris Langston
exactly how are you connecting to your Publisher? ActiveX, SQM DMO?
If you are using ActiveX controls you have to define the network method you
are using to connect to your publisher/distributor by using
PublisherAddress/PublisherNetwork and DistributorAddress/DistributorNetwork.
If you are pulling a subscription using UNC you will have to ensure that the
SQL Server agent account on the subscriber has rights to the network share
on the publisher. The easiest way to do this is to make sure that the SQL
Server agent account the subscribers run under is the same one that it runs
under on the Publisher and this account is in the local admin group. If you
can't make them the same domain account, give them the same name and
password.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Chris Langston" <clangst@.vectorcorporation.com> wrote in message
news:512901c4807f$6260a7c0$a401280a@.phx.gbl...
> Hello--
> I'm am having difficulty setting up what I thought would
> be something relatively simple to accomplish. I have two
> client computers running XP, each using a local copy of
> MSDE2000 to store data. I also have a server running
> Windows 2000 AS and SQL Server Enterprise.
> What I'm trying to do is have the data that's logged on
> the client computers replicate to the server. I have run
> into a variety of issues, most seem to be related to
> security problems. I have all SQL Server instances set up
> to use TCP/IP and named pipes, and all set for mixed mode
> authentication. I am not using the server as a domain
> controller, but I have created matching user accouts with
> administrator priviliges on each of the three computers in
> question.
> When I create a publication on one of the two client
> computers, sometimes a snaphot is generated immediately,
> sometimes it isn't. When I try to create a pull
> subscription on the server, I typically get an error about
> being unable to connect to the distributor (I presume
> security-releated problems).
> I feel as if I'm missing something obvious, because this
> seem like a very straightforward setup. What am I doing
> wrong?
> Thanks in advance for any help.
> Chris Langston
>
>

Replication Issues

Hello,
I am replicating databases from a sql2000 server with a
seperate distributer running sql2000 server and a MSDE subscriber. I
am using merge replication. The subscribers pull a subscription.
We want to have this setup across the internet for multiple
subscribers
each of whom might be updating as frequently as 1 hour. There are a
couple of issues I would like to find out about.
1.Security: I understood that the initial snapshots can be
published via ftp ( or any other means ofcourse). Can the merge
process be accomplished via ftp too? i.e The merge agent running on
the subscriber, writes to the ftp space reserved for it and the
publisher uses these files to bring the publisher up to speed and put
the updates back to all the subscribers. (sounds very slow). What are
our options as far as channel security if we are not using ftp ( and
is it possible in the first place across the internet).If we use a
secure ftp site, can the agents manage that?
2.Load: Our publisher has about 20 databases right now, with a
total of 1Gig file space occupied by mdf and log files. One database
is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
Is it because of the replication. How can I find out? Can anybody with
experience in this area, tell how much more load we can expect to
see? How much processing is needed for 40 users each updating about
500KB of data every one hour?
Thanks a lot,
Hananiel
1) I'm confused by what you are referring to by ftp. Are you talking about
the ftp port (21) or the ftp service. IE can the merge agent download the
commands using FTP? The answer is no, only the snapshot. If you are talking
about using port 21 the answer is yes. The merge agent will connect to the
publisher/distributor/subscriber using port 1433 or whatever port your SQL
Server is configured to run on, which could be port 21.
2) SQL Server will consume as much memory as is accessible on the machine or
whatever you configure SQL Server to consume using sp_configure 'max server
memory (MB)'
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Hananiel" <hananiel@.yahoo.com> wrote in message
news:e338e321.0409170710.68893ce9@.posting.google.c om...
> Hello,
> I am replicating databases from a sql2000 server with a
> seperate distributer running sql2000 server and a MSDE subscriber. I
> am using merge replication. The subscribers pull a subscription.
> We want to have this setup across the internet for multiple
> subscribers
> each of whom might be updating as frequently as 1 hour. There are a
> couple of issues I would like to find out about.
> 1.Security: I understood that the initial snapshots can be
> published via ftp ( or any other means ofcourse). Can the merge
> process be accomplished via ftp too? i.e The merge agent running on
> the subscriber, writes to the ftp space reserved for it and the
> publisher uses these files to bring the publisher up to speed and put
> the updates back to all the subscribers. (sounds very slow). What are
> our options as far as channel security if we are not using ftp ( and
> is it possible in the first place across the internet).If we use a
> secure ftp site, can the agents manage that?
> 2.Load: Our publisher has about 20 databases right now, with a
> total of 1Gig file space occupied by mdf and log files. One database
> is being replicated. Its consuming 1Gig of RAM Memory. Is that normal?
> Is it because of the replication. How can I find out? Can anybody with
> experience in this area, tell how much more load we can expect to
> see? How much processing is needed for 40 users each updating about
> 500KB of data every one hour?
> Thanks a lot,
> Hananiel
|||Hilary Thanks!,
Does SqlServer natively support encryption of
communication between its remote merge agents and itself? Or should we use a
tool like secure shell to make an encrypted connection?
Thanks,
Hananiel
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eXjhIAOnEHA.644@.tk2msftngp13.phx.gbl...
> 1) I'm confused by what you are referring to by ftp. Are you talking about
> the ftp port (21) or the ftp service. IE can the merge agent download the
> commands using FTP? The answer is no, only the snapshot. If you are
talking
> about using port 21 the answer is yes. The merge agent will connect to the
> publisher/distributor/subscriber using port 1433 or whatever port your SQL
> Server is configured to run on, which could be port 21.
> 2) SQL Server will consume as much memory as is accessible on the machine
or
> whatever you configure SQL Server to consume using sp_configure 'max
server
> memory (MB)'
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Hananiel" <hananiel@.yahoo.com> wrote in message
> news:e338e321.0409170710.68893ce9@.posting.google.c om...
>