Chapter 10A Notes: Managing Databases with Microsoft SQL Server 2019

Overview

  • This set of notes covers material from Chapter 10A: Managing Databases with Microsoft SQL Server 2019 (Sixteenth Edition, Pearson). It focuses on installation, graphical utilities, database creation, DDL/DML submission via SQL Server Management Studio (SSMS), data import from Excel, SQL/PSM concepts, user-defined functions, stored procedures, triggers, indexing, concurrency, cursors, security model, and backup/recovery facilities. It also introduces SQL Server 2019 editions, prerequisites, installation steps, and related toolchains (SSMS, Azure Data Studio, SSDT, and Reporting Services).
  • Emphasis on practical steps, tool usage, and the relationships between server features and real-world database management tasks.

Learning Objectives (1 of 2)

  • 10A.110A.1 To install Microsoft SQL Server 2019
  • 10A.210A.2 To use SQL Server 2019’s graphical utilities
  • 10A.310A.3 To create a database in SQL Server 2019
  • 10A.410A.4 To submit both SQL DDL and DML via Microsoft SQL Server Management Studio
  • 10A.510A.5 To import Microsoft Excel worksheet data into a database table
  • 10A.610A.6 To understand the use of SQL/Persistent Stored Modules (SQL/PSM) in SQL Server T-SQL
  • 10A.710A.7 To understand the purpose and role of user-defined functions and to create simple user-defined functions

Learning Objectives (2 of 2)

  • 10A.810A.8 To understand the purpose and role of stored procedures and to create simple stored procedures
  • 10A.910A.9 To understand the purpose and role of triggers and to create simple triggers
  • 10A.1010A.10 To understand how SQL Server implements indexes, concurrency control, and cursors
  • 10A.1110A.11 To understand the SQL Server security model
  • 10A.1210A.12 To understand the fundamental features of SQL Server backup and recovery facilities

Scope

  • To install Microsoft SQL Server 2019
  • To use SQL Server 2019’s graphical utilities
  • To create a database in SQL Server 2019

SQL Server 2019 Editions: Introduction (1 of 3)

  • Enterprise Edition — A powerful, feature-laden commercial version.
    • Handles up to the maximum number of CPUs or CPU cores allowed by the operating system.
    • Supports the maximum memory allowed by the operating system.
    • Maximum database size: 524extPB524 ext{ PB}.
    • Includes full data warehouse capabilities and business intelligence capabilities.
  • Standard Edition — Basic commercial version.
    • Up to extmin(4,24)ext{min}(4, 24) CPUs/cores.
    • Up to 128extGB128 ext{ GB} memory.
    • Maximum database size: 524extPB524 ext{ PB}.
    • Very limited data warehouse capabilities.

SQL Server 2019 Editions: Introduction (2 of 3)

  • Web Edition — Low-cost option for Web site database support.
    • Up to extmin(4,16)ext{min}(4, 16) CPUs/cores.
    • Up to 64extGB64 ext{ GB} memory.
    • Maximum database size: 524extPB524 ext{ PB}.
    • Very limited data warehouse capabilities.
    • Must run on hosted cloud Microsoft Windows Server OS; will not run on Windows 10.
  • Developer Edition — Single-user version with complete Enterprise feature set.
    • Intended for development work, not production use.
    • Free download; preferred for use with this book.
    • Contains full Enterprise feature set.

SQL Server 2019 Editions: Introduction (3 of 3)

  • Express Edition — Free, feature-limited version.
    • Up to 1extCPU1 ext{ CPU} or 4extcores4 ext{ cores} (whichever is lesser). Memory up to 1410extMB1410 ext{ MB}.
    • Maximum database size: 10extGB10 ext{ GB}.
    • Great learning tool and can be used in production.
    • Express Edition with Advanced Services adds Polybase (access to external data sources, including some non-relational DBMS) and Full-Text Search.
    • LocalDB variant exists: smaller version with a simpler installation, suitable for workstation development.
  • All Express variants share the same installer program.

Figure References: Preparations and Installation (Overview)

  • Figures 10A-2(a) to 10A-2(i) illustrate Preparations for Installing Microsoft SQL Server 2019, including:
    • (a) The Run Dialog Box
    • (b) The Control Panel
    • (c) The Windows Features Dialog Box
    • (d) The Windows Update Download Files Dialog Box
    • (e) The Visual Studio Redistributables Web Page
    • (f) The Finished Downloading Dialog Box
    • (g) The VC_redist.x64.exe Shortcut Menu
    • (h) The Visual C++ Redistributable Installation Dialog Box
    • (i) The Setup Successful Page
  • Figures 10A-3(a) to 10A-3(d) show Downloading the Microsoft SQL Server 2019 Installation Files:
    • (a) The SQL Server 2019 Web Page
    • (b) The SQL Server 2019 Downloads Web Page
    • (c) The Select an Installation Type Page
    • (d) The Select SQL Server Installer Download Page
  • Figures 10A-4(a) to 10A-4(p) depict Installing Microsoft SQL Server 2019, including:
    • (a) The SQL Server 2019 x64 ENU Dev ISO shortcut menu
    • (b) The Mounted SQL Server 2019 ISO File
    • (c) The Setup Planning Page
    • (d) The Setup Installation Center Dialog (Installation Page)
    • (e) The Setup Product Key Page
    • (f) The Setup License Terms Page
    • (g) The Setup Microsoft Update Page
    • (h) The Setup Install Rules Page
    • (i) The Setup Feature Selection Page
    • (j) The SQL Server 2019 Features Selected
    • (k) The Setup Instance Configuration Page
    • (l) The Setup PolyBase Configuration Page
    • (m) The Setup Java Install Location Page
    • (n) The Setup Server Configuration Page
    • (o) The Setup Database Engine Configuration Page
    • (p) The Setup Analysis Services Configuration Page
    • (q) The Setup Integration Services Scale Out Configuration – Master Node
    • (r) The Setup Integration Services Scale Out Configuration – Worker Node
  • Figures 10A-4(s) to 10A-4(t) cover: Consent to Install Microsoft R Open and Consent to Install Python, then Ready steps and progress.
  • Figures 10A-4(v) to 10A-4(w) show the Setup Complete Page and subsequent installation progress; Figure 10A-4(w) summarizes completion.
  • Figures 10A-3(d) and 10A-4 pages collectively represent the end-to-end installation flow for SQL Server 2019.

Installing Microsoft SQL Server 2019 Reporting Services

  • Reporting Services provides reporting capabilities (BI tooling) to sort, filter, group, and perform basic calculations on database data.
  • SSRS must be downloaded and installed as a separate package from the core SQL Server product.
  • Figures 10A-5(a) to 10A-5(f) show the SSRS installation path, including:
    • The Installation Center dialog
    • The Install SSRS Documentation Page
    • The SSRS Download Web Page
    • The SSRS Download and Setup progress (including the .exe and shortcut)
  • Figures 10A-6(a) to 10A-6(m) cover the configuration of the SSRS Report Server, including:
    • The Report Server Configuration Connection dialog
    • The Report Server Status Page
    • The Service Account Page
    • The Web Service URL Page (and applied settings)
    • The Initial Report Server Database Page and subsequent configuration steps (Web Portal URL, Completed Web Portal URL, etc.)

Microsoft SQL Server 2019 Utilities

  • SQL Server 2019 provides both command-line utilities and graphical user interface (GUI) utilities.
  • Windows PowerShell offers a powerful command-line interface with cmdlets.
  • MSSQL Common Language Runtime (CLR) is a technology built into SQL Server to provide Microsoft .NET CLR support.

SQL Server Management Studio (SSMS) and Azure Data Studio

  • SSMS provides data modeling, database development, and SQL Server administration tools.
  • Figures 10A-7(a) to 10A-7(f) illustrate the SSMS installation path and related pages, including the SSMS download and installation experience.
  • The SSMS installation page also references the option to install with Azure Data Studio, indicating integration between SSMS and Azure Data Studio installation paths.
  • Post-install, connecting to SQL Server 2019 via GUI tools is demonstrated in Figures 10A-8(a) and 10A-8(d).
  • Azure Data Studio provides a modern, cross-platform GUI alternative to SSMS, with a focus on data exploration and notebooks.

Microsoft SQL Server Data Tools (SSDT) and Visual Studio 2019

  • SSDT components are actually part of Microsoft Visual Studio 2019.
  • SSDT comprises three downloadable extensions for Visual Studio 2019:
    • Microsoft Analysis Services Projects
    • Microsoft Reporting Services Projects
    • Microsoft SQL Server Integration Services Projects
  • Figures 10A-10(a) to 10A-10(h) cover installing Visual Studio 2019 Community Edition and the SSDT extensions:
    • Installing Visual Studio Community 2019
    • Downloading the SSDT extensions via the Visual Studio Installer
    • The Web & Cloud Workload selection and other toolsets during installation
    • The Visual Studio environment and getting started dialogs
  • Figures 10A-11(a) to 10A-11(h) depict the Visual Studio Data Tools installation flow, including:
    • Extensions management and the VSIX Installer
    • The Ready to Install the Extension message
    • The VSIX Installer scheduled tasks page and completion steps

Visual Studio Community 2019 and SSDT Workflow Summary

  • Install Visual Studio Community 2019 with the Data Tools workloads to enable SQL Server Data Tools (SSDT) project types.
  • Use the Extensions Manager to add the three SSDT projects (Analysis Services, Reporting Services, Integration Services).
  • After installation, open Visual Studio to create and manage SQL Server projects (SSDT) alongside the core SQL Server installation.

Practical Significance and Connections

  • Editions determine feature availability, scale, and intended use cases (development, production, web hosting, etc.).
  • Prerequisites ensure a compatible runtime environment for the SQL Server Engine and related services; skipping these can lead to installation failures.
  • Polybase and Full-Text Search in Express Advanced Services extend external data access and text search capabilities, bridging relational and non-relational data sources.
  • SSRS provides a native reporting platform integrated with SQL Server to produce, publish, and manage reports, a key BI component.
  • SSMS remains the traditional primary GUI tool for database administration, while Azure Data Studio offers a newer, cross-platform experience with notebooks for analytics workflows.
  • SSDT integrates database development into Visual Studio, enabling database projects, deployment, and BI project types within a familiar IDE.
  • The installation flow emphasizes planning (type of installation, instance configuration, and feature selection) and post-install configuration (service accounts, URLs, and database engine settings).

Formulas, Numbers, and Key Specs (LaTeX)

  • Maximum database size (Enterprise): 524extPB524 ext{ PB}
  • Standard memory cap: 128extGB128 ext{ GB}
  • Web Edition memory cap: 64extGB64 ext{ GB}
  • Web Edition CPUs/cores cap: extmin(4,16)ext{min}(4,16)
  • Express Edition memory cap: 1410extMB1410 ext{ MB}
  • Express Edition single-CPU/core limit: 1extCPUextor4extcores1 ext{ CPU} ext{ or } 4 ext{ cores}
  • Express Edition maximum database size: 10extGB10 ext{ GB}

Quick Reference: Key Terminology and Concepts

  • SQL Server 2019 Editions: Enterprise, Standard, Web, Developer, Express (and Express with Advanced Services, LocalDB).
  • SQL/PSM: SQL Persistent Stored Modules, a scripting/embedding approach for procedural logic within SQL Server.
  • UDFs: User-Defined Functions; simple UDF creation and usage.
  • Stored Procedures: Precompiled SQL blocks for modular, reusable logic; include simple procedures.
  • Triggers: Database-side procedural logic that fires on data modification events.
  • Indexes: Structures to speed data retrieval; performance tuning and query optimization.
  • Concurrency control: Mechanisms to manage simultaneous data access (locking, transactions).
  • Cursors: Database object for row-by-row processing.
  • Security Model: Authentication, authorization, roles, permissions related to SQL Server.
  • Backup and Recovery: Core features to protect and restore data.
  • Tools: SSMS, Azure Data Studio, PowerShell, CLR integration, SSDT, Visual Studio, SSRS, SSIS, SSAS.

Notes on Figures and Figures-Only Content

  • The transcript heavily cites step-by-step installation screens and configuration pages (Figures 10A-2 through 10A-11). The practical takeaway is to mirror the sequence when performing a real install:
    • Preparations (Run dialog, Control Panel, Windows Features, Windows Update, Visual C++ Redistributables).
    • Download pages for installation files and the installation type selection.
    • Installation center workflows (Product Key, License Terms, Updates, Rules, Features, Instance, PolyBase, Java, Server Configuration, Database Engine, Analysis Services, Integration Services).
    • Additional steps for Reporting Services setup and configuration (web URL, service account, test connection).
    • Post-installation tooling setup (SSMS, Azure Data Studio, SSDT, Visual Studio, VSIX extensions).
  • These steps emphasize the tight coupling between server installation, BI tooling, development environments, and data deployment workflows.

Study Tips for Exam Preparation

  • Memorize the primary editions and their limits (CPU cores, memory, max DB size).
  • Be able to explain where each tool fits in the SQL Server ecosystem (SSMS for admin, SSRS for reporting, SSDT for development, Azure Data Studio as a modern IDE).
  • Understand the installation prerequisites and why each prerequisite is needed (e.g., .NET Framework, VC++ redistributables).
  • Know the sequence of installation steps at a high level and recognize the key configuration pages (Instance Configuration, Feature Selection, PolyBase, Java, Service Accounts, Web URLs).
  • Practice connecting to SQL Server 2019 using both SSMS and Azure Data Studio and explore a basic database creation and DDL/DML submission workflow.