Configuration Checker

The Configuration Checker tool checks for conditions that might prevent a successful SQL Server 2022 installation. When you choose Configuration Checker, a wizard runs on the local server. There is no option to choose an alternate computer location. The wizard returns an HTML report listing all installation requirement rules (facets) and the results of each test. Most of these rules are universal to all Windows configurations, and you can easily remedy most of them.

• AclPermissionsFacet. Checks if the SQL Server registry keys are consistent. Certain registry keys are required to install SQL Server and some registry key values must match. If these matching values are not present and consistent, SQL Server will not be successfully installed or upgraded.

• FacetDomainControllerCheck. We recommend that you do not install SQL Server 2022 on a domain controller, for two reasons. First, it can compromise the security of both Active Directory and the SQL Server instance. Second, it can cause resource contention between the two services. Microsoft also recommends against installing SQL Server on a domain controller. For more information, see https://learn.microsoft.com/sql/sql-server/install/security-considerations-for-a-sql-server-installation#Install_DC.

• IsDotNetInstalled. This rule determines if the Microsoft .NET Framework 4.7.2 or newer is installed on the system.

• MediaPathLength. The path for the location from which SQL Server 2022 is being installed must be fewer than 260 characters in length.

• RebootRequiredCheck. No installation reboots can be pending.

• SetupCompatibilityCheck. No subsequent incompatible versions of SQL Server can be installed on the computer.

• ThreadHasAdminPrivilegeCheck. The account running SQL Server must have administrative rights on the computer.

• WmiServiceStateCheck. This checks whether the Windows Management Instrumentation (WMI) service is started and running on the computer.

Data Migration Assistant (DMA)

There is a link on the Planning tab in the Installation Center to download the Data Migration Assistant installation package.

This application is really two tools in one, which you can use to create two project types:

• An assessment of upgrade or migration readiness

• A migration of data between versions of SQL Server and/or Azure SQL Database

For the assessment project type, the source server can be either a SQL Server instance or Amazon Web Services (AWS) Relational Database Service (RDS) for SQL Server instance. For the migration project type, the source server must be a SQL Server instance. The following target server choices accommodate both assessment and migration:

• Azure SQL Database

• Azure SQL Managed Instance

• SQL Server on Azure Virtual Machines

• SQL Server

Assessment

The assessment project type of the Data Migration Assistant detects database-specific compatibility issues between origin and destination SQL Server versions during pre-upgrade discovery. It is common between versions for there to be deprecation and feature differences, and this is especially true if the target server type is an Azure SQL Database. If not addressed, some of these items might affect database functionality during or after upgrade. The tool neatly outlines all findings and makes recommendations.

The assessment project type examines the following aspects of upgrading or migrating SQL Server:

• Feature parity. The assessment project provides a comprehensive set of recommendations, alternative approaches available in Azure, and mitigating steps.

• Compatibility issues. The assessment project provides partially supported or unsupported features that would block migration. Compatibility issues are categorized as breaking changes, behavior changes, or deprecated features.

Migration

Using the migration project type of the Data Migration Assistant, an administrator can move a database’s schema, data, and other objects such as database roles from a source server to a destination server. The wizard associated with this feature works by providing a user with the option to select a source and destination server and to choose one or more databases for migration.

The Migration Scope setting allows you to choose what to migrate. You can choose between the database schema only, the data only, or both the schema and data.

Move to SQL Server

For SQL Server migrations, there must be a backup location that is accessible by both the source and the destination server—generally a UNC path, such as \server01foldersubfolder. If this is a network location, the service running the source SQL Server instance must have write permissions for the directory. In addition, the service account running the destination SQL Server instance must have read permissions to the same network location.

If this poses a challenge, there is a Copy The Database Backups To A Different Location That The Target Server Can Read And Restore From check box that you can select to break up the process into steps and utilize the (hopefully) elevated permissions of the administrator running the wizard.

When you select this option, the security privileges of the account of the individual running the Data Migration Assistant are used to perform the copy of the file from the backup location to the restore location. The user must have access to each of these locations with the needed read and write permissions for this step to succeed.

The wizard also gives the user the option to specify the location to restore the data files and log files on the destination server.

As a final step, the wizard presents the user with a list of logins for migration consideration, with conflicting login names or logins that already exist identified. Where possible, the wizard attempts to map orphaned logins and align login security IDs (SIDs).

Move to Azure SQL Database

The Data Migration Assistant tool performs an Azure SQL Database migration in two phases:

1. Schema. First, it generates a script of the database schema (you can save this script before deployment for archival and testing purposes), which you deploy to the destination database.

2. Data. If you choose to move the data, another step is added after the creation of the tables on the destination database. This gives you the opportunity to verify that all tables exist in the destination database after the initial schema migration. Data migration uses Bulk Copy Program (BCP) under the hood. Any trusted constraints from the source database should be trusted in the database after data is copied, but it is a best practice to validate that constraints did not become untrusted in the copy process.

The schema migration is required; the data migration is optional.

Data Quality Server

To install Data Quality Server, you must first select its check box during SQL Server 2022 setup. This copies an installer file to a drive you specify. After you install SQL Server 2022, you can install Data Quality Server. To start, in your Windows Start menu, expand Microsoft SQL Server 2022, and then select SQL Server 2022 Data Quality Server Installer. This runs the DQSInstaller.exe file. The installation asks you to type and confirm a database master key password. It then creates three new databases into the SQL Server instance chosen as the host server: DQS_Main, DQS_Projects, and DQS_Staging_Data.

Data Quality Client

The Data Quality Client is an application used in conjunction with master data management (not to be confused with Master Data Services), data warehousing, or just plain data cleaning. It is typically used by a data steward who has a deep understanding of the business and domain knowledge about the data itself. You can use this tool to create knowledgebases surrounding data element rules, conversions, and mappings to help manage and align data elements. You can also use it to create and run data quality projects and to perform administrative tasks.

To sign into a Data Quality Server using the Data Quality Client tool, you must be either a member of the sysadmin server role or of one of these three roles in the DQS_Main database:

• dqs_administrator

• dqs_kb_editor

• dqs_kb_operator

SQLCMD

The SQLCMD utility is used to run T-SQL statements, stored procedures, or script files, using an ODBC connection to a SQL Server instance.

Although some people might consider the SQLCMD utility “old school” because it has been around since SQL Server 2005, it is still very popular because of its utility in automating scripts. You can invoke SQLCMD from any of the following:

• Windows, Linux, or macOS command line

• Windows, Linux, or macOS script files

• SQL Server Agent job step

• Using PowerShell with the command line

BCP

If you think SQLCMD is “old school,” hold on to your hat. The Bulk Copy Program (BCP), introduced in 1992 with the release of the very first edition of SQL Server, makes SQLCMD look like the new kid on the block.

It is quite a testament that to this day, BCP is still a practical way to work with SQL Server to insert or export large quantities of data. It uses minimal logging techniques and bulk data flows to its advantage.

If this reminds you of SQL Server Integration Services, be aware that BCP is not nearly as powerful. You use BCP to move data between data files (text, comma-delimited, or other formats) and a SQL Server table.

You can use BCP to import files into SQL Server tables or to export data from SQL Server tables into data files. BCP requires the use of a format file to designate the structure of the receiving table and the data types allowed in each column. Fortunately, BCP helps you to create this format file quite easily.

There are a few things about BCP that you must understand and do for the tool to perform optimally:

• Put the database into the simple or bulk-logged recovery model.

• Drop any non-clustered indexes on the destination table.

• Insert sorted data and use the sorted_data option if a clustered index exists.

• Run BCP on the same machine as the SQL Server.

• Place source and destination files on separate physical drives.

• Manually grow SQL data files in advance if growth is expected.

• Take advantage of instant file initialization.

• Use sp_tableoption to set table lock on bulk load (TABLOCK) to ON.

  • For more information, go to https://learn.microsoft.com/sql/tools/bcp-utility.

SQL Server PowerShell Provider

If you love to use a command line or if you have begun to use PowerShell to help manage and maintain your SQL Servers, Microsoft offers the PowerShell Provider for SQL Server. It can be installed with Windows PowerShell and PowerShell 7.

The SQL Server PowerShell Provider uses SQL Server Management Objects (SMO), which are included when you install the SqlServer PowerShell module. These objects were designed by Microsoft to provide for the management of SQL Server programmatically. There are many ways that developers and administrators can use PowerShell to automate their work in SQL Server, especially when dealing with multiple server environments.

• To learn more about automation in SQL Server using PowerShell, see Chapter 8, “Maintain and monitor SQL Server.”

Scenarios for use

You can use Extended Events in a wide range of scenarios. There are more than 1,700 events available in SQL Server 2022. Here are some of the most common uses for Extended Events:

• Troubleshooting

• Diagnosing slowness

• Diagnosing deadlocks

• Diagnosing recompiles

• Debugging

• Login auditing

• Baselining

By scripting out an event session and using automation, you have a stock set of sessions that you can use to troubleshoot depending on the problem. You can deploy these solutions on any server that needs a closer examination into performance issues.

You can also use Extended Event trace to provide a baseline from which you can track code improvements or degradation over time.

Object Explorer and Object Explorer Details

Object Explorer is the default SSMS view, providing both a hierarchical and tabular view of each instance of SQL Server and the child objects within those instances (including databases, tables, views, stored procedures, functions, and so on).

Object Explorer presents two panes (see Figure 1-3): the Object Explorer pane (left) and the Object Explorer Details pane (right). The Object Explorer pane is strictly hierarchical, whereas the Object Explorer Details pane is both hierarchical and tabular. As such, the latter provides additional functionality over its companion pane; for example, object search and the selection and scripting of multiple noncontiguous objects. To display the Object Explorer Details pane, choose View > Object Explorer Details or press F7.

Server Registration

The Server Registration feature within SSMS can both save time and make it easier to manage a complex environment by saving a list of commonly accessed instances. Registering connections in advance for future reuse provides the following benefits:

• Preservation of connection information

• Creation of groups of servers

• Aliasing of servers with more meaningful names

• Ability to add detailed descriptions to both servers and server groups

• Import and export of registered server groups for sharing between machines or teammates

To access the Server Registration feature within SSMS, select View > Registered Servers or press Ctrl+Alt+G.

You can use SSMS to register and manage four different types of servers and services:

• Database Engine

• Analysis Services

• Reporting Services

• Integration Services

Database Engine

When you use the Server Registration feature to work with Database Engines, two nodes appear: Local Server Groups and Central Management Servers. Each of these has some very useful features.

Local Server Groups node

The Local Server Groups node allows for the addition of either freestanding individual server registrations or the creation of server groups. Think of server groups as folders within the Local Server Groups node. Each of these folders can contain one or more individual servers. Figure 1-4 shows one of the many ways in which you can use the Local Server Groups feature to organize and save frequently used Database Engine connections.

From the Local Server Groups node, you can access the following tools:

• Export Registered Server Wizard. To access the Export Registered Servers Wizard, right-click the Local Server Groups folder node or any folder or server nested within this node. Then, on the shortcut menu, select Tasks > Export. From there, you have quite a bit of freedom. You can choose to export from any level within the tree structure as well as whether to include usernames and passwords. In the preceding case, if you want to export only the Development Servers node and any servers within it, you can do so by choosing within this wizard where to save the created file and then build out an XML document with the extension .regsrvr.

• Importing Registered Servers. To access the Import Registered Servers Wizard, right-click the Local Server Groups folder node or any folder or server nested within this node. Then, on the shortcut menu, select Tasks > Import. This opens the Import Registered Servers dialog box, which you can use to select a previously created .regsrvr file, as demonstrated in Figure 1-5.

  

  From here you can choose the folder in which you would like the imported object or object tree to reside. If you select a folder that already contains the same structures you are attempting to import, a message will appear asking you to approve an update/overwrite to the existing object structure.

Central Management Servers node

As mentioned, the second node available in the Database Engine feature is Central Management Servers. At first glance, this might appear to be almost the same thing as the Local Server Groups node. It allows you to add servers and create folders with descriptive names to which you can add servers. However, the Centralize Management Servers node includes some very significant differences.

First, when using this feature, you must choose a SQL Server instance to play the role of a Central Management Server (CMS). You can alias the server with a new name, but the server itself must exist. After you have chosen a server to play this role and have created a CMS, you can create new server groups or individual server registrations using the same methods explained in the “Local Server Groups node” section.

Here is where things become interesting. If you right-click any level (a server, a group, or the CMS itself), you are presented with multiple options:

• New Query

• Object Explorer

• Evaluate Policies

• Import Policies

Anything that is run will be run against each of the servers in the chosen group’s tree. Running a query on the CMS itself will result in the query being run on every server hierarchically present in all trees within the CMS. This is a very handy feature, but of course, with great power comes great responsibility!

The default behavior of CMS is that multiple server results are merged into one result set. You can change and customize this behavior by choosing Tools > Options > Query Results > SQL Server > Multiserver Results and enabling the Merge Results setting. Other behavior options available here include Add Login Name and Add Server Name to the result set from a CMS query.

When you create a CMS on an existing SQL Server, others can access and use the structure setup, so there is no need to export or import to keep folders and structures synchronized. This is great for team collaboration and efficiency.

Filtering objects

In the default Object Explorer view, SSMS lists items within each category in alphabetical order, starting with the schema name. There are several main groups, or tree categories, that are common across all versions of SQL Server. These include the following:

• Databases. This provides a full list of databases (including system databases) on the SQL Server instance. Database snapshots also appear here.

• Security. This contains a diverse list of object types, including logins, server roles, credentials, cryptographic providers, and audits.

• Server Objects. These include backup devices, endpoints, linked servers, and server-level triggers.

• Replication. This provides information about publishers and subscriptions.

• Always On High Availability. This includes Failover Clustering and Availability Groups.

• Management. This category covers diverse features and tools, including Policy Management, Data Collection, Resource Governor, Extended Events, Maintenance Plans, Database Mail, DTC (Distributed Transaction Coordinator), and SQL Server error logs.

• SQL Server Agent. Includes jobs, alerts, operators, proxies, and error logs of its own.

• Integration Services Catalogs. This contains the SQL Server Integration Services package catalog, depending on the SQL Server version.

By default, SSMS lists all objects alphabetically beneath each tree category. When working with databases that have a large quantity of objects, this can become quite aggravating, as the user may be subject to long list load times and must expend energy scrolling and watching the screen very closely for the object in question.

Fortunately, SSMS has a filtering feature. You can apply filters to many object categories, such as user databases, tables, views, stored procedures, table-valued functions, user-defined functions, and even database users.

You can independently configure filter settings in either the Object Explorer pane or the Object Explorer Details pane. The available filters change based on the type of item selected (databases, tables, stored procedures, etc.). Table 1-1 lists the available filtering options for tables.

After you have selected a filter, the suffix (filtered) appears in the Object Explorer or Object Explorer Details tree above your filtered list.

To clear an applied filter and display all objects in a tree again, right-click a filtered category and choose Filter > Remove Filter.

Multi-Select

In the Object Explorer pane, you can select only one object at a time. The Object Explorer Details pane, however, provides a multi-select feature that enables you to work on multiple objects (tables, views, jobs, and so on) at the same time.

Following the standard in the Windows environment, the Shift key allows for the selection of contiguous objects, whereas the Ctrl key allows for the selection of objects one by one. You can initiate actions on multiple objects using the GUI or you can choose to script multiple objects at once. Scripting each object into its own file or merging all object scripting into one larger file are both available options. Merging object scripting is helpful for both creating and deleting multiple objects at once.

IntelliSense tools

