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