Getting acquainted with PowerShell

PowerShell (POSH) is an area in which I have not explored yet. Colin Smith (blog | twitter) and Zach Mattson (blog | twitter) both presented topics about POSH and unfortunately I could not attend either, but that’s a different story. Today I felt I needed to take a break from my certification studies and start getting familiar with Microsoft’s new scripting language.

We have a POSH user group here in Arizona which is appropriately called the Arizona PowerShell Users Group. Though I haven’t attended a meeting yet the subjects that are covered is nonetheless intriguing. Jason Helmick (blog | twitter) and Mike Pfeiffer (blog | twitter) are the officers of the user group and their blogs contain a lot of information on how to get started.

Using Jason’s example from his video post: QuickBit: Executing scripts in PowerShell I saw him execute a cool script that utilized the system speech and it literally spoke what he had written. I used the script editor and ran the following:

script syntax

[void][System.Reflection.Assembly]::LoadWithPartialName("System.speech")
$Speak=new-object System.Speech.Synthesis.SpeechSynthesizer
$Speak.speak("SQL Saturday 47 Rocks!")

Another thing that Jason pointed out that I saw as really handy was the fact you can output the results to a grid view. Yeah a grid view!


script syntax

Get-Service | Out-GridView

The cool thing about output to grid is the fact that you have filtering capabilities and sorting. That’s a big win in my eyes!

results

I really dig command-line administration and I should have dove right into this but I didn’t. Now I need to begin my search for some good books and reference sites which Jason also refers to his video post: DrillBit: PowerShell books I keep. Hopefully I will have some scripts to post and come up with some ideas to help automate things. I’ll probably start with converting some of my old batch and VBS script first and go from there.

SQLSaturday #47 Postmortem

ex·haus·tion

[ig·zaws·chun]
noun

  1. extreme tiredness; fatigue
  2. the condition of being used up; consumption: exhaustion of the earth’s resources
  3. the act of exhausting or the state of being exhausted

(Dictionary.com)

Exhaustion is the first thing that comes to mind. Wow is a close second! To put it into perspective: imagine you have been tasked with instructing Amy Winehouse about sobriety… Yeah it’s gonna require a lot of time and energy! This was my first event so without any shadow of a doubt I was on pins and needles up until the end of the after party. Like anyone else you want your event to be successful from start to finish. Essentially you want things to go according to plan, in short… perfect. We all know Murphy’s law so there is no denying that things will not always go according to plan, so don’t let it bother you.

However I do feel that being experienced does help you roll with the punches much like an agile process. You are able to adjust as things come your way. That is exactly what Chris Coneybeer (blog | @coneybeer) brought to the table… experience among several other things! So I commend Chris on doing an exceptional job on adjusting to everything that presented itself.

volunteers

I want to say thank you for helping SQLSaturday #47 come together. It’s not easy and our time is valuable so thank you for taking time out of your schedule to help with the preparations of the community event. There is nothing like supporting our community.

So thank you: (in no particular order)

  • Anthony Williams (blog | twitter)
  • Qui Ly (blog | @QLy27)
  • Steve Cable (LinkedIn | twitter)

presenters

Because of your willingness to share the wealth of knowledge SQLSaturday #47 was able to offer five tracks of information packed sessions. The fact that our presenters actually participated in the event on their own dime speaks volumes about the their commitment to the community. That’s right! The flight, hotel, car rental and other incurred expenses were out of their own pocket. So thank you for your generosity and thank you for being you!

So thank you: (in no particular order)

