One of the concepts that has recently been growing rapidly in popularity, in the context of cloud technologies, is DevOps. Derived from the combination of two terms – Development and Operations, it represents a new approach to building synergy between these two seemingly conflicting types of activities. The source of this conflict is the difference between the nature of development, the primary purpose of which is constant change, and the goals of operations teams, which strive to provide the maximum level of stability and, as a result, resist changes. To facilitate these competing objectives, DevOps applies a number of development principles to operations to minimize the potential negative impact of changes, integrate two sets of activities, and bring mutual understanding between developers and operations staff. These principles include Version Control, Source Code repositories, Infrastructure as Code, Continuous Integration, Automated Testing, Continuous Delivery, Continuous Deployment, and Application Performance Monitoring and Management. In this article, we will focus on leveraging these principles in regard to Azure SQL Database deployments. To implement some of the more common DevOps practices, we will rely on Visual Studio Team Services.
Visual Studio Team Services (VSTS) is an online service that builds and deploys software across a variety of platforms, including Microsoft Azure. It integrates with Team Foundation Version Control and Git, allowing you to choose between centralized and distributed version control repositories. VSTS also supports Continuous Integration and Continuous Deployment. The first of these features facilitates automatic creation of a new build whenever a new version of code is checked into the repository. The second one provides the ability to automatically deploy a new build as soon as it becomes available. You have the option of implementing both when working with Azure SQL Database.
To start, you need to create a Visual Studio Team Services account by following the instructions available in the online documentation of Visual Studio Team Services. This will involve providing a unique name (in the visualstudio.com namespace) designating your VSTS account in an Azure region and choosing the version control repository (Git or Team Foundation Version Control) that you intend to use to store your code. When opting for Git, you should make sure that you have an up-to-date version of the Git client (you can download it from the Git website).
With a new account in place, you are ready to create your first project. When creating the project, you will be prompted to select the version control and the work item process (Agile, Capability Maturity Model Integration, or Scrum). To start working on the project, you can clone it to the Git repository or Visual Studio on your computer from the VSTS interface. If you have an existing project, then depending on its current location, you can push it from a Git repository or add it to source control from Visual Studio.
Let’s assume that your intention is to use Visual Studio to initiate a new project that will rely on VSTS for source control with Git as the code repository. Once you click Clone in Visual Studio link on the new project page in the VSTS window, you will be automatically redirected to the Visual Studio interface. From there, you will have the option of cloning the repository and starting the development process. Once you click the Clone command button, click New in the Solutions section of the Team Explorer window of Visual Studio. This will display the New Project dialog box. From there, click SQL Server in the list of installed templates, select the SQL Server Database Project, specify a meaningful project name, ensure that the Add to source control checkbox is selected, and click OK. Configure the database project in Visual Studio according to your requirements and ensure that you set the Target platform property of the project to Microsoft Azure SQL Database V12.
To commit the project to your repository, click Changes in the Team Explorer window, enter a commit message describing the changes you made and click Commit. Next, click the Sync link and then, on the Synchronization page within the Team Explorer window, click Push to push changes to VSTS.
Once you switch back to the VSTS interface and refresh the project page, you will be able to configure continuous integration. To accomplish this, click the Set up Build command button. In the Create new build definition window, click Visual Studio in the list of templates. Next, accept the default option of the Repository source, enable the Continuous integration (build whenever this branch is updated) checkbox, and click Create. On the Build page, you will be presented with the default sequence of build steps, including:
- NuGet Installer
- Visual Studio Build
- Visual Studio Test
- Index Sources & Publish Symbols
- Copy Files
- Publish Build Artifacts
This is geared towards Visual Studio-based builds. Save the default settings and assign a meaningful name to the new build definition. Now you can queue and test the build by clicking Queue new build on the Build definitions page, with the new build definition selected. This will automatically display the Explorer tab of the Build & Release tab, showing the progress of the build process. To determine the outcome, once the build completes, click Artifacts and then click Explore to the right of the drop folder. Here you will find the DACPAC file generated by the build. This file will be used to update a target Azure SQL database during code releases once you configure continuous deployment.
Before you configure continuous deployment, you will need to create that Azure SQL database by following the instructions provided in Microsoft Azure online documentation. Once completed, switch back to the VSTS interface and, on the Build & Release page, click the Releases tab and then click + New definition. In the Create release definition window, click Empty template and click Next. On the Artifacts page, make sure that the selections point to the continuous integration build definition that results in creation of the DACPAC file. Make sure to enable the Continuous deployment (create release and deploy whenever a build completes checkbox and click Create. This will result in the creation of a new release definition and display its Environments tab.
On the Environments tab, click Add tasks, in the Task catalog window, click Add next to Azure SQL Database Deployment, and click Close. In the Execute Azure SQL: DacpacTask section, configure the following settings:
- Azure Connection Type: Azure Resource Manager
- Azure RM Subscription: the subscription where you created the Azure SQL database. Once you type in this value, click Authorize and when prompted, sign in to your subscription.
- Azure SQL Server Name: the name of Azure SQL server you created (including the .database.windows.net suffix)
- Database Name: the name of Azure SQL database you created
- Server Admin Login: the name of the administrative account you used when creating Azure SQL server
- Password: the password of the administrative account
- Type: SQL DACPAC File
- DACPAC File: the full path to the .DACPAC file included in the build
- Publish Profile: the full path to the XML file containing the publish profile
- Additional SqlPackage.exe Arguments: additional arguments of SqlPackage.exe that will be applied when deploying the Azure SQL database
- Specify Firewall Rules Using: IPAddressRange
- Start IP Address: the starting IP Address of the range associated with the location where the automation agent is running
- End IP Address: the ending IP Address of the range associated with the location where the automation agent is running
- Delete Rule After Task Ends: enabled
- Control options
- Enabled: enabled
- Continue on error: leave cleared
- Always run: leave cleared
- Timeout: 0
Assign a meaningful name to the release definition and click Save. Finally, click +Release, followed by Create Release to initiate the process of applying the artifacts produced by the build to the target Azure SQL database. In the Create new release window, click Create again. Finally, click the link indicating that the release has been created to verify that the outcome was successful.
This concludes our article describing the steps to integrate DACPAC deployments of Azure SQL Database with Visual Studio Team Services. In our upcoming articles, we will explore a similar process applicable to script-based deployments.
