Microsoft SQL Server 2019 summary

Understanding SQL Server 2019

In Microsoft SQL Server 2019 by Dusan Petkovic, we start by understanding the fundamentals of SQL Server 2019. The book explains the installation process, configuration, and the various editions of SQL Server. It also covers the SQL Server Management Studio (SSMS) and Azure Data Studio (ADS), the two primary tools used to interact with SQL Server.

The book then delves into the core components of SQL Server, including the Database Engine, SQL Server Integration Services (SSIS), SQL Server Analysis Services (SSAS), and SQL Server Reporting Services (SSRS). It explains how these components work together to provide a comprehensive data platform.

Database Design and Transact-SQL

Next, Microsoft SQL Server 2019 focuses on database design and Transact-SQL (T-SQL), the language used to interact with SQL Server. It covers creating and managing databases, tables, and indexes, and then moves on to T-SQL programming, including data manipulation, stored procedures, functions, and transactions.

The book also discusses advanced T-SQL topics such as window functions, common table expressions, and error handling. It provides a solid foundation for writing efficient and maintainable T-SQL code.

Query Optimization and Performance Tuning

Performance is a critical aspect of any database system, and Microsoft SQL Server 2019 addresses this by discussing query optimization and performance tuning. It explains how to read and interpret execution plans, and how to optimize queries using indexes, statistics, and query hints.

Furthermore, the book covers various performance tuning techniques such as configuring server and database settings, monitoring and troubleshooting performance issues, and using tools like SQL Server Profiler and Extended Events.

High Availability, Security, and Disaster Recovery

Ensuring the availability and security of data is paramount for any database system. The book covers high availability features such as Always On Availability Groups, Failover Cluster Instances, and Database Mirroring. It explains how these features provide redundancy and ensure minimal downtime.

In terms of security, Microsoft SQL Server 2019 discusses authentication modes, permissions, encryption, and auditing. It also covers disaster recovery strategies, including backups, restores, and the use of Azure for offsite backups and disaster recovery.

Business Intelligence and Advanced Features

SQL Server is not just a database platform; it also offers powerful business intelligence (BI) capabilities. The book explores SSIS for ETL (Extract, Transform, Load) processes, SSAS for multidimensional and tabular models, and SSRS for creating and managing reports.

Additionally, Microsoft SQL Server 2019 introduces some of the advanced features of SQL Server 2019, such as Big Data Clusters, Intelligent Query Processing, and support for machine learning with R and Python. It explains how these features extend the capabilities of SQL Server to handle modern data challenges.

Conclusion

In conclusion, Microsoft SQL Server 2019 provides a comprehensive guide to understanding and working with SQL Server 2019. From installation and basic T-SQL to advanced performance tuning and BI, the book covers a wide range of topics essential for database professionals and developers. It serves as an invaluable resource for anyone looking to master SQL Server 2019.