Azure Synapse Notebook variable & parameter language context

While designing a data platform using Azure Synapse (or Azure Data Factory) it is quite common to want to use variables and/or parameters to control various aspects of your code execution. One common example of this is parameterizing your pipelines for environment configuration control, that is to distinguish between Dev, Test, Prod.

There are many articles already about how to parameterize an Azure Notebook, so I won't rehash that in detail. Here is my favourite for reference:

• https://docs.microsoft.com/en-US/Azure/synapse-analytics/synapse-notebook-activity?tabs=classical#passing-parameters
• https://microsoft-bitools.blogspot.com/2021/11/synapse-pipeline-pass-parameter-to.html

What isn't well documented is the Notebook default language will be the language context that the parameter is set in. Regardless of if you use a language magic command in your Parameterized Cell.

It makes sense if you stop and think about it. It can catch you out though if your Notebooks require multiple languages to complete their tasks. For example, let's assume this scenario.

Scenario: To interact with ADLS and other sources you use PySpark, but for creating data views etc you prefer Spark SQL due to language familiarity (TSQL), code formating, and maybe code migration. Due to the bulk of your code cells being Spark SQL that is the language you have set the Notebook to, therefore your PySpark cells use the %%pyspark magic command.

My pipeline has a parameter

The Notebook activity is passing that parameter through via dynamic values

The notebook is set to Spark SQL as the default language

The following screenshots have used the pipeline execution and notebook snapshot to trace the output/state of each step and highlight what is happening:

We can see the Parameterized Cell defined in PySpark code and then the Runtime Parameter being passed in and set in Spark SQL

Using some simple output commands in those respective languages we trace the current values.

This shows that the PySpark variable defined in our Parameterized Cell was not replaced and actually carries the value of 'dev'. The parameter passed into the Notebook was set in Spark SQL and the output of that language correctly has the value 'prod'.

That's not necessarily a problem, except if we need to reference the Parameter value from PySpark (or another language). We can recast the value from Spark SQL to PySpark with some simple code.

%%pyspark

param_df = spark.sql('SELECT ${env_selection}')

env_selection = param_df.columns[0]

Now we can see the Print function in our PySpark code return the correct value from the Parameter.

This behavior also applies to any variables you define within a specific language via the magic command.

Obviously, the right outcome here is to really think about what your Notebook's default language will be set to based on the requirements and behaviors. As I stated above though there are times you may need to use multiple languages and pass variable/parameter values between languages to achieve some functionality.

Convert CSV files to Parquet using Azure HDInsight

A recent project I have worked on was using CSV files as part of an ETL process from on-premises to Azure and to improve performance further down the stream we wanted to convert the files to Parquet format (with the intent that eventually they would be generated in that format). I couldn't find a current guide for stepping through that process using Azure HDInsight so this post will provide that.

Scripts and what samples used in this guide are available https://github.com/Matticusau/SQLDemos/tree/master/HDInsightConvertCSVtoParquet

To follow this blog post make sure you have:

1. Create a resource group in your Azure Subscription
2. Create a Storage Account within the resource group
3. Create an Azure HDInsight resource the same resource group (you can use that storage account for HDInsight)
4. Upload the sample GZip compressed CSV files from the SampleData folder to the Storage Account using Azure Storage Explorer. In my case I uploaded to a container "DataLoad"

The work that we will perform will be within the Jupiter Notebook. 

From your Azure Portal locate the HDInsight resource, click the Cluster dashboard quick link

Now select the Jupiter Notebook

This will open a new tab/window.

Authenticate as the cluster administrator.

Create a new PySpark Notebook.

Paste the following lines and press Shift+Enter to run the cell.

from pyspark.sql import *

from pyspark.sql.types import *

Now we can import the CSV into a table. You will need to adjust the path to represent your storage account, container and file. The syntax of the storage path is wasb://mycontainer@myaccount.blob.core.windows.net/foldername/filename

# import the COMPRESSED data

csvFile = spark.read.csv('wasb://dataload@mlbigdatastoracc.blob.core.windows.net/SalesSample_big.csv.gz', header=True, inferSchema=True)

csvFile.write.saveAsTable("salessample_big")

Press Shift+Enter to run the cell

Once complete you can use the SQL language to query the table you imported the data to. This will create a dataframe to host the output as we will use this to write the parquet file.

dftable = spark.sql("SELECT * FROM salessample_big")

dftable.show()

The final step is to export the dataframe to a parquet file. We will also use the gzip compression.

dftable.write.parquet('wasb://dataload@mlbigdatastoracc.blob.core.windows.net/SalesSample2.parquet',None, None , "gzip")

The complete Jupiter Notebook should look like:

In your storage account you should have a Parquet export of the data (note that this format is not a single file as shown by the file, folder and child files in the following screen shots.

In this example you may notice that the compressed file sizes are not much different, yet the parquet file is slightly more efficient. You experience may vary as it depends on the content within the CSV file.

Some reference material worth checking out if this is something you are working on:

https://docs.microsoft.com/en-us/azure/hdinsight/spark/apache-spark-jupyter-spark-sql-use-portal

https://spark.apache.org/docs/latest/api/python/

https://spark.apache.org/docs/latest/api/python/pyspark.sql.html#pyspark.sql.DataFrame

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Checklist for troubleshooting compilation of DSC Configuration in Azure Automation

I was recently working on a solution where a DSC Configuration block was not compiling in Azure Automation. This solution included Node Data, DSC Resources, and custom Composite Resources. The configuration would compile perfectly fine locally but not when published to Azure Automation. The other challenging aspect was that other composite resources within the same module were compiling fine.

Unfortunately Azure Automation doesn’t provide very detailed information for troubleshooting DSC Compilation errors. It actually will only show you the first level failure, which when your using a DSC Composite Resource it means you will simply receive an error that the composite resource failed to import, but the actual cause could be related to a problem with an underlying resource used by that composite resource.

So based on my experience I have come up with the following troubleshooting checklist when working through DSC Compilation errors in Azure Automation.

Troubleshooting Checklist

1. Check the exception details output by the DSC Configuration compilation job that is in the suspended state. Either within the Azure Portal like the following screen shot or via PowerShell by the Get-AzureRmAutomationDscCompilationJobOutput cmdlet.
   
   
   
   Depending on the exception reported the next steps may vary. In the above screenshot it is reporting that a Composite Resource has failed to import.
2. Can you compile the configuration locally?
1. If yes, can you upload the MOF to the DSC Node Configurations in the Azure Automation account?
3. Are all required Modules referenced by your Configuration(s):
1. Uploaded to your Azure Automation account
2. Up-to-date (see next point though)
3. Match the required version by your Configuration block or Composite Resource
4. If it is a Composite resource that is failing, are all Composite resources within your module affected or is it just a subset?
5. If it is a Composite Resource, extract the configuration from the failing Composite Resource and place it directly in a Configuration block. Compile that configuration block in Azure Automation and review the output as this will provide more granular details about the specific resources used by that configuration block.
6. Try simplifying the DSC Configuration block to reduce the number of DSC Composite resources or other resources being compiled to help narrow down the culprit

You should also read the "Common errors when working with Desired State Configuration (DSC)" section in the official documentation https://docs.microsoft.com/en-us/azure/automation/automation-troubleshooting-automation-errors

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Why VSCode has replaced Management Studio as my default SQL Database and Query editor

Firstly let me start by stating that when I originally set out in an IT career I was heading down a developer path, and certainly had a number of developer type roles over the years, or found ways of continuing development projects while working in infrastructure roles..... probably why I have an interest in DevOps. So taking that into account it's no surprise that for my entire career I have always been comfortable working in code and not relying on GUIs. Even for all the years as a SQL DBA armed with SQL Management Studio (SSMS), yet I was always most comfortable working in TSQL rather than the wizards. Probably comes from the days of Enterprise Manager and Query Analyser (ahhhhh nostalgia). Now the MS Product Team has done a great job at improving the wizards in SSMS and making tasks as easy as they can be in the tools. I will also state that this post is by no means saying SSMS is dead because there are just some things where it is better positioned.

What I will cover in this blog post is why my go to TSQL editor and tool for general database work is now VSCode with the MSSQL extension.

Please don't take this as a statement that I have now uninstalled SSMS or Visual Studio with SQL Data Tools (SSDT) from my laptop, I wish, but I have always found those tool a bit bloated with memory consumption when all I want to do is connect to a database, run some queries, or make some basic changes. What I will show is why/how I now perform those tasks with VS Code, but for anything more in depth like designing SSIS packages or performance troubleshooting I still rely on the existing tools (for now).

Another factor that is driving this adoption of a text based editor is that a large amount of my work is now with Azure and other cloud solutions, and for the majority of the work you need to do it is largely console or script based.

Now that you know why I have arrived at this place, lets get into how I setup and use VS Code for this purpose. I look forward to healthy discussions with people around this because I am not a believer of the "one size fits all" approach to a tool set either so it is always great to hear what others use.


Setup and configure your environment
Here are the steps I use to setup my VSCode environment:

1. Download and install VSCode https://code.visualstudio.com/download
2. Open VS Code
3. Press Ctrl+Shift+X (on windows)
1. Alternatively use the View > Extensions menu item
4. Locate and install the following extensions
1. vscode-icons
2. mssql
3. powershell
4. c#
5. Configure the extensions
1. From File > Preferences > File Icon Theme select "VSCode Icons"
   This will ensure that any files you open and access have nicely displayed icons to make your experience easier.
6. Configure the environment settings
1. From File > Preferences > Settings
1. VS Code works in two setting modes, User and Workspace. User should be personal preferences and Workspace should be used for project specific settings that will ship with the repo.
   User settings are stored in the file C:\Users\\AppData\Roaming\Code\User\settings.json but you shouldn't have to edit that manually as the VS Code window provides the best method for working with these files.
2. I don't change too many settings at this time from the default, but some to consider depending on your needs are:
   mssql.splitPaneSelection = "current|next|end"
   
   IntelliSense will help you complete the values if you need to see what is available.
7. Now you should be ready to start working inside VS Code. However, I recommend reading the release notes when new updates are made as the developer community is extremely active improving VS Code and there is always new and useful features being added.

While VSCode has a built-in integrated terminal, I like the cmder tool for my terminal use. If you aren't familiar with cmder check it out, very versatile, run multiple terminals and languages. Best of all a Quake mode. 

http://cmder.net

For more info check out my post on it http://blog.matticus.net/2017/03/cmder-enhance-your-windows-terminal.html

Connecting to a database and executing SQL queries
There are many tricks and ways to work within VS Code but here is a simply walk through on the basics to get you started.

1. Open VS Code if you haven't already
2. You do not need to open a folder or save files just to run queries but it could be beneficial. Think of a folder like a Project/Solution, but in a simplier (faster) format. This works great with Git and cross platform collaboration.
   For the case of this walk through just create a new file (click New File on the welcome page)
3. Without saving the file, lets make sure we are in the right language mode.
   
   Click the current language in the tray menu (e.g. Plain Text)
   
   
   
   This will open the command palette with prompts to select the language. Either browse or type to find your language and select it.
   
   
   
   Now the correct SQL language is shown in the tray menu
   
    
   
   Now the color coding and formatting, along with IntelliSense, will be suitable for SQL Server development.
   
   TIP: When you save a file then the language mode is automatically detected based on the file extention.
4. Press Ctrl+Shift+P to open the Command Palette
5. Type "mssql" and select the mssql: Connect option or press Ctrl+Shift+C
   
   
   
   TIP: Make sure your focus is in a file with the SQL language set and not any other areas of VSCode when you press Ctrl+Shift+C as otherwise it will open a console as per those keyboard shortcuts default.
6. Select an existing connection profile or select the Create Connection Profile to create a new one. So lets create one.
7. Follow the wizard filling out your server/instance, database (optional), authentication etc.
   
   
   
   
   
   
   
   Once you start to connect the status is shown in the tray menu
   
   
   
   Any errors connecting will be shown with an overlay
   
   
   
   Once connected VS Code will update intellisense dictionary and perform other operations set by the extension.
8. Now write your query in the file
9. When ready you can execute the query in a few methods
   
   Use the Command Palette and the MSSQL: Execute Query command.
   
   
   
   Right click in the editor and select
   
   
   
   or my favorite just simply press Ctrl+Shift+E
10. The query results tab will open. By default this opens in a new split window column, or the next one if you have multiples. The idea here is so you can see the query and result all in one window.
    
    
    
    You can put the query results at the bottom of the screen which might be a more familiar view to those use to SSMS. To do this select the Toggle Editor Group Layout from the View menu, or press Alt+Shiftt+1.
    
    
    
    Now the results are below the query you executed.
    
    
    
    Alternatively you can also set the query results to display in the current split window column (e.g. new tab)
    
    
    
    So as you can see you can customise where the results are displayed just like in SSMS.
    
    Something to keep in mind is that a new result tab will open for every file you execute a query from, but if you re-run a query or a new query from the same file then it will use the existing results tab for that file.
11. Now just like the query editor in SSMS, it will either execute the entire file contents or what you have selected. So like in this example it will just execute the selected query and not the entire file contents.
    
    
    
    This is why I like the keyboard shortcut Ctrl+Shift+E to execute queries because it becomes really quick to work from a file and execute different selected queries as desired.

Obviously some people will really miss Object Explorer to understand the schema's of databases they are not familiar with, but keep in mind VSCode is designed for developers and so typically you would have a folder that contains all the scripts for creating the database and therefore your schema to refer to, or you would be familiar with the schema. However, as we all know there are plenty of views you can easily query to get that data (because after all that is all Object Explorer does). 

Happy SQL Scripting.

Registering your SQL Server connections

VS Code has a json based configuration system. SQL Connections can be saved in the User Settings file, think along the lines of "Registered Servers" in SSMS. I have already briefly touched on how to create a new profile when you connect. However here is how to register them ahead of time and manage existing connection profiles.

Keep in mind though, these connections are not unique to a project/solution/folder, they are unique to your user settings. So you make sure you give them meaningful names to easily identify which databases/projects they belong to.

1. Press Ctrl+Shift+P to open the Command Palette
2. Type "mssql" and select the MSSQL: Manage Connection Profiles option
   
   
3. The Command Palette will then prompt you with some more options.
   
   
   
   Create: This will walk you through creating a new profile via the Command Palette prompts
   Edit: This will open the User Settings JSON file and allow you to manually edit the connection profiles. NOTE: Passwords can be saved in an encrypted form but are not stored in this file for security.
   Remove: This will walk you through removing an existing profile via the Command Palette prompts
   
   This is an example of the JSON configuration provided with the Edit option.
   
   
4. Once you have configured the profile you can then simply select it from the list provided under the MSSQL: Connect command.

References

VS Code official site https://code.visualstudio.com/
VS Code opensource repo  https://github.com/Microsoft/vscode

VS Code Extensions https://marketplace.visualstudio.com/VSCode
Cmder http://cmder.net/

Legal Stuff: The contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Latest SSMS stream lines the process for adding Azure Firewall Rules at connection

If you haven't heard, Microsoft have broken the release of SQL Management Studio away from the main product. While at this stage it is continuing to be released at the same cadence as the SQL 2016 CTP 2 cycle (e.g. Monthly) the updates are targeting features for existing releases, Azure SQL, and of cause SQL 2016. You can get the latest release @ http://aka.ms/ssms.

I have been working on a project of late with an Azure SQL DB back-end and one thing that has always frustrated me given my role keeps me out in the field is that I am constantly having to log into the portal and update the firewall rules....... Well the latest release of SSMS makes this process much easier.

Now when you try and connect to an Azure SQL Server and a firewall rule on the server blocks your connection, you are no longer prompted with the message informing you that the client is not allowed to connect. Now you are prompted with a dialog asking you to log into Azure and create a firewall rule. It even populates the client IP Address for you and suggests an IP range.

One you authenticate, select the appropriate option for the Firewall Rule (e.g. static or IP range). Click OK and SSMS will place a call to the Azure web services to add the firewall rule.

Once the rule is added, the authentication process continues which is a nice touch because I was half expecting to be prompted to log in again.

 

 

 Obviously for this to work the user then needs the required permissions on the subscription

This is a great example of how the team is actively working on improving the toolsets and responding to feedback so make sure you keep the connect items rolling in.

In other news, I am presenting with a colleague at MS Ignite Australia next month on some of the new toolset features in SQL 2016. If your attending make sure you attend and come say hi as I will be around the Data Den and Exhibition hall throughout the event.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Azure: How to utilise the Primary and Secondary storage access keys

One topic that comes up a bit when people first start to deal with Microsoft Azure storage is "what is the purpose of the Primary and Secondary storage access keys". The good news is that the guidance/documentation provided for Azure services and features is very rich in depth, and even includes screenshots. I've linked to the best documentation below.

The main purpose of the Primary and Secondary keys (and this doesn't just apply to storage resources), is to minimize application impact when the keys need to be regenerated. In fact the quote from Azure documentation is: “By providing two storage access keys, Azure enables you to regenerate the keys with no interruption to your storage service or access to that service.”

Lets take the scenario of a trusted employee has left the organization. This employee was either the Azure Account Administrator, a Developer, or for whatever reason had access to one or both of the Storage Access Keys for a critical storage blob on your Azure account. You also have a critical online application hanging off this blob storage where downtime costs you $$$$.

With only a Single Access Key the process would have to be the following which depending on the time between steps 1 and 4 could be a considerable outage for the application.

1. Stop the online application
2. Regenerate the Access Key
3. Update the application configuration
4. Start the application

Maybe in the above scenario you don't even have the ability to stop the application, then the impact of this becomes even more sever because your users will receive an error as the application code fails to use the out of date key.

Now with the Primary and Secondary Access Keys the scenario has a few more steps but far less if not zero outage to the application.

1. Regenerate the Secondary Access Key.
   This is important as the employee may have had access to this key as well and if they left under "bad terms" this may make this even more critical. Additionally you should regenerate keys routinely, so by doing this first you can test that the the secondary key works and should you have any issues then immediately stop this process and revert to the existing Primary Key while investigating what failed in your application.
2. Update the Application Code to use the new Secondary Access Key
3. Pause, take a breath….
   Give your some some time depending on your application to ensure all active processes have finished and new processes switched over to the new key.
4. Check your application is working off the Secondary Key and is successful. If not then you might want to revert to the existing key while you find the other places you need to update the Storage Account details.
5. Regenerate the Primary Access Key.
6. Update the Application Code to use the new Primary Access Key
7. Repeat steps 3 and 4

The best document to review regarding this topic is: http://azure.microsoft.com/en-us/documentation/articles/storage-create-storage-account/


There are some caveats when dealing with keys on storage for Virtual Machines, Media Services, and Applications (but I cover that above). For the full details see the above link but these are the extracts for reference:

Virtual Machines - If your storage account contains any virtual machines that are running, you will have to redeploy all virtual machines after you regenerate the access keys. To avoid redeployment, shut down the virtual machines before you regenerate the access keys.

Media services - If you have media services dependent on your storage account, you must re-sync the access keys with your media service after you regenerate the keys.

My suggested best practices

1. Routinely regenerate your keys so you can more easily track who has access to the environment
1. Do NOT regenerate keys on storage holding virtual machines, unless this can be factored into your monthly/quarterly patch maintenance
2. Use separate Storage Accounts for Virtual Machines and other application storage (e.g. Tables, etc).
3. Run the application off the Primary key, and only the Secondary key during key regeneration.
4. If you must provide access to the storage to an employee/developer/etc..... use a Shared Access key.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Importing SQLPS module results in Warnings

Update (June 2015): After originally posting this back in Nov, and debugging the issue earlier in the year I finally got around to recording my findings and updating this blog post. Hopefully this post also provides some insight into methods for Debugging PowerShell when faced with Provider issues.

I've been meaning to blog about this for a while now. If you do any work with PowerShell, SQL Server and Azure then chances are you have noticed that when you import the SQL module it reports the errors:

WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' failed with the following error: The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml' failed with the following error: The RPC server is
unavailable. (Exception from HRESULT: 0x800706BA)
...
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on
'Microsoft.WindowsAzure.Commands.SqlDatabase.dll' failed with the following error: The RPC server is unavailable.
(Exception from HRESULT: 0x800706BA)
WARNING: The names of some imported commands from the module 'sqlps' include unapproved verbs that might make them less
 discoverable. To find the commands with unapproved verbs, run the Import-Module command again with the Verbose
parameter. For a list of approved verbs, type Get-Verb.

The following Modules are affected by the issues described within this blog.

Module	Version	Notes
SQLPS	1.0	SQL 2014 release
SQLASCMDLETS	1.0	SQL 2014 release
Azure	0.8.8	Azure SDK June 2014 Release

Firstly a warning, do not modify any of the files mentioned in this blog as I will be exploring the code which runs when you import a module, but these are signed and any modifications may break the module functionality.

People generally notice this error when they run:

Import-Module SQLPS

What happens when you run this command is PowerShell loads up the Module Mainfest C:\Program Files (x86)\Microsoft SQL Server\120\Tools\PowerShell\Modules\SQLPS\SQLPS.PSD1


This references a number of other code bases.

Firstly a number of nested modules, most significantly both "Microsoft.SqlServer.Management.PSSnapins.dll" and "Microsoft.SqlServer.Management.PSProvider.dll" which load up the SQLPS provider and set the PSDrive path to the path of PS SQLSERVER:>

It also loads up the script file SqlPsPostScript.PS1 in the same path..... It is the code in this script where the warning is generated from (but this is not the cause).

This script has a step which is designed to import the SQLASCmdlets module and it is this step which is throwing the warnings. However, this isn't really to do with the loading of the SQL module but due to the Get-Module command which is executed.

#Load the SQLAS Module
$m = Get-Module -ListAvailable | where {$_.Name -eq "SQLASCmdlets"}
if($m -ne $null) { Import-Module $m -Global }

After a bit of troubleshooting I found that the problem even happens if you just run Get-Module -ListAvailable from within the SQLPS provider.

However if you run Get-Module -ListAvailable from another Provider (e.g. FileSystem) this does not return the error.

So the culprit is within the way the SQLPS Provider (via the PSDrive) implements standard cmdlets. You can see this even with Import-Module cmdlet when it specifically tries to work with the Azure module when the path is set to SQLSERVER:\.

Now typically this is where most would get stuck as the SqlPs and Azure modules are Binary Modules (aka it loads .dlls) so the general public cannot dig into the code.

However, here we can leverage PowerShell debugging to go deep into the code stack. So the actions that I took next were:

1) Use the -Debug switch parameter on the Import-Module

Import-Module -Name SqlPs -Debug

2) Use Trace-Command

Trace-Command -Expression {Import-Module SQLPS} -FilePath C:\ImportModuleTraceCommand.txt -Name * -Option All

Using the output captured by these commands we can actually trace the problem down further in the stack.

Firstly by looking at the Debug output we can see the following function calls just before the WMI errors.

---------------------------------------------------------------------------
DEBUG: ItemExists: SQLSERVER:\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
DEBUG: ItemExists: SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
WARNING: Could not obtain SQL Server Service information. An attempt to connect to WMI on 'Microsoft.WindowsAzure.Commands.S
qlDatabase.Types.ps1xml' failed with the following error: The RPC server is unavailable. (Exception from HRESULT: 0x800706BA
)
---------------------------------------------------------------------------

This tells us that the module loading is calling the ItemExists function to test a path for a file it is told to load (and we already know that the path is coming from the definition of the Azure module).

Next we can look at the Trace-Command data.

If you compare the logical code variations between the two extracts you can clearly see that the first resolves the path to the FILESYSTEM provider but the second resolves the path to the SQLSERVER provider. If we look even more closely we see a function call output by "Path is DRIVE-QUALIFIED" which returns true on the first and false on the second, and because it returns false on the second it then sets the SQLPROVIDER to go checking for the paths off the base drive which in this case is SQLSERVER:, and eventually fails with "ERROR: Item does not exist".


Successful loading of types file:

PathResolution Information: 0 :                                                     Resolving MSH path "C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml" to MSH path
...
PathResolution Information: 0 :                                                         Path is DRIVE-QUALIFIED
LocationGlobber Information: 0 :  WriteLine                                                           result = True
LocationGlobber Information: 0 :  WriteLine                                                           Drive Name: C
SessionState Information: 0 :  WriteLine                                                           Drive found in scope 1
LocationGlobber Information: 0 :  WriteLine                                                           path = Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           result=Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           result=C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
SessionState Information: 0 :  WriteLine                                                           result = C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\.\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                           basePath = C:\
...
PathResolution Information: 0 :                                                         DRIVE-RELATIVE path: Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml
PathResolution Information: 0 :                                                         Drive: C
PathResolution Information: 0 :                                                         Provider: Microsoft.PowerShell.Core\FileSystem
...
PathResolution Information: 0 :                                                         RESOLVED PATH: C:\Program Files (x86)\Microsoft SDKs\Azure\PowerShell\ServiceManagement\Azure\Services\Microsoft.WindowsAzure.Commands.Websites.Types.ps1xml




Unsuccessful loading of types file:
PathResolution Information: 0 :                                                 Resolving MSH path ".\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml" to PROVIDER-INTERNAL path
...
PathResolution Information: 0 :                                                     Path is DRIVE-QUALIFIED
LocationGlobber Information: 0 :  WriteLine                                                       result = False
LocationGlobber Information: 0 :  WriteLine                                                       path = Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       result=Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       result=SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
SessionState Information: 0 :  WriteLine                                                       result = SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
CmdletProviderClasses Information: 0 :  WriteLine                                                       basePath = SQLSERVER:\
...
PathResolution Information: 0 :                                                     DRIVE-RELATIVE path: Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
PathResolution Information: 0 :                                                     Drive: SQLSERVER
PathResolution Information: 0 :                                                     Provider: Microsoft.SqlServer.Management.PSProvider\SqlServer
...
PathResolution Information: 0 :                                                     RESOLVED PATH: SQLSERVER:\Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml
...
PathResolution Information: 0 :                                                             ERROR: Item does not exist: Sql\Microsoft.WindowsAzure.Commands.SqlDatabase.Types.ps1xml




