使用 GitHub Actions 連線到 Azure SQL DatabaseUse GitHub Actions to connect to Azure SQL Database

藉由使用工作流程將資料庫更新部署至 Azure SQL Database,來開始使用 GitHub ActionsGet started with GitHub Actions by using a workflow to deploy database updates to Azure SQL Database.

先決條件Prerequisites

您將需要:You will need:

工作流程檔案概觀Workflow file overview

GitHub Actions 工作流程是由您存放庫內 /.github/workflows/ 路徑中的 YAML (.yml) 檔案所定義的。A GitHub Actions workflow is defined by a YAML (.yml) file in the /.github/workflows/ path in your repository. 此定義包含組成工作流程的各種步驟與參數。This definition contains the various steps and parameters that make up the workflow.

檔案內有兩個區段:The file has two sections:

區段Section 工作Tasks
驗證Authentication 1.定義服務主體。1. Define a service principal.
2.建立 GitHub 祕密。2. Create a GitHub secret.
部署Deploy 1.部署資料庫。1. Deploy the database.

產生部署認證Generate deployment credentials

您可以使用 Azure CLI 中的 az ad sp create-for-rbac 命令來建立服務主體You can create a service principal with the az ad sp create-for-rbac command in the Azure CLI. 請使用 Azure 入口網站中的 Azure Cloud Shell,或選取 [試試看] 按鈕來執行此命令。Run this command with Azure Cloud Shell in the Azure portal or by selecting the Try it button.

以 Azure 上所裝載 SQL 伺服器的名稱取代預留位置 server-nameReplace the placeholders server-name with the name of your SQL server hosted on Azure. 以連線至 SQL 伺服器的訂用帳戶識別碼和資源群組取代 subscription-idresource-groupReplace the subscription-id and resource-group with the subscription ID and resource group connected to your SQL server.

   az ad sp create-for-rbac --name {server-name} --role contributor \
                            --scopes /subscriptions/{subscription-id}/resourceGroups/{resource-group} \
                            --sdk-auth

輸出是一個 JSON 物件,內有角色指派認證可讓您存取資料庫,其類似此範例。The output is a JSON object with the role assignment credentials that provide access to your database similar to this example. 複製輸出 JSON 物件以供稍後使用。Copy your output JSON object for later.

  {
    "clientId": "<GUID>",
    "clientSecret": "<GUID>",
    "subscriptionId": "<GUID>",
    "tenantId": "<GUID>",
    (...)
  }

重要

授與最小存取權永遠是最佳作法。It is always a good practice to grant minimum access. 前面範例中的範圍限制為特定伺服器,而不是整個資源群組。The scope in the previous example is limited to the specific server and not the entire resource group.

複製 SQL 連接字串Copy the SQL connection string

