Solved

Secure connection between two sql servers (one on a web server)

Posted on 2004-04-22
7
423 Views
Last Modified: 2012-05-04
We have a sql server database inside our firewall.

This allows us to take customer details and orders over the phone.

We have written a vb.net application that allows users to batch print orders which are then fulfilled in-house.

we now want to set up a web site that sells our products over the web.  

We plan to operate a separate sql server on the web server that logs customer details and orders.  

Once a day, out of hours, we want to import all records (through our firewall) into our in-house sql server.

In fact the data exchange needs to run both ways.  When new products are added on the in-house system, we want to automatically add them on the web server.

So I guess Customer and order details come down from the web server, Products are uploaded from the in-house server to the web server.

How can we securely connect one server to the other?

Wing
0
Comment
Question by:WingYip
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 3

Expert Comment

by:Younkman
ID: 10895129
It sounds like you may want to look into Replication:

Here are some links (from dtomyn, in a similar question, dunno what the etiquette is here for referencing sources):
 
http://www.mssqlserver.com/replication/
http://vyaskn.tripod.com/repl_ques.htm
http://www.sqlservercentral.com 

Y
0
 
LVL 13

Expert Comment

by:danblake
ID: 10897159
Replication both ways, would be merge replication.  In this scenario merge replication is the best as subscribers/destinations are off-line for part of the day.


When new products are added on the in-house system, we want to automatically add them on the web server.
--> Straight away or in a bit (20 mins to give the in-house guys room to check thev'e added the data correctly ?) -- Still merge replication or tran replication one way may be best here.


How can we securely connect one server to the other?
Seperate line from just your web-server -> database-server (that these machines are only on ) and then use SSL technologies (multiprotocal connection type for example) to encrypt the data-between the two points for an encrpyted session or establish a VPN between the two sites/machines.

Replication handshake establishment is made via NT Service Accounts or SQL Authentication -- hence why its a good idea to use some form of encryption between sites.
0
 
LVL 1

Author Comment

by:WingYip
ID: 10898559
Hmm looked into and unfortunately we will be using msde on the web server.  msde is also running the db in house.  Replication publishing requires a full copy of sql server which is probably out of the question.

Alternative suggestions?

Wing
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
LVL 13

Accepted Solution

by:
danblake earned 125 total points
ID: 10898743
Replication publishing requires a full copy of sql server which is probably out of the question.
--Nope it does not, you can have a merge replication without full copies of data at both sites.
(Or partition and have a merge publication established without a snapshot made).
--> MSDE Replication
http://www.microsoft.com/sql/msde/productinfo/features.asp

MSDE 2000 can act as a Subscriber for all types of replication, and can act as a Publisher and Distributor for merge replication and snapshot replication. It cannot act as a Publisher or Distributor for transactional replication.
(So you're going to be able to do this using Merge Replication / Snapshot replication only -- this will do the trick in most instances)
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replover_694n.asp


Other notes:
 web site that sells our products over the web with MSDE --> how many products/concurrent connections do you require ?

MSDE is only really suited for a low connection ratio (about 5-20  connections), I would seriously consider upgrading to SQL-Server processor licensing edition for your web-site.


Alternatives exist such as linked servers between the two-sites but this is not really a viable option in your scenario,
Or building a custom replication resolver such as with DTS/ouput files/input files/DTS at the second site -- better off using replication.


0
 
LVL 1

Author Comment

by:WingYip
ID: 10899401
Oh great!  

FYI we run MSDE on our in house database.  There are between 15 and 30 users.  The application we developed is written in VB.net.  All db calls are done using ADO.Net.  The web app will be written in ASP.Net and ado.net

Things can get pretty busy on the in house db but msde only starts to throttle off after 5 concurrent connections.  I believe that the chances of 5 concurrent connections occurring when using ADO.Net are slim anyway as each connection is maintained for a split second and then released.

We have not seen any noticeable slow down anyway and we do not anticipate that the web site will be nearly as busy as our in house db - At least not for some time.

MSDE has proved to be 100 per cent reliable so far (9 months).   Choosing between a sql server processor license and a free copy of msde is as somebody has said a bit of a 'no-brainer'.

I think MS tends to underplay and obscure how powerful MSDE is.  By the way my original decision to use MSDE was based on this article - http://www.eggheadcafe.com/articles/20021110.asp

Cheers

Wing
0
 
LVL 1

Author Comment

by:WingYip
ID: 10899422
Oh by the way forgot to ask.  I need more info on SSL do you know where I can find some?

Wing
0
 
LVL 13

Expert Comment

by:danblake
ID: 10899846
Yes, but what part of SSL -- its a big subject...

What is SSL, How to establish SSL Connections between sql-servers ?

All SSL is a secure socket layer representing a protocal for data-communications.
Have a look at webopedia for a definition of SSL, and some good key-link resources....
www.webopedia.com...

If interested in anything specific, ask a new Q, and I'm sure you're get some response.
0

Featured Post

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question