Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 440
  • Last Modified:

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

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
WingYip
Asked:
WingYip
  • 3
  • 3
1 Solution
 
YounkmanCommented:
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
 
danblakeCommented:
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
 
WingYipAuthor Commented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
danblakeCommented:
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
 
WingYipAuthor Commented:
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
 
WingYipAuthor Commented:
Oh by the way forgot to ask.  I need more info on SSL do you know where I can find some?

Wing
0
 
danblakeCommented:
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now