Azure SQL Database Architecture

Azure SQL Database is a highly scalable multi-tenant and a highly available Platform-as-a-Service (PaaS) or DBaaS offering from Microsoft.

Microsoft takes care of the operating system (OS), storage, networking, virtualization, servers, installation, upgrades, infrastructure management, and maintenance.

Azure SQL Database allows users to focus only on managing the data, and is divided into four layers which work together to provide relational database functionality to the users, as shown in the following diagram:

Azure SQL Database Architecture

Figure 1.1 The four layers of Azure SQL Database

Note

If you were to compare it to on-premises SQL Server architecture, other than the Service Layer, the rest of the architecture is pretty similar.

  • Client Layer:The Client layer acts as an interface for applications to access the SQL Database. The client layer can be either on-premises or on Microsoft Azure. The Tabular Data Stream (TDS) is used to transfer data between the SQL Database and applications. SQL Server also uses TDS to communicate to applications. This allows applications such as .NET, ODBC, ADO.NET, and Java to easily connect to SQL Database without any additional requirements.
  • Service Layer:TheService layer acts as a gateway between the Client and Platform layers. It is responsible for:
    • Provisioning the SQL database
    • User authentication and SQL database validation
    • Enforcing security (Firewall rules and denial of service attacks)
    • Billing and metering for SQL database
    • Routing connections from the Client layer to the physical server hosting the SQL database in the Platform layer
  • Platform Layer:ThePlatform layer consists of physical servers hosting SQL databases in data centers. Each SQL database is stored in one physical server and is replicated across two different physical servers:
    • As shown in Figure 1.1, the Platform layer has two other components, Azure Fabric and Management Services. Azure Fabric is responsible for load balancing, automatic failover, and automatic replication of the SQL Database between physical servers. Management Services takes care of individual server health monitoring and patch updates.
  • Infrastructure Layer: This layer is responsible for the administration of physical hardware and OS.

    Note

    Dynamic routing allows for moving the SQL Database to different physical servers in case of any hardware failures or load distribution.

Azure SQL Database Request Flow

Azure SQL Database Request Flow

Figure 1.2: Platform layer - nodes

The application sends a TDS request (login, DML, or DDL queries) to the SQL Database. The TDS request is not directly sent to the Platform layer. The request is first validated by the SQL Gateway Service at the Service layer.

The Gateway Service validates the login and firewall rules, and checks for denial of service attacks. It then dynamically determines the physical server on which the SQL database is hosted and routes the request to that physical server in the Platform layer. The dynamic routing allows SQL Database to be moved across physical servers or SQL instances in case of hardware failures.

Note

Here, a node is a physical server. A single database is replicated across three physical servers internally by Microsoft to help the system recover from physical server failures. The Azure SQL Server user connects to just a logical name.

Dynamic routing refers to routing the database request to the physical server which hosts an Azure SQL Database. This routing is done internally and is transparent to the user. If one physical server hosting the database fails, the dynamic routing will route the requests to the next available physical server hosting the Azure SQL Database.

Internals about dynamic routing are out of the scope of this book.

As shown in Figure 1.2, the Platform layer has three nodes: Node 1, Node 2, and Node 3. Each node has a primary replica of a SQL database and two secondary replicas of two different SQL databases from two different physical servers. The SQL database can fail over to the secondary replicas if the primary replica fails. This ensures high availability of the SQL Database.

Provisioning an Azure SQL Database

Provisioning an Azure SQL Database refers to creating a new and blank Azure SQL Database.

