SQL Server DBA Interview Questions and Answers – SQL Server Agent and Its Components

Hi friend

I posted SQL Server Agent and Its Components related question and answer for interview.

1) What is SQL Server Agent? What are its benefits?
SQL Server Agent is a Microsoft Windows service that executes scheduled administrative tasks, TSQL Statements, SSIS packages, Reports subscriptions etc.

2) What are various components of SQL Server Agent service?
⦁ Jobs
⦁ Schedules
⦁ Operators
⦁ Alerts

3) What is a SQL Server job?
A job is a specified series of actions that SQL Server Agent performs. We can use jobs to define an administrative task that can be run one or more times and monitored for success or failure. A job can run on one local server or on multiple remote servers.

We can run jobs in several ways:
⦁ According to one or more schedules.
⦁ In response to one or more alerts.
⦁ By executing the sp_start_job stored procedure.

Each action in a job is a job step. For example, a job step might consist of running a Transact-SQL statement, executing an SSIS package, or issuing a command to an Analysis Services server. Job steps are managed as part of a job

4) What is an Operator?
An operator defines contact information for an individual responsible for the maintenance of one or more instances of SQL Server. In some enterprises, operator responsibilities are assigned to one individual. In enterprises with multiple servers, many individuals can share operator responsibilities. An operator does not contain security information, and does not define a security principal.

SQL Server can notify operators of alerts through one or more of the following:
⦁ E-mail
⦁ Pager (through e-mail)
⦁ net send

5) What is a Schedule?
A schedule specifies when a job runs. More than one job can run on the same schedule, and more than one schedule can apply to the same job. A schedule can define the following conditions for the time when a job runs:
⦁ Whenever SQL Server Agent starts.
⦁ Whenever CPU utilization of the computer is at a level you have defined as idle.
⦁ One time, at a specific date and time.
⦁ On a recurring schedule.

6) What is an Alert?
An alert is an automatic response to a specific event. For example, an event can be a job that starts or system resources that reach a specific threshold. You define the conditions under which an alert occurs.

An alert can respond to one of the following conditions:
⦁ SQL Server events
⦁ SQL Server performance conditions
⦁ Microsoft Windows Management Instrumentation (WMI) events on the computer where SQL Server Agent is running
⦁ An alert can perform the following actions:
⦁ Notify one or more operators
⦁ Run a job

7) Which database contains information about the jobs and other components which are required for the execution of scheduled jobs by SQL Server Agent?
system database- msdb

8) Which tables can be Queries to get details about the Jobs and its steps?
Below are some examples of the tables in msdb database which contains information about the Jobs and its steps.
⦁ sysjobs
Stores the information for each scheduled job to be executed by SQL Server Agent.
⦁ sysjobsteps
Contains the information for each step in a job to be executed by SQL Server Agent.
9) Which table contains information about the SQL Server jobs scheudule?
⦁ sysjobschedules
It contains schedule information for jobs to be executed by SQL Server Agent

10) Which tables will give you the Job history related information?
dbo.sysjobhistory

11) How can we grant permissions on the SQL Server agent jobs and which roles are available?
SQL Server contains 3 fixed database roles on the MSDB database, which gives administrators fine control over access to SQL Server Agent. The SQL Server Agent node in SSMS is visible only to users in one of these 3 roles (except sysadmins, who can see everything irrespective of role membership). Here is an explanation of the roles, in order from the most restrictive to least restrictive:

SQLAgentUserRole – Users in this role are granted view/edit/delete/execute access to only jobs owned by them. Users in this role cannot view any jobs owned by system administrators, or by users in the other two roles. Grant this role when you want users to only see jobs owned by them.

SQLAgentReaderRole – Users in this role get all the privileges of theSQLAgentUserRole, i.e. they get access to owned jobs. In addition to that, they can also view (but not modify or execute) all jobs on SQL Server Agent, irrespective of ownership. Grant this role when you want users to be able to view, but not execute, all jobs in the system, but modify/execute only jobs owned by them.

SQLAgentOperatorRole – Users in this role get all the privileges of the SQLAgentReaderRole. In addition to that, they can also execute, or enable/disable any job in the system. However, users in this role can modify only owned jobs. Grant this role for super users who can view/execute all jobs on the system.

12) Difference between the permissions of above three roles?

