left-icon

SQL on Azure Succinctly®
by Parikshit Savjani

download-button

Previous
Chapter

of

Next
Chapter

A
A
A

CHAPTER 7

Provisioning, Managing & Migrating to Azure SQL Database

Provisioning, Managing & Migrating to Azure SQL Database

One of the best part of Azure SQL Database is that Microsoft has ensured to integrate it with all the familiar tools like Azure Portal, PowerShell, and SQL Server Management Studio. Hence the learning or ramp-up curve for provisioning, managing and migrating to Azure SQL Database is not that steep and very natural.

Provisioning an Azure SQL Database

There are two ways to provision an Azure SQL Database in an Azure Subscription namely

  • Using Azure Management Portal
  • Using Azure PowerShell

Azure Management Portal can be used as a GUI interface for provisioning of database while Azure PowerShell is useful as programmatic interface for automation.

To provision an Azure SQL Database in Azure, you must first obtain an Azure subscription. You can purchase an Azure subscription or sign up for Azure free trial subscription.

Using Azure Management Portal

After you obtain your Azure Subscription, you can follow the steps mentioned in this section to provision an Azure SQL Database using Azure Management Portal

  1. Login to Azure Management Portal and Click on New -> Data + Storage -> SQL Database as shown below

Provisioning an Azure SQL Database

Figure 13: Provisioning an Azure SQL Database

  1. The next step is to provide the name of the database and the logical SQL Server name where you would like to host the database. The database name should be unique within a given server and is checked by the portal as you are typing. You can use an existing Server name if you have already created it earlier or create a new server as shown in following figure.

You can think of logical Server as a SQL Instance provisioned in Azure which will be hosting your Azure SQL Database. While creating a Server, you need to provide a unique server name which will have DNS suffix of database.windows.net , login name and password which will be a sysadmin on that server and Azure Location where you would like to provision this database to be hosted as shown in the following figure

Creating a New Server for Azure SQL Database

Figure 14: Creating a New Server for Azure SQL Database

  1. The next step is to select the source for the database. You can create a blank database and create schema/objects or select sample database (yes our best friend Adventureworks is available as sample database in azure sql database as well) or restore a database from a backup.

Selecting Source for Azure SQL Database

Figure 15: Selecting Source for Azure SQL Database

  1. Next step is to select Service tier for the database which we discussed in previous chapter. The Service Tier also decides the per minute pricing of the database and hence it is referred to as Pricing Tier in the portal.
  2. Next step is to select desired Collation for the database and Resource Group to which database belong. Resource Group is new concept in Azure to group and manage the lifecycle and dependencies of applications running on Azure. If the Azure SQL Database is created as a backend for running an Azure service application, you can categorize all the resources belonging to that application in a single Resource Group which can be managed and monitored as a single unit. For sample database, you can choose the Default-SQL-<Location> Resource Group available in the list.
  3. Lastly, select the Azure subscription under which the database will be created and billed. This is useful when you manage multiple Azure subscription.
  4. Finally click Create at the bottom of the blade for the Azure SQL Database to be provisioned in the specified logical server.

Using Azure PowerShell

Using Azure PowerShell, you can provision an Azure SQL Database using the following PowerShell code which is simple and self-explanatory.

Code Listing 21: Provisioning a Azure SQL Database

Import-Module Azure

Add-AzureAccount

#Select the desired Azure Subscription

[array] $AllSubs = Get-AzureSubscription

If ($AllSubs)

{

        Write-Host "`tSuccess"

}

Else

{

        Write-Host "`tNo subscriptions found. Exiting." -ForegroundColor Red

        Exit

}

#Write-Host "`n[Select Option] - Select a Subscription to Work With" -ForegroundColor Yellow

$count = 1

ForEach ($Sub in $AllSubs)

{

   $SubName = $Sub.SubscriptionName

   Write-host "`n$count - $SubName"  -ForegroundColor Green

   $count = $count+1

}

$SubscriptionNumber = Read-Host "`n[SELECTION] - Select a Subscription to Provision the VM"

If($SubscriptionNumber -gt $count)

{

    Write-Host "`Invalid Subscription Entry - Existing" -ForegroundColor Red

    Exit

}

$SelectedSub = Get-AzureSubscription -SubscriptionName $AllSubs[$SubscriptionNumber - 1].SubscriptionName 3>$null