In this section, we'll create a new SQL database in Azure using the Azure portal:

  1. Open a browser and log in to the Azure portal using your Azure credentials: https://portal.azure.com.
  2. On the left-hand navigation pane, select New:
    Provisioning an Azure SQL Database
  3. In the New pane, under Databases, select SQL Database:
    Provisioning an Azure SQL Database
  4. In the SQL Database pane, provide:
    • Database name
    • Subscription
    • Resource group
    • Source as a blank database

    Note

    A Resource group is a logical container that is used to group Azure resources required to run an application. For example, say, toystore retail web application uses different Azure resources such as Azure SQL Database, Azure VMs, and Azure Storage Account. All of these resources can be grouped in a single Resource group, say, toystore.

    The SQL Database name should be unique across Microsoft Azure and should be as per the following naming rules and conventions: https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions

  5. Select the Server option to create an Azure SQL Server. You can also opt to create the database in an existing Azure SQL Server:
    Provisioning an Azure SQL Database
  6. In the Server pane, select Create a new server. In the New server pane, provide the following details and click Select at the bottom of the New server pane:
    • Server Name
    • Server admin login
    • Password
    • Confirm password
    • Location.

    The server name should be unique across Microsoft Azure and should be as per the following naming rules and conventions:

    https://docs.microsoft.com/en-us/azure/architecture/best-practices/naming-conventions

    Provisioning an Azure SQL Database
  7. Under the Want to use SQL elastic pool? option, select Not now.
  8. Under the pricing tier option, select Standard:
    Provisioning an Azure SQL Database
  9. Leave the Collation as default. Select the Create button to provision a:
    • New Azure Resource Manager group
    • New Azure SQL Server
    • New Azure SQL Database

    Provisioning may take 2-5 minutes. Once the resources are provisioned, you'll get a notification, as shown in the following screenshot:

    Provisioning an Azure SQL Database
    Provisioning an Azure SQL Database
  10. Select Go to resource to go to the newly created SQL Database.

Connecting and Querying the SQL Database from the Azure Portal

In this section, we'll learn to connect and query the SQL Database from Azure portal:

  1. Under the toystore pane, select Query editor (preview):
    Connecting and Querying the SQL Database from the Azure Portal
  2. In the Query editor (preview) pane, select Login:
    Connecting and Querying the SQL Database from the Azure Portal
  3. In the Login pane, under the Authorization type, select SQL server authentication if it is not already selected:
    • Observe that the Login textbox is automatically populated with the correct login name.
    • Under the Password textbox, enter the password.
    • Select OK to authenticate and return to the Query editor (preview) pane:
    Connecting and Querying the SQL Database from the Azure Portal
  4. In Query editor (preview), select Open query and open C:\code\Lesson01\sqlquery.sql.
    • The query will open in the Query editor (preview) window. The query creates new table orders, populates it with sample data, and returns the top 10 rows from the orders table: 
      -- create a new orders table
CREATE TABLE orders 
  ( 
orderid INT IDENTITY(1, 1) PRIMARY KEY, 
     quantity INT, 
     sales    MONEY 
  ); 
--populate Orders table with sample data
; 
WITH t1 
     AS (SELECT 1 AS a 
         UNION ALL 
         SELECT 1), 
     …
     nums 
     AS (SELECT Row_number() 
                  OVER ( 
                    ORDER BY (SELECT NULL)) AS n 
         FROM   t5) 
INSERT INTO orders 
SELECT n, 
       n * 10 
FROM   nums;
GO
SELECT TOP 10 * from orders;
  5. Select Run to execute the query. You should get the following output:
    Connecting and Querying the SQL Database from the Azure Portal

Connecting and Querying the SQL Database from SQL Server Management Studio