Action	SQLAgentUserRole	SQLAgentReaderRole	SQLAgent OperatorRole
Create/modify/delete	Only owned jobs	Only owned jobs	Only owned jobs
View List	Only owned jobs	All jobs	All jobs
Enable/Disable	Only owned jobs	Only owned jobs	All jobs
View Properties	Only owned jobs	All jobs	All jobs
Edit Properties	Only owned jobs	Only owned jobs	Only owned jobs
Start / Stop	Only owned jobs	Only owned jobs	All jobs
View job history	Only owned jobs	All jobs	All jobs
Delete job history	No	No	Only owned jobs
Change Ownership	No	No	No

13) Is SQL Mail option is available in SQL Server 2012 or not? If not what is the replacement of SQL mail?
SQL Mail has been removed in SQL Server 2012 version. It has been replaced by Database Mail option.

14) What is a Database Mail?
Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, our database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network.

15) What is the Database Mail Architecture?
Database Mail is designed on a queued architecture that uses service broker technologies. When users execute sp_send_dbmail, the stored procedure inserts an item into the mail queue and creates a record that contains the e-mail message. Inserting the new entry in the mail queue starts the external Database Mail process (DatabaseMail.exe). The external process reads the e-mail information and sends the e-mail message to the appropriate e-mail server or servers. The external process inserts an item in the Status queue for the outcome of the send operation. Inserting the new entry in the status queue starts an internal stored procedure that updates the status of the e-mail message. Besides storing the sent, or unsent, e-mail message, Database Mail also records any e-mail attachments in the system tables. Database Mail views provide the status of messages for troubleshooting, and stored procedures allow for administration of the Database Mail queue.

16) How to enable Database mail?
Database Mail is not active by default. To use Database Mail, We must explicitly enable Database Mail by using either the Database Mail Configuration Wizard, the sp_configure stored procedure, or by using the Surface Area Configuration facet of Policy-Based Management.

17) What is a Database Mail Account?
A Database Mail account contains the information that Microsoft SQL Server uses to send e-mail messages to an SMTP server. Each account contains information for one e-mail server.

A Database Mail supports three methods of authentication to communicate with an SMTP server:

Windows Authentication: Database Mail uses the credentials of the SQL Server Database Engine Windows service account for authentication on the SMTP server.

Basic Authentication: Database Mail uses the username and password specified to authenticate on the SMTP server.

Anonymous Authentication: The SMTP server does not require any authentication. Database Mail will not use any credentials to authenticate on the SMTP server.

18) What is a Database Mail Profile?
A Database Mail profile is an ordered collection of related Database Mail accounts. Applications that send e-mail using Database Mail specify profiles, instead of using accounts directly. Separating information about the individual e-mail servers from the objects that the application uses improves flexibility and reliability: profiles provide automatic failover, so that if one e-mail server is unresponsive, Database Mail can automatically send mail to another e-mail server. Database administrators can add, remove, or reconfigure accounts without requiring changes to application code or job steps.

Profiles also help database administrators control access to e-mail. Membership in the DatabaseMailUserRole is required to send Database Mail. Profiles provide additional flexibility for administrators to control who sends mail and which accounts are used.
19) What is the difference between Public profile and Private Profile?
Public profiles are available for all members of the DatabaseMailUserRole database role in the msdb database. They allow all members of the DatabaseMailUserRole role to send e-mail using the profile.

Private profiles are defined for security principals in the msdb database. They allow only specified database users, roles, and members of the sysadmin fixed server role to send e-mail using the profile. By default, a profile is private, and allows access only to members of the sysadmin fixed server role. To use a private profile, sysadmin must grant users permission to use the profile. Additionally, EXECUTE permission on the sp_send_dbmail stored procedure is only granted to members of the DatabaseMailUserRole. A system administrator must add the user to the DatabaseMailUserRole database role for the user to send e-mail messages.

20) What is a SQL Server Agent Proxy Account?
SQL Server agent jobs run with the account assigned to SQL Agent service. In case if we have to perform some task which requires some elevated permissions we can use SQL Server Agent Proxy. Proxy is about having additional security. We can specify the job step for which we need a different security context to be using the security context of the specified proxy.

21) Can we have multiple schedules for a single job?
Yes, we can run a job with multiple schedules.

22) Is it possible to get and change all the schedules information of all the jobs in a single window? If yes then from where?
Yes, we can list all the schedules of all the jobs from below option.
Click on SQL Server Agent à Jobsà Right Click on Jobsà Click on Manage Schedules

23) Is it possible to run an SSIS package from a job step? How?
Yes, it is possible to run an SSIS package by selecting the Type as SQL Server Integration Services Package in the Step.

24) Can we run Operating system command in the SQL Server jobs?
Yes, it is possible to run it using Operating System Type in the job step.

25) What is Notification property in SQL Server job properties?
These settings can be used to setup the notification with regard to failure or success of the job.

E-mail
Select this option to send e-mail when the job completes. After selecting this option, choose the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Page
Select this option to send e-mail to an operator’s pager when the job completes. After selecting this option, specify the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Net send
Select this option to use net send to notify an operator when the job completes. After selecting this option, specify the operator to notify and the condition that will trigger the notification: When the job succeeds; When the job fails; or When the job completes.

Write to the Windows Application event log
Select this option to write an entry in the application event log when the job completes. After selecting this option, specify the condition that will cause the entry to be written: When the job succeeds; When the job fails; or When the job completes.

Automatically delete job
Select this option to delete the job when the job completes. After selecting this option, specify the condition that will trigger deletion of the job: When the job succeeds; When the job fails; or When the job completes.

26) What are maintenance jobs for a SQL Server database?
Jobs which runs Database maintenance tasks like”
Index Rebuild\Reorganise tasks
Update stats tasks
Database shrink activities

27) Sometimes there is no History available for the Failure or Success of a job? What could be the reason for the same?
By default SQL Server doesn’t hold a very long job history for your server. The defaults that SQL Server comes with are as follows:
That is the reason sometimes we are not able to get the job history details due to purging of the job history.

28) How can we change the Job History Retention?
Yes, We can change the retention of the job history. Right Click on SQL Server Agent à Click on Properties à History. There are options to set the maximum job history log size.

29) What could be the reason if your Database Mail working fine but No SQL Agent Alerts are working?
Database Mail is part of the SQL Server Service which means that the SQL Server Agent cannot automatically know that it’s present and active and which settings to use. Therefore although you may have set up your Operators and are able to select them within the Notifications tab of a scheduled task, you still have to tell the SQL Server Agent which email account and profile it can use.

Check on “Enable mail profile” option and mention the profile name which needs to be used under the Alert System tab of SQL Server agent properties and Restart the SQL Server agent service.

30) What is Fail Safe Operator? How can we enable this?
We can enable a fail-safe operator feature that will receive all emails in the event that SQL Server Agent cannot communicate with the system tables in the msdb database. This is accomplished by recording the information about the fail-safe operator in the registry. The fail-safe operator will also receive notifications if you have scheduled operators to only receive notifications during certain time periods and a notification occurs outside of that range. For example, if no operators are set to receive notifications on Sunday, the notification will automatically go to the fail-safe operator.

To enable a fail-safe operator, right-click SQL Server Agent and select Properties from the context menu. Then select the Alert System page as shown in the following image.

Select the Enable fail-safe operator checkbox and then choose the appropriate operator and notification method. Click OK to save the changes.

31) What is the reason when SQL Server Agent fails to start because of the error 15281 which is a very common error?
When you start to restart SQL Agent sometimes it will give following error.

SQL Server blocked access to procedure ‘dbo.sp_get_sqlagent_properties’ of component ‘Agent XPs’ because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of ‘Agent XPs’ by using 

sp_configure. For more information about enabling ‘Agent XPs’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)

To resolve this issue we have to enable Agent XPs option at the SQL Server instance level using below script.

sp_configure 'show advanced options', 1;
GO
 RECONFIGURE;
 GO
 sp_configure 'Agent XPs', 1;
 GO
 RECONFIGURE
 GO

SQL DBA Interview Questions and Answers – Security Permissions

Hi friend

I posted SQL Server Security Permissions related question and answer for interview.

1) What is Authentication and Authorization? What is the difference between both?
Authentication is the process of verifying who you are. Logging on to a PC with a username and password is authentication.

Authorization is the process of verifying that you have access to something. Authorization is gaining access to a resource (e.g. directory on a hard disk) because the permissions configured on it allow you to access it.

2) How many type of SQL Server authentication mode supported by SQL Server 2012?
There are two type of authentication available in SQL Server.

Windows Authentication — TRUSTED connection
Windows Logins
Windows Groups

MIXED authentication — NON Trusted connection
Windows Logins
Windows Groups
SQL Server logins

3) What’s the difference between Windows and Mixed mode?
Windows authentication mode requires users to provide a valid Windows username and password to access the database server. In enterprise environments, these credentials are normally Active Directory domain credentials.

Mixed authentication mode allows the use of Windows credentials but supplements them with local SQL Server user accounts that the administrator may create and maintain within SQL Server

4) Being a DBA which authentication mode you will prefer if you are asked to give an advice for a new Application?
Windows authentication is definitely more secure as it’s controlled and authenticated by Active Directory policies.

