SQL Server Administration Part 2

Raphael introduces part two by outlining SQL Server performance and maintenance tools, covering installation, indexing, profiling, monitoring, blocking prevention, and topics like snapshots, partitioning, and linked servers.

Understand what an index is and its types. Create indexes with SQL and GUI; know when not to use them; tune with SQL Profiler, Database Tuning Advisor, and monitor fragmentation.

Install the Redgate SQL Data Generator from the SQL Developer Suite, create a test database, and populate it with 50 million rows for indexing.

Compare SQL Server 2014 and 2017 to show that core administration principles apply across versions 12, 14, 16 to 17, while noting Linux support and new features in 17.

Acquire and organize six essential tools for a production SQL database setup: VirtualBox with extension pack, Windows Server 2016 ISO, SQL Server, SSMS, AdventureWorks database, and Visual Studio.

Create a new virtual machine with VirtualBox, install a Windows 64-bit OS, allocate memory and 50 GB disk, and install SQL Server 2017. Enable hypervisor and install guest additions.

Install sql server 2017 in a virtual machine, set up a shared folder, and run setup to install the evaluation edition with defaults. Then install ssms for management.

Install SQL Server and SSMS, then restore the AdventureWorks 2017 database in a virtualized environment, connect to SQL Server, and compare interfaces across versions.

Compare SQL Server 2014 and 2017, showing similar front-end interfaces and how scripts from prior Learn SQL courses apply to 2017, with guidance to update AdventureWorks references to 2017.

Explore how clustered indexes sort table data to speed data retrieval, highlighting why a table has at most one clustered index, and comparing heaps with non-clustered options.

Create non-clustered indexes to speed data retrieval with a separate index structure that does not reorder data; unlike clustered indexes, they can be multiple but consume extra space.

Explore how to create a composite index using two or more columns, like last name and first name, and learn the benefits for faster data retrieval.

Explore when to use indexes in SQL Server, weighing table scans versus clustered and non-clustered indexes, and learn how data size and query patterns drive indexing decisions.

Explore when to create indexes by balancing table size, usage frequency, and where clause patterns, and learn how single, composite, clustered, and non-clustered indexes affect testing, cardinality, and performance.

Learn to use SQL profiler traces to identify indexing needs and analyze queries. Apply the database engine tuning advisor to recommend cluster and non-clustered indexes and validate improvements.

Learn how to manage index fragmentation in SQL Server by choosing reorganize or rebuild based on fragmentation thresholds of 5–30%, and apply online or offline maintenance techniques.

Learn to use SQL profiler to monitor SQL Server events, capture statements and stored procedures, and save traces to files or tables for performance analysis and auditing.

Use SQL profiler to identify the longest running queries in production. Filter by duration, then analyze and save results to a table for tuning.

Use SQL profiler as an audit tool to monitor logins and connections for security, and learn to create mixed-mode logins, run audits, and adjust user permissions.

Use SQL profiler to tune indexes with a tuning template, create user-defined templates, and generate workloads for the database tuning advisor to optimize last name and first name indexes.

Learn best practices for using SQL profiler to monitor a single database. Include descriptive trace names, event selection, column filtering, saving to file, and minimizing data capture.

Replace the SQL profiler with a server side trace to capture data on the server, reducing resource use and network traffic. Automate traces with T-SQL and SQL Agent.

Analyze the workload with the database tuning advisor to receive recommendations on indexes, statistics, and partitioning, then apply or save changes to improve performance.

Learn how SQL Server statistics describe data distribution and guide the query optimizer to choose the best data retrieval path, and how statistics are automatically created, updated, and viewed.

Learn to monitor SQL Server performance with Activity Monitor and database management views to identify bottlenecks, view expensive queries, and terminate problematic processes.

Leverage the Windows Performance Monitor (PerfMon) to capture SQL Server metrics, create baselines, and monitor memory, processor, disk, and network counters for performance health.

Learn to monitor hardware resource utilization with perfmon, identify bottlenecks in CPU, memory, disk, and network, and tune hardware or queries to optimize SQL Server performance.

Identify CPU performance indicators and establish baselines using performance counters, monitor processor time and processor queue length, and tune queries and indexing before hardware upgrades.

Explore memory monitoring in SQL Server using counters such as available megabytes, page set, page faults per second, and buffer cache hit ratio.

Monitor SQL Server disk performance with Performance Monitor, focusing on average seconds per read and average seconds per write to detect bottlenecks across physical and total disks.

Learn to schedule SQL Server performance monitoring with data collector sets, add counters, and generate daily or weekly reports for diagnostics.

Learn to use dynamic management views and functions to monitor SQL Server health, diagnose performance issues, and tune performance, with DMVs categorized by group and database or server scope.

Explore locks, blocking, and deadlocks in production SQL databases and learn how to monitor, avoid, and resolve blocking with practical techniques.

Explore blocking in SQL Server, including exclusive locks, and how begin tran, commit, and rollback control transactions while tracking blocking by spid.

Learn to capture and view deadlocks graphically with SQL Profiler, configure deadlock graph events, trigger a deadlock with two transactions, and identify the victim SPID and lock modes.

Discover extended events in SQL Server, a GUI-based tool that replaces SQL Profiler. Create sessions and packages to monitor long running queries, blocking, and memory with minimal overhead.

Explore building extended events with T-SQL, defining events, actions, predicates, and targets, and compare it to the wizard approach for production SQL database administration.

Create a database snapshot to view a read-only, point-in-time copy of the source database using copy-on-write at the data page level; not a backup, it supports reporting and data restoration.

Learn to migrate data between SQL Server instances with the import and export wizard, choosing full table transfers or partial data via a custom query.

Learn how to use the bulk copy program (BCP) to export and import data between SQL Server and flat files, using command-line switches and batch files.

Explore table partitioning to divide large tables into smaller file groups, map data with a partition function and scheme, and improve performance, archiving, and maintenance.

Partition part 2 demonstrates partitioning a large table by year, creating file groups and data files, and mapping data to partitions with T-SQL.

Demonstrate partitioning a table using a GUI, create and map file groups on drives D and E, and define a partition function by year 2005–2007 for a production table.

Explore contained databases, self contained with their own configurations and metadata, enabling movement without SQL logins, and note partially contained limitations like no replication or change data capture.

Explore contained databases in SQL Server, preventing orphaned logins by migrating data with partial containment, creating users within the database, and securely connecting to the contained database only.

Explore policy based management to enforce enterprise standards on SQL Server, using facets and conditions to find auto shrink and turn it off, and to prevent noncompliant stored procedures.

Learn to create and use linked servers to access data from another SQL Server or other platforms via ODBC, enabling cross-server queries for reporting and production databases.

Learn to access and analyze sql server error logs and sql agent logs using the graphical user interface or sp_read_error_log to troubleshoot issues and filter by log type and keywords.

Learn high availability and disaster recovery for production SQL databases, covering two-node clustering, log shipping, database mirroring, Always On availability groups, replication, and domain setup.