In this section, we'll connect and query an Azure SQL Database from SQL Server Management Studio (SSMS):

  1. Open SQL Server Management Studio. In the Connect to Server dialog box.
  2. Select the Server type as Database Engine, if not already selected.
  3. Under the Server name, provide the Azure SQL Server name. You can find the Azure SQL Server in the Overview section of the Azure SQL Database pane on the Azure portal:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  4. Select SQL Server Authentication as the Authentication Type.
  5. Provide the login and password for the Azure SQL Server and select Connect:
    Connecting and Querying the SQL Database from SQL Server Management Studio
    • You'll get an error saying Your client IP address does not have access to the server. To connect to Azure SQL Server, you must add the IP of the system you want to connect from under the firewall rule of Azure SQL Server. You can also provide a range of IP addresses to connect from:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  6. To add your machine IP to the Azure SQL Server firewall rule, complete the following steps:
    1. Switch to the Azure portal.
    2. Open the toystore SQL database Overview section, if it's not already open.
    3. From the Overview pane, select Set server firewall:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  7. In the Firewall settings pane, select Add client IP:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  8. The Azure portal automatically detects the machine IP and adds it to the firewall rule:
    • If you wish to rename the rule, you can do so by providing a meaningful name under the Rule Name column.
    • All machines with IPs between Start IP and End IP are allowed to access all the databases on the toyfactory server.

    Note

    The virtual network can be used to add a SQL database in Azure to a given network. A detailed explanation of virtual networks is out of the scope of this book.

    Connecting and Querying the SQL Database from SQL Server Management Studio
    • Select Save to save the firewall rule.
  9. Switch back to SQL Server Management Studio (SSMS) and click Connect. You should now be able to connect to the Azure SQL Server. Press F8 to open the Object Explorer, if it's not already open:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  10. You can view and modify firewall settings using T-SQL in the master database. Press Ctrl + N to open a new query window. Make sure that the database is set to master.

    Note

    To open a new query window in the master database context, in Object Explorer, expand Databases then expand System Databases. Right-click on master database and select New Query.

  11. Enter the following query to view the existing firewall rules: 
    SELECT * FROM sys.firewall_rules

    You should get the following output:

    Connecting and Querying the SQL Database from SQL Server Management Studio
    • The AzureAllWindowsAzureIps firewall is the default firewall which allows resources within Microsoft to access the Azure SQL Server.
    • The rest are user-defined firewall rules. The firewall rules will be different for you from what is shown here.
    • You can use sp_set_firewall_rule to add a new firewall rule and sp_delete_firewall_rule to delete an existing firewall rule.
  12. To query the toystore SQL database, change the database context of the SSMS query window to toystore. You can do this by selecting the toystore database from the database drop-down in the menu:
    Connecting and Querying the SQL Database from SQL Server Management Studio
  13. Copy and paste the following query in the query window: 
    SELECT COUNT(*) AS OrderCount FROM orders;
    • The query will return the total number of orders from the orders table. You should get the following output:
    Connecting and Querying the SQL Database from SQL Server Management Studio

Deleting Resources

To delete Azure SQL Database, Azure SQL Server, and Azure Resource group, complete the following steps:

Note

All resources must be deleted to successfully complete the activity at the end of this chapter.

  1. Switch to the Azure portal and select All Resources from the left-hand navigation pane.
  2. From the All resources pane, select the checkbox besides toyfactory and the Azure SQL Server which is to be deleted, and select Delete from the top menu:
    Deleting Resources
  3. In the Delete Resources window, type yes in the confirmation box and click the Delete button to delete Azure SQL Server and Azure SQL Database:
    Deleting Resources

    Note

    To only delete Azure SQL Database, check the Azure SQL Database checkbox.

  4. To delete the Azure Resource Group, select Resource groups from the left navigation pane:
    Deleting Resources
  5. In the Resource groups pane, click on the three dots besides the toystore resource group and select Delete resource group from the context menu:
    Deleting Resources
    • In the delete confirmation pane, type the resource under the TYPE THE RESOURCE GROUP NAME section and click the Delete button at the bottom of the pane.

Differences between Azure SQL Database and SQL Server

Azure SQL Database is a PaaS offering and therefore some of the features differ from the on-premises SQL Server. Some of the important features which differ from on-premises SQL Server are:

Backup and Restore

Conventional database backup and restore statements aren't supported. The backups are automatically scheduled and start within a few minutes of the database provisioning. The backups are transactionally consistent, which means that you can do a point-in-time restore.

There is no additional cost for backup storage until the backup storage goes beyond 200% of the provisioned database storage.

You can reduce the backup retention period to manage the backup storage cost. You can also use the long-term retention period feature to store the backup in the Azure vault for a much smaller cost for a longer duration.

Other than the automatic backups, you can export the Azure SQL Database bacpac or dacpac file to Azure storage.

Recovery Model

The default recovery model of an Azure SQL Database is Full and it can't be modified to any other recovery models as in on-premises recovery models.

The recovery model is set when the master database is created, meaning when an Azure SQL Server is provisioned. The recovery model can't be modified because the master database is read-only.

