Change SQL Server editions and versions

Upgrading editions in-place is supported by a feature of the SQL Server 2022 installer. You can upgrade in the following order: Express, Web, Standard, and Enterprise.

You cannot downgrade a SQL Server version or licensed edition. This type of change requires a fresh installation and migration. For example, you cannot downgrade in-place from SQL Server 2022 Enterprise edition to Standard edition.

In-place upgrades for major versions (from 2019 to 2022, for example) is supported but not recommended. Instead, we strongly recommend that you perform a fresh installation of the newer version and then migrate from old to new instances. This method offers major advantages in terms of duration of the planned outage, rollback capability, and robust testing in parallel.

Although in-place upgrades to SQL Server 2022 are not recommended, upgrades are supported for versions as old as SQL Server 2012 SP4. You can even migrate databases using detach and reattach, from older versions of SQL Server to SQL Server 2022, as long as the source database compatibility level is 90 or higher. Databases with a compatibility of 90 (SQL Server 2005) will be automatically upgraded to compatibility level 100. Databases already at compatibility level 100 will not change.

A supported upgrade also assumes that the OS and previous version of SQL Server are not 32-bit installations. Beginning with SQL Server 2016, SQL Server is available only for 64-bit platforms. For more information on upgrades to SQL Server 2022, visit https://learn.microsoft.com/sql/database-engine/install-windows/supported-version-and-edition-upgrades-2022.

Launch SQL Server Setup

You should not run SQL Server Setup with the installation media mounted over a remote network connection, a shared remote desktop drive, or any other high-latency connection. For a faster SQL Server Setup experience, unpack the contents of the .iso file to a physical folder local to the server.

Start setup.exe on the SQL Server Setup media, running the program as a Windows user with administrator privileges. If AutoPlay is not turned off (it usually is), setup.exe will start when you first mount the media or double-click to open the .iso. Instead, as a best practice, right-click setup.exe and select Run As Administrator on the shortcut menu that appears.

We’ll review here a few items (not all) in the SQL Server Installation Center worth noting before you begin an installation.

In the pane on the left, select Planning to open a long list of links to Microsoft documentation websites. Most helpful here might be a standalone version of the System Configuration Checker, which you run during SQL Server Setup later, but it could save you a few steps if you review it now. A link to download the Data Migration Assistant (DMA) is also present, which is a helpful Microsoft-provided tool for upgrading from prior versions of SQL Server.

On the Maintenance page, you will find the following:

• The Edition Upgrade Wizard is relatively painless. This is only for promoting your existing installation’s edition, as discussed earlier.

• The Repair feature is not commonly used except in the case of an instance with a corrupted installation. You might also need to repair an instance of SQL Server when the executables, .dll files, or Windows Registry entries have become corrupted or damaged by disk corruption, antimalware, malware, or malicious activity. A failed SQL Server in-place upgrade or cumulative update installation might also require a repair, which could be better than starting from scratch.

• Removing a node from an existing SQL Server failover cluster is an option in the Maintenance page. Adding a node to an existing SQL Server failover cluster is an option in the Installation page.

• The Advanced page features a link to perform an installation based on a configuration file. We will discuss how to easily generate and use a configuration file later in this chapter, in the section “Automate SQL Server Setup with configuration files.” If you are tasked with installing multiple SQL Servers with mostly common settings, consider this time-saving method. There are also links to wizards for advanced failover cluster installations.

  • We discuss failover cluster instances (FCIs) in Chapter 11, “Implement high availability and disaster recovery.”

Windows Update in the SQL Server Setup

Since SQL Server 2012, the SQL Server installer has had the ability to patch itself within the Setup wizard. The Product Updates page is presented after the License Terms page, and, after you accept it, it is downloaded from Windows Update (or Windows Server Update Services) and installed along with other SQL Server Setup files.

This is recommended, so a SQL Server 2022 Setup with Internet connectivity is the easiest way to carry out the installation. This also could be described as a way to “slipstream” updates, including hotfixes and cumulative updates, into the SQL Server installation process, eliminating these efforts post-installation.

For servers without Internet access, there are two setup.exe parameters that support downloading these files to an accessible location and making them available to Setup. When starting setup.exe from Windows PowerShell or the command line (you can read more about this in the next section), you set the /UpdateEnabled parameter to FALSE to turn off the download from Windows Update. The /UpdateSource parameter can then be provided as an installation location of .exe files. Note that the /UpdateSource parameter is a folder location, not a file. You will find more on these two parameters later in the “Install by using a configuration file” section.

Regardless, after installation is complete, and before the SQL Server enters further use, verify that the latest SQL Server patches have been applied. For SQL Server 2022, see the official build versions at https://support.microsoft.com/help/4518398.

Install SQL Server stand-alone installation

Although what follows in this chapter is not a step-by-step walk-through, we do cover key new features and decision points of the New SQL Server Stand-Alone Installation option of the SQL Server Installation Center.

PolyBase Services

Immediately after the instance configuration is a new configuration for the port range of PolyBase services. This is where you choose a range of ports to use for this service. If you plan to use PolyBase, the ports typically used are TCP ports between 16450 and 16460, of which there must be at least six ports. These should be allowed through the firewall if needed. This option was added to SQL Server Setup in SQL Server 2022.

Grant Perform Volume Maintenance Tasks

On the same Server Configuration page on which service accounts are set, notice the Grant Perform Volume Maintenance Task privilege to the SQL Server Database Engine Service check box. Selecting this check box automates what used to be a standard post-installation checklist step for SQL DBAs beginning with Windows Server 2003.

The reason to grant this permission to use instant file initialization is to speed the allocation of large database data files, which could dramatically reduce the Recovery Time Objective (RTO) capacity for disaster recovery. This can mean the difference between hours and minutes when restoring a very large database. It can also have a positive impact when creating databases with large initial sizes, or in large autogrowth events—for example, with multiple data files in the tempdb (more on this next). It is recommended that you allow SQL Server Setup to turn on this setting.

• For more information on instant file initialization, see Chapter 3, “Design and implement an on-premises database infrastructure.”

Instance collation

The Collation tab on the Server Configuration page allows you to choose a collation for the Database Engine. The collation determines how character data is stored, sorted, and compared. For more information, see the section on collation in Chapter 7, “Understand table features.”

Initially, the instance collation provided in SQL Setup is the default collation for the server’s regional settings, but you might need to change this collation based on vendor or developer specifications.

While changing the collation of a database is easy, the instance collation is important to get right at the time of SQL Server installation, as changing the instance collation is quite difficult.

The server collation you set here acts as the collation for all system databases as well as the default for any newly created user databases. For new application development, you may choose to take advantage of UTF-8 collations as the server default, introduced in SQL Server 2019.

Mixed Mode authentication

SQL Server supports two modes of authentication: Windows Authentication and SQL Authentication. Windows Authentication is preferable to SQL Authentication, and in multiple places in this book we will emphasize this.

• You can read more on this topic in Chapter 12, “Administer instance and database security and permissions,” but it is important to note this decision point here.

Ideally, all authentication is made via Windows Authentication, through types of server principals called logins that reference Windows accounts—ideally, AD domain accounts or, starting with SQL Server 2022, Azure Active Directory (Azure AD) principals. These are created by your existing enterprise security team, which manages password policy, password resets, password expiration, and so on.

