Hands-On Lab

SQL Azure Data-tier Applications

Lab version:1.0.0

Last updated:10/2/2018

Contents

Overview

Exercise 1: Deploying Data-tier Applications to a SQL Azure Database

Task 1 – Creating SQL Server Database Project

Task 2 – Creating Database Schema

Task 3 – Publishing the Database to SQL Azure

Verification

Exercise 2: Upgrading Data-tier Applications in SQL Azure

Task 1 – Extracting a Data-Tier Application File from SQL Azure

Task 2 – Importing a DAC Package in SSDT

Task 3 – Updating the database schema in a Database Project

Task 4 – Deploying the Upgraded DAC Package

Verification

Summary

Appendix A: Creating a SQL Azure Server

Task 1 – Retrieving your SQL Azure Server Name

Overview

SQL Azure Database supports a variety of mechanisms for deploying, versioning and migrating databases. You can use the traditional Transact-SQL script based approach to create your database objects, then populate your data either using scripts, BCP or SQL Server Integration Services. Schema upgrade scripts tend to be either hand coded or generated using additional tools. This approach is well known by SQL Server developers, but can be tedious for developers who are new to SQL Azure, especially when dealing with the complexities associated with database schema versioning.

Microsoft is addressing this complexity with a new framework called the Data-tier Application Framework, or DAC Fx, which makes it much easier to deploy, migrate and version your database schemas for SQL Server and SQL Azure. DAC Fx shipped initially with SQL Server 2008 R2, and support was soon added for SQL Azure. Since then, DAC Fx has evolved significantly and is an important part of the developer and managability story for SQL Azure. It is being integrated into developer tools like the SQL Server Data Tools, management tools like SQL Server 2012 Management Studio, and as a service accessible from the Windows Azure Management Portal.

Objectives

In this hands-on lab, you will learn how to:

  • Create a new SQL Server project using SQL Server Data Tools (known as SSDT) to target SQL Azure.
  • Publish a Data-tier Application to SQL Azure using SQL Server 2012 Management Studio.
  • Extract a .dacpac file from a SQL Azure database, using SQL Server 2012 Management Studio.
  • Import a .dacpac file in SSDT and update the schema.
  • Upgrade a Data-tier Application in SQL Azure using the updated .dacpac file.

Prerequisites

You must have the following items to complete this lab:

  • Microsoft.NET Framework 4
  • SQL Server 2012 Management Studio
  • SQL Server Data Tools

Exercises

This hands-on Lab includes the following exercises:

  1. Deploying Data-tier Applications to a SQL Azure Database
  2. Upgrading Data-tier Applications in SQL Azure

Estimated time to complete this lab: 45 minutes.

Exercise 1: Deploying Data-tier Applications to a SQL Azure Database

In this exercise, you will learn how to create a new SQL Server Database project in SSDT, create a database definition schema and deploy it to SQL Azure.

Note: In order to complete this lab you will need a SQL Azure server to host the database. If you do not have one server created yet, use this appendix as a guide to do it: Appendix A: Creating a SQL Azure Server.Make sure you added the necessary Firewall Rules to allow you access to the SQL Azure Server using Management Studio. Also add the Microsoft Services rule (IP Address range “0.0.0.0” to “0.0.0.0”)

Additionally, if this is not the first time you run this exercise, you will need to run Reset.cmd script within the Source folder of this lab in order to clean up the SQL Azure database status. The script will prompt you for several pieces of information including a database name, which is “BooksInventory”.

Task 1– Creating SQL Server Database Project

In this task you will create a SQL Server Database Project andset its target platform to SQL Azure.

  1. Open SSDT. To do this, in Start | All Programs, click the Microsoft SQL Server Data Tools shortcut.
  2. From the File menu, choose New and then Project.
  3. In the New Project dialog, select SQL Server in the Installed Templates list. Choose the SQL Server Database Project template, set the Name of the project to BooksInventory, set the location to \Source\Ex01-DeployingDACApp, change the solution name to Begin, and ensure that Create directory for solution is checked. Click OK to create the project.

Note: Make sure your project name is BooksInventory, as this is required in this lab to perform an upgrade to the database.

Figure 1

Create SQL Server Database Project

  1. You will update the project’sTarget Platform and the Output Type. To do this, in Solution Explorer,right-click the BooksInventory project node, select Properties.
  2. In the Project Settings tab, change the Target Platform to SQL Azure and make sure that the option Create additional down-level .dacpac file (v2.0)within Output Types is checked.

Figure 2

Configuring SQL Server Database Project

  1. Press Ctrl + Sto apply changes.

Note: You can set the target platform for your database project to any release of SQL Server 2005 or above or SQL Azure.

By doing so, you can centralize your database development in one project, but publish it to multiple SQL Server platforms as the need arises.

SSDT also makes this task simple by being aware of your target platform and automatically detecting any error in your code (e.g., when you are using unsupported features for a project that is going to be published to SQL Azure).

For more information about this refer to MSDN.

Task 2 – Creating Database Schema

