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

 

 

Playing with PowerShell Studio 2015 Windows Form 2/2

PSStudio2_01_4-21-2015

Adding the code

Now that we have the framework of the Windows Form built with added component, we can proceed to add the PowerShell code to make it work.

First, keep in mind, to add the code in any of the component you added to the form by just double-clicking on the component. This will take you to the editor “Script” pane and the cursor will be at the event generated code.

When you first create a blank Windows form, it will automatically generate the $formxxxxx_Load={..} event code. Here’s where you add any additional code before the windows form load at runtime. So, the following added code will set some “CheckBox” components properties to either “$true” or “$false”.

DataGrid2_01_04-28-2015

DataGrid2_02_04-28-2015

Here’s the code added to all out Windows Form components:

$formDisplayPSObjects_Load={
#TODO: Initialize Form Controls here
## - Setting checkboxes "check" properties:
$checkboxToDisplayAllObjectPr.Checked = $true;
$checkboxCommandHasSelectProp.Checked = $false;
};

$checkboxToDisplayAllObjectPr_CheckedChanged = {
#TODO: Place custom script here
$checkboxCommandHasSelectProp.Checked = $false;

};

$checkboxCommandHasSelectProp_CheckedChanged = {
#TODO: Place custom script here
$checkboxToDisplayAllObjectPr.Checked = $false;
};

$buttonRun_Click={
#TODO: Place custom script here
#Create DataSet Object

if ($checkboxToDisplayAllObjectPr.Checked -eq $true)
{
$caption = "Checkbox1 Selected..."; $textMsg = "checkboxToDisplayAllObjectPr.Checked"; `
[System.Windows.Forms.MessageBox]::Show($textMsg, $caption, [System.Windows.Forms.MessageBoxButtons]::OK);
};

if ($checkboxCommandHasSelectProp.Checked -eq $true)
{
$caption = "Checkbox2 Selected..."; $textMsg = "checkboxCommandHasSelectProp.Checked"; `
[System.Windows.Forms.MessageBox]::Show($textMsg, $caption, [System.Windows.Forms.MessageBoxButtons]::OK);
};

## Function to populate the DataGrid component:
RefreshDataGrid1

};

DataGrid2_03_04-28-2015

As you can see, just adding a few line of PowerShell code will bring your windows form to life. Please notice that in this application, both CheckBox component doesn’t really do anything except to display a Popup message.

Adding the DataGrid function

The following function (“RefreshDataGrid1″) is the heart of the form which will display the data into the DataGrid. In order to build our data we need to type then execute a one-line PowerShell command in our form textbox. This is where we use the “$executioncontext.invokecommand.NewScriptBlock($textbox1.Text)” which will convert the text into a PowerShell ScriptBlock object for processing in the “Invoke-Command”.

DataGrid2_04_04-28-2015

After we got the object create, the function will proceed to:
1. Create the DataSet object and the table placeholder for columns and rows of DataGrid.
2. Then, populate columns names and data rows.
3. Finally, it binds the DataSet table into the DataGrod component.

Function RefreshDataGrid1
{
param ()

$scriptblock = $executioncontext.invokecommand.NewScriptBlock($textbox1.Text);
$MyGridObject = $null; $MyGridObject = Invoke-Command -Scriptblock $scriptblock;

#Create DataSet Object
$dsObj = New-Object System.Data.DataSet;

#Create DataTable
$dtObj = New-Object System.Data.DataTable("PSObjTable");
$dtCols = $dtObj.Columns; $dtRows = $dtObj.Rows;

## - Populate Columns
foreach ($c in ($MyGridObject | gm -MemberType '*Property').Name)
{
$x = 1;
($dtObj.Columns.Add().ColumnName = $c);
};

## - Populate Rows:
foreach ($c in $MyGridObject)
{
## Initialize row:
$nwRow = $dtObj.NewRow();

## Data
foreach ($c2 in ($MyGridObject | gm -MemberType '*Property').name)
{
$nwRow[$c2] = ($c).$c2;
}
## Add row to object:
$dtObj.Rows.Add($nwRow);
};

$dsObj.Tables.Add($dtObj);
$datagrid1.SetDataBinding($dsObj, "PSObjTable");
};

DataGrid2_05_04-28-2015

Testing the Windows Form

Following the instruction from the previous blog, run the form and type the following one-liner:

dir c:\temp -file

Then, click on the “Run” button to execute the command and view the result in the Datagrid view.

DataGrid2_06_04-28-2015

Now, type another one-liner, select one either of the CheckBox, and click on the “Run” button:

dir c:\temp -file | Select name, extension, directory

DataGrid2_07a_04-28-2015

As you can see, the checkbox will display the message and the datagrid will refresh its data.

DataGrid2_07b_04-28-2015

Additional tip

If you want to lock the Windows Form so it won’t resize, use the “FormBorderStyle” property. This form has the “FormBorderStyle” property set to “FixedSingle”.

DataGrid2_08_04-28-2015

After the Windows form is fully tested and working, you can proceed to either keep executing it from PowerShell Studio or go ahead to build the executable file.

