You have an Azure SQL database.
You discover that the plan cache is full of compiled plans that were used only once.
You run the select * from sys.database_scoped_configurations Transact-SQL command and receive the results shown in the following table.

You need relieve the memory pressure.
What should you configure?

• A.LEGACY_CARDINALITY_ESTIMATION
• B.QUERY_OPTIMIZER_HOTFIXES
• C.OPTIMIZE_FOR_AD_HOC_WORKLOADS
• D.ACCELERATED_PLAN_FORCING

Comment: *

Name: *

Email: *

Verification: *

You have the following Azure Data Factory pipelines:
Ingest Data from System1
Ingest Data from System2
Populate Dimensions
Populate Facts
Ingest Data from System1 and Ingest Data from System2 have no dependencies. Populate Dimensions must execute after Ingest Data from System1 and Ingest Data from System2. Populate Facts must execute after the Populate Dimensions pipeline. All the pipelines must execute every eight hours.
What should you do to schedule the pipelines for execution?

• A.Add a schedule trigger to all four pipelines.
• B.Add an event trigger to all four pipelines.
• C.Create a parent pipeline that contains the four pipelines and use an event trigger.
• D.Create a parent pipeline that contains the four pipelines and use a schedule trigger.

Comment: *

Name: *

Email: *

Verification: *

You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?

• A.Azure SQL Database Premium
• B.Azure SQL Database Hyperscale
• C.Azure SQL Database managed instance Business Critical
• D.Azure SQL Database Standard

Comment: *

Name: *

Email: *

Verification: *

You plan to create a table in an Azure Synapse Analytics dedicated SQL pool.
Data in the table will be retained for five years. Once a year, data that is older than five years will be deleted.
You need to ensure that the data is distributed evenly across partitions. The solutions must minimize the amount of time required to delete old data.
How should you complete the Transact-SQL statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all.
You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.

Correct Answer:

Reference:
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-table-azure-sql-data-warehouse
https://docs.microsoft.com/en-us/azure/synapse-analytics/sql/best-practices-dedicated-sql-pool

Comment: *

Name: *

Email: *

Verification: *

DRAG DROP
You have an Azure SQL Database instance named DatabaseA on a server named Server1.
You plan to add a new user named App1 to DatabaseA and grant App1 db_datacenter permissions. App1 will
use SQL Server Authentication.
You need to create App1. The solution must ensure that App1 can be given access to other databases by using
the same credentials.
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.
Select and Place:

Correct Answer:

Section: [none]
Explanation:
Step 1: On the master database, run CREATE LOGIN [App1] WITH PASSWORD = 'p@aaW0rd!'
Logins are server wide login and password pairs, where the login has the same password across all databases.
Here is some sample Transact-SQL that creates a login:
CREATE LOGIN readonlylogin WITH password='1231!#ASDF!a';
You must be connected to the master database on SQL Azure with the administrative login (which you get from
the SQL Azure portal) to execute the CREATE LOGIN command.
Step 2: On DatabaseA, run CREATE USER [App1] FROM LOGIN [App1]
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;
Step 3: On DatabaseA run ALTER ROLE db_datareader ADD Member [App1]
Just creating the user does not give them permissions to the database. You have to grant them access. In the
Transact-SQL example below the readonlyuser is given read only permissions to the database via the
db_datareader role.
EXEC sp_addrolemember 'db_datareader', 'readonlyuser';
Reference:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/

Comment: *

Name: *

Email: *

Verification: *