Manage Azure SQL Databases with PowerShell

In previous posts (see links below), I've talked about Microsoft Azure virtual machines (VMs), and how similar they are to your on-premises servers with respect to management. In many cases, you don't need the full infrastructure of an Azure VM, but you'd like to spin up one or more databases. Using Microsoft's Database-As-A-Service (DBaaS) offering gives you the flexibility to create databases as you need them, and you let Microsoft worry about backups, disaster recovery, and any other maintenance you normally focus on.

Related previous posts:

Microsoft created a set of PowerShell cmdlets to manage your Azure SQL databases, but didn't put them in the SQLPS module, but in the Azure module instead. I talked about installing the PowerShell Azure modules in Using PowerShell to Get Started in Microsoft Azure. You can see the available cmdlets by using the Get-Command cmdlet.

Get-Command *Sql* -Module Azure>


$azdbsvr = New-AzureSqlDatabaseServer -location "Central US" -AdministratorLogin "DBAdmin" -AdministratorLoginPassword 'P@ssw0rd1'

I've assigned the output to a variable so I can explore the properties of the server after it's been created. You can always use the Get-Member cmdlet to see the methods and properties available for an object once it's been created. If you execute the Get-AzureSqlDatabaseServer, you can see the ServerName, Location, Admin Login. and Version of the new server created. Notice that the Server Name is a random set of letters and numbers. I'll save that property to a variable for later use. I'll convert the password I used to a SecureString object and then create a PSCredential object using the admin login and password to use as my credential parameter in accessing that server going forward.

$azsvnm = $azdbsvr.ServerName
$azpwd = ConvertTo-SecureString 'P@ssw0rd1' -AsPlainText -Force;
$azcred = New-Object System.Management.Automation.PSCredential -ArgumentList 'DBAdmin', $azpwd

Now, I'll connect to our server. I do that by establishing a connection context using the credential we've just created, using the New-AzureSqlDatabaseServerContext cmdlet.

$azcn = New-AzureSqlDatabaseServerContext -ServerName $azsvnm -Credential $azcred

Now, an ugly error message appears telling me that the "Firewall check failed." I have to set up a firewall rule to allow us to create that connection context. Embedded in the error message is the IP address that will let us set up that firewall rule, so I'll grab that, and use the New-AzureSqlDatabaseServerFirewallRule cmdlet to create the rule. Once I've done that, I can successfully connect to the server.

New-AzureSqlDatabaseServerFirewallRule -ServerName $azsvnm -RuleName 'AzureDBServerIP' -StartIpAddress '24.165.214.253' -EndIpAddress '24.165.214.253'
$azcn = New-AzureSqlDatabaseServerContext -ServerName $azsvnm -Credential $azcred

I can now use the Get-AzureSqlDatabase cmdlet to see what databases I have on my server.

Get-AzureSqlDatabase -ConnectionContext $azcn

Of course, the only database there is 'master,' so let's create one. For demo purposes, I'll create a database called 'Northwind'.

New-AzureSqlDatabase -ConnectionContext $azcn -DatabaseName 'Northwind'

At this point, I have a Microsoft Azure SQL Database called Northwind, which I can access from my application from SQL Server Management Studio (SSMS), or from PowerShell.

Object Explorer screenshot

For my part, I like to script as much of the setup as I can. Because PowerShell is built on top of .NET, I like to use ADO.NET to work with SQL Server. I'll create a connection string, just like I'd do in any application, and then create a SqlConnection object using that connection string. I have a DDL script to create the tables I need in the database, so I'll use the Get-Content cmdlet to load that script into a variable, and then create a SqlCommand object to connect the SqlConnection object and the DDL script together. I'll then open the connection, use the ExecuteNonQuery method to run the script, and close the connection.

$cstr = "Server=tcp:$azsvnm.database.windows.net;Database=Northwind;User ID=DBAdmin@$azsvnm;Password=P@ssw0rd1;Trusted_Connection=False;Encrypt=True;"
$cn = new-object system.data.SqlClient.SqlConnection($cstr)
$nwddl = Get-Content .\NorthwindDDL.sql
$cmd = New-Object System.Data.SqlClient.SqlCommand($nwddl,$cn)
$cn.Open()
$cmd.ExecuteNonQuery()
$cn.Close()

Now, the database tables have been created and the database is ready for the application to use. Without having to purchase server hardware, set up SQL Server, establish maintenance procedures, or any of the other operational tasks usually associated with using SQL Server, I have a database up and ready to use. And when I'm done, I can just as easily get rid of them.

Remove-AzureSqlDatabase -ConnectionContext $azcn -DatabaseName 'Northwind'
Remove-AzureSqlDatabaseServer $azsvnm

I like this method for proofing concepts and testing applications, or for creating short-term databases for projects that have a limited shelf-life.

Comments

Plain text