Keep in mind, This sample is not perfect but shows some essentials in building the Windows forms using PowerShell Studio 2015.  This tool is a GREAT tool that has a lot to offer.

If you want more information about Window Component properties, search the MSDN library online. Also, don’t forget to check SAPIEN’s Blog at:  http://www.sapien.com/blog/

 

Playing with PowerShell Studio 2015 Windows Form 1/2

Yes! SAPIEN TechnologiesPowerShell Studio 2015” product allow you to create Windows form and at the same time you can compile it creating an executable application.  So, just for playing around I’m going to create a Windows application that will allow me to execute a PowerShell command and display all of its properties values in a datagrid form.

PSStudio_00_4-7-2015

Creating a PowerShell Studio Windows form

This application will have the following Windows components:

1. A textbox to type the PowerShell command.
2. Two checkboxes to allow display all or selected properties.
3. A datagrid to display the results.
4. And the button to execute the PowerShell command typed in the textbox.

DataGrid_01_4-8-2015

Also, I created a PowerShell function use to build the data to be sent to the datagrid component.

To create a new Windows form click on the “File” menu and select “New” then from the dropdown list pick “New Form“. This option will create a “*.psf” file.  I’ve named it “TestDataGrid1.psf“.

PSStudio_01_4-7-2015

PSStudio_02_4-7-2015

One important thing to keep in mind, when working building a Windows-based solution, this editor is Visual Studio-like. So, if you have work with any version of Visual Studio then your learning curved is minimal.  Just drag-and-drop the object into the form then later add the script code afterward.

This Windows form will contain the following controls:
1. TextBox
2. Label
3. Checkbox
4. Button
5. DataGrid

DataGrid_02_4-8-2015

As you add object components to the form their properties can change too. Mainly, look into changing the “Text” property of some of the objects, such as: Form, Label, and the Button.

DataGrid_03_4-9-2015

At this point there’s no code added to this Windows application but, from the “Home” menu, you can click on the “Run” option to see it running.  And, ‘No!’, you can’t execute the application outside of this editor.  Unless, you create an executable program out of this solution which you can do with this editor.

DataGrid_04_4-10-2015

Next upcoming blog will be adding the PowerShell code and some functions to our solution.

 

PowerShell SQL Server SMO Simplicity Series – 1

This series is a learn by sample blog which I’m hoping any DBA and/or SQL Developer will take advantage to re-use these code snippets.  Due to the lack of SQL Server cmdlets, I prefer to use SMO which gives me flexibility to build my own PowerShell commands to automate most of my routine tasks.

1 – Connecting to your SQL Server engine

This take at least three lines of code: (excluding the comment lines)

## - Setting variables with Server and Database name:
$SQLInstanceName = 'YourSQLServerInstancename';

## - Loading SMO .NET Assembly: (Required)
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## - Connecting to SQL Server (Windows Authentication) and building you table object:
$MySQL = new-object('Microsoft.SqlServer.Management.Smo.Server') SQLInstanceName;

The first line you create a variable holding the SQL Server instance name, then loads the SQL Server assembly “Microsoft.SQLServer.SMO“, and then creating the variable holding the SQL Server objects.

The third line it’s what connect to the server using Windows Authentication.  Please be advice that, even you don’t have a Windows Authentication, it will not returned an error and it will return a Null object.

So, if you need to use SQL Server Authentication, you will need to add the following code after creating the “$MySQL” object:

## - Uncomment code block within [#region - #endregion] to enable use of SQL Authentication:
#region - Changing from default Windows to SQL Authentication:

#   ## - Variables required to SQL UserID and SQLPassword:
$SQLUserName = "YourSQLUserID"; $sqlPwd = "YourSQLUSerPassword";

## - Block of Code required to build SQL Authentication:
$MySQL.ConnectionContext.LoginSecure = $false;
$MySQL.ConnectionContext.set_Login($SQLUserName);
$SqlUserPwd = ConvertTo-SecureString $sqlPwd -AsPlainText -Force;
$MySQL.ConnectionContext.set_SecurePassword($SqlUserPwd);

Now, that you’re connected to the SQL Server instance, you can check the content in the variable $MySQL.  To do this the following one-liner help in exploring your object members (Methods and Properties);

$MySQL | Get-member | Out-GridView;

Get-MemberSMO01_3-30-2015

For starters, you might be looking to display some information so you need to look for $MySQL members “Properties” which hold values you can display using the PowerShell “Select-Object” cmdlet.  For example, the following oneliner will display the following database properties: Name, Owner, CreateDate, FileGroup, and PrimaryFilePath.

$MySQL.Databases | Select name, Owner, CreateDate, Filegroups, PrimaryFilePath | Format-Table -autosize;

So, Get-Member is you best command to explore your PowerShell objects and understand its content.  Another very useful command is the “Out-Gridview” which will use more heavily in the next blog series.

Don’t forget for any of these PowerShell cmdlet you can use the “Get-Help” display the command documentation:

Get-Help Get-Member -ShowWindow

Have fun and Keep learning PowerShell!