Chapter 1
Getting started with SQL Server tools

This chapter provides information about where to find many of the Microsoft tools used to manage and work with the Microsoft SQL Server platform. It also walks you through the installation, configuration, and basic utility of each tool.

The chapter is divided into five distinct sections:

• Installation Center

  • The Planning Tab

  • The Installation Tab

• Tools installed with the Database Engine

• SQL Server Management Tools

• SQL Server Data Tools

The following section covers installing SQL Server natively on Microsoft Windows.

For more details on how to set up and configure SQL Server, read Chapter 4.

It might seem a bit confusing, but on the Installation tab, the installers for SQL Server Reporting Services, SQL Server Management Tools, and SQL Server Data Tools, are merely links that redirect to a download location on the Microsoft website for each of these components’ installation files. You also can download and install the tools independently without using Installation Center.

The same is true for the Planning tab, on which the Upgrade Advisor (also known as the Data Migration Assistant) is simply a launch point for a continually updated .MSI file that you can download.

1. FacetDomainControllerCheck. We recommend that you do not install SQL Server 2017 on a Domain Controller. There are two reasons for this. 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.

2. FacetWOW64PlatformCheck. Windows operating systems must be 64-bit to support a SQL Server 2017 installation.

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

4. NoRebootPackage. The correct .NET Frameworks must already be installed.

5. RebootRequiredCheck. No installation reboots can be pending.

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

7. SSMS_IsInternetConnected. Verifies that the computer is connected to the internet. This is required for validating a certificate when a .NET application starts.

8. ThreadHasAdminPrivilegeCheck. The account running the setup file must have Local Administrator rights on the computer.

9. WmiServiceStateCheck. Checks whether the Windows Management Instrumentation (WMI) service is started and running on the computer.

You can find the most up-to-date Rules Documentation at http://go.microsoft.com/fwlink/?LinkID=398124.

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 Microsoft Azure SQL Database

In both cases, the Source Server must be a SQL Server instance. The target server choices for all three Target Server Types accommodate both Assessment and Migration, as shown in Table 1-1.

Assessment The Assessment project type of the Data Migration Assistant detects database-specific compatibility issues between origin and destination SQL Server versions in the course of 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 post upgrade. The tool neatly outlines all findings and makes recommendations.

The Assessment project type examines the following aspects of upgrading SQL Server:

• Database compatibility. Looks at deprecated features and functionality issues that could be “show stoppers.”

• Feature parity. Identifies unsupported or partially supported features and functions that applications using the database might rely on. For example, if you plan to move to Azure SQL Database, these include Cross Database Queries, Server-Scoped Logon Triggers, and Trace Flags.

• Benefits from new features. This feature is expected sometime soon and is not currently available.

You can find more information online by searching for “Discontinued Database Engine Functionality in SQL Server XXXX,” where XXXX is your Source SQL Server platform.

Migration Using the Migration project type of the Data Migration Assistant, an administrator can move a database’s schema, data, and noncontained objects from a source server to a destination server. The wizard works by providing a user with the option to select a Source and Destination Server and to choose one or more databases for migration.

Moving to SQL Server For SQL Server migrations, there must be a backup location that is accessible by both the source and destination servers, generally a UNC path. 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 check box labeled Copy The Database Backups To A Different Location That The Target Server Can Read And Restore From 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 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).

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

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

• 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 of the tables exist in the destination database after the initial schema migration. Data migration makes use of Bulk Copy Program (BCP) under the hood.

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

During an in-place upgrade of an existing SQL Server instance, you can neither add nor remove components. The process will simply upgrade existing components.

If you have multiple versions installed on the same server (instance stacking), a number of shared components will be upgraded automatically, including SQL Server Browser and SQL Server VSS Writer.

You can read more about multiple instances and versions of SQL Server on Microsoft Docs at https://docs.microsoft.com/sql/sql-server/install/work-with-multiple-versions-and-instances-of-sql-server.

For more information about configuring different features, see Chapter 4.

You can install R and Python independently or together, depending on your requirements. What’s more, you can install these ML services directly in the Database Engine (in-database) or as standalone components.

The in-database option creates a secure integration between the Database Engine and the external runtimes containing the ML libraries. You can run queries using Transact-SQL (T-SQL) and make use of the Database Engine as the compute context.

If you decide to use of R or Python without installing SQL Server, you must choose the standalone option. Each service will then run in its own independent compute context.

You can read more about Machine Learning Services on Microsoft Docs at https://docs.microsoft.com/sql/advanced-analytics/r/sql-server-r-services.

Data Quality Services is a product that makes possible important data quality tasks, including the following:

• Knowledgebase-driven correction

• De-duplication

• Additional metadata enrichment

Data Quality Services has two parts: the Data Quality Server and the Data Quality Client. Data Quality Server has a dependency on the SQL Server Database Engine. Apart from that, you can install these two components on the same computer or on different computers. The tools are completely independent, and you can install one without having to install the other previously (i.e., the order doesn’t matter).

To be functional, the Data Quality Client tool needs only to be able to connect to a Data Quality Server. There are certain operations the Data Quality Client can perform that require an installation of Microsoft Excel local to the client installation. It is commonplace to have the Data Quality Client on one or more workstations, not the SQL Server instance itself.

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

• dqs_administrator

• dqs_kb_editor

• dqs_kb_operator

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

• Windows, Linux, or macOS command line

• Windows script files

• SQL Server Agent job step

• Using Windows PowerShell with the command line

If you are thinking about SQL Server Integration Services right now, that’s fine, but 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 it 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 (for more information go to https://technet.microsoft.com/library/ms177445.aspx):

• Use SELECT INTO syntax

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

• Drop any nonclustered 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

• Windows PowerShell Extensions for Microsoft SQL Server

• SQL Server Management Objects (SMO)

Both are installed by default with the SQL Server Management Tools, but you also can download and install them separately through the Microsoft SQL Server Feature Pack.

The SQL Server PowerShell Provider uses SQL Server Management Objects. These objects were designed by Microsoft to provide management of SQL Server programmatically. There are many ways that developers and administrators can use Windows PowerShell to automate their work in SQL Server, especially when dealing with multiple server environments.

To learn more, see Chapter 13.

Administrators use SQL Server Configuration Manager to manage SQL Server Services. These services include the SQL Server Database Engine, the SQL Server Agent, SQL Server Integration Services, the PolyBase Engine, and others. SQL Server Configuration Manager provides a GUI to perform the following tasks associated with SQL Server–related services:

• Start or stop a service

• Alter the start mode (manual, automatic, disabled)

• Change startup parameters

• Create server aliases

• Change the Log On As accounts

• Manage client protocols, including TCP/IP default port, keep alive, and interval settings

• Manage FILESTREAM behavior

The suggestions that Database Engine Tuning Advisor makes focus solely on indexing, statistics, and partitioning strategies. The Database Engine Tuning Advisor simplifies the implementation of any administrator-approved changes it suggests. You need to scrutinize these changes to ensure that they will not negatively affect the instance.

You can read more about how extended events are supported in Azure SQL Database, with some differences, at https://docs.microsoft.com/azure/sql-database/sql-database-xevent-db-diff-from-svr.

SQL Server extended events is an event-handling system created with the intent to replace SQL Server Profiler. Think of it as the “new and improved” version of Profiler. It is more lightweight, full-featured, and flexible, all at once. Extended events offer a way of monitoring what’s happening in SQL Server, with much less overhead than an equivalent trace run through the SQL Profiler. This is because extended events are asynchronous.

You access extended events through SQL Server Management Studio by connecting to a SQL Server instance and navigating to the Management folder. When you’re there, expand the Extended Events node to display Sessions. Right-click this, and then, on the shortcut menu that opens, select the New Session Wizard. You can then use this wizard to schedule events to run at server startup or immediately after the event has been created.

Scripting extended events sessions via T-SQL can be a much quicker and consistent way to create a library of extended event sessions for reuse in multiple environments. This gives you the flexibility to start and stop them as needed, even as a job in SQL Server Agent.

Events Because Profiler has been deprecated, a number of new features in SQL Server have matching extended events, but not Profiler events. This means that using extended events to capture diagnostic and performance visibility (rather than Profiler) provides a much larger library of events to choose from than Profiler. Each event has its own set of default fields. In addition, there are global fields (known as actions) that you can add to the collection of any event, for example, database_name, database_id, sql_text, and username.

Targets (Data Storage Options) Targets are basically the consumers, or recipients of events. Targets can log additional event context upon receipt of an event. The target options allow for different ways to view or even save event data. Although users can observe data during collection with extended events (watching the target in action), we do not recommend that you do so on a production machine. Targets come in two flavors:

• File (event_file target). Best for large datasets, later analysis, remote analysis for retrieval by a consultant or tool, or historical record keeping/baselines. Options with this target setting are where to save the file and what to name it, what the maximum file size should be, should the file roll over and how many files total should be saved.

• Memory (ring_buffer target). Best for smaller datasets or long sessions. With this option, the user can specify how many events to keep, how much memory to use (max), or how many events (per type) to keep.

Actions These are instructions that specify what to do when an event fires. Actions are associated with events.

Predicates These are filters that limit event firing and provide a more concise view of the issue being reviewed. Some examples are LoginName, ApplicationName, or SPID.

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

Upon installation, the data warehouse provides three reports: Server Active History, Query Statistics History, and System Disk Usage. You can create additional reports and add them to the data warehouse collection.

Using the three-report configuration of management data warehouse makes it possible for a database administrator to do basic performance baselining and to plan for growth. It also allows for proactive tuning activities. It might be good practice to set this up when you install a new instance of SQL Server.

1. In SQL Server Management Studio, in Object Explorer, expand the Management node, and then right-click Data Collection. On the shortcut menu that opens, point to Tasks, and then select Configure Management Data Warehouse, as shown in Figure 1-2.

   

   Figure 1-2 Navigating to the Management Data Warehouse menu item.

2. In the Configure Management Data Warehouse Wizard, Select Create Or Upgrade A Management Data Warehouse, and then click Next.

3. The server name is already populated, so either select the Management Data Warehouse database you are already using for collection by clicking it in the Database Name list box, or, to the right of the list box, click New. If you click New, in the New Database dialog box that opens, type the database name to which you want to store the information collected by the management data warehouse, as demonstrated in Figure 1-3.

   

   Figure 1-3 Creating a new database for collecting management data warehouse data.

4. Select a user to map to the Management Data Warehouse role.

1. In Object Explorer, expand the Management node, right-click Data Collection, point to Tasks, and then click Configure Data Collection.

2. In the wizard that opens, select the Server Name and Database Name specified during the installation. Finish by choosing any other needed settings.

Go back and view Data Collection node. Things look different now: The Data Collection Sets in Object Explorer as well as the Data Collection node itself no longer displays a red down-arrow icon, as depicted in Figure 1-4.

You install SQL Server Reporting Services in Native mode, which provides a web portal interface to manage and organize reports and other items. Internet Information Services (IIS) is not required to use SQL Server Reporting Services, as it was in versions prior to SQL Server 2016.

Completing the installation of SQL Server Reporting Services sets up and configures the following services and features:

• Installs the Report Server Service, which consists of the following:

  • Report Server Web Service

  • Web Portal for viewing and managing reports and report security

  • Report Services Configuraton Manager

• Configures the Report Service and Web Portal URLs

• Establishes the Service accounts needed for SQL Server Reporting Services to operate

You can read more about configuring SQL Server Reporting Services in Chapter 4.

After the installation is complete, using administrative rights, browse to the following directories to verify that the installation was in fact successful and that the service is running:

• http://localhost/Reports

• http://localhost/ReportServer

If you are running a nondefault instance of SQL Server, you might need to use the Web Portal URL tab in the Report Services Configuration Manager dialog box to determine the exact path of both the Web Service URL and the Web Portal URL, as illustrated in Figure 1-6.

• Create or select existing Report Server databases

• Define the URLs used to access the Report Server and Report Manager

• Configure the Report Server Service Account

• Modify the connection string used by the Report Server

• Set up email distribution capability

• Integrate with a Power BI service

Initially, Microsoft went with monthly releases, but beginning in August of 2016, the company changed to a versioned release system that allows for more flexibility, and no longer uses a month/year naming convention.

After you download the executable file, install and then watch the Package Progress and Overall Progress meter bars do their thing. There’s not much more to it than that. The installation finishes with a Setup Completed message.

At this point, you can start the application by browsing through your Start Menu to Microsoft SQL Server Tools 17 > Microsoft SQL Server Management Studio 17. For ease of access, you might want to pin the program to your Start Menu or copy the icon to your desktop.

Now that the tools used to manage SQL Server are completely independent of the Database Engine, upgrading these components has become very easy. It has also become much safer to upgrade: there is no longer any concern about accidentally affecting your production environment (SQL Server Database Engine) because you upgraded your SQL Server Management Studio toolset.

Object Explorer presents two panes (see Figure 1-7): 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, it provides additional functionality over its companion pane; for example, object search and multiple, and noncontiguous object selection and scripting. To display the Object Explorer Details pane, click View > Object Explorer Details, or press F7.

• 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 SQL Server Management Studio, click View > Registered Servers, or press Ctrl+Alt+G. You can use SQL Server Management Studio to manage four different types of servers and services:

• Database Engine

• Analysis Services

• Reporting Services

• Integration Services

Database Engine When you use the Registered Servers 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. 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-8 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.

  

  Figure 1-8 The Local Server Group.

• Exporting Registered Servers. To access the Export Registered Servers Wizard, right-click the Local Server Groups folder node or any folder or server nested within this node. On the shortcut menu, point to Tasks, and then click Export. From there, you have quite a bit of freedom; you can choose to export from any level within the tree structure and whether to include user names and passwords. In the preceding case, if you wanted to export only the Development Servers node and those servers within it, you could do so easily. Using the wizard, you can choose 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. On the shortcut menu, point to Tasks, and then click Import. Browse to and select a previously created .regsrvr file, as demonstrated in Figure 1-9.

  

  Figure 1-9 Importing registered servers.

  From here you can choose in which folder 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 or disapprove an update/overwrite to the existing object structure.

• Central Management Servers. 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 Local Server Groups: you’re able to add servers and create folders with descriptive names to which you can add servers. And, yes, in this way, it is much the same. However, Centralized Management Servers includes some very significant differences.

  First, when using this feature, you must choose a SQL Server Database Engine 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 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 against the CMS itself will result in the query being run against every server hierarchically present in all trees within the CMS. This is a very handy feature, but 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 going to Tools > Options > Query Results > SQL Server > Multiserver Results, and then turning on 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 utilize the structure setup, so there is no need to Export or Import and keep folders and structures synchronized. This is great for team collaboration and efficiency.

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

• Security. A diverse list of object types here, including Logins, Server Roles, Credentials, Cryptographic Providers, and Audits.

• Server Objects. These include Backup Devices, Endpoints, Linked Servers, and Triggers (server-level triggers).

• Replication. Information about Publishers and Subscriptions.

• Always On High Availability. Includes Failover Clustering and Availability Groups.

• Management. Covers a number of 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. Covers jobs, alerts, operators, proxies, and error logs of its own.

• Integration Services Catalogs. SQL Server Integration Services package catalog. Depends on SQL Server version.

By default, SQL Server Management Studio 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 waits through potentially long list load times and expends energy scrolling and watching the screen very closely for the object in question.

Fortunately, SQL Server Management Studio has a filtering feature! You can apply filters to many object categories; for example, user databases, tables, views, stored procedures, table-valued functions, user-defined functions, and even database users.

You can configure filter settings in either the Object Explorer pane or the Objects Explorer Details pane independently. Table 1-2 lists the available filtering options.

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, select Filter, and then click 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 with which you can work on multiple objects at the same time (tables, views, jobs, and so on). Following the standard in the Windows environment, the Shift key allows for the selection of contiguous objects, whereas the Ctrl key allows for noncontiguous selecting. You can specify actions against multiple objects using the GUI or 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.

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

Let’s take a look at some use cases for snippets.

One of the options for SQL Server 2012 and later includes 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

• Right-click and use the option from the context menu that opens

• On the Edit menu, point to IntelliSense, and then click the snippets option you want—Surround With, in the example shown in Figure 1-10.

You can double-click the Function folder to see the available snippets; clicking a snippet surrounds the highlighted code with the snippet template code. You also can insert “placeholder” text that you can replace later.

There are only a few stock Surround With snippets, but many Insert Snippets. You can find these by going to the Edit menu, pointing to IntelliSense, and then clicking Insert Snippet. You use them in the same manner you do for Surround With snippets, except that the code is placed at the current location of the cursor within a block of code. There are also keyboard shortcuts to use this feature. You can use Ctrl+K, Ctrl+S for Surround With snippets, and Ctrl+K, Ctrl+X for Insert Snippets.

Customize toolbars SQL Server Management Studio installs with only the standard toolbar as the default. There are many other toolbars available for use. To access these options, on the toolbar, click Tools, and then click Customize. In the Customize dialog box, there are two tabs. on the first tab, Toolbars, 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, XML, and even a web browser. On the second tab, the Commands tab, you can set up a custom toolbar or edit the drop-down menus and functionality of the existing toolbars.

Tool options You also can customize the appearance of your SQL Server Management Studio interface. Click Tools and then Options to adjust 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 is the Keyboard, Query Shortcuts feature. SQL Server Management Studio comes with several shortcuts already turned on (see Figure 1-11), but you can tailor these to your needs. Many long-time DBAs make heavy use of this feature to reduce the number of keystrokes to carry out common stored procedures.

Using shortcuts in SQL Server Management Studio, you can highlight text and then press the shortcut to run the associated stored procedure, supplying a parameter of the highlighted text. For instance, to see the text of a stored procedure or view, you can use the system procedure sp_helptext. By adding this stored procedure to the shortcut Ctrl+0 (which you can see in Figure 1-11), displaying the data definition language (DDL) of any scripted object within a database requires nothing more than a highlight of a name and simple key combination.

To keep the log information to a reasonable and searchable size, the information is kept in a series of files rather than a single file. It is possible to close one file and start a new, blank file. Unfortunately, the default settings for cycling the log are not very useful.

By default, SQL Server keeps the six most-recent error log files. To configure the number of log files to maintain, in the SQL Server Management Studio Object Explorer, in the Management folder, right-click SQL Server Logs, and then select Configure. In the dialog box that opens, select the check box labeled Limit The Number Of Error Log Files Before They Are Recycled, and then, in the Maximum Number Of Error Log Files box, type a value. The value must be between 6 and 99.

For more about this and other post-installation checklist items, see Chapter 4.

Every time the SQL Server service is restarted, it cycles the log file. This creates a brand new, empty log file and moves the previous log file down one spot in the list. Any log file older in sequence than the maximum specified number of files to keep is deleted.

You also can choose to manually cycle the log file by using the sp_cycle_errorlog command, or you can automate this process by using a SQL Server Agent job to perform this task. When working with SQL Server instances that are quite large and remain online for a long timeframe, this can prevent any single log file from becoming overly large and unwieldy.

No matter which method you use, the resulting action is the same: the current file is closed and a new, blank file is opened. If this causes the file count to exceed the maximum number of files, the oldest file is deleted.

To open the Activity Monitor window, In the Object Explorer pane, right-click the SQL Server instance, and then select Activity Monitor. The window opens in the Object Explorer Details pane.

Activity Monitor consists of six distinct parts:

• Overview. Displays a basic version of what you might already be familiar with viewing in the Task Manager window but with a SQL Server flair, with four distinct graphs

• Processes. Displays all nonsystem processes with open connections to the SQL Server instance

• Resource Waits. Displays the Wait Events of active, open connections

• Data File I/O. Displays a difference between two interval readings of the storage subsystem

• Recent Expensive Queries. Displays information about the most expensive queries from the past 30 seconds

• Active Expensive Queries. Displays a more detailed view of queries that are running at that moment

You can expand each of these six parts to show more information, with the Overview section expanded by default. If you want to sort or filter the results, click the column header of any of the columns in each section.

Each of the graphs display information in real time; however, you can configure the refresh interval by right-clicking any of the graphs. The default for each is 10 seconds; you can adjust this from as short as one second to as long as one hour. The graph settings are adjusted as a unified set, meaning that 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.

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

• User Process. Displays a 1 if this is a user process; 0 if it is a system process

• Login. The login name for 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). Amount of time that 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)

