Shailesh,
transactional replication over a VPN would be my choice,
with your home as a pull subscriber. If you haven't got
the VPN set up, then you can still use SQL Server logins
for synchronization and FTP for initialization.
This article should help in the configuration:
http://support.microsoft.com/?id=321822
Rgds,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Paul Ibison wrote:
> Shailesh,
> transactional replication over a VPN would be my choice,
> with your home as a pull subscriber. If you haven't got
> the VPN set up, then you can still use SQL Server logins
> for synchronization and FTP for initialization.
> This article should help in the configuration:
> http://support.microsoft.com/?id=321822
> Rgds,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
There are so many options and issues. I don't think there's any way
to avoid spending a week just to read through everything and
understand it all...
sql
Showing posts with label home. Show all posts
Showing posts with label home. Show all posts
Monday, March 26, 2012
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.
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.
Monday, March 12, 2012
Replication over satelite connection.
Hi everybody, we are designing an application for ships, and I want to replicate data between the ships and a home office. TCP/IP communication is not possible, but we have a e-mail based system for transferring files.
What is the best solution? Is it possible to have the publisher/subscriber to leave a file, and then let the e-mail system transfer the file and then later the subscriber/publisher can pick up the file and do the merge?
Any ideas?
Thanx, Kjetil
Kjetil,
this is not directly possible in the replication setup. You might want to
use DTS to import the file into a database then in the DTS package. Setup
replication between this staging database and your central system and you
can initiate replication locally.
HTH,
Paul Ibison
|||Thanks, this is what I tried to say to my boss. But how can this be implemented? Should I use merge replication or transactional replication.
In my home office, I will need a 'copy' of the ship database, in the first stage 9 ships.
Is it possible to block the database in the ships for updates while the replication process is running?
Or should I use transactional replication, will I then need a 'proxy database' at each ship and one for each ship in the home office. Is it possible to implement conflict resolution methods that guarantee the same result at all sites always?
The amount of data over the satellite link (64k) is an issue, so it is important to send only the changes.
Do you think I'm on track? Is it possible to implement.
Kjetil
|||Kjso,
as you need inserts updates and deletes to be replicated over the internet,
you are essentially coding what MS developers may well be creating in SQL
2005 :-). This is not straightforward at all (to say the least). You might
want to use triggers on each table which write an audit of changes to your
audit table(s). These audit table changes are then sent over the link to a
central server where they are applied to the central database. In my opinion
this is only going to be feasible at the central server if you have
partitioned data; dealing with conflicts will make this too difficult. Then
sending only the changes required back to the other subscribers is not
simple. You say that you want only changes, but a 'snapshot' would be more
straightforward.
HTH,
Paul Ibison
What is the best solution? Is it possible to have the publisher/subscriber to leave a file, and then let the e-mail system transfer the file and then later the subscriber/publisher can pick up the file and do the merge?
Any ideas?
Thanx, Kjetil
Kjetil,
this is not directly possible in the replication setup. You might want to
use DTS to import the file into a database then in the DTS package. Setup
replication between this staging database and your central system and you
can initiate replication locally.
HTH,
Paul Ibison
|||Thanks, this is what I tried to say to my boss. But how can this be implemented? Should I use merge replication or transactional replication.
In my home office, I will need a 'copy' of the ship database, in the first stage 9 ships.
Is it possible to block the database in the ships for updates while the replication process is running?
Or should I use transactional replication, will I then need a 'proxy database' at each ship and one for each ship in the home office. Is it possible to implement conflict resolution methods that guarantee the same result at all sites always?
The amount of data over the satellite link (64k) is an issue, so it is important to send only the changes.
Do you think I'm on track? Is it possible to implement.
Kjetil
|||Kjso,
as you need inserts updates and deletes to be replicated over the internet,
you are essentially coding what MS developers may well be creating in SQL
2005 :-). This is not straightforward at all (to say the least). You might
want to use triggers on each table which write an audit of changes to your
audit table(s). These audit table changes are then sent over the link to a
central server where they are applied to the central database. In my opinion
this is only going to be feasible at the central server if you have
partitioned data; dealing with conflicts will make this too difficult. Then
sending only the changes required back to the other subscribers is not
simple. You say that you want only changes, but a 'snapshot' would be more
straightforward.
HTH,
Paul Ibison
Tuesday, February 21, 2012
Replication in XP home
Please help!!
I have recently installed ms sql on my system and I am trying to replicate my machine which happens to be a compaq laptop which is running xp home edition. The publisher machine can see my server however gives me an invalid subscription error. I have been told that it might be due to the fact the the laptop is on xp home, would this be a problem???
please get back to me, anyone.,.much appreciated!!!!
JaysunJaysun,
What version of SQL Server did you install? Check http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp for version/OS compability.
If your version of SQL Server is compatible with XP home I would also suggest looking at the account that SQL Server is using to login. If it is using the local system account then I will not be able to talk to other servers on the domain.
Regards,|||hi thanks for coming back to me, ive checked the version and its ok. plus i can connect to the publisher and also to other servers. esp to another laptop that is running xp prof, and that does not seem to have a problem replicating. however i cannot connect to other servers running windows 2k. ive checked the sql account and username and password is the same as the other laptop./
the error i get when i try and connect to another server:
login failed for user 'sa' not associated with a trusted sql server
would it be because i this one is not logged into a domain??
I have recently installed ms sql on my system and I am trying to replicate my machine which happens to be a compaq laptop which is running xp home edition. The publisher machine can see my server however gives me an invalid subscription error. I have been told that it might be due to the fact the the laptop is on xp home, would this be a problem???
please get back to me, anyone.,.much appreciated!!!!
JaysunJaysun,
What version of SQL Server did you install? Check http://www.microsoft.com/sql/evaluation/sysreqs/2000/default.asp for version/OS compability.
If your version of SQL Server is compatible with XP home I would also suggest looking at the account that SQL Server is using to login. If it is using the local system account then I will not be able to talk to other servers on the domain.
Regards,|||hi thanks for coming back to me, ive checked the version and its ok. plus i can connect to the publisher and also to other servers. esp to another laptop that is running xp prof, and that does not seem to have a problem replicating. however i cannot connect to other servers running windows 2k. ive checked the sql account and username and password is the same as the other laptop./
the error i get when i try and connect to another server:
login failed for user 'sa' not associated with a trusted sql server
would it be because i this one is not logged into a domain??
Subscribe to:
Posts (Atom)