SQL Server 2008

Speaking in Chicago this weekend at SQL Saturday #31

Apr 12th, 2010

I’ll be speaking at SQL Saturday #31 in Chicago this weekend. I’m presenting a PowerShell for Data Professionals session. This is based off a previous session but does include some new content. If you’ve seen the session before, there will be something new and the demos will hopefully transition a little smoother. I’m working with PowerShell automation every day at work and am still discovering new tricks and finding new targets for automation.

Here are some details on the session:

The first time that I saw PowerShell I hated it. I thought it was complicated and stupid. After trying a couple of scripts I gave up on PowerShell. Then Windows 7 (and it’s nice script editor) was released and a light went on. My session takes you through Power Shell basics and then I put it in action automating tasks DBAs do daily and showing you how to check servers for some common worst practices.

There are a lot of excellent speakers at this SQL Saturday. I hope to see you there.

Start Time

Track 1

Track 3

Track 4

Track 2

08:30 AM

SQLSaturday Chicago
Welcome and Keynote

09:00 AM

Jeremiah Peschka
A Dynamic World Demands Dynamic SQL

Tim Ford
SQL DMVs, DMFs, DMOs. WTH?

David Levy
Getting Out from Behind the Curtain – The New DBA

10:00 AM

Kevin Kline
TBD

Brent Ozar
Perfmon & Profiler 101

Jorge Segarra
SQL University 201: More DBA Fun

Wes Dumey
Building a Data Warehouse using SQL Server 2008

11:00 AM

Kevin Kline
Stored Procedure Best Practices

Aaron Nelson
PowerShell for Data Professionals*

Ross LoForte
Proactive DBA: Manage SQL Server Better

12:00 PM

SQLSaturday Chicago
Vendor Session A

12:30 PM

SQLSaturday Chicago
Vendor Session B

01:00 PM

Brent Ozar
BLITZ! 60 Minute Server Takeovers

Jason Strate
Using XML to Query Execution Plans

Tim Ford
Tools for The Driven, But Lazy DBA

02:00 PM

Chuck Heinzelman Michael Steineke
Oh Cluster, My Cluster!

Jason Strate
Extended Events, Work Smarter Not Harder

Jorge Segarra
Policy Based Management 101

Adam Jorgensen
Zero to Cube in 60 Minutes

03:00 PM

Brad McGehee
How and When to Use Indexed Views

Dean Richards
Wait-Time Based Performance Management

Jeremiah Peschka
Fundamentals of SQL Server Internals

Andrew Karcher
One Developers Journey into MDX

04:00 PM

Brad McGehee
Introduction to Graphical Execution Plans

Derek Comingore
Delivering Rapid Business Value with Microsoft BI

Arie Jones
Working with Spatial Data in SQL Server 2008

Devin Knight
Analysis Services Partitions and Aggregations

05:00 PM

Alex Kuznetsov
Defensive programming with Transact SQL

Arie Jones
Reporting Services 2008 Unleashed!

Aaron King
The Benefits of SQL Azure

06:00 PM

SQLSaturday Chicago
Ask the Experts Q&A

07:00 PM

SQLSaturday Chicago
After Party

LiveJournal Tags: SQL,SQL Saturday,PASS,PowerShell,Free Training

PASS Virtual Chapters Early April Presentations

Apr 5th, 2010

by Aaron Nelson.

Comments are off for this post

In April we’ve got free training coming your way from the usual suspects of the PASS VCs (AppDev, DBA, and Virtualization). Here’s what’s on tap in chronological order:

_____________________________________________________________
Using Virtualization to Solve Common Dev/QA Environment Problems
April 7th Noon Eastern (GMT -4)
Presenters: Aaron Nelson & Dushyanth Nataraj

Isn’t two always better than one? This month the Virtualization Virtual Chapter is going to run a unique double session with two separate presentations focused on solving common problems for Development and Quality Assurance environments using virtualization technology!

Our first session will focus on using Microsoft Hyper-V to solve problems DBA’s face when trying to QA a change before moving it to production. Aaron will show you how Microsoft’s Hyper-V can streamline the process and ensure results from your QA testing that will be consistent in your production environment.

