Skip to content

Latest commit

 

History

History
142 lines (105 loc) · 6.84 KB

dns-alias-powershell-create.md

File metadata and controls

142 lines (105 loc) · 6.84 KB
Raw
title titleSuffix description author ms.author ms.reviewer ms.date ms.service ms.subservice ms.topic ms.custom keywords ms.devlang
DNS Alias (PowerShell & Azure CLI)
Azure SQL Database & Azure Synapse Analytics
PowerShell and Azure CLI cmdlets enable you to redirect new client connections to a different SQL server in Azure, without having to touch any client configuration.
rajeshsetlem
rsetlem
wiassaf, mathoma, amagarwa, maboja, vanto
05/14/2019
sql-database
high-availability
how-to
sqldbrb=1
devx-track-azurecli
devx-track-azurepowershell
dns sql database
powershell

PowerShell for DNS Alias to Azure SQL Database

[!INCLUDEappliesto-sqldb-asa]

This article provides a PowerShell script that demonstrates how you can manage a DNS alias for the SQL server hosting your Azure SQL Database.

Note

This article has been updated to use either the Azure PowerShell Az module or Azure CLI. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020.

To learn more about the Az module and AzureRM compatibility, see Introducing the Azure PowerShell Az module. For installation instructions, see Install Azure PowerShell or Install Azure CLI.

DNS alias in connection string

To connect a logical SQL server, a client such as SQL Server Management Studio (SSMS) can provide the DNS alias name instead of the true server name. In the following example server string, the alias any-unique-alias-name replaces the first dot-delimited node in the four node server string:

<yourServer>.database.windows.net

Prerequisites

If you want to run the demo PowerShell script given in this article, the following prerequisites apply:

  • An Azure subscription and account, for free trial, see Azure trials
  • Two servers

Example

The following code example starts by assigning literal values to several variables.

To run the code, edit the placeholder values to match real values in your system.

PowerShell

The cmdlets used are the following:

To install or upgrade, see Install Azure PowerShell module.

Use Get-Module -ListAvailable Az in powershell_ise.exe, to find the version.

$subscriptionName = '<subscriptionName>';
$sqlServerDnsAliasName = '<aliasName>';
$resourceGroupName = '<resourceGroupName>';  
$sqlServerName = '<sqlServerName>';
$resourceGroupName2 = '<resourceGroupNameTwo>'; # can be same or different than $resourceGroupName
$sqlServerName2 = '<sqlServerNameTwo>'; # must be different from $sqlServerName.

# login to Azure
Connect-AzAccount -SubscriptionName $subscriptionName;
$subscriptionId = Get-AzSubscription -SubscriptionName $subscriptionName;

Write-Host 'Assign an alias to server 1...';
New-AzSqlServerDnsAlias –ResourceGroupName $resourceGroupName -ServerName $sqlServerName `
    -Name $sqlServerDnsAliasName;

Write-Host 'Get the aliases assigned to server 1...';
Get-AzSqlServerDnsAlias –ResourceGroupName $resourceGroupName -ServerName $sqlServerName;

Write-Host 'Move the alias from server 1 to server 2...';
Set-AzSqlServerDnsAlias –ResourceGroupName $resourceGroupName2 -TargetServerName $sqlServerName2 `
    -Name $sqlServerDnsAliasName `
    -SourceServerResourceGroup $resourceGroupName -SourceServerName $sqlServerName `
    -SourceServerSubscriptionId $subscriptionId.Id;

Write-Host 'Get the aliases assigned to server 2...';
Get-AzSqlServerDnsAlias –ResourceGroupName $resourceGroupName2 -ServerName $sqlServerName2;

Write-Host 'Remove the alias from server 2...';
Remove-AzSqlServerDnsAlias –ResourceGroupName $resourceGroupName2 -ServerName $sqlServerName2 `
    -Name $sqlServerDnsAliasName;

Azure CLI

The commands used are the following:

To install or upgrade, see Install Azure CLI.

$subscriptionName = '<subscriptionName>';
$sqlServerDnsAliasName = '<aliasName>';
$resourceGroupName = '<resourceGroupName>';  
$sqlServerName = '<sqlServerName>';
$resourceGroupName2 = '<resourceGroupNameTwo>'; # can be same or different than $resourceGroupName
$sqlServerName2 = '<sqlServerNameTwo>'; # must be different from $sqlServerName.

# login to Azure
az login -SubscriptionName $subscriptionName;
$subscriptionId = az account list[0].i -SubscriptionName $subscriptionName;

Write-Host 'Assign an alias to server 1...';
az sql server dns-alias create –-resource-group $resourceGroupName --server $sqlServerName `
    --name $sqlServerDnsAliasName;

Write-Host 'Get the aliases assigned to server 1...';
az sql server dns-alias show –-resource-group $resourceGroupName --server $sqlServerName;

Write-Host 'Move the alias from server 1 to server 2...';
az sql server dns-alias set –-resource-group $resourceGroupName2 --server $sqlServerName2 `
    --name $sqlServerDnsAliasName `
    --original-resource-group $resourceGroupName --original-server $sqlServerName `
    --original-subscription-id $subscriptionId.Id;

Write-Host 'Get the aliases assigned to server 2...';
az sql server dns-alias show –-resource-group $resourceGroupName2 --server $sqlServerName2;

Write-Host 'Remove the alias from server 2...';
az sql server dns-alias delete –-resource-group $resourceGroupName2 --server $sqlServerName2 `
    --name $sqlServerDnsAliasName;

Next steps

For a full explanation of the DNS alias feature for SQL Database, see DNS alias for Azure SQL Database.