0

I can see my SQL Azure database in SQL Server Management Studio, and am able to execute queries, create objects, etc etc, in SSMS. The VPN is working.

I know there are (or will be) synchronization utilities available from Microsoft, but let's say we didn't want to use them. Is it possible for a stored procedure, running locally in an on-premise instance, to establish a connection to the remote (cloud) database, and then reference a table in the cloud as if it were local?

        insert azure.dbo.clients CLOUD
        select * from mylocaldb.dbo.clients EARTH 
        where EARTH.lastSyncDate is null

Is it possible to connect to the cloud database as to a linked server, to permit heterogeneous (earth/cloud) queries and data flow?

blint
  • 191
  • 1
  • 9

2 Answers2

0

Yes, you will have to create a linked server entry in your local server and refer to the SQL Azure db as you would with any other linked server. You just have to create an ODBC DSN before you can create a linked server. Check Linked Servers to SQL Azure for details.

After that, you can execute a remote stored procedure by using its four-part name, eg:

EXEC [RemoteServer].DatabaseName.DatabaseOwner.StoreProcedureName params
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • Thanks. I discovered that the xxxxx.servername.windows.net must be pasted into the combo box for the server name and must also be appended to the username, username@xxxxxx.servername.windows.net in the System DSN creation dialog. – blint Feb 04 '12 at 14:04
0

Although Panagiotis is correct, you need to know that there is a disclaimer about this "feature": it is not supported. Here is the official SQL Server support team statement regarding linked servers and SQL Azure: http://blogs.msdn.com/b/psssql/archive/2011/05/03/not-linkedservertosqlazure-where-provider-msdasql-and-dsn-sql-server-native-client.aspx

Herve Roggero
  • 5,149
  • 1
  • 17
  • 11
  • The post you refer to is about OLEDB, not ODBC. The post by Lubor Kolar (one of the creator of SQL Server's optimizer by the way), specifically states that you have to create an ODBC DSN. While it would seem that there are two contradictory "official SQL Server team" statements, they really refer to two different things. – Panagiotis Kanavos Feb 04 '12 at 15:25
  • Hi Panagiotis. I may be mistaken, but the post you refer to basically uses Linked Servers. And Linked Servers use OLE DB to access whatever underlying data source you expose. The fact that you are creating an ODBC DSN doesn't negate the use of OLEDB to access that DSN. Here is the Linked Server architecture that shows what I am referring to: http://msdn.microsoft.com/en-us/library/aa213778(v=SQL.80).aspx. If I am incorrect, please let me know why. Thanks! – Herve Roggero Feb 06 '12 at 18:01