Install and configure 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: SSIS, SSRS, and SSAS. 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 SSIS—specifically a new server-integrated deployment, built-in performance data collector, environment variables, and more developer quality-of-life improvements. For these reasons, you should use the new Project Deployment Model and 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 must create the SSISDB catalog soon after installation and before an SSIS development can take place. You will need to create the catalog only once. Because this involves potential surface area configuration changes and the creation of a new strong encryption password, a SQL DBA, not an SSIS developer, should perform this step and should store the password securely alongside others generated at the time of installation.

To create the catalog, in Object Explorer, connect to your instance, right-click Integration Services Catalog, and select Create Catalog in the shortcut menu that appears. In the single-page setup window, select the Enable CLR Integration check box, decide whether SSIS 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 back up the SSISDB database master key and securely store the SSISDB database password where it can be retrieved along with other disaster-recovery information for this server.

  • arrow.jpg For more on database master keys, see Chapter 13.

The SSISDB database will contain SSIS packages and their connection strings. The SSISDB encryption would not allow these sensitive contents—a treasure trove of connections to other servers—to be decrypted by a malicious user who gains access to the database files or backups. This SSISDB password will be required if the database is restored to a new 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 probably because SSIS was not actually installed. Most likely, the 6-MB template database backup was not copied from the SQL Server media, probably because the SSIS feature was not a selected feature. To rectify this, 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 an SSRS native-mode installation from the downloaded installer file, SQLServerReportingServices.exe.

At the end of the Microsoft SQL Server 2022 Reporting Services installer wizard, on the Setup Completed screen, select the Configure Report Server button to open the Reporting Services Configuration Manager application. Then connect to the newly installed SSRS instance and review the following options, from top to bottom:

  • Service Account. You can change the SSRS service account here. Remember that you should use only the Reporting Services Configuration Manager tool to make this change, never services.msc.

  • Web Service URL. The web service URL is not for user interaction; rather, it is for Report Manager and custom applications to programmatically connect to the SSRS 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, select Apply.

    You can optionally configure an SSL certificate for 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 necessary changes.

  • Database. Each SSRS instance requires a pair of databases running on a SQL Server instance. Executing the SSRS installer alone does not configure the databases for SSRS; you need to configure them via 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 an on-premises SQL Server instance or Azure VM–hosted SQL Server instance or, since SQL Server 2019, 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, select 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 administrative features to the SSRS instance. This is the link to share with users if you will be using the SSRS portal. By default, the URL for the web portal is servername/Reports for the default instance, or servername/Reports_InstanceName for named instances. You can change the name from the default if desired. To proceed, at the bottom of the application window, select Apply.

  • Email Settings. You use these email settings to send reports to user subscribers via email. SSRS 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.

    SSRS can authenticate to an SMTP server using anonymous (No Authentication), Basic, or NT LAN Manager (NTLM) authentication, which uses the SSRS service account to authenticate to the SMTP server.

    Modern email systems likely require at least TLS 1.2. For example, with Office 365, TLS 1.0 and 1.1 have been deprecated since 2020. Older versions of Windows and SQL Server may need to be patched to support TLS 1.2.

    SQL Server 2022 introduces support for TLS 1.3 as well. Leverage this when you can, including with SMTP connections.

NOTE

Enterprise SMTP servers usually have an allow list of IP addresses. You will need to add this server’s IP to this list to relay email.

  • Execution Account. You can optionally provide this domain account to be used when reports are configured to run unattended on a schedule or to connect to remote servers for external images. This credential must be a domain account.

    To follow the principle of least privilege, the execution account should not be the same as the SSRS service account. Further, this account should have minimal read-only access to any data sources that will require it. You also can give it EXECUTE permissions to stored procedures that serve as data sources for reports, but you should never give it 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 SSRS databases have been created, you should back up this instance’s encryption keys. These are 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. Alternatively, rotate the key by using the Change operation on this page 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.

NOTE

SSRS key encryption is different from TDE, which was first supported for SSRS databases in SQL Server 2019. SSRS encryption keys are used inside tables to secure connection strings, while TDE is used to secure database files from being restored or attached to other SQL Servers.

  • 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 employ this file share credential, which can be used on this page in a central location. This account should be different from the SSRS execution account, to follow the principle of least privilege.

  • Scale-Out Deployment. Visit this page on multiple SSRS instances to join them together. By using the same SSRS databases for multiple SSRS 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 Network Load Balancing (NLB), 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 SSRS 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 SSRS instance to view the status, add servers to the scale-out, or remove servers.

  • Power BI Integration. Use this page to associate the SSRS instance to a Microsoft Power BI account—specifically to an account in Azure AD. The administrator joining the Power BI instance to the SSRS instance must be:

    • A member of the Azure AD

    • A member of the system administrator role of the SSRS instance

    • A sysadmin on the SQL Server instance that hosts the SSRS databases

SQL Server Analysis Services initial configuration and setup

No additional steps are required after setup to begin using a new SSAS instance.

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

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

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

Azure Synapse Link for SQL Server

This feature replicates operational data into a dedicated SQL pool in Azure Synapse Analytics, directly from SQL Server 2022.

Azure Synapse Analytics is an enterprise analytics service in the Azure cloud running on both serverless and dedicated resource models. Azure Synapse Analytics is a combination of broad technologies, including relational data warehousing, serverless data pools for nonrelational data, built-in machine learning, and other big data technologies.

Far outside the scope of this book, Azure Synapse Analytics accelerates insights into data for logs, time series data, and data integrations. The built-in streaming and deep integrations with Power BI, Cosmos DB APIs, and Azure Machine Learning (AzureML), as well as other analytics tools and pipelines, provide convenient access to cloud resources for all kinds of workloads.

Azure Synapse Link connection

The Azure Synapse Link connection initially does a bulk upload, and then a continuous incremental upload of change feed data on a regular basis. The link between the SQL Server 2022 database and the dedicated SQL pool is mapped and can be changed. This ensures the ability to create, manage, monitor, and delete link connections or add and delete tables to the connection. To access corporate data inside a firewall, it is recommended to use a self-hosted integration runtime (IR).

NOTE

Self-hosted IRs created for Azure Synapse workspaces currently cannot be shared across Azure data factories or between other Synapse workspaces, unlike other self-hosted integration runtimes.

Azure Synapse Link landing zone

The landing zone is an intermediate staging location required to hold the data as it comes in from the SQL Server and before it is loaded into the Synapse dedicated SQL pool.

You must provide an Azure Data Lake Storage (ADLS) Gen2 account to be used as a landing zone, and this landing zone cannot be used for anything else. It must be different from the account created with the Azure Synapse Analytics workspace. An unexpired shared access signature (SAS) token for the ADLS Gen2 account is also crucial, because without it, the data will fail to replicate.

Ensure your database in SQL Server 2022 has a master key created by running the following command:

CREATE MASTER KEY ENCRYPTION BY PASSWORD = '<a new password>'

The Synapse Link feature is also available for data in Microsoft Dataverse for the Power Platform, Cosmos DB APIs, and Azure SQL Database.

NOTE

The Azure Synapse Link for SQL Server 2022 is in preview at the time of this writing and important details may change.

Save to your account

This chapter is from the book