Reference no: EM132846071
DBAD6010 Database Server Administration - The Independent Institute of Education
Assignment brief
You have been hired as a Database Administrator at the Steve Biko Academic Hospital in Pretoria. They have been using a combination of SQL Server 2008 and Microsoft Access in order to store and manage the data of all patients that are admitted to the hospital. However, they have experienced a number of performance and security issues of late. They would like to upgrade their systems without changing much of the hardware in which they have already invested.
Research and answer the following questions:
Question 1
*Q.1.1 Write a short motivation to your manager explaining why it is necessary to upgrade to SQL Server 2012. Your explanation needs to focus on the following elements:
• Return on investment;
• Scalability;
• Performance;
• Productivity;
• Security.
Q.1.2 Write a brief discussion detailing the key differences between main editions of the SQL Server 2012.
Q.1.3 After you gave your manager the motivation for upgrading to SQL Server 2012, he mentioned that he attended a conference a few weeks back where SQL Server 2014 was introduced to them. He wants to know why you do not just upgrade to SQL Server 2014 straight away.
Write a short report to him comparing SQL Server 2012 and SQL Server 2014, focusing on the hardware requirements, costs, versions, and any other relevant elements that you consider important to make your case for upgrading to SQL Server 2012 instead of SQL Server 2014.
Q.1.4 After searching on the Web, you realise that SQL Server 2016 has already been released. Write a short paragraph with relevant sources on the important features of SQL Server 2016 focusing on technologies such as security and cloud computing.
Question 2
After much discussion and debate, your Database Manager has decided that you need to set up a sandbox-server (Database Server) so as to test some of its functionalities within a real-life environment. The following questions relate to the installation, configurations, and testing of a Database Server.
Q.2.1 On your installation of Microsoft Virtual PC and Server or desktop environment, install a stand-alone fresh copy of Database Server with all the important features (e.g. Database Engine Services, Reporting Services and others). You need to also pay attention to the following settings:
• Named Instance: ‘YOURNAME'01;
• Authenticate all services using LocalSystem account;
• Choose mixed authentication mode;
• Set appropriate System Administrator password.
Q.2.2 Create a prototype database for Steve Biko Academic Hospital to manage its patients.
You need to create at least five tables and populate it with dummy records. You need to follow all the rules of creating a database and tables with appropriate data types. Also create appropriate constraints, such as age, gender, child or adult, doctor's name, medical bill paid, disease, etc.
Q.2.3 Create three stored procedures that will assist the statistics manager with the following statistics:
• Show all patients who have not paid their medical bill;
• Show all patients that are not considered adults;
• Show the total cost of medical bill paid by individuals over the age of 65. You need to also use built-in functions in this case.
Q.2.4 For the newly installed sandbox Database Server, you only want to have the IT staff, Database manager, and yourself to have access to the prototype database. Create the Database Server Logins and assign the relevant permissions as you deem necessary. Test at least one login.
Q.2.5 One of the issues experienced with SQL Server 2008 in your organisation was the issue of security. After you have consulted a security expert, it was suggested that you apply the following security changes to SQL Server 2012:
• SQL Server logins that use windows authentication and SQL Server authentication;
• Manage server and database permissions using Object Explorer;
• Hide SQL instances from showing in the network;
• Allow only network protocols that are needed;
• Add only yourself (dba_user) to the sysadmin role.
Instructions: Show the screenshots of all the configurations above in your SQL Server 2012.
Note: Need Only question 1.1
Attachment:- Database Server Administration.rar