SQL Server Roles, Policy-Based Management, and Exception Handling

This assignment delves into critical aspects of SQL Server administration and database management. It begins by explaining fixed server and database roles such as sysadmin, securityadmin, DB-Creator, DB-Owner, DB-accessadmin, and DB-Securityadmin, detailing their respective privileges and responsibilities. The solution then explores Policy-Based Management (PBM) in SQL Server, illustrating its role in monitoring and enforcing database policies, including examples like checking for auto-shrink options. Finally, the assignment discusses various exception handling techniques for identifying and resolving problems in database environments, emphasizing the importance of proper documentation, avoiding common pitfalls like catching `Throwable`, and handling exceptions in a chronological order. The assignment references several academic sources, including works by MacLennan, Tang, and Crivat (2011), Bernstein et al. (2011), and Agrawal et al. (2009), to support the concepts presented.

2. Explain the following fixed server/ database roles that exist in the SQL server:
I. Sysadmin
The members of the sysadmin have the privilege to carry out any activity in the server
II. Securityadmin
They manage the logging in and its properties. They have the ability to deny, grant and also
revoke a server level permission. They can also do the same to the database if they have the
access to the database. They can also execute reset of the password used for the login of the SQL
server.
III. DB-Creator
The members of the DB-Creator include creating a database, altering the database, drop or even
restoring any database.
IV. DB-Owner
The DB-Owner directs and controls roles of the various members of the fixed database. The DB-
Owner can also input extra Server roles and database accounts to the database-level roles
(MacLennan, Tang, & Crivat, 2011).
V. DB-accessadmin
The members in the DB-accessadmin have the ability to remove or add access to the database
login for the SQL server, Windows groups, and Windows logins.
VI. DB-Securityadmin
The roles of the DB security admin are managing the member of the fixed database and also
manage permits.

Qn 3
Policy-Based Management
These are instances of the sql server that allow it to hold more instances. This a technique that is
used for monitoring and enforcing policies in an organization database. While there are many
conditions that are to be chosen, the policy management can mainly be set as a standard based.
This feature can evaluate the state of multiple servers with just one glance. Policy based allow
users to check servers which can be used to confirm the configuration of servers. PBM property
of databases can be illustrated by checking databases if they have an auto shrink option which is
normally out of the currency policy (Bernstein et al., 2011). This is built in feature of the of the
sql servers. The main feature of IBM is the ability to create anything using the custom
policy .This involve use of articles like “large values out of space” for tables that possess varchar
fields. A custom query is checked in order to confirm id the values exits in the given table in the
database. It uses condition that are based on the facets. Conditions are, as the name infers,
criteria that will be executed amid the strategy check. Aspects are pre-characterized objects that
recoup data from a few sorts of articles in the server. They are utilized to construct the
conditions. Approaches, thusly, unite the conditions, deciding how their result will be utilized.
5. Discuss different types of exception handling techniques for the identification of
problems and write down the appropriate action required to resolve the problem.
As much as the exception is used as an error handling technique, they are also used as a medium
of communication.
Consider that the not all the coworker do not understand your code or in some instances, you
may later be needed to call your method and also handle the exception. In order to handle this
one need to provide adequate information, making your code and API easier to understand.
Documentation of exception you specify

This exception is brought when there is no documentation when you specify an exception in the
Javadoc. In order to solve this, there is a need to put a @throws declaration in the Javadoc and
also state the instances that can cause an exception.
Don’t catch Throwable
The throwable is a mother class to errors and exceptions and is used in the catch clause. One
should avoid them unless one is very sure that he can handle the errors.
Don’t ignore exceptions
This is when one ignores an exception and later causes an error. To handle this, one should not
ignore an exception.
Catching the specific expectation with a chronological order
Majority of the IDEs report the unreachable blocks of codes when they are catching the less
specific exception (Agrawal et al., 2009). To handle this you should handle the most specific
exception and handle the less one later.

References
Agrawal, S., Chaudhuri, S., Kollar, L., Marathe, A., Narasayya, V., & Syamala, M. (2009, June).
Database tuning advisor for microsoft sql server 2005. In Proceedings of the 2005 ACM
SIGMOD international conference on Management of data(pp. 930-932). ACM.
Bernstein, P. A., Cseri, I., Dani, N., Ellis, N., Kalhan, A., Kakivaya, G., ... & Talius, T. (2011, \
April). Adapting microsoft SQL server for cloud computing. In Data Engineering \
(ICDE), 2011 IEEE 27th International Conference on (pp. 1255-1263). IEEE.
MacLennan, J., Tang, Z., & Crivat, B. (2011). Data mining with Microsoft SQL server 2008.
John Wiley & Sons.