Distributed Databases using SQL Server Database replication

@RibeyedMar 19.2003

hi Forum,
I have a client that has the need for a distributed database. Presently the e-commerce site is hosted on the host’s server. At my clients retail shop they are currently about ready to install a customized EPOS system. Now both databases are requiring the same set of data, i.e the products and categories etc. The e-commerce website has its own content management system allowing my client to update the e-commerce site from any location. My client needs to be able to do updates via the content management system, these updates are then replicated from the database at the hosts to a database located at the retail shop.
So hopefully you can see that our best and most logical way is to use the SQL Server built in functionality, Database replication. My problem now is that our host is not willing to allow us to replicate our database on there server.
I have a few questions about this;

Does anyone know of another way I can replicate an SQL Server database without using the SQL Server built-in database replication function?
Currently I am looking for a host that can support database replication.
Has anyone every heard of database replication using Queries from SQL Server and not the database replication function?
Does anyone think it would be possible to build some ASP server-side script that could mimic database replication?
If so, can I automate ASP to automatically replicate the database are a set interval?
I still can’t believe that my host will not allow database replication surely a lot of companies have been in the same boat as us. I am sure database replication is a common thing and there are bound to be companies doing this but not just internally on there own servers but from hosts to retail shops.
Someone correct me if I am wrong but I am sure it should just be the case of configuring a VPN between the 2 SQL servers then set up a SSL to handle encryption, configure SQL server on both ends for database replication and that would be it. My client has invested a lot of money into this project and I have invested almost 2 years developing this project for them and I am not ready to through in the towel because my host can support database replication.
Anyway any help on this matter would be much appreciated, thanks

to post a comment

Full-stack Developer

5 Comments(s) _↴

@DamienMar 19.2003 — #Someone correct me if I am wrong but I am sure it should just be the case of configuring a VPN between the 2 SQL servers then set up a SSL to handle encryption, [/QUOTE]

There's no problem doing that if you using a dedicated server for the project. However, there may be a problem with virtual space on a server.

What is the hosting setup?

@RibeyedauthorMar 20.2003 — #hi,

thank you for your reply. I see you are UK so you would possible have heard of Telewest they are my host. I have no access to server specs at moment but they are running Windows 2000 possible Advanced Server maybe Data centre server. This is on T3 lines. Can you describe further any issues you foresee with virtual space on there’s or our server?

Sorry can’t provide more info on hosts server setup ?

@DamienMar 20.2003 — #First lets look at the ideal environment, a dedicated server running W2K Server and SQL2000, a firewall VPN box on top of that. Therefore, using the VPN between you and the server would be just like working on your own machine and very secure, you could replicate any database easily enough while the server is dishing out the content of the storefront.

The problem with virtual space is the willingness of your hosting provider to setup anything outside the norm of average hosting. Very few clients will ask for a VPN or ask for the ability to install a DLL, so they tend not to get into it.

But back to your question “is it possible” of course it is.

If you run into problems with your host, drop me an email and I’ll request a price from the data centre I use.

P.S. Just a small point, Dublin is not in the UK

@RibeyedauthorMar 20.2003 — #Hi Damien,

please accept my apologies for saying Dublin is UK. Thank you for your input in to this matter.

Its is not the question of is it possible, my hosts reasons why they well not supply this service; 1) Security, 2) Bandwidth, this was from there system administrator. I have a friend that is network security manger at the Alliance & Leicester bank. After talking to him more about VPN's he explained that it is like forming a tunnel between the 2 PC's, as this tunnel is between 2 PC's and not over the internet then no one can hack in to the VPN.

Based on this my question would be is how i would be able to configure SQL Server to use the VPN?

Would it just be the case of creating a VPN connection in Windows then using ODBC link to the second PC with another SQL Server?

My client today is now talking about host the web site there self. Being a computer retailer this should be no problem supplying hardware and software to do this, but I am still open to less costly solutions. If you can forward any information you have regarding the host services at the data centre you suggested that would be great.

[[email protected]]david[/EMAIL]

@DamienMar 21.2003 — #Your friend in the Bank used an excellent analogy of VPN.

There's a tad more than just the ODBC link, If you are using two databases on two different machines, you could use database clustering. SQL server will handle this. The two databases will work as one in real time. Have a look at the docs here http://www.microsoft.com/windows2000/technologies/clustering/default.asp

I forgot to mention that using virtual space, your hosting provider may have to licence each user on the server(although I'm not fully sure) which is maybe why they won't allow it. Security and bandwidth seem like a lame excuse when that's what their selling.

If you are just using the software of SQL 2000 to setup the VPN, creating the database cluster will take you through a stack of screens. If your client is planning self hosting you should have no problem practicing this before going live.