Solved

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

Posted on 2004-04-22
7
421 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
DTS Connection Failed 7 66
MS SQL Server Management Studio, Save query 2 17
SQL Help 27 40
Find results from sql within a time span 11 29
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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…

813 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

10 Experts available now in Live!

Get 1:1 Help Now