Approved 70-473 Study Guides 2021

NEW QUESTION 1
You have four Microsoft Azure SQL databases located on the same database server. You need to configure the databases to be in an elastic database pool.
Which four 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.
NOTE: More than one order of answer choices is correct. You will receive credit for any of the correct orders you select.

Answer:

Explanation: References:
https://azure.microsoft.com/en-gb/documentation/articles/sql-database-elastic-pool-create-portal/

NEW QUESTION 2
You have a Microsoft Azure virtual machine that has Microsoft SQL Server installed. The virtual machine contains five databases. You need to identify whether the performance of queries to the databases has degraded during the last two weeks. Which SQL Server feature should you use?

• A. server-side tracing
• B. Extended Events
• C. Query Store
• D. Policy-Based Management

Answer: B

NEW QUESTION 3
Your company identifies a stored procedure that runs slowly.
You review the execution plan for the stored procedure, and you discover the properties of a Clustered Index Scan operator as shown in the exhibit. (Click the Exhibit button.)

The stored procedure contains the following code.

You need to improve the performance of the stored procedure. What should you do?

• A. Add a FORCESEEK query hint to the SELECT statement.
• B. Add a NOLOCK query hint to the SELECT statement.
• C. Convert the table to an In-Memory OLTP optimized table.
• D. Change the FileName column from varchar(512) to nvarchar(512).

Answer: D

NEW QUESTION 4
You plan to migrate an on-premises Microsoft SQL Server database to a virtual machine hosted in Microsoft Azure. You need to ensure that each user of the database has a unique login after the migration is complete. Which stored procedure should you use to export the logins?

• A. sp_change_users_login
• B. xp_logininfo
• C. sp_help_revlogin
• D. sp_helplogins

Answer: C

Explanation: References:
https://www.mssqltips.com/sqlservertip/3650/script-sql-server-logins-for-disaster-recovery/

NEW QUESTION 5
DRAG DROP
Your company’s data center contains a server named SQL1 that has Microsoft SQL Server 2021 installed. All the database files on SQL1 are stored in the default location.
You plan to migrate SQL1 to a Microsoft Azure virtual machine.
Which three actions should you perform in sequence? To answer, select the appropriate actions from the list of actions to the answer area and arrange them in the correct order.

Answer:

Explanation: Box 1. Create a VHD from the SQL Server’s C: Drive. The databases will be included on the C: drive by default.
Box 2. Use the Add-AzureVHD cmdlet to upload the VHD to Azure. Box 3. Create a virtual machine using the uploaded VHD.

NEW QUESTION 6
Your company has a data warehouse that contains a database named DB1. You plan to implement data encryption for DB1.
You need to configure DB1 to meet the following requirements:
• Prevent the system administrators of App1 from reading the Personally Identifiable Information (PII) stored in DB1.
• Ensue that all backups are Stored in an on-premises local drive for three days.
• Ensure that all backups are stored in a remote location for 10 days.
• Encrypt all backups.
All solution must support the ODBC Driver for Microsoft SQL Server 2012.
Which two solutions should you implement for DB1? Each correct answer presents part of the solution. NOTE: Each correct selection is worth one point.

• A. Encrypt within the application tier.
• B. Back up DB1 by using VSS snapshots.
• C. Implement the Always Encrypted feature.
• D. Back up DB1 to a disk and a URL by using Mirror TO.
• E. Encrypt within DB1 by using EncryptBypassword.

Answer: BC

NEW QUESTION 7
You have several web applications that are hosted in the west us region in Microsoft Azure. You discover that a web application named App4 takes longer than expected to run.
You review the Azure SQL databases in your Azure subcription and discover that all the databases are hosted in the East US regions.
You identity that App4 queries a local database as quickly as possible.
Which three actions should you perform in sequence? To answer, move the appropriate cmdlets from the list of cmdlets to the answer area and arrange them in the correct order.

Answer:

Explanation:

NEW QUESTION 8
Your company has a Microsoft Azure subscription that contains an AlwaysOn availability group. The availability group contains a single database named DB1.
You recently forced a manual failover of the availability group.
You discover that DB1 on a secondary replica has a status of Not Synchronizing. You need to ensure that DB1 synchronizes correctly.
What should you do?

• A. Issue an ALTER DATABASE DB1 HARD RESUME on the primary replica.
• B. Restart the instance that hosts the secondary replica.
• C. Restart the instance that hosts the primary replica.
• D. Issue an ALTER DATABASE DB1 HARD RESUME on the secondary replica.

Answer: B

Explanation: References:
https://blogs.msdn.microsoft.com/alwaysonpro/2021/11/29/availability-group-database-reports-not-synchronizin

NEW QUESTION 9
Relecloud recently implemented the planned changes. you plan to add a new customer named Fabrikam, Inc.
You need to ensure that the administrators at Fabrikam can access their data after the data loading process. You collect the IP addresses used by the Fabrikam administrators.
Which Transact-SQL command or Azure PowerShell cmdlet should you use next?

• A. sp_set_firewall_rule
• B. Set-AzureRmSqlServerFirewallRule
• C. sp_set_database_firewall_rule
• D. new-AzureRmSqlServerFirewallRule

Answer: D

Explanation: The New-AzureRmSqlServerFirewallRule cmdlet creates a firewall rule for the specified Azure SQL Database server.
Database-level firewall rules enable clients to access certain (secure) databases within the same logical server. You can create these rules for each database (including the master database) and they are stored in the individual databases.
Reference:
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/new-azurermsqlserverfirewallrule?view=azure https://docs.microsoft.com/en-us/azure/sql-database/sql-database-firewall-configure

Topic 5, Tailspin Toys
Overview
Tailspin Toys is a worldwide manufacturing company that operates on several continents. The company has datacenters in London and New York.
Existing Environment Technical Problems
User in the London office and the New York office report quick response times when they connect to the datacenters. Users in all other offices report slow response times.
Application Environment
Tailspin Toys stores sensitive customer data.
The company uses several applications. An application named App1 uses Microsoft SQL Server as a back-end database. An application named App2 uses MySQL as a back-end database.
customer audits of documents rarely occur. When an audit occurs, it is scheduled between the customer and the auditing team of Tailspin Toys.
Requirements Planned Charges
Tailspin Toys plans to migrate several workloads to Microsoft Azure. As part of the planned migration, the company requires that the lowest costs for monthly Azure services and for SQL Serverlicense planes be implemented, whenever possible.
Technical Requirements
The customer-sensitive data stored in Azure must be encrypted when at rest.
The on-premises environment of Tailspin Toys has several jobs that support business processes scheduled by using the SQL Server Agent. After the migration to Azure, the business processes must be scheduled to execute righty at midright Eastern Time (UTC-5). The implementation of the business processes must minimize administrative effort.
Networking Requirements
All Internet traffic initiated from the servers in Azure must be routed through the local network firewalls and content filters.
Applications
App1
App1 is developed and maintained by the developers at Tailspin Toys the company plans to make architectural changes to the application to achieve the best possible performance and state.
The database for App1 is named DB1 and is 100-TB. 99.8 percent of the data in DB1 is large object binary (LOB) data. After the data is uploaded to DB1, the data is accessed frequently for 10 days; then the data is accessed rarely for 20 days; and then the data is never accessed, unless a customer audit occurs.
Users in multiple Tailspin Toys officers worldwide must use app1 to access the relational records stored in DB1.
App1 has a Service Level Agreement (SLA) that requires an uptime of 99.95 percent.
After the planned migration to Azure, the finance department plans to replicate a subset of DB1 to an on-premises database for reporting purposes.
App2
App2 is developed by using third-party open source code that is updated frequently. The database for App2 is named DB2. App2 only supports My SQL. None of the developers at Tailspin Toys are skilled is using MySQL DB2 is supported by a team of external developers.
DB2 is 20 GB and is expected to reach 50 GB during the next five years. DB2 has a storage performance requirement of 200 IOPs.
DB2 must be highly available. App3
As part of the planned migration to Azure, Tailspin Toys is developing a new application named App3 that will provide business-to-business features. Some of the customers that will use App3 will have a single employee, while others will have tens of thousands of employees using the application.
Due to the different size of the workloads, Tailspin Toys is concerned that parameter sniffing will hinder the performance of App3.
The web servers for App3 will be in multiple Azure regions and will use Traffic Manager.
Customers worldwide will use App3. For each customer, the database for App3 must be located near the customer's corporate office. The customer also must be able to choose additional regions in which to store read only copies of their database for an additional cost.
Two hundred customers plan to use App3 as soon as it becomes available. The customers request that their data be made available to them for reporting purposes. Some customers plan to use reporting in Azure, while other customers plan to use their database for an additional cost.
App3 has an SLA that requires 99.95 percent uptime.
The developers of App3 require an empty development database and a test database that contains data to test the application. The databases must be refreshed regularly.

NEW QUESTION 10
You have 10 Microsoft Azure SQL databases. One of the databases is named DB1.
You need to ensure that only a specific IP address can be used to access DB1. The solution must affect only DB1.
What should you use?

• A. the sp_set_database_firewall_rule statement
• B. the New-AzureSqlDatabaseServerFirewallRule cmdlet
• C. the Azure portal to add an endpoint
• D. the Set-AzureSqlDtatabaseServerFirewallRule cmdlet

Answer: B

NEW QUESTION 11
You have Microsoft SQL Server installed on a Microsoft Azure virtual machine. The virtual machine is on the Standard D3 service tier. All of the data files for the databases on the virtual machine are located on a single volume that is mounted as drive E.
The virtual machine experiences performance issues.
You view the wait statistics and discover a high number of GEIOLATCH_SH waits associated to the tempdb data files.
You need to resolve the performance issues. What should you do?

