Certification Exam Prep Questions For
Administering a Microsoft SQL Server Database Infrastructure (MS-20764)


QuickStart is now offering assessment questions for Administering a Microsoft SQL Server Database Infrastructure (MS-20764). Whether you are deciding which exam to sign up for, or simply want to practice the materials necessary to complete certification for this course, we have provided a practice assessment to better aid in certification. 100% of the questions are real questions; from a recent version of the test you will take for Administering a Microsoft SQL Server Database Infrastructure (MS-20764).


Arrow
 
Module 1
Module 1

SQL Server Security

1

Which of the following statements Authentication in SQL Server are not correct. Choose all that applies:

2

Logins cannot access databases if they have not been granted access. To grant access to a principal, you create a user. Following are some Create User statements, in which you have to choose which one is correct. You can also choose none or more than one.

3-A

You are configuring Linked Servers, and you already know that distributed queries reach the correct data source and return the data, the deployment of the linked server require building of three Tiers. Which one of the following is not a tier in Linked Server Configuration?

A. Client Tier
Incorrect.
B. Database Server Tier
Incorrect.
C. Server Tier
Incorrect.
D. None of the above
Correct!
3-B

These are actual data sources and you can use multiple SQL Server databases for your distributed query. Which of the following Tier describes the above-mentioned statement?

A. Client Tier
Incorrect.
B. Database Server Tier
Correct!
C. Server Tier
Incorrect.
D. None of the above
Incorrect.


Back To Top
 
 
Module 2
Module 2

Assigning Server and Database Roles

1

Permission at the server level generally relate to administrative actions, such as:

A. Creating Databases
Incorrect.
B. Altering logins
Incorrect.
C. Shutting down the server
Incorrect.
D. All the above
Correct!
2

You can add and remove database principals from database roles using the Alter Role command. Which of the following statements about Assigning Database Principals to Database Roles are correct. Choose All that are correct.

3

You have been given a task by your manager to assign roles based on Finance department’s request. Their main requirement is to restrict a group of users to Select Permissions on a table or Execute permissions on a stored procedure on Database Level. Also, note that accounts are both application and service based. Which of the following T-SQL statements can be used to achieve this objective?



Back To Top
 
 
Module 3
Module 3

Authorizing Users to Access Resources

1

Which of the following is true about Column Level Security:

2

Which of the following permission enables a user to change the definition of a stored procedure?

A. Change
Incorrect.
B. Change Definition
Incorrect.
C. Alter
Correct!
D. Alter Definition
Incorrect.
3

Choose all the options below which describes User-Schema Separation:



Back To Top
 
 
Module 3
Module 4

Protecting Data with Encryption and Auditing

1

Triggers are a form of stored procedure that is triggered automatically in response to an event .There are three forms of Triggers in SQL Server. Which of the following Trigger/s is/are not supported in Azure SQL database?

2

SQL Server Supports 3 forms of triggers DDL,DML and Logon Triggers. But, they have some limitations . Choose all the statements below which are not true about Trigger limitations:

3

Given below is a statement to create a temporal table with System Versioning feature on: CREATE TABLE db. Employee
(
EmployeeID int NOT NULL PRIMARY KEY CLUSTERED,
ManagerID int NULL,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL,
SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL,
PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.EmployeeHistory));
GO
But, Temporal tables have some limitations. Choose all the answers below which does not fall under the limitations of Temporal Table:



Back To Top
 
 
Module 2
Module 5

Recovery Models and Backup Strategies

1

You are considering to Backup your on-premises SQL Server database to Microsoft Azure Blob storage. But, only you are aware of its benefits and not your Project Manager. Your Project Manager wants to know the benefits of it. Consider the following statements below and choose all those options which you think are the Benefit/s of Microsoft Azure Blob storage:

2

Which of the following options are true about SQL Server Transaction Logs. Mark all that are correct:

3

SQL Server has three database recovery models : Simple , Full and Bulk Logged. There are four options given below regarding Bulk Logged . You have to select all answers which you think is true about Bulk Logged:



Back To Top
 
 
Module 2
Module 6

Backing Up SQL Server Databases

1

Being the database administrator of your organization you want to perform Backup of your database. You have chosen Full Database Backup but also know about Differential Backup and when to use it . Below mentioned are two statements one for each : Full Database Backup and Differential Backup. Choose the correct option through which you can do the Full Backup of your database:

2

Which of the following statements about Transaction Log Backups are correct?

3

SQL Server tracks all of the backup activity in the following tables in the msdb database Mark all which are correct:

A. Backup File
Incorrect.
B. Backup File Group
Incorrect.
C. Backup Set
Correct!
D. All the above.
Correct!


Back To Top
 
 
Module 2
Module 7

Restoring SQL Server 2016 Database

1

The restore process of a SQL Server database consist of how many phases ?

A. 3
Correct!
B. 4
Incorrect.
C. 2
Incorrect.
D. None of the above
Incorrect.
2

Among the 3 categories of Restoring databases namely : Complete database(Simple and Full), System database restore and Advanced restores. Given below are some options in which advance restore works best .Choose all that are correct:

A. Page restores
Incorrect.
B. File or Filegroup restore
Correct!
C. Encrypted backup restores
Correct!
D. All the above
Correct!
3

Following are the phases of restore process ,mark all or none which are correct:

A.
Incorrect.
B. Undo
Incorrect.
C. Copy
Correct!
D. All the above.
Correct!


Back To Top
 
 
Module 2
Module 8

Automating SQL Server Management

1

As a database administrator you have certain tasks that should be performed regularly in terms of Automating SQL Server . Given below are some technologies through which you can automate regular tasks.
• SQL Server Agent
• Maintenance Plans
• PowerShell
• System Center Operations Manager
Which of the following statements about SQL Server Agent is true?

2

There are four core object types provided by SQL Server Agent. From the options below choose all that are correct

A. Operators
Incorrect.
B. Jobs
Correct!
C. Schedules
Correct!
D. All the above
Correct!
3

Part1: Sometimes Job fails and does not execute as expected. It is important to follow a consistent process when attempting to work out why job is failing. How many Steps are there for troubleshooting jobs?

A. 5
Incorrect.
B. 2
Incorrect.
C. 3
Correct!
D. 4
Correct!

Part2: Which of the following is not a part of Job troubleshooting process ?

A. SQL Server Agent Status
Incorrect.
B. Access to Dependencies
Incorrect.
C. Job Execution
Correct!
D. None of the above
Correct!


Back To Top
 
 
Module 2
Module 9

Configuring Security for SQL Server Agent

1

What happens to a credential when the password of the windows user that the credential references is changed?

2

Credentials can be created by using the Transact-SQL CREATE statement, or by using SQL Server management Studio. Below mentioned are some steps to configure it. Choose the one which is incorrect:

3

There are three types of SQL Server Agent Roles namely : SQLAgentUserRole, SQLAgentReaderRole and SQLAgentOperatorRole. Which of the following statements are true about SQLAgentOperatorRole?



Back To Top
 
 
Module 2
Module 10

Monitoring SQL Server with Alerts and Notifications

1

Part1: How many types of Error Severity levels are there in SQL Server?

A. 5
Incorrect.
B. 3
Incorrect.
C. 1
Correct!
D. 2
Incorrect.

Part2: The severity of an error indicates the type of problem that SQL Server encounters. Which of the following is correct in both description as well as Error Ranges?

2

Choose all the statements below which are correct regarding Database Mail Profiles. (Database Mail Profile is a collection of one or more database mail accounts.

3

There are 4 types of Alerts in Azure SQL Database. Choose all the correct options below which are correct:



Back To Top
 
 
Module 2
Module 11

Introduction to managing SQL Server using PowerShell

1

Which of the following statements about PowerShell is/are not correct?

2

You can change SQL Server Database settings by assigning new values to the properties of objects. Which of the following options below is correct regarding amending SQL Server Database Settings:

3

What is an SMO Object?



Back To Top
 
 
Module 2
Module 12

Tracing Access to SQL Server with Extended Events

1

There are total 3 types in the Architecture of Extended Events. Which of the following is not part of the architecture?

2

Packages acts as containers for the Extended Events objects and their definitions. For which of the following object type/s does a Package not act as a container?

A. Maps
Incorrect.
B. Targets
Incorrect.
C. Actions
Incorrect.
D. None of the above
Correct!
3

Which system Dynamic Management View provides the list of events configured in an active Extended Event session?



Back To Top
 
 
Module 2
Module 13

Monitoring SQL Server

1

The component/s that make up the data collection system in SQL Server is/are:

2

Data Collector is a toolkit for collecting SQL Server performance information into a single location which includes:

3

Fixed Roles for data collection security are created in the msdb system database. Which of the following is not a fixed role in Data Collection Security?

A. Dc_admin
Incorrect.
B. Dc_operator
Incorrect.
C. Dc_proxy
Incorrect.
D. None of the above
Correct!


Back To Top
 
 
Module 2
Module 14

Troubleshooting SQL Server

1

A key characteristic of good troubleshooters is that they follow a clear methodology in a logical manner. There are many different methodologies, with 4 most common phases namely : Investigate, Validate, Analyze and Implement. From the options below what should be the logical order of these phases?

2

When troubleshooting Service Related issues, the most common one is SQL Server service not starting or cannot be accessed. If it does not start from the options below which options should be selected to resolve the issue?

3

Choose all the answers below which are correct about SQL Server Error Log:



Back To Top
 
 
Module 2
Module 15

Importing and Exporting Data

1

You want to import data from a text file into a SQL Server table, which of the following data transfer tools you can use to accomplish this task?

A. SQL SERVER Integration Services
Incorrect.
B. BCP(Bulk Copy Program)
Incorrect.
C. BULK INSERT
Incorrect.
D. Any of the above
Correct!
2

SSIS is an extensible platform for building complex ETL solutions. It is included with SQL Server and consists of a Microsoft Windows service that manages the execution of ETL workflows, along with tools and components for developing them. In SSIS a service called SSIS Service is used to perform management of control flow , execution of task workflows, which are defined in the packages. Which of the following statements below are false about SSIS Service?

3

A DAC is a logical collection of all the database objects—such as tables, stored procedures, logins, and users—associated with a SQL Server user database. Definitions for all the objects in a DAC can be combined into a deployment package file, called a DAC package (DACPAC), which can be used to install or upgrade a DAC across multiple instances of the database engine. Which of the following operations can you perform on a DAC?

A. Upgrade
Incorrect.
B. Register
Incorrect.
C. Deploy
Incorrect.
D. all of the above
Correct!


Back To Top
 
PDP Url

Practice Exam - Administering a Microsoft SQL Server Database

Self-Paced

Learning Style

Beginner

Difficulty

1 Hour

Course Duration

Course Info

Download PDF

Certificate

See Sample

Buy Individually
About Individual Course:
  • Individual course plan gives you access to this course
$0.00
$0.00
/ Each

More Information

More Information
Lab Access No
Learning Style Self-Paced Learning
Difficulty Beginner
Course Duration 1 Hour
Language English

Reviews

Write Your Own Review
Only registered users can write reviews. Please Sign in or create an account

Frequently Asked Questions

FAQ's

Frequently Asked Questions About Virtual Instructor-Led Courses

I can't connect to my class, what are my options?

The link to the class is available upon logging in to your dashboard. If you are unable to see it, please contact our support team at 1-855-800-8240 and they will be happy to provide you the direct link via email or the dial in number.


I can't make it to attend to class. Can I reschedule?

Yes, you can reschedule your class. Please contact your Sales representative and they will arrange this for you. If you forgot his/her name, feel free to contact our support team at support@quickstart.com or 1-855-800-8240.


Will I get my certificate upon completion?

Yes. Upon completion of the course, it will be available on your course as a Trophy Icon for you to download. If you do not see this, you will need to contact support@quickstart.com with the following details so they can email you the certificate: Class Name, Class Date, Account Rep, and Your Email.


I cannot connect to my lab. Help!

Your Lab is accessible on the bottom part of your course. You will see a button that says "LAB". Just click it to launch the lab. Please note that some classes don’t need/require a LAB. You can verify with our support team by calling them at 1-855-800-8240 or by email at support@quickstart.com. You can also check with your Instructor or the Associate Instructor if your class includes one.


What is my access code for Skillpipe?

A. Not all of the classes have or require Skillpipe. If your class includes one, please check your email as you should have received one from noreply@skillpipe.com. In case you do not find it in your inbox, please check the Spam / Junk folder. For any further assistance, you can call the support at 1-855-800-8240 or contact them via email at support@quickstart.com.


I don't have audio. I can't hear the instructor.

Make sure you are using a compatible headset for your laptop or computer. If you don’t have a headset, you can use the built-in speaker of your laptop. Otherwise, you can use the dial in option by calling the dial in number provided in the class joining email. You may also contact support team for the dial in numbers associated for your training at 1-855-800-8240 or contact them via email at support@quickstart.com.


How can I reach student support?

Support can be reach via phone at 1855-800-8240; via email at support@quickstart.com or via chat support through the chat button on our website. Please note that support office hours will be from 8am-5pm CST Monday to Friday. Any concerns after office hours will be attended the following business day.

REQUEST MORE INFORMATION

click here