Is is possible to create a view based on a table in a different server?

My client have an azure service app MyServiceApp, its database called MyCloudServer (its data is taken from a legacy MyPremiseServer).

The MyPremiseServer is a server located on my client’s site, because the cloud’s service app MyServiceApp can’t access it, They created the MyCloudServer with the exact same scheme of MyPremiseServer.

In order to make the MyCloudServer to hold the same data of MyPremiseServer, they developed a MyCopyService which is a periodic .net service that just bulk copy each one of the data of MyPremiseServer into MyCloudServer, it does it with accessing the MyPremiseServer views with entity-framework, and just bulk copy into the correspond view in MyCloudServer which has two databases: MyDb and MyDbMirror (the copy process tells the app service to stop using the MyDb, and start using MyDbMirror while the copy process is in progress, delete the old data inside each table, bulk copy it from the correspond table in MyPremiseServer and by the end tells the app service it can start using again the MyDb instead of MyDbMirror).

The copy whole process is being executed once in a hour which is causes the app service data to use a not fully updated data (from last hour), and the copy process cost me a lot of effort to maintain, so my question is: Is it possible to create a view in MyCloudServer that will be based upon a view located in MyPremiseServer (a different server)?

I know for sure is can be done for querying a table in another database like:

USE [DatabaseA]
CREATE VIEW [dbo].[MyView]
AS
SELECT        DatabaseB.dbo.Events.*
FROM            DatabaseB.dbo.Events
GO

Answer

You cannot create linked server in Azure SQL.

You can sync data between on-premises server & Azure SQL, using below approaches:

• SQL Data Sync
• Azure Data Factory pipeline
• Create a linked server in MyPremisesServer and merge to MyCloudServer as regular job Create linked server in onpremises