A redundant security model for connecting to SQL Server also exists within each instance: SQL Server Authenticated logins. Logins are maintained at the SQL Server level, are subject to local policy password complexity requirements, are reset/unlocked by SQL DBAs, have their own password change policy, and so forth.

Enabling Mixed Mode (SQL and Windows Authentication Mode) activates SQL Authenticated logins. Be aware that SQL Authentication is not on by default, and isn’t the recommended method of connection. The recommended Windows Authentication cannot be turned off. When possible, applications and users should use Windows Authentication.

Enabling Mixed Mode also activates the sa account, which is a special built-in SQL Server Authentication that is a member of the server sysadmin role. Setup will ask for a strong password to be provided at this time.

• You can learn more about the sa account and server roles in Chapter 12.

If you find you have an actual need to enable SQL Server Authentication, but didn’t do this during SQL Server Setup, you can do it later by connecting to the SQL Server instance via Object Explorer in SQL Server Management Studio. To do so, right-click the server name and select Properties from the shortcut menu. Then, select the Security page and change to Mixed Mode. You must perform a SQL Server service restart to effect this change.

Default settings for the tempdb database

Starting with SQL Server 2016, SQL Server Setup provides a more realistic default configuration for the number and size of tempdb data files. This has been a common to-do list for all post-installation checklists for DBAs since the early days of SQL Server.

The TempDB database page in SQL Server Setup provides not only the ability to specify the number and location of the tempdb’s data and log files, but also their initial size and autogrowth rates. The best number of tempdb data files is almost certainly greater than one and less than or equal to the number of logical processor cores, including hyper-threading for local machines. For example, with 16 logical processors, SQL Server Setup will default the installation to have eight tempdb data files.

Adding too many tempdb data files can degrade SQL Server performance—perhaps severely. For example, with 20 logical processors, SQL Server Setup will still default the installation to have 8 tempdb data files. If you add 20 tempdb data files, SQL Server may struggle to respond.

• For more information on the best number of tempdb data files, see Chapter 3.

Specifying tempdb’s initial size to a larger, normal operating size is important and can improve performance after a SQL Server restart when the tempdb data files are reset to their initial size. Setup accommodates an individual tempdb data file initial size up to 256 GB. For data file initial sizes larger than 1 GB, you will be warned that SQL Server Setup can take a long time to complete if instant file initialization is not turned on.

Since SQL Server 2016, all tempdb files autogrow at the same time, keeping file sizes the same over time, which is critical to the way multiple tempdb data files are used. This is superior to the old way of ensuring tempdb data files stay the same size: using the server-level setting via server Trace Flag 1117, which applied the data file growth behavior to all databases. Trace Flag 1117 is no longer necessary.

Also note the naming convention for the second tempdb data file and beyond: tempdb_mssql_n.ndf. A SQL Server uninstallation will automatically clean up tempdb data files with this naming convention. For this reason, we recommend that you follow this naming convention for tempdb data files.

• The tempdb system database is discussed in detail in Chapter 3.

Default settings for MAXDOP

New in SQL Server 2019 were defaults for the configuration of the server-wide Maximum Degree of Parallelism (MAXDOP) setting on the Database Engine Configuration page under the new MaxDOP tab.

In the same way that new tempdb defaults since SQL Server 2016 are dependent on the detected processors, a suggested default MAXDOP is also configured based on the number of logical processors. For many servers with 16 or fewer virtual processor cores, the default is the same as the number of cores, effectively the same as a MAXDOP setting of 0, which allows for unlimited parallelism.

For example, with 8 logical processors, SQL Server Setup will default the installation to use a MAXDOP of 8. With over 16 logical processors, SQL Server Setup may default to half the number of logical processors—at most 16. For example, with 20 logical processors, SQL Server Setup will default the installation to use a MAXDOP of 10.

• For more recommendations about MAXDOP, visit Microsoft Support at https://support.microsoft.com/help/2806535. See also the section on “Max degree of parallelism” in Chapter 3.

You can always reconfigure the MAXDOP after installation without a restart, though not without potential disruption. Although changing the server-wide (or database-level) MAXDOP setting takes effect immediately, it is definitely not advisable to do so during normal production operating hours, because it can lead to widespread plan recompilation and a heavy CPU spike. This server-wide MAXDOP setting can be overridden at the database, query, or Resource Governor group level. The MaxDOP tab in the Database Engine Configuration tab has a recommended MAXDOP setting of 8 for a server with eight virtual cores. This is effectively the same as a MAXDOP of 0, but offers the administrator an option to potentially change the MAXDOP at the time of installation.

• For much more information on performance tuning, parallelism, and the MAXDOP setting, see Chapter 14, “Performance tune SQL Server.”

Default settings for Maximum Server Memory

New in SQL Server 2019 were defaults for the configuration of the instance-level Max Server Memory option, a common post-installation checklist item, under the Memory tab of the Database Engine Configuration page. SQL Server Setup makes a guess based on total server memory for an appropriate option. In previous versions of SQL Server, it was important to remember to change the Max Server Memory setting after installation was complete; otherwise, SQL Server memory would be uncapped and have access to all memory on the server.

You can configure this Max Server Memory option intelligently at the time of installation. It’s important to note (and there’s a check box to accept this guess) that SQL Server Setup assumes this SQL Server instance will run alone on this server. If you expect to host other applications on this server, or to run memory-heavy features of SQL Server on the same server such as SSAS or SSRS, you should further reduce the Max Server Memory setting for the SQL Server instance.

• Chapter 3 discussed the Max Server Memory setting, in the “Configuration settings” section.

Let’s use an example of the new Max Server Memory recommendation for a Windows Server with one SQL Server instance and 16 GB of memory. SQL Server Setup recommends a Max Server Memory setting of 12672 MB. The Min Server Memory setting, which establishes a floor for memory allocation, is set to 0. It is generally unnecessary to change this setting from the default. You might find this setting useful for situations in which the total system memory is insufficient and many applications, including SQL Server instances, are present. The Min Server Memory setting is not immediately allocated to the SQL Server instance upon startup; instead, it does not allow memory below this level to be freed for other applications. Figure 4-1 shows the Memory tab of the Database Engine Configuration page, with the Min Server Memory and Max Server Memory settings visible.

After installation, server memory settings are accessible via SQL Server Management Studio, in Object Explorer, and on the Server Properties page.

You should ensure that SQL Server leaves enough memory for the OS and other applications. Keep in mind that SQL Server will slowly consume more memory over time and may take hours or days, depending on your business cycle, for the SQL Server instance to consume the maximum amount of memory made available. Lowering this setting after installation and during operation does not return SQL Server memory back to the OS immediately; rather, it does so over time during SQL Server activity. Increasing this setting will not immediately show the effect of a change in memory use.

Install SQL Server Analysis Services

Installing SQL Server Analysis Services (SSAS) requires you to decide ahead of time which mode to install. Each instance of SSAS can be in only one mode, which means that with a single license, you can run either Multidimensional mode, the newer Tabular mode (introduced in SQL Server 2012), or Power Pivot mode.

Ask your business intelligence (BI) decision makers which platform you should use. For most new development, Tabular mode is popular and recommended. Tabular mode databases can also run in Azure Analysis Services. Brief descriptions of each mode follow:

• Multidimensional. This is the SSAS setup that was introduced in SQL Server 2000 and helped revolutionize the data-warehousing industry. This is also the only mode to support data mining and other features on which existing SSAS data models predating SQL Server 2012 may be dependent. The primary language for building and querying multidimensional models is MDX.

• Tabular. This is the newer and recommended SSAS setup introduced in SQL Server 2012, using the in-memory VertiPaq processing engine. Since SQL Server 2017, this has been the default installation mode selected on the Analysis Services Configuration page of SQL Server Setup. The primary language for building and querying tabular models is DAX, which is similar to the Excel function language.

• Power Pivot. This mode installs SSAS in Power Pivot for SharePoint mode. Power Pivot workbooks use both DAX and MDX. Note that Analysis Services Power Pivot for SharePoint support for Microsoft SharePoint 2019 has been discontinued.

  • For more on the differences between these SSAS installation options, visit https://learn.microsoft.com/analysis-services/comparing-tabular-and-multidimensional-solutions-ssas.

Install SQL Server Integration Services

The SQL Server Integration Services (SSIS) instance is installed once per server per version, not once per instance like other features. Starting in SQL Server 2017, however, a new Integration Services Scale Out Configuration became available. We discuss this new feature further in the next section.

A 64-bit version of SSIS is installed on 64-bit operating systems. If you worry about connecting to 32-bit servers, data sources, or application installations (such as Microsoft Office), you don’t need to. Those connections are not dependent on the 32-bit/64-bit installation and are handled at the package or connection-string level. Unlike other features, you can install SSIS on a 32-bit OS; however, we do not recommend this.

Installations of different versions of SSIS are installed side by side on a server. Specifically, SSIS 16.0 is compatible with prior versions.

Apart from configuring the service account, you need not do any additional configuration when installing SSIS during SQL Server Setup. The default virtual service account is NT ServiceMsDtsServer160.

Install SQL Server Integration Services Scale Out configuration

Since SQL Server 2017, SSIS supports a Scale Out configuration, by which you can run a package on the same or multiple SQL Server instances. This also allows for high availability of SSIS, and a similar architecture allows for integration and “lift and shift” code deployments from on-premises SSIS to the Azure Integration Runtime.

• Additional information on integration runtimes can be found in Chapter 19.

The master node talks to worker nodes in an SSIS Scale Out system, with the communication over a port (8391 by default) and secured via a new Secure Sockets Layer (SSL) certificate. The SQL Server installer can automatically create a 10-year self-signed certificate and endpoint for communication when the master node is set up.

When adding another SSIS installation as a Scale Out Worker, start the new SSIS Manage Scale Out window via SQL Server Management Studio. To do so, right-click the catalog you have created and select Manage Scale Out. At the bottom of the page, select the + button to add a new Scale Out Worker node.

Next, you provide the server name on which to connect. If using a named instance, provide only the server name of the node; do not include the instance name. A dialog box confirms the steps taken to add the Scale Out Worker node, including copying and installing certificates between the Worker node and Master node, updating the endpoint and HttpsCertThumbprint of the worker, and restarting the Worker node’s Scale Out service.

After the worker node is added, refresh the Worker Manager page. Then select the new Worker node entry, which will be red. Finally, turn on the Worker node by selecting Enable Worker.

You also can copy and install the certificates manually between servers. You will find them in %Program Files%Microsoft SQL Server160DTSBinn.

• For more information on certificates between servers, visit https://learn.microsoft.com/sql/integration-services/scale-out/deal-with-certificates-in-ssis-scale-out. For a Microsoft-provided walk-through of setting this up, visit https://learn.microsoft.com/sql/integration-services/scale-out/walkthrough-set-up-integration-services-scale-out.

One major security difference with Scale Out is that even though the SSIS service account doesn’t run packages or need permission to do very much, the Scale Out Master and Worker service accounts do run packages. The SSIS service account is different from the Scale Out Master and Scale Out Worker service accounts.

The Worker and Master nodes do not appear in SQL Server Configuration Manager (as of SQL Server 2019) but do appear in the Services console (services.msc). By default, these services run under virtual accounts NT ServiceSSISScaleOutMaster160 and NT ServiceSSISScaleOutWorker160, but you might want to change these to a Windows-authenticated domain service account that will be used to run packages across the Scale Out.

Install SQL Server Reporting Services

Starting with SQL Server 2017, SQL Server Reporting Services (SSRS) is no longer found in the SQL Server Setup media; it is instead available as a simplified, unified installer and a small download. SSRS is now a 95+MB download named SQLServerReportingServices.exe but still needs a SQL Server Database Engine instance as part of the license to host the two Report Server databases. Note that SSRS isn’t free, and that the separate installer isn’t a licensing change—although SQL Server Express with Advanced Services offers some limited SSRS support.

• For more information on the limitations of SSRS with SQL Server Express license, see https://learn.microsoft.com/sql/reporting-services/reporting-services-features-supported-by-the-editions-of-sql-server-2016.

To install SSRS, you need to provide a license key upon installation in a production environment. You can choose a free edition to install (Evaluation, Developer, or Express), but you should note that Developer edition is not allowed in a production environment.

The “native mode” of SSRS Is now the only mode since SQL Server 2017. If you are familiar with Reporting Services Report Manager from the past, accessible via the URL servername/Reports, that is the “native mode” installation of Reporting Services.

Report Server Configuration Manager is in a new location, in its own Program Files menu: Microsoft SQL Server Reporting Services. After installation, start the Report Server Configuration Manager (typically installed in a path like Program Files (x86)Microsoft SQL Server160ToolsBinnRSConfigTool.exe). The Report Server Configuration Manager application itself is largely unchanged since SQL Server 2008.

The default SSRS service account is the virtual service account called NT SERVICESQLServerReportingServices. It is a second-best option, however. We recommend that you instead create a new domain service account to be used only for this service—for example, Domainsvc_ServerName_SSRS or something with a similar naming convention. You will need to use a domain account if you choose to configure Report Server email with Report Server service account (NTLM) authentication.

If you choose to change the SSRS service account later, you must use the Reporting Services Configuration Manager tool. As with other SQL Server services, you should never use the Services console (services.msc) to change service accounts.

After installation, you will need to follow up on other changes and necessary administrative actions—for example, configuring the SSRS Execution Account and email settings or backing up the encryption key using Reporting Services Configuration Manager.

SSRS can also integrate with Microsoft Power BI dashboards. A page in the Report Server Configuration Manager supports the registration of this installation of SSRS with a Power BI account. You will be prompted to sign into Azure AD. The account you provide must be a member of the Azure tenant where you intend to integrate with Power BI. The account should also be a member of the system administrator in SSRS, via Report Manager, and a member of the sysadmin role in the SQL Server that hosts the Report Server database.

Install machine learning features

The Machine Learning Services feature makes it possible for developers to integrate with the R and Python language extensions using standard Transact-SQL (T-SQL) statements.

Data scientists can take advantage of this feature to build advanced analytics, data forecasting, and algorithms for machine learning. Data engineers can leverage these languages to integrate predictive analytic and machine learning. The scripts you create can be executed in-database without having to move data. You can prepare, clean, train, evaluate, perform feature engineering, and deploy machine learning models where the data resides. This eliminates the transfer of data across the network to another server.

Machine Learning Services is not a standalone feature. It requires a Database Engine instance. Also, it is now only available in the Instance Features section, and is no longer available in the Shared Features section.

Beginning with SQL Server 2022, runtimes for R, Python, and Java are no longer installed with SQL Setup. You must run the SQL Setup Wizard to install Machine Learning Services and Language Extensions. Then you must install your desired R, Python, or Java runtime(s) and packages.

You can install and use your open-source package and framework of choice, such as PyTorch, TensorFlow, and others. Machine Learning Services use an extensibility framework to run Python and R scripts.

EXEC sp_configure  'external scripts enabled';

• Note that there are separate Microsoft Docs articles for installation of Machine Learning Services on SQL Server 2019 and prior, and for SQL Server 2022. For installation on SQL Server 2022 on Windows, visit https://learn.microsoft.com/sql/machine-learning/install/sql-machine-learning-services-windows-install-sql-2022. For information about installing Machine Learning Services for SQL Server 2022 on Linux, see https://learn.microsoft.com/sql/linux/sql-server-linux-setup-machine-learning-sql-2022.

Availability groups are supported for Machine Learning Services, to ensure business continuity by configuring packages on each node, and failover cluster instances are supported from SQL Server 2019 onward.

You can execute Python and R scripts on a SQL Server instance with the stored procedure sp_execute_external_script.

You can find more details on each framework for this evolving feature in these Microsoft Docs articles:

• Extensibility framework. https://learn.microsoft.com/sql/machine-learning/concepts/extensibility-framework

• Python extension. https://learn.microsoft.com/sql/machine-learning/concepts/extension-python

• R extension. https://learn.microsoft.com/sql/machine-learning/concepts/extension-r

Install PolyBase Query Service for External Data

The PolyBase connector is a much-marketed feature for allowing native connectors for external data sources—even non-Microsoft or non–relational database platforms like Oracle, Teradata, and MongoDB.

Using PolyBase EXTERNAL tables, we can use SQL data types and T-SQL queries to seamlessly query data sources in-place in what Microsoft calls data virtualization. This eliminates the need for complex heterogeneous data movement and reduces the need for developers to have knowledge of other external query languages.

The PolyBase Query Engine feature is specifically designed for read and write queries on non-Microsoft database platforms like Oracle and DB2, but also for Azure Blob Storage files, MongoDB, and more. This is a superior alternative to linked servers to the same external data sources, because PolyBase allows “push down” computation for these external sources, reducing the amount of data transferred and increasing the performance of analytical-scale queries.

Install Azure extension for SQL Server

A new feature for SQL Server 2022 is extensibility for Azure features. This is in large part where the connections are initially set up for the features that make up the most Azure-connected version of SQL Server to date. Let’s look at the most common ones available so you understand what you are setting up.

Azure Arc–enabled servers

Azure Arc–enabled SQL Server instances are on-premises but still managed by Azure. This extends the services of Azure to the datacenter or wherever it is needed.

Azure Arc–enabled servers are supported only for the following operating systems:

• Windows Server 2012 R2 and higher

• Ubuntu 16.04 and 18.04 (x64)

• Red Hat Enterprise Linux (RHEL) 7 (x64)

• SUSE Linux Enterprise Server (SLES) 15 (x64)

To perform all the actions needed to connect an Azure Arc–enabled server to Azure, you need an account with all of the following privileges:

• Microsoft.HybridCompute/machines/extensions/read

• Microsoft.HybridCompute/machines/extensions/write

• Microsoft.HybridCompute/machines/extensions/delete

• Microsoft.HybridCompute/machines/read

• Microsoft.HybridCompute/machines/write

• Microsoft.GuestConfiguration/guestConfigurationAssignments/read

• Microsoft.Authorization/roleAssignments/write

• Microsoft.Authorization/roleAssignments/read

To enable the services so that Azure Arc recognizes your instance, you need to register it for the services you want to take advantage of. There are a few steps to follow, both in Azure and on the server itself, for existing instances. Detailed instructions on how to do this can be found at https://learn.microsoft.com/sql/sql-server/azure-arc/overview.

Microsoft Defender for Cloud

Microsoft Defender for Cloud is a Cloud Security Posture Management (CSPM) and Cloud Workload Protection Platform (CWPP) that can be run in Azure but has been extended to on-premises and third-party clouds for multi-cloud opportunities with Azure Arc. The purpose of Defender is to assess, secure, and defend from threats. It does this by:

• Continuously assessing your security posture so you can identify opportunities, track vulnerabilities, and report

• Securing resources and checking best practices to provide cloud recommendations

• Defending from, detecting, alerting on, and resolving threats in real-time so you can prevent security events from happening

  • For more detailed steps on setting up Microsoft Defender for Cloud, see the Microsoft Cloud Guide tutorial at https://mslearn.cloudguides.com/guides/Protect%20your%20multi-cloud%20environment%20with%20Microsoft%20Defender%20for%20Cloud.

Microsoft Defender is only supported for SQL Server on Windows machines and must have one of the RBAC roles assigned to it, as described in the next paragraph.

• Details on how to install Defender on your Azure Arc–enabled server for SQL Server can be found at https://learn.microsoft.com/sql/sql-server/azure-arc/configure-advanced-data-security.

Microsoft Defender for Cloud uses Azure role-based access control (RBAC)—a built-in set of roles assigned to users, groups, and services in Azure—to assess, manage, and access resources. Users require the Assignments role with write permissions, such as a User Access Administrator or Owner. You can access information related to a resource when you are assigned the role of Owner, Contributor, or Reader for the subscription or the resource’s resource group.

Other built-in roles are specific to Microsoft Defender for Cloud:

• Security Reader users have viewing rights, which lets them view recommendations, alerts, security policies, and security states, but not make changes.

• Security Admin users have the same rights as Security Reader users but can also update the security policy, dismiss alerts and recommendations, and apply recommendations.

  • For detailed instructions on how to assign roles in the Azure portal, see “Assign Azure roles using the Azure portal” at https://learn.microsoft.com/azure/role-based-access-control/role-assignments-portal.

  • Find instructions for assigning administrator roles in Azure AD at https://learn.microsoft.com/azure/active-directory/roles/manage-roles-portal.

Azure AD Authentication

New with SQL Server 2022, you can authenticate SQL Server with Azure AD using the following methods:

• Azure AD Password

• Azure AD Integrated

• Azure AD Universal with Multi-Factor Authentication

• Azure AD access token

Azure AD support makes hybrid integrations with Azure Synapse Analytics, Azure SQL Managed Instance, Azure Arc, and other services easier. If your Windows Server AD is federated with Azure AD, users can use those credentials to sign into SQL Server. However, Azure AD authentication does not support service accounts or other complex architectures of AD.

Azure AD support requires that both SQL Server and the host server (Windows or Linux) be registered with Azure Arc.

• For more details on Azure AD, see Chapter 12.

Microsoft Purview

Microsoft Purview is a data-governance tool designed to support organizations in finding, understanding, governing, and consuming data stores. Microsoft Purview has been a cloud-first feature for some time, and has come to SQL Server on-premises with SQL Server 2022.

As with many other Azure hybrid features, SQL Server must be registered with Azure Arc to use Microsoft Purview. In addition, you will need to create a Microsoft Purview account and enable Azure AD.

• For information on creating a Microsoft Purview account, see https://learn.microsoft.com/azure/purview/create-catalog-portal.

Azure extension for SQL Server

To connect SQL Server to Azure Arc and take advantage of Microsoft Defender, Azure AD, and Microsoft Purview, you must install the Azure extension for SQL Server during SQL Server Setup on the Azure Extension for SQL Server page (see Figure 4-2). You can use your existing Azure credentials or an Azure Service Principal, and then complete the required fields such as Azure Research Group, Azure Region, and Azure Tenant ID. If you are not interested in connecting your SQL Server instance to Azure Arc, simply deselect the Azure Extension for SQL Server check box.

Start SQL Server Setup from the command line

You can start setup.exe from either Windows PowerShell or the command prompt, providing repeatability and standardization of parameter options. You also can use it to prefill sections of the Setup wizard or to change the default behavior of Setup.

For the purposes of the installer, ensure you always use the Administrator level for these two shells. The title on each application window should be preceded by Administrator:—for example, Administrator: Windows PowerShell.

Sometimes you also might find it necessary to start Setup from the command line or Windows PowerShell because of a workaround for a specific problem or to automate and standardize future SQL Server installations. To start Windows PowerShell or the command prompt as Administrator, in the Start menu, search for the desired application, right-click it, and then select Run As Administrator on the shortcut menu that opens.

From the location of the SQL Server Setup installation files—for example, the mounted .iso file—execute the following command with PowerShell or the Windows Prompt:

Click here to view code image

.setup.exe /ConfigurationFile=c:installSQL2019_basic.INI

This sample script, and all scripts for this book, are available for download at https://www.MicrosoftPressStore.com/SQLServer2022InsideOut/downloads. The preceding code sample uses a configuration file to pre-select installation choices—for example, features to be installed. Let’s talk more about configuration files.

Generate a configuration file

Writing a configuration file by hand is not necessary, and can be tedious. Instead of going through that effort, you can let SQL Server Setup create a configuration file for you.

Work your way through the normal SQL Server Setup user interface, completing everything as you normally would, but pause when you get to the Ready to Install page. Near the bottom of this page is a path (see Figure 4-3). At that location, you’ll find a generated configuration file, ready for future use and modification if needed.

For example, the first modification you need to make to the .ini file is to accept the SQL Server license terms via the IACCEPTSQLSERVERLICENSETERMS parameter, which isn’t automatically provided in the automatically generated .ini file. Unless you modify an .ini file to provide this, it isn’t possible to run the installer without user interaction.

Install by using a configuration file

Now that you have a configuration file generated using the previous walk-through, you can take the next step to automate or standardize your installation.

You can start setup.exe from a command prompt with a configuration file by using the /CONFIGURATIONFILE parameter of setup.exe. Or you can launch SQL Server Setup with a configuration file by navigating to the Advanced page of the SQL Server Installation Center that starts with setup.exe in Windows. Once there, select the Install Based On A Configuration File check box. A message appears, asking you to browse to the .ini file. After you select the appropriate file, setup.exe will start with those options.

One thing to keep in mind is that configuration files generated by setup.exe do not and should not store the passwords you provided for any service accounts. If you do want to configure service account credentials in your configuration file, for security reasons, do not store the service account passwords in plain text in a configuration file. Instead, store passwords separately and securely, and provide them when you run setup.exe.

Each service’s account parameters are available in a setup.exe runtime parameter, which is listed in Table 4-1.

For example, in the snippet that follows, the PROD_ConfigurationFile_Install.INI provides the account name of the SQL Server Database Engine service account, but the password is provided when setup.exe runs in the command prompt or PowerShell:

Click here to view code image

setup.exe /SQLSVCPASSWORD="securepwd" /ConfigurationFile="d:SQLPROD_Install.INI"

You can provide further parameters like passwords when you run Setup. Parameter settings provided override any settings in the configuration file, just as the configuration file’s settings override any defaults in the Setup operation. Table 4-2 lists and describes the parameters.

By default, the /UpdateEnabled parameter is enabled and doesn’t need to be specified, and SQL Server will include updates found via Windows Update. If you choose to disable this behavior by providing /UpdateEnabled=False, you can also specify /UpdateSource as the location of the cumulative update or other SQL patch file executables to be included in the installation.

Check your SQL Server patch level version and apply patches if necessary

After you install SQL Server, check the version number against the latest cumulative updates list—especially if you did not opt to or could not use Windows Update during SQL Server Setup. You can view the version number in SQL Server Management Studio’s Object Explorer or via a T-SQL query on either of the following built-in functions:

Click here to view code image

SELECT @@VERSION;
SELECT SERVERPROPERTY('ProductVersion');
SELECT SERVERPROPERTY('Edition');

While you’re at it, double-check that you installed the right edition of SQL Server, too!

Review maximum server memory settings for other features

Other features of SQL Server have their own maximum server memory settings. As you will notice by their default settings, for servers on which both the Database Engine and SSAS and/or SSRS are installed, competition for and exhaustion of memory is possible. It is recommended that you protect the Database Engine by lowering the potential memory impact of other applications.

Limit SSAS memory

SQL Server Analysis Services (SSAS) has not just one maximum server memory limit, but five, and you can enforce limits by hard values in bytes or by a percentage of total physical memory of the server.

You change these memory settings via SSMS by connecting to the SSAS instance in Object Explorer. To start, right-click the server and select Properties on the shortcut menu. Some of the memory settings described here are identical for Multidimensional and Tabular installations, whereas others are for Tabular mode only:

• LowMemoryLimit. A value that serves as a floor for memory, but also the level at which SSAS begins to release memory for infrequently used or low-priority objects in its cache. Below this level, no memory maintenance is performed by SSAS. The default value is 65, or 65 percent of total server physical memory (or technically the virtual address space, but SSAS, among other features, is no longer supported on 32-bit systems, so this is not a concern).

• TotalMemoryLimit. A value that serves as a threshold for SSAS to begin to release memory for higher priority requests. This is not a hard limit. The default is 80 percent of total server memory.

• HardMemoryLimit. A hard memory limit that leads to more aggressive pruning of memory from cache and potentially to the rejection of new requests. By default, this is displayed as 0, and is effectively the midway point between the TotalMemoryLimit and the server physical memory. The TotalMemoryLimit must always be less than the HardMemoryLimit.

• VertiPaqMemoryLimit. For SSAS installations in Tabular mode only, a value that serves as a memory limit for the VertiPaq processing engine. The default is 60, or 60 percent of server physical memory. Above this percentage, and only if VertiPaqPagingPolicy is turned on (it is by default), SSAS begins to page data to the hard drive using the OS page file. Paging to a drive can help prevent out-of-memory errors when the HardMemoryLimit is met.

• QueryMemoryLimit. A value that can limit the amount of memory used by individual DAX queries, preventing any one query from dominating memory. For any individual query, this setting can be overridden by a new XMLA property, DbpropMsmdRequestMemoryLimit, specified for the query connection. This setting can be specified as a percentage (values <=100) or as a number of bytes greater than 100. The default setting of 0 implies no limit to the memory of individual queries.

Figure 4-4 shows the General page of the Analysis Server Properties dialog box, as started in Object Explorer in SSMS, and the locations of the preceding memory configuration properties with their defaults in SQL Server 2019 for a Tabular mode installation of SSAS. Note that the Show Advanced (All) Properties check box is checked.

Limit SSRS memory

Four options are available for limiting SQL Server Reporting Services (SSRS) memory: MemorySafetyMargin, MemoryThreshold, WorkingSetMaximum, and WorkingSetMinimum. All four are based on numbers contained in tags within a config file, so be sure to make a backup of it before editing. You can configure memory settings only in the RSReportServer.config file, which is a text file stored at %ProgramFiles%Microsoft SQL Server Reporting ServicesSSRSReportServer.

Two of the settings are in the config file by default; two more are available to administrators to use in advanced scenarios. Let’s look at each one:

• MemorySafetyMargin. The percentage of WorkingSetMaximum that SSRS will use before taking steps to reduce background task memory use and prioritize requests coming from the web service, attempting to protect user requests. User requests could still be denied.

• MemoryThreshold. The percentage of WorkSetMaximum at which SSRS will deny new requests, slow down existing requests, and page memory to a hard drive until memory conditions improve.

Two more settings are given values automatically upon service startup, but you can override them in the config file. Two older memory settings from SQL Server 2005 with which SQL DBAs might be familiar are MemoryLimit and MaximumMemoryLimit, but these two values have been ignored since SQL Server 2008.

• WorkingSetMaximum. By default, this is the server’s total physical memory. This setting does not appear by default in the config file, but you can override it to reduce the amount of memory of which SSRS will be aware. This value is expressed in kilobytes of memory.

• WorkingSetMinimum. By default, this value is 60 percent of WorkingSetMaximum. If SSRS needs memory below this value, it will use memory and not release it due to memory pressure. This setting does not appear by default in the config file, but you can override it to increase the variability of SQL SSRS’s memory use.

These four settings can appear in the rsreportserver.config file. As demonstrated here, you could override the default settings to 4 GB maximum and 2 GB minimum (each expressed in KB):

Click here to view code image

<MemorySafetyMargin>80</MemorySafetyMargin>
<MemoryThreshold>90</MemoryThreshold>
<WorkingSetMaximum>4194304</WorkingSetMaximum>
<WorkingSetMinimum>2097152</WorkingSetMinimum>

Limit Machine Learning Server memory

Like SSAS and SSRS, the Machine Learning Server has a config file at %ProgramFiles%Microsoft SQL ServerMSSQL15.instancenameMSSQLBinn launcher.config.

By default, Machine Learning Server is similar to 20 percent of total server memory. You can override this by adding a tag to the config file to provide a value for MEMORY_LIMIT_PERCENT. This value is not in the config file by default.

Remember to make a backup of this config file before editing. The following is an example of the contents of the rlauncher.config file, with the default memory limit changed to 25 percent:

Click here to view code image

RHOME=C:PROGRA~2MICROS~1MSSQL1~4.SQLR_SERV~2
MPI_HOME=C:Program FilesMicrosoft MPI
INSTANCE_NAME=SQL2K22
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:Program FilesMicrosoft SQL
ServerMSSQL16.SQL2K22MSSQLExtensibilityData
PKG_MGMT_MODE=0
MEMORY_LIMIT_PERCENT=25

Review the surface area configuration

If you are a veteran SQL Server DBA, you will remember when the SQL Server Surface Area Configuration was a separate application. Now, surface area settings are a facet, accessed via the Facets dialog box in SSMS starting with SQL Server 2008.

To view surface area configuration settings in SSMS, open Object Explorer, connect to the SQL Server, right-click the server, and select Facets on the shortcut menu. (The Facets window sometimes takes a moment to load.) Then, in the dialog box that opens, change the value in the list box to Surface Area Configuration.

Most of these options should remain off unless needed because they present a specific potential for misuse by an administrator or unauthorized user. In typical installations of SQL Server 2022, however, you should consider enabling three of these options:

• Database Mail. This should be enabled on most instances to allow SQL Server to, at the very least, send out a message in case of a high-severity incident or job failure, and to allow developers to send custom email messages using the system procedure sp_send_dbmail. You also can turn this setting on or off via the Database Mail XPs option in sp_configure. (More about this setting in Chapter 9, “Automate SQL Server administration.”)

• Remote Dedicated Admin Connection. This could be particularly useful for bypassing a malfunctioning login trigger or Resource Governor. You also can turn this setting on or off via the remote admin connections option in sp_configure. (More on this setting in Chapter 13: “Protect data through classification, encryption, and auditing.”)

• CLR Integration. Turn on if you need to use SSIS or to write CLR objects. You also can turn this setting on or off via the clr_enabled option in sp_configure.

You should turn on other options in the Surface Area Configuration only if they are specifically required by an application and you are aware of the potential security concerns.

Set up SQL Agent

There are several post-installation tasks to set up in SQL Agent before SQL Server can begin to help you automate, monitor, and back up your new instance.

• Chapter 8, “Maintain and monitor SQL Server,” and Chapter 9 cover SQL Agent and monitoring topics in detail.

You will likely want to do the following:

1. Change the SQL Agent service from Manual to Automatic startup.

2. Set up a Database Mail account and profile (see Chapter 9) to send email notifications for alerts or job status notifications.

3. Set up an operator for a distribution group of IT professionals in your organization who would respond to a SQL Server issue.

4. Configure SQL Server Agent to use Database Mail, including a fail-safe operator.

5. Set up SQL Server Alerts for desired errors and high severity (Severity 21+) errors.

At the very least, these steps are put in place so that SQL Server can send out a call for help. Even if you have centralized monitoring solutions in place, the most rare and severe of errors should be important enough to warrant an email.

You can choose to configure many Windows Management Instrumentation (WMI) conditions, Performance Monitor counter conditions, and SQL Server Error messages by number or severity in SQL Server Alerts. However, do not overcommit your inboxes, and do not set an inbox rule to Mark As Read and file away emails from SQL Server. By careful selection of emails, you can assure yourself and your team that emails from SQL Server will be actionable concerns that rarely arrive.

• For much more information on maintaining and monitoring SQL Server, see Chapter 8.

Turn on TCP/IP if needed

Depending on the edition you have installed, the common network protocol TCP/IP is off by default. The only protocol that is on is Shared Memory, which allows only local connections. You will likely not end up using Shared Memory alone to connect to the SQL Server for common business applications that use multiple servers for database, web, and application tiers.

When you connect to SQL Server using SSMS while local to the server, you connect to the Shared_Memory endpoint whenever you provide the name of the server, the serverinstance, localhost, dot character (.), (local), .instance, or (local)instance.

TCP/IP, however, is ubiquitous in many SQL Server features and functionality. Many applications will need to use TCP/IP to connect to the SQL Server remotely. Many SQL Server features require TCP/IP to be enabled, including the Remote Dedicated Admin Connection (DAC), the availability groups listener, and Kerberos authentication.

To configure TCP/IP, open the SQL Server Configuration Manager application locally on the server. Then, in the left pane, select SQL Server Network Configuration. Browse to the protocols for your newly installed instance of SQL Server. The default instance of SQL Server, here and in many places, will appear as MSSQLSERVER.

You can also enable TCP/IP for a SQL Server instance with PowerShell:

Click here to view code image

