First steps with Azure SQL Database and Powershell – Part 1

In addition to the ability to deploy a virtual server on a SQL server in Azure, there is also the “simpler” method to deploy only one database on demand => the Azure SQL Database. This database-as-a-service is also very good and easy to use for a variety of applications, although there are a lot of options you should think about before.

• is a simple database enough
• rather an elastic pool
• In which region to deploy
• or rather geo-redundant
• is the “supplied” backup is enough
• or must have to have a “Long Time Retention” backup
• Who accesses the database from where?
• What level of performance do I need for the database or the Elastic Pool?

An overview of the individual possibilities and their use for the respective solution, as well as a larger number of answers to many questions, can be found in the documentation of Microsoft => https://docs.microsoft.com/en-us/azure/sql-database/

Start with a simple Azure SQL Database

An important point in providing an Azure SQL Database is that you not only need a database but also a logical SQL Server (=> listener endpoint), without this “shell” you can not host databases.

In my example, we use Powershell to create a default database, of course, using a script, so that we can create the server and its database, or just the database, the same default way as needed.

What do we need to create an Azure SQL Database

• a resource group or its name
• a location/region for the resource group
• admin-username and password
• a name for the logical SQL Server (which must be unique)
• a database name
• typically also the IP addresses/ranges, which can access it (the own IP suffices for the first access)

The login to Azure and the selection of to-use Subscription I leave here outside forwards and start the actual script. My first script starts with the definition of various variables (see above):

# Set the resource group name and location for your server
$resourcegroupname = "RG-AzureSQLDatabase-Demo"
$location = "west europe"
# Set an admin login and password for your server
$adminlogin = "dbadmin"
$password = "DemoPwd@2017"
# Set server name - the logical server name has to be unique in the system
$servername = "server-sqldbdemo"
# The sample database name
$databasename = "db-sqldbdemo"
# The ip address range that you want to allow to access your server
$clientIP = (Invoke-WebRequest ifconfig.me/ip).Content
$startip = $clientIP
$endip = $clientIP

Create the logical server and the Azure SQL Database using sample data

Now – as in almost all my scripts – first the query whether the resource group is already exisitiert, if not it is created. After the resource group of logical servers coming next in which we can integrate in the end our Azure SQL Database. We assign the credentials from adminlogin and password to the logical server so that the server and the databases are protected. Speaking protected, the firewall of the server we need to open external access, this I ermittel an additional function and an external service my own public IP address. With this IP address, we are now configuring the logical SQL server, and finally, we check if the desired database already exists, if not, with the desired parameters.

# Create a resource group
Get-AzureRmResourceGroup -Name $resourcegroupname -ev notPresent -ea 0
if ($notPresent) {
  $resourcegroup = New-AzureRmResourceGroup -Name $resourcegroupname -Location $location
} else {
  Write-Host $resourcegroupname "already exists"
}

# Create a server with a system wide unique server name
Get-AzureRmSqlServer -ResourceGroupName $resourcegroupname -ServerName $servername -ev notPresent -ea 0
if ($notPresent) {
  $server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -Location $location `
  -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
} else {
  Write-Host $servername "already exists"
}

# Create a server firewall rule that allows access from the specified IP range
Get-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname -ServerName $servername -FirewallRuleName "AllowedIPs" -ev notPresent -ea 0
if ($notPresent) {
  $serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -FirewallRuleName "AllowedIPs" -StartIpAddress $startip -EndIpAddress $endip
} else {
  Write-Host "FirewallRule already exists"
}

# Create a blank database with an S0 performance level
Get-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename -ev notPresent -ea 0
if ($notPresent) {
  $database = New-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname `
  -ServerName $servername `
  -DatabaseName $databasename `
  -RequestedServiceObjectiveName "S0"
} else {
  Write-Host "Database" $databasename "already exists"
}

Now we can work from our workstation to use SQL Server Tools – e.g. SQL Server Management Studio – connecting to this database and try to link the application and run some tests.