How to Query Azure SQL Databases via PowerShell with SMO

In previous articles, I have demonstrated how to create Azure SQL Server and database instances via PowerShell in:

Certainly you could create both your Azure SQL Server instance and the database in the Azure Portal, so do as you prefer. In this post, I will ultimately give you the steps to connect to Azure SQL and work with a bit with the dataset returned.

Why SMO? We’re going to use Shared Management Objects (SMO) in this example, which is a convenient approach because really only requires that SMO (and its dependencies) are installed where you run the PowerShell script. You don’t need any specialized SQL Server PowerShell modules or management tools.

Prerequisites

In order to connect, you will need to ensure the following is true on the machine you are working from:

TIP: Make sure you load the CLR Types first, as SMO requires CLR in order to install successfully. Both of these components are on the same page on the MS site (http://www.microsoft.com/en-us/download/details.aspx?id=29065)

Firewall Rule

Another prerequisite is a firewall rule on your Azure SQL Server. Azure SQL blocks all remote connections by default, so you have to create a firewall rule. The Azure Portal will show you what it sees as your current IP, making it easy to add an exclusion for yourself.

To create an exclusion for all IPs, simply enter a range of 0.0.0.0 – 255.255.255.255. Obviously this is not the most secure option, so open up only to the source IPs that really require.

Azure SQL

How to create firewall rules for Azure SQL is also available at http://blogs.msdn.com/b/umits/archive/2012/11/20/windows-azure-sql-database-how-to-manage-your-firewall-rules-with-windows-azure-management-portal.aspx.

Sample Script

The sample code here will run whatever T-SQL SELECT, INSERT, UPDATE or DELETE query you assign to the $sql variable.

# Connect to SQL

[Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")

$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') ' tcp:mySQLServer.windows.net'

$s.ConnectionContext.LoginSecure = $false

$s.ConnectionContext.Login="myuser"

$s.ConnectionContext.Password="P@ssw0rd1!"

# Set DB

$db = $s.Databases["MySQLDB"]

# Build SQL Query (SELECT, INSERT, UPDATE or DELETE)

$sql = 'SELECT * FROM MySQLTable'

#Submit SQL Query (sample 1)

$ds = $db.ExecuteWithResults($sql).Tables[0]

At this point, your dataset is stored in the $ds variable.

If you wanted simply to dump your dataset to a spreadsheet, you could do change the line with $ds to the following:


#Submit SQL Query and write results to CSV (sample 2)
$OutputFile = 'c:\mydir\myfile.csv'
$ds = $db.ExecuteWithResults($sql).Tables[0]  | Export-Csv "$OutputFile" -NoTypeInformation

If you wanted to loop through a multi-row dataset and write to your console, flip the last section to this snippet:

</pre>
#Loop through results and write to console (sample 3)
Foreach ($t in $ds.Tables)
{
   Foreach ($r in $t.Rows)
   {
      Foreach ($c in $t.Columns)
      {
          Write-Host $c.ColumnName "=" $r.Item($c)
      }
   }
}

Hopefully this gives you a nice jump start. I did not mention stored procedures, which I’ll cover in a quick post in the near future.

Print Friendly, PDF & Email
Posted in Blog Tagged with: , ,

Leave a Reply

%d bloggers like this: