Solutions for distributing Website on SQL SERVER

We are going to put our servers in two places to keep high level redundancy and to avoid bandwidth critics. The two servers are on different LANs but communicate over a WAN network (via Internet). One of the servers is the main server and the other a secondary server. The problem is that the software doesn’t consist just of a database but has also files on the main server that must be transferred to the second server, too. The Server is using SQL SERVER 2005 as its database. The main requirements of our design are the following:

1. We are using SQL Server 2005 and are going to upgrade the database to Oracle in future. So we must consider maintenance and evolution issues.
2. The files (images, movies etc.) have to be transmitted to the second server, too.
3. The relation between the two servers which is around the internet must be completely secure. Security is one of the major points. The only port that is open on the main server is Port 80 for HTTP request which is just read-only and all other ports are closed which proved to be a very secure option.
4. The bandwidth between the two servers is very limited and we don’t want to burden the main server.
5. The second server must be writeable but any modification at the second server is not going to be posted back to the main server. So we have a one-directional transaction and don’t want a bi-directional one.

1.Solution I

Data transmission between the SQL Servers: Transactional Replication File transmission between the servers: Cute FTP Security Option: VPN In this solution we are going to use VPN to secure the relation between the two servers. The data between the two SQL SERVERs are being sent via Transactional Replication.

2.Solution II

Data transmission between the SQL Servers: Backup and Restore File transmission between the servers: Cute FTP Security Option: VPN Here we are going to back up the database every six hours and sent the data with the files through a secure tunnel – VPN – to the second server via FTP. The disadvantage of this solution is that it uses much of the bandwidth and costs much more time than the first solution.

3.Solution III

Data transmission between the SQL Servers: Web synchronization with Merge Replication File transmission between the servers: WebDAV over SSL Security Option: - Here we use Merge Replication for our Replication although we are not going to use the bi-directional option of Merge Replication. We are going to use Web Synchronization instead of VPN. To transfer the files to the second server we are going to use WebDAV over SSL to secure to connection. The possible drawback of this option is that I am not sure that the data transmission will be secure and may cause security lacks on the main server. Even for Web synchronization we have to open the 443 port which may also cause security matters especially that we are not using VPN in this solution.

4.Solution IV

Data transmission between the SQL Servers: Transactional Replication File transmission between the servers: FTP or WebDAV over SSL Security Option: Configuring Proxy Server The proxy server is configured as a multihomed server to prevent unauthorized users on the Internet from accessing the internal server running SQL Server. The proxy server is configured as a multihomed server to prevent unauthorized users on the Internet from accessing the internal server running SQL Server. In this option we have to open ports: 1433 and 21. I am not sure that this causes security lacks especially that we are not using VPN in this solution. Note: You consider that we are not using features like Mirroring or Log Shipping. We cannot use features like Mirroring in SQL Server, because in these cases the standby server is either unavailable or - if using snapshot – just read-only.

I prefer to use Solution Nr.1 because of security and performance matter. I’ll appreciate your advice and I am looking forward to get your opinion about my design solutions. If you know another forum that would be better toward this, please inform me. I am awaiting your reply impatiently.

Hi yakub_andre,

I have never configured such system before. So I can not give detailed technical advice. But I can tell you my idea about the general concept.

Security is always an important factor while planning the systems. On any options you choose, you may consider opening the ports to only connecting server's IP address using both firewall + application you use (SQL Server, FTP etc.).

Solution 2 did not really look like a good solution for me as it is not updating (if I did not get it wrong) instantly. If I had two servers to balance the resources like network, bandwidth etc., I would prefer an option which allows the both file and database changes updated immediately.

On Solution 3 and 4 you talk about not using VPN. I don't think that there will be a security problem if you use SSL Security. But in this business adding more security options make you sleep better, as long as they don't cause any performance issues.

So in this case, I'll agree with you with the solution 1.

I hope I could help.

Regards