In our second session session Dushyanth Nataraj will give attendees an overview of VMware vCenter Lab Manager. You will learn how VMware vSphere and Lab Manager can help you build a internal cloud for dev/test. The session will conclude with a demo of Lab Manager running a SQL workload showcasing how you can use the product for test/dev.

About the speakers

Aaron Nelson (@SQLvariant ) http://sqlvariant.com/wordpress/
Aaron is a Senior SQL Server Architect with over 10 years experience in architecture, business intelligence, development, and performance tuning of SQL Server. He has experience managing enterprise-wide data needs in both transactional and data warehouse environments.
Dushyanth Nataraj
Dushyanth is the Technical Marketing Manager for Lab Manager. He has been with VMware 5 years and has held roles in Engineering and Technical Marketing. He holds a MS in Computer Science from the University of Kansas.

This live one-hour webcast will happen on Wednesday, April 7th at 12:00 noon Eastern, 16:00 GMT.

How do I view the presentation?
Click here to join the webcast.

Want to speak at our next event? We’re looking for database administrators who can talk about how virtualization has worked for them, what they’ve learned, and any tips they can pass on. It can be as formal or informal as you like. To volunteer, email Tim Edwards at tim_edwards@fsafood.com.
_____________________________________________________________________

PowerShell for Database Developers
April 13th at 12:00 PM Eastern (GMT -4)
Presenter: Peter Schott
Add to Calendar

PowerShell for Database Developers

Peter Schott is currently working as a Development DBA for Fellowship Technologies, using SQL Server, SSIS, and PowerShell (in his spare time). He has worked with SQL Server since 1996, designing databases, writing reports, scripting, performance tuning, and moving around lots of data. Peter blogs at Schott SQL. (And is open to better names for his blog.)

How do I view the presentation?
Attendee URL: Live Meeting link
_____________________________________________________________________

Using SQL Server 2008 Database Mirroring to Become a Super Hero!
Date: April 14, Noon Mountain Time (GMT -6)
Presenter: Glenn Berry

Abstract:
Database mirroring is a valuable technology that can be used to minimize or avoid downtime due to hardware failures, with less complexity than fail-over clustering. It can also be used for other innovative uses, such as easily migrating from SQL Server 2005 to SQL Server 2008 with a sub-minute outage. Database mirroring can be used during normal scheduled maintenance for rolling updates to minimize downtime. Database mirroring works very well for migrating very large databases from one SAN to another with minimal downtime. This session will cover all of these scenarios, with practical techniques, scripts and tips to help you take advantage of database mirroring.

Speaker:
Glenn Berry works as a Database Architect at NewsGator Technologies in Denver, CO. He is a SQL Server MVP, and he has a whole collection of Microsoft certifications, including MCITP, MCDBA, MCSE, MCSD, MCAD, and MCTS, which proves that he likes to take tests. His expertise includes DMVs, high availability, full text search, and SQL Azure.

He is also an Adjunct Faculty member at University College – University of Denver, where has been teaching since 2000. He recently completed the Master Teacher Program at University College.

Door Prize: There will be a drawing for a $50.00 Amazon Gift Certificate
Registration: You can attend the meeting without registering but if you want to be entered in the drawing, you must register at https://www.livemeeting.com/lrs/8000181573/Registration.aspx?pageName=dt7v502vq7nrtbmj no later than 5:00 PM Eastern on April 13th.

How do I view the presentation?
Live Meeting Link: https://www.livemeeting.com/cc/8000181573/join?id=Z92ZRJ&role=attend

Schedule Posted for SQL Saturday #41 – Sort of…

Apr 2nd, 2010

by Aaron Nelson.

1 comment

We’ve decided to try a different approach to SQL Saturday and get attendees to vote on what sessions they would like to see so that we can use that as a guide to figure out which size room to put sessions in. You can find the schedule as we currently have it configured here. Once we get the results of the survey we will redo the schedule based on that. I’d give you the link to the survey but unless you registered for the event you don’t get to vote so check your email if you registered.

