You are assigned to the project that is implementing new monitoring and alerting system. The system consists of monitoring devices connected to the application server and it is storing information in the SQL Server databases.
There are three sites and only staff who are working on the site needs to be alerted by the system. Due to the application limitations, to ensure that staff at different sites are not alerted to the events, separate application servers will be created for each hospital site.
Each application server requires two databases with the following names:
- Database A– database where real time device information is stored
- Database B – replica of certain tables from database A used for reporting
Application is utilizing SQL Server Reporting Services (SSRS) to run reporting dashboards for the device status, based on the data from database B.
The two databases can be on the same SQL Server instance or on different instances, however, database names are hardcoded in the application and must have the same name for each site – the only configurable parameter is the name of the SQL Service instance and the account used to create the databases. Application installation is also creating SSRS reports which are created with identical folder and report names.
As the Senior Database Administrator, you are tasked to design the SQL Server infrastructure solution to host the databases for this application (six databases in total). The solution must be hosted on-premises on the hospital network, which covers all three sites, and it is running on the Virtual Machines with optional vMotion capability.
Prepare 3 design options and discuss technical and financial pros and cons for each option. Solutions should be able to host SQL databases in Highly Available mode and be cost effective both from the perspective of hardware resources and software licensing.