INFO1085 (Winter 2022)_Lab 9.1.pdf

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 1 of 14 School of Workforce Development, Continuing Education, and Online Learning Network Technical Support (# 1470) Course Information Name SQL Server Code INFO1085 - Section 1 Faculty Information Professor/Instructor Firas Chahine, Ph.D. Email fchahine@conestogac.on.ca Office Number 113 Office Hours By appointment only Lab 9.1 Working with database monitoring and troubleshooting tools: 1- Preparations for this lab on DB1 server: a. Create database ASSIGNMENT9: CREATE DATABASE ASSIGNMENT9; b. Create tables EmployeePay, OrderDetail and OrderHeader from copying contents from AdventureWorks2016: USE ASSIGNMENT9; SELECT * INTO EmployeePay FROM AdventureWorks2016.HumanResources.EmployeePayHistory SELECT * INTO OrderDetail FROM AdventureWorks2016.Sales.SalesOrderDetail SELECT * INTO OrderHeader FROM AdventureWorks2016.Sales.SalesOrderHeader c. Create database user RogueAdmin with Sysadmin access to your instance DB1\SQLSVR01: CREATE LOGIN RogueAdmin WITH PASSWORD = 'Secret55!';

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 2 of 14 CREATE USER RogueAdmin FOR LOGIN RogueAdmin; ALTER SERVER ROLE SysAdmin ADD MEMBER RogueAdmin; 2- Working with Performance Monitor (perfmon.exe): a. Launch Performance Monitor and observe the following performance counters live to determine the health of your DB1 server: i. Processor Information: % Processor Time - measures how busy the processor is 1. Sustained values over 70% during normal activity should be investigated ii. System: Processor Queue Length - displays a count of the number of processes that are waiting for the processor to finish processing the active request iii. Memory: Available MBytes - measures the amount of physical memory that is available for allocation iv. Memory: Pages/sec - measures the activity transferring data between the physical disk and memory v. Logical Disk: Avg. Disk sec/Read - measures disk throughput 1. A high number indicates the disk if operating efficiently vi. Logical Disk: Avg. Disk sec/Write - measures disk throughput for processes writing data to the disk vii. Logical Disk: Avg. Disk Queue Length - provides a count of the number of requests waiting to read or write data from the physical disk

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 3 of 14 b. Users are complaining that performance of their applications degrade between 4-5pm on Sundays. From your DB1 server, create a Data Collector Set and schedule it and collect logs during that time: Data Collector Sets > User Defined > [Right click] New > Data Collector Set i. Name: DB1 Troubleshooting 4-5pm Sunday ii. Create manually (Advanced) iii. Create data logs: 1. [Check] Performance Counter iv. Add 7 performance counters below: 1. Processor > % Processor Time 2. System > Processor Queue Length 3. Memory > Available MBytes 4. Memory > Pages/sec 5. Logical Disk > Avg. Disk sec/Read 6. Logical Disk > Avg. Disk sec/Write 7. Logical Disk > Avg. Disk Queue Length v. Change Sample internal to 2 seconds vi. Choose location of log file (C:\Users\SQLAdmin\Desktop\DB1 Troubleshooting 4-5pm Sunday) vii. Set schedule to run at 4pm for 1 hour on a Sunday

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 4 of 14 3- Working with SQL Profiler: a. Your manager suspects that there is a rogue DBA and you are tasked with monitoring his activities on the database and collecting evidence of any malicious activities: Launch SQL Server Profiler > File > New Trace… i. > General: 1- Connect to your SQL instance 2- Trace name: RogueDBA Trace 3- [Check] Save to file and select location (C:\Users\SQLAdmin\Desktop\SQL Profiler\ RogueDBA Trace.trc) ii. > Event Selection > [Check] Show all columns iii. > Event Selection > Columns Filter > 1- LoginName = RogueAdmin iv. Run trace and leave it collecting events b. Become a rogue DBA, login to DB1\SQLSVR01 using RogueAdmin and attempt to modify sensitive records: Using PowerShell: SQLCMD.EXE -S DB1\SQLSVR01 -U RogueAdmin -P 'Secret55!' USE ASSIGNMENT9 GO SELECT * FROM EmployeePay WHERE BusinessEntityID = 10 GO UPDATE EmployeePay SET Rate = 65.80 WHERE BusinessEntityID = 10 AND Rate = 42.4808 GO

INFO1085 - SQL Server (Winter 2022 - Section #1) Page 5 of 14 c. Go back to SQL Profiler and observe the rogue DBA and see if you can find any evidence of malicious acts: i. When done, stop the trace. d. Users are complaining that performance of their applications degrade between 4-5pm on Sundays. From Performance Monitor logs, you can see that during that time DB1 server experiences a jump in a number of performance counters. You suspect that this might be related to activities on the SQL database instance. You decide to monitor SQL Server events using SQL Profiler during that period. i. Assume it is Sunday 4pm. Run the Data Collector set you create manually: Performance Monitor > Data Collector Sets > User Defined > [Right-click] DB1 Troubleshooting 4-5pm Sunday > Start ii. Run a trace on SQL Profiler as follows: Launch SQL Server Profiler > File > New Trace… 1- > General: - Connect to your SQL instance

Your preview ends here

Eager to read complete document? Join bartleby learn and gain access to the full version

Access to all documents
Unlimited textbook solutions
24/7 expert homework help