Skip to content

Azure SQL Execute Query

Jump to bottom
Adrien Siffermann edited this page Aug 22, 2016 · 10 revisions

Icon

With this task, you can execute a SQL query on an Azure SQL Database, using an Azure Resource Manager Service Endpoint. The query will be executed with SQLCMD, and allow uses of variables.

Requirements

You should have Setup an Azure Resource Manager Endpoint to use this task.

Your Build Agent should have the SQLPS capability, so you have to Install SQLPS on your Build Agent.

Add the task to your Build or Release Definition

Add-Tasks

You will find the task in the "Deploy" category.

Configuration

Configure-ExecuteSql

General

  • Azure RM Subscription - Specifies the Azure Resource Manager Service Endpoint to use.

  • Type - Type of the SQL script to execute on target: File Path, Inline Script or Predefined Script.

  • Script Path - Path of the script to execute. Should be fully qualified path or relative to the default working directory.

  • Inline Script - An in-place editable script.

  • Predefined Script - This task come with several predefined scripts of common tasks on SQL Azure.

    • Create User - Create an user on the target database. Must match an existing login. You must provide the Login variable using the Variables input (i.e. Login=MyUser or Login=$(MyUserBuildVariable)) Configure-ExecuteSql-CreateUser

      • Add Role Member - Add a role member on the target database (with sp_addrolemember). You must provide the Login and Role variables using the Variables input (i.e. Login=$(MyUserBuildVariable) and Role=$(MyRoleBuildVariable)) Configure-ExecuteSql-AddRoleMember
      • Create Database Owner - Combine the two previous script: create an user and add it to the db_owner role on the target database. You must provide the Login variable using the Variables input (i.e. Login=$(MyUserBuildVariable)) Configure-ExecuteSql-CreateDatabaseOwner
      • Feel free to contribute to add your generic predefined scripts!

  • Variables - Variables passed to the SQLCMD script with the -v option. Should be formatted like Var1=Value1, with one variable per line. It can then be used with the SQLCMD $(Var1) syntax in the script.

  • Predefined SQLCMD variables:

    • WorkingDirectory: The current path of the script. Usefull with the SQLCMD :r syntax to link other SQL files. See CreateDbOwner.sql as an example.

Target

  • Azure SQL Server Name - Azure SQL Server name like, FabrikamSQL.database.windows.net,1433 or FabrikamSQL.database.windows.net.
  • Database Name - Name of the Azure SQL Database.
  • Login - The Azure SQL Server login, with the permissions required for your script.
  • Password - Password for the Azure SQL Server login provided. It can accept variable defined in Build / Release Definitions as $(PasswordVariable). You may mark variable type as "secret" to secure it.

Firewall

  • Specify Firewall Rules Using - For the script to execute, the IP Address of the automation agent has to be added to the 'Allowed IP Addresses' in the Azure SQL Server's Firewall. Provide the IP Address range of the automation agents, or select auto-detect (it will find the external IP of your agent).
  • Start IP Address - If you choose 'Allowed IP Addresses', the starting IP Address of the automation agent machine pool like 196.21.30.50.
  • End IP Address - If you choose 'Allowed IP Addresses', the ending IP Address of the automation agent machine pool like 196.21.30.65.
  • Delete Rule After Task Ends - If selected, then after the task ends, the IP Addresses specified here are deleted from the 'Allowed IP Addresses' list of the Azure SQL Server's Firewall.
Clone this wiki locally