In this lab, we will begin manually releasing our changes to downstream local database.

DevOps MPP Course Source


Lab Tasks:

  • Prepare your local environment
  • Implement a Manual Release for Integration

Estimated Lab Time:

  • approx. 30 minutes

Task 1: Prepare our local environment

We will set up a local environment for releases.

  1. Open Management Studio and connect to your instance. Enter this code in a query window and execute it. This will create the empty databases that we will use for our release process.

  2. Verify that the databases exist. You should see the WWI_SSDT_QA and WWI_SSDT_Staging databases if you refresh the database folder.

    Both the WWI_SSDT_QA and WWI_SSDT_Staging databases display in the database list in SSMS

Task 2: Implement a Manual for Integration

We will implement a manual release for build output.

In this task, we will deploy the latest build that is available from VSTS to our local database server.

  1. Open your VSTS account, select WideWorldImporters-SSDT project. From the Build&Release menu click Builds. You should see the build that you created previously.

    On the Build tab, under Build Definitions, Mine is selected, and WideWorldImporters-SSDT-CI displays.

  2. Click to open the WideWorldImporters-SSDT-CI build.

    On the WideWorldImporters-SSDT-CI build page, under Summary, details display for WideWorldImporters-SSDT. Number of builds is 1, and Success rate is 100 percent.

  3. Click the latest build number that was completed to open it.

    Information for WideWorldImporters-SSDT-CI / Build 6  displays, including a green bar that says build succeeded. The Summary tab is selected, and Build details display. Details include Definition, Source, Source version, Requested by, Queue name, and dates/times the Build was queued, started, and finished. At the bottom, two build issues display.Linking release to build definition

    Note that you might have a series of warnings in the list of build issues. You can see that these warnings did not prevent the build from continuing. SQL Server has a mechanism called Deferred Name Resolution. This means that a stored procedure can refer to objects that do not as yet exist. In the example shown, the AddRoleMemberIfNonexistent procedure has unresolved references. In fact, in this case, it is because the views that are mentioned are present in the master database, not in the current database. This distinction is not important at this time for our lab at this time.

  4. Click on the Artifacts tab.

    Information for WideWorldImporters-SSDT-CI / Build 6  displays, including a green bar that says build succeeded. The Artifacts tab is selected, and the DatabasePackage folder displays with two button options: Download, or Explore.

  5. Click the Explore link, then in Artifacts Explorer, expand the Database_Package folder to see what the package contains.

    In Artifacts explorer, the Database_Package folder is expanded. Within the folder are the following files: WideWorldImporters_SSDT.dll; WideWorldImporters_SSDT.pdb; WideWorldImporters_SSDT.dacap

  6. Click the ellipsis to the right of the Database_Package folder, and from the drop-down list, click Download as zip.

    To the right of the Database_Package folder, the ellipsis is clicked, and from the drop-down menu, Download as zip is selected.

    Save the file to a known location. (You might want to create a folder specifically for downloaded packages). Click Close to close the Artifacts Explorer. Using Windows Explorer, extract the contents of the downloaded package to the folder.

  7. In SQL Server Management Studio, in Object Explorer, right-click the WWI_SSDT_Integration database, click Tasks, then click Upgrade Data-tier Application.

    In Object Explorer, WWI_SSDT_Integration database is selected. Its right-click menu displays with Tasks, and Upgrade Data-tier Application selected.

  8. In the Upgrade Data-tier Application window, in the Introduction page, note details of the process, then click Next.

    In the Upgrade Data-tier Application window, on the Introduction page, under Upgrade a data-tier application, details of the process display.

  9. On the Select Package page, click Browse and locate the .dacpac file that you extracted earlier, then click Next.

    The Select Package page displays. Under Select the DAC package to use for the upgrade, a file path is in the  field, DAC package file name with the .dacpac extension.

  10. On the Detect Change page, note that this database has not previously been registered as a data tier application (DAC). Click Next.

    The Detect Change page displays. Under Change detection results, a message displays that the database WWI_SSDT_Integration is not registered as a DAC.

  11. On the Options page, note that we are able to have a failed deployment rolled back. Check this option and click Next.

    The Options page displays. Under Please select from the following options, the check box for Rollback on failure is selected.

  12. On the Review Upgrade Plan page, review the plan shown, then click Next.

    The Review Upgrade Plan page displays. Under Review the upgrade plan, a table displays with Data Loss and Action columns.

  13. On the Summary page, review the summary, expand the Potential Data Loss node, then click Next.

    The Summary page displays. Under Summary, a message says the following settings will be used to upgrade your DAC, and a green check mark is next to Change detection. Under Potential Data Loss is the message the upgrade plan is not likely to cause data loss.

  14. The upgrade should then run to completion. If so, click Finish.

    The Upgrade DAC page displays. Under Upgrading the DAC, a table displays with Action and Result columns.

  15. In Object Explorer, right-click the WWI_SSDT_Integration database and click Refresh. Expand the WWI_SSDT_Integration database, expand the Tables folder, and note the tables that are now present. (This was previously an empty database that you created earlier).

    In Object Explorer, under WWI_SSDT_Integration, the Tables folder is expanded. In addition to System Tables, FileTables, and External Tables folders, other table files display.


In this lab you completed the following tasks:

  • Prepared your local environment
  • Implemented a Manual Release for Integration