IntelliSense is a ubiquitous Microsoft technology found in many of its products to help with code completion. IntelliSense effectively reduces the amount of typing you do by offering shortcuts and autocompleting keywords and object names. This also makes your code more accurate.

Additionally, SSMS comes with snippets to help you code more easily. Snippets are preconfigured code fragments that you can quickly drop into (using the Insert Snippet command) or around (using the Surround With command) an existing block of code. You also can create your own snippets—you build them using XML—but that is beyond the scope of this discussion.

Let’s look at some use cases for snippets.

One of the options in SQL Server 2012 and later versions is to include a snippet for an IF statement. After testing a block of code, you can quickly add the IF statement (including the BEGIN/END statements) by highlighting your code and choosing a snippet.

There are three ways to access snippets:

• Use a keyboard shortcut, such as Ctrl+K followed by Ctrl+S (Surround With), and Ctrl+K followed by Ctrl+X (Insert Snippets).

• Right-click and choose an option from the context menu that opens.

• Choose Edit > IntelliSense and select a snippets option—for example, Surround With. (See Figure 1-6.)

When you choose Surround With, a snippet surrounds the highlighted code with the snippet template code. You can even insert “placeholder” text for replacing later.

There are only a few stock Surround With snippet options, but there are many Insert Snippet options. You can find these by choosing Edit > IntelliSense > Insert Snippet. You can also double-click the Function folder to see the available snippets and use them the same way you do the Surround With snippets (except that the code is placed at the current location of the cursor within a block of code rather than around selected code).

Guided upgrades using the Query Tuning Assistant

SSMS 18.0 introduced the Query Tuning Assistant (QTA), which works with the Query Store to help guide SQL Server upgrades from SQL Server 2016 and SQL Server 2017 to SQL Server 2019 and above. Note that the QTA does not support Azure SQL Database, Azure SQL Managed Instance, or Azure SQL Analytics.

• The QTA is covered in more detail in Chapter 14, “Performance tune SQL Server.” You can also read the official documentation at https://learn.microsoft.com/sql/relational-databases/performance/upgrade-dbcompat-using-qta.

Customize menus and shortcuts

SSMS is based on the Visual Studio integrated development environment (IDE), which means that it is customizable and extensible. Adding extensions is beyond the scope of this book, but the next few sections describe how to customize elements such as the toolbars and keyboard shortcuts.

Customize toolbars

By default, SSMS displays only the standard toolbar at the top. However, there are many other toolbars available for use. To access these options, on the toolbar, select Tools > Customize. This opens the Customize dialog box, which has two tabs:

• Toolbars. On this tab, you can select the toolbars that are useful in your work environment. Among the many choices are toolbars for working with database diagrams, Extended Events, and XML.

• S. You can use this tab to set up a custom toolbar or to edit the drop-down menus and functionality of an existing toolbar.

Tool options

You also can customize the appearance of your SSMS interface. Select Tools > Options to adjust the color, font, keyboard hotkeys, length of strings in results, location of results, scripting preferences, international settings, theme, autorecovery timeframe, and more.

One very handy option in the Options dialog box is Keyboard > Query Shortcuts. SSMS comes with several shortcuts already turned on (see Figure 1-7), but you can use this setting to tailor them to your needs. Many long-time DBAs make heavy use of this setting to reduce the number of keystrokes required to carry out common procedures.

Using shortcuts in SSMS, you can highlight text and then activate the keyboard shortcut to run the associated stored procedure, supplying a parameter of the highlighted text. For instance, to see the name, created date, and a list of columns and associated data types in a view, you can use the sp_help system procedure. As you can see in Figure 1-7, this procedure is called by the keyboard shortcut Alt+F1. This shortcut is not modifiable, but several others are.

If you populate a query window with the name of a view, and then use the keyboard shortcut Alt+F1, you will see results like the ones shown in Figure 1-8.

Overview

Four graphs in the Overview relay the most basic overview of the instance. The % Processor Time graph is an average combined value for all logical processors assigned to the instance (see the section “Allocate CPU cores with an affinity mask” in Chapter 3, “Design and implement an on-premises database infrastructure”). The other three graphs are Waiting Tasks (an instance-level value), Database I/O (all databases, measured in MB/sec) and Batch Requests/sec (all databases).

