Breathing 70-765 Free Practice Questions 2021

NEW QUESTION 1
DRAG DROP
A new Azure Active Directory security principal named ReportUser@contoso.onmicrosoft.com should have access to select all current and future objects in the Reporting database. You should not grant the principal any other
permissions. You should use your Active Directory Domain Services (AD DS) account to authenticate to the Azure SQL database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1:
To provision an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database (here the Reporting database) with an Azure AD identity (not with a SQL Server account) that has access to the database.
Step 2: CREATE USER ... FROM EXTERNAL PROVIDER
To create an Azure AD-based contained database user (other than the server administrator that owns the database), connect to the database with an Azure AD identity, as a user with at least the ALTER ANY USER permission. Then use the following Transact-SQL syntax:
CREATE USER <Azure_AD_principal_name> FROM EXTERNAL PROVIDER;
Step 3:
Grant the proper reading permissions.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad- authentication

NEW QUESTION 2
HOTSPOT
You need to open the firewall ports for use with SQL Server environment. In table below, identify the firewall port that you must use for each service.
NOTE: Make only one selection in each column.

Answer:

Explanation: Report Server: 80
By default, the report server listens for HTTP requests on port 80.

NEW QUESTION 3
Note: This questions is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question in the series. Each question is independent of the other questions in this series. Information and details provided in a question apply only to that question.
You deploy Microsoft SQL Server to a virtual machine in Azure. You distribute the database files and filegroups across multiple Azure storage disks.
You must be able to manage the databases as individual entities by using SQL Server Management Studio. All data in the databases must be stored encrypted. Backups must be encrypted by using the same key as the live copy of the database.
You need to secure the data. What should you implement?

• A. transport-level encryption
• B. cell-level encryption
• C. Transparent Data Encryption
• D. Always Encrypted
• E. Encrypting File System
• F. BitLocker
• G. dynamic data masking

Answer: C

Explanation: Transparent data encryption (TDE) encrypts your databases, associated backups, and transaction log files at rest without requiring changes to your applications.
TDE encrypts the storage of an entire database by using a symmetric key called the database encryption key. In SQL Database the database encryption key is protected by a built-in server certificate. The built-in server certificate is unique for each SQL Database server.
References:https://msdn.microsoft.com/en-us/library/dn948096.aspx

Topic 3, Deploy and migrate applications

NEW QUESTION 4
You plan to deploy a Microsoft SQL Server database that will use FILESTREAM. The database will store 4 TB of FILESTREAM data on a single Windows partition.
You need to configure the hard disk that will support the FILESTREAM data. The solution must provide the fastest read and write access to the data.
How should you configure the disk? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: File System: NTFS
8.3 filename support: Disabled Indexing: Disabled
NTFS is required.
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage.
Check that search indexing is not enabled on FILESTREAM volumes, under the Volume Properties window, unchecking the “Allow files on this drive to have contents indexed in addition to file properties” box.
References:
https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestreamimplementations/

NEW QUESTION 5
A company has an on-premises Microsoft SQL Server 2021 infrastructure. The storage area network (SAN) that supports the SQL infrastructure has reached maximum capacity.
You need to recommend a solution to reduce on-premises storage use without changing the application. What should you do?

• A. Configure an Express Route connection to Microsoft Azure.
• B. Configure a Microsoft Azure Key Vault.
• C. Configure geo-replication on the SAN.
• D. Configure SQL Server Stretch Database in Microsoft Azure.

Answer: D

Explanation: Stretch warm and cold transactional data dynamically from SQL Server to Microsoft Azure with SQL Server Stretch Database. Unlike typical cold data storage, your data is always online and available to query. Benefit from the low cost of Azure rather than scaling expensive, on-premises storage.
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/stretch-database?view=sql-server-2021

NEW QUESTION 6
You plan to migrate a database To Microsoft Azure SQL Database. The database requires 500 gigabytes (GB) of storage.
The database must support 50 concurrent logins. You must minimize the cost associated with hosting the database.
You need to create the database. Which pricing tier should you use?

• A. Standard S3 pricing tier
• B. Premium P2tier
• C. Standard S2 pricing tier
• D. Premium P1 tier

Answer: D

Explanation: For a database size of 500 GB the Premium tier is required. Both P1 and P2 are adequate. P1 is preferred as it is cheaper.
Note:

NEW QUESTION 7
Settings Value VM size D3
Storage Location Drive E Storage type Standard Tempdb location Drive C
The workload on this instance has of the tembdb load.
You need to maximize the performance of the tempdb database.
Solution: You use a D- Series VM and store the tempdb database on drive D. Does this meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: For D-series, Dv2-series, and G-series VMs, the temporary drive on these VMs is SSD-based. If your workload makes heavy use of TempDB (such as temporary objects or complex joins), storing TempDB on the D drive could result in higher TempDB throughput and lower TempDB latency.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 8
You have Microsoft SQL Server on a Microsoft azure virtual machine that has 12 databases. All database files are in the same Azure Blob storage account.
You need to receive an email notification if I/O operations to the database files exceed 800 MB/s for more than five minutes.
Solution: You run the Get-Counter cmdlet and specify the –counter ‘physicaldisk:disk Transfers/sec’ parameter.
Does this meet the goal?

• A. Yes
• B. No

Answer: A

NEW QUESTION 9
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You have two Windows accounts named serviceAccount1 and ServiceAccount2. The SQL Server Agent runs as ServiceAccount1.
You need to run SQL Server Agent job steps by using ServiceAccount2. Which cmdlet should you run first?

• A. Set-ADServiceAccount
• B. Set-SqlCredential
• C. New-ADServiceAccount
• D. New-SqlCredential

Answer: C

Explanation: The New-ADServiceAccount command creates a new Active Directory managed service account or group managed service account object.

NEW QUESTION 10
You have a database named DB1 that contains a table named Table1. Tabe1 has a non-clustered index named index1.
You discover that index1 is corrupt. You need to repair index1.
Which statement should you execute?

• A. DBCC CHECKDB (‘db1’, REPAIR_FAST)
• B. ALTER INDEX indx1 ON table1 REBUILD WITH (ONLINE-ON)
• C. ALTER INDEX index1 ON table1 REORGANIZE
• D. DBCC CHECKDB (‘db1’, DATA_PURITY)

Answer: B

Explanation: If REBUILD is performed online (ON) the data in this table is available for queries and data modification during the index operation.

NEW QUESTION 11
You use Microsoft SQL Server 2014 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?

• A. Reference a user-defined function within the computed column.
• B. Create a BEFORE trigger that maintains the state of the computed column.
• C. Add a default constraint to the computed column that implements hard-coded values.
• D. Add a default constraint to the computed column that implements hard-coded CASE statements.

Answer: A

Explanation: A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.
References:https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column-defined-with-a-user-define

NEW QUESTION 12
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?

• A. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers-CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW
• B. SELECT OrderId, OrderDate, Amount, Name, CountryFROM Orders INNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML RAW, ELEMENTS
• C. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML AUTO
• D. SELECT OrderId, OrderDate, Amount, Name, CountryFROM OrdersINNER JOIN CustomersON Orders.CustomerId = Customers.CustomerIdWHERE Customers.CustomerId= 1FOR XML AUTO, ELEMENTS
• E. SELECT Name, Country, OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO
• F. SELECT Name, Country, Crderld, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId= FOR XML AUTO, ELEMENTS
• G. SELECT Name AS `@Name', Country AS `@Country', OrderId, OrderDate, AmountFROM OrdersINNER JOIN CustomersON Orders.CustomerId= Customers.CustomerIdWHERE Customers.CustomerId = 1FOR XML PATH (`Customers')
• H. SELECT 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

NEW QUESTION 13
You have an on-premises SQL Server database named DB1 that contains a table named TB1. TB1 is stretched to Microsoft Azure.
A catastrophic hardware failure occurs on the on-premises SQL server.
You deploy a new on-premises server and restore all databases to the new server. You need to resume Stretch Database operations to Azure.
Which statements should you execute?