5) What are Principals?
Principals are entities that can request SQL Server resources. A Windows Login is an example of an indivisible principal, and a Windows Group is an example of a principal that is a collection. Every principal has a security identifier (SID). e.g.

Windows-level principals
• Windows Domain Login
• Windows Local Login

SQL Server-level principals
• SQL Server Login
• Server Role

Database-level principals
• Database User
• Database Role
• Application Role

6) What is a Securable?
Securables are the resources to which the SQL Server Database Engine authorization system regulates access. For example, a table is a securable. Some securables can be contained within others, creating nested hierarchies called “scopes” that can themselves be secured. The securable scopes are server, database, and schema.

7) Explain scope of securable on Server, Database and Schema level?
Securable scope: Server —The server securable scope contains the following securables:
• Endpoint
• Login
• Server role
• Database

Securable scope: Database —The database securable scope contains the following securables:
• User
• Database role
• Application role
• Assembly
• Message type
• Route
• Service
• Remote Service Binding
• Full text catalog
• Certificate
• Asymmetric key
• Symmetric key
• Contract
• Schema

Securable scope: Schema —The schema securable scope contains the following securables:
• Type
• XML schema collection
• Object – The object class has the following members:
o Aggregate
o Function
o Procedure
o Queue
o Synonym
o Table
o View

8) What are logins and users and its difference?
A login is the principal that is used to connect to the SQL Server instance. A user is the principal that is used to connect to a database.

The security context on the instance itself is dictated by the login, it’s roles and the permissions granted/denied. The security context on the database is dictated by the user, it’s roles and the permissions granted/denied.

9) What is a schema?
SQL Server 2005 introduced the concept of database schemas and the separation between database objects and ownership by users. An object owned by a database user is no longer tied to that user. The object now belongs to a schema – a container that can hold many database objects. schema as a collection of database objects that are owned by a single principal and form a single namespace

10) What are Fixed Server roles and importance?
1 Bulk Admin: Members of this role can perform Bulk Insert operations on all the databases.
2 DBCreator: Members of this role can Create/Alter/Drop/Restore a database.
3 Disk Admin: Members can manage disk files for the server and all databases. They can handle backup devices.

4 Process Admin: Members of this role can manage and terminate the processes on the SQL Server.
5 Server Admin: Members of this role can change Server-wide configurations and shutdown SQL Server instance.
6 Setup Admin: Members of this role can Add/Remove Linked Servers.
7 Security Admin: Members of this role can create/manage Logins, including changing and resetting passwords as needed, and managing GRANT, REVOKE and DENY permissions at the server and database levels.
8 SysAdmin: Members of this role have Full Control on the instance and can perform any task.
9 Public: Public is another role just like Fixed Server Roles, that is by default granted to every login (Windows/SQL)

11) What are “View Server State”,”VIEW DATABASE STATE” permissions meant for?
Dynamic management views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems, and tune performance.
There are two types of dynamic management views and functions:

Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.

Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

12) What are “View Definition” permissions?
The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.

The VIEW DEFINITION permission can be granted on the following levels:

• Server scope
• Database scope
• Schema scope
• Individual entities

13) What is a guest account?
Guest user permits access to a database for any logins that are not mapped to a specific database user. The guest user cannot be dropped but it can be disabled by revoking the CONNECT permission. The recommendation is not valid for master, msdb and tempdb system databases. If Guest user is disabled in msdb system database, it may cause some issues. Distribution database is also system database and more information about the Guest User in distribution database can be found below. It is recommended to disable guest user in every database as a best practice for securing the SQL Server.

14) Is it possible to create new User Defined Server role in 2012 or not?
Yes, it is possible to create a Server role in SQL Server 2012.

15) What are the security related catalog views?
Server-Level Views

• sys.server_permissions
• sys.sql_logins
• sys.server_principals
• sys.server_role_members
• Database-Level Views
• sys.database_permissions
• sys.database_role_members
• sys.database_principals

16) What are the extra DB roles available in msdb?
SQL Server 2005 introduced the following msdb database fixed database roles, which give administrators finer control over access to SQL Server Agent. The roles listed from least to most privileged access are:

• SQLAgentUserRole
• SQLAgentReaderRole
• SQLAgentOperatorRole

17) Which one is highest privileged role out of SQLAgentUserRole, SQLAgentReaderRole, SQLAgentOperatorRole?
SQLAgentOperatorRole is the most privileged of the SQL Server Agent fixed database roles. It includes all the permissions of SQLAgentUserRole andSQLAgentReaderRole. Members of this role can also view properties for operators and proxies, and enumerate available proxies and alerts on the serve.

18) What are Fixed Database Roles?

1 db_datareader: The db_datareader role has the ability to run a SELECT statement against any table or view in the database.

2 db_datawriter: The db_datawriter role has the ability to modify via INSERT, UPDATE, or DELETE data in any table or view in the database.

3 db_denydatareader: The db_denydatareader role is the exact opposite of the db_datareader role: instead of granting SELECT permissions on any database object, the db_denydatareader denies SELECT permissions.

4 db_denydatawriter: db_denydatawriter role serves to restrict permissions on a given database. With this role, the user is preventing from modifying the data on any data via an INSERT, UPDATE, or DELETE statement

5 db_accessadmin: The db_accessadmin fixed database role is akin to the securityadmin fixed server role: it has the ability to add and remove users to the database.

The db_accessadmin role does not, however, have the ability to create or remove database roles, nor does it have the ability to manage permissions.

Granted with GRANT option: CONNECT

6.db_securityadmin: The db_securityadmin role has rights to handle all permissions within a database. The full list is:

DENY, GRANT, REVOKE, sp_addapprole, sp_addgroup, sp_addrole, sp_addrolemember, sp_approlepassword, sp_changegroup, sp_changeobjectowner, sp_dropapprole, sp_dropgroup, sp_droprole, sp_droprolemember

The list includes the DENY, GRANT, and REVOKE commands along with all the store procedures for managing roles.

7. db_ddladmin: A user with the db_ddladmin fixed database role has rights to issue Data Definition Language (DDL) statements in order to CREATE, DROP, or ALTER objects in the database.

8. db_backupoperator: db_backupoperator has rights to create backups of a database. Restore permissions are not granted, but only backups can be performed.

9. db_owner: Equal to a sysadmin at instance level, DB_OWNER can perform any task at DB Level.

9. public: By default all the users in database level are granted Public Role.

19) What is the purpose of db_denydatawriter and db_denydatareader?
The deny roles are an extra safeguard that you can use to make sure that certain logins or groups will never have the type of access that is specifically denied under the role. With nesting of groups in Windows and multiple role assignments sometimes individuals inadvertently end up with excessive permissions. This is just another level that can be applied to lessen these accidental cracks in the permissions hierarchy.

20) What are Application Roles?
An application role is a database principal that enables an application to run with its own, user-like permissions. You can use application roles to enable access to specific data to only those users who connect through a particular application. Unlike database roles, application roles contain no members and are inactive by default. Application roles work with both authentication modes. Application roles are enabled by using sp_setapprole, which requires a password. Because application roles are a database-level principal, they can access other databases only through permissions granted in those databases to guest. Therefore, any database in which guest has been disabled will be inaccessible to application roles in other databases.

21) What are Orphaned Users?
A database user for which the corresponding SQL Server login is undefined or is incorrectly defined on a server instance cannot log in to the instance. Such a user is said to be an orphaned user of the database on that server instance.

• A database user can become orphaned if the corresponding SQL Server login is dropped.
• A database user can become orphaned after a database is restored or attached to a different instance of SQL Server.
• Orphaning can happen if the database user is mapped to a SID that is not present in the new server instance.

22) How to troubleshoot issues with the Orphaned users?
This will lists the orphaned users:
 EXEC sp_change_users_login 'Report'
If you already have a login id and password for this user, fix it by doing:
 EXEC sp_change_users_login 'Auto_Fix', 'user'
If you want to create a new login id and password for this user, fix it by doing:
 EXEC sp_change_users_login 'Auto_Fix', 'user', 'login', 'password'
23) How can SQL Server instances be hidden?
To hide an instance of the SQL Server Database Engine
1. In SQL Server Configuration Manager, expand SQL Server Network Configuration, right-click Protocols for , and then select Properties.
2. On the Flags tab, in the HideInstance box, select Yes, and then click OK to close the dialog box. The change takes effect immediately for new connections.

24) Being a DBA what all measures you will follow to make SQL SERVER more secure?