To see the detailed T-SQL query being run (last T-SQL command batch), to trace the process in SQL Profiler, or to Kill the process, right-click any of the rows in the Processes pane.

• 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://technet.microsoft.com/library/cc879320.aspx.

As a general rule, average response times of five milliseconds or less allow for acceptable performance, notwithstanding the occasional outlying peak.

• Query

• Executions/Min

• CPU (ms/sec)

• Physical Reads/sec

• Logical Writes/sec

• Logical Reads/sec

• Average Duration (ms)

• Plan Count

• Database

• 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, which might very well differ if a query is running long.

There is built-in functionality for Job Notifications that makes it possible for a person or group to receive communications about the status of a job, using the Database Mail feature. The setup provides a few straightforward configuration options as to when notifications are sent: Success, Failure, and Completion.

For more information on configuring Database Mail and configuring SQL Agent to use Database Mail, see Chapter 13.

SQL Server Agent’s list of Jobs also includes any SQL Server Reporting Services subscriptions that have been created and scheduled on the server.

• Event alerts. Raised by SQL Server’s Error and Severity mechanism. You can specify this for all databases or for a single database. You can use Error Number or 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 utilize the entire library of SQL Server Performance Monitor counters. Any Counter Object can be chosen, the Sub Counter Object specified, the Counter Instance (if applicable), and a threshold (falls below, becomes equal to, rises above) at which an alert should fire. Figure 1-13 depicts setting up a Performance Condition Alert Definition to notify an administrator if Page Life Expectancy on an instance of SQL has dropped below five minutes.

  

  Figure 1-13 Creating a performance condition alert.

