I need some one to do a final project for me.

SQL16A – Final Project: Administering SQL Server 2019 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. 1. Install a new virtual machine with Windows Server 2019. Ensure that this new virtual machine is called DBSERVER and is a domain controller for the domain YOURNAME.COM (where YOURNAME is your actual name). 2. Install a default instance of SQL Server 2019. Ensure that Windows and SQL logins can be used. Give the sa account a password of Secret555. 3. 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. 4. Set up a fake Database Mail profile and account that can be used to send email (the details and name are irrelevant). 5. Configure an operator for yourself that lists your triOS email address and availability times (be creative here). 6. Create a folder in your virtual machine called C:Project. Next, 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:Project1.rpt. SELECT @@SERVERNAME AS ‘Server Name’ SELECT DEFAULT_DOMAIN()[DomainName] SELECT * FROM sys.dm_server_services SELECT * FROM sys.tcp_endpoints SELECT * FROM msdb.dbo.sysmail_account SELECT * FROM msdb.dbo.sysoperators 7. Create a new database called Project. Ensure that the location of database files optimizes database performance (i.e. don’t use the default file location!). SQL16A – Final Project: Administering SQL Server 2019 8. Create a SQL login for the developer on the server called woot with a password of Secret555. 9. 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. 10. 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:Project2.rpt. SELECT db.name AS Name, type_desc AS Type,Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id SELECT * FROM sys.sql_logins WHERE name = ‘woot’ USE Project GO SELECT * FROM sys.sysusers WHERE hasdbaccess = 1 SELECT * FROM sys.database_role_members SELECT * FROM sys.database_principals WHERE name = ‘db_owner’ 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 H101 Benson Max H102 Martinez Sandra H103 Erickson Gregory H104 Lew Judy Title General Manager North Wing Supervisor South Wing Supervisor East Wing Supervisor Dept Name Office # Ext Housekeeping 501 3410 Housekeeping 503 3413 Housekeeping 503 3411 Housekeeping 503 3412 R221 Valentine Brian Host Restaurant 101 7601 R222 Poland Carole Hostess Restaurant 101 7602 R234 R344 Yukish White Gary Suki Asst. Chef Head Chef Restaurant Restaurant 167 167 7603 7604 Date Hired 26-Nov87 5-May-91 17-Aug91 20-Jan92 27-Jan91 10-Apr89 16-Sep89 1-Jul-87 Salary $42,000.00 $32,330.00 $31,800.00 $33,920.00 $25,040.00 $29,000.00 $34,000.00 $45,000.00 SQL16A – Final Project: Administering SQL Server 2019 R455 Jackson Sue S503 Lee Frank Asst. Chef Athletic Director S504 Harui Roger Asst. Athletic Director S505 Bourne Stephanie S606 Schatz Bethany S607 Chen John Asst. Athletic Director Entertainment Director Children’s Director Restaurant 167 7605 Sports & Rec 314 6801 11-Oct88 24-Jun90 Sports & Rec 315 6803 10-Feb91 Sports & Rec 315 6804 Sports & Rec 320 6805 Sports & Rec 320 6806 13-May92 15-Mar92 19-Dec92 $36,450.00 $43,860.00 $38,390.00 $36,800.00 $32,500.00 $32,500.00 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:Project3.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:Project4.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 17. 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:Project5.rpt. SELECT * FROM sys.sql_logins WHERE name = ‘project-access’ OR name = ‘project-update’ USE AdventureWorks2019 GO SELECT ( dp.state_desc + ‘ ‘ + dp.permission_name collate latin1_general_cs_as + ‘ ON ‘ + ‘[‘ + s.name + ‘]’ + ‘.’ + ‘[‘ + o.name + ‘]’ + ‘ TO ‘ + ‘[‘ + dpr.name + ‘]’ ) AS GRANT_STMT FROM sys.database_permissions AS dp INNER JOIN sys.objects AS o ON dp.major_id=o.object_id INNER JOIN sys.schemas AS s ON o.schema_id = s.schema_id INNER JOIN sys.database_principals AS dpr ON dp.grantee_principal_id=dpr.principal_id WHERE 1=1 AND o.name IN (‘Employee’) ORDER BY dpr.name 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:Project6.sql. 19. Manually perform a full backup of your Project database to C:ProjectProject.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:Project7.png. 21. Create a SQL Query file that can quickly be executed to restore the previous night’s backup (without losing any transactions since the previous night’s backup!). Perform a sample restore using this query to make sure it works. Finally, save the query to C:Project8.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:Project9.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:Project10.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:ProjectDisk Usage Summary.pdf C:ProjectQuery Statistics History.pdf C:ProjectServer 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:Project11.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 locations for the database and log files). 29. Perform a bulk export of the data within that table that you created within your project database to a text file called C:ProjectBulkRecords.txt. Following this, bulk import the data into the same table within the HeadOffice database on your ARFA instance. SQL16A – Final Project: Administering SQL Server 2019 30. Open a new query in SSMS that is connected to your ARFA instance. 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:Project12.rpt. SELECT @@SERVERNAME AS ‘Server Name’ SELECT * FROM sys.dm_server_services SELECT * FROM sys.tcp_endpoints SELECT * FROM HeadOffice.HR.Employee SELECT db.name AS Name, type_desc AS Type,Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id 31. Set up database mirroring for your Project database between your default and 2nd instance that uses manual failover. Perform 2 manual failovers. 32. Open a new query in SSMS that is connected to your default instance. 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:Project13a.rpt. Next, repeat this step while connected to your ARFA instance but save the results to C:Project13b.rpt. SELECT * FROM sys.database_mirroring SELECT * FROM sys.database_mirroring_endpoints 33. Remove the mirror to restore your Project database to its original state. 34. Create a new database on your 2nd instance called BranchOffice (use appropriate locations for the database and log files). 35. Open a new query in SSMS that is connected to your ARFA instance. 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:Project14.rpt. SELECT db.name AS Name, type_desc AS Type,Physical_Name AS Location FROM sys.master_files mf INNER JOIN sys.databases db ON db.database_id = mf.database_id 36. Set up replication such that the table that you created in the Project database on your default instance is replicated to the BranchOffice database on your ARFA instance using merge replication. SQL16A – Final Project: Administering SQL Server 2019 37. Add a new record to your BranchOffice database and ensure that it is added to your Project database. When finished, open the Replication Monitor and take a screenshot of your replication status (expand your publication) and save it as C:Project15.png. 38. Use the SQL Server Profiler to capture the existing performance of your Project database in a single trace file called C:ProjectPerformanceTrace.trc (no file rollover). Next, use the DTA to analyze the trace file and produce index recommendations (these will be poor recommendations because this is not a production SQL server). Take a screenshot of the recommendations and save it as C:Project16.png. 39. Add appropriate performance counters to the Performance Console in Windows and create a baseline for later use in HTML (webpage) format. Save the baseline as C:ProjectBaseline.htm. 40. Create alerts to ensure that you are notified by email when the following occurs on your default instance (research these thoroughly – have fun): • Deadlocks are being created • The SQL memory usage is not performing well • There are too many connections to your SQL server • A database is offline • A database has failed • Log shipping has failed • Replication has failed 41. Open a new query in SSMS that is connected to your default instance. 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:Project17.rpt. SELECT * FROM msdb.dbo.sysalerts 42. Compress the C:Project folder into a ZIP file called Project.zip and send it to your instructor (via email or Teams) for marking. Your C:Project folder should have the following contents: 1.rpt 8.sql 14.rpt 2.rpt 9.png 15.png Project.bak Baseline.htm 3.prt 10.sql 16.png 4.png 5.rpt 11.rpt 12.rpt 17.rpt PerformanceTrace.trc BulkRecords.txt Disk Usage Summary.pdf Query Statistics History.pdf Server Activity History.pdf 6.sql 13a.rpt 7.png 13b.rpt

Collepals.com Plagiarism Free Papers

Are you looking for custom essay writing service or even dissertation writing services? Just request for our write my paper service, and we'll match you with the best essay writer in your subject! With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.

Get ZERO PLAGIARISM, HUMAN WRITTEN ESSAYS

Why Hire Collepals.com writers to do your paper?

Quality- We are experienced and have access to ample research materials.

We write plagiarism Free Content

Confidential- We never share or sell your personal information to third parties.

Support-Chat with us today! We are always waiting to answer all your questions.