Month: July 2011

Yes, is out!!  Download at: https://www.microsoft.com/betaexperience/pd/SQLDCTP3CTA/enus/default.aspx

You must have a valid Live ID account to be able to proceed downloading the SQL Server Denali CTP3, and must allow to install the Activex control or use the Java Applet for the Download Manager to begin the process.

Don’t forget the read the Release Notes, and have fun with it!!

Post in Twitter:

From @SQLvariant: Pretty Cool: Microsoft SQL Server Code-Named “Denali” CTP3 Release Noteshttp://social.technet.microsoft.com/wiki/contents/articles/3711.aspx #SQLDenali  (07/12/2011 – updated release note link)

(Updated: 07/09/2011 – Microsoft decided to remove the link to this Release Notes.)

From me (@MaxTrinidad): #SQLDenali #PowerShell – Make sure to read the release notes on PowerShell stuff: 1.1, 1.5, 2.4, 5.8, 5.9, 5.11.1, 5.11.3, & 8.1. Awesome!!

From me (@MaxTrinidad): #SQLDenali CTP3 comes with the long awaited SQLPS module… HURRAY!!

Yes!! Read the “Microsoft SQL Server Code-Named “Denali” CTP3 Release Notes”: http://social.technet.microsoft.com/wiki/contents/articles/3711.aspx (What a teaser??)

About the SQLPS Module.  Finally, Microsoft have provided us with a PowerShell Module that will load all available SQL Server PowerShell commands (SQLPS).   But, keep in mind, that this module only load the command use with the SQL Database Engine.  You will still need to manually load the Master Data Services commands, plus whatever they will give us.

So,  for now, it’s a waiting game to see SQL Server Denali CTP3 come out.

WE ARE ALL WAITING!!

Here’s a good example of how useful is SMO in your SQL Server Developement environment.  As I the task repopulating data in a quarterly basis, I need to clear all my staging tables in databases before my import processes.   OK! I could just create a T-SQL query with all my truncate lines.  But, this will force me to open SSMS, file open my T-SQL file, and then execute the query.  Or, I can create simple PowerShell function so I can run from my PowerShell Console, or ISE editor when it’s needed.

In my previous blog, I showed the Get-TSQLTableRecordCount function which allow me to either list all my databases, or display a list of row count for all the tables in the database.  I can use this function to verify that my PowerShell table truncate function is working.

So, when using SMO, how many lines of code it will take me to create this function?  About 11 line of code.   But truely, the heart of this code is only 3 lines, and rest if only adding the necessary code to it a function.

Here’s the basic 3 lines that make the truncate work:

[sourcecode language=”powershell”]
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
[/sourcecode]

Of course, you need to provide both the SQL Server instance, and the database name

Note: Be careful with this function because is meant to truncate all tables in the given Database.

I went back to SSMS to graphically confirm that all data was removed, but I found out that the database size was still showing.  I knew I had to incorporate the SMO Shrink() method for the database after doing the SMO TruncateData() method on each of the tables.

Here’s an image showing the database size after the truncating all tables:

So, I added the following line after the truncate tables:

[sourcecode language=”powershell”]
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
[/sourcecode]

Image after truncate and running the SMO Shrink() method:

Putting everything together into a basic function performing your tables truncate and database shrink process:

[sourcecode language=”powershell”]
Function Clear-TSQLTruncateAllTables{
[CmdletBinding()]
Param(
[Parameter(Mandatory=$True, Position=0)] [ValidateScript({$_ -ne $null})] [string] $SQLInstanceName,
[Parameter(Mandatory=$True, Position=1)] [ValidateScript({$_ -ne $null})] [string] $DatabaseName
)
$MySQL = new-object(‘Microsoft.SqlServer.Management.Smo.Server’) $SQLInstanceName
$Mytables = $MySQL.Databases[$DatabaseName].tables
foreach($table in $Mytables){$table.TruncateData()}
$MySQL.Databases[$DatabaseName].Shrink(0,[Microsoft.SqlServer.Management.Smo.ShrinkMethod]’TruncateOnly’)
}
[/sourcecode]

This is just a teaser code so you can see the potential and benefit in understanding SMO in PowerShell.  I will be incorporating a more elaborate version of this function into my SQLDevTools Module available in CodePlex in the next release.

For more information on this SMO DatabaseShrink Method, here’s some links: http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.database.shrink(v=SQL.110).aspx and http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.shrinkmethod(v=SQL.110).aspx