Max Trinidad – The PowerShell Front

If you’re experiencing problems installing the latest Azure PowerShell cmdlets (0.8.0 – 04/03/2014)  after installing Windows Management Framework v5.0 Preview (WMF) then don’t worry.

You can go back the uninstall the WMF v5.0 Preview, then proceed to download/install the recent *Azure PowerShell cmdlets, and go back to re-install the WMF v5.0 Preview.  It will back be working like nothing happened.

Looks like there are two Azure modules in this update:

1. Azure
2. AzureProfile

*Note: I’m this steps may need to be repeated until WMF v5.0 becomes RTM.

🙂

Here it is.  I had to clean it up so I could have everyone get the demo scripts and the PowerPoint presentation.  Thanks to the Orlando Code Camp organizers for having me and to all whom attend my session.

Click on the following link my session:  http://1drv.ms/1gtBvFV

Don’t stop learning PowerShell!

Due to my current workload I decided to change my monthly PowerShell meetings to be on every third Thursday of the month starting February 20th at 6:30pm.

Next meeting I’ll be speaking on “SSIS – Integrating PowerShell in a Script Task Component”.  Register at: https://www.eventbrite.com/e/florida-powershell-user-group-monthly-meeting-february-2014-tickets-10610571505

Keep in mind, we start gathering at 6:30pm and the meeting will start at 7:00pm (EST).

See you next week!

This is a quick blog on how you can use PowerShell and SMO to change some of the SQL Server database  properties you may find useful.

In my scenario I’m working on some legacy SQL Server 2005 databases existing properties have the  following values:

1. “Recovery Model” = ‘FULL’.
2. “Compatibility Level” = ‘Version80’ (for SQL Server 2000).
3. “PageVerify” = ‘TornPageDetection’.

As a proof of concept I created a dummy database named ‘testdatabase’ with the above properties so I  can test my *PowerShell script and alter the values for all three properties.

*Note: If you have SQL Server Management Studio (SSMS) 2005 (or Greater), then you already have SMO  (SQL Server Management Objects) assemblies already installed in your machine.

First thing I needed to do is to list all databases that will meet the above criteria. This way we can  changes the following values to:

1. “Recovery Model” = ‘Simple’.
2. “Compatibility Level” = ‘Version90’ (for SQL Server 2005).
3. “Page Verify” = ‘CHECKSUM’.

The following script will list all databases meeting these criterias:

[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly and connect the your SQL Server instance:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;

## – List all database meeting criteria:
$MySQL.Databases `
| where{ `
(($_.PageVerify -ne ‘Checksum’) `
-or ($_.CompatibilityLevel -ne ‘Version90’) `
-or ($_.RecoveryModel -ne ‘Simple’))} `
| Select-Object name, @{label=’dbSize(MB)’; `
Expression={($_.Size).ToString("###,###,###");}} `
,CompatibilityLevel, Pageverify `
| ft -AutoSize;

[/sourcecode]

Of course, this list let me know that in some of the databases not all three properties need to change.  (see sample list below)

Name dbSize(MB) RecoveryModel CompatibilityLevel PageVerify
—- ———- ————- —————— ———-
xxxxP 1,386,603 Simple Version80 TornPageDetection
xxw 700 Simple Version90 TornPageDetection
xxxxxxin 200 Simple Version80 None
xxxxxxxx_SZ 200 Simple Version80 None
testdatabase 20 Full Version80 TornPageDetection

I can proceed to test my PowerShell SMO script lines to make some database property value changes. As  you seen in the originating script I’ve created a PowerShell objects named ‘$MySQL’ which contains all  of my SQL Server instance objects. So, by navigating thru my Database objects I can go directly to my  ‘testdatabase’ properties and make the changes with the following lines:

[sourcecode language=”powershell”]
## – Changing the properties values:
$MySQL.Databases[‘testdatabase’].RecoveryModel = ‘Simple’;
$MySQL.Databases[‘testdatabase’].CompatibilityLevel = ‘Version90’;
$MySQL.Databases[‘testdatabase’].PageVerify = ‘CHECKSUM’;

## – Update database properties and refresh the database:
$MySQL.Databases[‘testdatabase’].Alter();
$MySQL.Databases[‘testdatabase’].Refresh();

[/sourcecode]

Now, I have succesfully updated the database properties on a fly and listed the updated properties with  the following script:

[sourcecode language=”powershell”]
## – list the updated properties:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| where{ `
(($_.name -eq ‘PageVerify’) `
-or ($_.name -eq ‘CompatibilityLevel’) `
-or ($_.name -eq ‘RecoveryModel’))} `
| Select-Object name, value | Format-Table -AutoSize;

[/sourcecode]

Name Value
—- —–
CompatibilityLevel Version90
PageVerify Checksum
RecoveryModel Simple

In summary, after making my changes, I’ve used both SMO methods of ‘.Alter()‘ and ‘.Refresh()‘ to  secure the updated values. Also, is good to validate these changes by going back to SSMS application.

On my next part 2 of this blog, I’ll be creating a PowerShell function to automate this process so it  can be executed against multiple databases and SQL Servers.

Here’s full script to change all three properties on database ‘testdatabase’:

[sourcecode language=”powershell”]
## – Loads the SQL Server SMO Assembly:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;

## – Connect SQLServer instance:
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;

## – Changing properties in Databases:
$MySQL.Databases[‘testdatabase’].GetPropertySet() | Select-Object name, value | Format-Table -AutoSize;
$MySQL.Databases[‘testdatabase’].RecoveryModel = ‘Simple’;
$MySQL.Databases[‘testdatabase’].CompatibilityLevel = ‘Version90’;
$MySQL.Databases[‘testdatabase’].PageVerify = ‘CHECKSUM’;
$MySQL.Databases[‘testdatabase’].Alter();
$MySQL.Databases[‘testdatabase’].Refresh();

## – List the updated properties:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| where{ `
(($_.name -eq ‘PageVerify’) `
-or ($_.name -eq ‘CompatibilityLevel’) `
-or ($_.name -eq ‘RecoveryModel’))} `
| Select-Object name, value | Format-Table -AutoSize;

[/sourcecode]

If you like to learn more about PowerShell working with SQL Server SMO, check my SQL Server Pro article at:
http://sqlmag.com/powershell/using-sql-server-management-objects-powershell

Bonus – Another way to list a database property names and values:

[sourcecode language=”powershell”]

## – Loads the SQL Server SMO Assembly and connect the your SQL Server instance:
[system.reflection.assembly]::LoadWithPartialName("Microsoft.SQLServer.Smo") | Out-Null;
$MySQL = new-Object Microsoft.SqlServer.Management.Smo.Server ‘YourSQLServerInstanceName’;

## – List everything:
$MySQL.Databases[‘testdatabase’].GetPropertySet();

## – Or, List the database only property: name and value:
$MySQL.Databases[‘testdatabase’].GetPropertySet() `
| Select-Object name, value | Format-Table -AutoSize;

[/sourcecode]

Here’s a shout out of two great tools you should try if you want (or planning) to integrate your PowerShell scripts into a Visual Studio solution.  Specially, if you’re using SQL Data BI Tools for Visual Studio or just build apps with Visual Studio full version.

1. “PowerShell Tools for Visual Studio” by Adam Driscoll (PowerShell MVP) – http://visualstudiogallery.msdn.microsoft.com/c9eb3ba8-0c59-4944-9a62-6eee37294597
2. “StudioShell” by Christopher Beefarino (PowerShell MVP) – http://studioshell.codeplex.com/

In my laptop I have both the SQL Data BI tools for Visual Studio 2012 (VS shell), and the full version of Visual Studio 2013.  One thing to point out, only the “PowerShell Tools for Visual Studio” will install in both versions: 2012, and 2013.  But, when using both at the same time, then you have both full editor features to write scripts and a practical PowerShell prompt so you can test one-liners.

You can create either a PowerShell solution by adding a new PowerShell Project, or simple adding “a new item” to your existing solution.

Now we can work with Visual Studio and PowerShell from the cloud if we want.  Here’s an image of my physical laptop and my Surface 2 connected to my Windows Azure VM running Windows Server 2012 R2 with Visual Studio 2013 installed.

Notice when creating scripts Intellisense is available and you can execute the code from withing Visual Studio.

Next you may want to pay attention to Microsoft Team Foundation server either on-premise or cloud options.  So you can build your solution and do some source code versioning.

Everything is about trying and evaluating these great community tools.  Give them your honest feedback so they can keep making enhancements and increase their value.

 Additional Information

Here’s some other important updates for Visual Studio 2012: Visual Studio 2012 Team Explorer 8/2/2012 – http://www.microsoft.com/en-us/download/details.aspx?id=30656 Visual Studio 2012 Update 4 11/12/2013 – http://www.microsoft.com/en-us/download/details.aspx?id=39305

Happy PowerShelling!!