1

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:

  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.

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:

  1. 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?
  2. 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?
  3. The two servers must be in one forest. I am not sure that these will work in our case!? Does it reflect security?
  4. 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.

1
  • I can't edit yet, but please add tag sql-server and specify what kind of sql server are you talking in the topic. it's hard to find in the question at first glance.
    – Fox
    Dec 20, 2011 at 9:28

2 Answers 2

6

If I'm reading this right, and I like to think that I am, you've actually got quite a simple problem. But only if you're prepared to modify any of your 4 solutions.

I'm also going to assume that your budget is in the $1-10M USD ballpark, given that you've expressed an interest in migrating to Oracle in the future, and if you don't have a sufficiently high budget, well, you're screwed.

So. The TL;DR summary of your above question is the following. "We would like to create a high-availability database cluster, with two (or more) servers in two (or more) sites."

I don't know why you keep mentioning FTP and WebDAV, given that you seem to be quite concerned about security. FTP is anything but secure. WebDAV is anything but high performance.

First step. Gain and maintain absolute control of your network. You will need decent connectivity between your sites. I'm thinking somewhere between 10 and 100Mbit of uncontended, unfiltered, "carrier-grade" internets. At each site, you'll need a decent VPN router. Something Cisco ASA-like would be a good start. Then you set up a AES secured IPSec VPN between your two sites.

To replicate files between the two servers, you can use DFS. This is a built-in, multi-master filesystem replication tool in Windows Server 2003 and up. I'm not going to explain the exact ins and outs of setting up DFS. That's a different question entirely.

To replicate the database, there are a number of alternatives available to you, and they each offer a different benefit. You haven't said much more about your application architecture, or whether you hope to benefit from improved availability, or improved scalability, or both. Again, the actual process of setting up replication is an issue for another question.

I propose that you do the initial sync of the new database server 'by hand', with a portable USB hard disk, which will probably be far faster than a sync over the internet.

You also haven't mentioned anything about the infrastructure of your network, do you have separate webservers from database servers? Do you plan to replicate those too?

TL;DR: Use industry best-practice methods of site-to-site links with a secure VPN, Use built-in replication systems for database and filesystems. Forget about FTP, WebDAV and all that other shit, and do it properly. If you do it properly, it's less likely to bite you in the arse.

0

As for the SQL Server part of it, you'll want to use Transactional replication.

For the file replication you'll want to look at DFS. If that isn't going to work because of your AD setup you'll want to look at robocopy using the /sync option. DFS has nothing to do with security, it's a data replication solution. To use DFS it does require that you have a single AD forest between the two sites. This is normal for a multi-site company.

You'll probably want to look at upgrading from SQL 2005 to something newer. Microsoft is about to release the third major version after that.

If you are having performance problems because of the users accessing the website you'll probably need to increase the network speed at both sites so that it can handle the network traffic of all the data replication which you'll have to deal with.

Why the need to only have the data that the users in the second site write only available in the second site? If the application is only used (or mostly used) from the second site, why not just move the application to the second site and have anyone who needs to access it from the first site simply connect to the server in the second site?

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .