|
-18th July 2016
The Case:
The solution that was wished for was a database solution mirroring with automatic failover at the database backend (transparent to the IIS servers and the clients) where two IIS servers serve the frontend to the clients. IIS availability and failover is handled by round robin names in the clients hosts file. The client internet browsers will automatically select the IIS server that responds if one of the addresses in the hosts file is not responding. There is no need f or a reverse proxy in this situation, so no reverse proxy considerations are made. Application Request Routing with IIS could be such a reverse proxy solution that would handle availablity for IIS and the browser clients, but round robin names in the clients hosts files were chosen instead.
Furthermore, Windows Server cannot be used in this scenarion since license costs have to be held down and there are already Windows 7 machines in place that can be used in stead.
The depicted case would look like something similar to this, where the client has chosen one available IIS server and the IIS servers address the database mirroring solution as a single point regardless of what database server is active or what database server is the warm standby;
The available technologies that SQL server ships with for the replication of data between two databases or servers are as follows;
- Database mirroring
- Log shipping
- Always On Failover Cluster
- Always On Availability Group
- Merge replication
- Transactional replication
- Snapshot replication
None of these technologies are available in SQL Server Express version.
For reference: https://msdn.microsoft.com/en-us/library/cc645993%28v=sql.120%29.aspx?f=255&MSPPError=-2147217396
All of these technologies are available on SQL Server Standard, which is paid license software.
SQL Server Standard is possible to install on Windows 7. In this case it was necessary, since server infrastructure was not available.
For reference: https://msdn.microsoft.com/en-us/library/ms143506(v=sql.120).aspx
In regards to what technology is best suited for our case, a short list below will go through the technologies listed above;
Snapshot replication
- Replicates snapshots of data from a publisher (database) via a distributor over to a subscriber (database).
- Does not alone support automatic failover or HA solution
Conclusion: Has to be used together with WSFC (Windows Server Failover Clustering) in order to provide an automatic failover solution. This requires Windows Server.
Transactional replication
- Replicates transaction from publisher (database) via a distributor over to a subscriber (database).
- Can filter what is being replicated
- Does not alone support automatic failover or HA solution
Conclusion: Has to be used together with WSFC (Windows Server Failover Clustering) in order to provide an automatic failover solution. This requires Windows Server.
Merge replication
- Replicates changes from either subscriber or publisher via a merge agent where conflicts are handled by rules set up by the programmer or administrator.
- Can filter what is being replicated
- Does not alone support automatic failover or HA solution, but can receive updates at one server and merge them to the second server when the second server comes back up.
Conclusion: Handling rules for merging data from two different databases might be sufficient for correct data handling in our scenarion.
Setup would be similar to this, not very different from the first illustration:
|
Log shipping
- Automatic transaction log transfer to replica database where the transactions are restored from the copied log and saved.
- Does not support automatic failover, only manual failover.
Conclusion: Has to be used together with WSFC (Windows Server Failover Clustering) in order to provide an automatic failover solution. This requires Windows Server.
Always On Availability Group
- Requires Windows Server Failover Clustering (requires Windows Server).
- Supports automatic failover
- Fails over databases
Conclusion: WSFC (Windows Server Failover Clustering) requires Windows Server.
Always On Failover Cluster
- Requires Windows Server Failover Clustering (requires Windows Server).
- Supports automatic failover
- Fails over SQL Server Instances
Conclusion: WSFC (Windows Server Failover Clustering) requires Windows Server.
Database Mirroring
- Will not be included in future releases of SQL server, and therefore it is recommended by Microsoft to not use Mirroring for compatibility with future SQL server releases.
- Replicates data from a primary database (principal database) to a secondary database (mirror database).
- Supports automatic failover if installed in high-safety mode with a witness SQL server instance (can be SQL Server Express).
Conclusion: This feature will be depreciated in future SQL server releases, but for the purpose of this project might fit right in.
Based on the information written above, and aiming to use a configurable and not a coded solution, it seems we should use Database Mirroring with a witness server.
This requires the following;
- 2 x SQL Server Standard installed on each their Windows 7 (DB1 and DB2).
- 1 x SQL Server Express installed on a undetermined place to act as a witness for automatic failover.
- Configure Synchronous Database Mirroring in High-Safety mode with automatic failover by using the above software.
The backside of this is that this technology will be discontinued. Possibly, one could use SymmetricDS with SQL Express or just use MySQL instead.
Further references are:
Sources: Microsoft
Tagged as: SQL 2016 |