• When possible, use Windows Authentication logins instead of SQL Server logins
• Using server, database and application roles to control access to the data
• Using an un guessable SA password
• If possible, disable and rename the sa account
• Restricting physical access to the SQL Server
• Disabling the Guest account
• Minimize the number of sysadmins allowed to access SQL Server.
• Give users the least amount of permissions they need to perform their job.
• Use stored procedures or views to allow users to access data instead of letting them directly access tables.
• Don’t grant permissions to the public database role.
• Remove user login IDs who no longer need access to SQL Server.
• Avoid creating network shares on any SQL Server.
• Turn on login auditing so you can see who has succeeded, and failed, to login.
• (select * from sys.fn_get_audit_file ('E:\backup\*.sqlaudit', default, default)
• Ensure that your SQL Servers are behind a firewall and are not exposed directly to the Internet.
• Do not use DBO users as application logins
• Firewall restrictions ensure that only the SQL Server listening port is available on the database server.
• Apply the latest security updates / patches

25) What is Transparent Data Encryption?
Transparent Data Encryption (TDE) is a feature introduced in SQL Server 2008 and available in later versions for bulk encryption at the database file level (data file, log file and backup file) i.e. the entire database at rest. Once enabled for a database, this feature encrypts data into pages before it is written to the disk and decrypts when read from the disk. The best part of this feature is, as its name implies, it’s completely transparent to your application. This means literally no application code changes (only administrative change to enable it for a database) are required and hence no impact on the application code\functionalities when enabling TDE on a database being referenced by that application.

26) What is Service master key?
The Service Master Key is the root of the SQL Server encryption hierarchy. It is generated automatically the first time it is needed to encrypt another key. By default, the Service Master Key is encrypted using the Windows data protection API and using the local machine key. The Service Master Key can only be opened by the Windows service account under which it was created or by a principal with access to both the service account name and its password.

27) What are the types of keys used in encryption?
Symmetric Key – In Symmetric cryptography system, the sender and the receiver of a message share a single, common key that is used to encrypt and decrypt the message. This is relatively easy to implement, and both the sender and the receiver can encrypt or decrypt the messages.

Asymmetric Key – Asymmetric cryptography, also known as Public-key cryptography, is a system in which the sender and the receiver of a message have a pair of cryptographic keys – a public key and a private key – to encrypt and decrypt the message. This is a relatively complex system where the sender can use his key to encrypt the message but he cannot decrypt it. The receiver, on the other hand, can use his key to decrypt the message but he cannot encrypt it. This intricacy has turned it into a resource-intensive process.

28) How to take backup of the Service master key?
 BACKUP SERVICE MASTER KEY TO FILE = 'path_to_file'
 ENCRYPTION BY PASSWORD = 'password'

29) Is it possible to disable SA, how?
Disable the SA Login
Disabling the SA account is a good option to prevent its use. When it is disabled no one can use it in any circumstance until it is enabled. The only disadvantage is that we can’t use the SA account in an emergency. we can use the below T-SQL to disable SA account.
–Query to disable the SA account.
 ALTER LOGIN sa DISABLE;

30) Is it possible to Rename the SA Login
Yes we can rename the SA account which will prevent hackers/users to some extent.
–Query to check account status
 ALTER LOGIN sa WITH NAME = [newname];

31) Define SQL Server Surface Area Configuration Tool
SQL Server 2005 contains configuration tools such as a system stored procedure calledsp_configure or SQL Server Surface Area Configuration tool (for services and features) in order to enable/disable optional features as needed. Those features are usually installed as disabled by default. Here is the list of the features that can be enabled using the tool:

• xp_cmdshell
• SQL Server Web Assistant
• CLR Integration
• Ad hoc remote queries (the OPENROWSET and OPENDATASOURCE functions)
• OLE Automation system procedures
• procedures for Database Mail and SQL Mail
• Remote use of a dedicated administrator connection

SQL Server DBA Interview Questions and Answers – SQL Server DBA Tools and Executables

Hi friend

I posted SQL Server DBA Tools and Executables related question and answer for interview.

1) What is SSMS?
SSMS stands for SQL Server Management Studio. SQL Server Management Studio (SSMS) is an integrated environment for accessing, configuring, managing, administering, and developing all components of SQL Server. SSMS combines a broad group of graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators of all skill levels. SSMS combines the features of Enterprise Manager, Query Analyzer, and Analysis Manager, included in previous releases of SQL Server, into a single environment. In addition, SSMS works with all components of SQL Server such as Reporting Services and Integration Services.

2) What is SQL Server profiler?
Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. We can capture and save data about each event to a file or table to analyze later. For example, we can monitor a SQL Server database to see which stored procedures are affecting performance by executing too slowly. SQL Server Profiler is used for activities such as:

• Stepping through problem queries to find the cause of the problem.
• Finding and diagnosing slow-running queries.
• Capturing the series of Transact-SQL statements that lead to a problem. The saved trace can then be used to replicate the problem on a test server where the problem can be diagnosed.
• Monitoring the performance of SQL Server to tune workloads using Database Engine Tuning Advisor.