Each graph displays information in near-real time, refreshing every 10 seconds by default. You can configure the refresh interval by right-clicking any of the graphs and choosing an option from the context menu that appears. The refresh interval can be as short as 1 second to as long as 1 hour. The graph settings are adjusted as a unified set; all four graphs use the same interval setting, so changing one interval changes them all. Likewise, selecting Pause on any of the graphs pauses the entire set.

Processes

The Processes section of Activity Monitor displays all non-system processes (also known as tasks) with open connections to the SQL Server instance, regardless of whether the process is actively running a query. It provides the following types of important metadata:

• Session ID. The session process identifier (SPID) of the current process

• User Process. Displays a 1 if this is a user process and a 0 if it is a system process (this list is filtered to show only user processes by default)

• Login. The login name of the user running this process

• Database. The name of the database

• Task State. Populated from the list of possible tasks in the task_state column of the sys.dm_os_tasks dynamic management view

• Command. Populated from the list of command types in the command column of the sys.dm_exec_requests dynamic management view

• Application. The name of the application

• Wait Time (ms). The amount of time this task has been waiting, in milliseconds

• Wait Type. The current wait type for this task

• Wait Resource. The resource for which this task is waiting

• Blocked By. If this task is being blocked by another process, this shows the SPID of the blocking process

• Head Blocker. If there is a chain of blocking processes, this is the SPID of the process at the start of the blocking chain

• Memory Use (KB). How much memory this process is using

• Host Name. The host name of the machine that made this connection

• Workload Group. The name of the Resource Governor workload group that this process belongs to (you can read more about the Resource Governor in Chapter 3)

Each column allows you to filter to specific values. For example, right-click any row in the Processes pane to see the detailed T-SQL query being run (last T-SQL command batch), trace the process in SQL Profiler, or kill the process.

Resource Waits

The Resource Waits section of the Activity Monitor displays the wait events of active, open connections, sorted by default by the cumulative wait time (in seconds).

This can be very useful when you’re trying to determine the root cause of a performance issue. Having a baseline for these counters when things are good is very useful later, when you’re trying to gauge whether a problem you’re experiencing is new or normal.

Understanding the meaning of certain wait times can help you diagnose the root cause of slowness, be it storage, memory pressure, CPU, network latency, or a client struggling to receive and display a result set. Following are the wait statistics provided by this section:

• Wait Category

• Wait Time (ms/sec)

• Recent Wait Time (ms/sec)

• Average Waiter Count

• Cumulative Wait Time (sec)

  • You can read more about Resource Waits at https://learn.microsoft.com/sql/relational-databases/system-dynamic-management-views/sys-dm-os-wait-stats-transact-sql or by referring to the “Understand wait types and wait statistics” section in Chapter 8.

Data File I/O

The Data File I/O section of the Activity Monitor displays the difference between two readings taken from the metadata stored in the sys.dm_io_virtual_file_stats dynamic management view. For this reason, when you first expand this section, you might not see results for a short while. The server needs at least two readings, so, for example, if your interval is 10 seconds, you’ll wait 10 seconds before data appears.

The information displayed shows each of the data files for all of the databases on the SQL Server, the file location and name, megabytes per second read (MB/sec read), megabytes per second written (MB/sec written), and average response time in milliseconds (ms).

Generally, average response times of 5 milliseconds or less allow for acceptable performance, the occasional outlying peak notwithstanding.

Recent Expensive Queries

The Activity Monitor’s Recent Expensive Queries section displays information about the most expensive queries that have run on the SQL Server instance in the past 30 seconds. It includes both queries in flight and queries that have finished.

To see the full query text or the execution plan currently in use, right-click any of the queries listed. Here are the fields returned in this pane:

• Query

• Executions/min

• CPU (ms/sec)

• Physical Reads/sec

• Logical Writes/sec

• Logical Reads/sec

• Average Duration (ms)

• Plan Count

• Database

Active Expensive Queries

If you’re trying to determine what is running at this precise moment that might be causing performance issues, the Active Expensive Queries part of the Activity Monitor is the place to look. It is more granular than the aggregated “past 30 seconds” view provided in the Recent Expensive Queries section. In addition, the list of queries in this part shows some very interesting details that are available at only a granular level:

• Session ID

• Database

• Elapsed Time

• Row Count

• Memory Allocated

Again, you can see the full query text and the execution plan by right-clicking, but here you get an additional feature: Show Live Execution Plan. This might differ if a query is running long.

Job Activity Monitor

Job Activity Monitor provides a snapshot view of all jobs on a server. Using this feature, you can quickly see many attributes of the jobs scheduled on a SQL Server instance, and you can use many of these attributes to narrow the list of jobs displayed. Table 1-2 lists the attributes.

Notify operators with alerts

You can configure alerts to notify you when a specific event occurs. Unlike jobs that run on a schedule, alert notifications can be sent in reaction to a scenario that has been set off. Examples include emailing the DBA team when a data or log file experiences auto growth, or when Target Server Memory drops below a certain threshold on a virtual machine.

SQL Server Agent’s alerting feature gives administrators the ability to create three different types of alerts:

• Event alerts. These are raised by SQL Server’s Error and Severity mechanism. You can specify this for all databases or for a single database. You can use an error number or a severity level to set off an alert. Text within the system message can be parsed to only alert in specific scenarios.

• Performance condition alerts. These alerts use the entire library of SQL Server Performance Monitor counters. Any counter object can be chosen, the sub counter object specified, the counter instance noted (if applicable), and a threshold (falls below, becomes equal to, or rises above) provided at which an alert should fire. Figure 1-10 shows the configuration of a performance condition alert definition to notify an administrator if the Page Life Expectancy metric on a SQL Server instance drops below 5 minutes.

  

• Windows Management Instrumentation (WMI) alerts. A WMI alert uses the Windows Management Instrumentation Event Provider to allow for more complicated actions upon the detection of an event that sets off an alert. One example is to use the alerting system to detect a deadlock and then save the XML deadlock graph information to a table for later analysis. Another is to detect any DDL or system configuration changes that occur and to document them for later review. Because the WMI Provider has access to many server event classes and properties, this feature is quite versatile. It does come with several catches, however:

  • It requires Service Broker to be enabled in the msdb database of the instance.

  • If your code queries objects within a particular database on the server, Service Broker must be enabled on that database also.

  • It is not very GUI-friendly and requires a bit more programming know-how than the other alert options.

  • It is not supported on SQL Server on Linux or Docker containers.

  • You can read more about creating WMI alerts in Chapter 8, as well as at https://learn.microsoft.com/sql/ssms/agent/create-a-wmi-event-alert.

A response to an alert can be to run a job, notify a list of operators, or both.

Operators

Operators are users or groups designated as points of contact to receive notifications from the SQL Server Agent. They are most commonly defined with email addresses, but there are additional delivery methods available.

Extend the features of Azure Data Studio

Azure Data Studio allows for additional features that are not part of the base product, directly from the interface, on the Extensions pane. To access the Extensions pane, select View > Extensions, or press Shift+Ctrl+X (Windows and Linux) or Shift+Cmd+X (macOS).

Extensions recommended by Microsoft are identified by a white star on a blue background at the top left of the extension item. (See Figure 1-15.)

To install an extension, select its Install button. When the extension is installed, you may be prompted to reload the application; select the Reload button, and Azure Data Studio will reopen with the extension enabled.

Access the command line

One extremely useful feature in Azure Data Studio (and Visual Studio Code) is the built-in command line interface. This terminal allows you to interact with the underlying OS from within the application using familiar terminal types like PowerShell or the command prompt, or other terminal types like Git Bash. This improves productivity because you don’t need to switch away from the Azure Data Studio interface to manipulate files or execute external scripts.

