SQL Server Administration Part1

Understand how a domain centralizes user accounts and resources via an active directory domain controller and virtualization with VMware to deploy Windows 2012 and SQL Server.

Learn to set up a production SQL database environment by downloading VMware Workstation, Windows Server 2012, and SQL Server 2012, creating domain controllers, dedicated drives, and SQL Server service accounts.

This post addendum explains that 12–16 concepts apply to SQL Server 2017 and beyond, with new 2016–17 features noted. It outlines installation steps, tools (SSMS, SSIS, SSRS), and Linux support.

Rafael guides you to collect and download six essential apps—virtual box, Windows server 2016 ISO, SQL Server, SSMS 17, AdventureWorks from GitHub, and Visual Studio 2015—then prepares a VM setup.

Create a new VirtualBox virtual machine and install Windows. Set up SQL Server 2017 and SSMS, allocate host memory and disk, and enable guest additions.

Guide to installing SQL Server 2017 evaluation in a VirtualBox environment, including sharing a host folder, enabling bidirectional clipboard, and completing a default instance installation with selective features.

Install SQL Server and SSMS, then install SSDT and restore the Adventure Works 2017 database on a virtual machine.

Compare SQL Server 2014 and 2017 to show their similar front-end interfaces, and apply the same database administration principles across versions, ensuring script compatibility with Adventure Works.

Create a virtual machine with VMware Workstation, install Windows Server 2012 from an ISO, and prepare a domain controller with a 60 GB disk and server manager.

Promote a Windows Server 2012 VM to a domain controller by installing the Active Directory Domain Services role and DNS to create the sql.com forest.

Learn to create a Windows Server 2012 VM, install SQL Server 2012, and allocate separate drives for data, logs, and tempdb with static IP and service accounts.

Learn how to set up a Windows Server 2012 VM, configure the administrator account, network and domain settings, map the SQL 2012 ISO, and prepare for SQL Server 2012 installation.

Install sql server 2012 using active directory service accounts and separate data and log drives, creating virtual drives for data on i and logs on l.

Install SQL Server with separate service accounts for each engine, configure data and log directories on separate drives, and enable Windows authentication for production performance.

Learn how to install sql server service pack 2 (sp2), test in a dedicated environment, back up production databases, apply the update, and verify the new version.

Explore sql server management studio to connect to the database engine, use object explorer and new query, and review system and user databases, security, replication, and sql server agent basics.

Identify the system databases—master, model, msdb, temp—and the read-only resource database, and understand their roles, template behavior, and backup considerations.

Learn how SQL Server uses the data file and transaction log to store data and changes, and how backups, recovery models, and log management enable point-in-time and high-availability recovery.

Explore the MDF data file and the LDF transaction log through an ATM example, showing how the log records multiple actions, grows with activity, and how backups truncate.

Learn how the transactional log grows automatically and why pre-sizing data and log files, using fixed megabyte growth, avoids fragmentation and performance blocking.

Learn how simple, full, and bulk recovery models govern transaction log backups and log truncation, balancing data safety with log growth in production SQL Server.

Explore how the fn_dblog function reveals transactional log activity via LSNs, and demonstrate how transactional log backups truncate the log while full backups do not.

Explore virtual log files (VLFs) in the SQL transaction log, showing how log size determines VLF count (4, 8, or 16) and why pre-sizing prevents growth-driven performance issues.

Learn how to create a SQL database using GUI or scripting, set initial sizes and auto growth for data and log files, and script production databases for repeatable deployment.

Discover how tempdb, a busy system database rebuilt on restart, hosts temporary objects like tables and procedures, and how pre sizing boosts performance.

Detach and attach a database to move its data and log files to another drive or server, using the GUI or scripts, and set single-user mode to detach.

Explore backup and restore strategies essential for a production SQL database administrator, including full, differential, and transactional log backups, with planning for redundancies and restore timeframes.

Learn how to perform a full database backup in SQL Server using GUI and T-SQL, capturing the entire schema, with recovery model considerations, storage best practices, and backup verification.

Discover how transactional log backups work with full database backups to record changes, manage the recovery model, truncate logs, and enable point in time restoration in SQL Server.

Explore how differential backups record changes since the last full backup and simplify restores alongside full and transactional log backups for large databases.

Learn to restore a SQL Server database using full backups, differential backups, and transactional log backups to recover data after a crash, including GUI restoration and the transactional sequence.

Restore a deleted database via the GUI using a full backup and subsequent transaction log backups in sequence to recover data without gaps.

learn how to restore a SQL Server database using differential backups, starting with a full backup, then the last differential, followed by transactional logs, noting the cumulative nature of differentials.

Master recovery state options during SQL Server restores, using restore with recovery, and restore with no recovery, and tail-log backups with no truncate and copy only to preserve data.

Automate system database backups with a maintenance plan wizard in SQL Server, configure full backups, schedule daily runs, and verify backup integrity.

Design and implement a maintenance plan to back up the admin database with full, differential, and transaction log backups, using sub plans for automated scheduling.

Learn maintenance plan tasks in SQL Server, including backups, DBCC check database integrity, and history cleanup, with guidance on off-hours scheduling, avoid shrink, and set up operator notifications.

avoid shrinking a database; shrinking causes fragmentation and performance issues. if needed, shrink rarely and with sizing, using dbcc shrink database and dbcc shrink file, while keeping auto shrink off.

Explore how SQL Server Agent automates backups and routine tasks with scheduled jobs, alerts, and multi-server administration, enabling centralized control across local and remote servers.

Create a simple SQL Server Agent backup job that runs a T-SQL full backup of the admin database, scheduled daily at 9:31 PM, with enablement and basic success/failure handling.

Learn how to use dbcc check database to validate both physical and logical integrity of databases, manage resource-intensive checks, and implement production-ready job scheduling for reliable backups.

Learn to create a SQL Server Agent job with multiple steps, performing a full database backup, a DBCC check database for integrity, and configure alerts, operators, and database mail.

Activate database mail, configure a mail profile and SMTP account, enable the SQL Server agent mail profile, and create an operator to receive email notifications for job success or failure.

Learn to set up and monitor SQL Server database mail via GUI and T-SQL, create accounts and profiles, enable through sp_configure, and script mail status and logs for multiple servers.

Learn to set up alerts in SQL Server Agent for severity 17–25, map them to an operator, and receive email notifications when the transaction log is full.

Apply a correction in central server management by setting the max cached operations to zero in the MSX encryption channel options to ensure proper registry edits.

Learn to manage many SQL Server instances with central management, using multi server administration to propagate identical jobs from a master to target servers.

Install the free VMware Workstation Player, create a domain controller, and configure virtual machines to set up a Windows-based environment for SQL server administration training.

learn how a Windows domain user is mapped to an SQL login, then to an SQL user, and how to grant granular permissions on databases, tables, and columns.

This demonstration shows how SQL Server administration handles users, logins, and roles using Windows authentication and SQL logins, mapping Windows users to SQL logins and assigning database permissions.

Learn to manage security with T-SQL by creating logins, mapping them to database users, granting select permissions on tables, and safely dropping logins and users.

Explore windows authentication versus mixed mode in sql server, including the risks of the sa account, and learn to enable, disable, or rename it with strong passwords.

Learn to create a SQL authentication login bypassing Active Directory for individuals or applications, enforce password policy. Map the login to a database as a reader and verify permissions.

Explore how SQL roles streamline permission management with fixed and user-defined database and server roles, then create a user-defined database role granting select on two human resources tables.

Learn to manage SQL Server roles with T-SQL by planning role names, selecting securables, granting permissions on tables, and adding logins to database roles through script-driven workflows.

Explore SQL Server roles and create user defined server roles, granting create any database and view any database, while carefully managing logins.

Explore grant, deny, and revoke permissions in SQL Server, including how deny overrides grants, how to assign permissions via table grants and role memberships, and using scripts to audit access.

Apply best SQL Server security practices by using Windows authentication, disabling the SA account, and granting least-privilege service accounts. Document permissions, hide databases, disable unused features, and keep offsite backups.