This post is part of the series: “Professional SSRS 2016 Book Preview Posts” which are excerpts for my Wrox Press book: Professional SQL Server 2016 Reporting Services and Mobile Reports. Each of the posts is a condensed version of the material covered in a corresponding chapter from the book. The goal for this posts is to provide useful and meaningful information you can use. For more comprehensive details, I refer readers to the rather lengthy book itself. Note that large portions are copied directly from the book manuscript that may refer to figures and screen capture images that have been removed for brevity.
- What’s changed in SQL Server 2016?
- Installing a report server
- Building an enterprise deployment
- Using tools to manage the reporting life cycle
- Exploring report server architecture
- Leveraging reporting services extensions
WHAT’S IN THIS CHAPTER
To use the examples and work through the exercises in this book, you need a report server and administrative rights to access it. Unless you have a server set up for this purpose I recommend that you install a local instance of SQL Server on a machine that you manage. Your learning machine can be on your local computer, a local virtual machine or a virtual server hosted in a cloud service like Windows Azure. I recommend that you install the Developer Edition of SQL Server 2016 on Windows 8 Professional or higher, or Windows Server 2012 or higher.
TIP If you are just getting started or you are new to Reporting Services, some of the technical information in this chapter may not be relevant or necessary at this stage in your experience. To get up-and-running quickly, follow the steps in “Installing Reporting Services” section of this chapter and then follow the section titled “Installing the Reporting Services Samples, Exercises, and SQL Server Databases.” To use the chapter samples and exercises, you should install Reporting Services in native mode and install Analysis Services in multidimensional mode.
You need the SQL Server database engine and Reporting Services for the majority of the book samples and exercises. For some of the optional and specialized topics later in the book, you need to run Analysis Service in multidimensional mode and a full version of Visual Studio 2015 or newer, with Visual Basic or C# language support.
TIP The Developer Edition is essentially the same is the Enterprise Edition of SQL Server, priced and scaled-down for desktop use. The Standard Edition is sufficient for most business purposes but lacks a few enterprise features we discuss in later sections.
You could get by with as little as 4 GB of memory but I recommend that you have at least 8 GB. You should install the 64-bit version of SQL Server on a 64-bit operating system.
NOTE It is possible to install SQL Server on older and less-capable equipment with some additional upgrades. If you are working on a computer that doesn’t meet these recommended specifications, check the product system requirements for more details: https://msdn.microsoft.com/en-us/library/ ms143506(v=sql.130).aspx. Keep in mind that the minimum documented requirements are sufficient for the software to load and services to run, before you start working with data.
The topics of SQL Server setup and server architecture are kind of a chicken-and-egg thing. On one hand, it’s helpful to understand all the product nuances sufficiently to appreciate what all the options mean. On the other hand, I would like to give you enough guidance to get started without unnecessary details. This chapter guides you through a basic installation of SQL Server 2016 Reporting Services, and reviews some important considerations for an enterprise deployment.
Although the basic installation may not cover some of the choices critical in an enterprise deployment, a development instance provides an environment in which features and the installation process itself can be explored. Such an environment is ideal for performing the exercises and tutorials in this book.
You explore how features in Reporting Services are implemented and exposed. This information is foundational for both administrators and developers. Subsequent chapters build off concepts explored here.
The reporting life cycle gives you the context within which Reporting Services is employed. You explore the various applications and utilities associated with Reporting Services.
Following this, you dig a little deeper into Reporting Services itself by examining the architecture of the Reporting Services Windows service, its components, and supporting databases. By the end of the chapter, you will have a solid understanding of how all these pieces come together to deliver Reporting Services’ functionality.
WHAT’S CHANGED IN SQL SERVER 2016?
If you have been using previous versions of Reporting Services, I will save you some time by summarizing a few minor changes to the installation experience. The changes are brief so it is easy to keep this simple. In previous versions of SQL Server, you would normally include the Client Tools options from the Feature Selection page on the Setup Wizard. This would install SQL Server Management Studio and the Visual Studio project designer add-ins (called Business Intelligence Development Studio or SQL Server Data Tools, depending on the product version) from the SQL Server installation media.
The SSMS and SSDT client tools are now managed as separate downloads so they can be updated frequently and integrated with multiple versions of Visual Studio. The new web portal replaces the Report Manager web interface for Reporting Services. Although the portal has a different visual presentation, installation and configuration is really no different. You’ll just see a different report user interface after you finish the installation. Several new components and enhancements have been added to the Reporting Services feature set that do not affect the standard installation experience when compared to prior versions.
THE BASIC INSTALLATION
To understand the installation of Reporting Services, it is important to have some knowledge of its components. In SQL Server 2016, Reporting Services offers two modes:
➤ Native mode
➤ SharePoint Integrated mode
At its core, Reporting Services is a Windows service that relies on a pair of databases hosted by an instance of the SQL Server Database Engine. Note that in SharePoint Integrated mode, Reporting Services in SQL Server 2016 runs as a SharePoint shared service. This chapter is focused primarily on Reporting Services Native mode installations.
Interaction with the Reporting Services service is provided through applications such as web portal or the SharePoint Add-in, and other applications such as the SQL Server Data Tools. These applications, the SSRS service, and the report catalog databases are introduced in this chapter.
With the basic installation of Native mode, server-side and client-side components are installed on a single system. The Reporting Services databases are also installed to a local instance of the SQL Server Database Engine. With no dependencies on other systems, the basic installation is often referred to as a standalone installation.
SQL Server Developer Edition is a good choice for evaluation, development and testing environments. In addition to providing access to the full suite of Reporting Services features at no licensing cost, Developer edition supports a wider range of operating systems than other production-ready versions of SQL Server. The operating systems supported include Windows Server 2012, various editions of Windows 8 and, of course, new versions of Windows.
TIP As I mentioned at the beginning of the chapter, a practical development machine configuration should have significantly more horsepower than the stated minimum requirements. As a baseline, the virtual machine I have is configured to run all the book samples with optimal performance, has two processor cores assigned, and is configured to use dynamic memory, which typically uses 5–8 GB of RAM.
The minimum system requirements include 1 GB of memory. The basic installation also requires at least 6 GB of free storage space and additional space for the system updates and SQL Server samples. SQL Server 2016 is supported in virtual machine environments running on the Hyper-V role.
Installing Reporting Services
Before performing the Reporting Services installation, it’s a good idea to be certain your system is up-to-date with the latest service packs and Windows updates. You also need to be a member of the local Administrators group on the system on which you intend to perform the installation or be prepared to run the setup application using the credentials of an account that is a member of the local Administrators group.
TIP In the examples, I am installing SQL Server 2016 Developer Edition, which is what I recommend if you are setting up a development or evaluation machine with a local instance of SQL Server and Reporting Services. These instructions also apply to Standard and Enterprise Editions, although there may be subtle differences in the setup experience. SQL Server Developer Edition is available for free through the Visual Studio Dev Essentials program. To sign up and download software, go to https://www.visualstudio.com/en-us/products/ visual-studio-dev-essentials-vs.aspx
To start the installation, access the installation media for SQL Server 2016 You can run setup from a DVD, mount an ISO file as a logical DVD drive, or use a folder or file share. Figure 3-1 shows the SQL Server setup DVD image mounted as a logical drive. It is important that the media be accessed from the system on which you intend to install the Reporting Services software. Start the setup application by launching SETUP.EXE, located at the root of the installation media.
First the setup application checks your system for the Microsoft .NET Framework 3.5 SP1 and Windows Installer. If these are not present, the setup application initiates their installation. If either the .NET Framework or Windows Installer is installed by the setup application, your system may require a reboot. Upon restart, you need to re-launch the SQL Server 2016 setup application.
The setup application displays the SQL Server Installation Center, as shown in Figure 3-2. The Installation Center is divided into several pages, each providing access to documentation and tools supporting various aspects of the installation process.
For the purposes of the basic installation, proceed to the Installation page by clicking the appropriate link on the left side of the Installation Center form. On the Installation page, shown in Figure 3-3, select the option “New SQL Server stand-alone installation or add features to an existing installation.” This launches the SQL Server Setup Wizard.
The first step the SQL Server Setup Wizard performs is to compare your system against a set of “setup support” rules. These rules determine whether the system configuration prerequisites for installation are met. When the analysis is complete, the wizard shows summary information. If violations are present, you see the list of rules, identifying which ones require attention. If there are no violations, you can click the Show Details button to see this list, which is shown in Figure 3-4.
Clicking the “View detailed report” link on the Global Rules page opens a new window with a detailed report containing recommendations for addressing any warnings or violations, as shown in Figure 3-5. After reviewing this report, you can close this window.
On the Global Rules page of the SQL Server Setup Wizard, click the OK button to go to the Product Key page, shown in Figure 3-6. You can select one of the free editions of SQL Server or enter a product key for one of the other editions. Select the Evaluation edition or enter the product key of the Developer edition to proceed.
NOTE This is the point where I’m supposed to advise you to carefully read the terms of the product license before you agree to them. Please read the fine print if you feel so inclined.
To continue with the installation, check the box labeled “I accept the license terms.” This agreement allows high-level information about hardware and SQL Server component usage to be sent to Microsoft to help improve the product. You can read the privacy statement by clicking the hyperlink. Examples of feature usage are whether Reporting Services or other services are installed, and the operating system of the host computer.
NOTE Having worked with the SQL Server product teams at Microsoft over the years, I can tell you that any usage and telemetry information is gathered and sent to Microsoft only with a customer’s permission. This information is used to make product improvements and to prioritize feature development.
The usage data collection is very small and not granular. It does not count how often a feature area is used, just whether it is used at all.
Click the Next button to go to the Install Setup Files page, shown in Figure 3-8. This page informs you that files will be installed for the purposes of the setup process. When this process is complete, the wizard proceeds to the next page.
Click the Next button to proceed to the Setup Role page, within which you select a SQL Server
Click the Next button to proceed to the Feature Selection page, within which you select the SQL Server products and features to install, as shown in Figure 3-10. For the basic installation, select the Reporting Services and Database Engine Services features. If you want to install other components, such as Analysis Services, you can select these as well.
TIP To support all of the chapter samples and exercises, particularly for Chapters 9, 10, and 11, choose the option to install Analysis Services in Multidimensional mode.
The Feature Selection page also allows you to modify the path to which shared components will be installed. For the basic installation, typically this is left at the default location. If you have a compelling reason to change this location, click the button next to the displayed path, and select an appropriate alternative location.
TIP The difference between default and named instances are explained in “The Enterprise Deployment” section later in this chapter. For simplicity, if you are installing SQL Server and Reporting Services for the first time on a non-production server for development and learning, install a default instance.
Click the Next button to go to the Instance Configuration page, shown in Figure 3-11. Here you identify the instance name for the Database Engine and Reporting Services instances selected on the previous page. Other SQL Server instances that are already installed on the system are listed in the bottom half of the page. If a default instance is not already installed, you can choose to perform this installation to a default instance; otherwise, you need to provide an appropriate instance name.
When naming an instance, it’s important to keep in mind that the name is not case-sensitive and must be unique on the system. The name must also be no longer than 16 characters and may include letters, numbers, underscores (_), and the dollar sign ($). The first character must be a letter, and the instance name must not be one of the 174 setup reserved words listed in Books Online. In addition, it is recommended that the instance name not be one of the 235 ODBC reserved words, also listed in Books Online.
NOTE The Instance Configuration page also allows you to enter an installation ID other than the instance name. The instance ID is used to identify installation directories and registry keys for the SQL Server instance. In general, you should not alter the instance ID without a compelling reason to do so.
Click the Next button to proceed to the Disk Space Requirements page. Here you can review the amount of space consumed by the various components of the installation.
You will have the option to change service accounts and collation settings. On the Service Accounts tab in the Server Configuration page, shown in Figure 3-12, select the service account to be used for each service. For the local development installation, it is generally recommended that you accept the defaults and use the local service or (generated) network service accounts for the Database Engine and Reporting Services Windows service. You can change the service accounts later, after the installation.
You can typically skip the Collation page because the default selection is determined by the locale configured with the local operating system. As with other options, it is generally recommended that you not alter the collation unless you have a compelling reason to do so.
Click the Next button to proceed to the Database Engine Configuration page. This page allows you to configure the instance of the SQL Server Database Engine you are installing with Reporting Services. It is divided into four tabs: Server Configuration, Data Directories, FILESTREAM, and TempDB.
On the Server Configuration tab, shown in Figure 3-13, click the Add Current User button so that you will be set up as an administrator of the Database Engine instance. Leave all other options on this tab as they are, unless you have a compelling reason to change them.
On the Data Directories tab, you can alter various paths used by the Database Engine instance. Again, unless you have a compelling reason to make changes, leave the settings as they are configured by default. You won’t be using the FILESTREAM feature for the book samples so you can leave the default setting unless you plan to use the feature for other reasons.
NOTE For simple development purposes, you can accept the default TempDB options. In a true production server, the configuration options on this page are crucial for achieving good performance for production workloads. The right settings will depend on several factors including data storage, number of proces- sor cores, and memory. For example, among many recommended practices, con- figuring one file per CPU core will improve concurrency and query times.
Click the Next button to go to the Reporting Services Configuration page, shown in Figure 3-14. On this page, you can select different Reporting Services installation options. The various options are discussed in the second half of this chapter. For most basic installations, you should select the “Install and configure” option under Reporting Services Native Mode. The remaining instructions assume that you have selected this option.
Click the Next button to go to the Feature Configuration Rules page. These rules check that everything is in order before proceeding with the installation given the options you have selected. As before, the “View detailed report” link opens a separate report.
Click the Next button to go to the Ready to Install page, shown in Figure 3-15. Carefully review the options you have selected. If you will be repeating this installation on other systems, consider copying the path of the INI file listed at the bottom of the page.
Click the Install button to start the software installation. The installation process can take quite a bit of time to complete. During this time, an Installation Progress page appears, as shown in Figure 3-16. Upon completion, a summary of the installation process is presented.
Click the Close button to complete the wizard and return to the Installation Center. You can now close the Installation Center.
With the installation completed, your final step should be to verify the installation. Open Internet Explorer, and enter one of the following URLs:
➤ If you installed a default instance on the local computer, enter http://localhost/reports
➤ If you installed a named instance, enter http://localhost/reports_instancename, with the appropriate substitution. If you installed on a different machine, substitute the server name for localhost.
The URL may take a while to completely resolve upon this first use, but it should take you to the web portal, shown in Figure 3-17.
You can also navigate directly to the report server by replacing “Reports” with “ReportServer” in the address as you see in Figure 3-18.
Of course, there is no content visible in the web portal or in the browser view of the report server because nothing has been deployed, but this is where you will see folders, reports, and other items once the report server is being utilized to manage content.
Installing the Reporting Services Samples, Exercises, and SQL Server Databases
With SQL Server and Reporting Services installed, you should install the Reporting Services samples and sample databases that are used throughout this book. Two sample databases are included in the book samples available on this book’s web page at http://www.w x.com. The SQL Server database named WroxSSRS2016 is required for all of the chapter sample and exercise projects. The Analysis Services multidimensional database named “Adventure Works Multidimensional” is used only in Chapters 9, 10, and 11.
NOTE Both of the provided sample databases are prepared specifically for the report samples and chapter exercises in the book. The WroxSSRS2016 database includes data and database objects built from the Adventure Works Cycles data warehouse database from Microsoft, that have been simplified and adapted for reporting purposes. Don’t try to use other sample databases in place of these.
Verify that the SQL Server Database Engine, Reporting Services, and SQL Server Analysis Services are running.
The downloaded files from the book sample site include these three files:
- WroxSSRS2016 Projects.zip
- WroxSSRS2016.bak
- Adventure Works Multidimensional.abf
- Enterprise
- Standard
- Developer
- Web
- Express
Setup is quite simple…
…
SQL Server Editions
SQL Server 2016 comes in several editions, the following of which include Reporting Services:
Enterprise, Standard, and Web editions are the only editions supported in a production environment. The Enterprise edition provides access to the full set of features available with Reporting Services. The Standard and Web editions provide access to a reduced feature set. They cost less than the Enterprise edition, and may be more appropriate for smaller installations.
The Developer edition provides access to the same features available through the Enterprise edition. The Developer edition is free and is intended for development, evaluation, and testing environments only.
The Web edition supports a reduced feature set, even more so than the Standard edition, and reduced capacity as may be appropriate for small-scale or web-based deployments.
Finally, the Express edition is a highly restricted edition of SQL Server with limited support for Reporting Services. This edition is freely available, but its limitations make it unlikely to be used for anything other than highly specialized needs.
…
Hi, Apparently Wiley posted the wrong code for download on their site. I’ve alerted the editor and they will correct it. Thanks for letting me know.
Hi, I downloaded de code from wrox but the zip seems to have some, for me, strange apps, not find code nor db!