Windows 10, PowerShell, Visual Studio Tools and more

More to learn and education never stops.

Now that Windows 10 has arrived take advantage of the new tools to start developing applications. Check out PowerShell version 5.0 and the new Visual Studio 2015 with its tools.

Take advantage of all the free training provided by both Microsoft people and MVP’s by connecting to either Microsoft Virtual Academy and Channel 9.  There are no excuses, just do it!

Click on the following banners for more information; (its free)

DownloadVS2015a

MSVirtualAcada

Channel9a

Don’t forget to check out the new Microsoft PowerShell at:

Also, take the opportunity to check out our PowerShell community sites, such as:

All these are GREAT source of information about PowerShell.  Along with it you can find other organizations that provide free registrations to webcasts, such as:

And, for affordable online training check Pluralsight Video Library: http://www.pluralsight.com/

So, as you can see, there’s plenty of resources available to start your education in Microsoft Technologies.

Go ahead and don’t wait any longer!

FLPSUG PowerShell August 2015 special events

A live Florida PowerShell User Group session at PC Professor in West Palm has to be rescheduled to a later date. Topic: “Integrating PowerShell in Visual Studio Community“.

DownloadVS2015a

For more information and registration:  (Coming soon)

I’m hoping to provide more live presentation at this location in the future. Stay tuned!!

Also, our online Florida PowerShell User Group meeting is pleased to have PowerShell MVP Trevor Sullivan on August 27th at 7:00pm. Topic: “Microsoft Azure Cloud Automation using PowerShell“.

For more information and registration: http://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-august-2015-tickets-18088286552

Come and join us on any of these free events!

FLPSUG – “PowerShell SQL Server SMO Simplicity” video available

Recorded Thursday – 05/28/2015 7:00pm
Speaker Bio: Maximo Trinidad, (Microsoft MVP – PowerShell)

Topic: “PowerShell SQL Server SMO Simplicity” –
In this presentation I will be showing how easy it is to use PowerShell the extract information from SQL Server. I will cover creating, discovering, and formattting objects which is part of how you use PowerShell. At the same time, I will show the use of SMO to connect and interact with your SQL Server engine to collect information.

Download presentation script file:

Devart dbForge Studio for SQL Server Management Tool

I’ve been using DevartdbForge Studio for SQL Server mainly for helping documenting some of my legacy databases and to build some T-SQL script to embed in my PowerShell scripts.

This is a Great tool to have in your SQL arsenal. Check it out!

devart_logo

Download free for 30 days evaluation copy: https://www.devart.com/dbforge/sql/studio/download.html

* Customized Skin
* Database Comparison
* .. Many more interesting features.

Here are some pictures showing what I like about this product:

devart_01

devart_02

devart_03

devart_04

devart_05

devart_06

devart_07

devart_08

devart_09

devart_10

devart_11

PowerShell in South SQL Saturday 379 was a Great Success

SQLSaturdaySoFlorida2015

Once again I’m thankful to the organizers to have me speak at this “Awesome” event.  I appreciate the all whom attend my session meking it a Great Success and they got more.  My “PowerShell with Visual Studio SQL Data Tools” session became also a “SMO Simplicity Recap” session. They got two session in one.

SQLSat379_02

 

Session highlights

1. Visual Studio Community 2013 is the environment to use for integrated development by including Microsoft and Third-Party tools like:
a. PowerShell Tools for Visual Studio
b. Python Tools for Visual Studio
c. SQL Data Tools – Business Intelligent Developement
d. PowerShell Studio 2015  – call from within Visual Studio
e. PrimalXML 2015 – Call from within Visual Studio
d. And many more can be added…

SQLSat379_03

2. Visual Studio integration with either Team Foundation and Github repositories.

3. A quick dive in XML objects.

4. PowerShell error trapping in integrated solution.

SQLSat379_06

5. PowerShell SMO embedding and executing T-SQL code.

SQLSat379_05

During my presentation I demo for the first time how Visual Studio can trap PowerShell errors from within a SSIS Script Task component. And, everyone dropped their mounth. Beside the fact that you can also run and trap script errors by executing by itself.

SQLSat379_04

In the SMO session, I show how useful the ScriptBlock can be when embedding and running T-SQL code while PowerShell reads one object at a time.

To download my presentation and demo files are all available under South Florida SQLSaturday Schedule page:
http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

Once again, THANKS to everyone for your attendance and support.

June Month for learning PowerShell and SQL Server SMO

Yes! This is month you could learn PowerShell and SQL Server Management Object (SMO) techniques to start building script(s) to manage your SQL Server(s).

I will be speaking at the following events:

SQLSaturdaySoFlorida2015

1. South Florida SQL Saturday #379 on 06/13/2015 all day event. Session: “PowerShell with Visual Studio SQL Data Tools” (03:00PM – 04:15 PM) Register at:  http://www.sqlsaturday.com/379/Sessions/Schedule.aspx

GeekSynch

2. IDERA’s Geek Sync Webinar – “PowerShell Essentials using SQL Server SMO” on 06/16/2015 at 10:00 AM CT (11:00 AM EST). Register at: https://attendee.gotowebinar.com/register/5998631242300195841

At the SQLSaturday event there will some giveaways during my session.  Go ahead and register to any of these events.  It’s Free!

PowerShell SQLServer SMO Simplicity Series – 2

Now that we got the connection to the server there are a couple of lines we could include to avoid connection timeout during execution of our T-SQL script using SMO with PowerShell. So, in this blog will be covering executing a T-SQL script and viewing its results.

The *T-SQL script will build me table with data statistics about my selected Database indexes need to either Reorganize, or Rebuild.

*Note: The following T-SQL script modified from its original “Index optimization – REBUILD vs. REORGANIZE” by Author Sarjen Haque. It can be found at http://sqltouch.blogspot.com/2013/07/index-optimization-rebuild-and.html

$TSQLqry1 = @"
if object_id('$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]') is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
'$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]' as [IdxFragStatDatabase] ,
'$($DatabaseName)' as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then 'RELAX'
when dmv.avg_fragmentation_in_percent <= 30 then 'REORGANIZE' when dmv.avg_fragmentation_in_percent > 30 then 'REBUILD'
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N'LIMITED') dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, 'ISMSShipped') = 0
and (i.name is not Null and i.type_desc <> 'HEAP')
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

Notice that PowerShell will substitute the values for $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList] within the Here-String @” .. “@. It’s important to notice when using Here-String TABS are not allow at the end of ‘@”;’ or you’ll get an error.

Back to the connection section. We need to include the following just after the ‘$MySQL = New-Object $MySQL = new-object Microsoft.SqlServer.Management.Smo.Server …‘:

$MySQL.ConnectionContext.ConnectTimeout = 21600;   (This is an example (optional) set to 21600 sec = 6hrs)
$MySQL.ConnectionContext.StatementTimeout = 0;   (This is an example set to 0 for no timeout when running T-SQL queries)

Here’s the sample for the code placement:

## - Connect and Execute T-SQL script:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

If you don’t use the ‘ConnectionContext.StatementTimeout’ you’ll get a timeout error after 10 minutes of execution.

Now, we proceed to create our variables use for the T-SQL script:

[string] $SQLServerInstanceName = '.'
[string] $DatabaseName = 'AdventureWorks2014'
[string] $tblSchema = 'dbo'
[string] $IdxStatDatabase = 'devMaxTest'

The purpose of the $IdxStatDatabase is to redirect the data index stat report to another Database. Then, we use the following line to execute our T-SQL script using SMO with PowerShell.

$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);

After the PowerShell script execute, open SQL Server Management Studio (SSMS), go to the database where the index stat data is stored, and query the table to view results.

SQLPOSHSIMPLY2_01

This report will help you identify those indexes that need to be taken care off.

Full sample script below:

## - Set variables for T-SQL script:
$SQLServerInstanceName = '.';
$DatabaseName = "AdventureWorks2014";
$tblSchema = "dbo";
$IdxStatDatabase = "devMaxTest";

## - Build the T-SQL script for execution:
$TSQLqry1 = @"
if object_id('$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]') is not null
begin
Drop Table $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList];
end