$SubName = $SelectedSub.SubscriptionName

Write-Host "`n Selected Subscription - $SubName" -ForegroundColor Green

$SelectedSub | Select-AzureSubscription | out-Null

$Location = "South Central US"

$DatabaseName = "myazuresqldb"

$server = New-AzureSqlDatabaseServer  -Location $location –AdministratorLogin "mysqlsa" -AdministratorLoginPassword "Pa$$w0rd" -Version "12.0"

New-AzureSqlDatabase  -ServerName $server.ServerName -DatabaseName $DatabaseName -Edition "Basic" -MaxSizeGB 2

Managing Azure SQL Database

As mentioned earlier one of the best parts of Azure SQL Database is Microsoft has ensured to maintain the same tools namely SQL Server Management Studio, TSQL and PowerShell for managing Azure SQL Database which makes it easier to manage it due to familiarity of tools.

In order to be able to manage and connect to Azure SQL Database, you need to first create firewall rules to allow connections to your servers and databases from outside. You can define server-level and database-level firewall settings for the master or a user database in your Azure SQL Database server to selectively allow access to the database.

Configure Server-Level Firewall Rules

Server-level firewall rules can be created and managed through the Microsoft Azure Management Portal, Transact-SQL, and Azure PowerShell.

The following are the steps to create server-level firewall rules using Azure Management Portal

  1. Login to Azure Management Portal with admin privileges, click on Browse All on the left banner and click SQL Servers
  2. In the server blade, click Settings at the top of the blade, and then click Firewall to open the Firewall Settings blade for the server.
  3. Add or change a firewall rule.
  • To add the IP address of the current computer, click Add Client IP at the top of the blade.
  • To add additional IP addresses, type in the RULE NAME, START IP address, and END IP address.
  • To modify an existing rule, click and change any of the fields in the rule.
  • To delete an existing rule, click the rule, click the ellipsis (…) at the end of the row, and then click Delete.
  1. Click Save at the top of the Firewall Settings blade to save the changes.

Configuring Server Level Firewall using Azure Portal

Figure 16: Configuring Server Level Firewall using Azure Portal

You can also configure Server-level firewall using Azure PowerShell using the following Cmdlets

Code Listing 22: Configuring Server Level Firewall using Azure PowerShell

Import-Module Azure

Add-AzureAccount

## To Add a New Server-Level Firewall Rule

New-AzureSqlDatabaseServerFirewallRule –StartIPAddress 172.16.1.1 –EndIPAddress 172.16.1.10 –RuleName DBAFirewallRule –ServerName myazuresqldbserver

## To Modify an existing Server-Level Firewall Rule

Set-AzureSqlDatabaseServerFirewallRule –StartIPAddress 172.16.1.4 –EndIPAddress 172.16.1.10 –RuleName DBAFirewallRule –ServerName myazuresqldbserver

# To delete an existing Server-Level Firewall Rule

Remove-AzureSqlDatabaseServerFirewallRule –RuleName DBAFirewallRule –ServerName myazuresqldbserver

In order to connect to Azure SQL Database Server using SQL Server Management Studio (SSMS) from a client workstation, you would first need to create the server-level firewall rule using one of the above technique to allow the Client workstation IP address as a legitimate entry to SQL Server. However after you establish the connection to SQL Server using SSMS, you can create additional firewall rule using T-SQL queries fired against the server using management studio.

To create or update server-level firewall rules, execute the sp_set_firewall rule stored procedure while to delete the firewall rules, execute the sp_delete_firewall_rule stored procedure as shown

Code Listing 23: Configuring Server Level Firewall using Transact SQL

--Create New Firewall rule

EXEC sp_set_firewall_rule @name = N'DBAFirewallRule', @start_ip_address = '172.16.1.1', @end_ip_address = '172.16.1.10'

-- Update an existing Firewall rule

EXEC sp_set_firewall_rule @name = N'DBAFirewallRule', @start_ip_address = '172.16.1.4', @end_ip_address = '172.16.1.10'

--Delete an existing Firewall rule

EXEC sp_delete_firewall_rule @name = N'DBAFirewallRule'

Configure Database-Level Firewall Rules