To view the recovery model of an Azure SQL Database, execute the following query:

SELECT name, recovery_model_desc FROM sys.databases;

Note

You can use any of the two methods discussed earlier in the chapter to run the query – the Azure portal or SSMS.

You should get the following output:

Recovery Model

SQL Server Agent

Azure SQL Server doesn't have SQL Server Agent, which is used to schedule jobs and send success/failure notifications. However, you can use the following workarounds:

  • Create a SQL Agent job on an on-premises SQL Server or on an Azure SQL VM SQL Agent to connect and run on the Azure SQL Database.
  • Azure Automation: It allows users to schedule jobs in Microsoft Azure to automate manual tasks. This topic is covered in detail later in the book.
  • Elastic Database Jobs: It is an Azure Cloud Service that allows the scheduled execution of ad hoc tasks. This topic is covered in detail later in the book.
  • Use PowerShell to automate the task and schedule the PowerShell script execution with Windows Scheduler, on-premises, or Azure SQL VM SQL Agent.

Change Data Capture

Change Data Capture (CDC) allows you to capture data modifications to CDC-enabled databases and tables. The CDC feature is important in incremental load scenarios, such as incrementally inserting changed data to the data warehouse from an OLTP environment. The CDC requires SQL Server Agent, and therefore isn't available in Azure SQL Database. However, you can use the temporal table, SSIS, or Azure Data factory to implement CDC.

Auditing

The auditing features, such as C2 auditing, system health extended event, SQL default trace, and anything that writes alerts or issues into event logs or SQL error logs, aren't available. This is because of the fact that it's a PaaS offering and we don't have access or control to event logs or error logs.

However, there is an auditing and threat detection feature available out of the box for Azure SQL Database.

Mirroring

You can't enable mirroring between two Azure SQL Databases, but you can configure Azure SQL Database as a mirror server. You can also set up a readable secondary for an Azure SQL Database, which is actually better than mirroring.

Table Partitioning

Table partitioning using a partition scheme and partition function is allowed in Azure SQL Database; however, because of the PaaS nature of the SQL database, all partitions should be created on a primary file group. You won't get the performance improvement for having partitions on different disks (spindles); however, you will get performance improvement of partition elimination.

Replication

Conventional replication techniques such as snapshot, transactional, and merge replication can't be done between two Azure SQL Databases. However, an Azure SQL Database can be a subscriber to an on-premises or Azure VM SQL Server. However, this too has the following limitations:

  • Supports one-way transactional replication, not peer-to-peer or bi-directional replication
  • Supports only push subscription
  • You should have SQL Server 2012 or above at on-premises
  • Replication and distribution agents can't be configured on Azure SQL Database

Multi-Part Names

Three-part names (databasename.schemaname.tablename) are only limited to tempdb wherein you access a temp table as tempdb.dbo.#temp. For example, if there is a temporary table, say, #temp1, then you can run the following query to select all of the values from #temp1:

SELECT * FROM tempdb.dbo.#temp1

You can't access the tables in different SQL databases in Azure on the same Azure SQL Server using three-part names. Four-part (ServerName.DatabaseName.SchemaName.TableName) names aren't allowed at all.

You can use Elastic query to access tables from different databases from an Azure SQL Server. Elastic query is covered in detail later in the book. You can access objects in different schemas in the same Azure SQL Database using two-part (Schemaname.Tablename) names.

To explore other T-SQL differences, visit: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-transact-sql-information.

Unsupported Features

Some features not supported by Azure SQL Database or Azure SQL Server are:

SQL Browser Service

The SQL Browser is a Windows service and provides instance and post information to incoming connection requests. This isn't required as the Azure SQL Server listens to port 1433 only.

File Stream

Azure SQL Database doesn't support FileStream or filetable, just because of the PaaS nature of the service. There is a workaround to use Azure Storage; however, that would require re-work on the application and the database side.

Common Language Runtime (SQL CLR)

SQL CLR allows users to write programmable database objects such as stored procedures, functions, and triggers in managed code. This provides significant performance improvement in some scenarios. SQL CLR was first supported and then the support was removed due to concerning security issues.

