Microsoft 70-765 Exam Practice Test Instant Access

Question 1

You have Microsoft SQL Server on a Microsoft Azure virtual machine. You discover that a database has 1.000 corrupt pages. You need to repair the corrupt pages. Which statement should you execute?

ADBCC CHECKALLOC (REPAIR_REBUILD)

BDBCC CHECKDB WITH DATA_PURITY

CDBCC CHECKDB (REPAIR_ALLOW_DATA_LOSS)

DDBCC CHECKALLOC (REPAIR_FAST)

Answer : C

References:

https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15

Question 2

You administer two instances of Microsoft SQL Server 2014. You deploy an application that uses a database on the named instance.

The application is unable to connect to the database on the named instance.

You need to ensure that the application can connect to the named instance.

What should you do?

AConfigure the application as data-tiered.

BOpen port 1433 on the Windows firewall on the server.

CConfigure the named SQL Server instance to use an account that is a member of the Domain Admins group.

DStart the SQL Server Browser Service.

Answer : D

The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:

References:

https://technet.microsoft.com/en-us/library/ms181087(v=sql.105).aspx

Question 3

You administer a Microsoft SQL Server 2014 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerId value set to 1 in the following XML format.

Which Transact-SQL query should you use?

ASELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW

BSELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS

CSELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO

DSELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS

ESELECT Name, Country, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO

FSELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS

GSELECT Name AS `@Name', Country AS `@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH (`Customers')

HSELECT Name AS `Customers/Name', CountryAS `Customers/Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML PATH (`Customers'

Answer : G

Question 4

You have a server named server1-contoso.database.windows.net that has a Microsoft Azure SQL database.

You need to create a group of Azure SQL databases that share the resources on server1-contoso.database.windows.net.

Which cmdlet should you run before you create the database?

ANew-AzureRmAvailabilitySet

BNew-AzureRmLoadBalancer

CNew-AzureRmSqlDatabaseSecondary

DNew-AzureRmSqlElasticPool

ENew-AzureRmVM

FNew-AzureRmSqlServer

GNew-AzureRmSqlDatabaseCopy

HNew-AzureRmSqlServerCommunicationLink

Answer : D

The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database.

SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

Question 5

You administer a Microsoft SQL Server 2014 database.

The database contains a Product table created by using the following definition:

You need to ensure that the minimum amount of disk space is used to store the data in the Product table.

What should you do?

AConvert all indexes to Column Store indexes.

BImplement Unicode Compression.

CImplement row-level compression.

DImplement page-level compression.

Answer : D

Question 6

You plan to create an AlwaysOn availability group that will have two replicas in Microsoft Azure and two on premises replicas.

You need to configure the network to support the availability group listener.

Which cmdlet should you run first?

ANew-AzureRmAvailabilitySet

BNew-AzureRmLoadBalancer

CNew-AzureRmSqlDatabaseSecondary

DNew-AzureRmSqlElasticPool

ENew-AzureRmVM

FNew-AzureRmSqlServer

GNew-AzureRmSqlDatabaseCopy

HNew-AzureRmSqlServerCommunicationLink

Answer : B

An availability group listener is a virtual network name that clients connect to for database access. On Azure virtual machines, a load balancer holds the IP address for the listener. The load balancer routes traffic to the instance of SQL Server that is listening on the probe port. Usually, an availability group uses an internal load balancer.

References:

https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windowsportal-sql-ps-alwayson-int-listener

Question 7

A Microsoft SQL Server instance has a database named Employees. The database contains information about employees including their salary.

You must create a custom system message as an alert when an error is raised.

You need to create the custom message in the sys.messages table.

Which argument insp_addmessage should you configure?

A@1ang

B@with_log

C@replace

D@severity

Answer : D

The following example adds a custom message to sys.messages.

USE master;

EXEC sp_addmessage 50001, 16,

N'Percentage expects a value between 20 and 100.

Please reexecute with a more appropriate value.';

Syntax:

sp_addmessage [ @msgnum= ] msg_id , [ @severity= ] severity , [ @msgtext= ] 'msg'

[ , [ @lang= ] 'language' ]

[ , [ @with_log= ] { 'TRUE' | 'FALSE' } ]

[ , [ @replace= ] 'replace' ]

References:

https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-addmessage-transact-sql?view=sql-server-2017

Microsoft Provisioning SQL Databases 70-765 Exam Practice Test