Get Instant Help From 5000+ Experts For
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing:Proofread your work by experts and improve grade at Lowest cost

And Improve Your Grades
myassignmenthelp.com
loader
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Guaranteed Higher Grade!
Free Quote
wave

1.Explain the following fixed-server/database roles that exist in the SQL server:


i) Sysadmin

ii) Securityadmin

iii) DB-creator

iv) DB-owner

v) DB-accessadmin

vi) DB-securityadmin 

2.Discuss different types of exception handling techniques for identification of problems and write down the appropriate actions required to resolve the problems.

3.a) Evaluate the importance of database backup. Explain the different levels of backup in SQL server database in details.

b) Analyse and explain the common sources of database failures in details. 

Fixed-Server/Database Roles in SQL Server

The sysadmin plays a fixed-server role in the SQL server. This actually is an admin level role which covers all the other roles in the SQL server and consists nof unlimited scope. By having this property, the sysadmin has become very much powerful in the environment of SQL server (Delaney & Freeman, 2013). It is very much important to know which peoples are having the role of a fixed server and this query can be made using the sysadmin as this is having full control over the server.

Like the sysadmin, the securityadmin also plays the fixed-server role in a SQL server and can be considered equivalent to the role of the sysadmin. The members of the securityadmin can manage the logins in the server and the properties of them (Masood-Al-Farooq, 2014). The securityadmin can DENY, GRANT and can REVOKE the permissions in the server level. With the server level permission members of the securityadmin can also GRANT, REVOKE and DENY permissions in the database level if the members are having access to the database. Another additional feature of the securityadmin is that SQL server login passwords can be reset through it.

As per the name, the DB-Creator allows creating a database in the server after a successful login in the database. A normal user cannot be a member of this DB-Creator (Ward, 2018). In many of the cases the DBA’s does not get full rights to the sysadmin, and in this type of cases, the DB-creator is used for creating the database in the server. The DB-Creator is very much useful for creating new databases without any intervention when the sysadmin is absent to grant the permission. The DB-Creator is an ideal role for the junior DBA for controlling the SQL server.

The DB-Owner plays the role of the fixed database in the SQL server. This type of fixed database roles is stated at the level of database and in each of the database these roles exist. The members of the DB-Owner are able to manage the fixed-database role membership (Utami & Raharjo, 2014). By this, the members of the DB-Owners are able to do all the activities related to the maintenance and configuration of the SQL database. The member of the DB-Owner is also able to drop a specific database in the SQL server. Though the DB-owner is related with the configuration and server maintenance tasks, it cannot perform some of the maintenance activity. This type of maintenance activities are performed in the data warehouse of the SQL server and require server-level permission.

The DB-Accessadmin also plays the role of the fixed database within the SQL server. The main role of the DB-Accessadmin members is adding and removing the accesses to the SQL database for the logins from the Windows system, Windows group and for the server logins of the SQL (??????, 2015). The DB-Accessadmin can also handle the security measures of the database. The DB-Accessadmin can grant, revoke and deny the permissions which are essential for entry into the database. With the combination with DB-Securityadmin, the total security of the database can be managed. The DB-Accessadmin is also handled by the junior DBAs, and normal users of the database cannot be a member of this role.

Types of Exception Handling in SQL Server

The DB-Security admin role is similar to the role of the securityadmin role. The main difference between these two is that securityadmin plays the role of the fixed server while the DB-Securityadmin plays the role of the fixed database in the concern of security of the database. The DB-Securityadmin manages a membership role and also manages permissions considering the securable of the SQL database (Zhao et al., 2014). Though the main role of the DB-security is in the security purposes, it is used very little as in most of the cases DBAs manage the securities. In some rare critical cases, the actual role of the DB-Securityadmin is used.

For the identification of the problems, the SQL server uses two types of exception handling techniques. These two techniques are the TRY block and the CATCH block exception handling techniques that are used for the identification of the problems (Cacho et al., 2014). The TRY and the CATCH blocks determine and catches all the errors related with the execution having severity higher than 10 and which does not close the connection of database. This mechanism of the TRY and CATCH block is mainly two code blocks which store some of the procedures which are essential for executing some other codes. The two section works in a specific way that when some errors are detected by the TRY block, these errors are handled in the CATCH blocks (Lal & Sureka, 2016). Whenever there is no error is detected by the TRY block the control passed to the statement instantly next to the statement of the END CATCH. For the cases when an error has been detected within the TRY block the control pass to the first statement within the CATCH block. In this case, if the last statement is the END CATCH in the procedure, then the control is passed back to the stored procedure statement. After the execution of the CATCH block, the control goes to the state next to the statement of the END CATCH (Mistry & Misner, 2014). The errors which trap in the in the CATCH block does not go back to the calling application. In many cases when the information needs back to the application, the CATCH block uses the mechanism of the SELECT or RAISERROR and PRINT.

