0

I have a VM with SQL Server and an application that uses no more than 50 users. I don't require to have a zero downtime application in case my VM or datacenter had an issue, but what I need at least to assure is that I can make the app available again in less than 30 minutes.

  • First approach: using an Availability Set with 2 VM's won't work actually because my SQL Server lives in the same VM and I don't think Availability Set will take care of the real time replication of my SQL Server data, it will care only about the web application itself and not the persistent data (if I'm wrong please let me know), so having the above statement AV Set is not for me. Also It will be twice expensive because of the 2 VMs.

  • Second approach: using Recovery Site with disaster recovery I was reading that wont warranty to have a zero data loss, because there is a minimum frequency of replication and I think is 1 hour, so you have to be prepared to deal with 1 hour of data loss and I don't like this.

  • Third option: Azure Backup for SQL Server VM, this option could work the only downside is that has a RPO of 15 minutes that is not that much, but the problem is that if by some reason the user generates in the app some critical records we wont be able to get them again into the app because the user always destroy everything right away when they register into the app.

  • Fourth approach: Because I don't really require a zero downtime app, I was thinking on just having the actual VM using 2 premium disks one for SQL Server data files and other for SQL Server logs. In case of a VM failure I will get notified by users inmediately and what I can do is to create a snapshot of OS disk, and SQL premium disks (total of 3) and then create a new VM using these snapshots, so I will get a new working VM maybe in a different region having the exact very last data inserted into SQL before the failure happened.

Of course I guess I will need on top the VM a load balancer so I can just reroute traffic to the new VM. The failed VM i will just kill it and use the new VM as my new system. If fail happens again I just follow same process so this way I just only pay for one VM and not two.

Is this someone has already tried, does this sound reasonable and doable or Im missing a big thing or maybe I wont get what I expect to get?

Pod Mo
  • 248
  • 2
  • 8
VAAA
  • 14,531
  • 28
  • 130
  • 253

1 Answers1

0

You better use Azure SQL (PaaS) instead of VM, there are many different options that you can do for your needs. Running SO + SQL in the same VM is not recommended, changing to a Azure SQL (PaaS) you can decrease your hardware for SO VM and configure your SQL for supporting 50 users. Also you can use Load Balancer as you said, either Traffic Manager (https://learn.microsoft.com/pt-br/azure/traffic-manager/traffic-manager-overview) or Application Gateway (https://learn.microsoft.com/pt-br/azure/application-gateway/overview) to route traffic to your SO VM's where the application is running. Depends on your application you can migrate to Azure Web App (https://learn.microsoft.com/en-us/azure/app-service/).

Azure SQL (Paas) you can have less than 30 minutes for sure, I would say almost zero down time although you don't required it.

Automatic backups and Point-in-time restores https://learn.microsoft.com/pt-br/azure/sql-database/sql-database-automated-backups

Active geo-replication https://learn.microsoft.com/pt-br/azure/sql-database/sql-database-active-geo-replication

Zone-redundant databases https://learn.microsoft.com/pt-br/azure/sql-database/sql-database-high-availability

Finally I don't think having Always-on (https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-availability-groups-sql-server?view=sql-server-ver15) solution is good, once it is expensive and there are only 50 users. That's why I believe you better thinking of a Saas + PaaS solution for your application and database. Your 4th option sounds fine, but you need to create a new VM, configure IP, install SQL, configure SQL and so on to bring up your SQL.

What users is going to do if it happens when you are not available to fix it immediately? Your 30 minutes won't be accomplished :)

Taguada
  • 428
  • 1
  • 5
  • 8