Archive for the 'SQL' Category

PowerShell-Based SQL Management Console

Chad Miller @cmille19 has just published his SQL Server PowerShell Extensions PowerPack for anyone to download and use.

The console has tons of functionality to access and manage all the aspects of SQL: servers, databases, permissions, replication, services, and so on, and so forth.

And for accidental DBAs like myself it is just a great resource for managing SQL and learning how to script it (because I can see all the PowerShell code behind!) If you like myself don’t know and might not have time to learn T-SQL and standardize on PowerShell for your scripts – this is a great resource to download and start using.

Read more about the pack in Chad’s blog. Download the pack here.

Tags: , , ,

Quest SQL Cmdlet References

We have significantly expanded our online reference wiki at wiki.powergui.org. In addition to PowerGUI and AD cmdlets documentation, it now has:

I am not a SQL guy myself, but this surely looks like a very impressive addition to the cmdlets available out of the box with SQL.

Tags: , ,

Any SQL gurus out there?

Do you know both SQL and PowerShell? We have a thread on our forum about a PowerShell script not working properly for SQL and it have no idea what the issue can be. Any help troubleshooting the issue is appreciated.

As usual people with knowledge from different areas are in the highest demand. In our forums at PowerGUI.org we have great community of people like Shay, Kirk, Paul, Robbie (sorry for not listing everyone) handling all sorts of questions on PowerShell as such, and its use to manage Windows, AD, Exchange. We even have a VMware guru – Scott – starting to handle VI Toolkit-related questions. But it looks like SQL is still the area our community does not cover well. Any help will be appreciated.

[UPDATE] Woohoo! Arnoud Jansveld posted a solution to the thread. Issue resolved now. Thanks guys! I really appreciate the community help here.

Tags: , ,

More SQL Cmdlets

It didn’t take long for the community to start adding more cmdlets to the (somewhat limited) default Microsoft’s implementation of PowerShell cmdlets and provider for SQL.

Last week I found this blog post by Jacob Bindslet in which he is using PowerShell to get SQL transaction data!

And once I found out that the cmdlets are actually shipped with a trial version of Litespeed (which you can freely download here) – I got really interested and gave it a try.

For some reason (“to ship is to choose”? ;)) the cmdlets were not registered by default, but this was easy to fix:

set-alias installutil $env:windir\Microsoft.NET\Framework\v2.0.50727\installutil.exe
installutil "C:\Program Files\Quest Software\LiteSpeed\SQL Server\Quest.LogReader.SqlServer.PSSnapIn.dll"
add-pssnapin Quest.LogReader.SqlServer

Now I could connect to a database:

$lr = New-QLRSqlReader -Server spb9771 -Database AdventureWorksDW -LoginMode Windows -Online

And retrieve the latest changes from the transaction log:

Get-QLRSqlCommand $lr

Or use parameters to limit the set:

Get-QLRSqlCommand $lr -After '24-09-2008 12:00:00'

And not only can I use standard PowerShell cmdlets to output the data into csv/xml/html – the snapin has more advanced Out-* cmdlets that let me save the data into another DB:

Get-QLRSqlCommand $lr | Out-QLRSqlDatabase -Server mydbserv -Database MyAuditDB -Table AdventureWorksDW_log -LoginMode Windows

Now, if I schedule that to be automatically run every now and then (or actually better off just before the backup is started) I get full audit trail of any changes made to my database! Is that cool or not?

Besides Litespeed, Log Reader seems to also be available with Toad and Change Director for SQL Server – if you have any of these products, look for the Quest.LogReader.SqlServer.PSSnapIn.dll file. If not, download the trial here!

Tags: , ,

SQL PowerShell details revealed

Kirk, Richard, and Jeffrey have already blogged that SQL 2008 is (unless this gets canceled) going to have PowerShell support built in. Today on the last day of the IT Forum we learned the details. And frankly were not impressed. At least Richard and I – who were sitting on the front row and were ranting so much that Bob Beauchemin stopped giving us a word at some point (just kidding, Bob had this talk kind of imposed on him and did his job quite well despite not really being a PowerShell guy.)

Anyways, here are the details:

PowerShell is likely to debut in SQL 2008 in CTP 6 in February 2008.

What they will ship is a provider which will allow you to browse SQL databases and tables as a file system.

The provider will not give access to the actual data in the databases: only to the structure (down to columns) and configuration settings