The construction of the TRY CATCH is generally nested kind. In this type of structure, either the CATCH block or the TRY block can contain the nested constructs of the TRY CATCH block. The errors which are detected in the CATCH block are treated such as that it is generated in somewhere else (Deng, Offutt & Li, 2013). Considering the execution of the TRY block, it handles the errors in some specific ways. In the first case when the stored procedure does not have any type of TRY CATCH construct error to return the control to TRY block associated CATCH block which holds the statement of EXECUTE. In the second case when the stored procedure does have a construct of a TRY CATCH, the error returns the control to the CATCH block. After the execution of the CATCH block, the control goes to the next after the EXECUTE statement which is a stored procedure.

Significance of Database Backup

The backup of the database is very much important in the aspects of the business needs. There are various reasons behind the database backup. The database backup provides unrestricted access to the data. When the database is backed up to an online server, it becomes available irrespective of location and time. Thus it helps in the business processes at any time. Another main importance behind the database backup is the extra protection (Jukic, Vrbsky & Nestorov, 2016). When the whole database is backed up regularly to a cloud server, it provides some extra security regarding the data safety. When the main database is corrupted or loosed then this backup very much useful for retrieving the data. Protection of the customer data is very much essential for any organization. Thus by creating a backup of the database, the data regarding the customer can be stored safely (Zaman & Butt, 2013). The data management systems can be costly in various cases for the organizations. In such of the cases the database backup becomes very much helpful as this will help the organization to overcome the costs of the data management system which can be very much expensive for some start-up organizations.

 For the backup of a SQL, the main types of database backups are the normal or a full backup, incremental backup, differential backup and the daily backup. Each of these backup techniques has their own advantage and disadvantage. Moreover, these backup strategies can be used together for creating an utmost strategy of recovery. Thus it can maximize the efficiency of the organization.

The first level of backup is the normal or the full backup. When the normal backup starts its execution, it backs up all the files of the targeted database of the location. This backup includes all the system files, user files and the application data. All the targeted files copied into a selected destination and then the archive bits are cleared.

In the next, another type of backup is the incremental backup. Since backup is a long process and takes a huge amount of time taking the backup in a smarter way is the essential solution for the business (Xu et al., 2015). This type of incremental backup only runs once per a week. Also, only those files are backed up which have been created new or have been updated since the last backup. That means whenever a file will be backed up. It will be not backed up further until any changes occur in the file. Thus the backup actually becomes lighter and takes a very less amount of time.

The differential backup is similar with the incremental backup but actually simpler than the incremental backup. It is simpler than the incremental backup in the sense that the backup restore process is less critical than the incremental backup restoration method. The main difference between the incremental backup and the differential backup is that the differential backup only targets those files of the database which are modified since the last normal backup, but this backup does not clear any type of archive bit.

Common Sources of Database Failures

The last level of the data backup is the daily backup. This type of backup is less common in the database backup system. In this type of backup system, only the critical operational files are taken into backup consideration. In this backup system, all the modified files backed up continuously. Generally, this types of backup include the backup technique of determining the timestamp of a file.

There are several types of database failures when considering the database processing. In this, some of the failures affect the main memory while the others affect the secondary storage. The main two common sources of database failure are the hardware failures and software failures.

The hardware failure includes the memory errors in the database. This memory error arises due to the bad disk sector, disk crashes and due to the disk full errors (Wang et al., 2015). Hardware errors are considered under the design errors, poor quality during the process of fabrication and overloading issue of the mechanical parts.

The software failure arises in the system due to the issues in the software. The software issue includes crashing of DBMS software, operating system, an application program and more.

Another type of database failures is the system crash. The system crash can occur due to both hardware and software failure. This type of failure occurs due to when the hardware part or the software part of the stop proper functioning and exits from the system. This type of failures can crash the entire data of the database permanently.

References:

Cacho, N., Barbosa, E. A., Araujo, J., Pranto, F., Garcia, A., Cesar, T., ... & Garcia, I. (2014, September). How does exception handling behavior evolve? an exploratory study in java and c# applications. In Software Maintenance and Evolution (ICSME), 2014 IEEE International Conference on(pp. 31-40). IEEE.

