Automated Release to Azure SQL Database in State-Based Development

In this lab, we will begin releasing our changes to downstream databases by using Microsoft Azure SQL Database.

DevOps MPP Course Source

Prerequisites:

Lab Tasks:

  • Prepare your Azure environment
  • Implement a Continuous Release process for Integration

Estimated Lab Time:

  • approx. 40 minutes

Task 1: Prepare your Azure environment

We will set up an Azure environment for releases.

  1. In a browser, open the Azure Portal. In the list of available options, click SQL databases.

    In the Azure Portal, in the left menu, a callout points to SQL Databases.

  2. Create a new database WWI_SSDT_Integration. Also create a new server if you don’t already have a suitable one. Specify SSDTRelease for the server administrator if creating a new server just for this lab.

    In the SQL Database blade, the Database name field displays WWI_SSDT_Integration. Under Resource group, the create new radio button is selected, and the name is greglowazurevsts. The server selected is greglowvsts (Australia Southeast), and the Pricing tier is Standard S1.

  3. Refresh the databases list until the new database appears, then click WWI_SSDT_Integration to open the database blade for your new database. Note the Server name.

    In the WWI_SSDT_Integration blade, in the left menu, Overview is selected. In the right menu, the Essentials section is expanded, and the Resource group greglowazurevsts displays.

  4. From the top menu, click Set server firewall. Configure a rule that includes your IP address (0.0.0.0 to 255.255.255.255 would be the entire internet, but if you are on a fixed IP address, you might be able to make a more-specific setting).

  5. Open SQL Server Management Studio, and in Object Explorer, click Connect, then click Database Engine.

    In Object Explorer, the Connect drop-down menu displays, and Database Engine is selected.

  6. Configure the connection dialog as required, then click Connect.

    In the Connect to Server dialog box, fields are set to the following settings: Server type, Database Engine (grayed out); Server name, greglowazurevsts.database.windows.net; Authentication, SQL Server Authentication; Login, greglow@greglowvsts; Remember password, selected.

  7. In Object Explorer, expand Databases and note that your new database should be present.

    In Object Explorer, greglowvsts.database.windows.net displays.

Task 2: Implement a continuous release process for integration

We will implement an automation release process for continuous integration output.

  1. Open your VSTS account, select the WideWorldImporters-SSDT project, click the Build&Release menu at the top and select Releases. You should see an empty release definition page.

    In VSTS, for WideWorldImporters-SSDT, the build and Release menu is selected. The Releases tab has no release definitions, but provides instructions for how to create a new one.

  2. Click New definition button. You will receive a list of templates to select. We will choose the Empty definition, then click Next.

    Uncer Create release definition, under Select a template, a list of templates displays, and the Empty template is selected.

  3. The next page asks which project and build source to use. The default project and your CI definition should be listed. Check the Continuous Deployment check box then click Create.

    On the Create release definition page, under Artifacts, the Build tile is selected. The Project is WideWorldImporters-SSDT, and the Source (Build definition) is WideWorldImporters-SSDT-CI. At the bottom, the check box for Continuous deployment is selected.

  4. This will create an empty release definition with a default name.

    The Definition*.New Empty Definition section displays, with one environment named Environment 1. A callout points to the pencil icon.

Click the pencil icon next to the name to edit it. Enter Release to Integration as the name.

![Now, the name is Definition*. Release to Integration.](../assets/autorelstatebasedev-jan2018\Lab4.3_Image13.jpg)

  1. Click the name Environment 1 and change this to Integration.

    Now the environment name is Integration.

  2. Click Run on agent line in the middle section. This will open properties on the right. Change the deployment queue to Hosted.

    In the left pane, under Add tasks, Run on agent is selected. In the right, Run on agent pane, the Deployment queue field is set to Hosted.

  3. Click Add tasks in the center section. This should bring up the Task catalog. Scroll down to find the Azure SQL Database Deployment task and click Add.

    In the Task catalog, Azure SQL Database Deployment is selected.

Click Close to close the Task catalog.

  1. The Execute Azure SQL task should be selected with properties to the right. We will configure these items separately in the next few steps.

    The Execure Azure SQL : DacpacTask property fields display, with most of them empty.

  2. Click the Add link beside the Azure Classic Subscription. In the window, configure your subscription details, copying your subscription name and subscription ID from the Azure portal, set the Connection name to WWI_SSDT_Integration_Connection, and click OK.

  3. Enter the properties as listed, substituting your values as needed:

Azure SQL Server Name: yourdatabaseserver.database.windows.net Database Name: WWI_SSDT_Integration Server Admin Login: $(DeployUser) Password: $(DeployPassword)

  1. Click the ellipsis next to DACPAC File. This will allow us to select a package from our build artifacts. Expand the tree to find the .dacpac file in the Database_Package artifact. This should be named for your project with an _DeployPackage at the end. Select it then click OK.

    Under Select File or Folder, the following tree view path is expanded: Linked Artifacts\WideWorldImporters-SSDT-CI (Build)\Database_Package. In the Database_Package folder, WideWorldImporters-SSDT.dacpac is selected.

  2. Click the Variables menu item in the release definition. In the Variables list below, enter two new variables:

    • DeployUser: VSTSRelease (or your server administrator)
    • DeployPassword: The server administrator password. Click the padlock to the right to hide the actual value as a secret.

    In the Variables section, two variables display: DeployUser, and DeployPassword. The DeployUser value is SSDTRelease, and the DeployPassword value is hidden.

  3. Click the Triggers menu item. Continuous Deployment should be selected. This will trigger a release when the CI build completes.

    Under Release triggers, the Continuous Deployment check box is selected.

  4. Click Save to save the release. Enter a comment **for the version control of this definition, then click **OK.

    The Save comment field text is Initial release definition.

  5. Attempt to perform an initial release by clicking Create Release from the Release drop-down menu, then in the window that appears, wait for the data to populate, then click Create.

  6. In the blue bar that appears, click your new release to open it.

    In a blue bar, Release Release-1 has been created displays. A callout points to the Release-1 live link text.

  7. Note that the release has not succeeded. This is because when we originally create the project, the target platform was set as SQL Server 2016, not Azure SQL Database.

    On the Release to Integration / Release-1 page, on the Summary tab, under Environments, the Deployment status for Integration is Failed. Under Issues, three errors display.

  8. Make sure your VSTS Agent is running, then in Visual Studio 2017, open the WideWorldImporters-SSDT project. In Solution Explorer, right-click the WideWorldImporters-SSDT project, and click Properties. Change the Target Platform to the latest Azure SQL Database option, then on the Toolbar, click the Save All icon.

    In Project settings, the Target platform is now set to Microsoft Azure SQL Database V12.

  9. From the Build menu, click Build WideWorldImporters-SSDT. You will notice that the build now fails. This is because the database contains some syntax that is not permitted in Azure SQL Database. In this platform service, filegroup management is carried out by the platform service and isn’t managed by users. Filegroups have been scripted in several places. Double-click the first entry in the Errors window and look at the issues found.

    Filegroup errors display, with the word USERDATA highlighted.

  10. From the Edit menu, click Find and Replace, then click Quick Replace. Replace all references to ON [USERDATA] to an empty string. Make sure you replace it in the Current Project.

    The Find and Replace fields display. The find field text is "ON (USERDATA), and the replace field is empty.

    Similarly, replace all references to TEXTIMAGE_ (note this has a trailing underscore).

  11. From the Build menu, click Build WideWorldImporters-SSDT. Now we only have about 2 errors. Again double-click the first error. This script is the definition of the filegroup that we just removed references to.

    USERDATA.sql script displays.

    Remove all text in that script and from the Toolbar, click Save All.

  12. From the Build menu, click Build WideWorldImporters-SSDT. The build should now succeed. In Team Explorer, click Changes, enter a comment, then from the drop-down beside Commit All, click Commit All and Push.

    In the Team Explorer – Changes window, the comment now reads, “Modified database for Azure SQL Database.” From the Commit All drop-down menu, Commit All and Push is selected.

  13. Open your VSTS project and navigate to the Build definitions. You should see the build occurring, and then succeeding.

    Under Build Definitions, on the Mine tab, the WideWorldImporters-SSDT-CI definition has the status of succeeded.

  14. Navigate to the Releases page, as this shows the release having been triggered and proceeding. (It may take some time to complete)

    Under All release definitions, on the Releases tab, the Release-2 line is selected. A callout points to the Environment column, which has a white play button in a blue box. A second callout points to the Description field, which is, “Triggered by WideWorldImport…”

Summary

In this lab you have completed the following tasks:

  • Prepare your Azure environment
  • Implement a Continuous Release process for Integration