In this task you will create the database schemathat will be deployed to SQL Azure at the end of this exercise.

  1. Add a new table to the Database project. To do this, right click in the BooksInventory project node, select Add and click Table.

Figure 3

Add Table

  1. In the Add New Item dialog, make sure Tabletemplate is selected and set the name to Authors. Click Add to create the table script.

Figure 4

Create Table

  1. In the BooksInventory node, within the Solution Explorer, double-click the Authors table we created in the previous step.

Figure 5

Open Table Designer

  1. Inthe Design tabwithin the Table Designer, add the following fields to the Authors table:

Name / Data Type / Allow Nulls? / Is Primary Key?
Id / Int / No / Yes
FirstName / nvarchar(50) / No / No
LastName / nvarchar(50) / No / No
  1. Press Ctrl + S to apply changes.
  2. Create a new table. To do this, repeat the first step of this task.This time, set the table name to Books and add the following fields:

Name / Data Type / Allow Nulls? / Is Primary Key?
Id / int / No / Yes
Title / nvarchar(50) / No / No
ISBN / nvarchar(13) / Yes / No
Price / float / Yes / No
AuthorId / int / No / No
  1. Add a Foreign Key tothe Books table. To do this, in the Table Designer, right-click the Foreign Keys node at the right pane and select Add New Foreign Key. Set its name to FK_Books_ToAuthors.

Figure 6

Adding the Authors Foreign Key

  1. To associate your Foreign Key to the Primary Key located in the Authors table, switch to the T-SQL tab and replace the CONSTRAINT line with the following highlighted code.

T-SQL

CREATETABLE [dbo].[Books]

(

[Id]INT NOT NULL PRIMARYKEY,

[Title]VARCHAR(50) NOT NULL,

[ISBN]VARCHAR(13) NULL,

[Price]FLOAT NULL,

[AuthorId]INTNOT NULL,

CONSTRAINT[FK_Books_ToAuthors]FOREIGNKEY ([AuthorId]) REFERENCES[Authors]([Id])

)

  1. Press Ctrl + S to apply changes.
  2. Right-click the BooksInventory project node in the Solution Explorer and select Build.
  3. After the build is completed, right-click again the BooksInventory project node in the Solution Explorer and select Open Folder in Windows Explorer. Browse to the Bin folder and then to Debug. Inside the folder, you will see 2dacpac files that were generated in the previous step (one with the 2.5 schema version and the other one with the 2.0 version which is compatible with SQL Azure).

Figure 7

Generated dacpac files in Bin folder

Task 3 – Publishing the Database to SQL Azure

In this task, you will use SQL Server Management Studio (SSMS) to deploythedatabase project to SQL Azure.

  1. Open SSMS. To do this, in Start | All Programs | Microsoft SQL Server 2012 RC0, click the SQL Server Management Studio shortcut.
  2. In the Connect to Server dialog, enter your SQL Azure server name and your credentials. Click Connect.

Figure 8

Connecting to SQL Azure Server

  1. In the Object Explorer panel, expand the Management node. Right click theData-tie Applications node and select Deploy Data-tier Application.

Figure 9

Deploying a Data-tier Application

  1. In theDeploy Data-tier Application dialog, click Next. Click Browse and browse to the folder where the dacpac were generated. Select the dacpac file with the 2.0 suffix at the end. The DAC Details fields will show the values of the dacpac metadata. Click Next.

Figure 10

Selecting a DAC package

  1. Leave the default value, BooksInventory, in the name of the deployed DAC and database. Click Next. In the summary page, click Next. The deployment process will start.
  2. Once the deployment process is completed, click Finish.

Figure 11

DAC deployment process completed

Verification

In this task you will verify that the DAC package was successfully published to SQL Azure. To do this, you will explore the SQL Azure databases using the SQL ServerObject Explorerthat is included with SSDT.

  1. Go back to SSDT. If not opened, in Start | All Programs, click the Microsoft SQL Server Data Tools shortcut
  2. In the View menu, select SQLServer Object Explorer.
  3. In the SQL Server Object Explorer pane, right-click theSQL Servernode and selectAdd SQL Server. It will prompt a Connect to Server dialog. Fill it with your Server Name and Credentials and click Connect.

Figure 12

Connecting to SQL Azure Server

  1. Locate your SQL Azure Server and expand Databases folder. Locate the BooksInventory database we created in the previous task.
  2. Expand Tables folder within BooksInventory database and verify that both Authors and Books tables were created.

Figure 13

BooksInventory Verification

  1. To see the DAC metadata registration, expand the Management node and the Data-tier Applications.

Figure 14

Visualizing registered DAC Packages

  1. Right-click the BooksInventory DAC and select Properties. You can view the DAC metadata registered in SQL Azure.

Figure 15

Visualizing DAC metadata

Exercise 2: Upgrading Data-tier Applications in SQL Azure

In this exercise, you will extract a Data-Tier Application Component (DAC) from your SQL Azure Database, apply some changes in SSDTand publish the DAC back to the SQL Azure database.

Note: This exercise requires the databasethat is created in the Exercise 1.

Task 1 – Extracting a Data-Tier Application File from SQL Azure