Delaney, K., & Freeman, C. (2013). Microsoft SQL Server 2012 Internals. Microsoft Press.

Deng, L., Offutt, J., & Li, N. (2013, March). Empirical evaluation of the statement deletion mutation operator. In Software Testing, Verification and Validation (ICST), 2013 IEEE Sixth International Conference on (pp. 84-93). IEEE.

Jukic, N., Vrbsky, S., & Nestorov, S. (2016). Database systems: Introduction to databases and data warehouses. Prospect Press.

Lal, S., & Sureka, A. (2016, February). LogOpt: Static feature extraction from source code for automated catch block logging prediction. In Proceedings of the 9th India Software Engineering Conference (pp. 151-155). ACM.

??????, ?. (2015). MS SQL Server 2000.

Masood-Al-Farooq, B. A. (2014). SQL Server 2014 Development Essentials. Packt Publishing Ltd.

Mistry, R., & Misner, S. (2014). Introducing Microsoft SQL Server 2014. Microsoft Press.

Utami, E., & Raharjo, S. (2014). Database security model in the academic information system. International Journal of Security and Its Applications, 8(170).

Wang, Y., Wei, X., Zi, Y., & Tsui, K. L. (2015). In situ measurement of seeking speed and seeking induced head-disk interface instability in hard disk drives. Shock and Vibration, 2015.

Ward, B. (2018). Security in SQL Server. In Pro SQL Server on Linux (pp. 317-367). Apress, Berkeley, CA.

Xu, Q., Zhao, L., Xiao, M., Liu, A., & Dai, Y. (2015). YuruBackup: a space-efficient and highly scalable incremental backup system in the cloud. International Journal of Parallel Programming, 43(3), 316-338.

Zaman, M., & Butt, M. A. (2013). Enterprise Data Backup & Recovery: A Generic Approach. International Organization of Scientific Research Journal of Engineering (IOSRJEN), 2278-4721.

Zhao, G., Lin, Q., Li, L., & Li, Z. (2014, November). Schema conversion model of SQL database to NoSQL. In P2P, Parallel, Grid, Cloud and Internet Computing (3PGCIC), 2014 Ninth International Conference on (pp. 355-362). IEEE.

Cite This Work

To export a reference to this article please select a referencing stye below:

My Assignment Help. (2021). Essay: Fixed-Server/Database Roles, Exception Handling, Database Backup's Significance, Common Database Failures.. Retrieved from https://myassignmenthelp.com/free-samples/info151-databases/environment-of-sql-server.html.

"Essay: Fixed-Server/Database Roles, Exception Handling, Database Backup's Significance, Common Database Failures.." My Assignment Help, 2021, https://myassignmenthelp.com/free-samples/info151-databases/environment-of-sql-server.html.

My Assignment Help (2021) Essay: Fixed-Server/Database Roles, Exception Handling, Database Backup's Significance, Common Database Failures. [Online]. Available from: https://myassignmenthelp.com/free-samples/info151-databases/environment-of-sql-server.html
[Accessed 19 March 2024].

My Assignment Help. 'Essay: Fixed-Server/Database Roles, Exception Handling, Database Backup's Significance, Common Database Failures.' (My Assignment Help, 2021) <https://myassignmenthelp.com/free-samples/info151-databases/environment-of-sql-server.html> accessed 19 March 2024.

My Assignment Help. Essay: Fixed-Server/Database Roles, Exception Handling, Database Backup's Significance, Common Database Failures. [Internet]. My Assignment Help. 2021 [cited 19 March 2024]. Available from: https://myassignmenthelp.com/free-samples/info151-databases/environment-of-sql-server.html.

Get instant help from 5000+ experts for
question

Writing: Get your essay and assignment written from scratch by PhD expert

Rewriting: Paraphrase or rewrite your friend's essay with similar meaning at reduced cost

Editing: Proofread your work by experts and improve grade at Lowest cost

loader
250 words
Phone no. Missing!

Enter phone no. to receive critical updates and urgent messages !

Attach file

Error goes here

Files Missing!

Please upload all relevant files for quick & complete assistance.

Plagiarism checker
Verify originality of an essay
essay
Generate unique essays in a jiffy
Plagiarism checker
Cite sources with ease
support
Whatsapp
callback
sales
sales chat
Whatsapp
callback
sales chat
close