Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By : Donabel Santos
Book Image

SQL Server 2012 with PowerShell V3 Cookbook

By: Donabel Santos

Overview of this book

PowerShell is Microsoft's new command-line shell and scripting language that promises to simplify automation and integration across different Microsoft applications and components. Database professionals can leverage PowerShell by utilizing its numerous built-in cmdlets, or using any of the readily available .NET classes, to automate database tasks, simplify integration, or just discover new ways to accomplish the job at hand."SQL Server 2012 with PowerShell V3 Cookbook" provides easy-to-follow, practical examples for the busy database professional. Whether you're auditing your servers, or exporting data, or deploying reports, there is a recipe that you can use right away!You start off with basic topics to get you going with SQL Server and PowerShell scripts and progress into more advanced topics to help you manage and administer your SQL Server databases.The first few chapters demonstrate how to work with SQL Server settings and objects, including exploring objects, creating databases, configuring server settings, and performing inventories. The book then deep dives into more administration topics like backup and restore, credentials, policies, jobs.Additional development and BI-specific topics are also explored, including deploying and downloading assemblies, BLOB data, SSIS packages, and SSRS reports. A short PowerShell primer is also provided as a supplement in the Appendix, which the database professional can use as a refresher or occasional reference material. Packed with more than 100 practical, ready-to-use scripts, "SQL Server 2012 with PowerShell V3 Cookbook" will be your go-to reference in automating and managing SQL Server.
Table of Contents (21 chapters)
SQL Server 2012 with PowerShell V3 Cookbook
Credits
About the Author
Acknowledgement
About the Reviewers
www.PacktPub.com
Preface
Resources
Index

Installing SQL Server 2012 on a VM


We are now ready to install SQL Server 2012. Carry out the following steps:

  1. Launch VMWare Player.

  2. Play SQL2012VM and login.

  3. Go to VM | Removable Devices | CD/DVD | Settings.

  4. Change the ISO image file path to the SQL Server 2012 ISO file, and click on OK.

  5. Once you click on OK, the Autoplay window should appear. Click on Run SETUP.EXE as shown in the following screenshot:

  6. Select Installation from the left-hand pane, and choose New SQL Server stand-alone installation or add features to an existing installation, as shown in the following screenshot:

  7. Run Setup Support Rules and click on Next.

  8. For the trial edition, we want to keep the default Evaluation product key selected.

  9. In the License Terms window, select I accept the license terms, and click on Next as shown in the following screenshot:

  10. Possible issues will be flagged in the next window. For our purposes, we expect to see a warning because of the domain controller and the firewall, as shown in the following screenshot:

    For security reasons, it is recommended to not install SQL Server on top of the domain controller, as discussed in the article at http://msdn.microsoft.com/en-us/library/ms143506.aspx. For our purposes, this is just a test machine that will not be used as a production box, we can ignore this warning.

  11. In the Setup Role screen, select SQL Server Feature Installation as shown in the following screenshot:

  12. In the feature selection, be sure to choose:

    • Database Engine Services (all components)

    • Analysis Services

    • Reporting Services - Native

    • SQL Server Data Tools

    • Integration Services

    • Documentation Components

    • Management Tools - Basic

    • Management Tools - Complete

    Feel free to choose additional features you want to try and then click on Next.

  13. The install wizard will now check Installation Rules. If there are any errors reported, be sure to resolve them before continuing with the installation. Click on Next.

  14. In the Instance Configuration window, choose to install Default instance.

  15. The next screen shows the disk space summary for all the features we've chosen so far; click on Next.

  16. In the Server Configuration screen, change the account settings for all services except Full-text Filter Daemon Launcher and SQL Server browser; for other services:

    • Use QUERYWORKS\sqlagent and corresponding password for SQL Server Agent.

    • Use QUERYWORKS\sqlservice and corresponding password for remaining services.

  17. For the database engine configuration, choose Windows authentication mode. Be sure to click on the Add Current User button to add QUERYWORKS\Administrator as sysadmin to your default instance.

  18. In Analysis Services Configuration, select Multidimensional and Data Mining mode. Click on Add Current User again for users that will have administrative privileges over Analysis Services.

  19. For Reporting Services Configuration, select Install only. We will configure reporting services later.

  20. On the Error Reporting window, leave the checkbox unchecked. Click on Next.

  21. Review your settings in the Ready to Install screen, and click on Install.

  22. Once installation is finished, click on Close.

  23. Now, we need to configure Reporting Services in the native mode. Go to Start | All Programs | SQL Server 2012 | Configuration Tools | Reporting Services Configuration Manager.

  24. Connect to the default instance.

  25. Click on Service Account. Double-check the service account assigned to run Reporting Services. If it is properly set, we do not need to make any changes in this window.

  26. Click on Web Service URL; we will accept the default values. Click on Apply.

  27. Click on Database and then click on the Change Database button. This will launch another set of windows.

  28. Select Create a new report server database.

  29. Leave the default database name as ReportServer, and click on Next.

  30. For Authentication Type, use Service Credentials. Click on Next.

  31. Review your Summary and click on Next.

  32. When the configurations have been successfully applied, click on Finish.

    Note that the original Report Server Database screen will now be populated with the newly configured values.

  33. Click on Report Manager URL, and click on Apply.

  34. Test the URL by launching Internet Explorer. In the browser, type http://localhost/Reports (this is the same URL as http://KERRIGAN:80/Reports):