SQL Server Administration (Mock Internship Program Part1)

Apply core SQL Server administration principles across versions, with separate 2014, 2016, and 2017 instances. Perform common tasks like selecting data and creating or dropping objects, while embracing 2016 features.

Join this mock internship program to gain hands-on SQL Server administration experience through real-world projects in production environments, while completing seven mandatory courses in order.

Download and install VirtualBox, Windows Server 2012, and SQL Server 2012 to build a virtual environment, create a GUI VM, and configure shared folders and data drives.

Set up a virtual box environment, enable shared folders and emulate hard drives, then install Eskudo Server 2012 with designated data and log file directories and firewall adjustments.

Attach the Adventure Works 2012 database to a new dev sql server instance, create a database via gui, and import and export a Movies file using a shared folder.

Attach the Adventure Works 2012 database to the Dev One instance, create the Greatest Movies database, and import the movies and actors text file via the GUI and import wizard.

Create a backup plan with a maintenance plan for system and user databases, size guides recovery mode and choices like transactional logs, differential logs, and simple or bulk-logged backups.

Learn to build maintenance plans that back up system and user databases, determine size, and set simple or full recovery models.

Create an ssrs product report filtered to accessories, listing name, price, size, and color. Format with a dollar sign, centered headers, and frozen panes, and enable Bob-only access.

Create an ssrs report for products table filtered by accessories, with currency formatting and headers. Deploy to the web portal and grant read access to Bob Enescu via Eskil login.

Deploy the SSRS report by setting the target reports folder and server, rebuilding, and deploying from the management studio, then verify via the report manager.

Develop a backup plan for three large databases, choosing full, differential, or transaction log backups, and stagger jobs to meet restore windows while ensuring data integrity with dbcc checkdb.

Develop a backup plan for databases (10 gb, 100 gb, 300 gb) using full, differential, or log backups. Run dbcc checkdb first and schedule off-hour maintenance to meet restoration windows.

Learn to design a backup plan for large databases using full, differential, and transaction log backups. Schedule these backups to meet restore time targets and data-loss tolerances.

Identify and investigate security breaches in a production database using the Ezekial profiler, by setting up a breach scenario, monitoring activity, and analyzing results to locate the cause.

Detect a security breach on a sql server with the Eskil profiler, back up the table, configure a recurring delete job, and identify the responsible administrator.

Create secure logins, switch authentication mode to mixed, map the logins Tom and Matt to Adventurer's 2012, and grant only select on the human resources table to restrict access.

Configure mixed mode authentication and script, creating SQL logins such as Tom and Matt. Grant them restricted select access to Adventurer's 2012 Human Resources table.

Learn to migrate sql server data between instances by backup and restore, and use the import-export wizard to migrate specific tables to a new dev database, and configure Ezekiel login.

Learn to restore a corrupt database using full and transactional log backups, set up the environment, validate 40 records, and apply proper backup directory paths.

Learn to restore databases by combining a full backup with transaction log backups to capture all changes; understand consequences when the last log backup is missing.

Set up the school database mail system and an operator to alert via email when performance issues occur, then test using your SMTP email account.

Set up and test a database mail system in the school environment, configure a mail profile and SMTP account, restart services, and create an operator to receive alert emails.

Create a database using t-sql with default settings. Build a names table (first name, last name, age), insert five rows, copy the table, and export csv report via the wizard.

Create a developers database, define a names table, insert data, back up with a copy, generate a report, and export to CSV using T-SQL and the export wizard.

Explore a native backup and restore plan for a 500-gigabyte database, covering full, differential, and log backups, with a 15-minute recovery point and a 1.5-hour restore window.

Design and execute a backup and restore plan for a very large database using weekly full backups, differentials every six hours, and 15-minute transaction log backups.

Write an inner join to list employees by department and a count by job title, including first name, last name, gender, and title, plus an SRS report.

Learn to write inner join queries between employee and person tables, using SQL or a query designer, to list first name, last name, gender, job title, and counts by title.

Identify the longest running query using SQL profiler, focusing on duration, application name, login name, and database, then propose a concise resolution to reduce resource usage.

Identify long running queries causing memory and CPU spikes using SQL profiler, filter for durations over 15 seconds, capture results, and propose query-level resolutions before hardware changes.

Explore monitoring sql server performance using Windows performance monitor, identify key counters for CPU, memory, and disk, set spike thresholds, and evaluate hardware upgrades or query optimizations to resolve issues.

Learn to use Windows performance tools to monitor SQL Server performance, identify bottlenecks with CPU, memory, disk, and network counters, and apply indexing and query optimization.

Enforce data quality with T-SQL constraints by creating a database, a drivers table, inserting valid and invalid ages, removing garbage data, and adding a check constraint for ages.

Create a prod database and drivers table with age, cleanse garbage data by deleting underage rows, and apply a check constraint to enforce ages 18 or above.

Create a new virtual machine for a developer's testing environment, install Windows 2012 and 2014 servers, set up a blank five-gig database with a one-gig log, and grant restricted access.