Import-Module SqlServer
$wmi = new-object('Microsoft.SqlServer.Management.Smo.Wmi.ManagedComputer')
#Path to the local server
$path = "ManagedComputer[@Name='$env:COMPUTERNAME']/"
$path = $path+"ServerInstance[@Name='SQL2K22']/ServerProtocol[@Name='Tcp']"
#Enable the TCP protocol on the local server, on the named instance SQL2K22
$TCPIP = $wmi.GetSmoObject($path)
$TCPIP.IsEnabled = $true
$TCPIP.Alter()
$TCPIP.IsEnabled
#Restart SQL Server Database Engine service to apply the change

After turning on TCP/IP, regardless of what method you use, you need to restart the SQL Server Database Engine service for it to take effect.

Verify server power options

The Windows Server Power Options setting should be set to High Performance for any server hosting a SQL Server instance.

In other power plans, Windows might not operate the processor at maximum frequency during normal or even busy periods of SQL Server activity. This applies to physical or virtual Windows servers.

Review this setting and ensure that the group policy will not change it back to Balanced or another setting. Also ensure that group preferences are configured with High Performance selected for new SQL Servers. Finally, you may also need to check that the BIOS is also configured for High Performance.

Configure antivirus exclusions for SQL Server processes and files

Configure any antivirus software installed on the SQL Server to ignore scanning files with extensions used by your SQL Server data and log files. Typically, these will be MDF, LDF, and NDF files.

Also, configure any antivirus programs to ignore folders containing SQL Server files. These could include:

• Full-text catalog files

• Backup files

• Replication snapshot files

• SQL Server trace (TRC) files

• SQL Audit files

• Analysis Services database

• Log and backup files

• FILESTREAM and FileTable folders

• SSRS temp files and log files

Processes might also be affected, so set antivirus programs to ignore the programs for all instances of the SQL Server Database Engine service, Reporting Services service, Analysis Services service, and R Server (RTerm.exe and BxlServer.exe).

In SQL Server FCIs and availability groups, configure antivirus software to exclude the MSCS folder on the quorum drive if in use, the MSDTC directory on the MSDTC share, and the WindowsCluster folder on each cluster node, if they exist.

Optimize for ad hoc workloads

The server-level setting Optimize for Ad Hoc Workloads doesn’t have the most intuitive name. We are not optimizing ad hoc queries; we are optimizing SQL Server memory usage to prevent ad hoc queries from consuming unnecessary cache.

• For more about the Optimize for Ad Hoc Workloads setting, see Chapter 3.

For the unlikely scenario in which a large number of queries are executed only two times, setting this option to True would be a net negative for performance. Enabling this setting can also affect performance tuning for single-use queries.

• For more about cached execution plans, read Chapter 14.

Evaluate whether Lock Pages in Memory is necessary

Consider using the Lock Pages in Memory policy for environments in which instances of SQL Server are expected to experience memory pressure due to other applications, server limitations, or overallocated virtualized systems. This is an in-depth topic to be carefully considered.

• For more about the Lock Pages in Memory setting, see Chapter 2, “Introduction to database server components.”

• For more about the Windows page file, see Chapter 3.

Review the size and location of the Windows page file

The page file is used to page out system memory. It can also capture a system memory dump for crash forensic analysis, a factor that dictates its size on modern operating systems with large amounts of memory. Therefore, the general recommendation for the system page file is that it should be at least the same size as the server’s amount of physical memory. This is also why the page file is best moved to its own volume, away from the OS volume, so that it does not unexpectedly grow and create space issues.

• For more guidance on the operating system page file, see the section “Configure the operating system page file” in Chapter 3.

Set up scheduled backups, index maintenance, log retention maintenance, and integrity checks

Backups are a critical part of disaster recovery. They should begin as soon as possible after installation, and before users or applications begin to use the SQL Server.

Generate database backups, at least of the master and msdb system databases, right away. You should also back up other SQL Server Setup–created databases, including ReportServer, ReportServerTempDB, and SSISDB, as soon as possible.

• For more information on backups, index maintenance, and monitoring, see Chapter 11.

As soon as your new SQL Server instance has databases in use, regularly perform selective index maintenance and integrity checks that consider the current fragmentation levels of indexes rather than performing index maintenance on entire databases. In many cases, statistics maintenance may be more effective in the shorter term.

• For more information on automating maintenance, see Chapter 9.

Back up service master and database master keys

You should back up service master keys and any database master keys as they are created, securely storing their information.

• For more information on service master and database master keys, see Chapter 13.

To back up the instance service master key, use the following command:

Click here to view code image

BACKUP SERVICE MASTER KEY TO FILE = 'localfilepath_or_UNC'
ENCRYPTION BY PASSWORD = 'complexpassword'

As soon as database master keys come into existence in each user database—for example, as you implement features like transparent data encryption (TDE) or column data encryption, back up individual database master keys as follows:

Click here to view code image

BACKUP MASTER KEY TO FILE = 'localfilepath_or_UNC' ENCRYPTION BY PASSWORD =
'complexpassword'

If you implement TDE, Always Encrypted, native backup encryption, column encryption, or any other native or external solutions that generate certificates, keys, and/or passwords, develop a secure storage and retrieval method inside your enterprise. Failure to back up master and database master keys could compromise future disaster recovery attempts!

Increase SQL Agent and SQL error log retention from the defaults

By default, SQL Server maintains the current SQL Server Error Log plus six more historical error logs. Logs are cycled each time the SQL Server service is started.

One fun weekend of server troubleshooting or maintenance where the SQL Server service is restarted many times could wipe out a significant amount of your error history. This could make the task of troubleshooting periodic or business cycle–related errors difficult or impossible.

You need visibility into errors that occur only during a monthly processing, monthly patch day, or periodic reporting, for example. Follow these steps:

1. In SQL Server Management Studio, in Object Explorer, connect to the SQL Server instance.

2. Expand the Management folder, right-click SQL Server Logs, and select Configure in the shortcut menu.

3. Select the Limit the Number of Error Logs Before They Are Recycled check box.

4. For the Maximum Number of Error Log Files setting, type a value larger than 6. You might find that a value between 25 and 50 will result in more useful log history contained for multiple business cycles.

On SQL Server instances that generate a large amount of log noise, consider other options to reduce the clutter of the SQL Server Error Log, including Trace Flag 3226 to suppress the logging of successful backup operations. (Much more on this in the next section.)

You may also choose to configure a SQL Agent Job to manually cycle the SQL Server Error Log using sp_cycle_errorlog so that no one log file contains so much data it becomes unwieldly for scan and analysis. Consider scheduling sp_cycle_errorlog to execute weekly, and keep 50 SQL Agent error jobs, leaving at most 50 weeks of history.

Suppress successful backup messages

By default, SQL Server writes an event to the SQL Server error log upon a successful database backup, whether it be FULL, DIFFERENTIAL, or TRANSACTION LOG.

On instances with many databases, and with many databases in the full recovery model with regular transaction log backups, the amount of log activity generated by just their successful frequent log backups could flood the log with clutter, lowering log history retention.

• For more on backups, see Chapter 10, “Develop, deploy, and manage data recovery.”

SQL Server Trace Flag 3226 controls an option at the instance level to suppress successful backup notifications.