We’re also trying something else different with our schedule, we’re going with 7 sessions in a day. If you think this is a bad idea please tell us (comment box below). We just got so many good sessions from so many good speakers that we didn’t want to leave people out.

Boxes of swag are continuing to arrive so we should have plenty to giveaway at the end. My goal is for 20% of the attendees to leave with a book. I’m half way to achieving that goal, no idea if I’ll succeed but I figured I needed something to shoot for. Currently searching for where to hold the after-party, we’re definitely open to suggestions on that.

More info to come next week!

SQL Saturday #33 PowerShell Slide Deck

Mar 12th, 2010

by Aaron Nelson.

1 comment

I’ve posted a copy of my slide deck that I used for my PowerShell sessions at SQL Saturday #33 in Charlotte. Anyone is welcome to use all or part of it if they would like to do they’re own PowerShell session. I’d love to hear any suggestions for improving it. I’m working on getting all of the scripts I used together so that I can post them up on the Scripting Guys repository.

I’m still not satisfied with the Virtualization slide deck so I’m going to continue to tweak that one and hopefully post that after SQL Saturday #29 in Birmingham.

Get More Done with SQLPSX

Feb 25th, 2010

by Aaron Nelson.

Comments are off for this post

In my LiveMeeting session for the AppDev Virtual Chapter of PASS yesterday I talked about building on top of tools that others had already built for you to use. A great one for any DBA to use is the SQL PowerShell Extensions known as SQLPSX. Even if you’ve never used PowerShell before you should take a look at this. Heck even Sys Admins in shops that don’t have a DBA should have a look at this. I have found the commands I have worked with very easy to use; even easier than T-SQL in some cases.

After you download the files and RTFM you can then you can follow along on this next part. Oh wait, first a little warning straight from Buck Woody:

Script Disclaimer, for people who need to be told this sort of thing:

Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately.

get-module -listAvailable

import-module SQLServer

Invoke-SqlBackup -sqlserver "WINX64ULT7\Kilimanjaro" -dbname "AdventureWorks" `

-filepath "C:\Temp\AdventureWorks_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"

Now this little script here (above) will backup a db for you and even include the current YearMonthDayHourMinute in the file string. This one below will backup all of the non-system databases on your instance. If you’re like me you’re thinking this doesn’t do anything that you can’t already do today with a maintenance plan. That’s true and maybe I should have titled this post “Get Something Done with SQLPSX” but I will build on how you can leverage this more tomorrow. For now, why don’t you add an AND clause only backup all the databases that start with ‘A’

foreach ($dbn in invoke-sqlcmd -query "SELECT name  FROM sys.databases WHERE owner_sid !=0x01" `

-database master -serverinstance WIN7\Kilimanjaro )

$k="C:\Temp\" + $($dbn.name) + "_db_$(((Get-Date).ToString("yyyyMMddHHmm"))).bak"WIN7\Kilimanjaro" -dbname $($dbn.name)  -filepath $k

$dbn; Invoke-SQLBackup -sqlserver "

I’ve gone ahead and included the SQLPSX help items here. Please see if there’s one that catches your eye and see if it might work for you.

* Get-AgentAlert

* Get-AgentAlertCategory

* Get-AgentJob

* Get-AgentJobHistory

* Get-AgentJobSchedule

* Get-AgentJobServer

* Get-AgentJobStep

* Get-AgentOperator

* Get-AgentOperatorCategory

* Get-AgentProxyAccount

* Get-AgentSchedule

* Get-AgentTargetServer

* Get-AgentTargetServerGroup

* Set-AgentJobHistoryFilter

* Get-ReplArticle

* Get-ReplEnumLogReaderAgent

* Get-ReplEnumPublications

* Get-ReplEnumPublications2

* Get-ReplEnumSnapshotAgent

* Get-ReplEnumSubscriptions

* Get-ReplEnumSubscriptions2

* Get-ReplLightPublication

* Get-ReplMonitor

* Get-ReplPublication

* Get-ReplPublicationMonitor

* Get-ReplPublisherMonitor

* Get-ReplScript

