?
Solved

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

Posted on 2004-04-22
7
Medium Priority
?
426 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
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 13

Accepted Solution

by:
danblake earned 500 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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 to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

765 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