In this task, you will extract a Data-Tier Application file from a SQL Azure database using SQL Server Management Studio. The extracted dacpac file will be used in SSDT to perform updates to your database.

  1. Open SSMS. To do this, in Start | All Programs | Microsoft SQL Server 2012 RC0, click the SQL Server Management Studio shortcut.
  2. In the Connect to Server dialog, enter your SQL Azure server name and your credentials. Click Connect.

Figure 16

Connecting to SQL Azure Server

  1. In the Object Explorer panel, expand the Databases node of your SQL Azure server. Right-click the BooksInventory, select Tasks, Extract Data-tier Application.

Figure 17

Extracting a Data-tier Application

  1. In the Extract Data-tier Application dialog, click Next.
  2. Leave the DAC properties fields as they are and select a destination path for the extracted dacpac file clicking the Browse button. Click Next.
  3. Wait until the Building the DAC package process is finished. Click Finish.

Figure 18

Creating and saving the DAC package file

Task 2 – Importing a DAC Package in SSDT

In this Task, you will import the previously extracted DAC package to SSDT and change the database schema and DAC metadata information.

  1. Open SSDT. To do this, in Start | All Programs, click the Microsoft SQL Server Data Tools shortcut.
  2. From the File menu, choose New and then Project.
  3. In the New Project dialog, select SQL Server in the Installed Templates list. Choose the SQL Server Database Project template, set the Name of the project to BooksInventory, set the location to \Source\Ex02-UpdatingDACApp, change the solution name to Begin, and ensure that Create directory for solution is checked. Click OK to create the project.

Note: Make sure your project name is BooksInventory, as this is required in this lab to perform an upgrade to the database.

Figure 19

Create SQL Server Database Project

  1. Right-click the BooksInventory project node, select Import and Data-tier Application (*.dacpac).

Figure 20

Importing a Data-tier Application file

  1. In the Import Data-tier Application File dialog, click Browse to select the previously extracted DAC package file. Make sure that Import Permissions and Import database settings optionsare checked, and the Folder structure is set to None. Click Start.

Figure 21

Selecting the DAC package file and settings

  1. Click Finish when the import process is completed. You will end up with a solution structure like the following:

Figure 22

Imported Objects in Solution Explorer

  1. Your project now contains two new files, which are the tables you created in the Exercise 1 of this lab. Before changing the database schema, you will update the project’s Target Platform and the Output Type. To do this, in Solution Explorer,right-click the BooksInventory project node, select Properties.
  2. In the Project Settings tab, change the Target Platform to SQL Azure and make sure that the option Create additional down-level .dacpac file (v2.0) within Output Types is checked.

Figure 23

Configuring SQL Server Database Project

  1. Click the DAC Properties button.
  2. In the Data-tier Application Properties dialog, leave the Name field as BooksInventory, change the Version to 1.1.0.0 and set the Description field to Upgraded Database. Click OK.

Figure 24

Setting the DAC package properties

  1. Press Ctrl + Sto apply changes.

Task 3 – Updating the database schema in a Database Project

In this task, you will you will apply some changes to the database schema.

  1. Add a new table. To do this, right click in the BooksInventory project node, select Add and click Table.

Figure 25

Add Table

  1. In the Add New Item dialog, make sure the Table template is selected and set the name to Genders. Click Add to create the table script.

Figure 26

Creating the Genders Table

  1. In the Solution Explorer, double-click the Genders table script we created in the previous step.
  2. In the Design tab within the Table Designer, add the following fields to the Genders table:

Name / Data Type / Allow Nulls? / Is Primary Key?
Id / Int / No / Yes
Description / nvarchar(50) / No / No
  1. Select the <unnamed> key from the right panel and press F4. The Properties panel will appear.
  2. Set the Is Clustered property value to False.
  3. Press Ctrl + S to apply changes.
  4. Press CTRL+SHIFT+B to build the Solution. The Error List pane will appear showing a warning related to the new Genders table. This occurs because SSDT is validating the table schema against the target platform and detects statements which are incompatible in SQL Azure. This is useful to detect incompatibility issues between different platforms. In this case, the Genders table uses a nonclustered index, which is not supported by SQL Azure.

Figure 27

Statement Options not supported in SQL Azure

  1. Now, you will fix these statements. Double-click the Genders table script in the Solution Explorer and remove the NONCLUSTEREDstatement. Press CTRL+S to save the file.
  2. Also, open the Authors and Books tables and remove the error marked statements. These are ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON and PAD_INDEX = OFF.
  3. In the Solution Explorer, double-click the Authors table script.
  4. In the Table Designer, add a new field named GenderId as int.
  5. Add a Foreign Key to the Genders table. To do this, in the Table Designer, right-click the Foreign Keys node at the right pane and select Add New Foreign Key. Set its name to FK_Authors_Genders.

Figure 28

Adding the Genders Foreign Key

  1. To associate your Foreign Key to the Primary Key located in the Genders table, switch to the T-SQL tab and replace the CONSTRAINT line with name [FK_Authors_Genders]with the following highlighted code.

T-SQL