I would like to know if it somehow is possible to have a SMB share point towards the LAN NAS but when connecting to the share from an offsite location, it would use the backup remote share that is hosted e.g. on Azure instead. In the background I would arrange a sync that keeps both shares synchronized.
Is there some tool that does some kind of load balancing or failover that is easily configurable and affordable?
Our environments exists out of recent Windows (server) machines.
What you are describing sounds like Azure Files Sync. It's a GA service now but this preview announcement does a good job of describing what it is and how to use it.
https://azure.microsoft.com/en-us/blog/announcing-the-public-preview-for-azure-file-sync/
Use Azure File Sync to centralize your organization's file shares in Azure Files, while keeping the flexibility, performance, and compatibility of an on-premises file server. Azure File Sync transforms Windows Server into a quick cache of your Azure file share. You can use any protocol that's available on Windows Server to access your data locally, including SMB, NFS, and FTPS. You can have as many caches as you need across the world.
https://docs.microsoft.com/en-us/azure/storage/files/storage-sync-files-planning