You are not able to create items (tables, columns, rows) or copy them.

Changing any settings is very unintuitive because they have to be done via properties of objects. So you have to first cd into the database/table/column, and then do something like dir | foreach-object { $_.Method (parameter1, parameter2, etc.) } – this is not only lengthy but also not quite in line with PowerShell discoverability as you get no help or tab-completion for such calls.

A much better approach would have been a set of cmdlets but this was not the way selected by the team. At least not for now.

For now, there will be just 4 cmdlets with quite auxiliary tasks:

  • Run-Sqlcmd
  • Decode-SqlName
  • Encode-SqlName
  • Convert-UrnToPath

I hope that post 2008 RTM, SQL team will revise their PowerShell design and make it fit better into PowerShell principles and provide better ways of managing SQL databases and accessing the data. Until then, the use will probably be limited to just browsing the databases, and for most tasks .NET classes being called from PowerShell syntax (like in this pack from PowerGUI library).

Tags: ,

PowerShell for SQL Change Management

Quest Change Director for SQL TechEd logoQuest Software has just released another PowerShell-enabled product (don’t you see a trend here? 😉 – this time the company has added PowerShell command-line to SQL Change Management product – Quest Change Director for SQL.

Change Director basically allows SQL administrators and developers to perform all the operation related to changes: automatically move changes from dev to test to production database environments, perform impact analysis without actually making changes, rolling back changes (including schema changes), version control, detailed change audits, and much more.

In earlier releases of the product it was UI and UI alone. Customers wanted more automation and scripting options. The development team responded with adding PowerShell to the project.

Now you can for example output database comparison into an Excel file with this command:

$a=get-cddatabase myServ base1
$b=get-cddatabase servMain base2
compare-cdobject $a $b | export-cdexcel 'c:\test.xls'

Or export a database snapshot to a file with that:

$a = Get-CDDatabase hostname base1
Get-CDSnapshot $a | Export-CDSnapshot 'c:\cdsnapshot.cds'

You can watch Change Director flash demo, read documentation or download a trial version at the site page. There’s also online community for Change Director with a wealth of videos, tutorials, documentation, team’s blog and discussion forums.

Disclosures:

Tags: , ,

Revamped PowerGUI Library

We have revamped the PowerPack Library hosting dozens of useful PowerGUI extensions so it now lets you browse the categories rather than look through one big flat list.

One list was pretty handy in April when we were just starting but now with 27 packs already there and new one submitted by the community every few days search became the only way to find what you need.

So what we did was create a bunch of categories and put the existing packs in them. Here are the ones we have at the moment:

A lot of good stuff. All available for free and most of the packs posted by community members (with really few exceptions of packs by the PowerGUI team.)

Another good thing that I like about the packs is that they can actually show you how to use PowerShell to script against the systems.

For example, if you need to learn to manage SQL Server with PowerShell scripts you can download the SQL PowerPack, use it and at any time click the PowerShell Code tab to see/copy-paste the code you need.
If there’s something else we can do to make the site more convenient to use please comment here or in the site discussion forum.

Dmitry

Tags: , , , , , , , , , , , , , , , ,

Rob “Deuce” gets $200 for SQL PowerGUI pack

Our $200 a week draw for PowerGUI draw is on the way and this week the prize goes to Rob “Deuce” Doucette.

This week he submitted his PowerPack for SQL management (who would have thought you could use PowerShell to manage SQL!) and I am a big fan of his OpsManager 2007 pack which he submitted earlier. Well deserved!

Just yesterday Rob gave me a web demo of his MOM pack which I recorded and will hopefully publish on the web later this week. Stay tuned!

Richard’s PowerGUI Write-Up

Richard has a nice summary of what PowerGUI is and how the demo at the UK user group went.

Richard says: “I have tested PowerGUI against Windows, Active Directory, Exchange 2007 and SQL Server 2005. I would highly recommend it as an addition to your administration tool set. It definitely saves hunting for the script you know you have somewhere.

Nice. We are very lucky having Richard and other PowerShell User Group members on the community!


My Recent Tweets

Legal

The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer - WSO2 or anyone else for that matter. All trademarks acknowledged.

© 2007-2014 Dmitry Sotnikov

August 2022
M T W T F S S
1234567
891011121314
15161718192021
22232425262728
293031  

%d bloggers like this: