Managing SQL Server 2008 with Powershell

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!

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