At this point I new there was something different happening in the implementation of a PathResolution process.

Taking in everything I had investigated I could conclude that the SQLPROVIDER was implementing the ItemExist function differently to the FileSystem provider. In particular the SQLPROVIDER was checking the given file path and trying to match it to the SQLSERVER: drive, and in this case because the next part of the path starts with "\Sql\....." this would cause it to think it is trying to get the path of a SQL Server in which it would then attempt to connect to the name (in our case the file name) as a Server Name to retrieve the instance details...... hence the WMI errors.

To the best of my knowledge this issue has been around since an update to the Azure module in early 2014, as they change the folder structure they were using. At the time of writing this post, both initially and subsequent updates, the problem still exists.

If you want to help get this resolved please vote on the MS Connect item: https://connect.microsoft.com/SQLServer/feedback/details/1420992/import-module-sqlps-may-take-longer-to-load-but-always-returns-warnings-when-the-azure-powershell-module-is-also-installed#



 Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Troubleshooting blocking locks in SQL Azure

I was faced with an interesting situation yesterday where a customer had created a blocking chain on their SQL Azure database. The scenario they described was:

A query took out and held a lock on object but the client connection closed and orphaned the session on the SQL Azure database. This resulted in other queries trying to access the object being blocked and timing out (even a SELECT *).

Given we do not have any graphical tools in SSMS to see current activity when you connect to a SQL Azure database, this offers a challenge for those that are use to going to Activity Monitor or the Reports to view this sort of information. Thankfully most of DMV's and DMF's have made their way into support on SQL Azure.

To troubleshoot the above scenario we then used the following queries to identify the culprit and eventually terminate it with the only option available.... KILL [spid]. To demonstrate this I have created the scenario on my Azure database through the use of PowerShell (but i am not sharing that as it is bad practice). The good news is that these queries will also work for your on-premise environments.

TSQL to identify the Blockers and Victims

WITH Blockers AS
    (select DISTINCT blocking_session_id as session_id
 from sys.dm_exec_requests
 where blocking_session_id > 0
)
SELECT 'Blocker' as type_desc
 , sys.dm_exec_sessions.session_id
 , sys.dm_exec_requests.start_time
 , sys.dm_exec_requests.status
 , sys.dm_exec_requests.command
 , sys.dm_exec_requests.wait_type
 , sys.dm_exec_requests.wait_time
 , sys.dm_exec_requests.blocking_session_id
 , '' AS stmt_text
FROM sys.dm_exec_sessions
LEFT JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
INNER JOIN Blockers ON Blockers.session_id = sys.dm_exec_sessions.session_id
UNION
SELECT 'Victim' as type_desc
 , sys.dm_exec_sessions.session_id
 , sys.dm_exec_requests.start_time
 , sys.dm_exec_requests.status
 , sys.dm_exec_requests.command
 , sys.dm_exec_requests.wait_type
 , sys.dm_exec_requests.wait_time
 , sys.dm_exec_requests.blocking_session_id
 , ST.text AS stmt_text
FROM sys.dm_exec_sessions
INNER JOIN sys.dm_exec_requests ON sys.dm_exec_requests.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sys.dm_exec_requests.sql_handle) AS ST
WHERE blocking_session_id > 0

The output of this query looks then like the following where you can clearly see the blocker and it's victims (in fact this example then has a 2nd layer of blocking).

TSQL to view the established locks within the current database

SELECT
 (CASE sys.dm_tran_locks.request_session_id
  WHEN -2 THEN 'ORPHANED DISTRIBUTED TRANSACTION'
  WHEN -3 THEN 'DEFERRED RECOVERY TRANSACTION'
  ELSE sys.dm_tran_locks.request_session_id
 END) AS session_id
 , DB_NAME(sys.dm_tran_locks.resource_database_id) AS database_name
 , sys.objects.name AS locked_obj_name
 , sys.dm_tran_locks.resource_type AS locked_resource
 , sys.dm_tran_locks.request_mode AS lock_type
 , ST.text AS stmt_text
 , sys.dm_exec_sessions.login_name AS login_name
 , sys.dm_exec_sessions.host_name AS host_name
 , sys.dm_tran_locks.request_status as request_status
FROM sys.dm_tran_locks
JOIN sys.objects ON sys.objects.object_id = sys.dm_tran_locks.resource_associated_entity_id
JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.dm_exec_connections ON sys.dm_exec_connections.session_id = sys.dm_exec_sessions.session_id
CROSS APPLY SYS.DM_EXEC_SQL_TEXT(sys.dm_exec_connections.most_recent_sql_handle) AS st
WHERE sys.dm_tran_locks.resource_database_id = DB_ID()
ORDER BY sys.dm_tran_locks.request_session_id

The output of this query shows the various locks which have been established by each session (one-to-many in sessions-to-locks). From here we can see which locks will have taken priority and potentially blocking more queries in the future.

So while you may not have a graphical view of this information we can definitely use our trusted DMV's and DMF's to gain access to the relevant data. Who knows with the rate of change it can surely only be a matter of time until Activity Monitor and the standard reports are available to us in SSMS for SQL Azure.

Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.

Azure CmdLets which do not support ErrorAction WarningAction behaviour

While working on some scripts for an upcoming presentation I noticed some behavioral changes in the Azure CmdLets particularly with the use of the ErrorAction and WarningAction common parameters.  In my case I was using the Add-DomainControllerAndMemberServer.ps1 (available here) and found that it uses the -ErrorAction parameter for error handling when testing if azure services are already provisioned.

What i found is that the Get-AzureVNetSize cmdlet still throws an error when the Virtual Network does not exist even if you supply the ErrorAction parameter.

So running this:

Get-AzureVNetSite -VNetName "mlaveryvnet"

Or this:

Get-AzureVNetSite -VNetName "mlaveryvnet" -ErrorAction SilentlyContinue

Still results in the following error when the virtual network does not exist:

Get-AzureVNetSite : The specified virtual network name was not found: mlaveryvnet
Parameter name: VirtualNetworkName
At line:1 char:9
+ $vNet = Get-AzureVNetSite -VNetName "mlaveryvnet"
+         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [Get-AzureVNetSite], ArgumentException
    + FullyQualifiedErrorId : System.ArgumentException,Microsoft.WindowsAzure.Commands.ServiceManagement.IaaS.GetAzureVNetSiteCommand

Something to be aware of because even though the cmdlet may take the parameters from the command line, it doesn't mean it has been programed to include that functionality.

In my case I had to switch to the Get-AzureVNetConnection cmdlet as it does provide the functionality and as all I was doing was testing if the network exists this works. Your particular case may differ.

Get-AzureVNetConnection -VNetName "mlaveryvnet" -ErrorAction SilentlyContinue

Infrastructure Saturday 2013 - PowerShell for Azure..... a perfect partnership

The last time I presented at Infrastructure Saturday was 2010 and it is certainly great to see how the event has continued and grown. A huge thanks to everyone from contributors, sponsors, speakers and organizers, it was clearly appreciated by everyone.

So this year my original session on Automating SQLwith PowerShell had to be squashed to allow Heidi (WardyIT) to present on SQL. After shedding a tear for only a short moment I regrouped and decided to talk about PowerShell and Windows Azure, another passion of mine. So a massive thanks goes to everyone that chose my PowerShell session over the SQL Session because naturally we had to be scheduled at the same time so that I was then competing against my own primary skill set... nah lets be honest PowerShell is overtaking SQL in that way for me ;)

Ok ok enough banter.

A number of people have expressed interest in the scripts demonstrated during the session so I thought I would provide a walk-through recap of the session. The links to download the "cleansed" versions of the scripts is at the end of this post.

Session Key Takeaways

In my experience, the Azure Portal is a great tool for exploring new features or once off tasks, but due to some annoyances, like the lag for the portal to refresh when back end tasks complete, it can be time consuming when doing very large tasks or more specifically regular repeated tasks.

PowerShell overcomes this :)

Oh and one more takeaway, be careful what is a news headline at the time of your presentation otherwise you may have a docked picture of our PM and another guy kissing show on your Windows 8 Start Screen (it was quite comical though).

Links

http://www.windowsazure.com

SDKs: http://www.windowsazure.com/en-us/downloads/
Azure PowerShell: http://go.microsoft.com/?linkid=9811175&clcid=0x409
Scott Guthrie (ScottGu) Blog: http://weblogs.asp.net/scottgu

Session Demos

This is a walk through the PowerShell scripts that were demonstrated during the session.

For the first timers, you need to configure your PowerShell console session to be authorised to access your Azure subscription.

1. Download and install the Azure PowerShell pack. Reboot your computer to make it easier from here on out to import the module.
2. Import the Azure module with
   

   Import-Module Azure;
   

3. Next call the cmdlet to get the publish settings file. When you call this cmdlet it will launch your browser and ask you to log into your Azure subscription with your Live ID. It will then generate a new subscription management certificate and then prompt you to download the publishsettings file. Take note of the path where this file will be saved
   

   Get-AzurePublishSettingsFile;

   
   
4. Open the Publish Settings file and check that the Subscription ID matches the details of your management certificate as shown in the portal.
5. Next import the publishing settings file which will import the required certificate into your users certificate store.
   

   Import-AzurePublishSettingsFile –PublishSettingsFile "C:\Subscription-credentials.publishsettings"

6. You can verify that the certificate was imported correctly by traversing the certificate store:
   

   Get-ChildItem -Path Cert:\CurrentUser\my

   Or by retrieving a list of Azure Services available:
   

   Get-AzureService | Select ServiceName

Now that the management certificate has been imported you now need to bind to the Azure Subscription so that any subsequent cmdlets can authenticate without having to stipulate which subscription they should be executed against each time. This is very useful when you have more than one subscription within your organization.

