Tag: powershell

  • How to Install the PowerShell Cmdlets for Apache™ Hadoop™-based Services for Windows

    How to Install the PowerShell Cmdlets for Apache™ Hadoop™-based Services for Windows

    Small Bites of Big Data

    Cindy Gross, SQLCAT PM

    UPDATED JUNE 2013 – The very early version of PowerShell cmdlets I discussed below have been replaced – see Managing Your HDInsight Cluster with PowerShell http://blogs.msdn.com/b/carlnol/archive/2013/06/07/managing-your-hdinsight-cluster-with-powershell.aspx

    We have a cool new addition to Microsoft’s Apache™ Hadoop™-based Services for Windows – new PowerShell cmdlets! The initial readme.txt is not very detailed so I went through the installation and initial use step by step to help get your started. Enjoy this new way to remotely administer your Hadoop cluster!

    1)      Log in to your Windows client (the location where you plan to do remote administration of your Hadoop cluster) with a local administrator account.

    2)      Download the Hadoop PowerShell Cmdlets zip file from your Apache™ Hadoop™-based Services for Windows cluster: https://YourHadoopCluster.cloudapp.net/Home/Downloads(substitute your actual Hadoop Cluster name for “YourHadoopCluster”).

    3)      Unzip the files to C:WindowsMicrosoft.NETFramework64v4.0.30319 (assuming an x64 OS and an installation on C:).

    4)      Change PowerShell to use .NET 4.0 (by default it uses an older version that does not work with these cmdlets)

    • Open “Windows PowerShell” (choose the x64 version – the one that does not show (x86) after the name).
    • Type $pshome and capture the result such as “C:WindowsSystem32WindowsPowerShellv1.0.”  This directory is for an x64 installation of PowerShell, the SysWOW64 directory is for x86/32bit.
    • Close PowerShell.
    • Open a dos-prompt or Windows Explorer with “Run as administrator” and navigate to the directory you found above (i.e. C:Windowssystem32WindowsPowerShellv1.0)
    • If no powershell.exe.config file exists create an empty file with that name in that directory. Edit powershell.exe.config to add the following section:
      <?xml version=”1.0″?>
      <configuration>
      <startup useLegacyV2RuntimeActivationPolicy=”true”>
        <supportedRuntime version=”v4.0.30319″/>
        <supportedRuntime version=”v2.0.50727″/>
      </startup>
      <
      /configuration>

    5)      Install the SDK cmdlets

    • Open a “Command Prompt” with the “Run as administrator” option. Go to your .NET 4.0 directory which will be something like C:WindowsMicrosoft.NETFramework64v4.0.30319.
    • Run:
      installutil.exe Isotope.Sdk.Cmdlets.dll
    • Review the output to verify there were no errors during the install (look for things like commit instead of rollback and no errors).

    6)      Open a new Windows PowerShell window (make sure you choose the x64 version on an x64 OS) and type 

                Add-PSSnapin IsotopeSdkPSSnapIn

    Verify it completes successfully.

    7)      Type a set of commands in the PowerShell window to verify the cmdlets work. For example set which cluster the other commands apply to (change to your actual names/password), check that the settings are correct, then get a file listing or the user directories:

    Set-ClusterSettings -ClusterDns <cluster name> -UserName <username> -Password <password>
    Get-ClusterSettings
    Get-FileListFromHdfs -HdfsPath hdfs:///user/

    8)      Once the cmdlets are installed and you have verified it is working you can add the SnapIn to the profile so you don’t have to add it every time you open PowerShell. Details on PowerShell profiles can be found here, a summary is below.

    a.       Open PowerShell with “Run as administrator” (required if you are changing the execution policy or creating a new profile)

    b.      Allow scripts you’ve self-created to be run without being signed (or you can choose to sign the ps1 you create later):

    Set-Executionpolicy -ExecutionPolicy RemoteSigned

    c.       See if a Profile already exists: test-path $profile

    d.      If no Profile exists, make a new one that’s available to all users (this assumes x64):

    new-item -path $env:windirsystem32WindowsPowerShellv1.0profile.ps1 -itemtype file -force

    e.      Edit the file you just created. Open Notepad with “Run as administrator”, paste in:

    Add-PSSnapin IsotopeSdkPSSnapIn

    f.        Optionally add the default Hadoop cluster (use your actual cluster name, username, password)

    Set-ClusterSettings -ClusterDns <cluster name> -UserName <username> -Password <password>

    g.       Save as C:Windowssystem32WindowsPowerShellv1.0profile.ps1 (choose “all files” so it does not append “.txt”).

    h.      The next time you open PowerShell you will not have to add the snapin or set your default cluster  you want to manage.

    The full list of cmdlets is in the readme.txt. As of this time the list includes:

    Set-ClusterSettings -ClusterDns <hadoopcluster.cloudapp.net or hadoopcluster> -UserName <username> -Password <password>

    Get-ClusterSettings

    New-HiveJob -Type query -Execute “SELECT * from HiveSampleTable limit 10;” [-Define <# delimited key=value pairs>]

    New-MapReduceJarJob -Jar c:appsdisthadoop-examples-1.1.0-SNAPSHOT.jar -Arguments “pi 16 100” [-Define <# delimited key=value pairs>]

    New-MapReduceStreamingJob -Input “/example/data/gutenberg/davinci.txt” -Output “/example/data/streamingoutput/wc.txt” -Mapper cat.exe -Reducer wc.exe -File “hdfs:///example/apps/wc.exe,hdfs:///example/apps/cat.exe” [-Define <# delimited key=value pairs>]

    Get-JobStatus -JobId <isotope-jobId>

    Get-FileListFromHdfs -HdfsPath hdfs:///user/

    Copy-File -LocalPath <file> -HdfsPath hdfs:///<path>

    Get-JobHistoryList -NumberOfItems 2

    Get-JobHistory -JobId <isotope-jobId>

    Get-JobHistoryCount

    Set-AsvCredentials -AccountName <accountname> -AccountKey <accountkey>

    Get-AsvCredentials

    Remove-AsvCredentials

    Get-S3Credentials

    Set-S3Credentials -AccessKeyId <accesskeyid> -AccessKey <accesskey> -IsS3N <0|1>

    Remove-S3Credentials

     

    I hope you’ve enjoyed this small bite of big data! Look for more blog posts soon on the samples and other activities.

    Note: the CTP and TAP programs are available for a limited time. Details of the usage and the availability of the CTP may change rapidly.

  • SQL PowerShell Demo – Intro

    SQL PowerShell Demo – Intro

    Cindy Gross, Dedicated Support Engineer  

    Audience: SQL Server DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

    0.     Prerequisites

    1.     Install Basics

    • Check what version of PowerShell you currently have on your desktop.
      • Run PowerShell.exe with the “run as administrator” option
      • Get the version by executing the following in the PowerShell window:

    # Get the version

    $host.version

    2.     Hints

    • To paste into a PowerShell window (or a dos-prompt), copy your source then right click in the PowerShell window to paste. If you paste something that has a bullet point you will need to delete the o: or hidden character/space at the beginning before you hit enter.
    • To copy from a PowerShell window (or a dos-prompt), right click in the menu bar at the top. Choose edit.mark and highlight a “box” of data. You’re basically drawing from one corner to another, not picking individual words. Then without clicking anywhere else within the window, right click on the menu bar again and choose edit.copy. Then paste wherever you need to.
    • The pound sign (#) indicates a comment. You can cut/paste the demo steps including the lines with #. However, be careful about pasting an entire section at once, it will execute them all and the results may scroll off the screen.
    • For my SQL Server connections in this demo, I have used localhost to indicate the default instance on my local box. If you want to connect to a named instance, to an instance on another box, or to any clustered instance, replace localhost with the instance name (i.e. SERENITYHOMEInstance 1 or SomeRemoteSQL or localhostInstance1). Note that localhost is equivalent to a period (.) or (local).
    • Within SQLPS even a default instance needs an “instance” name specified and for a default instance the name is DEFAULT. For example this is the default instance on my local box: SQLSERVER:SQLlocalhostDEFAULTDatabasesAdventureWorks2008R2TablesPurchasing.Vendor
    • To change the dimensions of the PowerShell window (current and future), right click in the menu bar and choose properties. Here you can change the height, width, font, etc.
    • Many basic dos commands still work, such as cls to clear the screen.

    3.     Explore Basic PowerShell Functionality

    • Run PowerShell.exe from the start menu with the “run as administrator” option. PowerShell should be in your path, but if you need to find the source location, look in a path similar to C:WindowsSystem32WindowsPowerShellv1.0.
    • Check your security level. I have put the # sign (comment) on the lines that are not PowerShell commands so that you can cut/paste the entire section into PowerShell or a script. See the hints section on how to cut/paste.

    # Run this command to see what your security level is:

    Get-ExecutionPolicy

    # For information about execution policies:

    get-help about_execution_policies

    # If the execution policy is restricted then change it to unrestricted, RemoteSigned, or whatever level

    # is most appropriate http://technet.microsoft.com/en-us/library/ee176961.aspx

    # Example of changing the execution policy: 

    Set-ExecutionPolicy unrestricted

    • In the PowerShell window run these commands and review the output. This is just a small sampling of the information available via Windows cmdlets.

    # List of available commands and info about each:

    get-help

    # “Dir” is an alias for get-childitem so we see the help info for get-children when we pass in dir:

    get-help dir

    # “Ls” is also an alias for get-childitem:

    get-help ls

    # The “full” option includes examples, parameters, etc.:

    get-help dir –full

    # Returns one row per running instance of sqlservr.exe:

    get-process sqlservr

    # Returns one row per running instance of sqlservr.exe with different output:

    get-process sqlservr -fileversioninfo

    # one row per service with SQL in the name:

    get-service *sql*

    # system path from environment variables/set:

    $env:path

    # All environment variables that start with proc:

    Dir env:proc*

    # List of all PowerShell “drives” including basic regkey hives:

    Get-psdrive

    # Look at a registry key, in this case we can see which SQL instances are installed:

    # Note that you can leave out HKEY_LOCAL_MACHINE since that is covered by the switch to hklm

    # It appears that the HKEY_LOCAL_MACHINE part of the name is case sensitive

    #32bit instances on an x64 box have keys in the Wow6432Node key

    CD hklm:
    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

    dir “HKEY_LOCAL_MACHINESOFTWAREWow6432NodeMicrosoftMicrosoft SQL ServerInstance Names”

    4.     Formatting

    # Look at some of the output format options

    # Switch to the registry

    CD hklm:

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property name, property

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names” | format-list –property *

    5.     Run a PowerShell Script

    # Change to the directory where the PowerShell script resides

    cd c:installsPowerShelltest

    # Specify the current directory with a period (.) and call the PowerShell script

    .getsysproperties.ps1

    • You can also run the PowerShell script from a dos-prompt
      • From start, execute cmd with “execute as administrator”. # is not a valid comment indicator in dos, so use REM instead.

    REM Execute the script

    cd c:installsPowerShelltest

    PowerShell.exe -noexit .getsysproperties.ps1

    • (Optional) Save one or more of the earlier PowerShell cmdlet calls in a .ps1 file and test executing them.
    • Close your PowerShell window and CMD window.

    6.     Run a SQL PowerShell cmdlet

    • On a box with the SQL 2008 client tools installed, run sqlps.exe with the “run as administrator” option which opens a PowerShell mini-shell window (it will look like a normal dos-prompt but have “PS SQLSERVER:>” at the prompt). Run these commands and explore the output (where you see localhost you can enter your own SQL Server serverinstance name)

    # Look at the syntax and options for the cmdlet that lets you execute any SQL query

    Get-help invoke-sqlcmd

    # Connect to SQL and return the version information

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

    # The output was cut off, let’s try a different format

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version” | format-list

    # Close the sqlps window

    • From SSMS 2008+, right click on any SQL Server instance name and choose “start PowerShell”. You will see a dos-prompt open with something like PS SQLSERVER:SQLSERENITYHOMEDEFAULT (my instance =is a default instance on the server SERENITYHOME). That line shows the context, I no longer have to specify the instance name for invoke-sqlcmd. I do still have to specify a database if I want a context other than my default database.

    # No instance name is specified, and it puts me in the default database for my Windows auth login

    invoke-sqlcmd -Query “select @@version, db_name()”

    # I can specify a specific database as a parameter

    invoke-sqlcmd -Query “select @@version, db_name()” –database tempdb

    # Close the “SQL Server PowerShell” window

    • From SSMS 2008+, right click on any SQL database or SQL database object and choose “start PowerShell”. When I do this for AdventureWorks2008.HumanResources.Department I see a dos-prompt open with PS SQLSERVER:SQLSERENITYHOMEDEFAULTDatabasesAdventureWorks2008TablesHumanResources.Department>). That’s the context, I no longer have to specify the instance name or database name for invoke-sqlcmd. I can then change to another database and/or another SQL Server (in this case I switched to the Kaylee instance and the AdventureWorks database).

    # The database context is the database I clicked on in SSMS

    invoke-sqlcmd -Query “select @@servername, db_name()”

    # We can change to another db (if you do not have AdventureWorks, choose another database)

    cd SQLSERVER:sqlserenityhomekayleedatabasesAdventureWorks

    # Now look at the current database context

     invoke-sqlcmd “select @@servername, db_name()”

    # Close the “SQL Server PowerShell” window

    7.     Execute from a SQL Agent Job

    • In SSMS, go to SQL Server Agent.Jobs and right click to create a new job called Test PS.
    • On the Steps page create a new job step and set the “type” = “PowerShell”.
    • I used “Run as” = “SQL Server Agent Service Account”, make sure whatever account you use has sufficient permissions.
    • Command = invoke-sqlcmd -serverinstance localhost -database TempDB -query “select getdate() AS RunTime, @@servername AS ServerInstance, db_name() AS DBName” > c:temppstest.txt
    • Note that if you don’t specify the ServerInstance parameter it will try to connect to the local default instance even if you run from a job on a named instance. The above command will connect to the local default instance, change it to another instance if needed.
    • Save and run the job.
    • Run notepad c:temppstest.txt and see that it outputs the expected ServerInstance and DBName.
    • Note that each job step opens its own sqlps.exe which takes about 20-40MB of memory.

    8.     SQL from Windows PowerShell

    • Close any existing PowerShell windows.
    • Run PowerShell.exe.
    • Try to switch to the SQL mini-shell and it will fail

    # Change to the SQL mini-shell

    Cd sqlserver:

    • In PowerShell, run sqlps to load the SQL provider and cmdlets.

    # Load SQLPS

    sqlps

    • Try to switch to the SQL mini-shell and it now works

    # Change to the SQL mini-shell

    Cd sqlserver:

    • You can now do things like get-help invoke-sqlcmd because loading sqlps loaded the SQL cmdlets.

    # Test a SQL cmdlet

    invoke-sqlcmd -ServerInstance localhost -Query “select @@version”

    • BOL topic “Running SQL Server PowerShell” has other ways to load the SQL provider/snap-ins and SMO to Windows PowerShell.

    9.     Run a SQL PowerShell Script

    Open notepad and create c:tempTestSQL.ps1 (make sure .txt is not added to the end). Paste in this text:

    # get a list of instances on this server

    cd hklm:

    dir “HKEY_LOCAL_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerInstance Names”

    # see what we can admin

    cd sqlserver:

    ls

    # see what the machine name is

    cd sql

    ls

    # issue a query

    invoke-sqlcmd -ServerInstance localhost -query “SELECT top 5 name FROM sys.databases ORDER BY name”

    • In Windows PowerShell load the SQL cmdlets and then execute the SQL PowerShell script

    # Load the SQL cmdlets

    Sqlps

    # Change to the C:temp folder

    Cd c:temp

    # Execute the PowerShell script

    .testsql.ps1

    • By default the format is slightly different for some of the output when executed from a script.

    10.     Cleanup

    • Close the PowerShell window.
    • Delete c:temptestsql.ps1 and c:temppstest.txt
    • Delete the SQL Agent job you created.

    My other PowerShell blogs:

    Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

    PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

  • PowerShell for SQL Server – Basics

    PowerShell for SQL Server – Basics

    Cindy Gross, Dedicated Support Engineer

     Audience: SQL DBAs and/or developers who are familiar with basic SQL Server commands, SQL Server Management Studio (SSMS), and SQL Agent jobs.

    Basics

    • PowerShell processes objects based on .NET.
    • Cmdlets are named in a Verb-Noun format such as Start-Service and Get-Help.
    • For help on any Cmdlet, use: get-help <cmdlet-name> -detailed.
    • Scripting is good for things like automating system administration tasks. Scripting languages include VBScript, CScript, and PowerShell.
    • Basic script editing can be done in notepad.exe.
    • SQL08+ installs sqlps.exe which is SQL’s PowerShell interface.
      • If you run sqlps.exe on its own it is a “mini-shell” with all the SQL functionality but not all of the Windows functionality.
      • If you open Windows PowerShell (PowerShell.exe) and then run sqlps to load the SQL provider and cmdlets you have both SQL and Windows functionality.
    • For the most part scripting is NOT case sensitive (though I found one registry key that is case sensitive – HKLM).
    • PowerShell scripts have the .ps1 suffix and you always specify the full path (or . for the current dir) when you call the script.

    SQL

    • You must use the SQL08+ components to use PowerShell, but you can connect to SQL 2005 SP2+ and SQL 2000 SP4+ though the functionality may be limited.
    • Sqlps.exe and SSMS (right click, start PowerShell) start PowerShell with the SQL Server PowerShell provider and cmdlets loaded.
    • Within Windows PowerShell you can run sqlps to load the SQL cmdlets into Windows PowerShell.
    • The main cmdlet for SQL is Invoke-SQLCmd which lets you run any SQL query. The other cmdlets in SQL Server 2008/2008R2 are invoke-PolicyEvaluation (PBM), Encode-SQLName/Decode-SQLName (format SQL identifiers), and Convert-UrnToPath (navigation). You can also load the SQL Server Management Object (SMO) DLLs for additional functionality.
    • Read gettingStarted.rtf in the “Windows PowerShell 1.0 Documentation Pack” and complete the demo steps.

    Learning

    Syntax

    • Compare with –eq –ne –gt –like
    • $_ is the current object
    • The main difference between the script hosts cscript and wscript (the default) is that echo for cscript is to the command window and echo for wscript is to a message box.
    • If a path has a space you need to add & “…”. For example: & “C:tempmy PowerShellmytest.ps1”.
    • The current path is . as in .mytest.ps1

    References

    SQL Powershell

    Windows Powershell

    Downloads

    My other PowerShell blogs:

    Powershell for SQL – Getting Started http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-getting-started.aspx

    SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx

  • PowerShell for SQL – Getting Started

    PowerShell for SQL – Getting Started

    Cindy Gross, Dedicated Support Engineer

    You may want to use PowerShell to automate SQL activities such as backups. There are various ways to automate backups. For example, you can centralize the backup calls on an admin server which has some type of list of instances and databases and possibly varying schedules/types of backups for each. Another method is to use an admin server to push backup jobs to newly installed SQL instances. A variation of the 2nd is to periodically scan for new databases and/or databases without backups and/or databases that don’t meet some standard backup schedule and push out backup jobs. I’m not sure how automated or complex your backup plans are now. I’ve seen a wide range such as:

    • Manually schedule backups for each database as it is added (and hope you don’t miss one)
    • When you install the instance run a script to generate backup jobs for each database. Then periodically scan for new databases/databases not backed up and add backup jobs.
    • Schedule one backup job per instance to backup all local databases (either all on the same schedule or read a table to find the type/interval for each database on that instance)
    • Use single server or multi-server maintenance plans (not something I personally recommend in an enterprise – http://sqlserverpedia.com/blog/sql-server-management/maintenance-plans-vs-t-sql-scripts/)
    • Use a central scheduling system (master-target / multiserver administration via SQL jobs, UCP, 3rd party tools, etc.) with varying levels of complexity
    • Use a central Central Management Server (CMS) and/or a custom central admin databases to track which databases need which backup schedules and generate/check appropriate jobs

    My next two blogs will have some details about the first steps with Powershell. Most of the SQL related operations you can do from Powershell are done by calling SMO http://msdn.microsoft.com/en-us/library/ms162169.aspx. Allen White does training on SMO, and I’m sure you can find others who do so as well.

    Here are some backup samples that use PowerShell:

    The Enterprise Policy Management (EPM) Framework is a classic example of how to use the new SQL Server 2008 Central Management Server (CMS) to automate tasks across multiple servers. In this case it is automating Policy Based Management (PBM) with PowerShell but the concept could apply to many types of automation.  http://epmframework.codeplex.com/releases/view/28621  

    Next:

    PowerShell for SQL Server – Basics http://blogs.msdn.com/b/cindygross/archive/2011/09/23/powershell-for-sql-server-basics.aspx

    SQL PowerShell Demo – Intro http://blogs.msdn.com/b/cindygross/archive/2011/09/23/sql-powershell-demo-intro.aspx