在 Azure 入口網站中,移至 Azure SQL Database,然後開啟 [設定] > [連接字串]。In the Azure portal, go to your Azure SQL Database and open Settings > Connection strings. 複製 ADO.NET 連接字串。Copy the ADO.NET connection string. 取代 your_databaseyour_password 的預留位置值。Replace the placeholder values for your_database and your_password. 連接字串看起來會像此輸出。The connection string will look similar to this output.

    Server=tcp:my-sql-server.database.windows.net,1433;Initial Catalog={your-database};Persist Security Info=False;User ID={admin-name};Password={your-password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

您將使用此連接字串作為 GitHub 秘密。You'll use the connection string as a GitHub secret.

設定 GitHub 祕密Configure the GitHub secrets

  1. GitHub 中,瀏覽您的存放庫。In GitHub, browse your repository.

  2. 選取 [設定] > [秘密] > [新增秘密]。Select Settings > Secrets > New secret.

  3. 將得自 Azure CLI 命令的整個 JSON 輸出貼到祕密的 [值] 欄位中。Paste the entire JSON output from the Azure CLI command into the secret's value field. 將祕密命名為 AZURE_CREDENTIALSGive the secret the name AZURE_CREDENTIALS.

    當您稍後設定工作流程檔案時,會將祕密用於 Azure 登入動作的輸入 credsWhen you configure the workflow file later, you use the secret for the input creds of the Azure Login action. 例如:For example:

    - uses: azure/login@v1
    with:
        creds: ${{ secrets.AZURE_CREDENTIALS }}
    
  4. 再次選取 [新增密碼]。Select New secret again.

  5. 將連接字串值貼到祕密的 [值] 欄位中。Paste the connection string value into the secret's value field. 將祕密命名為 AZURE_SQL_CONNECTION_STRINGGive the secret the name AZURE_SQL_CONNECTION_STRING.

新增您的工作流程Add your workflow

  1. 移至 GitHub 存放庫的 [動作]。Go to Actions for your GitHub repository.

  2. 選取 [自行設定工作流程]。Select Set up your workflow yourself.

  3. 刪除工作流程檔案 on: 區段之後的所有內容。Delete everything after the on: section of your workflow file. 例如,剩餘的工作流程看起來可能像這樣。For example, your remaining workflow may look like this.

    name: CI
    
    on:
    push:
        branches: [ master ]
    pull_request:
        branches: [ master ]
    
  4. 重新命名工作流程 SQL for GitHub Actions,並新增簽出和登入動作。Rename your workflow SQL for GitHub Actions and add the checkout and login actions. 這些動作會簽出您的站台碼,並使用您稍早建立的 AZURE_CREDENTIALS GitHub 祕密向 Azure 進行驗證。These actions will checkout your site code and authenticate with Azure using the AZURE_CREDENTIALS GitHub secret you created earlier.

    name: SQL for GitHub Actions
    
    on:
    push:
        branches: [ master ]
    pull_request:
        branches: [ master ]
    
    jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            creds: ${{ secrets.AZURE_CREDENTIALS }}
    
  5. 使用 Azure SQL 的「部署」動作來連線到您的 SQL 執行個體。Use the Azure SQL Deploy action to connect to your SQL instance. 以伺服器的名稱取代 SQL_SERVER_NAMEReplace SQL_SERVER_NAME with the name of your server. 您在存放庫的根層級應該會有一個 dacpac 套件 (Database.dacpac)。You should have a dacpac package (Database.dacpac) at the root level of your repository.

    - uses: azure/sql-action@v1
      with:
        server-name: SQL_SERVER_NAME
        connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
        sql-file: './Database.dacpac'
    
  6. 藉由新增動作至 Azure 的登出,來完成您的工作流程。Complete your workflow by adding an action to logout of Azure. 以下是完成後的工作流程。Here is the completed workflow. 檔案會出現在存放庫的 .github/workflows 資料夾中。The file will appear in the .github/workflows folder of your repository.

    name: SQL for GitHub Actions
    
    on:
    push:
        branches: [ master ]
    pull_request:
        branches: [ master ]
    
    
    jobs:
    build:
        runs-on: windows-latest
        steps:
        - uses: actions/checkout@v1
        - uses: azure/login@v1
        with:
            creds: ${{ secrets.AZURE_CREDENTIALS }}
    
    - uses: azure/sql-action@v1
      with:
        server-name: SQL_SERVER_NAME
        connection-string: ${{ secrets.AZURE_SQL_CONNECTION_STRING }}
        sql-file: './Database.dacpac'
    
        # Azure logout 
    - name: logout
      run: |
         az logout
    

檢閱您的部署Review your deployment

  1. 移至 GitHub 存放庫的 [動作]。Go to Actions for your GitHub repository.

  2. 開啟第一個結果,以查看工作流程的執行詳細記錄。Open the first result to see detailed logs of your workflow's run.

    GitHub 動作執行的記錄

清除資源Clean up resources

當您不再需要 Azure SQL 資料庫和存放庫時,請刪除資源群組和 GitHub 存放庫,以清除您所部署的資源。When your Azure SQL database and repository are no longer needed, clean up the resources you deployed by deleting the resource group and your GitHub repository.

後續步驟Next steps