Configuring Additional DBMS Components and User Roles

Best Beverages Limited requires an additional database server instance in the New York branch for testing applications built at the Moscow branch. The new database instance must be created with a detailed step-by-step guide and must allow remote access from other servers. Additionally, Database Mail service must be set up within this instance using T-SQL. Furthermore, any databases created after this point must have their Page Verify option set to None and their Read Only option set to true. Windows Authentication users must also be created for Mario Pace and Maria Saliba, with respective permissions assigned. A SQL Server user named Anna Cassar must also be created in the development database server instance in the Moscow branch, with permissions assigned and a specific database instance role (DBStuff) created allowing her to create and view databases.

Page 3 of 9
Assignment 1 (Home)
Plan, Install and Configure a DBMS solution
Instructions to Students
The following instructions are to be carefully read before starting this assignment. It is
important to clarify any of the below points with your lecturer before attempting this
assignment.
This home assignment must be completed within the date communicated by the
lecturer
This assignment has a total weight of 41%.
Copying is strictly prohibited and it will be penalised through a referral and other
disciplinary procedures as per MCAST Policies, Procedures and Regulations.
Your final answer should be one single file containing all the answers. It should be
uploaded to TurnitIn using MCAST’s official email. TurnitIn details shall be provided
by your lecturer.

Page 4 of 9
CASE
SCENARIO
You have been tasked with the development of a new database environment for a multi-national
beverage company named Best Beverages Limited. This company supplies its products to several
countries around the world but its main headquarters reside in:
Sydney (Australia): this branch is used to administer all the resources and users across the
different branches. The server in this branch should act as the domain controller without any
database instances installed.
Moscow (Russia): personnel in this branch are typically involved in building and modifying
different applications which make use of different types of data. The personnel in this
branch require that from a database environment point of view, the database development
environment is separated from the production environment within the same server. (Hint:
Make sure that the appropriate SQL Server version is used in for each environment)
New York (America): personnel in this branch include managerial people who review large
amounts of the worldwide data (stored locally) and require efficient report generation to
support their mission critical decisions making process.

Page 5 of 9
KU1 – Identify requirements for a DBMS solution
KU4 – Illustrate the need for multiple instances
1. You are to include a single diagram which clearly specifies the following items for each branch
mentioned in the description:
a. The selected database server edition.
b. The selected database instance names.
c. The selected database components.
2. You are to write a brief paragraph, in which you clearly justify your selections with regards to
the selected database server edition and components in the Moscow and New York branches.
3. The problem description suggests that the company has several Windows Servers around the
globe with different database servers installed. You are to discuss the reasoning behind this
decision. You are to mention plausible reasons why such an approach was used.
AA1– Complete a DBMS installation
4. In this section, you are to install all the required components so that the design depicted in
question 1 is implemented in a virtualised environment. You are to make sure that all the below
requirements are included:
a. The Windows Servers are connected via a domain controller (the domain controller
should be named using your name initials followed by your id card. Eg: If your name
is Luke Vella and your id card is 11295M, the domain name should be LV11295).
b. Roaming profiles should be allowed in such a system (hence the same user account
can be used to log on different Windows Servers)
c. Each Windows Server has all the necessary database servers installed (you should
use a meaningful name for each database server and include all the required
components)
d. All database engine instances should be accessible both locally and remotely. (Hint:
Named database instances require specific configuration settings to be accessed
remotely)
Note: For this section, you are to include the following deliverables

