NEW QUESTION 1
You are a database developer for an application hosted on a Microsoft SQL Server 2014 server. The database contains two tables that have the following definitions:

Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?

• A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT c.CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY c.CustomerID, c.CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
• C. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
• D. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDORDER BY OrderAmount DESC

Answer: A

Explanation: Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount). Finally a WHERE close is needed: WHERE o.Rnk = 1

NEW QUESTION 2
You administer a Microsoft SQL Server 2014 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account. What should you use?

• A. a schedule
• B. an alert
• C. an operator
• D. a proxy

Answer: D

Explanation: A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the proxy, and then runs the job step by using that security context.
References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx

NEW QUESTION 3
You have an on-premises Microsoft SQL Server named Server1.
You provision a Microsoft Azure SQL Database server named Server2. On Server1, you create a database named DB1.
You need to enable the Stretch Database feature for DB1.
Which five 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: Enable the remote data archive option in DB1 Prerequisite: Enable Stretch Database on the server
Before you can enable Stretch Database on a database or a table, you have to enable it on the local server. To enable Stretch Database on the server manually, run sp_configure and turn on the remote data archive option.
Step 2: Create a firewall rule in Azure
On the Azure server, create a firewall rule with the IP address range of the SQL Server that lets SQL Server communicate with the remote server.
Step 3: Create a master key in the master database
To configure a SQL Server database for Stretch Database, the database has to have a database master key. The database master key secures the credentials that Stretch Database uses to connect to the remote database.
Step 4: Create a database scoped credential for authentication to Azure
When you configure a database for Stretch Database, you have to provide a credential for Stretch Database to use for communication between the on premises SQL Server and the remote Azure server. You have two options.
Step 5: Create a server-level credential for authentication to Azure.
To configure a database for Stretch Database, run the ALTER DATABASE command. For the SERVER argument, provide the name of an existing Azure server, including the
.d atabase.windows.net portion of the name - for example, MyStretchDatabaseServer.database.windows.net.
Provide an existing administrator credential with the CREDENTIAL argument, or specify FEDERATED_SERVICE_ACCOUNT = ON. The following example provides an existing credential.
ALTER DATABASE <database name> SET REMOTE_DATA_ARCHIVE = ON (
SERVER = '<server_name>' ,
CREDENTIAL = <db_scoped_credential_name>
) ; GO
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-a-database?view=sq

NEW QUESTION 4
HOTSPOT
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.

Answer:

Explanation: Data Entry: Geo-replicated database only
From Contoso scenario: 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.
Business intelligence: Elastic database pools only
From Contoso scenario: For the business intelligence application, corporate executives must be able to view all data in near real-time with low network latency.
SQL DB elastic pools provide a simple cost effective solution to manage the performance goals for multiple databases that have widely varying and unpredictable usage patterns.
References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool
Topic 6, SQL Server ReportingBackground
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 2021. 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 2021. 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.

NEW QUESTION 5
You have a Microsoft SQL Server that has a database named DB1. DB1 has a data files on drive E and transaction logs on drive L.
Drive L fails and is replaced.
You need to recover DB1. The solution must minimize data loss.
Which three statements should you execute in sequence? To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.

Answer:

Explanation: ALTER DATABASE '<your_database>' SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO
DBCC CHECKDB ('<your_database>', REPAIR_REBUILD) GO
ALTER DATABASE '<your_database>' SET MULTI_USER GO

NEW QUESTION 6
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to diagnose deadlocks that happen when executing a specific set of stored procedures by recording events and playing them back on a different test server.
What should you create?

• A. A Database Audit Specification
• B. A Policy
• C. An Alert
• D. A SQL Profiler Trace
• E. A Resource Pool
• F. An Extended Event session
• G. A Server Audit Specification

Answer: D

Explanation: Use SQL Server Profiler to identify the cause of a deadlock. A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. Using SQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.
References:
http://msdn.microsoft.com/en-us/library/ms188246.aspx

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

• A. New-AzureRmAvailabilitySet
• B. New-AzureRmLoadBalancer
• C. New-AzureRmSqlDatabaseSecondary
• D. New-AzureRmSqlElasticPool
• E. New-AzureRmVM
• F. New-AzureRmSqlServer
• G. New-AzureRmSqlDatabaseCopy
• H. New-AzureRmSqlServerCommunicationLink

Answer: B

Explanation: 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-al

NEW QUESTION 8
You are building the database platform for a multi-tenant application. The application will have one database per tenant and will have at least 30 tenants. Each tenant will have a separate resource group for billing purposes.
The application will require at least 10 GB of clustered columnstore indexes for each database.
You need to implement the database platform for the application. The solution must minimize costs. What should you configure? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: The Standard tier service allows for 1TB of data. Here 30 x 10 GB, 0.3 TB, is required.

NEW QUESTION 9
You deploy a new Microsoft Azure SQL database instance to support a variety of mobile application and public websites. You configure geo-replication with regions in Brazil and Japan.
You need to implement real-time encryption of the database and all backups. Solution: You enable Transparent Data Encryption (TDE) on the primary instance. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: Azure SQL Database and Data Warehouse offer encryption-at-rest by providing Transparent Data Encryption (TDE) for all data written to disk, including databases, log files and backups. This protects data in case of unauthorized access to hardware. TDE provides a TDE Protector that is used to encrypt the Database Encryption Key (DEK), which in turn is used to encrypt the data. With the TDE and Bring Your Own Key (BYOK) offering currently in preview, customers can take control of the TDE Protector in Azure Key Vault.
Taking advantage of TDE with BYOK for databases that are geo-replicated to maintain high availability requires to configure and test the scenario carefully.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/

NEW QUESTION 10
You administer a SQL Server 2014 server that contains a database named SalesDB. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from any of the tables in the Customers schema. Which Transact-SQL statement should you use?

• A. REVOKE SELECT ON Schema::Customers FROM UserA
• B. DENY SELECT ON Object::Regions FROM UserA
• C. EXEC sp_addrolemember 'Sales', 'UserA'
• D. DENY SELECT ON Object::Regions FROM Sales
• E. REVOKE SELECT ON Object::Regions FROM UserA
• F. DENY SELECT ON Schema::Customers FROM Sales
• G. DENY SELECT ON Schema::Customers FROM UserA
• H. EXEC sp_droprolemember 'Sales', 'UserA'
• I. REVOKE SELECT ON Object::Regions FROM Sales
• J. REVOKE SELECT ON Schema::Customers FROM Sales

Answer: G

Explanation: Use SQL Data Warehouse or Parallel Data WarehouseGRANT and DENY statements to grant or deny a permission (such as UPDATE) on a securable (such as a database, table, view, etc.) to a security principal (a login, a database user, or a database role).
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/permissions-grant-deny-revoke-azure-sql-data-

NEW QUESTION 11
You administer a Microsoft SQL Server 2021 instance.
You need to configure a new database to support FILETABLES. What should you do? Choose all that apply.

• A. Disable FILESTREAM on the Database.
• B. Enable FILESTREAM on the Server Instance.
• C. Configure the Database for Partial Containment.
• D. Create a non-empty FILESTREAM file group.
• E. Enable Contained Databases on the Server Instance.
• F. Set the FILESTREAM directory name on the Database.

Answer: BDF

Explanation: References:
https://docs.microsoft.com/en-us/sql/relational-databases/blob/enable-the-prerequisites-for-filetable

NEW QUESTION 12
You use a Microsoft Azure SQL database as a data warehouse. The database is in the Standard service tier and has 400 elastic database throughput units (eDTUs).
You load data to the database by using Azure Data Factory. You need to reduce the amount of time it takes to load the data.
Solution: You move the database to a Standard database pool that has 800 eDTUs. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: We need at least 400 eDTUs and the use of a Standard database pool.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dtu-resource-limits