Resource Governor

Resource Governor allows you to throttle/limit resources (CPU, Memory, I/O) as per different SQL Server workloads. This feature is not available in Azure SQL Database.

Azure SQL Database comes with different services tiers, each suitable for different workloads. You should first evaluate the performance tier your application workload will fit into and accordingly provision the database for that performance tier.

Global Temporary Tables

Global temporary tables are defined by ## and are accessible across all sessions. These are not supported in Azure SQL Database. Local temporary tables are allowed.

Log Shipping

Log shipping is the process of taking log backups on a primary server, and copying and restoring them on the secondary server. Log shipping is commonly used as a high availability or disaster recovery solution, or to migrate a database from one SQL instance to another. Log shipping isn't supported by Azure SQL Database.

SQL Trace and Profiler

SQL Trace and Profiler can't be used to trace the events on Azure SQL Server. As of now, there isn't any direct alternate other than using DMVs, monitoring using Azure Portal, and extended events.

Trace Flags

Trace Flags are special switches used to enable or disable a particular SQL Server functionality. These are not available in Azure SQL Server.

System Stored Procedures

Azure SQL Database doesn't support all of the system stored procedures supported in the on-premises SQL Server. System procedures such as sp_addmessage, sp_helpuser, and sp_configure aren't supported. In a nutshell, procedures related to features unsupported in Azure SQL Database aren't supported.

USE Statement

The USE statement is used to switch from one database context to another. This isn't supported in Azure SQL Database.

Activity: Provisioning Azure SQL Server and SQL Database using PowerShell

This section discusses provisioning of an Azure SQL Server and SQL Database using PowerShell. To understand the process, let’s take the example of Mike, who is the newest member of the Data Administration team at ToyStore Ltd., a company that manufactures toys for children. ToyStore has an e-commerce web portal that allows customers to purchase toys online. ToyStore has migrated the online e-commerce portal to Microsoft Azure and is therefore moving to Azure SQL Database from the on-premises SQL Server. Mike is asked to provision the Azure SQL Database and other required resources as his initial assignment. This can be achieved by following the steps below:

Note

If you fall short of time, you can refer to the C:\code\Chapter01\ProvisionAzureSQLDatabase.ps1 file. You can run this file in the PowerShell console instead of typing the code as instructed in the following steps. Open a PowerShell console and enter the full path, as stated previously, to execute the PowerShell script. You'll have to change the Azure Resource Group name, Azure SQL Server, and Azure SQL Database name in the script before executing it.

  1. Save the Azure profile details into a file for future reference. Press Windows + R to open the Run command window.
  2. In the Run command window, type powershell and then press Enter. This will open a new PowerShell console window:
    Activity: Provisioning Azure SQL Server and SQL Database using PowerShell
  3. In the PowerShell console, run the following command: 
    Add-AzureRmAccount
    • You'll have to enter your Azure credentials into the pop-up dialog box. After a successful login, the control will return to the PowerShell window.
  4. Run the following command to save the profile details to a file: 
    Save-AzureRmProfile -Path C:\code\MyAzureProfile.json
  5. The Azure subscription details will be saved into the MyAzureProfile.json file in JSON format:
    • If you wish to explore the JSON file, you can open it in any editor to review its content:
    Activity: Provisioning Azure SQL Server and SQL Database using PowerShell

    Note

    Saving the profile in a file allows you to use the file to log in to your Azure account from PowerShell instead of providing your credentials every time in the Azure authentication window.

Provisioning Azure SQL Database

  1. Press Window + R to open the Run command window. Type PowerShell_ISE.exe in the Run command window and hit Enter. This will open a new PowerShell ISE editor window. This is where you'll write the PowerShell commands:
    Provisioning Azure SQL Database
  2. In the PowerShell ISE, select File from the top menu, and then click Save. Alternatively, you can press Ctrl + S to save the file. In the Save As Dialog box, browse to the C:\Code\Lesson01\ directory. In the File name textbox, type Provision-AzureSQLDatabase.ps1, and then press Save to save the file:
    Provisioning Azure SQL Database
  3. Copy and paste the following code in the Provision-AzureSQLDatabase.ps1 file one after another. The code explanation wherever required is given in the following code snippet and in the comments within the code snippet.
  4. Copy and paste the following code to define the parameters: 
    param