3) What is Database Engine Tuning Advisor?
SQL Server includes another performance tool called the Database Engine Tuning Advisor or DTA.  This tool allows you to have SQL Server analyze one statement or a batch of statements that you captured by running a Profiler or server side trace.  The tool will then go through each statement to determine where improvements can be made and then presents you with options for improvement. The Database Engine Tuning Advisor is basically a tool that helps you figure out if additional indexes are helpful as well as partitioning.  Here is a summary of the options:

• Adding indexes (clustered, non-clustered, and indexed views)
• Adding partitioning
• Adding statistics including multi-column statistics which are not created automatically even when you have the AUTO_CREATE_STATISTICS database option set to ON

4) What is SQL Server Configuration Manager and its usages?
SQL Server Configuration Manager is a tool to manage the services associated with SQL Server, to configure the network protocols used by SQL Server, and to manage the network connectivity configuration from SQL Server client computers.
SQLServerManager10.msc

5) What is Reporting Services Configuration manager?
Reporting Services Configuration Manager is used to configure a Reporting Services Native Mode installation. If we installed a report server by using the files-only installation option, we must use this tool to configure the server before we can use it. If we installed a report server by using the default configuration installation option, we can use this tool to verify or modify the settings that were specified during setup. Reporting Services Configuration Manager can be used to configure a local or remote report server instance.

6) What is Resource Governor?
SQL Server provides Resource Governor, a feature than we can use to manage SQL Server workload and system resource consumption. Resource Governor enables us to specify limits on the amount of CPU and memory that incoming application requests can use.Resource Governor enables us to manage SQL Server workloads and resources by specifying limits on resource consumption by incoming requests.

7) What is Replication Monitor?
Microsoft SQL Server Replication Monitor is a graphical tool that allows us to monitor the overall health of a replication topology. Replication Monitor provides detailed information on the status and performance of publications and subscriptions, allowing us to answer common questions, such as:

• Is my replication system healthy?
• Which subscriptions are slow?
• How far behind is my transactional subscription?
• How long will it take a transaction committed now to reach a Subscriber in transactional replication?
• Why is my merge subscription slow?
• Why is an agent not running?

8) What is Always on DashBoard?
Database administrators use the AlwaysOn Dashboard to obtains an at-a-glance view the health of an AlwaysOn availability group and its availability replicas and databases in SQL Server 2012. Some of the typical uses for the AlwaysOn Dashboard are:

• Choosing a replica for a manual failover.
• Estimating data loss if you force failover.
• Evaluating data-synchronization performance.
• Evaluating the performance impact of a synchronous-commit secondary replica

9) What is SQL Server upgrade advisor?
SQL Server Upgrade Advisor helps you prepare for upgrades to SQL Server 2012. Upgrade Advisor analyzes installed components from earlier versions of SQL Server, and then generates a report that identifies issues to fix either before or after you upgrade.

10) What is Activity Monitor in SQL Server 2012?
SQL Server Activity Monitor is a feature in SQL Server Management Studio that displays information about the SQL Server processes and their effect on SQL Server performance Activity Monitor consists of several panes – Overview, Processes, Resource Waits, Data File I/O, and Recent Expensive Queries. Panes can be expanded and collapsed. The activities are queried only when the specific pane is expanded

11) What is SQLCMD?
sqlcmd utility in SQL Server is a command-line tool that lets us submit T-SQL statements or batches to local and remote instances of SQL Server. The utility is extremely useful for repetitive database tasks such as batch processing or unit testing. It also provides an easy way of simulating load to a database under development.

12) What is OSQL?
osql utility allows us to enter Transact-SQL statements, system procedures, and script files. This utility uses ODBC to communicate with the server.

13) What is bcp?
bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files. Except when used with the queryout option, the utility requires no knowledge of Transact-SQL. To import data into a table, you must either use a format file created for that table or understand the structure of the table and the types of data that are valid for its columns.

14) What is SQLPS module and what are its usages?
By importing the SQLPS module into a Windows PowerShell 2.0 environment, we can work with SQL Server from Powersehll. The module loads and registers the SQL Server snap-ins and manageability assemblies.
After importing the sqlps module into Windows PowerShell, we can then:

• Interactively run Windows PowerShell commands.
• Run Windows PowerShell script files.
• Run SQL Server cmdlets.
• Use the SQL Server provider paths to navigate through the hierarchy of SQL Server objects.
• Use the SQL Server manageability object models (such as Microsoft.SqlServer.Management.Smo) to manage SQL Server objects.

15) What is Data Quality Client?
Data Quality Client application enables you to perform data quality operations using a standalone tool. This application enables you to create knowledge bases, create and run data quality projects, and perform administrative tasks.

