Database SQL Server Administration

This article discusses the different server roles in SQL Server, exception handling techniques, common sources of database failure, and the importance of database backup. It also covers the levels of backup and their applications. The article provides expert insights on Desklib.

Running head: DATABASE SQL SERVER ADMINISTRATION
Database SQL Server Administration
Name of the Student
Name of the University
Author’s note:

1DATABASE SQL SERVER ADMINISTRATION
Table of Contents
Answer to Question 1:................................................................................................................2
Answer to Question 2:................................................................................................................3
2.1 Exception Handling Technique:.......................................................................................3
2.2 Problems and Solutions:...................................................................................................4
Answer to Question 6:................................................................................................................5
6.1 Database Backup:.............................................................................................................5
6.2 Common Sources of Database Failure:............................................................................7
Bibliography:..............................................................................................................................8

2DATABASE SQL SERVER ADMINISTRATION
Answer to Question 1:
Sysadmin: Among all the server roles, the sysadmin is one of the most important one.
Taken as an example, if the login is the member of sysadmin role that it has the privilege of
doing with the MS SQL Server Database (Assaf et al., 2017). Moreover, it has the capability
of bypassing the security checks. Taken as an example, the administrator has created a
restriction login so that no login can affect the database. However, the sysadmin login has the
power of dodging the restriction. The sysadmin role has the privilege of manipulating other
roles. For this, in strict environments, few of the database administrators are provided the
accesses of sysadmin.
Securityadmin: To some point, as the name suggests, the securityadmin server role is
responsible for controlling the SQL Server security. This role has the purpose of allowing a
login to organize and handle logins to the SQL Server DB. This role determines which login
should be granted or revoked, enabled or disabled and also plays the part of authorizing
access to the login to particular parts of the SQL Server (Korableva, Kalimullina &
Kurbanova, 2017). The securityadmin role is not able to allow privileges to a login beyond
database boundary. The DBAs are very focused on the fact that the role of this membership
should be properly controlled. In terms of production, the membership of the securityadmin
role is carefully restricted.
db_cretator: The dbcreator role is for allowing a login to create databases in the SQL
Server. The access to the dbcreator role is not provided to any member of the server. The
junior database administrators are often given access to the dbcreator role so that they can
create the databases in order to allow the development team to work on a project (Pawar &
Batra, 2018). In some cases, the assigned application transfer the data to the new database on
a specific time period. To allow the application to do so, the login used by the application has

3DATABASE SQL SERVER ADMINISTRATION
the privilege of creating a new database (Hayat & Soomro, 2018). The owner of the database
has all rights over the database and this must be put under consideration while granting
membership for this dbcreator role.
db_owner: Any member of the db_owner role is capable of doing anything within the
database. The db_owner cannot bypass the security checks like the sysadmin which is a part
of DBO user. The db_owner can only face security checks if only the owner has used DENY
for blocking the access (Nevarez, 2015). Any member of the DBO_owner is capable of
removing the DENY. That is why a member is marked as db_owner only when necessary.
db_accessadmin: The db_accessadmin is responsible for managing the database
security. However, it only manages the logins to access the database. The db_accessadmin is
only designed for granting, denying or revoking permissions to logins for entering the
database. If the db_accessadmin can be used with the db_securityadmin then a complete
database security throughout the database can be implemented (Carter, 2016). The access to
the database of SQL Server is generally managed through manual procedures, like the
db_securityadmin.
db_securityadmin: The db_securityadmin is a fixed database role like the
securityadmin is a fixed server role. It manages the security of the database. It has the
purpose of allowing permissions for securable and handing memberships (Korableva,
Kalimullina & Kurbanova, 2017). Not many database administrators are using this role for
managing the security of the database. Because of its very little use, it can be considered as
the membership of exception.

