NEW QUESTION 1
Note: This question 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 have deployed several GS-series virtual machines (VMs) in Microsoft Azure. You plan to deploy Microsoft SQL Server in an Always On Availability Group. You expect to have less than 1 million IO transaction per month.
You need to recommend a storage solution for the SQL Servers. The solution must minimize costs. Which storage option should you use?

• A. Premium P10 disk storage
• B. Premium P20 disk storage
• C. Premium P30 disk storage
• D. Standard locally redundant disk storage
• E. Standard geo-redundant disk storage
• F. Standard zone redundant blob storage
• G. Standard locally redundant blob storage
• H. Standard geo-redundant blob storage

Answer: A

Explanation: P10 has 500 IOPS per disk, which provides for more than 1 million IOPS per month.
Note: 3600*30* 500 is 54 million IOPS/month.
References: https://azure.microsoft.com/en-us/pricing/details/managed-disks/

NEW QUESTION 2
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 write/sec’ parameter. Does this meet the goal?

• A. Yes
• B. No

Answer: B

NEW QUESTION 3
You have Microsoft SQL Server on a Microsoft Azure virtual machine. You create a SQL Server Agent job by using the following statement.

You need to send an email message if the job fails. Which stored procedure should you use?

• A. msd
• B. db
• C. sp_updace_alerc
• D. msdb.dbo.sp_add_jobstep
• E. msdb.dbo.sp_add_notification
• F. msdb.dbo.sp_help_alert

Answer: C

Explanation: To notify an operator of job status through Transact-SQL.
In Object Explorer, connect to an instance of Database Engine. On the Standard bar, click New Query.
-- adds an e-mail notification for the specified alert (Test Alert).
-- This example assumes that Test Alert already exists
-- and that François Ajenstat is a valid operator name. USE msdb ;
GO
EXEC dbo.sp_add_notification
@alert_name = N'Test Alert',
@operator_name = N'François Ajenstat',
@notification_method = 1 ; GO
References:
https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-2021

NEW QUESTION 4
You are the administrator of a Microsoft SQL Server 2014 server.
Some applications consume significant resources. You need to manage the server workload by restricting resource-intensive applications
You need to dynamically limit resource consumption. What should you do?

• A. Configure Resource Pools, Workload Groups, and Classifier Function, and then enable the Resource Governor
• B. Set up Service Broker to ensure that application are not allowed to consume more than the specified amount of resource
• C. Create a new rule for each application that sets the resource limit allowed
• D. Create a new plan Guide with a Scope Type of sql and define the resource limits for each application

Answer: A

Explanation: In the SQL Server Resource Governor, a resource pool represents a subset of the physical resources of an instance of the Database Engine. Resource Governor enables you to specify limits on the amount of CPU, physical IO, and memory that incoming application requests can use within the resource pool. Each resource pool can contain one or more workload groups. When a session is started, the Resource Governor classifier assigns the session to a specific workload group, and the session must run using the resources assigned to the workload group.
References:https://docs.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor-resou

NEW QUESTION 5
A company has an on-premises Microsoft SQL Server environment with a SQL-Server named SQL01. You need to create a local sysadmin account on SQL01 named Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.

Answer:

Explanation: B: First we create a login with the CREATE LOGIN command. E: Then we add it to the sysadmin role.
1. To add a member to a fixed server role
2. In Object Explorer, connect to an instance of Database Engine.
3. On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute. ALTER SERVER ROLE diskadmin ADD [DomainJuan] ;
GO
G: Finally we add a database user for the login we created.
References: https://technet.microsoft.com/en-us/library/aa337562(v=sql.110).aspx

NEW QUESTION 6
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 7
You administer a Microsoft SQL Server 2014 database that includes a table named Application.Events. Application.Events contains millions of records about user activity in an application.
Records in Application.Events that are more than 90 days old are purged nightly. When records are purged, table locks are causing contention with inserts.
You need to be able to modify Application.Events without requiring any changes to the applications that utilize Application.Events.
Which type of solution should you use?

• A. Partitioned tables
• B. Online index rebuild
• C. Change data capture
• D. Change tracking

Answer: A

Explanation: Partitioning large tables or indexes can have manageability and performance benefits including:
You can perform maintenance operations on one or more partitions more quickly. The operations are more efficient because they target only these data subsets, instead of the whole table.
References: https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes

NEW QUESTION 8
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 9
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
A network administrator upgrades the hardware firewalls on the network. You need to verify whether data migration still runs successfully.
Which stored procedure should you run?

• A. Sys_sp_testlinkedserver
• B. Sys_sp_rda_test_connection
• C. Sys_sp_rda_reauthorized_db
• D. Sp_set_firewall_rule

Answer: B

Explanation: The Sys_sp_rda_test_connection cmdlet tests the connection from SQL Server to the remote Azure server and reports problems that may prevent data migration.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-rda-test-connection-tr

NEW QUESTION 10
Your company has several Microsoft Azure SQL Database instances used within an elastic pool. You need to obtain a list of databases in the pool.
How should you complete the commands? To answer, drag the appropriate segments to the correct targets. Each 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.
NOTE: Each correct selection is worth one point.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/cli/azure/sql/elastic-pool?view=azure-cli-latest#az-sql-elastic-pool-list-dbs

NEW QUESTION 11
You are the administrator for a SQL Server 2021 instance that stores the data for an online transaction processing sales system. The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups.
These backups are stored on a backup server in the company's data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system. The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.

• A. Set up SQL Server Always On with a SQL Azure database as a replica.
• B. Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
• C. Put the differential backup on tape and send it to the third-party backup storage system.
• D. Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.

Answer: D

Explanation: Microsoft SQL Server Backup to Microsoft Azure Tool enables backup to Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.
References: https://www.microsoft.com/en-us/download/details.aspx?id=40740

NEW QUESTION 12
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the LEGACY_CARDINALITY_ESTIMATION option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: LEGACY_CARDINALITY_ESTIMATION = { ON | OFF | PRIMARY }
Enables you to set the query optimizer cardinality estimation model to the SQL Server 2012 and earlier version independent of the compatibility level of the database. This is equivalent to Trace Flag 9481.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 13
You plan to deploy an AlwaysOn failover cluster in Microsoft Azure. The cluster has a Service Level Agreement (SLA) that requires an uptime of at least 99.95 percent.
You need to ensure that the cluster meets the SLA.
Which cmdlet should you run before you deploy the virtual machine?

• 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: On Azure virtual machines, a SQL Server Availability Group requires a load balancer. The load balancer holds the IP address for the Availability Group listener. The New-AzureRmLoadBalancer cmdlet creates an Azure load balancer.
References:

NEW QUESTION 14
You have database that contains a 400-GB table that is read-only. You need to enable the Stretch Database feature.
How should you complete the 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. Select and Place:

Answer:

Explanation: Section: Deploy and migrate applications
To configure an existing table for Stretch Database, run the ALTER TABLE command. Here's an example that migrates the entire table and begins data migration immediately. USE <Stretch-enabled database name>;
GO
ALTER TABLE <table name>
SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ; GO
References: https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-atable

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 database that contains a table named OrderDetail.
You discover that the NCI_OrderDetail_CustomerID non-clustered index is fragmented. You need to reduce fragmentation. You need to achieve this goal without taking the index offline.
Which Transact-SQL batch should you use?

• A. CREATE INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID WITH DROP EXISTING
• B. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REORGANIZE
• C. ALTER INDEX ALL ON OrderDetail REBUILD
• D. ALTER INDEX NCI_OrderDetail_CustomerID ON OrderDetail.CustomerID REBUILD

Answer: B

Explanation: REORGANIZE specifies to reorganize the index leaf level. The REORGANIZE operation is always performed online. This means long-term blocking table locks are not held and queries or updates to the underlying table can continue during the ALTER INDEX REORGANIZE transaction.
References:https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-index-transact-sql

