instructions SQL Server Database DR Planning (15 points) Adventure Works has decided to purchase a second SQL Server and wants to do off-site DR. Assume for this lab that although your two SQL Servers...

n


instructions SQL Server Database DR Planning (15 points) Adventure Works has decided to purchase a second SQL Server and wants to do off-site DR. Assume for this lab that although your two SQL Servers will be on the same network - that the first server is at your Company's HQ, and the second server is at a datacenter. As in Lab 1 - Questions with an asterisk should be at least a paragraph. Questions lacking a asterisk should still be expressed in at least 1 complete sentence. 1. Installation Task (3 Points) Create a new server called U1234567-2 (UNID-2) and make sure both U1234567-1 and U1234567-2 are joined to the Active Directory domain. Install a second SQL Server 2016 on U1234567-2 but do NOT install the AdventureWorks2016 database. From the desktop of U1234567-1 connect to both SQL Servers at once in SSMS (provide a screenshot of object explorer showing both instances) Make sure to use the SQLSERVER Service account you created previously, and also make sure to add DBA security group as the administrator of the database. Install SSMS (same version as on Server 1) on the Second Server. Tip - in Lab 1 you should have created an Appliance which will make this process much easier. 2. Initial Disaster Planning (7 Points) Your company has decided that in order to maintain business that certain critical business infrastructure should be available in case of a failure of either the hardware or facilities at your main headquarters (this is where your data center is located). Your company has partnered with Super Awesome Datacenters to have a rack of equipment co-located at a different data center in the next town over. 1. (1 Point) What is the name of the evaluation that would you have conducted to determine the business requirements of the disaster recovery plan that you will implement at the co-location. As a result of the evaluation in Question 2.1 - your company has decided that the AdventureWorks2016 database can be down for no more than 24 hours and that in the case of a disaster that you can lose no more than 4 hours worth of data. 2.(1 Point) What is the RTO and RPO for this database? 3. (3 Points) Using ONLY backups (not using Mirroring or AlwaysOn) - build Maintenance Plans for your database that can best handle the RTO and RPO of the database. Since you already built a SQL Server on the second server - you should assume that it is possible to rebuild the database from backups on the second server within 24 hours. Your company has defined a maintenance window at midnight which means that database intensive backups can be taken at midnight. Lower intensity backups should be taken more frequently (frequently enough to hit the agreed to targets). Consider - your database should be in full recovery mode and you should use this to your advantage in taking more frequent backups. Also, make sure that you are backing up from server 1 to server 2 since you will need to rebuild on server 2. This will mean you need to create a share on server 2 that is accessible ONLY by the DBA and the Service Account. A final tip - your company transacts large numbers of transactions in a day - a backup that would be accumulative of those transactions can get quite large and cause system issues with the connection between your main site and the Data Center. Consider a backup type that will replicate changes without being very large and also consider replicating data as few times as possible. 4. (1 Point) In Week 5 we discussed various types of "Clustering" or "Stand-By" servers (in other words, Cold, Luke Warm, Warm, Hot, etc.). Considering the solution you have created in Question 2.3, what kind of stand-by server is server 2? Explain why you feel that this is correct? * 5. (1 Point) Your company has a public facing website where customers can order directly using the AdventureWorks2016 database. Your company has a single Internet Service Provider with a single Firewall. Access to the database from the outside is facilitated by a single IIS server running in your company's DMZ. Assuming that the database backups you have configured are good enough for your company's uptime requirements, what other suggestions might you make in order to increase uptime to the public website? * 3. Disaster Strikes (5 Points) 1. (1/2 Point) Update the following records in Production.Product - 1.a Update ProductID 2 so that the name is no longer "Bearing Ball" to be "Ball Bearing" 1.b Update ProductID 3 so that the list price is 30 Run your nightly backup maintenance plan manually and confirm that the file is created on the second server. Take a screenshot of the file on Server 2 as well as the updated values in the table on Server 1. 2. (1/2 Point) Update the following records in Production.Product 2.a Update ProductID 316 so that the DaysToManufacture is 20 2b Update Product ID 400 so that the StandardCost is 100 Run your lower intensity maintenance plan manually and that the file is created on the second server. Take a screenshot of the file on Server 2 as well as the updated values in the table on Server 1. 3. (2 point) Assume that the primary server has been destroyed. Restore the database to the latest version that you can using the backups you have. Take screenshots as you restore the database using the backups you have. 4. (1 Point) Confirm that the database has been restored to the proper time by querying the database and confirming that the updates that were done are in there. 5. (1 Point) Your company's Report Writer has contacted you indicating that they are unable to access the database. They use a SQL Server login on the database. Assuming that you are only doing the backups mentioned in this lab - what would need to be done to grant access to the user again? * lab environment Installing Lab Environment As noted in the installation video and lecture - the requirements for the lab environment is either a Microsoft Windows Computer running some recent version of Windows (7 or above) with virtualization capabilities or a recent version of Mac OS X with the same capabilities. Supported Lab Configuration Two (and eventually three) Virtual Box virtual machines running on either a Mac OS X or Windows host. Servers should (at a minimum) be configured as follows: 1. Domain Controller with 512 MB of RAM and usually 1 vCPU - running Windows 2016 64 Bit Standard (You will need to install this initially with 1GB of RAM - you can reduce it to 512MB after installation to save memory on your machine if needed) 2. Database Server with 1GB of RAM and usually 1 vCPU running Windows 2016 64 Bit Standard and Microsoft SQL Server 2016 Enterprise 3. Replica Database Server with 1GB of RAM and usually 1vCPU - follow directions to create a "template" or "appliance" ***If you are running the lab on Azure - the lab will be configured as follows***: 1. Domain Controller with 1GB of RAM 1 vCPU - running Windows 2019 64 Bit Core Datacenter (Standard_B1s) 2. Database Server with 4GB of RAM and usually 1 vCPU running Windows 2019 64 Bit Datacenter and Microsoft SQL Server 2016 Enterprise (Standard_B2s) 3. Replica Database Server will be the same as the primary database server. Please note, the database servers will run incredibly slow with 1GB of RAM - but on some machines with only 4GB of RAM this may be necessary. It may be possible to run this environment on a machine with 3GB of RAM - but it will not be possible to run on anything less. If you have more than 4GB of RAM on your machine, you may consider allocating more RAM to the Database platforms - there is no reason to allocate more than 512MB of RAM to the Domain Controller after install. Depending on your operating system - please view the following notes about how to make sure your computer will be able to run the lab environment. These links also provide the virtualization software you will use for this course (VirtualBox). Microsoft Windows Notes MacOS Notes All Users - Getting the Software The lab environment will consist of three Windows 2016 Guests two of which will be running SQL Server 2016 Enterprise. These were previously available from DreamSpark - however, the software is no longer available on DreamSpark and to simplify download - I have uploaded the software either to Box (the two ISOs for Windows 2016 and SQL Server 2016) or to Canvas directly (the AdventureWorks database and the correct version of SSMS that you need). You will need to have access to Box to download these files - but you should already have this access. If not, then you should be able to sign up for University of Utah Box (this is different than a personal account you may already have) to get the software. Download the Operating System and Database Software: ● Microsoft Windows Server 2016 Standard ● (Links to an external site.) ● (2 servers, the first is installed directly, then create an appliance from the first install and deploy the appliance for the second as directed in the install videos) *** You will not need this if you are running on Azure *** ● SQL Server 2016 Enterprise ● (Links to an external site.) ● ● SQL Server Management Studio ● (Links to an external site.) ● Download the Database: https://utah.instructure.com/courses/692156/pages/microsoft-windows-notes https://utah.instructure.com/courses/692156/pages/macos-notes https://uofu.box.com/s/9rf99r2immuhumvf8ydrwtptzviic2h0 https://uofu.box.com/s/9rf99r2immuhumvf8ydrwtptzviic2h0 https://uofu.box.com/s/i4yu6crcg9pv0e0vm3c9wi4weonawtxw https://uofu.box.com/s/i4yu6crcg9pv0e0vm3c9wi4weonawtxw https://uofu.box.com/s/trfjx1dm6itzsq1hvcqib0aywfdgkqw3 https://uofu.box.com/s/trfjx1dm6itzsq1hvcqib0aywfdgkqw3 ● AdventureWorks2016 ● download ● Lab Setup Instructions: Before Week 2, please complete Part 1 of the Lab Setup Instructions. Installing the Lab Part One If you want to run on Azure - use the instructions here: Lab Setup on Azure. You will not need Part One - but will need to read and perform part two. After you finish Part One - Please continue to Part Two: Installing the Lab Part Two Students are encouraged to help each other out on the labs, but each student must do their own work and turn in their own assignments. I want to reiterate this point: Turning in work that you (yourself) did not create and telling me that you created it is a form of academic dishonesty which violates the student code. Do not do this. At the very least - you will fail the assignment. You can ultimately fail the course for academic dishonesty and can be referred to the university for further action. DO NOT JEOPARDIZE YOUR DEGREE - DO YOUR OWN WORK. It is far better to hand in bad work that you did - then to pass off someone else's work as your own. Getting the lab functional is a fundamental first step to getting the labs finished. These parts have been separated as to not overwhelm students who are unfamiliar with this kind of work. If students