代写data留学生、SQL程序设计调试、代写SQL设计

日期：2020-06-30 11:13

Instructions for assignment 6 – Automating SQL Server

EACH DELIVERABLE IS NOTED WITH TWO DOLLAR SIGNS IN FRONT ($$), IN RED AND WORTH 10 POINTS (partial credit will be given as appropriate)

1)USE THIS WORD DOC AND INSERT THE DELIVERABLES WHERE INDICATED – PLEASE NOTE THAT ALL SCREENSHOTS SHOULD BE FULL-SCREEN SCREENSHOTS (no cropped screenshots)

Create a weekly Maintenance routine to run every Sunday morning.

2)Start SQL Server Agent from Configuration Manager

3)Start the “Maintenance Plan Wizard” as shown below:

4)Name the new maintenance plan “MaintenancePlan_AdventureWorks2014_Weekend” as shown below:

5)Create a schedule (click on “Change” at bottom of the dialogue) to have this process run every Sunday at Midnight.

6)($$)DELIVERABLE – Take a screenshot of the schedule after selections and add to the Word doc below:

7)Click Next and check the following boxes:

a.Check Database Integrity

b.Rebuild Index

c.Update Statistics

d.Cleanup History

e.Backup Database (Full)

f.Maintenance Cleanup Task

8)Click Next to get to the “Select Maintenance Task Order” dialogue

9)Click on the “Back Up Database (Full)” task and move it up to the top. – IT IS ALWAYS A BEST PRACTICE TO PERFORM A BACKUP PRIOR TO ANY OTHER OPERATIONS

10)Click Next to move to the “Define Backup Database (Full) Task” dialogue

11)Select the AdventureWorks2014 database

12)Click next to get to the “Define Database Check Integrity Task” dialogue

13)Select the AdventureWorks2014 database and make sure that “Include Indexes” is selected

14)Click Next to move to the “Define Rebuild Index Task” dialogue

15)Select the AdventureWorks2014 database, keep object set to “Tables and Views”, click on “sort results in tempdb” and “keep index online while rendering”.  Also, select “Rebuild indexes offline” for those indexes that do not support online rebuild (mostly XML indexes)

16)Click Next to move to the “Define Update Statistics” dialogue

17)Select the AdventureWorks2014 database, keep object set to “Tables and Views”, keep “All existing statistics” selected, but change the scan type to “sample by 75%”

18)Click Next to move to the “Define History Cleanup Task” dialogue

19)Keep all items selected, but change the “Remove historical data older than” to 8 weeks

20)Click Next to move to the “Define Maintenance Cleanup Task” dialogue

21)Select “Backup files” mark and under “Search folder and delete files based on extension” section fill in the folder where you save your backup files (if you have not changed this, then it should be located at C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Backup) and make sure the extension is “bak” (without the quotes).  Also, change the “Delete files older than the following” to 8 weeks.

22)Click Next twice and then click Finish

23)Find your newly created maintenance plan in Object Explorer and attempt to modify it as shown below:

24)A maintenance plan design window should open up.  Go to the last task in the chain (Maintenance Cleanup Task), right-click on it and select “copy”

25)Then paste (ctrl-v) and a duplicate task should appear.  Connect it to the bottom of the original Maintenance Cleanup Task such that it would execute after it.

26)Double click on the NEW Maintenance Cleanup Task and it should open dialogue box to allow making changes to the task

27)Change “Delete files of the following type” to “Maintenance Plan Text Reports”, change the search folder to C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Log and the extension to “txt” (without the quotes).

28)Click OK and your maintenance plan should be set!

29)Execute the maintenance task (right-click and select execute) and make sure it works (right-click view history).  Troubleshoot and correct any errors.

30)($$)DELIVERABLE x 2 (worth double – 20 points) – Take multiple screenshots of the maintenance plan design to ensure that all tasks are shown and add the screenshots to the word doc below:

31)($$)DELIVERABLE – Open up the “view history”, expand a successful execution to show all steps, take a screenshot and save it to the Word doc below:

Create another Maintenance routine to perform differential and transaction log backups.

32)Start the “Maintenance Plan Wizard” again

33)Name the new maintenance plan “MaintenancePlan_AdventureWorks2014_Backups”

34)Select “Separate schedules for each task”

35)Click Next and check the following boxes:

a.Back Up Database (Differential)

b.Back UP Database (Transaction Log)

36)Click Next twice to go to the “Define Back Up Database (Differential) Task” Dialogue

37)Select the AdventureWorks2014 database and then click on the “Change” button at the bottom to change the schedule

38)Schedule the task to run every night at midnight except on Sunday

39)($$)DELIVERABLE – Take a screenshot of the schedule after selections and add to the Word doc below:

40)Click Next to go to the “Define Back Up Database (Transaction Log) Task” Dialogue

41)Select the AdventureWorks2014 database and then click on the “Change” button at the bottom to change the schedule

42)Schedule the task to run every 4 hours on every day (even Sunday), with the first run to occur at 2AM the next day (this synchronizes the tran log backup to avoid conflict with the daily full & differential backups)

43)($$)DELIVERABLE – Take a screenshot of the schedule after selections and add to the Word doc below:

44)Click Next and Finish.  Your Maintenance plan is all set.

45)($$)DELIVERABLE – Take two screenshots (one for each sub_plan) of the maintenance plan design and add the screenshots to the word doc below:

46)Because this new maintenance plan has multiple sub_plans with different schedules, it needs to be executed directly from the SQL Server Agent dialogue.  Navigate to the “jobs” node under SQL Server Agent as shown below:

47)Select sub_plan_1 (Differential Backup) and click on “Start Job at Step…”

48)Validate that it executes successfully and if not, then troubleshoot accordingly

49)Repeat for sub_plan_2 (Tran Log Backup)

50)($$)DELIVERABLE – Right-click on the sub_plans again and select “View History” and then take screenshots showing the successful execution of both sub plans and add it to the word doc below:

($$)DELIVERABLE x 2 (worth double – 20 points) – Questions on this exercise - Provide answers below

1)For a truly automated production implementation for the entire instance (not just the AdventureWorks2014 database), what other steps would you add besides what was created above?

2)What could you add to ensure that any failures are detected?

3)If this database became very large and your weekend performance impact window (time when you can affect performance) became limited, what adjustments might you need to make?  Try to come up with at least two adjustments.