NEW QUESTION 16
You plan to deploy 20 Microsoft Azure SQL Database instances to an elastic pool in Azure to support a batch processing application.
Two of the databases in the pool reach their peak workload threshold at the same time every day. This leads to inconsistent performance for batch completion.
You need to ensure that all batches perform consistently. What should you do?

• A. Create an In-Memory table.
• B. Increase the storage limit in the pool.
• C. Implement a readable secondary database.
• D. Increase the total number of elastic Database Transaction Units (eDTUs) in the pool.

Answer: D

Explanation: In SQL Database, the relative measure of a database's ability tohandle resource demands is expressed in Database Transaction Units (DTUs) for single databases and elastic DTUs (eDTUs) for databases in an elastic pool.
A pool is given a set number of eDTUs, for a set price. Within the pool, individual databases are given the flexibility to auto-scale within set parameters. Under heavy load, a database can consume more eDTUs to meet demand.
Additional eDTUs can be added to an existing pool with no database downtime. References:https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 17
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the PARAMETER_SNIFFING option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: PARAMETER_SNIFFING = { ON | OFF | PRIMARY} enables or disables parameter sniffing. This is equivalent to Trace Flag 4136.
SQL server uses a process called parameter sniffing when executing queries or stored procedures that use parameters. During compilation, the value passed into the parameter is evaluated and used to create an execution plan. That value is also stored with the execution plan in the plan cache. Future executions of the plan will re-use the plan that was compiled with that reference value.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 18
You administer a Microsoft SQL Server 2014 database. You want to make a full backup of the database to a file on disk.
In doing so, you need to output the progress of the backup. Which backup option should you use?

• A. STATS
• B. COMPRESSION
• C. CHECKSUM
• D. IN IT

Answer: A

Explanation: STATS is a monitoring option of the BACKUP command. STATS [ =percentage ]
Displays a message each time another percentage completes, and is used to gauge progress. If percentage is omitted, SQL Server displays a message after each 10 percent is completed.
The STATS option reports the percentage complete as of the threshold for reporting the next interval. This is at approximately the specified percentage; for example, with STATS=10, if the amount completed is 40 percent, the option might display 43 percent. For large backup sets, this is not a problem, because the percentage complete moves very slowly between completed I/O calls.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION 19
You administer a Windows Azure SQL Database database named Orders. You need to create a copy of Orders named Orders_Reporting.
Which Transact-SQL command should you use?

• A. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'RESTORE DATABASEOrders_ReportingFROM DISK = 'D:Orders.bak
• B. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'CREATE DATABASEOrders_ReportingFROM DISK = 'D:Orders.bak
• C. CREATE DATABASE Orders_Reporting AS COPY OF Orders
• D. BACKUP DATABASE Orders TO DISK = 'D:Orders.bak'MIRROR TO DISK = 'Orders_Reporting

Answer: C

Explanation: BACKUP DATABASE …AS COPY OF [source_server_name.]source_database_name Is used for copying a database to the same or a different SQL Database server.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-database-azure-sql-database

NEW QUESTION 20
You have a database named DB1. You discover that DB1 is corrupt.
You run DBCC CHECKDB and receive an error message within a few seconds. No pages are listed in the error message.
You need to repair the database corruption as quickly as possible. The solution must minimize data loss.
What should you do?

• A. Run DBCC CHECKDB (‘db1’, REPAIR_ALLOW_DATA_LOSS).
• B. Run DBCC CHECKDB (‘db1’, REPAIR_FAST).
• C. Delete the transaction logs and restart the Microsoft SQL Server instance.
• D. Run DBCC CHECKDB (‘db1’, REPAIR_REBUILD).
• E. Restore the database from a backup.

Answer: C

Explanation:
REPAIR_REBUILD
Performs repairs that have no possibility of data loss. This can include quick repairs, such as repairing missing rows in non-clustered indexes, and more time-consuming repairs, such as rebuilding an index.