Category: Azure

Yes! It’s possible. Here’s the information in how to set it up and start doing some PowerShell scripting. But, first understand that everything posted here is still a Work-In-Progress. And, the good news, it’s all Open Source.

I hope you find the following information essential as there’s no really any instruction in how to install these components. So, let’s get started!

Where To Get It!

The Microsoft SQL Tools Service is a set of API that provided SQL Server Data Management capabilities on all system cross-platforms. It provide a small set for SMO dll’s enough to get started.

Windows Installation

You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

To make *”Sql Tools Services” to work in PowerShell Core, I had to extract all content in the file into the “C:\Program Files\PowerShell\6.0.0-Beta.x” folder. Remember, this will replace any existing DLL’s on that folder.

*Caution: This steps should be done on a test machine as there’s always a possibility that it could PowerShell Core DLL’s.

Don’t forget that all these components are still in development but this should stopped us from trying and even contributing.

The file you’ll need to download for Windows is: microsoft.sqltools.servicelayer-win-x64-netcoreapp2.0.zip

Please, for now ignore the *microsoft.sqltools.credentials*. If you install the Credentials DLL’s in the PowerShell Beta folder, PowerShell will not work.

Linux Installation

Now, for Linux is a different story as there’s no need to add the DLL’s in the PowerShell Core folder. You need to get the file from the latest release. At the time I writing this blog, it’s Pre-release “v1.0.0-alpha.34 – .Net Core 2.0 build“.

That’s it for Linux. Now, you are ready to work with SMO and PowerShell.

Testing SMO in PowerShell Core

This is changing my way I script SMO in PowerShell. As my normal way I’ve been scripting SMO in PowerShell doesn’t work in PowerShell Core. Basically, a few more lines need to be added and now I will use the Add-Type to get the SMO assemblies loaded.

Loading SMO Assemblies

The first step is to load the SMO assemblies needed to start working with SQL Server. So, the following line is finally depricated and won’t work:

The above assemblies are required in order to work since SQL Server SMO 2012 and greater. You can have limited use when connecting to SQL Servers version 2005, and possibly 2000.

Prepare connection parameters for Windows Systems

In Windows systems, we use ‘Integrated Authentication‘. But, here’s where things change a bit since SQL Server 2012 SMO. You will need to prepare the connection parameters, and set the *.UseIntegratedSecurity property to ‘true‘ (the default is ‘false‘). At the same time, you’ll need to set the password to ‘null’ in order to connect successfull.

Prepare connection parameters for Linux Systems

For Linux systems, we use ‘SQL Authentication’. Here we add the SQL User password, then passing the value to the SqlConnectionInfo class. And, the *.UseIntegratedSecurity property by the default is ‘false‘.

Now, my question here is, did you found Azure PowerShell 1.0 Preview? Probably not, but in fact, it’s AzureRM 1.0.1 to be installed. Although, I did found Azure version 0.9.11 and, Yes! I did download that one just in case.

In this presentation I will be covering how to subscribe to Azure, setup PowerShell to connect to your subscription, use scripting to create a SQL Database Server and then use SMO with PowerShell push data to your SQL Azure tables. All this using PowerShell scripting plus showing some editor, scripting techniques, and tips to avoid issues when working and setting Azure with PowerShell. (live demo with Windows Azure). Thanks to Adnan for assisting me in this meeting.

Azure SQL Database
1. Web and Business editions are no longer available. Now there’s Basic, Standard, and Enterprise editions. (New)
2. There is a limit of 6 SQL Database Servers and up to 150 databases per subscription.
3. Create database from 1 GB up to 500GB of storage.
4. Database Throughput Unit(DTU) Service performance levels available: (New)

In my previous blog I mention the latest version has two module. Well, I was wrong! It’s important that you take the time to read the documentation and pay attention to what’s trending in the social network (such as twitter). I did notice someone tweet about the new Azure module included in this last released: the “AzureResourceManager“. And here’s where the fun begin.

There are three modules:

Azure

AzureProfile

AzureResourceManager (Preview)

When executing the “Get-Module -ListAvailable” command you will notice that only 2 will show up: Azure and AzureProfile.

This new module is a “PREVIEW“, and the documentation states “The Azure and Azure Resource Management modules are not designed to be used in the same Windows PowerShell session. To make it easy to switch between them, we have added a new cmdlet, Switch-AzureMode.” Here’s the link to the documentation: http://msdn.microsoft.com/en-us/library/jj554330.aspx

