Answered step by step
Verified Expert Solution
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
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
Create separate nonclustered indexes on the last name and office fields.
Open a new query in SSMS Rightclick 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:Projectrpt
USE Project
GO
SELECT FROM INFORMATIONSCHEMA.COLUMNS where TABLENAME'Employee'
SELECT FROM HREmployee
SELECT FROM sysindexes
WHERE objectid
SELECT objectid FROM sysobjects WHERE name 'Employee'
Create a maintenance plan that rebuilds all indexes on this table and updates all statistics each Sunday at :am Any problems should be emailed to you. Execute your maintenance plan to ensure that it works. Finally, rightclick your maintenance plan and choose Modify to view a flowchart of its configuration. Take a screenshot of that configuration and save it as C:Projectpng
Create a SQL login and user called projectaccess that has the password Secret Ensure that projectaccess only has SELECT permissions to the HREmployee table.
Create a SQL login and user called projectupdate that has the password Secret Ensure that projectupdate only has permissions to add, update and delete records in the HREmployee table.
SQLA Final Project: Administering SQL Server
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:Projectsql
Manually perform a full backup of your Project database to C:ProjectProjectbak.
Ensure that full backups of the Project database are performed every day at :pm using a maintenance plan. Execute your maintenance plan to ensure that it works. Finally, rightclick your maintenance plan and choose Modify to view a flowchart of its configuration. Take a screenshot of that configuration and save it as C:Projectpng
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:Projectsql
Ensure that the Project database is automatically checked for errors and then shrunk every Monday and Thursday at :am using a single maintenance plan. Execute your maintenance plan to ensure that it works. Finally, rightclick your maintenance plan and choose Modify to view a flowchart of its configuration. Take a screenshot of that configuration and save it as C:Projectpng
SQLA Final Project: Administering SQL Server
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:Projectsql
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 eg day generate the three reports Disk Usage, Server Activity, Query Statistics Rightclick each report and choose Export PDF and save it to C:Project using the default filename. You should have reports in this folder:
C:ProjectDisk Usage Summary.pdf
C:ProjectQuery Statistics History.pdf
C:ProjectServer Activity History.pdf
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 Project Although Project would normally be on another SQL server, you will create Project on your own SQL server instance.
Open a new query in SSMS Rightclick 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:Projectrpt
USE distribution
GO
EXEC spreplmonitorhelppublication
Create a nd 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.
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
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started