You administer a Microsoft SQL Server 2012 server. The MSSQLSERVER service uses a domain account
named CONTOSO\\SQLService.
You plan to configure Instant File Initialization.
You need to ensure that Data File Autogrow operations use Instant File Initialization.What should you do? Choose all that apply.

You administer a Microsoft SQL Server 2012 database.
You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?

You administer a Microsoft SQL Server 2012 server. One of the databases on the server supports a highly
active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time.What should you do?

You administer a Microsoft SQL Server 2012 database.
Users report that an application that accesses the database displays an error, but the error does not provide
meaningful information. No entries are found in the SQL Server log or Windows event logs related to the error.
You need to identify the root cause of the issue by retrieving the error message.
What should you do?

HOTSPOT
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2,
Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All
SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.You need to set up the service accounts that the database engine and SQL Server Agent services will use.
How should you design the solution? To answer, select the appropriate configuration options in the answer
area.
Hot Area:

HOTSPOT
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2,
Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All
SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You need to maximize performance of writes to each database without requiring changes to existing database
tables.
In the table below, identify the database setting that you must configure for each database.
NOTE: Make only one selection in each column. Each correct selection is worth one point.
Hot Area:

HOTSPOT
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2,
Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All
SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
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.
Hot Area:

DRAG DROP
Background
You manage a Microsoft SQL Server environment that includes the following databases: DB1, DB2,
Reporting.
The environment also includes SQL Reporting Services (SSRS) and SQL Server Analysis Services (SSAS). All
SSRS and SSAS servers use named instances. You configure a firewall rule for SSAS.
Databases
Database Name:
DB1
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are inserted
into DB1 or updated each second. Inserts are made by many different external applications that your
company’s developers do not control. You observe that transaction log write latency is a bottleneck in
performance. Because of the transient nature of all the data in this database, the business can tolerate some
data loss in the event of a server shutdown.
Database Name:
DB2
Notes:
This database was migrated from SQL Server 2012 to SQL Server 2016. Thousands of records are updated or
inserted per second. You observe that the WRITELOG wait type is the highest aggregated wait type. Most
writes must have no tolerance for data loss in the event of a server shutdown. The business has identified
certain write queries where data loss is tolerable in the event of a server shutdown.
Database Name:
Reporting
Notes:
You create a SQL Server-authenticated login named BIAppUser on the SQL Server instance to support users
of the Reporting database. The BIAppUser login is not a member of the sysadmin role.
You plan to configure performance-monitoring alerts for this instance by using SQL Agent Alerts.
You create a login named BIAppUser. The login must be able to access the Reporting database.
You need to grant access to the BIAppUser login in the database.
How should you complete the Transact-SQL statements? To answer, drag the appropriate Transact-SQL
segments to the correct locations. Each Transact-SQL segment 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.
Select and Place:

HOTSPOT
Background
You are the database administrator for Contoso, Ltd. The company has 200 offices around the world. The
company has corporate executives that are located in offices in London, New York, Toronto, Sydney, and
Tokyo.
Contoso, Ltd. has a Microsoft Azure SQL Database environment. You plan to deploy a new Azure SQL
Database to support a variety of mobile applications and public websites.
The company is deploying a multi-tenant environment. The environment will host Azure SQL Database
instances. The company plans to make the instances available to internal departments and partner companies.
Contoso is in the final stages of setting up networking and communications for the environment.
Existing Contoso and Customer instances need to be migrated to Azure virtual machines (VM) according to the
following requirements:
Contoso instances – should use the method requiring the least administrative effort to migrate instances to
Azure Vms.
Customer instances – should use a method that allows customers to bring their own licenses to Azure VMs.
Customers have approved down time for the migration.
The company plans to deploy a new order entry application and a new business intelligence and analysis
application. Each application will be supported by a new database. Contoso creates a new Azure SQL database
named Reporting. The database will be used to support the company’s financial reporting requirements. Youassociate the database with the Contoso Azure Active Directory domain.
Each location database for the data entry application may have an unpredictable amount of activity. Data must
be replicated to secondary databases in Azure datacenters in different regions.
To support the application, you need to create a database named contosodb1 in the existing environment.
Objects

Database
The contosodb1 database must support the following requirements:
a size of at least 200 gigabytes (GB)
1,000 concurrent sessions
point-in-time restore to any point in the two weeks prior to a failure
minimize costs
Application
For the business intelligence application, corporate executives must be able to view all data in near real-time
with low network latency.
Contoso has the following security, networking, and communications requirements:
Multi-Location Load Balancing – to ensure customers have access to their tenants at multiple Azure
locations across the world.
Secure Message/Data Flow – to securely support communication between Azure and on-premises
applications and services.
Accounts should support accessing external domain resources and be configured in the most secure and
lowest-maintenance way possible, including meeting the company policy of regular service account
password changes.
You need to create the contosodb1 database.
How should you complete the Azure PowerShell command? To answer, select the appropriate Azure
PowerShell segments in the answer area.
Hot Area:

HOTSPOT
Background
You are the database administrator for Contoso, Ltd. The company has 200 offices around the world. The
company has corporate executives that are located in offices in London, New York, Toronto, Sydney, and
Tokyo.
Contoso, Ltd. has a Microsoft Azure SQL Database environment. You plan to deploy a new Azure SQL
Database to support a variety of mobile applications and public websites.
The company is deploying a multi-tenant environment. The environment will host Azure SQL Database
instances. The company plans to make the instances available to internal departments and partner companies.
Contoso is in the final stages of setting up networking and communications for the environment.
Existing Contoso and Customer instances need to be migrated to Azure virtual machines (VM) according to the
following requirements:
Contoso instances – should use the method requiring the least administrative effort to migrate instances to
Azure Vms.
Customer instances – should use a method that allows customers to bring their own licenses to Azure VMs.
Customers have approved down time for the migration.
The company plans to deploy a new order entry application and a new business intelligence and analysis
application. Each application will be supported by a new database. Contoso creates a new Azure SQL database
named Reporting. The database will be used to support the company’s financial reporting requirements. You
associate the database with the Contoso Azure Active Directory domain.
Each location database for the data entry application may have an unpredictable amount of activity. Data must
be replicated to secondary databases in Azure datacenters in different regions.
To support the application, you need to create a database named contosodb1 in the existing environment.
Objects

Database
The contosodb1 database must support the following requirements:
a size of at least 200 gigabytes (GB)
1,000 concurrent sessions
point-in-time restore to any point in the two weeks prior to a failure
minimize costs
Application
For the business intelligence application, corporate executives must be able to view all data in near real-time
with low network latency.
Contoso has the following security, networking, and communications requirements:
Multi-Location Load Balancing – to ensure customers have access to their tenants at multiple Azure
locations across the world.
Secure Message/Data Flow – to securely support communication between Azure and on-premises
applications and services.
Accounts should support accessing external domain resources and be configured in the most secure and
lowest-maintenance way possible, including meeting the company policy of regular service account
password changes.
You need to configure the data entry and business intelligence databases.
In the table below, identify the option that you must use for each database.
NOTE: Make only one selection in each column.
Hot Area: