讓Azure SQL Database 可以跨DB查詢資料表

Edward Kuo
Jul 16 · 2 min read
Image for post
Image for post

使用Azure SQL Database PaaS服務都知道,如果在同一個Azure SQL Database Server建立兩個資料庫,即使你的帳號可以讀取兩個資料的資料表,但是,實際上你卻不可能像用VM或是地端SQL一樣,直接跨DB讀另一個DB的資料表。

Image for post
Image for post

目前可以用的做法就是透過EXTERNAL DATA SOURCE方式來讀取資料。做法可以共四步驟

  • 建立MASTER KEY ENCRYPTION
  • 建立 DATABASE SCOPED CREDENTIAL
  • 建立EXTERNAL DATA SOURCE
  • 建立對應資料表

使用上述的Script,把需要置換的換成你要的就可以。如果事前已經有設定過Master Key 這裡也可以不需要設定,這只需要設定一次。

Image for post
Image for post

在外部資料來源就可以看到剛剛設定好的來源端。之後就是要設定資料表,基本上我們會設定資料表會跟來源端都是相同。而建立資料表一定要用CREATE EXTERNAL TABLE 而不是CREATE TABLE

建立好之後,就可以在外部資料表看到剛剛參照進來的資料表,同時,資料也在裡面了

Image for post
Image for post

透過這樣方式,就可以做到跨DB查詢對方資料表的資料了。如果來源端DB被很多DB作為外部資料表,這台DB效能就必須開高一點,不然在查詢時候會出現效能的瓶頸

Design,Thinking,Coding & have fun every thing

Edward Kuo

Written by

Enterprise IT Manager / Microsoft Regional Director / Microsoft MVP / DevOps Expert / Speaker, About me: https://profile.edwardkuo.dev/about/

EK.Technology Learn

Design,Thinking,Coding & have fun every thing

Edward Kuo

Written by

Enterprise IT Manager / Microsoft Regional Director / Microsoft MVP / DevOps Expert / Speaker, About me: https://profile.edwardkuo.dev/about/

EK.Technology Learn

Design,Thinking,Coding & have fun every thing

Medium is an open platform where 170 million readers come to find insightful and dynamic thinking. Here, expert and undiscovered voices alike dive into the heart of any topic and bring new ideas to the surface. Learn more

Follow the writers, publications, and topics that matter to you, and you’ll see them on your homepage and in your inbox. Explore

If you have a story to tell, knowledge to share, or a perspective to offer — welcome home. It’s easy and free to post your thinking on any topic. Write on Medium

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store