(
[parameter(Mandatory=$true)]
[String] $ResourceGroup,
[parameter(Mandatory=$true)]
[String] $Location,
[parameter(Mandatory=$true)]
[String] $SQLServer,
[parameter(Mandatory=$true)]
[String] $UserName,
[parameter(Mandatory=$true)]
[String] $Password,
[parameter(Mandatory=$true)]
[String] $SQLDatabase,
[parameter(Mandatory=$true)]
[String] $Edition="Basic",
[parameter(Mandatory=$false)]
[String] $AzureProfileFilePath
)

    The preceding code defines the parameter required by the scripts:

    • ResourceGroup: The resource group which will host the logical Azure SQL Server and Azure SQL Database.
    • Location: The resource group location. The default is East US 2.
    • SQLServer: The logical Azure SQL Server name which will host the Azure SQL Database.
    • UserName: The Azure SQL Server admin username. The default username is sqladmin. Don't change the username, keep it as default.
    • Password: The Azure SQL Server admin password. The default password is Packt@pub2. Don't change the password, keep it as default.
    • SQLDatabase: The Azure SQL Database to create.
    • Edition: The Azure SQL Database edition. This is discussed in detail in Chapter 2,Migrating SQL Server Database to an Azure SQL Database.
    • AzureProfileFilePath: The full path of the file which contains your Azure profile details. You created this earlier under the Saving Azure Profile Details to a File section.
  5. Copy and paste the following code to log in to your Azure account from PowerShell: 
    Start-Transcript -Path .\log\ProvisionAzureSQLDatabase.txt -Append
if([string]::IsNullOrEmpty($AzureProfileFilePath))
{
    $AzureProfileFilePath="..\..\MyAzureProfile.json"
}
if((Test-Path -Path $AzureProfileFilePath))
{
    $profile = Import-AzureRmContext-Path $AzureProfileFilePath
    $SubscriptionID = $profile.Context.Subscription.SubscriptionId
}
else
{
    Write-Host "File Not Found $AzureProfileFilePath" -ForegroundColor Red
    $profile = Login-AzureRmAccount
    $SubscriptionID =  $profile.Context.Subscription.SubscriptionId
}
Set-AzureRmContext -SubscriptionId $SubscriptionID | Out-Null
    • The preceding code first checks for the profile details in the Azure Profile file. If found, it retrieves the subscription ID of the profile, otherwise, it uses the Login-AzureRmAccount command to pop up the Azure login dialog box. You would have to provide your Azure credentials in the login dialog box. After a successful login, it retrieves and stores the subscription ID of the profile in the $SubscriptionID variable.
    • It then sets the current Azure subscription to yours for the PowerShell cmdlets to use in the current session.
  6. Copy and paste the following code to create the resource group if it doesn't already exist: 
    # Check if resource group exists
# An error is returned and stored in notexists variable if resource group exists
Get-AzureRmResourceGroup -Name $ResourceGroup -Location $Location -ErrorVariable notexists -ErrorAction SilentlyContinue

#Provision Azure Resource Group
if($notexists)
{

Write-Host "Provisioning Azure Resource Group $ResourceGroup" -ForegroundColor Green
$_ResourceGroup = @{
  Name = $ResourceGroup;
  Location = $Location;
  }
New-AzureRmResourceGroup @_ResourceGroup;
}
else
{

Write-Host $notexits -ForegroundColor Yellow
}
    • The Get-AzureRmResourceGroup cmdlet gets the given resource group. If the given resource group doesn't exist, an error is returned. The error returned is stored in the notexists variable.
    • The New-AzureRmResourceGroup cmdlet provisions the new resource group if the notexists variable isn't empty.
  7. Copy and paste the following code to create a new Azure SQL Server if it doesn't exist: 
    Get-AzureRmSqlServer -ServerName $SQLServer -ResourceGroupName $ResourceGroup -ErrorVariable notexists -ErrorAction SilentlyContinue
