讓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效能就必須開高一點,不然在查詢時候會出現效能的瓶頸

EK.Technology Learn

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

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch

Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore

Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade

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