There are many trace flags available to administrators to alter default behavior—many more options than there are user interfaces to accommodate them in SQL Server Management Studio. Take care when turning them on and understand that many trace flags are intended only for temporary use when aiding troubleshooting. Because Trace Flag 3226 is intended to be a permanent setting, simply enabling the trace flag by using DBCC TRACEON is not sufficient, as the trace flag will no longer be active following a SQL Server service restart. Instead, add the trace flag as a startup parameter to the Database Engine service by using SQL Server Configuration Manager. In the Properties of the SQL Server service, go to the Startup Parameters tab, and use the syntax -Tflagnumber. This field is essentially adding parameters that are passed to the sqlserver.exe executable. For example, enter -T3226, then select Add. The change will not take effect until the SQL Server Database Engine service is restarted.

• For more information on SQL Server Configuration Manager, refer to Chapter 1, “Get started with SQL Server tools.”

Increase default SQL Agent history retention

Similarly, you might find that the SQL Server Agent history is not sufficient to cover an adequate amount of job history, especially if you have frequent job runs.

You can use SSMS to change the job history settings for SQL Server Agent. In Object Explorer, connect to the SQL Server instance. Then right-click SQL Server Agent, select Properties from the shortcut menu, and select the History page.

This page is not intuitive and can be confusing. The first option, Limit Size of Job History Log, is a rolling job history retention setting. Consider adding zeros to increase the maximum log history size in rows from the default of 1,000 to 10,000 or more, and increasing the maximum job history per job in rows from the default of 100 to 2,000 or more. This data is stored in the msdb system database and will cause that database to grow larger over time. Consider pre-allocating some additional file space to the msdb data file now.

Heads up: The second option on the History page, Remove Agent History, along with its corresponding Older Than text box, is not a rolling job history retention setting. Rather, it is an immediate and manual job history pruning. Select this second check box and select OK. When you return to the History page, you will find the second check box is cleared. Behind the scenes, SQL Server Management Studio immediately ran the msdb.dbo.sp_purge_jobhistory stored procedure to remove the job history once.

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).

• For step-by-step details on how to create a self-hosted IR, read the documentation here: https://learn.microsoft.com/azure/data-factory/create-self-hosted-integration-runtime?tabs=synapse-analytics.

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:

Click here to view code image

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

• For current detailed steps on how to set up your own Azure Synapse link for SQL Server 2022, see https://learn.microsoft.com/azure/synapse-analytics/synapse-link/connect-synapse-link-sql-server-2022#create-your-target-synapse-dedicated-sql-pool.

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

• For all the known limits and issues outstanding with Synapse Link, review the list here: https://learn.microsoft.com/azure/synapse-analytics/synapse-link/synapse-link-for-sql-known-issues.

Get started with SQL Server in a Docker container

One of the biggest attractions of running SQL Server in a container is that your choice of OS does not matter. While the container images of SQL Server use Linux as their base, your host machine can run Windows, Linux, or macOS.

While containers can run on almost all host operating systems, SQL Server in containers is only supported for production on Linux hosts running Intel or AMD 64-bit CPU architecture.

First, you will need to install Docker Desktop on your workstation.

• Download Docker Desktop from https://www.docker.com/products/docker-desktop.

After you have Docker installed, you can deploy a SQL Server container with the following steps:

1. Pull a copy of the container image from the Microsoft Container Registry (MCR) to your local environment. To do so, run this command from either a bash shell on Linux or macOS, or an elevated PowerShell prompt on Windows:

   Click here to view code image

   sudo docker pull mcr.microsoft.com/mssql/server:2022-latest

   • You can find out more about the bash shell in Chapter 5 and about PowerShell in Chapter 9.

2. Use the following command to deploy the container. Note that the backslash in this command is a way to split a single bash command across multiple lines:

   Click here to view code image

   sudo docker run -e 'ACCEPT_EULA=Y' -e 'MSSQL_SA_PASSWORD=<YourStrong!Passw0rd>' 
      -p 1433:1433 --name sql2022 
      -v /users/randolph/mssql:/mssql 
      -d mcr.microsoft.com/mssql/server:2022-latest

You may be curious what these parameters (also called switches in Linux) mean:

• The docker pull command downloads the container image to your local container repository.

• The docker run command is where the deployment takes place.

• The -e switch allows for an environmental variable to be passed into the deployment. (Chapter 5 covers environment variables.) In this case, you are accepting the End-User License Agreement (EULA) for SQL Server, and providing a strong password for the SA account.

• The -p (or --publish; note the double-dash before the parameter) switch publishes the container’s public and private port for your container. To run multiple SQL Server containers simultaneously, specify a TCP port other than 1433 for the subsequent containers that are deployed.

• The --name switch (note the double-dash before the parameter) specifies the name of your container. This is not required, but if it is not specified, the system will generate a name.

• The -v switch is probably the most important in terms of database use. It allows a persistent volume to be mounted from your local machine to your container. In this case, the local directory /users/randolph/mssql will appear in the container as /mssql. Use this directory to store database backups or data files to be mounted to the container.

• The -d switch refers to the container image you are deploying. In this case you are deploying a SQL Server 2022 container from the MCR.

  • These are only a few of the command-line parameters that you might need. The full list is documented here: https://docs.docker.com/engine/reference/commandline/run/.

After the container is deployed, execute the docker ps command (which lists all the running containers) to confirm that your container is running. (In some environments you may need to run sudo docker ps.) Also, you can connect to your container using SQL Server tools like SSMS or Azure Data Studio, or sqlcmd, by connecting to localhost. This is possible because when you deployed the container, you configured it to run on TCP port 1433, which is the default SQL Server port.

You can also connect into your container with an interactive shell and execute sqlcmd. The first command will launch the bash shell within your container:

Click here to view code image

sudo docker exec -it sql1 "/bin/bash"

After launching the interactive bash shell within your container, you then call sqlcmd using the full path, since it is not in the path by default:

Click here to view code image

/opt/mssql-tools/bin/sqlcmd -S localhost -U SA -P '<YourNewStrong!Passw0rd>'

Once your SQL Server container is deployed, you can execute T-SQL just like it was any other SQL Server.

Kubernetes as part of Docker Desktop

You can install Kubernetes with Docker Desktop. However, as mentioned, persistent volumes are not supported on Intel-based macOS. If you are using Docker on Windows and you are running Windows 10 or Windows 11 Professional, you can configure Kubernetes after enabling Hyper-V.

• You can find the instructions for deploying Docker with Kubernetes at https://docs.docker.com/desktop/kubernetes.

Kubernetes using minikube

Another commonly used option for development and testing of Kubernetes is minikube, which runs across Windows, Linux, and macOS. minikube is an open-source project that allows for a deployment to your local workstation.

• Configuring minikube is part of the main Kubernetes documentation, available at https://kubernetes.io/docs/tutorials/hello-minikube.

Kubernetes using the Azure Kubernetes Service

If you need to simulate a production environment, we recommend deploying using Azure Kubernetes Service (AKS). (See Figure 4-7.) AKS is a managed service that allows you to quickly deploy a Kubernetes cluster of 1 node or up to 100 nodes.

• Configuring AKS is part of the main Azure documentation, available at https://learn.microsoft.com/azure/aks/learn/quick-kubernetes-deploy-cli.

AKS offers the benefit of hosting a highly available control plane for the cluster in Azure, as well as deploying the latest release of Kubernetes without installing software, worrying about dependencies, or finding hardware to build on. The other benefit of AKS is that the service itself is free. You are charged only for the underlying VM compute costs. Storage in AKS is provided by using either Azure Managed Disks or the Azure File service that acts as a file share.