asked
Solutions for distributing Website on SQL SERVER
Solutions for distributing Website on SQL SERVER Subject: 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.
Your preferred solution sounds about right. VPN between the machines is imperative in my opinion - you want a clean connection without opening the servers up too much, VPN offers that in the easiest way. The file synchronisation outside of SQL Server could be achieved through windows distributed file system, that is like transactional replication from the SQL Server side. You effectively mark a folder(s) for replication and windows handles the work for you, we use it at my current employer across multiple locations around the world. My only issue would be with the updates on the subscriber side that should not go to the publisher. This can break your replication if you are not careful. Example: Table1 has 100 rows in it and is replicated from Server1 to Server2. You delete row 1 on Server2, this is not done on Server1 - you now have a mismatch. You then update row 1 on Server1, this is successful, but the row is not updated on Server2 because it doesn't exist. You now point your website at Server2 because Server1 has gone offline. You now want to access row 1, but it is gone. You now have a problem! To add to the example, what happens if you have to rebuild Server1? Would you be using the data from Server2? If so, that row 1 is lost forever! Why don't you want changes made on Server2 to be pushed back to Server1? You no longer have a synced data-set and will have issues when a failover occurs. I think the way you want to connect (VPN) is a safe option, but the data-sync needs to be re-analysed.
Thanks for your reply. About your concern of not updating Server 1 let me explain you and ask you something. The only modification task that is being performed on the Server 2 is just INSERT and UPDATE (not DELETE). And this modification on the Server 2 is due to a lack in the application, which cannot be corrected right now. So we don’t want to send the updated record back to Server1. My question and concern is the following: 1. The application and database uses incremental identity (integer) as its primary key in all tables. So consider the following case: 2. Server1 inserts record with (Primary Key field) 20. 3. Server1 replicates record (Primary Key field) 20 to Server2. 4. Server2 inserts new record in that table: (Primary Key field) 21. (It’s not replicated back to Server1, you know). 5. Server1 inserts new record (Primary Key field) 21. 6. Server1 replicates record (Primary Key field) 21 to Server2. There will be a conflict with the previous record 21 in Server2. Look, we don’t even need the new data in Server2. As I told you the new data in Server2 is just a result of a lack in the application design. We just need the data in Server1 and it would be fine if Server1 is going to replace that data (21). Is that possible in Transaction Replication in SQL SERVER 2005? How? We don’t want Server2 to replicate back to Server1.