After creating server-level database rules, you will have to create Database-Level Firewall for the clients to be able to connect to the specific database on that server. You can configure Database-Level Firewall using T-SQL.

In order to create Database-Level Firewall, you need to connect to the Server using SQL Server Management Studio from the client workstation which is opened for connection in server-level firewall and execute the sp_set_database_firewall_rule stored procedure to create or update database-level firewall rule as shown in the following code snippet

Code Listing 24: Configuring Database Level Firewall using Transact SQL

--Create New Database Level Firewall rule

EXEC sp_set_database_firewall_rule @name = N'ApplicationFirewallRule', @start_ip_address = '172.16.1.11', @end_ip_address = '172.16.1.11'

-- Update an existing Database Level Firewall rule

EXEC sp_set_database_firewall_rule @name = N'ApplicationFirewallRule', @start_ip_address = '172.16.1.11', @end_ip_address = '172.16.1.12'

--Delete an existing Firewall rule

EXEC sp_delete_database_firewall_rule @name = N'ApplicationFirewallRule'

Connecting to Azure SQL Database using SSMS

As discussed earlier, in order to connect to the Azure SQL Database server, the first step is to ensure the Client Workstation from where you intent to connect is added in the server-level firewall. Once the IP address is allowed to connect to the Server, you can follow the steps mentioned in this section to connect to Azure SQL Database Server using SSMS for managing server instance. SQL Server 2014 SSMS with the latest updates offers expanded support for tasks like creating and modifying Azure SQL databases. In addition, you can also use Transact-SQL statements to accomplish these tasks. The steps below provide examples of these statements. For more information about using Transact-SQL with SQL Database, including details about which commands are supported, see Transact-SQL Reference (SQL Database).

If you do not know the name of the server for a given Azure SQL Database, you can identify the server from Azure Portal as described here

  1. Login to Azure Portal, Click on Browse All on the left hand banner and click on SQL Databases.
  2. Click on the Azure SQL Database you wish to connect and you will see the server name in database blade

Server name in Azure SQL Database

Figure 17: Server name in Azure SQL Database

  1. The Server name is unique and appended with DNS suffix database.windows.net
  2. On the taskbar, click Start, point to All Programs, point to Microsoft SQL Server 2014, and then click SQL Server Management Studio.
  3. In Connect to Server, specify the fully-qualified server name as serverName.database.windows.net noted earlier
  4. Select SQL Server Authentication since Windows Authentication is not supported in Azure SQL Database yet.
  5. In the Login box, enter the SQL Server administrator login that you specified in the portal when you created your server.
  6. In the Password box, enter the password that you specified in the portal when you created your server.
  7. Click Connect to establish the connection.

Managing Azure SQL Database using T-SQL

Once connected to Azure SQL Database Server using SSMS, you can use T-SQL to create and manage database objects as shown in following code snippet. One important consideration while working with Azure SQL Database is the USE statement is not supported for switching between databases. Instead, you need to establish or re-establish a connection directly to the target database.

Code Listing 25: Managing Azure SQL Database using T-SQL

-- Create New Database

CREATE DATABASE myTestDB

(

 EDITION='Standard',

 SERVICE_OBJECTIVE='S0'

);

-- ALTER DATABASE

ALTER DATABASE myTestDB

MODIFY

(

SERVICE_OBJECTIVE='S1'

);

-- DROP DATABASE

DROP DATABASE myTestDB;

-- Create New Server Login

CREATE LOGIN User1 WITH password='Password1';

-- Create Database User (Switch to User Database)

CREATE USER dbuser1 FROM LOGIN User1;

--Adding User to db_datareader role (Switch to User Database)

exec sp_addrolemember 'db_datareader', 'dbuser1';   

-- DROP Login (Switch to master Database)

DROP LOGIN User1

-- Query Catalog Views (on Master database)

SELECT * FROM sys.databases

SELECT * FROM sys.sql_logins

-- Query Dynamic Management Views (On User database)

SELECT text,* from sys.dm_exec_requests

cross apply sys.dm_exec_sql_text(sql_handle)

SELECT * from sys.dm_exec_connections

SELECT * from sys.dm_exec_sessions

Migrating to Azure SQL Database

