您现在访问的是微软AZURE全球版技术文档网站,若需要访问由世纪互联运营的MICROSOFT AZURE中国区技术文档网站,请访问 https://docs.azure.cn.

使用 GitHub Actions 连接到 Azure SQL 数据库Use GitHub Actions to connect to Azure SQL Database

使用工作流将数据库更新部署到 Azure SQL 数据库以开始使用 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.

将占位符 server-name 替换为在 Azure 上托管的 SQL Server 的名称。Replace the placeholders server-name with the name of your SQL server hosted on Azure. subscription-idresource-group 替换为连接到你的 SQL Server 的订阅 ID 和资源组。Replace 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 数据库并打开“设置” > “连接字符串” 。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_passwordReplace 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_NAME 替换为服务器的名称。Replace 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