SQL Server Distribution and Security Issue

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL Server Distribution and Security Issue

Author

Topic

yakub_andre
Starting Member

1 Post

Posted - 2011-12-20 : 03:50:51

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 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.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources