Provisioning databases

Contents

Installing and configuring features

SQL Server installation is now complete, but three main 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 were 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.

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 database soon after installation and before a SQL Server Integration Services development can take place. You will need to do this only once. Because this will involve potential Surface Area Configuration changes and the creation of a new strong 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 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.

arrow.jpg For more information on database master keys, see Chapter 7.

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\140\DTS\Binn\SSISDBBackup.bak’ could not be accessed” or similar, it is likely because SQL Server Integration Services was not actually installed, so the template database backup was not copied from the SQL Server media. 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

If you did not select the Install And Configure check box in SQL Server Setup, you will have more tasks to complete here, but there are still tasks to perform upon first installation of a SQL Server Reporting Services native-mode installation.

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

    http://servername/ReportServer

    or:

    http://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 here, and the Reporting Services Configuration Manager will make the changes necessary.

  • Database. Each reporting service requires a pair of databases running on a SQL Server instance. If you selected the Install And Configure check box in SQL Server Setup, the databases will have been created for you with the names ReportServer and ReportServerTempDB, or, for a named instance, ReportServer$InstanceName and ReportServer$InstanceNameTempDB. Both of these databases are important and you should create backups. The ReportServerTempDB is not a completely transient database like the SQL Server instance’s TempDB system database.

    To set the databases for a new instance, click Change Database, and then follow the Create A New Report Server Database Wizard. This will add both databases to the instance.

  • 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

    Http://servername/Reports for the default instance

    or:

    Http://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 are used 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.

    First introduced in SQL Server 2016 was the ability for SQL Server Reporting Services to authenticate to an external 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.

    Prior to SQL Server 2016, authentication to external SMTP servers required the installation of a local SMTP server to provide a relay to the external, Azure-based (such as SendGrid) or cloud-based SMTP server. With SQL Server 2016 and 2017, SMTP connections can be made directly to these external SMTP servers.

    NOTE

    Keep in mind that enterprise SMTP servers have an allow list of IP addresses, and you will need to add this server’s IP to this list to send 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 or remote connections 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 SQL Server server roles, including sysadmin.

  • Encryption Keys. Immediately after installation and after the two SQL Server Reporting Services databases have been created, 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, use 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 be used in a network load balancer such as a Network Load Balancing Cluster.

    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.

  • PowerBI Integration. Visit 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 Active Directory, a system administrator of the SQL Server Reporting Services instance, and a sysadmin on the SQL Server instance that hosts the SQL Server Reporting Services databases.

arrow.jpg For the latest information on Power BI/SQL Server Reporting Services integration and the latest Azure authentication features, search for MT598750 or visit https://docs.microsoft.com/sql/reporting-services/install-windows/power-bi-report-server-integration-configuration-manager.

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.

Because of the nature of SQL Server Analysis Services databases, their size, and how they are populated, typically they are not updated 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 Command. You also 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.

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