• A. Move the tempdb data files to drive D.
• B. Move the tempdb data files to drive C.
• C. Change the service tier of the virtual machine to Standard D12.
• D. Change the service tier of the virtual machine to Standard DS3.

Answer: A

NEW QUESTION 12
You need to configure a runbook to execute when a Microsoft Azure SQL database uses more than 80 percent of the Database Throughput Unit (DTU) resources.
What should you configure in the runbook?

• A. a trigger
• B. a connection
• C. a webhook
• D. a job

Answer: C

NEW QUESTION 13
You are planning the consolidation of the databases from Host1.
You need to identify which methods to use to back up the data after the consolidation completes. What are two possible methods that achieve this goal? Each correct answer presents a complete solution.

• A. BACKUP TO URL
• B. AlwaysOn failover clustering
• C. a maintenance plan
• D. AlwaysOn Availability Groups

Answer: AC

Explanation: Topic 2, ADatumCorporationOverview
General Overview
ADatumCorporation is a real estate firm that has offices throughout North America.
ADatumhas a main office and four branch offices. The main office is located in Seattle. The branch offices are located in New York, Montreal, Denver, and Vancouver.
Existing Environment
Network Infrastructure
The network contains one Active Directory domain namedADatum.com. Each office contains one domain controller.
Each office has a 100-Mbps connection to the Internet that is 20 percent saturated on average. The offices connect to each other through the Internet by using VPN appliances.
ADatumuses the public IP addresses shown in the following table.

SQL Server Infrastructure
In the main office,ADatumhosts a Microsoft SQL Server instance on a server named SQL1. SQL1 has a
400- GB database named Listings. Log-shipped copies of Listings are present in each branch office. The copies are used for reporting. Currently, all of the SQL Server instances run SQL Server 2014 Enterprise edition.
Each branch office has an application server that hosts an application named App1. App1 is configured to connect to the Listings database on each local SQL Server instance for reporting and to connect to the SQL Server instance in the main office for any updates to property listings.
The main office also has an application server that hosts App1. The application server connects to the local Listings database for reporting and for any updates to the property listings.
Historic activity of the Listings database shows a maximum of 475 concurrent requests from as many as 200 concurrent connections.
User Issues
Users report that, frequently, they are disconnected from the Listings database when they run reports. Users also report that there is an unacceptable delay between when a property listing is updated and when the updated listing appears in the listings reports.
Developers report concerns about the lack of a testing environment in which code changes can be validated before being deployed to the production Listings database.
Requirements
Business Requirements
ADatumidentifies the following business requirements:

Planned changes
ADatumplans to implement the following changes:

Technical Requirements
ADatumidentifies the following technical requirements:

High-Availability and Recovery Requirements
ADatumidentifies the following high-availability and recovery requirements:

NEW QUESTION 14
Which database platform should App3 use?

• A. Azure SQL Database
• B. Azure Database for PostgreSQL
• C. Azure Database for MySQL
• D. an Azure virtual machine that has SQL Server installed

Answer: C

NEW QUESTION 15
Your company has two offices located in London and New York.
The London office has a public IP of 131.107.1.1 and a private IP subnet of 192.168.4.0/24. The New York office has a public IP of 131.107.15.1 and a private IP subnet of 192.168.8.0/24.
You have a Microsoft Azure SQL database. Currently, only the users in the London office use the database. You plan to provide the users in the New York office with access to the database.
You need to ensure that the New York office users can access the database. Which command should you execute?

• A. EXECUTE sp_set_database_firewall_rule N’NewYork,’131.107.15.1’,’255.255.255.255’
• B. EXECUTE sp_set_database_firewall_rule N’NewYork, ’192.168.8.0’, ‘192.168.8.255’;
• C. EXECUTE sp_set_database_firewall_rule N’NewYork,’192.168.8.0’, ’255.255.255.0’;
• D. EXECUTE sp_set_database_firewall_rule N’NewYork,’131.107.15.1’,’131.107.15.1’;

Answer: B

Explanation: References:
http://azure.patrickkeisler.com/2015/11/configuring-the-azure-sql-database-firewall/
https://msdn.microsoft.com/en-gb/library/dn270010.aspx

NEW QUESTION 16
Your company's data center contains three Microsoft SQL Server databases named DB1, DB2, and DB3. You plan to migrate the databases to Microsoft Azure.
You need to provision the recourses in Azure to meet the following requirements.
* DB1 must be able to execute CLR assemblies.
* DB2 must be able to run SQL Server Agent jobs.
Which database solution should you use for each database? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation:

NEW QUESTION 17
You have an application named Appl that connects to a Microsoft Azure SQL database named AppDBl. Appl uses columnstore indexes as part of the database schema.
You plan to deploy another Azure SQL database named AppDBDev that will be used as the development environment for Appl.
You create a copy of AppDBl for AppDBDev.
You need to identify which service tier must be used to store AppDBDev. The solution must minimize the cost to store AppDBDev.
Which service tier should you identify?

• A. Basic
• B. Standard
• C. Premium
• D. Premium RS

Answer: B