* Get-ReplServer

* Get-ReplSubscriberSubscription

* Get-ReplSubscription

* Get-ReplTransPendingCommandInfo

* New-ReplMergePublication

* New-ReplScriptOptions

* New-ReplTransPublication

* Add-SqlDatabaseRole

* Add-SqlDatabaseRoleMember

* Add-SqlServerRoleMember

* Get-SqlDatabasePermission

* Get-SqlDatabaseRole

* Get-SqlIndexFragmentation

* Get-SqlInformation_Schema.Columns

* Get-SqlInformation_Schema.Routines

* Get-SqlInformation_Schema.Tables

* Get-SqlInformation_Schema.Views

* Get-SqlLinkedServerLogin

* Get-SqlLogFile

* Get-SqlLogin

* Get-SqlObjectPermission

* Get-SqlServerPermission

* Get-SqlServerRole

* Get-SqlShowMbrs

* Get-SqlStatistic

* Get-SqlStoredProcedure

* Get-SqlSynonym

* Get-SqlSysDatabases

* Get-SqlUserDefinedDataType

* Get-SqlUserDefinedFunction

* Get-SqlVersion

* Get-SqlView

* Invoke-SqlBackup

* Invoke-SqlDatabaseCheck

* Invoke-SqlIndexDefrag

* Invoke-SqlIndexRebuild

* Invoke-SqlRestore

* New-SqlScriptingOptions

* Remove-SqlDatabase

* Remove-SqlDatabaseRole

* Remove-SqlDatabaseRoleMember

* Remove-SqlLogin

* Remove-SqlServerRoleMember

* Remove-SqlUser

* Set-SqlData

* Set-SqlDatabasePermission

* Set-SqlObjectPermission

* Set-SqlServerPermission

* Update-SqlStatistic

* Copy-ISItemFileToSQL

* Copy-ISItemSQLToFile

* Copy-ISItemSQLToSQL

* Get-ISData

* Get-ISItem

* Get-ISPackage

* Get-ISRunningPackage

* Get-ISSqlConfigurationItem

* Set-ISConnectionString

* Set-ISPackage

* Test-ISPath

Basic SQL Querying From PowerShell

Feb 22nd, 2010

by Aaron Nelson.

1 comment

I promise to make this worth your time. (Oops, I better make this quick.)

