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. We are using SQL SERVER 2005
The main requirements of our design are the following:
- 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.
- The files (images, movies etc.) have to be transmitted to the second server, too.
- 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.
- The bandwidth between the two servers is very limited and we don’t want to burden the main server.
- 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.
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.
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.
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.
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.
Summary: I prefer to use Solution Nr. 1 because it solves all security issues and proved to be safe. Due to performing Transactional Replication it will be fast and can be extended to other database sources like Oracle in the future and matters the bandwidth issue. I am waiting for your suggestion and comments about my solutions and look forward for any other idea.
Thank you very much for your reply.
Let me give you a much clearer picture of our case. We have right now a server on a WAN network. This server contains both, the database and the web application. The problem is that the number of users of our company is very high but our connection line is very low and it lacks for bandwidth. So when they start to use the web site on the WAN, it is unusable.
So we decided to run another slave server on our LAN connection which will be synchronized by the main server. The users of the company will use this server as a performance issue. I think that this explanation makes the subject much clearer to you.
So I want to use replication for the database (SQL SERVER 2005) not for failover or availability. It is just the problem I explained before. You have to pay attention that our bandwidth is very slow.
And I am using VPN connection because the security is critical for me and I don’t want to open any unnecessary ports and lacks.
Your advice to use DFS for replicating files looks fine. But there are some restrictions and requirements for DFS:
- We are using a secure relation through a VPN channel and the data is encrypted. So why using DFS at all when FTP data is sent encrypted through a secure VPN channel?
- The servers that will participate in DFS Replication must run a Windows Server 2003 R2 operating system. But our server is just Windows Server 2003 Service Pack 1 but not R2. Does that work for that, too?
- The two servers must be in one forest. I am not sure that these will work in our case!? Does it reflect security?
- Can you give me an advice about some useful DFS e-books?
I hope that I’ve offered you enough details. I am waiting for your reply.