Azure PowerShell Preview 1.0 is here

Welcome to PowerShell Azure Resource Manager (RM)!

Microsoft has introduce and made available Azure PowerShell Preview 1.0.  Please read all about it in the following Azure Blog site:

Also, feel free to search for it at usign the following link:

Some Interesting links 

Well, within the search there are more interesting blog articles to start doing some PowerShell scripting:

How to install and configure Azure PowerShell: https://azure.microsoft.com/en-us/documentation/articles/powershell-install-configure/

Using Azure PowerShell with Azure Resource Manager: https://azure.microsoft.com/en-us/documentation/articles/powershell-azure-resource-manager/

Manage Azure SQL Database with PowerShell: https://azure.microsoft.com/en-us/documentation/articles/sql-database-command-line-tools/

Integrating SQL AlwaysOn with Azure Site Recovery: https://azure.microsoft.com/en-gb/blog/integrating-sql-alwayson-with-azure-site-recovery/

Get it at PowerShell Gallery

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.

To download these new bits from the PowerShell Gallery, check out the “Get Started with the PowerShell Gallery” link: http://www.powershellgallery.com/pages/GettingStarted

AzureRM_01_10-16-2015

Search for both “Azure” and AzureRM”.

AzureRM_02_10-16-2015

AzureRM_03_10-16-2015

During my quest to download Azure PowerShell 1.0, I realized that its AzureRM the one I need to download using: “Install-Module AzureRM” followed by “Install-AzureRM -force“.

AzureRM_04_10-16-2015

To start using the AzureRM Cmdlets, just run the “Import-AzureRM” command.

AzureRM_07_10-16-2015

Now, we are ready to play with Azure Resource Manager and Azure SQL databases.   There’s a total of 653 *AzureRM* cmdlets.

AzureRM_08_10-16-2015

AzureRM_09_10-16-2015

Run the following command to list all *AzureRM* commands:

Import-AzureRM; Get-Command *AzureRM*;

 

In my next blog article I’ll be converting my classic Azure SQL script to use the Azure RM new paradigm.

 

PowerShell connecting to SQL Server without SMO

Sometimes there will be a need to provide a scripting solution to connect to SQL Server to execute either a TSQL query or Stored-Procedure without the need of installing a SQL Server bits.  Here I’m providing two code snippets that gets the job done without the use of SMO (SQL Server Management Object) class.

Executing a T-SQL Query

Use the System.Data.SqlClient namespace from the .NET Framework Data Provider for SQL Server in order to build functions to execute T-SQL statement(s) and/or SQL Stored-Procedure with PowerShell.

Here’s an example in how to execute a T-SQL statement querying a table in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Select * from AdventureWorks2014.dbo.AWBuildVersion;";

## - Connect and Execute Stored-Procedure:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery, $ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;

 

nonSMO_01_10-16-2015

Executing a T-SQL Stored-Procedure

Now the code for executing SQL Stored-Procedure will be differect as we are going to use the ‘.sqlCommand‘ class to process the T-SQL Statement to run a custom table update procedure in its basic form:

## - Set PowerShell variables:
$ConnectionString = 'server=YourMachineName\MSSQL2K14;database=Master;Integrated

Security=false;User ID=sa;Password=$myPwd!';
$TSQLQuery = "Exec [AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion];";

## - Connect and Execute Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open()
$sqlCmd = New-Object Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
$sqlCon.close();

I use dbForge for SQL Server to create new Stored-Procedure to dynamically increase the version number.

nonSMO_dbForge_01_10162015

After executing the non-SMO code then the record did change:

nonSMO_01a_10-16-2015

nonSMO_dbForge_02_10162015

Most important, when using the Data.SqlClient.SqlConnection class, the connection need be ‘.Close()‘ after executing the T-SQL command.

Error Handling

As we are connecting to the SQL Server to execute T-SQL Commands, its important to add some error handling routine to trap errors during the connectivity and/or T-SQL Statement(s) processing.  And, we do this by evolving the basic PowerShell code into a function.

Lets create two functions that will accept two parameters: a connection string, and the T-SQL Query.

* Get-TSQLQuery function

function Get-TSQLQuery
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)
Try
{
## - Non_SMO Get SQL query:
$sda = New-Object System.Data.SqlClient.SqlDataAdapter ($TSQLQuery,

$ConnectionString);
$sdt = New-Object System.Data.DataTable;
$sda.fill($sdt) | Out-Null;
$sdt.Rows;
}
Catch
{
Write-Verbose "Error executing T-SQL Query: `r`n[$($error[0])]" -Verbose;
};
};

To test this function copy/paste the code into your PowerShell console, and the execute the following commands:

## - Set PowerShell variables:
$conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!';
$tsql = "Select * from AdventureWorks2014.dbo.AWBuildVersion";

## - Execute the function with its parameters:
(Get-TSQLQuery -ConnectionString $conn -TSQLQuery $tsql) `
| Select-Object `
SystemInformationID, 'Database Version', VersionDate, ModifiedDate `
| Format-Table -AutoSize;

nonSMO_02_10-16-2015

* Execute-TSQLStoredProc function

function Execute-TSQLStoredProc
{
Param (
[string]$ConnectionString,
[string]$TSQLQuery
)

Try
{
## - NON-SMO executing TSQL Stored-Procedure:
$sqlCon = New-Object Data.SqlClient.SqlConnection;
$sqlCon.ConnectionString = $ConnectionString;
$sqlCon.open();
$sqlCmd = New-Object Data.SqlClient.SqlCommand;
$sqlCmd.Connection = $sqlCon;
$sqlCmd.CommandText = $TSQLQuery;
$sqlCmd.ExecuteNonQuery();
}
Catch
{
Write-Verbose "Error executing T-SQL Stored-Procedure: `r`n[$($error[0])]"

-Verbose;
}; $sqlCon.close();

};

## - Set PowerShell variables:
$Conn = 'server=MTRINIDADLT2\MSSQL2K14;database=Master;Integrated Security=false;User

ID=sa;Password=$Adm1n!';
$tsql = "Exec x[AdventureWorks2014].[dbo].[usp_UpdAWBuildVersion]";

## - Execute the function with its parameters:
Execute-TSQLStoredProc -ConnectionString $conn -TSQLQuery $tsql;

nonSMO_03_10-16-2015

Adding the Try/Catch error handling block helps is trapping and displaying the errors.

This method serves as another alternative to connect to a SQL Server without the need to install an instance on a system.

 

 

PowerShell Deep Dive session at Work – a Success!

At TBC Corp. (Tire Kingdom) I scheduled the second PowerShell meeting for September 28th close to noon.  A give a 2hr PowerShell Deep Dive where everyone brought their laptops so they could follow along the session.  It was a total success!  I had 15 people signed up and 30 showed up for this session.

TBCPowerShell

This was a diversified group of people which included: System Administrators(Windows, Linus, iSeries, SAP), Data Center Operators, Business Intelligence Developers, SQL Server Administrator and even some Business Analyst.  The general overview went well. The whole group was very interested in learning about PowerShell.

GettingStartedPowerShell_01_9-28-2015 jpg

GettingStartedPowerShell_02_9-28-2015 jpg

It all happened thanks for the support of all the technologies managers which realized this technology need to be adopted and embraced in their daily responsibilities.  This is opening to new opportunities to improve productivity.

I’m honored to be part of TBC Corp organization to allow me to be an “influencer” and provide guidance to this amazing technology.  I thanks Microsoft MVP program for helping me in accomplishing my goals and keeping up-to-date with technology.

I’m planning to do more TBC PowerShell Monthly meetings so they can easily adopt PowerShell and its amazing new features such as Desired State Configuration.

WP_20150928_020

WP_20150928_019

WP_20150928_017

WP_20150928_018

I’m so excited!!