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.
# Enter values for thumbprint and subscription ID $thumbprint = "94B9A15BAD1309A671A0D1BF56C6125613567618" $myCert = Get-Item cert:\\CurrentUser\My\$thumbprint $subID = "068f241f-be2f-4f4b-82be-ec0daec178f4" Set-AzureSubscription -SubscriptionName "Example" -SubscriptionId $subID -Certificate $myCert
You then need to set this subscription as the active subscription for the script.
# Select the active subscription to be used Select-AzureSubscription -SubscriptionName "Free Trial" Get-AzureSubscription
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.
#Save the T-SQL to create the table to a variable $TableStatement = 'CREATE TABLE DSCTracking (ServerName varchar(25) NOT NULL, DSCGuid char(25) NOT NULL, Roles varchar(400) NOT NULL, Applications varchar(400) NOT NULL, Compliance varchar(400), Owner varchar(50), DateAdded datetime, DateUpdated datetime, Description varchar(4000) PRIMARY KEY (ServerName), UNIQUE (DSCGuid))'
Now you will create the table. Make sure to replace the -ServerInstance, -Username and -Password parameters to values appropriate to your environment.
#Call the server and populate the table Invoke-Sqlcmd -Database PeteTest -ServerInstance usquchkpa7.database.windows.net -Username pzerger -Password P@ssw0rd1! -OutputSqlErrors $True -Query $TableStatement
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.
#insert data into newly created table $TableData = "INSERT INTO DSCTracking VALUES ('hrweb4.contoso.com','79ad26df-4f09-4526-82e1-a849bcb123be','domain;dotnet;web','HRBenefits','HIPPAA;SOX','email@example.com','9/1/14 7:15 AM','9/8/14 6:30 AM','HR server')" Invoke-Sqlcmd -Database PeteTest -ServerInstance usquchkpa7.database.windows.net -Username pzerger -Password P@ssw0rd1! -OutputSqlErrors $True -Query $TableData
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.)
And if I wanted to update an existing record (an UPDATE query), my command would look like this example:
Invoke-Sqlcmd -Database DSCSource -ServerInstance lmeucs55p7.database.windows.net -Username corpadmin -Password P@ssw0rd -OutputSqlErrors $True -Query "UPDATE DSCTracking SET Applications = 'FourthCoffee;PetShop', DateUpdated = '2014-09-15 06:45:00.000' WHERE ServerName = 'corpweb1.contoso.com' AND DSCGuid = '79ad26df-4f09-4526-82e1-a849bcb123ae'"
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:
Invoke-Sqlcmd -Database DSCSource -ServerInstance lmeucs55p7.database.windows.net -Username corpadmin -Password P@ssw0rd -OutputSqlErrors $True -Query "select * from DSCTracking where ServerName = 'corpweb1.contoso.com' AND DSCGuid = '79ad26df-4f09-4526-82e1-a849bcb123ae'"
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:
Invoke-Sqlcmd -Database DSCSource -ServerInstance lmeucs55p7.database.windows.net -Username corpadmin -Password P@ssw0rd -OutputSqlErrors $True -Query "select * from DSCTracking where Roles LIKE '%web' AND Applications LIKE '%HRBenefits%'"
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.
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.
To see the previous installments in this series, visit “100 Days of DevOps with PowerShell”.