Alwayson HA

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:

  1. Database A– database where real time device information is stored
  2. 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.

Since this sounds like a certification exam question, you'll have to show some work on your part before we can advise you on anything.

You can find a very comprehensive list of high availability options for SQL Server here:

It's relatively recent, and has multiple options that would suit the question's parameters. Most of them don't use any AlwaysOn features (which is a marketing term by the way, it has nothing to do with availability groups)

Some free commentary:

  • AlwaysOn features would conflict with several of the requirements
  • Some of the parameters/requirements sound like red flags. Whether this was intentional by the exam writers or not I can't say, but the options they are indicating are not ideal
  • Some of the terminology used in the requirements is incorrect or outdated