To create a query from an Azure server SQL Database to an on premise server server database.
This query used to be made using linked server objects from two on premise servers with their respective databases.
I have already successfully done cross database queries between two databases on the Azure cloud server. However, I have not been able to replicate the similar aspect that a linked server object can provide between an Azure server and an on premise server.
On serverA I have created a linked server object to serverB. My two on premise servers communicate as such below:
--serverA = on premise server
--serverB = on premise server
Using mycn As New SqlConnection("Data Source=serverA;Initial Catalog=DatabaseA;User Id=username;Password=pwd")
Dim query As String = "SELECT * FROM [DatabaseA].dbo.tableA " &
"INNER JOIN [serverB].[DatabaseB].dbo.tableB ON tableA_ID = tableB_ID"
End Using
In order to communicate with my on premise server to my Azure SQL server I must create an external data source... I believe my problem relies in my external data source.
-- ===========================================================
-- Create external data source template for Azure SQL Database
-- ===========================================================
IF EXISTS (
SELECT *
FROM sys.external_data_sources
WHERE name = N'serverB_DataSource'
)
DROP EXTERNAL DATA SOURCE serverB_DataSource
GO
CREATE EXTERNAL DATA SOURCE serverB_DataSource WITH
(
TYPE = RDBMS,
LOCATION = N'serverB',
DATABASE_NAME = N'databaseB',
CREDENTIAL = myCreds
)
GO
Since I am trying to access my on premise server called serverB from the Azure server, do I need to specify it's actual IP? Not sure what I'm missing here...
Since I am trying to access my on premise server called serverB from the Azure server
you can't do this.. but you can do other way around
Lets say your azure server name is AZ and onpremises server name is B... you can create a linked server for AZ in B and query AZ from B