• Windows Management Instrumentation (WMI) alerts. A WMI alert uses the Windows Management Instrumentation Event Provider to allow for more complicated actions upon event detection setting 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 use is to detect any DDL or system configuration changes that occur and to document them for review at a later time. Because the WMI Provider has access to many Server Event Classes and Properties, this feature is quite versatile. It does come with a couple of catches, however:

  • It requires that Service Broker is turned on 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.

You can read more about creating WMI alerts in Chapter 13 as well as at https://docs.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.

SQL Server Integration Services manages these solutions using packages, which you create and modify via a graphical user interface.

From SQL Server Data Tools:

1. Open an Integration Services Project

2. Right-click SSIS Packages

3. Select Upgrade

From SQL Server Management Studio:

1. Connect to Integration Services

2. Expand Stored Packages

3. Right-click MSDB (File System)

4. Select Upgrade

From the Windows command prompt:

1. Navigate to the Microsoft SQL Server140DTSBinn Folder

2. Locate and run the SSISUpgrade.exe file

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-14 presents an example of the Data Profile Viewer displaying the XML created by a Data Profile Tasks, pointed at Microsoft’s WideWorldImporters sample database and analyzing the Sales.Customers table.

Reference is made in several chapters of this book about features that have been deprecated, but the easiest way to stay up to date is to check the Microsoft documentation at https://docs.microsoft.com/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017.

You can also access the list of deprecated features by using the T-SQL query that follows, which provides a list of more than 250 features that are deprecated, along with a count of the number of occurrences on your SQL Server instance. We leave as an exercise for you to identify and resolve specific occurrences.

Click here to view code image

SELECT object_name, counter_name, instance_name, cntr_value, cntr_type
FROM sys.dm_os_performance_counters
WHERE object_name = ‘SQLServer:Deprecated Features’;