16) What are Extended events in SQL Server 2012?
Extended Events has a highly scalable and highly configurable architecture that allows users to collect as much or as little information as is necessary to troubleshoot or identify a performance problem.

17) What is SQLDiag?
SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft Customer Support Services.
SQLdiag can collect the following types of diagnostic information:

• Windows performance logs
• Windows event logs
• SQL Server Profiler traces
• SQL Server blocking information
• SQL Server configuration information

18) What is PSSDIAg Manager Utility?
PSSDIAG is a general purpose diagnostic collection utility that Microsoft Product Support Services uses to collect various logs and data files. PSSDIAG can natively collect Performance Monitor logs, SQL Profiler traces, SQL Server blocking script output, Windows Event Logs, and SQLDIAG output. The data collection can be customized by enabling or disabling any of these log types, by changing the sample interval of the blocking script and the Performance Monitor logs, and by modifying the specific events and counters for SQL Profiler and Performance Monitor to capture.

19) What are Database Maintenance Plans?
Database Maintenance Plans allow us to automate many database administration tasks in Microsoft SQL Server. We can create maintenance plans using an easy wizard-based process without any knowledge of Transact-SQL.
We may perform the following tasks within a database maintenance plan:

• Shrinking a database
• Backing up a database
• Performing an operator notification
• Updating database statistics
• Verifying the integrity of a database
• Cleaning up leftover maintenance files
• Executing a SQL Server Agent job
• Executing a Transact-SQL statement
• Rebuilding an index
• Reorganizing an index
• Cleaning up database histories

20) What is SQL Nexus Tool?
SQL Nexus is a tool that helps you identify the root cause of SQL Server performance issues. It loads and analyzes performance data collected by SQLDiag and PSSDiag. It can dramatically reduce the amount of time you spend manually analyzing data.

21) What is tablediff.exe? What are the usages?
The tablediff utility is used to compare the data in two tables for non-convergence, and is particularly useful for troubleshooting non-convergence in a replication topology. This utility can be used from the command prompt or in a batch file to perform the following tasks:

• A row by row comparison between a source table in an instance of Microsoft SQL Server acting as a replication Publisher and the destination table at one or more instances of SQL Server acting as replication Subscribers.
• Perform a fast comparison by only comparing row counts and schema.
• Perform column-level comparisons.
• Generate a Transact-SQL script to fix discrepancies at the destination server to bring the source and destination tables into convergence.
• Log results to an output file or into a table in the destination database.

22) What is Distrib.exe?
Replication Distribution Agent (Distrib.exe) is an executable that moves the snapshot (for snapshot replication and transactional replication) and the transactions held in the distribution database tables (for transactional replication) to the destination tables at the Subscribers.

23) What is logread.exe?
The Replication Log Reader Agent is an executable that monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database.

24) What is snapshot.exe?
Replication Snapshot Agent is an executable file that prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

25) What is DTExec.exe?
dtexec command prompt utility is used to configure and execute SQL Server Integration Services packages.

26) What is DTutil.exe?
The dtutil command prompt utility is used to manage SQL Server Integration Services packages. The utility can copy, move, delete, or verify the existence of a package.

27) What is Data Profile Viewer?
Data profiles can be Viewed and analyzed in the data profiling process using Data Profile Viewer. We can view these profiles after we have run the Data Profiling task inside an Integration Services package and computed the data profiles.

28) What is Project Conversion Wizard?
Integration Services Project Conversion Wizard converts a complete project to the project deployment model.

29) What is Policy Management Tool?
Policy Management tool is used to create policies to manage entities on the server, such as the instance of SQL Server, databases, or other SQL Server objects. Policy-based management in SQL Server assists the database administrators in defining and enforcing policies that tie to database objects and instances. These policies allow the administrator to configure and manage SQL server across the enterprise.

30) What is Master Data Services Configuration?
Master Data Services Configuration Manager is the tool that we use to create or configure a Master Data Services database. After we create a database, we can use Master Data Services Configuration Manager to create a web application and to enable integration with Data Quality Services. Finally, we can use Master Data Services Configuration Manager to associate an MDS database with an MDS web application.

31) What is the use of setup.exe under the below folder
C:\Program Files\Microsoft SQL Server\100\Setup BootStrap\Release
Before SQL Server 2008 if there was a requirement to rebuild the master databases then its required SQL Server complete Setup. But starting SQL Server 2008 we don’t need SQL server setup anymore, system databases can be rebuild with the setup.exe located in the above location.