2

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:

  1. Create a separate database instance on shipside and let the application write the data to the database.
  2. 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.
  3. 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.

Considerations

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

The questions

My questions are about option (2).

  1. What happens if one ship carrying a database node goes offline?
  2. 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?
  3. What happens if shipside application wants to perform "read" when other nodes are offline?

Pictorial Representation (showing just 3 ships for illustration)

Dashed arrows between shipside database servers means connectivity using satellite; generally its poor with packet loss. enter image description here

  • 2
    I don't think you want High Availability, you want a distributed database. The closest thing that SQL has for this would be one of the replication options (Merge, Transaction, etc.). 85 SQL Servers ( or 85 databases) isn't that many to manage, especially if they are all basically the same. – Jonathan Fite Oct 8 at 3:35
  • I want all the Database Servers in Sync.Want I want to ensure here is "If a user modifies data in a ship, the application should write the data to the particular shipside database server even though the other shipside database is offline. When the other ships comes online, all the database should be in sync". How can we achieve this? Or anyother best ways to achieve/make all dbs in sync? – Karthick Oct 8 at 3:53
  • 3
    You to not want Always On Availability Groups (AGs). AGs do not allow Active/Active configurations with multiple Read-Write nodes. You can have one writable node and other nodes must be read only. You can have a maximum of 9 nodes in an AG; You cannot have 85 AG nodes. It sounds like you need a more complicated architecture, probably using something like transactional replication, or a homemade data sync. – AMtwo Oct 8 at 10:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.