Day 70 – Creating and Populating Tables in Azure SQL with PowerShell

Welcome to Day 70 of the “100 Days of DevOps with PowerShell”! For background on our goals in this series, see Announcing the “100 Days of Devops with PowerShell” Series here at SCC. If this is the first time you are viewing an article of the series and you are new to DevOps and PowerShell Desired State Configuration (DSC), I encourage you to read through the previous installments, particularly Day 1.

Objective of this post…

This post is part 3 in a 4-part series within the series, focused on Azure SQL Database provisioning and management with PowerShell. This installment builds on Day 61 (Creating an Azure SQL Server [PaaS] instance with PowerShell) and Day 66 (automation creation of a database instance in that server). If you have not read those installments yet, definitely go back and read those quickly. 

In this post, we are going to create a table in our SQL database from Day 66 and populate it with data. For good measure, I will include sample code for T-SQL SELECT and UPDATE statements as well.

Step 1: Connect to and Set Your Subscription

Make sure you remove the spaces and then paste between the quotes to the right of the $thumbprint variable in the snippet below.

You will also need to replace the $subID value with your Azure subscription ID.

You then need to set this subscription as the active subscription for the script.

Now you are ready to create your table.

Step 2: Create a Table in Your Azure SQL Database

Here is the code (borrowed from Day 45) to create a table. This is just ordinary T-SQL code, which I have saved to a variable named $TableStatement.

Now you will create the table. Make sure to replace the -ServerInstance, -Username and -Password parameters to values appropriate to your environment.

Now you should have a table in your database. Use SQL Server Management Studio to connect to your Azure SQL Server instance and verify this is the case.

Step 3: Populate the Table with Data

In the last step, you will populate your SQL table with data. The T-SQL assigned to the $TableData variable is just standard T-SQL just like you would use with a SQL instance in a Windows VM (IaaS). My data here will make a little more sense to you if you go back and read Day 45, which is where I pulled this example from.

Now you should have a table with data in it. For good measure, I will include T-SQL UPDATE and SELECT statement examples in PowerShell below so you have samples all in one place. As with all previous queries, the samples below will require you to update with your according to your environment (server, username,password, table data, etc.)
Updating Records

And if I wanted to update an existing record (an UPDATE query), my command would look like this example:

Retrieving Records

Retrieving data would also be a simple PowerShell call. For example, to retrieve configuration for a server named corpweb1.contoso.com, I would use the following snippet:

If I wanted to update the configuration of all the HR web farm servers, I might base my query on a combination of factors in the Roles and Applications columns, like so:

The bottom line here is we are now building a base of information that will enable us to drive detailed configuration MOF file development in a highly automated manner.

Conclusion

That’s all for this installment. I hope you are gaining confidence in working with the PaaS flavor and SQL in Microsoft Azure via PowerShell automation.  As you move into a DevOps driven world, Azure SQL and PowerShell can be a very effective combination for highly automated, standardized and cost-effective method for provisioning and managing SQL databases.

Previous Installments

To see the previous installments in this series, visit “100 Days of DevOps with PowerShell”.

Leave a Reply