Assignment

Course Code: IT$4$A $($MIS$)$

Course Name: Managing Infrastructure Services

Assignment: Final Project Part $1$: SQL Server $2019$

Textbook: Petkovic, D$.(2020).$ Microsoft SQL Server $2019$: A Beginner's Guide $(7$e$).$ McGraw Hill.

Software: Windows Server $2019,$ SQL Server $2019$

Materials and Resources

Assignment Description

This project reviews the key topics related to database server administration in SQL Server $2019.$ Ensure that the following steps are performed on a clean installation of Windows Server $2019.$

You are required to install a new SQL Server $2019$ database server that will be used by developers within your organization. All server maintenance will be your responsibility and you must communicate$/$coordinate with the company developer in order to create the appropriate structures and objects on the database server. You will connect to the SQL Server $2019$ computer as the Administrator Windows account $($or sa account$),$ but all other connections will be done using a separate account.

Assignment Steps

Activity Steps

$1.$ Install a default instance of SQL Server $2019.1$ mark Ensure that Windows and SQL logins can be used. Give the sa account a password of Secret$555.1$ mark

$2.$ Allow TCP connections on TCP port $1433$ and ensure that all services $($including the SQL Server Agent$)$ are set to automatically start each time the server is booted. $2$ marks

$3.$ Set up a fake Database Mail profile and account that can be used to send email $($the details and name are irrelevant$).1$ mark

$4.$ Configure an operator for yourself that lists your triOS email address and availability times $($be creative here$).1$ mark

$5.$ Create a new database called Project. Ensure that the location of database files optimizes database performance $($do not use the default file location$).1$ mark

$6.$ Create a SQL login for the developer on the server called woot with a password of Secret$555.1$ mark

$7.$ Create a SQL user in the Project database called woot for the developer and ensure that the developer can perform any functions within the Project database. $2$ marks

$8.$ Although the developers will be creating most tables in the database, they have asked you to create normalized tables for them in the Project database called Employee in a new schema called HR$.$ A sample of the type of information that these tables will store is shown below $$ use this information to set up the appropriate fields and constraints $($ensure that you use the best data types$).$ Ensure that there are primary keys and that there is a nonclustered indexes on each table. $6$ marks

Employee ID Last Name First Name Title Dept Name Office # Ext Date Hired Salary

H$101$ Benson Max General Manager Housekeeping $501341026-$Nov$-87$ $$42,000\mathrm{.}00$

H$102$ Martinez Sandra North Wing Supervisor Housekeeping $50334135-$May$-91$ $$32,330\mathrm{.}00$

H$103$ Erickson Gregory South Wing Supervisor Housekeeping $503341117-$Aug$-91$ $$31,800\mathrm{.}00$

H$104$ Lew Judy East Wing Supervisor Housekeeping $503341220-$Jan$-92$ $$33,920\mathrm{.}00$

R$221$ Valentine Brian Host Restaurant $101760127-$Jan$-91$ $$25,040\mathrm{.}00$

R$222$ Poland Carole Hostess Restaurant $101760210-$Apr$-89$ $$29,000\mathrm{.}00$

R$234$ Yukish Gary Asst. Chef Restaurant $167760316-$Sep$-89$ $$34,000\mathrm{.}00$

R$344$ White Suki Head Chef Restaurant $16776041-$Jul$-87$ $$45,000\mathrm{.}00$

R$455$ Jackson Sue Asst. Chef Restaurant $167760511-$Oct$-88$ $$36,450\mathrm{.}00$

S$503$ Lee Frank Athletic Director Sports & Rec $314680124-$Jun$-90$ $$43,860\mathrm{.}00$

S$504$ Harui Roger Asst. Athletic Director Sports & Rec $315680310-$Feb$-91$ $$38,390\mathrm{.}00$

S$505$ Bourne Stephanie Asst. Athletic Director Sports & Rec $315680413-$May$-92$ $$36,800\mathrm{.}00$

S$606$ Schatz Bethany Entertainment Director Sports & Rec $320680515-$Mar$-92$ $$32,500\mathrm{.}00$

S$607$ Chen John Children's Director Sports & Rec $320680619-$Dec$-92$ $$32,500\mathrm{.}00$

$9.$ Create a maintenance plan that rebuilds all indexes on this table and updates all statistics each Sunday at $5$:$00$a$.$m$.$ Any problems should be emailed to you. Execute your maintenance plan to ensure that it works. Finally, right$-$click your maintenance plan and choose Modify to view a flowchart of its configuration. Take a screenshot of that configuration. $2$ marks

$10.$ Create a SQL login and user called project$-$access that has the password Secret$555.$ Ensure that project$-$access only has SELECT permissions to the HR$.$Employee table. $2$ marks

$11.$ Create a SQL login and user called project$-$update that has the password Secret$555.$ Ensure that project$-$update only has permissions to add, update and delete records in the HR$.$Employee table. $2$ marks

$12.$ Create a trigger that prevents the database developer from deleting tables accidentally in the Project database. Take a screenshot. $1$ mark

$13.$ Manually perform a full backup of your Project database to C:$\backslash$Project$\backslash$Project$.$bak. Take a screenshot $1$ mark

$14.$ Ensure that full backups of the Project database are