4DATABASE SQL SERVER ADMINISTRATION
Answer to Question 2:
2.1 Exception Handling Technique:
Exception Handling with Try, Catch and Throw: The ‘Try’ statement if for
capturing the exception. Suppose, within the try block an exception occurs then the exception
handler, which is a part of system, works for delivering the exception to various other parts of
the program that handles the raised exception. The handling of the exception is managed by
the catch. Taken as an example, a referential integrity errors occur in the code (Freedman,
Ismert & Larson, 2014). Now, if the code is written within the Try block then the exception is
raised and the CATCH block is activated this. The catch plays the role of rolling back all the
codes or statements and shows an associated message on the screen. After the Throw
statement is executed, the THROW returns the error statements to caller’s batch.
2.2 Problems and Solutions:
SQL Error 80004005: This error tells that the connection between the software and
the SQL is not possible to establish. Until this error is resolved, the software cannot be
opened.
The only thing can be done in this scenario is that checking all the connections
between the application and the SQL Server (Bui, 2018). The DBA must check whether the
local host and other client agents are same as configured.
SQL Error - Error Code 10 (Services Not Installed): This error means that the
MyVision software is not able to detect that instance of the SQL server is being pointed
toward the exit.
The first thing that can be done is checking whether the SQL Server is installed or
not. If the server is not installed then the server must be installed (Assaf et al., 2017). After

5DATABASE SQL SERVER ADMINISTRATION
the installation is complete, the database must be tested for making sure that this error will
not occur again.
SQL Error - Error Code 10 (Services Not Running): Eleven after installing the SQL
server, the error code 10 can appear on interface screen. This can be caused by the issue of
SQL server automatically stops running.
First open the windows services by typing services.msc in the command prompt. Then
find the service named SQL Server (Bui, 2018). After the server service is found, check if the
SQL server is running or not. If the server is not running then right click on the service and
select start service.
SQL Error - Error Code 20: This error generally happens when the database file is
not accessible while the SQL server is properly running (Mills et al., 2015). The
communication between the application and the data file is being possible to establish.
The solution is to attach the raw data file again and then copy the data file. After the
copy is complete, the permissions are set so that database file can be accessed properly.
Answer to Question 6:
6.1 Database Backup:
Importance of Database Backup: It is essential to protect the business data. The data
backup and recovery are the vital aspects of making the business run. The owners of the
business have realized that the computer system crash, error from human side and disaster are
three inevitable things in business (Ismail et al., 2016). This catastrophes happens when the
business owner is least expecting it. The business can be more resilient to the catastrophes
like these by using the data backup/recovery plan as organization’s operational part. The

6DATABASE SQL SERVER ADMINISTRATION
massive amount information are typically saved in the separate dedicated drives or offsite
servers.
The first point for considering the database backup as importance is security. If the
data stored in the database is stored in the safe physical location then the data theft can be
easily avoided. The encryption application tools works as encrypt and decrypt mechanism for
making the unreadable for external source or element (Harvey & Oliver, 2016). The second
factor is the reduced workload. It is a headache to manually backup the data. In the modern
era where the petabytes of data are stored in organization database, the manual backups are
just out of the concern. The automated backup not only reduces the workload of the
organization but also decreases the money needed for the process. The most important factor
of data backup can be saving data from distant location. Due to technological advancement,
the vendor organization can upload the organization data into its own warehouse database
from distant location. The interesting fact is, the organization can save the data as frequently
as they like. The impact of data backup is not only limited to these factors (Ismail et al.,
2016). The database backup is also capable of mitigating risks. The computers installed
within the organization has a high possibility of being attacked by virus or malware. These
cyber threats can damage the data permanently. The organization if has database backup plan
then can easily recollect the damaged data or even stolen data.
Levels of Backup: There are mainly five levels of backup available in the SQL
Server. The levels are as flowing.
i. Full Backup: The full backup or also called as the database backup is the
most common level in the SQL Server. In this backup, the whole database as
well as the transaction logs are stored (Bleja, 2016). This level is the simplest
of all because all the content of the database and transaction remain in one file.