if($notexists)
{
Write-Host "Provisioning Azure SQL Server $SQLServer" -ForegroundColor Green
$credentials = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $UserName, $(ConvertTo-SecureString -String $Password -AsPlainText -Force)
$_SqlServer = @{
  ResourceGroupName = $ResourceGroup;
  ServerName = $SQLServer;
  Location = $Location;
  SqlAdministratorCredentials = $credentials;
  ServerVersion = '12.0';
  }
New-AzureRmSqlServer @_SqlServer;
}
else
{
Write-Host $notexits -ForegroundColor Yellow
}
    • The Get-AzureRmSqlServercmdlet gets the given Azure SQL Server. If the given Azure SQL Server doesn't exist, an error is returned. The error returned is stored in the notexists variable.
    • The New-AzureRmSqlServercmdlet provisions the new Azure SQL Server if the notexists variable isn't empty.
  8. Copy and paste the following code to create the Azure SQL Database if it doesn't already exist: 
    # Check if Azure SQL Database Exists
# An error is returned and stored in notexists variable if resource group exists
Get-AzureRmSqlDatabase -DatabaseName $SQLDatabase -ServerName $SQLServer -ResourceGroupName $ResourceGroup -ErrorVariable notexits -ErrorAction SilentlyContinue

if($notexits)
{
# Provision Azure SQL Database
Write-Host "Provisioning Azure SQL Database $SQLDatabase" -ForegroundColor Green
$_SqlDatabase = @{
 ResourceGroupName = $ResourceGroup;
 ServerName = $SQLServer;
 DatabaseName = $SQLDatabase;
 Edition = $Edition;
 };
New-AzureRmSqlDatabase @_SqlDatabase;
}

else
{

Write-Host $notexits -ForegroundColor Yellow
}
    • Get-AzureRmSqlDatabase gets the given Azure SQL Database. If the given Azure SQL Database doesn't exist, an error is returned. The error returned is stored in the notexists variable.
    • The New-AzureRmSqlDatabase provisions the new Azure SQL Database if the notexists variable isn't empty.
  9. Copy and paste the following code to add the system's public IP address to the Azure SQL Server firewall rule: 
    $startip = (Invoke-WebRequest 
http://myexternalip.com/raw --UseBasicParsing -ErrorVariable err -ErrorAction SilentlyContinue).Content.trim()
$endip=$startip
Write-host "Creating firewall rule for $azuresqlservername with StartIP: $startip and EndIP: $endip " -ForegroundColor Green
$NewFirewallRule = @{
 ResourceGroupName = $ResourceGroup;
 ServerName = $SQLServer;
 FirewallRuleName = 'PacktPub';
 StartIpAddress = $startip;
 EndIpAddress=$endip;
 };
New-AzureRmSqlServerFirewallRule @NewFirewallRule;
    • The preceding code first gets the public IP of the system (running this PowerShell script) by calling the website http://myexternalip.com/raw using the Invoke-WebRequest command. The link returns the public IP in text format, which is stored in the $startip variable.
    • The IP is then used to create the firewall rule by the name of PacktPub using the New-AzureRmSqlServerFirewallRule cmdlet.

Executing the PowerShell Script

  1. Press Window + R to open the Run command window. Type PowerShell and hit Enter to open a new PowerShell console window.
  2. Change the directory to the folder that has the shard-toystore.ps1 script. For example, if the script is at the C:\Code\Lesson01\ directory, then run the following command to switch to this directory: 
    cd C:\Code\Lesson01
  3. In the following command, change the parameter values. Copy the command to the PowerShell console and hit Enter: 
    .\ProvisionAzureSQLDatabase.ps1 -ResourceGroup toystore -SQLServer toyfactory -UserName sqladmin -Password Packt@pub2 -SQLDatabase toystore -AzureProfileFilePath C:\Code\MyAzureProfile.json
    • The preceding command will create the toystore resource group, toyfactory Azure SQL Server, and toystore Azure SQL Database. It'll also create a firewall rule by the name of PacktPub with the machine's public IP address.
上一章目录下一章