Answered step by step
Verified Expert Solution
Link Copied!

Question

1 Approved Answer

1 1 . Although the developer will be creating most tables in the database, he has asked you to create a table for him in

11.Although the developer will be creating most tables in the database, he has asked you to create a table for him in the Project database called Employee in a new schema called HR. A sample of the type of information that this table 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 is a primary key (you can add a field for one) and that there is a clustered index on the primary key field.
Employee ID Last Name First Name Title Dept Name Office # Ext Date Hired Salary
12.
Create 2 separate non-clustered indexes on the last name and office fields.
13.
Open a new query in SSMS. Right-click the white background of the query window and click Results To > Results to File. Next, execute the following queries in this window. When prompted to save the results to a file, choose C:\Project\3.rpt.
USE Project
GO
SELECT * FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='Employee'
SELECT * FROM HR.Employee
SELECT * FROM sys.indexes
WHERE object_id =
(SELECT object_id FROM sys.objects WHERE name = 'Employee')
14.
Create a maintenance plan that rebuilds all indexes on this table and updates all statistics each Sunday at 5:00am. 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 and save it as C:\Project\4.png.
15.
Create a SQL login and user called project-access that has the password Secret555. Ensure that project-access only has SELECT permissions to the HR.Employee table.
16.
Create a SQL login and user called project-update that has the password Secret555. Ensure that project-update only has permissions to add, update and delete records in the HR.Employee table.
SQL16A Final Project: Administering SQL Server 2019
18.Create a trigger that prevents the database developer from deleting tables accidentally in the Project database. Save the query you used to create this trigger to C:\Project\6.sql.
19.
Manually perform a full backup of your Project database to C:\Project\Project.bak.
20.
Ensure that full backups of the Project database are performed every day at 10:00pm using a maintenance plan. 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 and save it as C:\Project\7.png.
21.
Create a SQL Query file that can quickly be executed to restore the previous nights backup (without losing any transactions since the previous nights backup!). Perform a sample restore using this query to make sure it works. Finally, save the query to C:\Project\8.sql.
22.
Ensure that the Project database is automatically checked for errors and then shrunk every Monday and Thursday at 2:00am using a single maintenance plan. 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 and save it as C:\Project\9.png.
SQL16A Final Project: Administering SQL Server 2019
23.
Check your database for errors. Perform a sample repair in single user mode and return to multiuser mode when finished. Save the commands you performed in a file called C:\Project\10.sql.
24.
Configure the Performance Data Warehouse to monitor the key areas of your SQL server (this data should be stored in a database called PDW). After a period of time (e.g.1 day), generate the three reports (Disk Usage, Server Activity, Query Statistics). Right-click each report and choose Export > PDF and save it to C:\Project\ using the default filename. You should have 3 reports in this folder:
C:\Project\Disk Usage Summary.pdf
C:\Project\Query Statistics History.pdf
C:\Project\Server Activity History.pdf
25.
Enable your system as a publisher and distributor (use the default database name of distribution for your distributor database). Next, configure snapshot replication to back up the Employee table in the Project database to a new database you create called Project2. Although Project2 would normally be on another SQL server, you will create Project2 on your own SQL server instance.
26.
Open a new query in SSMS. Right-click the white background of the query window and click Results To > Results to File. Next, execute the following queries in this window. When prompted to save the results to a file, choose C:\Project\11.rpt.
USE distribution
GO
EXEC sp_replmonitorhelppublication
27.
Create a 2nd (named) instance of SQL on your computer called ARFA that allows network access. Ensure that the SQL Server Agent is started and set to start automatically at boot time.
28.
Create a new database on your ARFA instance called HeadOffice (use appropriate lo

Step by Step Solution

There are 3 Steps involved in it

Step: 1

blur-text-image

Get Instant Access to Expert-Tailored Solutions

See step-by-step solutions with expert insights and AI powered tools for academic success

Step: 2

blur-text-image_2

Step: 3

blur-text-image_3

Ace Your Homework with AI

Get the answers you need in no time with our AI-driven, step-by-step assistance

Get Started

Recommended Textbook for

More Books

Students also viewed these Databases questions