This means that in order to use the new commands from the “AzureResourceManager” you need to run the “Switch-AzureMode” which will prevent you from using the Azure commands such as Get-AzureVM on the same PowerShell session. Now, keep in mind that you can always open another session to keep working with the Azure module commands.

PowerShell with module Autoload On

At the same time, If you need to use the command “Import-Module Azure“, you’ll notice that it will give an error telling that it can’t find the module. The trick here is, if you haven’t turned off the PowerShell Module Autoload option, the commands will be available. Here’s a TechNet link on how to Turn-Off the PowerShell Autoload module (not recommended): http://blogs.technet.com/b/heyscriptingguy/archive/2013/02/20/powertip-turn-off-powershell-module-autoload.aspx

Import-Module Azure Error

This is a bug that have been recently reported to the Microsoft Azure PowerShell team. By default, PowerShell have the Module Autoload “ON” and you will be able to list all the Azure module commands.

PowerShell Autoload Azure commands

So, you can still work with your Azure PowerShell commands and use the “Switch-AzureMode” on another session.

Now you can continue to work with PowerShell Azure command and check out is new (Preview) module AzureResourceManager.

I don’t know if you notice when you go to download the latest version of Windows Azure PowerShell cmdlets, the version number is no longer displayed on the Download page. As you already know, Azure PowerShell commands gets updated sporadically, so you need to periodically check if there are new updates. The download page was previously giving you the hint that there was an updated set of commands.

Last year Windows Azure Download page used to display the Azure PowerShell version which made it easy to check for the updated product:

Last year Windows Azure PowerShell version displayed

Currently, as of February 2014, the version number is Gone!

Windows Azure PowerShell version is Gone!

Well, here’s where PowerShell comes to the rescue.

The following quick code can query both you current version and check for the latest version available on the Internet. And, Yes! This code can be improved and taken a little further. But, this will give you a head start.

Get-WindowsAzurePowerShellVersionfunction:

Copy/Paste code in PowerShell Console.

View Results.

Notice the first portion of the code uses WMI “Win32_Product” Class which will take a few minutes to query your system for what’s installed. By a strong recommendation from my MVP college Aleksandar Nikolic I change the code from using the WMI Win32_Product class and instead use the Get-Module Version property. This way it will effectively provide the needed information.

Then, for the “Microsoft.Web.PlatformInstaller” piece, if you previously installed Windows Azure PowerShell (or any of the other options), this assembly will be already in your system.

Note: My college Aleksandar Nikolic suggested not to use Win32_Product. BAD IDEA!! Check the following links why not to use “Win32_Product“:

This blog describe the essentials to get you started with building Windows Azure Virtual Machines. This is slightly different from the previous blogs on Windows Azure SQL Database Servers. As a refresher, in order to use PowerShell with Windows Azure, you need to create and install a Certificate key. Then, it will enable PowerShell to work with Windows Azure commands.

So, after the Azure subscription has been activated and the certificate key uploaded to azure then PowerShell is all set. Then, remember to check periodically for PowerShell Azure module updates.

When working with Powershell, loading the “Azure” module is not required. As you typed and execute the command, PowerShell by default will autoload the module. This way there’s no need to use the “Import-Module xxxxx” command.

But, what’s the sequence for building an Azure VM using PowerShell? I’m presenting the way I’ve done it. This is just a suggestion. Please feel free to use this as a possible guideline.

Here’s the order of the samples scripts I’m providing for building an Azure VM:

When this command runs successfully, it will also create the both Primary and Secondary “Azure Storage Account” keys automatically.

At the same time, don’t forget to pick the Windows Azure location where the Storage Account is going to be create. To list all locations available use the “Get-AzureLocation” command.

## list of all Azure locations:
Get-AzureLocation

If there’s a need to list all Storage Account then use the “Get-AzureStorageAccount” command using the

## list of all Azure Storage Account(s):
Get-AzureStorageAccount

Reconnect to Azure with the “Current Storage Account”

After you got everything set for PowerShell to connect to Windows Azure then you need to create the “Storage Account “. There’s one noticeable difference between the Azure SQL and the Virtual machine. Azure Virtual Machines need a Storage Account. This is done using the “Set-AzureSubscription” with the “-ContainerStorageAccount” parameter.

This section help set the Azure Subscription to the “Current Storage Account” which maybe optional. I included this section because I started using my subscription to create only Azure SQL Database Server and I didn’t need any Storage Account.

