1 / 22

Managing SQL Server 2008 with Powershell

Managing SQL Server 2008 with Powershell. L.Srividya Architect Advisor | Microsoft India. Agenda. Brief introduction to Windows PowerShell Briefly outline the SQL Server PowerShell architecture Demo using PowerShell with SQL Server. Windows PowerShell An Introduction.

tatum
Download Presentation

Managing SQL Server 2008 with Powershell

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Managing SQL Server 2008 with Powershell L.Srividya Architect Advisor | Microsoft India

  2. Agenda • Brief introduction to Windows PowerShell • Briefly outline the SQL Server PowerShell architecture • Demo using PowerShell with SQL Server

  3. Windows PowerShellAn Introduction • Task based scripting technology that uses the .Net2.0 Framework • Windows PowerShell 1.0 currently supports Windows XP SP2, Windows Server 2003, Windows Vista and Windows Server 2008. • upported on multiple platforms (x86, x64 & Itanium) and by multiple language technologies (English language, Localized and Multilingual User Interface).

  4. Windows PowerShellCategories of commands • CMDlets – Built-in commands in .NET language. Users can create their own CMDLets and use them in Powershell • Functions – Functions created dynamically with PowerShell commands • Scripts – text files with .PS1 files & have one or more PowerShell commands • Applications – Existing Windows commands that work inside of Powershell

  5. Windows PowerShell contd.. • PowerShell.exe: shell for interactively editing and running PowerShell commands, or running .ps1 script files • Core language elements, such as operators, variables, arrays, and constants • Cmdlets: • Commands with parameters • Stop-Process –name Calc #comment – stops calc.exe • Verb-Noun names: Get-Item, Set-Location • Can be aliased using shorter names: gci, pa • Support for file system paths • Get-Help cmdlet to provide help

  6. SQL Server 2008 PowerShell • Leverages the power of the SQL Server management object models • Not a replacement for Transact-SQL scripts, but another tool to be used for scripting SQLPS.EXE SqlServerProviderSnapin100 SqlServerCmdletSnapin100

  7. SQL 2008 PowerShell in SSMS

  8. SQL Server 2008 PowerShell SQLPS.exe SQLSERVER:\ drive over SMO, PBM,DC, RegisteredServers Encode-SqlName Decode-SqlName Convert-UrnToPath Invoke-Sqlcmd Invoke-PolicyEvaluation Microsoft.SqlServer.Management. PSProvider.dll Microsoft.SqlServer.Management. PSSnapins.dll

  9. PowerShell in Agent • SQL Server Agent: • SQL Server PowerShell subsystem • Can now create PowerShell job steps • Very similar to command prompt job steps • Launches sqlps.exe with an input script

  10. PowerShell Providers • Implement a powershell drive • Navigation similar to file paths:

  11. SQL Server PowerShell Provider • Implements a SQLSERVER: drive. • The SQLSERVER: drive implements four folders to support SQL Server management object models:

  12. SQLSERVER: PowerShell Paths • You already know the object hierarchy: it’s very similar to the Object Explorer tree • But it is slightly different (it’s the SMO model) • Navigate using the PowerShell commands and their command prompt aliases • Use dir (gci) and cd (sl) to investigate the path structure • At each node you can use the methods and properties of the underlying management class

  13. SQLSERVER: PowerShell PathsStructure • Paths follow the hierarchy of the underlying object model • 1st node: SQLSERVER:\ • 2nd node: SQL\, SQLPolicy\, SQLRegistration\, or \DataCollection • 3rd and 4th nodes: ComputerName\InstanceName • 5th and higher nodes alternate between collection classes and object classes

  14. Create a Database Using SMO • Create a database in the default instance on the local computer:cd SQLSERVER:\SQL\localhost\DEFAULT\Databases$MyDBVar = New-Object Microsoft.SqlServer.Management.SMO.Database$MyDBVar.Parent = (Get-Item ..)$MyDBVar.Name = “NewDB”$MyDBVar.Create()dir$MyDBVar.Drop()

  15. SQL Server Identifiers • SQL Server identifiers allow characters which PowerShell may try to interpret • Have three ways to deal with them: • Encode hex value: %28local%29 • This one always works • Escape with ` character: `(local`) • Quote: cd “SQLSERVER:\SQL\(local)” • Use Encode-Sqlname and Decode-Sqlname to encode or decode quoted identifiers.

  16. Final PowerShell Reminder • While you can leverage a lot of what you learned from the command prompt, you’ll need to familiarize yourself with the new environment. • While PowerShell looks a lot like the command prompt environment, it’s not exactly the same. • While Invoke-Sqlcmd looks a lot like sqlcmd.exe, it’s not exactly the same.

  17. Microsoft Confidential demo SQL Server PowerShell

  18. The Road Ahead • Incorporate PowerShell 2.0 • Incorporate the PowerShell 2.0 visual editor • Add support for other SQL Server management object models • Analysis Services, Reporting Services, SSIS • Additional cmdlets based on customer demand and common scenarios • Additional documentation

  19. References • SQL Server 2008 Books Online: • SQL Server PowerShell Overview • SQL Server PowerShell Help • Learning PowerShell • Books • Windows PowerShell in Action by Bruce Payette • Windows PowerShell Cookbook by Lee Holmes • Whitepapers • Windows Powershell Website

  20. Feedback / QnA • Your Feedback is Important! Please take a few moments to fill out our online feedback form at: << Feedback URL – Ask your organizer for this in advance>> For detailed feedback, use the form at http://www.connectwithlife.co.in/vtd/helpdesk.aspx Or email us at vtd@microsoft.com • Use the Question Manager on LiveMeeting to ask your questions now!

  21. આભાર ধন্যবাদ நன்றி धन्यवाद ధన్యవాదాలు ಧನ್ಯವಾದಗಳು ଧନ୍ୟବାଦ നിങ്ങള്‍‌ക്ക് നന്ദി ਧੰਨਵਾਦ

More Related