Installing and configuring SQL Server instances and features

Contents

Post-installation configuration of other features

SQL Server Database Engine installation is now complete, but three other features require post-installation configuration, including SQL Server Integration Services, SQL Server Reporting Services, and SQL Server Analysis Services. You will need to perform the steps detailed in this section before use if these features are installed.

SSISDB initial configuration and setup

Among the best features added by SQL Server 2012 were massive improvements to SQL Server Integration Services, specifically with a new server-integrated deployment, built-in performance data collector, environment variables, and more developer quality-of-life improvements. For these reasons, use the new Project Deployment Model and the built-in SSISDB for all new development.

When the Integration Services Catalog is created, a new user database called SSISDB is also created. You should back it up and treat it as an important production database.

You should create the SSISDB catalog soon after installation and before a SQL Server Integration Services development can take place. You will need to create the catalog only once. Because this will involve potential Surface Area Configuration changes and the creation of a new strong encryption password, a SQL DBA, not a SQL Server Integration Services developer, should perform this and store the password securely alongside others generated at the time of installation.

In Object Explorer, connect to your instance, right-click Integration Services Catalog, and then, on the shortcut menu, click Create Catalog. In this single-page setup, you must select the Enable CLR Integration check box, decide whether SQL Server Integration Services packages should be allowed to be run at SQL Server Startup (we recommend this due to the maintenance and cleanup performed then), and provide an encryption password for the SSISDB database.

The encryption password is for the SSISDB database master key. After you create it, you should then back up the SSISDB database master key.

  • arr-icon.jpg For more on database master keys, see Chapter 13.

The SSISDB database will contain SQL Server Integration Services packages, their connection strings, and more data about the packages. The encryption would not allow these sensitive contents to be decrypted by a malicious user who gains access to the database files or backups. This password would be required if the database were moved to another server, so you should store it in a secure location within your enterprise.

NOTE

If you receive an error when creating the SSSIDB catalog that reads “The catalog backup file ‘C:\Program Files\Microsoft SQL Server\150\DTS\Binn\SSISDBBackup.bak’ could not be accessed” or similar, it is likely because SQL Server Integration Services was not actually installed. It’s likely that the 6MB template database backup was not copied from the SQL Server media, perhaps because the SSIS feature was not installed on this instance. You can run SQL Server Setup again or copy the SSISDBBackup.bak file from another SQL Server installation of the same version.

SQL Server Reporting Services initial configuration and setup

There are still tasks to perform upon first installation of a SQL Server Reporting Services native-mode installation from the downloaded installer file, SQLServerReportingServices.exe. At the end of the Microsoft SQL Server 2019 Reporting Services installer wizard, on the Setup Completed screen, click the Configure report server button to open the Reporting Services Configuration Manager application. Connect to the newly installed SQL Server Reporting Services instance, and then review the following options, from top to bottom:

  • Service Account. You can change the SQL Server Reporting Services service account here. Remember that you should use only the Reporting Services Configuration Manager tool to make this change.

  • Web Service URL. The web service URL is not for user interaction; rather, it is for the Report Manager and custom applications to programmatically connect to the SQL Server Reporting Services instance.

    By default, a web service on TCP Port 80 is created called ReportServer. For named instances, the web service will be called ReportServer_instancename. The URL for the webservice would then be:

    servername/ReportServer

    or:

    servername/ReportServer_instancename

    To accept defaults, at the bottom of the application window, click Apply.

    You can optionally configure an SSL certificate to a specific URL for the Web Portal in the Advanced section here. Choose an identity and an HTTPS certificate that’s been loaded to the server, and the Reporting Services Configuration Manager will make the changes necessary. For more information on configuring SSL connections for the SSRS Web Service and Web Portal, visit: https://docs.microsoft.com/sql/reporting-services/security/configure-ssl-connections-on-a-native-mode-report-server.

  • Database. Each instance of SSRS requires requires a pair of databases running on a SQL Server instance. Simply executing the SSRS installer alone does not configure the databases for SSRS, you need to configure them via the Reporting Services Configuration Manager. The database names by default are ReportServer and ReportServerTempDB, or, for a named instance, ReportServer$InstanceName and ReportServer$InstanceNameTempDB. Both of these databases are important and you should create a backup schedule for each. The ReportServerTempDB is not a completely transient database like the SQL Server instance’s TempDB system database.

    The databases for SSRS can be hosted on a on-premises SQL Server instance or Azure VM-hosted SQL Server instance or, new to SQL Server 2019, hosted on an Azure SQL Managed Instance.

    To set the databases for a new instance of SSRS, in the Database page of the Reporting Services Configuration Manager, click Change Database, and then follow the Report Server Database Configuration Wizard.

  • Web Portal URL. The web portal URL is the user-friendly website that hosts links to reports and provides for administrative features to the SQL Server Reporting Services instance. This is the link to share with users if you will be using the SQL Server Reporting Services portal.

    By default, the URL for the web portal is /Reports

    servername/Reports for the default instance

    or:

    servername/Reports_InstanceName for named instances

    You can change the name from the default here if desired. To proceed, at the bottom of the application window, click Apply.

  • Email Settings. You use these email settings for sending reports to user subscribers via email. SQL Server Reporting Services uses its own Email Settings and does not inherit from the SQL Server instance’s Database Mail settings. This setting is optional if you do not intend to send reports to subscribers via email.

    SQL Server Reporting Services can authenticate to an SMTP server using anonymous (No Authentication), Basic, or NT LAN Manager (NTLM) authentication, which will use the SQL Server Reporting Services service account to authenticate to the SMTP server.

    NOTE

    Keep in mind that enterprise SMTP servers usually have an allow list of IP addresses, and you will need to add this server’s IP to this list to relay email.

  • Execution Account. You can provide this domain account optionally to be used when reports are configured to run on a schedule, to run without credentials (select the Credentials Are Not Required Option), or to connect to remote servers for external images.

    The execution account should not be the same as the SQL Server Reporting Services service account.

    This account should have minimal read-only access to any data sources that will require it. You also can give it EXECUTE permissions for data sources that use stored procedures, but you should never give it any additional SQL Server permissions or let it be a member of any server roles, including sysadmin.

  • Encryption Keys. Immediately after installation and after the two SQL Server Reporting Services databases have been created, you should back up this instance’s encryption keys. This key is used to encrypt sensitive information such as connection strings in the two databases. If the databases are restored to another server and this key is not available from the source server, credentials in connection strings will not be usable and you will need to provide them again for the reports to run successfully on a new server.

    If you can no longer locate the backup of a key, back it up again, or rotate the key by using the Change operation on this page to replace the key, and then back it up.

    To restore the original key to a new server to which the databases have been moved, use the Restore operation on this page.

  • Subscription Settings. Use this page to specify a credential to reach file shares to which report subscriptions can be written. Reports can be dropped in this file share location in PDF, Microsoft Excel, or other formats for consumption.

    Multiple subscriptions can use this file share credential, which can be used on this page in a central location.

    This account should be different from the SQL Server Reporting Services execution account to serve its purpose appropriately.

  • Scale-Out Deployment. Visit this page on multiple SQL Server Reporting Services instances to join them together. By using the same SQL Server Reporting Services databases for multiple SQL Server Reporting Services instances, multiple front ends can provide processing for heavy reporting workloads, including heavy subscription workloads. The server names can optionally be used in a network load balancer such as a Network Load Balancing Cluster, or you can distribute workload to each SSRS instance from different applications.

    Upon first installation, the Scale-Out Deployment page will show that the instance is “Joined” to a single server scale-out.

    Each scale-out instance of SQL Server Reporting Services must use the same settings on the Database page of the Reporting Services Configuration Manager. Connect to each instance in the scale-out and visit this page by opening it on each SQL Server Reporting Services instance to view the status, add servers to the scale-out, or remove servers.

    For more detail on scale-out deployments of SSRS, visit: https://docs.microsoft.com/sql/reporting-services/install-windows/configure-a-native-mode-report-server-scale-out-deployment.

  • PowerBI Integration. Use this page to associate the SQL Server Reporting Services instance to a Microsoft Power BI account, specifically to an account in Azure Active Directory. The administrator joining the Power BI instance to the SQL Server Reporting Services instance must be:

    • A member of the Azure AD

    • A member of the system administrator role of the SQL Server Reporting Services instance

    • A sysadmin on the SQL Server instance that hosts the SQL Server Reporting Services databases.

SQL Server Analysis Services initial configuration and setup

No additional steps are required after setup to begin using a new SQL Server Analysis Services instance.

You can initiate manual backups of SQL Server Analysis Services databases in Object Explorer in SQL Server Management Studio as well as restore SQL Server Analysis Services databases. Because of the nature of SQL Server Analysis Services databases, their size, and how they are populated, typically they are not backed up on a schedule, but you can do so by passing an XMLA command via a SQL Server Agent job step: type SQL Server Analysis Services.

When installing SQL Server Analysis Services, a security group should have been chosen to grant permissions to SQL Server Analysis Services server administrators, granting a team full access to the server.

If you need to add a different group to the administrator role of the SQL Server Analysis Services instance, open SQL Server Management Studio, and then, in Object Explorer, connect to the Analysis Services instance. Right-click the server, and then, on the shortcut menu, click Properties. On the Security page, you can add additional windows-authenticated accounts or groups to the administrator role.

Save to your account

This chapter is from the book