• A. Option A
• B. Option B
• C. Option C
• D. Option D

Answer: B

Explanation: Use ALTER TABLE, not CREATE TABLE.
When you enable Stretch for a table by specifying ON, you also have to specify MIGRATION_STATE = OUTBOUND to begin migrating data immediately, or MIGRATION_STATE = PAUSED to postpone data migration.
Syntax:
<stretch_configuration> ::=
{
SET ( REMOTE_DATA_ARCHIVE
{
= ON ( <table_stretch_options> )
| = OFF_WITHOUT_DATA_RECOVERY ( MIGRATION_STATE = PAUSED )
| ( <table_stretch_options> [, ...n] )
}
)
}
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2021

NEW QUESTION 14
You administer a Microsoft SQL Server 2014 instance.
The instance contains a database that supports a retail sales application. The application generates hundreds of transactions per second and is online 24 hours per day and 7 days per week.
You plan to define a backup strategy for the database. You need to ensure that the following requirements are met:
No more than 5 minutes worth of transactions are lost. Data can be recovered by using the minimum amount of administrative effort.
What should you do? Choose all that apply.

• A. Configure the database to use the SIMPLE recovery model.
• B. Create a DIFFERENTIAL database backup every 4 hours.
• C. Create a LOG backup every 5 minutes.
• D. Configure the database to use the FULL recovery model.
• E. Create a FULL database backup every 24 hours.
• F. Create a DIFFERENTIAL database backup every 24 hours.

Answer: BCDE

Explanation: The full recovery model uses log backups to prevent data loss in the broadest range of failure scenarios, and backing and restoring the transaction log (log backups) is required. The advantage of using log backups is that they let you restore a database to any point of time that is contained within a log backup (point-in-time
recovery). You can use a series of log backups to roll a database forward to any point in time that is contained in one of the log backups. Be aware that to minimize your restore time, you can supplement each full backup with a series of differential backups of the same data.
References:https://technet.microsoft.com/en-us/library/ms190217(v=sql.105).aspx

NEW QUESTION 15
DRAG DROP
You deploy a new Microsoft Azure SQL Database instance to support a variety of mobile applications and public websites. You plan to create a new security principal named User1.
The principal must have access to select all current and future objects in a database named Reporting. The activity and authentication of the database user must be limited to the Reporting database.
You need to create the new security principal.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Step 1, Step 2:
First you need to create a login for SQL Azure, it's syntax is as follows: CREATE LOGIN username WITH password='password';
This command needs to run in master db. Only afterwards can you run commands to create a user in the database.
Step 3:
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin; References:https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

NEW QUESTION 16
You are the database administrator for your company. Your company has one main office and two branch offices. You plan to create three databases named DB1, DB2, and DB3 that will be hosted on one Azure SQL Database server. You have the following requirements:
The main office must be able to connect to all three databases.
The branch offices must be able to connect to DB2 and DB3.
The branch offices must not be able to access DB1.
You need to configure transparent data encryption (TDE) for DB1. Which two actions should you perform? Each correct answer presents part of the solution.

• A. Run CREATE CERTIFICATE certl WITH Subject = TDE Cert1 on DB1.
• B. Connect to DB1.
• C. Run ALTER DATABASE DB1 SET ENCRYPTION ON;.
• D. Connect to the master database.
• E. Run CREATE MASTER KEY on the master database.

Answer: BC

Explanation: You should connect to DB1. To encrypt DB1, you connect directly to DB1. When you connect to DB1. You use your dbmanager or administrative credentials.
You should run ALTER DATABASE DB1 SET ENCRYPTION ON.
You use the ALTER DATABASE DB1 SET ENCRYPTION ON statement to encrypt the database. This is the statement that turns on TDE for Azure SQL Database.

NEW QUESTION 17
You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA. You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs
owned by UserA
To which role should you add UserA?

• A. DatabaseMailUserRole
• B. ServerGroupAdministratorGroup
• C. SQLAgentUserRole
• D. Securityadmin

Answer: C

Explanation: SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job schedules that they own. Members can create local jobs.
References:https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed-database-roles