SQL Server Configuration

[boanrb]

Hi all,

I need some help with the above.

I have an application written in visual foxpro which basically manages sales and the inventory.

A need has arisen to use a central SQL Server database so that all sales from all regions will be reflected on that database - the whole of East Africa.

VPN is a bit expensive for us. For now, a web-based solution is still out of question because of the complexity of the POS. We would rather turn it into an offline/online system by doing a separate app to sense the internet connection and update accordingly.

User's will work normally updating their LAN but this app will be responsible for posting all the data to the central SQL database at an interval of say 30mins. I can handle this for normal operations - accessing SQL Server on the LAN.

But in this particular case, I want to depend on the internet connection and have the program that will do the updates on the SQL Server use the internet connection to connect to the remote SQL database and send updates.

Can someone please assist me on where to begin. I know almost nothing on web publishing and configuring a web server. Kindly.

Thanks all.

Asumwa

 

[mrdenny]

In order to do this in the most secure method (which I assume you want) you'll need to do some learning about .NET development.

What you'll want to do is setup some web methods on a web server which will be exposed to the public Internet. These web methods will have parameters which will accept the various values from your local stores. The web methods will then log into the SQL Server database and make the updates.

Within the web methods be sure to provide fields for a username and password so that you can authenticate the connection to ensure that no one else is trying to upload bogus data to you, and secure the web server with an SSL certificate.

I can't give you any specifics on how to setup the Web Methods, but check over in the ASP.NET forum (forum855) and I'm sure they can provide some information to you.

Once the web methods are setup, you'll just need to modify the app at the stores to connect to the Web Methods at the main office as needed and upload the sales data.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[boanrb]

Denny,

Thanks. I'll try out your approach.

 

[MDXer]

While your looking at .NET Web Methods be sure to include encryption and decryption capabilities. If you are sending you data across the internet then you will want to secure it.

 

[mrdenny]

MDXer makes an excellent point. Encrypt as much data as possible when sending it over the wire (all of it if you can) as you'll be sending it over the Public Internet so you don't want people eves dropping on your data.

Denny
MVP
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / SQL 2005 BI / SQL 2008 DBA / SQL 2008 DBD / SQL 2008 BI / MWSS 3.0: Configuration / MOSS 2007: Configuration)
MCITP (SQL 2005 DBA / SQL 2008 DBA / SQL 2005 DBD / SQL 2008 DBD / SQL 2005 BI / SQL 2008 BI)

My Blog

 

[MDXer]

Depending on the data you may have specific requirements to legally send data over the internet, such as credit card and such.

 

[hmckillop]

Have you had a look at SQL replication - it will do exactly what you need and you can even use SSL to encrypt the data .

I have recently completed this and you can do it over a LAN or internet, it will synch as you request e.g. 30 mins, encrypted etc.

There are many articles on the web but I will soon post some pointers.

Why right an app when there is inbuilt function to do this already.

"I'm living so far beyond my income that we may almost be said to be living apart