In order to migrate an existing on premise SQL database to Azure SQL Database, you need to first ensure the TSQL code and objects within the database are compatible with Azure SQL Database. As mentioned in previous chapter, there are still T-SQL Features which are either partially supported or not supported in Azure SQL Database all of which are documented in the following MSDN article

https://azure.microsoft.com/en-us/documentation/articles/sql-database-transact-sql-information/

If a database schema is compatible with Azure SQL Database, the migration to Azure SQL Database can be done either in a single step using SSMS by deploying the database to Azure SQL Database or as a two-step process by first exporting a BACPAC of the database and then importing that BACPAC into an Azure SQL Server as a new database. Let us understand each of the migration methods below

Note: A data-tier application (DAC) introduced in SQL 2008 R2 is an entity that contains all of the database and instance objects used by an application. A DAC provides a single unit for authoring, deploying, and managing the data-tier objects instead of having to manage them separately. A DAC allows tighter integration of data-tier development with the development of the associated application code. It also gives administrators an application level view of resource usage in their systems. A BACPAC file is a DAC package file along with the data in the database and can be considered as a backup file for the database which can be used to recreate the database objects and data by importing the BACPAC file.

Using SSMS to Deploy to Azure SQL Database

With latest update in SQL 2014, SQL Server Management Studio (SSMS) comes with deploy database to Azure SQL Database wizard which allows you to directly deploy an on premise version of SQL Database to Azure SQL Database provided its schema is compatible. Following are the steps to use Deployment wizard in SSMS to migrate the database

  1. Using SSMS 2014 SP1, connect to the on premise database to be migrated, right click on the database in the object explorer and click task to see the “Deploy Database to Microsoft Azure SQL Database” option as shown in the figure

Deploy Database to Microsoft Azure SQL Database Wizard

Figure 18: Deploy Database to Microsoft Azure SQL Database Wizard

  1. Click Next and Connect to Azure SQL Database Server using SQL Authentication by providing the admin username and password. Select the database edition, service tier, max size as shown on the following figure.

Deploy Database to Microsoft Azure SQL Database

Figure 19: Deploy Database to Microsoft Azure SQL Database

  1. Click next and Click Finish to let wizard extract the database schema and exporting data to a bacpac file which is then importing to Azure SQL Database.

Depending on the size and complexity of the database, deployment may take from a few minutes to many hours. If there are incompatibilities, the schema validation routines will quickly detect errors before any deployment to Azure actually occurs. This method is useful when you want to deploy the entire database in one go when the entire database schema is compatible to Azure SQL Database. If however only subset of the database schema is compatible to Azure SQL Database and you would like to selectively extract database objects to Azure SQL Database you can manually export the bacpac and copy it to Azure Blob storage and restore it on Azure SQL Database Server as discussed in the next section.

Using SSMS to export a BACPAC and then importing it to SQL Database

This method can be broken into two steps 1) Export the database objects to bacpac file on local storage or Azure blob storage 2) Import the bacpac file to Azure SQL Database Server

This method involves following steps

  1. Connect to the source database in SSMS and export to bacpac file as shown in the following figure

Exporting a SQL Database to Bacpac

Figure 20: Exporting a SQL Database to Bacpac

  1. In the export wizard, configure the export to save the BACPAC file to either a local disk location or to Azure blob storage. Click the Advanced tab if you want to exclude data from some or all of the tables as shown in the following figure

Export to Bacpac by filtering the database objects

Figure 21: Export to Bacpac by filtering the database objects

  1. Once the BACPAC has been created, connect to Azure SQL Database Server using SSMS and right click on the Databases folder to import the bacpac file.

Import the Bacpac file in Azure SQL Database Server

Figure 22: Import the Bacpac file in Azure SQL Database Server

  1. While importing you need to provide the edition, service tier and max size of the database you want to create and complete the wizard to finish importing the bacpac file to Azure SQL Database Server.

This method gives you finer control on the objects you want to migrate to Azure SQL Database and hence is useful when you only want to migrate partial database to Azure SQL Database.


Scroll To Top
Disclaimer
DISCLAIMER: Web reader is currently in beta. Please report any issues through our support system. PDF and Kindle format files are also available for download.

Previous

Next

A
A
A

of



You are one step away from downloading ebooks from the Succinctly® series premier collection!
A confirmation has been sent to your email address. Please check and confirm your email subscription to complete the download.