Alright so with PowerShell if you open it up and just run this code you’ll be able to query your database right from the start. Anyone who has worked with data systems for a while will notice right away the first line of this script kinda looks like a connection string for an application. I’m sure the fact that PowerShell was built on .Net has something to do with that. (But I’m not a developer so I don’t really know, it could be just a coincidence for all I know.

$conn = New-Object System.Data.SqlClient.SqlConnection(“Data Source=YourMachineName\YourInstanceName; InitialCatalog=master; Integrated Security=SSPI”)
$conn.Open()
$cmd1 = $conn.CreateCommand()
$cmd1.CommandType = [System.Data.CommandType]::StoredProcedure
$cmd1.CommandText =“sp_databases”
$data = $cmd1.ExecuteReader()
$dt = new-object “System.Data.DataTable”
$dt.Load($data)
$dt | format-table #PipeLining is Awesome!
$conn.Close()

But this code is so much Nicer!

If you’ve got SSMS 2008 installed on your machine all you need to do is fire up the ISE and add the SnapIn like I talked about in my last blog. Then you can use invoke-sqlcmd and pass it your query. As you see this is a lot easier and cleaner than all of the code above was.

#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100

#sp_databases | format-table
invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table

Conclusion:

Unless you really like writing 6 or 7 extra lines of code every time you query your database, load up the Snapin and use invoke-sqlcmd.

Bite Sized Blog Posts

Feb 20th, 2010

by Aaron Nelson.

Comments are off for this post

For the next several weeks (about 9) I will be really busy with a data center move and doing some presentations on PowerShell. During that time I’m sure I’ll find all kinds of cool uses for PowerShell and SQL Server and I’ll post them as quick as I can in a small bite-sized format. This will make it quick and easy for people to read and for me it will mean much less time to get a post ready. Sometimes when getting a blog post ready I will take as many a 8 screen shots of the same thing just trying to make sure that I got everything easy to understand, syntactically correct, and useful.

Once I’m done with all this (around May) I will come back and thread these posts together into a bigger story and show you how much time we saved from end to end. So far I already have about 3 of the bite-sized posts that I need to write and we’ve saved at least 4 hours on tasks that previously would have required loads of manually clicking around.

I hope this format helps people easily take advantage of what I’ve found useful between leveraging SQL and PowerShell.

btw… If you’re looking to learn more about PowerShell during your daily commute I HIGHLY recommend that you subscribe to the PowerScripting Podcast. My coworker Shyam and I are catching up on back episodes and listening to it nearly every day on the way into work. They don’t talk about SQL Server too much but what they do talk about is still very useful to us.

SQLServer:\SQL\Databases\Tables> Dir

Feb 17th, 2010

by Aaron Nelson.

1 comment

Getting started talking to your SQL Servers in the PowerShell 2.0 ISE

By now hopefully you’ve noticed that SQL Server 2008 comes with PowerShell all over the place. Just about anything you click on in Object Explorer has “Start PowerShell” and you can even execute PowerShell steps in SQL Agent Jobs.

You may be wondering why on Earth you would want to “Start PowerShell” in the first place. I’ll show you one thing today and then expand on that in later blog posts but today I wanted to tell you how to run PowerShell in something a little more organized than a command prompt window. The first thing I’d like you to do is open up SSMS, connect to an instance and Right-Click the Databases folder > then select Start PowerShell. A command prompt window should open up and from there you can talk to your SQL Server instance as if was a drive on your machine:

The SQLPS window that just opened up is PowerShell 1.0 with a special ‘expansion pack’ that allows it to talk to SQL Server in a way that PowerShell alone can’t. So now that we’ve got that covered I want to show you how to do the same thing in PowerShell 2.0’s ISE.

Go to Start > All Programs > Accessories > Windows PowerShell > Windows PowerShell ISE

Copy the code from the bottom and step through it like I did in this picture. When you’re done you will be able to drill down to tables like they were just folders directories on your hard drive. If you aren’t running Windows 7 (upgrade already!) check this post and see if you’re operating system is eligible.

When you get to the part where you do “get-psdrive” for the second time you should now see the SQL Server on your local machine. If you haven’t figured out yet, this is only going to work if you have SQL Server installed on your local machine.

#Before
get-psdrive

#Snapins that are running
get-pssnapin
#Snapins that you can load
get-pssnapin -registered

#Go ahead and add the SQL Snapins
add-pssnapin SqlServerCmdletSnapin100
add-pssnapin SqlServerProviderSnapin100

#New Resource
get-psdrive

#What can we do with that?
cd SQLSERVER:\SQL\YourComputerName\YourInstanceName\DATABASES\ADVENTUREWORKS\TABLES

Now that we have done all of that we can do something that I have seen mentioned several places but I think that Allen White’s blog post explains it the best (here’s my version of it). You can now create a variable and populate it with one the DDL structures in your database and from there generate a Create script for it like so:

$PTH = get-item Production.TransactionHistory
$PTH.Script()

Your output should look something like this:
PS SQLSERVER:\sql\WIN7\DATABASES\ADVENTUREWORKS\TABLES> $PTH = get-item Production.TransactionHistory
$PTH.Script()
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory](
[TransactionID] [int] IDENTITY(100000,1) NOT NULL,
[ProductID] [int] NOT NULL,
[ReferenceOrderID] [int] NOT NULL,
[ReferenceOrderLineID] [int] NOT NULL,
[TransactionDate] [datetime] NOT NULL,
[TransactionType] [nchar](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Quantity] [int] NOT NULL,
[ActualCost] [money] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

And that’s it for this edition of how to do something when you open up the PowerShell 2.0 ISE. Next we’ll do something more useful I promise.

SQL Server 2008

But this code is so much Nicer!

Conclusion:

Getting started talking to your SQL Servers in the PowerShell 2.0 ISE

Search

Tags