Page 6 of 9
- A list of the major steps (VMware settings, Operating System Installation, Operating System
Configuration, DBMS Installation, DBMS Configuration) that have been undertaken to
implement the designed system as per question 3.
- A step-by-step guide (with screen-shots), capturing all the steps which were carried out to
create the designed database environment. In this guide, you should clearly show:
o All the specific settings related to the Windows Server, such as; server edition, server
name, domain name, and firewall rules.
o All the specific settings related to the database instances such as: SQL Server edition,
instance names, and the installed components and any configuration settings.
o Evidence that you can connect to each database instance both locally and remotely
SE1 – Manage additional DBMS components
5. Best Beverages Limited requires an additional database server instance within the New York
branch which will be specifically used for testing of applications built at the Moscow branch.
You are therefore to add a new database instance within the server which resides in New York.
You are to include a detailed step-by-step guide of how this requirement was implemented. You
are also required to be able to access this instance remotely from the other servers (make sure
to include evidence of this).
6. Another requirement for the New York branch is that of setting up a Database Mail service
within the newly created testing database instance in question 5. You are to use your school
email account and T-SQL to set up the Database Mail service. The T-SQL code which was used is
to be included in the answer file. You are also to include a screenshot of the T-SQL code and its
result which shows that successful emails have been sent through this Database Mail service
(You are to clearly show the values for the mailitem_id, recipients, subject, body and status)
7. You are to modify your testing database instance, such that any databases which are created
from this point onwards will have their Page Verify option set to None and their Read Only
option set to true. You are to do this using T-SQL and the code be included in the answer file.
8. In this question, you are to include the syntax which would be used in the case that you wish to
remove the database instance which was implemented in question 5.

Page 7 of 9
AA3 – Differentiate between different type of logins and roles
AA4 – Change user and role permissions
Best Beverages Limited has numerous employees forming part of their organisation. In this section
you are required to include the following requirements in relation to users, roles and permissions.
9. You are to create the following Windows Authentication users within the installed environment.
Each user created should be able to log into all the installed server machines.
User 1: Mario Pace – this user should be allowed to perform any activity both in
Windows Server and any database instance (make sure to assign respective setting
for both Windows and Database Server)
User 2: Maria Saliba – this user should be a normal user within Windows server, but
should be the only person who is responsible of create, change, remove and restore
databases within any database instance
All these users can be created using the graphical user interface. You are to record each and
every step carried out while creating and assigning privileges to these users. Evidence that this
user exists and functions as required needs to be included.
10. You are to create the following SQL Server user within the development database server
instance in the Moscow branch.
User 3: Anna Cassar – this database instance user should be able to access the
development database server using an SQL Server login account.
You are to use T-SQL code to generate and assign the required permissions to this user.
You are to provide evidence that the user can access the database instance.
You are to create a specific database instance role on this server name DBStuff which
will allow users to create and view any database on this database server instance.
You can either use T-SQL or the GUI wizard to perform this task.
You are to assign Anna Cassar to this newly created role and make sure that these are
the only tasks she is allowed to perform.
You can either use T-SQL or the GUI wizard to perform this task.

Page 8 of 9
Marking Scheme
KU1 – Identify requirements for a DBMS solution
Maximum Awarded
Correctly selected Database Server Editions
Correctly selected Database Components
Correct justifications for all the 2 factors
1.5
1.5
2
Total 5
KU4 – Illustrate the need for multiple instances
Maximum Awarded
Correctly justify your design selections in Q2 3
Plausible reasons for Q3 2
Total 5
AA1 – Complete a DBMS installation
Maximum Awarded
Windows Server OS installed successfully 1
Domain Controller installed successfully 1
SQL Server instances installed successfully in Moscow Server
SQL Server instances installed successfully in New York Server
1
1
All instances accessible locally and remotely 3
Total 7
SE1 – Manage additional DBMS components
Maximum Awarded
Implement the testing instance in Sydney server
Connect to the testing instance from another server
1
2
Setting up database mail using T-SQL in the testing instance
Evidence that email was sent successfully through T-SQL
2
2
Page Verify option successfully implemented
Read Only option successfully implemented
1
1
Syntax to remove database instance 1
Total 10

Page 9 of 9
AA3 –Differentiate between different type of logins and roles
Maximum Awarded
Successfully create Windows authenticated user (1 mark each) 2
Correct Windows permissions given to Mario Pace 1
Correct Database instance permissions given to (1 mark each) 2
Correct Instance logins created (1 mark each) 2
Total 7
AA4 – Change user and role permissions
Maximum Awarded
Correct code to allow for SQL Server users 1
Anna Cassar can successfully log into the database server 2
Database instance role created successfully 2
Anna Cassar is assigned to the DBStuff database instance role 1
Evidence that DBStuff role works as required 1
Total 7
Total