7DATABASE SQL SERVER ADMINISTRATION
ii. Transaction Log: The Transaction Log level backup is applied when the
database is set for Bulked Log or Full recovery model. If the transaction log
level is also done with full backup then the organization can easily put the
database in the mode when the backup was done (Eshghi et al., 2016).
iii. File Backup: The file level backup is another database backup. In this level,
each file is stored in the computer individually instead of storing the whole
database at once. This level of backup is only applicable when different files
are generated for creating the database.
iv. Filegroup Backup: Rather than executing the file backup, the organization
can consider the Filegroup level backup. This level of backup allows the
organization to backup the files that are in a specific file group (Bleja, 2016).
As a default, the database must have a primary filegroup that is linked one
data file which is created.
v. Partial Backup: The partial backups was relatively new level of backup that
other discussed levels. This level of backup is applied when only primary
filegroup is to be saved. Along with this, the entire Read-Write filegroup set
and other optional specific files can be saved.
6.2 Common Sources of Database Failure:
System Crash: The system crash failure generally refers to the type of hardware
malfunctioning or bugs within the OS or operating system of database application. It is
capable of pausing the transaction processing (Kendrick, 2015). It is also able to delete the
contents that are in the database.
Statement Failure: It is known that the database generates the output based on the
inserted SQL statement (Makela et al., 2014). Therefore, a failure of statement can be
considered as the failure of the database system for executing the entered SQL Statement.

8DATABASE SQL SERVER ADMINISTRATION
Media Failure: The media failure is one of the most severe failure of database. If the
media failure occurs then it is highly possible that entire data can be lost (Kendrick, 2015).
The way to resolve is to apply proper database backup method in the right time.

9DATABASE SQL SERVER ADMINISTRATION
Bibliography:
Assaf, W., West, R., Aelterman, S., & Curnutt, M. (2018). SQL Server 2017 Administration
Inside Out. Microsoft Press.
Assaf, W., West, R., Aelterman, S., & Curnutt, M. (2018). SQL Server 2017 Administration
Inside Out. Microsoft Press.
Bleja, M. (2016). Overview and implementing sql server high availability solutions.
Information Systems in Management, 5.
Bui, T. (2018). Proactive Maintenance Solution for Industrial Robots.
Carter, P. A. (2016). Expert Scripting and Automation for SQL Server DBAs. Apress.
Eshghi, K., Lillibridge, M., Bhagwat, D., & Watkins, M. (2015). Improving Multi-Node
Deduplication Performance for Interleaved Data via Sticky-Auction Routing.
Technical Report HPL-2015-77. HP Laboratories.
Freedman, C., Ismert, E., & Larson, P. Å. (2014). Compilation in the Microsoft SQL Server
Hekaton Engine. IEEE Data Eng. Bull., 37(1), 22-30.
Harvey, D. R., & Oliver, G. (2016). Digital curation. ALA Neal-Schuman.
Hayat, Z., & Soomro, T. R. (2018). Implementation of Microsoft SQL Server using
‘AlwaysOn’for High Availability and Disaster Recovery without Shared Storage.
International Journal of Experiential Learning & Case Studies, 3(1), 09-17.
Ismail, Z., Kiennert, C., Leneutre, J., & Chen, L. (2016). Auditing a cloud provider’s
compliance with data backup requirements: A game theoretical analysis. IEEE
Transactions on Information Forensics and Security, 11(8), 1685-1699.

10DATABASE SQL SERVER ADMINISTRATION
Kendrick, T. (2015). Identifying and managing project risk: essential tools for failure-
proofing your project. Amacom.
Korableva, O., Kalimullina, O., & Kurbanova, E. (2017). Building the monitoring systems
for complex distributed systems: Problems & solutions. In ICEIS 2017-Proceedings
of the 19th International Conference on Enterprise Information Systems (Vol. 2, pp.
221-228).
Makela, K. T., Matilainen, M., Pulkkinen, P., Fenstad, A. M., Havelin, L., Engesaeter, L., ...
& Malchau, H. (2014). Failure rate of cemented and uncemented total hip
replacements: register study of combined Nordic database of four nations. Bmj, 348,
f7592.
Mills, R. J., Dupin-Bryant, P. A., Johnson, J. D., & Beaulieu, T. Y. (2015). Examining
Learning Styles and Perceived Benefits of Analogical Problem Construction on SQL
Knowledge Acquisition. Journal of Information Systems Education, 26(3), 203.
Nevarez, B. (2015). Microsoft SQL Server 2014 Query Tuning & Optimization. McGraw-Hill
Education.
Pawar, S., & Batra, H. (2018). Online electronic laboratory notebook: A secured cloud
storage system scripted in Hypertext Pre-processor (PHP) programming language.
Journal of Engineering and Technology Research, 10(1), 1-6.