We have an application (vendor provided) writes/reads data to/from SQL Server 2016 whenever the application is accessed. The database is located on shoreside, and the network connection between the application and database is Very Very Good (a dedicated high speed LAN Connection).
Our options so far
Now, we want to use the same application on our ships. We have three options now:
- Create a separate database instance on shipside and let the application write the data to the database.
- Create a new node in Ship1, form a Always On (Sync Mode) High Availability Group Cluster with the another node on shoreside. Let the application uses the database cluster through SQL Server Listener and write/read database. The connectivity between two databases (shoreside database and shipside database) is through satellite link. The users on the ship will use the application on shipboard. Shoreside Application is connected to the Shoreside Database. Shipside application is connected to shipside database. The application (shoreside/shipside) is configured as ACTIVE-ACTIVE, bothe the databases are connected on Always On SYNC Mode.
- Do not create a separate database. Use the same database on shoreside, let the application on shipside use shoreside database through satellite link connection. I don't want to consider option # (3) here, as the satellite link goes down, the application users on the shipside can't access the database.
With option (1), we have manage 'n' + 1 SQL Server installations and 'n' + 1 Application Instances where 'n' being number of ships. All database and applications are completely independent. This option provide the highest level of availability and it also comes with the highest level of administrative effort. In our case 'n' is 85, so we have to manage 85 web application instances, 85 separate database servers and this is without considering DR options. If we consider DR, then this will be doubled.
With option (2), We have to manage one web application, and one database instance. The web applications across the ships can be configured as ACTIVE-ACTIVE, so users from shoreside/shipside can use the highly available web application. At SQL Server "backend", we have the shoreside node and shipside nodes are connected on ALWAYS-ON SYNC Mode. Like this, all 85 ships database nodes are connected, making it single database instance across all ships.
My questions are about option (2).
- What happens if one ship carrying a database node goes offline?
- Considering the scenario, what happens if Shoreside application wants to perform "WRITE" action on the database, but all the 85 ship database nodes are offline? When this "write" action will be completed and all database nodes are synchronised?
- What happens if shipside application wants to perform "read" when other nodes are offline?