To access the terminal, choose View > Terminal or use Ctrl+` (backtick) on Windows and Linux or Cmd+` on macOS.

Notebooks as runbooks

One particularly interesting feature for data professionals is SQL kernel support. This enables you to create powerful interactive and shareable notebooks with SQL Server and PostgreSQL as supported environments.

In this book, we refer to these notebooks as SQL notebooks. As of version 1.16.0 (released in March 2020), SQL notebooks support creating and saving charts.

The structure of SQL notebooks makes them an ideal format for runbooks. A runbook is a set of standardized written procedures for completing repetitive tasks. SQL notebooks allow you to mix cells containing text explanations for steps in a task with the cells containing SQL to be run to execute a task. The results of an executed SQL script can be saved in the notebook as well. This is helpful for showing expected results of a task.

Using the Markdown language, you can convert existing scripts and documentation into SQL notebooks. Because these documents are plain text files that render as web pages, they can be treated like source code and checked into a source control system. This gives you a lot more control over versioning, especially around runbooks.

• See Chapter 10, “Develop, deploy, and manage data recovery,” for a practical use case for runbooks.

SQL Server Integration Services Package Upgrade Wizard

The SQL Server Integration Services Package Upgrade Wizard is a tool that you can use to upgrade SSIS packages created in versions earlier than SQL Server 2022. Although you most commonly access this tool from SQL Server Data Tools, you can also find and launch it from SSMS and from the Windows command prompt. Part of the upgrade wizard in all these scenarios involves the automated backup of the original packages.

To launch this wizard from SQL Server Data Tools:

1. Open an Integration Services Project.

2. Right-click SSIS Packages.

3. Select Upgrade All Packages.

To launch it from SSMS:

1. Connect to Integration Services.

2. Expand Stored Packages.

3. Right-click the File System or MSDB node.

4. Select Upgrade Packages.

To launch it from the Windows command prompt:

1. Navigate to the Microsoft SQL Server160DTSBinn folder.

2. Locate and run the SSISUpgrade.exe file.

SQL Server Import and Export Wizard

The Import and Export Wizard simplifies the act of copying of data from a source to a destination. It uses SQL Server Integration Services to copy data by creating a package in memory. You can choose to save the package the wizard creates for future reuse. The variety of source and destination platforms supported by the wizard is generous. In some cases, you might need to download and install additional drivers and providers from a vendor or from a Microsoft Feature Pack. Table 1-3 lists examples of compatible data sources.

You can launch the Import and Export Wizard from the following places:

• The Start menu

• The command prompt

• SQL Server Management Studio

• SQL Server Data Tools

Data Profiling Task and Viewer

You can use the Data Profiling Task within SQL Server Integration Services to clarify data patterns (normal versus abnormal) and identify data quality issues before they reach a particular destination (usually a data warehouse). This tool provides visibility into the data quality by calculating and documenting the following metadata and statistical metrics:

• Column Length Distribution Profile. Reports the distinct lengths of strings in a selected column and the percent of total rows in the table that each string length represents. This helps you identify invalid values.

• Column Null Ratio Profile. Reports the percentage of null values in a selected column. This helps you identify unexpectedly high ratios of missing values.

• Column Pattern Profile. Reports a set of regular expressions that cover the specified percentage of values in a selected column. This helps you identify values that are invalid or not in the correct format.

• Column Statistics Profile. Reports minimum, maximum, average, and standard deviation for a selected numeric column, or minimum and maximum for a selected datetime column. This helps you identify values that may be outside of the expected range.

• Column Value Distribution Profile. Reports the distinct values in a selected column and the percentage of rows in a table that the value represents. This helps you identify unexpected values, especially when you know the number of distinct values expected.

The Data Profiling Task creates an XML output file. You can view this file by using the Data Profile Viewer, which is a standalone application and does not require Visual Studio or SQL Server Integration Services to run.

Figure 1-16 presents an example of the Data Profile Viewer. It displays the XML created by a Data Profile Task, pointed at Microsoft’s WideWorldImporters sample database and analyzing the Sales.Customers table.