Course 11: SQL Server 2019 on Windows Server 2019 Series

Plan the lab by cloning a source VM, updating to Windows Server 2019, joining the domain, fixing the domain trust, and creating temporary and final checkpoints with System Center tools.

Clone a Windows Server 2019 box, configure initial settings, set memory and network, assign a static IP, and prepare to join the domain for a secure server 2019 deployment.

Configure a cloned Windows Server 2019 by setting a static IP 192.168.0.20, performing initial configuration tasks, applying updates, and enabling firewall exceptions for connectivity.

Prepare the network by staging a fresh snapshot representing a domain-joined state with a secure URL connected to the domain controller, ensuring time sync before activation and labs.

Outline Windows Server 2019 system requirements for SQL 2019 prerequisites, including 64-bit hardware, 4–6 GB RAM, 60 GB disk, NTFS partition, and .NET Framework 4.6.x, installation considerations.

Access the evaluation center to download SQL Server 2019, then download media for virtual machines, with basic or custom installation and an ISO image around 1.5 gigabytes.

Explore the SQL Server 2019 installation interface on Windows Server 2019, using the main wizard to review hardware requirements, internet access, and media options.

Explore the system configuration checker in Windows Server 2019 to verify eight readiness checks, ensure administrator rights and 64-bit compatibility, and choose between standalone installation or failover clustering.

Choose the evaluation edition for a 180-day trial and create a checkpoint before installation. Open ports 1433 and 1434, and prepare authentication options and service accounts.

Configure the network to access the internet, then open SQL Server ports 1433 and 1434 via Windows Firewall inbound rules and test connectivity.

enforce firewall inbound and outbound rules via group policy, open tcp 1433 and udp 1434 for sql server, and apply the policy to the database servers organizational unit.

Create a local secure admin account (not a domain admin) for emergency access to the secure url database during a mixed mode installation, then disable it after setup.

Create a domain secure admins group and add accounts as members to grant admin access to the secured database, managing permissions with active directory and group policy inheritance.

this lecture compares analysis services instances—multidimensional, tabular, and power pivot—and shows how to install them on one box, with tabular mode as the default for network access.

Learn to use installation center tools and secure server management studio 18 to access database tables, manage instances, and build inventories with the Microsoft assessment and planning toolkit.

Install SQL Server Management Studio 18 to manage SQL Server 2019 on Windows Server 2019, explore the main tools, and prepare for database backup and recovery.

Update sql server management studio to the latest version on the secure sql server 2019 box, download and install the package, and verify the instance name and remote access.

Enable remote connections for SQL Server 2019 on Windows Server 2019 by adjusting server properties, enabling TCP/IP in network protocols, restarting the service, and verifying access via management studio.

Arm SQL with PowerShell modules by installing secure server modules from the PowerShell gallery and enabling automation for database development and administration, including multi-dimensional and tabular queue processing.

Discover how relational databases, normalization, and primary and foreign keys, within a database management system, enable efficient data storage and retrieval.

Learn how to build and save a database diagram in SQL Server Management Studio, exploring table relationships, keys, and normalization for a bike stores database.

Explore how normalization drives online transactional processing (OLTP) by splitting a relational database into related tables through 1NF, 2NF, and 3NF.

Explore the range of database types from OLTP relational systems to data warehouses and OLAP. Examine column-oriented storage, Hadoop with polybase, and hierarchical and graph concepts.

Explore how data becomes information through processing, and how intelligence explains trends; apply data cleaning, data duplication, reporting with Crystal report, Costello report, Excel, and Power Pivot to present insights.

Learn the core sql languages used on secure servers, including ddl, dml, and dcl; practice select syntax, from, where, order by, and basic grant and revoke permissions.

Practice building basic SQL queries with select, from, where, and top, using real table names to filter and display data while emphasizing secure coding.

Explore data modeling techniques and entity-relationship modeling, apply normalization and transaction path analysis to design a course database with students, instructors, and venues.

Explore entity relationship modeling concepts, including primary and foreign keys, relationship types, and normalization, using a practical student and course example.

Explore defining entity relationships in an ER model, including one-to-one, one-to-many, and many-to-many, with notations for mandatory relations and steps from gathering documents to normalization and delivering a database design.

Design a normalized database for a training institute, modeling students, staff, and courses, and manage enrollments, credits, quotas, assignments, and marks through a conceptual model and transaction tables.

Design a database for students and courses, add a transaction assignment table to record assignment IDs, marks, and finish status using foreign keys and normalization.

Explore normalization as a foundational database redesign technique that reduces redundancy, standardizes data, and enables easy extension and efficient online transaction processing, using keys like primary, foreign, candidate, and surrogate.

Explore normalization benefits through dependencies, including functional, transitive, and multivalued types, to ensure uniqueness and data integrity. Learn to design flexible transaction tables using candidate keys like SSN.

explains the five normal forms of database normalization using an employee example, illustrating 1nf and 2nf with tables for laptops, mobiles, doctors, and salary history.

Create a SQL Server database using graphical user interface and command line, build schemas for employees and doctors, and design data tables with primary keys and relationships.

Create doctor and employee tables, define attributes with integer, varchar, and date types, use database diagrams to explore relationships, and practice entering sample data while noting key constraints.

Create a SQL Server database via command line, including schemas money and Dante, and tables with primary keys. Use go for execution and set up basic database diagrams.

Denormalization examines when normalization guidelines trade off performance, reporting, and concurrency in transactional databases and data warehouses. Learn techniques like decomposition, report tables, duplication, and derived columns to optimize queries.

Explore how to implement and enforce referential integrity in a secure server database by linking primary and foreign keys across one-to-many, many-to-many, self-referencing, and optional relationships, with cascading updates.

Explore how indexing accelerates data retrieval and improves query performance and concurrency by using clustered and nonclustered indexes, plus distribution statistics, with practical demonstrations and a final lab.

Explore query performance strategies in SQL Server, including hash joins, loop joins, and merge joins, and learn how normalization, indexing, and selective searches optimize cross-table data retrieval.

Explore how concurrency affects performance when multiple users access resources simultaneously and how concurrency control, isolation levels, locking, and blocking prevent lost updates, dirty reads, non-repeatable reads, and phantom reads.

Explore core SQL Server objects such as tables, views, stored procedures, and triggers. Learn how data types, constraints, identity properties, and keys enforce data integrity within a schema.

Design the main table from requirements, normalize data, and split into related tables with keys. Load data, run cross-table queries, and apply cascade techniques to build a secure administration base.

Gather initial office data to design a database model for the bike store sales department, linking staff, customers, stores, products, and orders through an ERD.

Design a staff table using first, second, and third normal forms, with staff_id as the key, including first name, last name, email, phone, active, store_id, and a self-referencing manager_id.

Design a customer table with customer_id as primary key, capturing first and last name, phone, email, and address fields (street, city, state, zip code), preparing links to staff and orders.

Design the store table with normalization to avoid data repetition. Link stores to stocks and products via a stock table, using primary keys and foreign keys to define relationships.

Explore the final diagram linking tables, keys, and one-to-many relationships among staff, stores, orders, and stock, with product data normalized by brand and category, and queries to build the database.

Deploy your query by first creating the related link tables before the main table, ensuring both tables exist to avoid errors, and implement cascade on update across foreign keys.

Learn to define primary and foreign keys across customer, store, and staff tables, implement composite keys in order item and stock bridge tables, with on update and on delete actions.

Load a file into a secured database, manage database lifecycles with create, drop, and online/offline operations, switch to master and target databases, and set up and verify the schema.

Populate the designed database with data, ensure proper normalization, and use identity insert; then run complex cross-table queries to verify reporting goals.

Explore join queries across staff, stores, stock, and product tables, using aliases and key matches on store_id and product_id to retrieve staff locations, stock levels, and product names.