Then, use the “Get-AzureSubscription” command to view all Azure Subscription values.

Search and Select the Windows Azure pre-built VM

For search the list of available Azure pre-built VM’s we use the “Get-AzureVMimage” command. Here’s one creative approach for searching and select the VM imagename using the PowerShell V3 enhanced “Out-GridView” command with the ‘-PassThru’parameter. By creating a PowerShell variable “$x” we can store the value select from the “Out-GridView” and pass it to the “New-AzureQuickVM” command to build the VM. Check the following example:

This way we can pick and choose the image. Then, we use the variable with the member property that holds the name: $x.ImageName. Remember to use the oneliner “$x | Get-Member” to view all variable member objects.

Create the Azure VM (Caution w/Service Name)

At the same time it will need to be provide a “Service Name”. The “New-AzureQuickVM” help documentation mention that this is either a new one or existing one. The following is an example of a new VM with a new ServiceName “MyPITcloudSvc2“:

Please keep in mind, that I haven’t discuss anything about the creating network items and/or affinity group to established connectivity between the Azure VMs. At least this information will help in getting started.

I hope you all find this information useful! There will be more coming soon.

As I venture into the realm of learning some PowerShell Automation in Windows Azure, its interesting the things you learn by just trying things out. On my previuos blogs I mention, in order to use PowerShell, you need to create and install the certificate in the portal. After that, you can use following commands to connect to Azure:

*Note: The ‘Import-Module Azure’ is more of a habit to do it. Powershell 3.0/4.0 will search and automatically load a module the first time the cmdlet is been executed.

I just realized, after the Certificates Keys are installed in Azure, then you don’t need to execute the above commands Set-AzureSubscriptionand Select-AzureSubscriptioneverytime I open the PowerShell Console. Yes! I can start typing away and work with Azure commands.

Just try it! If you already loaded the certificate keys, then Open a PowerShell console session and type “Get-AzureVMimage” to display the list of available Azure VM images:

If there’s no certificates installed, the you’ll get the following message: (on another PC)

So that you know, when working with Windows Azure SQL Database Server(s), you don’t need to set up a Storage (Container) Account nor a Azure Cloud Service. Definitely you will need them when working with Windows Azure VM’s.

Next, I will be blogging on “PowerShell working with Windows Azure VM’s”.

As I work on my second blog piece for the “Getting Ready with Windows Azure SQL Database Server PowerShell and SMO Part – 2/2“, I came up with a way to trap the current IP Address with PowerShell scripting. When using the Portal for creating your SQL Database Server, it will ask you if you want to create the Firewall rule for you. But you may want to automate this step using PowerShell and there’s no cmdlet to identify the “current” IP Address of your Windows Azure connection.

Here’s an example of how the Portal message when is asking for the current IP Address to be added to the Firewall rules:

I’m going right to the point with this small blog piece. Basically, I’m trapping the error message from the “New-AzureSqlDatabaseServerContext” which will fail to connect to your Azure SQL Database. Then, I’m dissecting the string to get the IP Address in error. This is my way of trapping the IP address. I know there might be a better way but for now it works.

I’m assuming the connection to Windows Azure has already been established and you are trying to use the “New-AzureSqlDatabaseServerContext” for connecting to the database. If you haven’t created the rule then it won’t connect.

In the “New-AzureSqlDatabaseServerContext” I’m including the following two parameters: -ErrorAction ‘SilentlyContinue’ and -ErrorVariable errConn. The “ErrorAction” results in not displaying the message. The “ErrorVariable” define the PowerShell variable you will be storing the error message. Notice the “ErrorVariable” name doesn’t include a “$” but its needed to view it (ie. $errConn).

The additional script code shown next will dissect the error message string from $errConn variable. It will take the string to create an array which will help identify the element position where the IP Address is stored. In this case I’m assuming the error message will not change so the IP Address will always be located in the same place (Right!). So the first time this code execute, it will find the IP Address in element #18.

Note: Please run first the code to identify the element position in case the “Culture” settings might change the location of the IP Address.

You can refine this script code to suit your need. Just make sure to test a few times and verify you are getting the results you need.

Here’s a few more commands you could use to work with these rules. The “Remove-AzureSqlDatabaseServerFirewallRule” to remove any existing rule(s) and the “Get-AzureSqlDatabaseServerFirewallRule” to list them all.