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.1 To install Microsoft SQL Server 2019
- 10A.2 To use SQL Server 2019’s graphical utilities
- 10A.3 To create a database in SQL Server 2019
- 10A.4 To submit both SQL DDL and DML via Microsoft SQL Server Management Studio
- 10A.5 To import Microsoft Excel worksheet data into a database table
- 10A.6 To understand the use of SQL/Persistent Stored Modules (SQL/PSM) in SQL Server T-SQL
- 10A.7 To understand the purpose and role of user-defined functions and to create simple user-defined functions
Learning Objectives (2 of 2)
- 10A.8 To understand the purpose and role of stored procedures and to create simple stored procedures
- 10A.9 To understand the purpose and role of triggers and to create simple triggers
- 10A.10 To understand how SQL Server implements indexes, concurrency control, and cursors
- 10A.11 To understand the SQL Server security model
- 10A.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: 524extPB.
- Includes full data warehouse capabilities and business intelligence capabilities.
- Standard Edition — Basic commercial version.
- Up to extmin(4,24) CPUs/cores.
- Up to 128extGB memory.
- Maximum database size: 524extPB.
- 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) CPUs/cores.
- Up to 64extGB memory.
- Maximum database size: 524extPB.
- 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 1extCPU or 4extcores (whichever is lesser). Memory up to 1410extMB.
- Maximum database size: 10extGB.
- 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.
- 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.
- 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).
- Maximum database size (Enterprise): 524extPB
- Standard memory cap: 128extGB
- Web Edition memory cap: 64extGB
- Web Edition CPUs/cores cap: extmin(4,16)
- Express Edition memory cap: 1410extMB
- Express Edition single-CPU/core limit: 1extCPUextor4extcores
- Express Edition maximum database size: 10extGB
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.