Providers What are Providers? Providers in PowerShell are a hierarchical namespace way flattening out an infrastructure to make it accessible and traverse-able just like a directory structure hard drive. Why do you need them? Well you quite possibly may not ‘need’ them per-say but they sure do make things easier to work with from time to time. In case you don’t already know, you can traverse your SQL Server [2008] as if it were just another drive in either SSMS 2008 or in the PowerShell ISE if you add the Provider Snapin. To make sure that you have this snapin installed on your machine see yesterday’s post but this time we are going to add the ProviderSnapin instead of the CmdletSnapin. To do that just run add-pssnapin SqlServerProviderSnapin100 and poof you can traverse SQL Server. Let’s take a look at what we might see. Run get-psdrive and you should see a list of drives including SQL Server. Now if you have more that one instance you will be able to access all of them through this one “SQLServer:\” PSDrive that you now have. In a lot cases what we find inside of these drives will be similar to what we see in the Object Explorer view in SSMS but almost always have something extra too. Let’s take a look at that here by changing directories down to our databases cd SQLSERVER:\sql\YourComputerName\YourInstanceName\ cd SQLSERVER:\sql\WIN7\KILIMANJARO\ Now let’s go ahead and run the good old “Dir” command and we should get back something like this dir As I mentioned before, we got back more than we see if we expand our SQL Server node in the Object Explorer window of SSMS, now if we CD to Databases and do another dir we will see not just more than what we see in SSMS, we see what at first glace to a SQL person is just garbage (but it’s not). cd Databases dir What you’re seeing here is the Methods and Properties of the databases in your instance. What are Methods and Properties? Well if you’re like I was 6 months ago you have no idea what Methods and Properties are. Methods are the Verbs of what you can do to your database (Create, Rename, Drop, Shrink) and Properties and the Adjectives that describe your database (Collation, CreateDate, Compatibility Level, Owner, AutoShrink Enabled). To get just the list of the names of your databases you’re going to have to run this: dir | select-object name Ok so now we can see a list of databases that our Provider made available to us. So what? Well I’m going to dive a little deeper tomorrow but for now let’s just go with one final example on scripting out tables (more on that here). Run this: cd AdventureWorks\Tables Then this: foreach ($tbl in dir ) { $k=”C:\Temp\” + $($tbl.name) + “_table.SQL” $tbl.Script() > $k } I really hope you have temp directory on your C:\ drive. If so go have a look what’s there now So is that it? Almost. While SQL Server 2008 only comes with one provider and it’s for the Relational Engine a few people have taken it upon themselves to create their own Provider for things like Analysis Services, Reporting Services, and event BizTalk up on the codeplex site. I can say that I have tried the Analysis Services project and it does work. del.icio.us Tags: cmdlets,functions,PowerShell,snapins,SQL Server 2008,SQL University
May, 2010:
PowerShell Week at SQL University – Post 2
Cmdlets, Functions and Snapins So far we’ve learned how to open up PowerShell and add on to it with some modules; then we learned how to create a variable and populate it with anything from some numbers to an entire table. Today we’re going to quickly look at another way of expanding PowerShell with something called a snapin so that we can get to some cmdlets. First, what are cmdlets? Cmdlets are similar to DOS commands but have some noteworthy differences. In SQL Server terms you might think of cmdlets as the System Stored Procedures of PowerShell and think of functions as the regular user defined Stored Procedures of PowerShell. Cmdlets and functions accept parameters just like stored procedures do. Just like stored procedures you can even define default values for these parameters in case the user doesn’t have one to pass in. Unlike stored procedures though there is actually a drive that you can go to and see all of your functions. Just run this and you’ll see what I mean dir function: Snapins are akin to finished product code. They were created for PowerShell 1.0 and were succeeded in PowerShell 2.0 by modules– which are more like the manifest list for deploying a bunch of SSIS packages. If you have SSMS 2008 installed you’ve got yourself two extra snapins already. And if you have Idera’s SQL Diagnostic Manager installed you may be in for a real surprise when you retrieve your list of snapins. Run this command to see all of the extra snapins that are available to you: get-pssnapin –registered At the very least you should see this: If you don’t see those two you’ll need to install SSMS 2008. If you don’t have a copy of that handy you can get the free version of it here. The SqlServerCmdletSnapin100 snapin give us the 5 cmdlets that come with SQL Server 2008 and today we’re going to focus on Invoke-SQLcmd which basically just calls out to good old sqlcmd. To load this just run: add-pssnapin SqlServerCmdletSnapin100 From there we can do a quick query like this: invoke-sqlcmd -query “sp_databases” -database master -serverinstance YourServerName\YourInstanceName | format-table invoke-sqlcmd -query “sp_databases” -database master -serverinstance WIN7\Kilimanjaro | format-table Now the reason that I brought up functions is because they override cmdlets and that’s really important to know. If you run this command get-command invoke-sqlcmd you will only see one thing returned for invoke-sqlcmd. However if we create a new function like: function invoke-sqlcmd { get-date } You end up overriding the Invoke-SQLcmd with your new function. Give it a try, run just this invoke-sqlcmd and you should end up seeing something like this: So how do you know when you’ve done something like that? Just run get-command invoke-sqlcmd again and this time you will see this: I realize that we kind of rushed through that info but thankfully there is only one more piece to the basics before we can get on to code that you can use on a daily basis del.icio.us Tags: SQL Server 2008,PowerShell,SQL University,snapins,cmdlets,functions
PowerShell Week at SQL University – Post 1
Getting Started With PowerShell Variables Yesterday we laid the ground work for PowerShell Week. Today we will learn about Variables in PowerShell. You will notice that PowerShell variables work very differently from SQL Server variables. The evolution of variables in SQL Server is pretty straight forward. In SQL Server 2000 you had to DECLARE a variable as a specific type in one statement and then SET the value of the variable in another statement, like this: DECLARE @SQLvariable VARCHAR(12) SET @SQLvariable = ‘OldFeature’. By the time SQL 2008 hit the streets we could DECLARE and SET a variable in one statement like this: DECLARE @SQLvariable VARCHAR(12) = ‘NewFeature’ . PowerShell evolves variables to the next logical step. You no longer need to declare or type your variable. Instead PowerShell uses the value of the variable to infer it’s type. If a variable does not alreayd exist PowerShell simply creates it for you. In PowerShell our statement looks like this: $SQLvariable = ‘NewFeature’. (Beginer tip: $ identifies variables in PowerShell just like @ identifies them in SQL Server.) Since it doesn’t work in SQL let’s try it in PowerShell: $SQLvariable = ‘NewFeature’. OK so we loaded data into a variable, ‘big deal’ right? How do we know that we actually loaded it and more importantly what datatype is it? Well in PowerShell you can simply call the variable to get its value. (Another beginer tip: you don’t need to use a keyword like SELECT for this, just the variable name: $SQLvariable.) What if you just want to determine the datatype? All you have to do is tack on the .GetType() Method: $SQLvariable.GetType(). $SQLvariable = ‘NewFeature’ $SQLvariable $SQLvariable.GetType() When you run this here’s what your result will be: As I alluded to earlier, another feature of PowerShell is that it remembers your variables for you, even between executions. Basically as long as you don’t end your session the variable, it’s datatype, and its value will persist. You will see later than you can also set a variable in one script and consume it in another. Let’s try this again with a new variable and set it to a different value: $PoShvariable = 123 $PoShvariable $PoShvariable.GetType() What happens if once the datatype is set we try to set it to a different, incompatible datatype? It’s going to explode right? Nope, you’ll have to throw something more difficult than that at it. Give this a try: $PoShvariable.GetType() $PoShvariable = ‘NewFeature’ $PoShvariable $PoShvariable.GetType() Since PowerShell relies on .NET a variable could be any datatype in .NET so if you’re sitting inside the AdventureWorks database you can see for yourself, just run this: $MyTable = get-item TABLESProduction.TransactionHistory $MyTable.GetType() $MyStoredProc = get-item StoredProceduresHumanResources.uspUpdateEmployeePersonalInfo $MyStoredProc.GetType() Now earlier I mentioned that PowerShell keeps track of and remembers the variables that you previously declared. So how do you see what variables are already there? Just run this command and you can see every variable you have running in the session, even the internal ones. Variable In the list of items that get returned you’ll see one called Error that holds the last error message that occurred in your session. This handy feature can help you when you have to debug your own work. Is that all there is to know about variables in PowerShell? Not hardly, but I think that’s a good stopping point for this post. More on variables later. del.icio.us Tags: SQL Server 2008,PowerShell,SQL University,Modules,Execution Policy,SQLPSX
PowerShell Week at SQL University – Post 0
Welcome to PowerShell Week at SQL University. For regulars at SQLvariant, SQL University is something Jorge Segarra organized to get industry experts together to create learning courses around SQL Server. These learning courses are in one week segments that focus on a specific area. When I began blogging about PowerShell there were some complications based on your operating system. There were so many people blogging about these issues that I didn’t address it initially. Since this is an introductory course I decided to go ahead and spin up a brand new Windows 7 VM so that I can walk you through these small but vital details. If you don’t have Windows 7 this would be a great time to upgrade. If you can’t upgrade then download PowerShell for you operating system from this link. Now, onto getting started: Disclaimer: PowerShell opens in a protected state as a security measure. To enjoy the full power of PowerShell (and run scripts you downloaded off of the internet) you need to run without these safeguards. In today’s lesson we are simply launching PowerShell and priming it for the scripts and work we will interact with in the next several lessons. To get started with PowerShell just click the Start button and type in ISE to locate the Integrated Script Editor in your program listing. Right click and launch PowerShell in Run as Adminsitrator mode: Enter Get-ExecutionPolicy as a command. If you haven’t used PowerShell yet you will most likely see that the Execution Policy is set to “Restricted”. You’ll need to run the command Set-ExecutionPolicy RemoteSigned to be able to run the scripts and follow along with the lesson. You’ll also get a popup that you’ll have to say yes to. Your output should look very non-informative like this: Now that we have that set let’s go to SQLPSX.CodePlex.com and and download the SQLPSX project. It’s currently in version 2.2.1 and consists of 9 modules containing 133 advanced functions, only 2 cmdlets and 7 scripts. It’s for working with ADO.NET, SMO, SQL Agent, RMO, SSIS, SQL script files and using the PowerShell ISE as a SQL query tool: NOTE: Update at bottom with workaround: During the installation we will run it without modifying the profile (just yet). Once you’ve installed it successfully you should now have a WindowsPowerShell directory under your My Documents if you didn’t have one already (which is really important): After it’s installed we should be able to open up the ISE again and run this command : Get-Module -ListAvailable And finally we’re going to get to the step where we load something up. Run import-module SQLServer to load up the main SQL Server module. After you run that command it won’t give you any output telling you that it successfully loaded so you can run this command write-host -foregroundcolor DarkGreen “Modules Loaded: $(get-module)” to have it tell you what Modules it currently has loaded. Alright that pretty much wraps it up for today. I know we didn’t do a lot with PowerShell and SQL Server yet but this gets some important ground work out of the way so that hopefully everyone is able to script along for the rest of the week. (If you’re dying to read ahead have a look at this post: Basic Querying From PowerShell) If you run into any issues please leave a comment and I’ll be glad to lend any help that I can. See all you back here tomorrow for the next post in this series Post 1. UPDATE: There seems to be an issue with loading the SQLServer Module as I had described. I have found this to be a work-around: Go to \\~\My Documents\WindowsPowerShell\Modules\SQLIse\Modules\ and copy everything except for the SQLISE folder, then go to the \\~\My Documents\WindowsPowerShell\Modules\ folder and paste all of those items. At this point you should now be able to run the command and get it to load up properly. I will post more when I found out other alternatives. del.icio.us Tags: SQL Server 2008,PowerShell,SQL University,Modules,Execution Policy,SQLPSX
Atlanta PowerShell User Group May Meeting
Please come join us for our third meeting When: Tuesday May 18, 2010 at 6 pm Where: 211 Perimeter Center Parkway, Suite 200, Atlanta, GA 30346 How: Please RSVP so we can get a head count What: People from all over Atlanta getting together to talk PowerShell What is a script club you ask? You bring an idea for a script, and ask your fellow PowerShell users for help getting the script written. If it’s PowerShell, its covered — just bear in mind that it may be hard to test things like Exchange scripts, Active Directory, or SQL Server management scripts unless you have a nice virtual lab on your laptop. What are the Rules of Script Club? You always talk about script club You always talk about script club If someone asks for help, and you can help, you help Two people help one person at one time One module per person per night All scripts, all PowerShell Scripts will be as short as they can be If this is your first time at Script Club, You have to script! Please RSVP so we can get a head count. Our meeting is graciously sponsored by PowerShellCommunity.org. del.icio.us Tags: PowerShell,Atlanta,User Group
The Scripts
Last week I did a podcast for geekSpeak and a similar session at SQL Saturday #38 in Jacksonville. I’ve already blogged about most of the scripts in those sessions but here is a download of a bunch of those scripts. I have changed them a little from the way they were written in my session so that they can run independently without worrying about whether or not you need to load a snapin or module. If you need it to run the script, it’s in there. Don’t forget that for all but one of them you will either need to have SSMS 2008 installed or the SQLPSX codeplex project. If you don’t remember which script it was that you were interested in, here’s the recording of the session that I did for geekSpeak. Enjoy! Oh, and don’t forget Buck Woody’s disclaimer about running scripts you find on the internet: 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. del.icio.us Tags: PowerShell,geekSpeak,MSDN,Channel 9,SQL Saturday
Early May PASS VC Presentations
Next week is the 24 Hours of SQL Server 2008 R2 PASS but before we get there the Virtual Chapters of PASS have some free training on tap for us: _____________________________________________________________ “You Got XML In My Database? What’s Up With That?” May 11th 12:00 PM EDT (GMT -4) Add to Calendar Presenter: Stuart Ainsworth A brief presentation exploring the marriage of XML and relational databases, including when it works and when it doesn’t. Coverage will include various use case scenarios, and some tips on how to improve performance using design techniques. Stuart Ainsworth: Stuart I Ainsworth, MA, Med is a Database Architect working in the realm of Financial Information Security; over the last 15 years, he’s worked as a Research Analyst, a report writer, a DBA, a programmer, and a public speaking professor. He’s one of the chapter leaders for AtlantaMDF, the Atlanta chapter of PASS. A master of air guitar, he has yet to understand the point of Rock Band (“You push buttons? What’s that all about?”). How do I view the presentation? Attendee URL: Live Meeting link _____________________________________________________________ Indexing Guidelines May 12, 2010 Noon Mountain Time (-6 GMT) Presenter: Greg Larsen This session will discuss indexing guidelines. During this discussion there will be information about how developers and DBA’s should approach the indexing development lifecycle. This lifecycle will help attendees understand that you shouldn’t just develop some indexes while you are developing your applications and then forget about them. In addition some indexing guidelines will be discussed. These guidelines will help you make better choices on which columns to select for your indexes and how those columns should be ordered in multi-column indexes. There will be number of demonstrations to support this discussion. Lastly an indexing toolkit (a set of scripts) that you can use to help manage your indexes will be provided. Greg Larsen: Greg Larsen has been working with SQL Server since 1999. He has authored over 125 articles related to SQL Server. He holds a MCITP Database Administrator and Developer certification for SQL Server 2005 and is a SQL Server MVP. Greg also hosts the www.sqlserverexamples.com website which contains a number of T-SQL examples to help you manage your SQL Server environment and application T-SQL code. Greg has a full-time DBA gig, but also provides SQL Server consulting services in his spare time. 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=jhh2nc28185sd034 no later than 5:00 PM Eastern on May 11th. Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=CPN75W&role=attend _____________________________________________________________ 12 Key Development Areas May 13th 1:00 PM EDT (GMT -4) Presenter: Andy Warren A good professional development plan has to go far beyond ‘learning more technology’, it has to encompass the wide variety of skills you need to build the next step in your career, and it has to include a variety of learning methods. We’ll discuss how much time and money you should budget when building your plan, and how the distribution of time and money changes as your career evolves. We’ll look at the growth phases you’ll encounter, learning how you learn best, how to keep up with new technology without burning out, and even how things like blogging and Twitter can be an interesting part of your plan. Andy Warren Andy Warren (@SQLAndy) is a SQL trainer focusing on basic administration and performance tuning, he runs the free SQLShare.com training site, is currently a SQL Server MVP, blogs daily at SQLAndy.com, started the SQLSaturday franchise, is co-President of the Orlando SQL Server Users Group, serves as a member of the Board of Directors of PASS, and was a founding partner in SQLServerCentral.com. In his remaining free time he’s working on a book for first time managers and squeezes in some woodworking for relaxation. Live Meeting Link: https://www323.livemeeting.com/cc/usergroups/join?id=5233Z9&role=attend _____________________________________________________________ Extract, Transform, and Load your Data Warehouse May 14, 2010 12:00 PM EDT (GMT -4) Add to calendar Presenter: Jessica M. Moss One data warehouse buzzword that often gets used is “ETL”, also known as extract, transform, and load. But what does this really mean from a data warehousing perspective and how do we use SQL Server Integration Services to perform these ETL functions? This session will answer these questions and show solutions to common ETL problems. Jessica M. Moss Jessica M. Moss, an architect with Ironworks Consulting and a Microsoft SQL Server MVP, is a well-known practitioner, author, and speaker in Microsoft SQL Server business intelligence. Jessica has created numerous data warehousing solutions for companies in the retail, internet, health services, finance, and energy industries and authored technical content for multiple magazines, websites, and the book “Microsoft SQL Server 2008 Integration Services Problem-Design-Solution”. Jessica enjoys working with the central Virginia community and speaks regularly at user groups, code camps, and conferences. You can read about her work on her blog, http://www.jessicammoss.com. Live Meeting Link: https://www.livemeeting.com/cc/usergroups/join?id=CPPC3S&role=attend _____________________________________________________________ del.icio.us Tags: PASS,VC,AppDev,DBA,Prof-Dev,Free Training,SQL Server
quick post checking mount points with PowerShell
Having problems with my blog feed so this is just a temporary post. I hope to get things fixed this weekend. Does anyone know how to fix a blog feed that is bigger than 512k? This code here will loop through a list of servers that you supply it in a text file and then spit out only the Mount Points on those servers that have less than 20% free space remaining on them. foreach ($svr in get-content “C:\PowerShell\AllServers.txt”) { $svr; Get-WmiObject Win32_PerfFormattedData_PerfDisk_LogicalDisk -ComputerName $svr ` | where{$_.Name -ne “_Total” -and $_.PercentFreeSpace -lt 20} | select-object Name, PercentFreeSpace | format-list }