select
'$($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]' as [IdxFragStatDatabase] ,
'$($DatabaseName)' as [IdxFragStatOnDatabase] ,
object_name(o.object_id) as [table_name] ,
schema_name(o.schema_id) as [schema_name] ,
i.name as [index_name] ,
i.type_desc as [index_type] ,
dmv.page_count as [Page_Count] ,
dmv.fragment_count as Fragment_Count,
round(dmv.avg_fragment_size_in_pages, 2, 2) as [avg_fragment_size_in_pages] ,
round(dmv.avg_fragmentation_in_percent, 2, 2) as [avg_fragmentation_in_percent] ,
case when dmv.avg_fragmentation_in_percent <= 5 then 'RELAX'
when dmv.avg_fragmentation_in_percent <= 30 then 'REORGANIZE' when dmv.avg_fragmentation_in_percent > 30 then 'REBUILD'
end as [action],
getdate() as [RunOnDate]
into $($IdxStatDatabase).$($tblSchema).[$($DatabaseName)_IndexFragmentationStatsList]
from sys.partitions as p with ( readpast )
inner join sys.indexes as i with ( readpast ) on i.object_id = p.object_id
and i.index_id = p.index_id
inner join sys.objects as o with ( readpast ) on o.object_id = i.object_id
inner join sys.dm_db_index_physical_stats(db_id(), null, null, null, N'LIMITED') dmv
on dmv.OBJECT_ID = i.object_id and dmv.index_id = i.index_id
and dmv.partition_number = p.partition_number
where objectproperty(p.object_id, 'ISMSShipped') = 0
and (i.name is not Null and i.type_desc <> 'HEAP')
order by [avg_fragmentation_in_percent] DESC,
[table_name],
[index_name]
"@;

## - Connect, set timeout values:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-object Microsoft.SqlServer.Management.Smo.Server $SQLServerInstanceName;
#$MySQL.ConnectionContext.ConnectTimeout = 21600; #Optional#
$MySQL.ConnectionContext.StatementTimeout = 0;

## - Execute T-SQL script:
$r = ($MySQL.Databases[$DatabaseName]).ExecuteWithResults($TSQLqry1);
Write-Host "End of Script";

Using PowerShell Studio 2015 snippet sample

In my previous blog I show how to integrate a custom function called “RefreshDataGrid1″ which is used to populate the DataGrid.  Now, SAPIEN PowerShell Studio does provide a Snippet code called “ConvertTo-DataTable” will convert your PSObject to a Data Table type which can be load into the DataGrid component. Thanks to June Blender (SAPIEN Technology Evangelist) for showing me the ” ConvertTo-DataTable” function.

Snippet_00_5-1-2015

This function can be found in the Snippet panel just below the WMI folder.

Snippet_01_5-1-2015

So, changes can be made to the existing TestDataGrid01 solution: 1. Removing the “RefreshDataGrid1″ function code and from Button_Click event. 2. From the “Snippet” panel, Drag/Drop the “ConvertTo DataTable” code into the Script pane. 3. finally add the code to use the function in the “Button_Click” event.

Snippet_02_5-1-2015

So, adding the following two line of code will get the data populated into the DataGrid component:

$table = ConvertTo-DataTable -InputObject $MyGridObject
$datagrid1.DataSource = $table;

The use of Snippet code can help cut down your PowerShell Coding time.

Snippet_03_5-1-2015

Snippet_04_5-1-2015

Please, take the time to learn and understand this tool.  It’s full of interesting features.  Don’t forget to check SAPIEN Blog site at: http://www.sapien.com/blog/

WMF Version 5.0 PowerShell Preview for Windows 7 SP1

Yes!  Windows Management Framework Version 5.0 PowerShell Preview is also available for Windows 7 SP1 machines.  HURRAY!!

POSH5tWin7_01_4-29-2015

Go ahead and check the “Windows PowerShell Blog” article about it: http://blogs.msdn.com/b/powershell/archive/2015/04/29/windows-management-framework-5-0-preview-april-2015-is-now-available.aspx

Take it for a test-drive and I guarantee you won’t look back.  Here’s the Microsoft Download page: http://www.microsoft.com/en-us/download/details.aspx?id=46889

POSH5tWin7_00_4-29-2015

Make sure to execute both of the following commands:

Set-ExecutionPolicy -ExecutionPolicy RemoteSigned;
Update-Help

By the way, this still is a Preview version.  So, expect some errors to show up.   Don’t be discourage by it!

POSH5tWin7_02_4-29-2015