1. Set the values in the variable block as required and then execute the script/command
   

   <#######################################################
   Variable Block
   #######################################################>
   
   # Subscription Information...
   $subscriptionName = "<<<<< Insert your Subscription Name here >>>>>"
   $subscriptionID = "<<<<< Insert your Subscription ID here >>>>>"
   $thumbPrint = "<<<<< Insert the Certificate Thumb Print here >>>>>"
   
   # Storage Account Information...
   $storageAccountName = "azurestorage" # Must be globally unique and all lowercase...
   $storageAccountLabel = "azurestorage"
   
   <#######################################################
   Select the Subscription
   #######################################################>
   
   #get the certificate definition from the certificate store via the PowerShell drive
   $certificate = Get-Item cert:\currentuser\my\$thumbPrint
   
   #set the context to the required subscription
   Set-AzureSubscription `
       -SubscriptionName $subscriptionName `
       -SubscriptionId $subscriptionID `
       -Certificate $certificate `
       -CurrentStorageAccount $storageAccountName
   
   #next mark the required subscription as active
   Select-AzureSubscription `
       -SubscriptionName $subscriptionName
   

TIP: The above could be configured as part of your PowerShell profile script to ensure this is set each time you start the Console, or added to a script file to easily execute, if working regularly with Azure through PowerShell.

Next we can look at the cmdlets available to us and other useful information available from within Azure:

1. Get all the cmdlets available from the Azure module

   Get-Command -Module Azure
   

2. Lets look further into the various commands available with
   

   #get all the "Get" commands
   Get-Command -Module Azure -Verb Get
   
   #get all the "Set" commands
   Get-Command -Module Azure -Verb Set
   
   #get all the "Restart" commands
   Get-Command -Module Azure -Verb Restart
   

3. When it comes time to deploy a VM we have the option of using some of the ready-made images, the following is an example of how to get a list of each of these:
   

   #Get the list of all windows images
   Get-AzureVMImage | where -property "OS" -EQ -Value "Windows" | select label,imagename | Sort -Property "label" | ft -AutoSize
   
   #what about all the images
   Get-AzureVMImage | where -property "OS" -NE -Value "Windows" | select label,imagename | Sort -Property "label" | ft -AutoSize
   

Platform as a Service

The first deployment demo during the session was to deploy a WebSite with a SQL Database. This demo used the Platform as a Service features of Azure. The script used for this deployment came from

1. Deploy the Web Site and SQL Database with the script by specifying the required parameters:
   

   #create the web site with a SQL Database (we have to use the location East Asia as Southeast Asia is not valid for web sites)
   .\New-AzureWebsitewithDB.ps1 `
       -WebSiteName "InfraSatWebSite" `
       -Location "East Asia" `
       -StorageAccountName $storageAccountName `
       -ClientIPAddress "***.***.***.***"; #validate this Client IP with WhatIsMyIP.com or the Azure Portal.
   

2. Once the deployment has completed we can see what was deployed. Firstly the new SQL Azure Server and Database

   #get the database server
   Get-AzureSqlDatabaseServer;
   
   #get the database(s) for the servers
   Get-AzureSqlDatabaseServer `
       | Get-AzureSqlDatabase;
   
   #get the database with extra info
   Get-AzureSqlDatabaseServer `
       | Get-AzureSqlDatabase -DatabaseName infrasatwebsite_db `
       | Select Name,CollationName,Edition,MaxSizeGB,Status,CreationDate `
       | Format-List;
   

3. We can also retrieve the information about the web sites created

   #get the website
   Get-AzureWebsite 

4. Thanks to the Azure management pack we can perform all sorts of operations on the services we created.

   #stop the web site
   Stop-AzureWebsite -Name InfraSatWebSite
   
   #start the web site
   Start-AzureWebsite -Name InfraSatWebSite
   
   #Default documents
   Get-AzureWebsite -Name "InfraSatWebSite" | Select DefaultDocuments;
   
   #Update the default document list for what we will publish
   Set-AzureWebsite -Name "InfraSatWebSite" -DefaultDocuments "default.aspx";
   

5. In my demo the next step here was to show how to deploy the web site code from Visual Studio through to the newly provisioned Web Site.

If you are testing this and wish to do some clean up of your Azure account following a deployment, the following commands are a good starting point.

#Remove the database (WARNING: This removes ALL databases provisioned on your account.... don't say I didn't warn you as there is no UNDO!)
Get-AzureSqlDatabaseServer | Remove-AzureSqlDatabaseServer;

#Remove the database (with a filter to exclude servers with a particular admin account)
Get-AzureSqlDatabaseServer | Where-Object -NE -Property AdministratorLogin -Value <adminaccountofserveryouwishtokeep> | Remove-AzureSqlDatabaseServer;

#remove the web site
Get-AzureWebsite -Name InfraSatWebSite | Remove-AzureWebsite;

#or
Remove-AzureWebsite -Name InfraSatWebSite;

#Check that it was removed
Get-AzureWebsite;

Infrastructure as a Service

During my session I demonstrated the automation of Infrastructure as a Service, unfortunately I cannot release the exact script that was demonstrated at the request of some colleagues until it is out of BETA. However there are plenty of scripts which perform some of the components of our complete solution.

The general concept of IaaS Automation is:

1. Deploy infrastructure components (e.g. Network, Storage, Virtual Machines, etc)
2. Simplify configuration and common tasks
3. Repetitive with minimal differences
4. Minimal user input (I recommend XML input files to achieve this)

Some existing example scripts that perform "some" of these tasks.

Deploy a Windows Azure Virtual Machine with Two Data Disks
http://gallery.technet.microsoft.com/scriptcenter/Create-a-Windows-Azure-ec901cbe

Deploy Windows Azure VMs to an Availability Set and Load Balanced on an Endpoint
http://gallery.technet.microsoft.com/scriptcenter/Create-Windows-Azure-VMs-244bd3cb

Deploy Multiple Windows Azure VMs in the Same Windows Azure Virtual Network
http://gallery.technet.microsoft.com/scriptcenter/Create-Multiple-Windows-df9e95b7

Deploy a SQL Server VM with Striped Disks in Windows Azure
http://gallery.technet.microsoft.com/scriptcenter/Create-a-SQL-Server-VM-in-0b8c6eed


At a time when the script demonstrated can be released I will update this post with references to that script.

PPT and Scripts Download

Get the PPT and the "cleansed" Scripts used during the Demos here.




Legal Stuff: As always the contents of this blog is provided “as-is”. The information, opinions and views expressed are those of the author and do not necessarily state or reflect those of any other company with affiliation to the products discussed. This includes any URLs or Tools. The author does not accept any responsibility from the use of the information or tools mentioned within this blog, and recommends adequate evaluation against your own requirements to measure suitability.