Presenter Topic
Dale Cunningham
(blog | @fly_scuba)
Using Confio Ignite 8 for Production
Jonathan Atkins
(blog | @JonAtkins57)
TSQL Fun – Avoiding cursors
Amy Lewis
(blog | @amy_lewisAZ)
First Flight: Taking off with Analysis Services
First Flight: Earn your ETL wings with SSIS
Janis Griffin
(blog | @DoBoutAnything)
Performance Management – 2008 MDW – How & Why
Tuna Helper – Proven Process for Tuning SQL
William E. Pearson III
(blog | @Bill_Pearson)
Entity Strategies: Structuring Your Consultancy
Getting Started with MDX
Robert Miller
(blog | @RobertMiller)
Mirroring 101
Manage and Monitor Your Mirror
Colin Smith
(blog | @smithco32)
Powershell for the DBA
Michael Collins
(blog | @mfcollins3)
Database Development using Visual Studio
Introduction to Microsoft SQL Azure
Ira Whiteside
(blog | @irawhiteside)
Creating a Metadata Mart w/ SSIS – Data Governance
(SSIS, TSQL and MDS) – Record Linkage(Fuzzy Matchi
Bill Ramos
(blog | @billramo)
SQL Server Data Collection Strategies
The Making of the SQL Server Denali Always On Dash
Meredith Ryan-Smith
(blog | @coffegrl)
Work/Life Balance? Just A Myth?
Backup Basics – Knowing your Options and When to
Denny Cherry
(blog | @mrdenny)
SQL Server Clustering 101
Back To Basics; Getting Back To The Basics of SQL
Where should I be encrypting my data?
Orion Gebremedhin
(blog | @OrionGM)
SSRS-Subscriptions & Render Device Settings
SSAS Partitioning and Usage Based Optimization
John Racer
(blog | @speedracer)
Where Are My Reports? Managing Reporting Services
Empowering End Users with Report Models
Zach Mattson
(blog | @WIDBA)
Powershell Eye for the Monitoring Guy
Denise McInerney
(blog | @denisemc06)
DBA as Protector of the Data: Notes from the Field
Tara Kizer
(blog | @TaraKizer)
Performance Tuning with Traces

sponsors

Without your support SQLSaturday #47 would have never happened. Your contributions help more than you know. You provided valuable information about the many services and tools available to help us do our job better, easier and faster. Thank you for your generosity and continued support for the SQL Community!

So thank you: (in no particular order)

Website Logo
SQLPass.org
Confio.com
CozyRoc.com
FusionIO.com
Quest.com
GoDaddy.com
Idera.com
Neudesic.com
SQLMag.com
Statera.com
MelissaData.com
Gerasus.com

venue

Thanks to the generous staff at Chandler-Gilbert Community College for letting us use their awesome campus to host the event. It was a beautiful venue and we look forward to continuing our partnership for events to come!

thank you

So to the volunteers, presenters, sponsors, CGCC and to all that attended… THANK YOU!. We couldn’t have done it without you and I look forward to seeing and meeting you at the next one!!!

Needing a change… Modify your Filename

A friend of mine told me that he felt a need for a change. Without question I said cool. Change is always inviting as it keeps things interesting. Little did I know or realize he was referring to the changing of his database file name. So he issued the simple ALTER DATABASE MODIFY FILE statement and specified a new name for his database.

ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_data, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_data.mdf')
ALTER DATABASE AdventureWorks
MODIFY FILE (NAME = AdventureWorks_Log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AdventureWorks_log.ldf')

and in the results were

The file "AdventureWorks_data" has been modified in the system catalog. The new path will be used the next time the database is started.
The file "AdventureWorks_Log" has been modified in the system catalog. The new path will be used the next time the database is started.

So he took this message as needing to restart the SQL Server Engine Service. Which he did; however, that did not satisfy the underlying issue. After refreshing the databases he attempted to expand the db and received a nice little error indicating that the database was not accessible. Which is when I received the call.

Having experienced this myself I remembered that:

1. the database needed to be taken offline

ALTER DATABASE AdventureWorks SET OFFLINE WITH ROLLBACK IMMEDIATE

2. he would need to physically rename the files accordingly

3. bring the database back online

ALTER DATABASE AdventureWorks SET ONLINE WITH ROLLBACK IMMEDIATE

Once these steps were completed he was back in business. On a side note this permitted the changing of the file names but you can also change the actual file path location for each of the files as well using the same syntax. If you wanted to update the logical name for both the data and log file below is the syntax for doing so.

ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks', NEWNAME=N'AdventureWorks_data')
ALTER DATABASE [AdventureWorks] MODIFY FILE (NAME=N'AdWks_log', NEWNAME=N'AdventureWorks_log')