Solved

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

Posted on 2004-04-22
7
415 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
  • 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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

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.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

708 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now