NEW QUESTION 13
You use Microsoft SQL Server 2014 to develop a database application. You need to create an object that meets the following requirements:
Which object should you use?

• A. Scalar-valued function
• B. Inline function
• C. User-defined data type
• D. Stored procedure

Answer: D

Explanation: Stored procedures accept input parameters and return multiple values in the form of output parameters to the calling program. They cannot be used in views.
References:https://docs.microsoft.com/en-us/sql/relational-databases/stored-procedures/stored-procedures-datab

NEW QUESTION 14
You have Microsoft SQL Server on a DS-series Microsoft Azure virtual machine. The virtual machine has 28 GB of memory.
You discover the following performance statistics on the server:
The average Page life expectancy is 30.
The server has excessive PAGELATCH_IO waits.
You need to decrease the PAGELATCH_IO waits. What should you do?

• A. Enable large-page support.
• B. Enable lock pages in memory.
• C. Configure buffer pool extensions.
• D. Add more tempdb files.

Answer: ACD

NEW QUESTION 15
You develop a Microsoft SQL Server 2014 database that contains a heap named OrdersHistorical. You write the following Transact-SQL query:
INSERT INTO OrdersHistorical SELECT * FROM CompletedOrders
You need to optimize transaction logging and locking for the statement. Which table hint should you use?

• A. HOLDLOCK
• B. ROWLOCK
• C. XLOCK
• D. UPDLOCK
• E. TABLOCK

Answer: E

Explanation: When importing data into a heap by using the INSERT INTO SELECT <columns> FROM statement, you can enable optimized logging and locking for the statement by specifying the TABLOCK hint for the target table.
References:https://docs.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

NEW QUESTION 16
You manage an on-premises Microsoft SQL server that has a database named DB1. An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records. You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
A)

B)

C)

D)

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

Answer: B

Explanation: Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.

NEW QUESTION 17
You have a database named DB1 that uses simple recovery mode.
Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup. There was no corruption when the last backup was complete.
You run the sys.columns catalog view and discover corrupt pages.
You need to recover the database. The solution must minimize data loss. What should you do?

• A. Run RESTORE DATABASE WITH RECOVERY.
• B. Run RESTORE DATABASE WITH PAGE.
• C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
• D. Run DBCC CHECKDB and specify the REPAIT_REBUILD parameter.

Answer: B

Explanation: A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
RESTORE DATABASE WITH PAGE
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models. References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql

NEW QUESTION 18
You administer a Microsoft SQL Server 2014 Enterprise Edition server that uses 64 cores.
You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O.
What should you configure?

• A. Processor affinity
• B. Lightweight pooling
• C. Max worker threads
• D. I/O affinity

Answer: D

Explanation: The affinity Input-Output (I/O) mask Server Configuration Option.
To carry out multitasking, Microsoft Windows 2000 and Windows Server 2003 sometimes move process threads among different processors. Although efficient from an operating system point of view, this activity can reduce Microsoft SQL Server performance under heavy system loads, as each processor cache is repeatedly reloaded with data. Assigning processors to specific threads can improve performance under these conditions by eliminating processor reloads; such an association between a thread and a processor is called processor affinity.
References:
http://msdn.microsoft.com/en-us/library/ms189629.aspx

NEW QUESTION 19
You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?

• A. ALTER INDEX IX_OrderDateON CustomerOrder DISABLE
• B. ALTER INDEX IX_OrderDateON CustomerOrder ENABLE
• C. ALTER INDEX IX_OrderDateON CustomerOrder REORGANIZE
• D. ALTER INDEX IX OrderDateON CustomerOrder REBUILD

Answer: C

Explanation: Reorganize: This option is more lightweight compared to rebuild. It runs through the leaf level of the index, and as it goes it fixes physical ordering of pages and also compacts pages to apply any previously set fillfactor settings. This operation is always online, and if you cancel it then it’s able to just stop where it is (it doesn’t have a giant operation to rollback).
References:https://www.brentozar.com/archive/2013/09/index-maintenance-sql-server-rebuild-reorganize/