NEW QUESTION 1
You plan to migrate a Microsoft SQL server instance between physical servers.
You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts. Solutions: You restore the msdb database.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: A

Explanation: The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/databases/msdb-database?view=sql-server-2021

NEW QUESTION 2
You administer a Microsoft SQL Server 2014 database named Orders. Orders contains a table named OrderShip that is defined as follows:

A NULL value represents a domestic order. Ninety percent of the values in CountryCode are NULL. Customers require a procedure that will return orders for all customers from a specified country. You create a new procedure:

Performance on this procedure is slow.
You need to alter the schema to optimize this query. Objects created must use a minimum amount of resources.
Which Transact-SQL statement should you use?

• A. CREATE NONCLUSTERED INDEX IX_CountryCode ON Ordership (CountryCode) WHERE CountryCode IS NOT NULL
• B. CREATE STATISTICS ST_CountryCode ON OrderShip (CountryCode) WHERE CountryCodeIS NOT NULL
• C. CREATE CLUSTERED INDEX IX_CountryCode ON OrderShip (CountryCode)
• D. CREATE INDEX IX_CountryCode ON OrderShip (CustomerID) WHERE CountryCode IS NOT NULL

Answer: B

Explanation: Here creating statistics is relevant. The CREATE STATISTICS command creates query optimization statistics on one or more columns of a table, an indexed view, or an external table. For most queries, the query optimizer already generates the necessary statistics for a high-quality query plan; in a few cases, you need to create additional statistics with CREATE STATISTICS or modify the query design to improve query performance.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/create-statistics-transact-sql

NEW QUESTION 3
You administer a Microsoft SQL Server 2014 failover cluster.
You need to ensure that a failover occurs when the server diagnostics returns query_processing error. Which server configuration property should you set?

• A. SqlOumperDumpFlags
• B. FailureConditionLevel
• C. HealthCheckTimeout
• D. SqlDumperDumpPath

Answer: B

Explanation: Use the FailureConditionLevel property to set the conditions for the Always On Failover Cluster Instance (FCI) to fail over or restart.
The failure conditions are set on an increasing scale. For levels 1-5, each level includes all the conditions from the previous levels in addition to its own conditions.
Note: The system stored procedure sp_server_diagnostics periodically collects component diagnostics on the SQL instance. The diagnostic information that is collected is surfaced as a row for each of the following components and passed to the calling thread.
The system, resource, and query process components are used for failure detection. The io_subsytem and events components are used for diagnostic purposes only.
References:https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/windows/configure-failurecondition

NEW QUESTION 4
You are using dynamic management views to monitor an SQL Server server named SQL1. A database administrator named Dba1 must monitor the health of SQL1.
You need to ensure that Dba1 can access dynamic management views for SQL1. The solution must use the principle of least privilege.
Which permissions should you assign to Dba1?

• A. VIEW ANY DEFINITION
• B. VIEW SERVER STATE
• C. VIEW DEFINITION
• D. CONTROL SERVER

Answer: B

Explanation: To query a dynamic management view or function requires SELECT permission on object and VIEW SERVER STATE or VIEW DATABASE STATE permission.
There are two types of dynamic management views and functions:
Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server.
Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-

NEW QUESTION 5
A company uses several Microsoft Azure elastic pools with Azure SQL Database instances.
You have two pools named Pool1 and Pool2. Pool2 is near maximum capacity and cannot accommodate the database move.
You need to move the database from Pool1 to Pool2.
Which PowerShell cmdlets should you run? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: References:
https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqlelasticpool?view=azurermps-6.1 https://docs.microsoft.com/en-us/powershell/module/azurerm.sql/set-azurermsqldatabase?view=azurermps-6.12.

NEW QUESTION 6
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution that might meet the stated goals. Some question sets might have more than one correct solution, while others might not have a correct solution.
After you answer a question in this sections, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You are migrating an on-premises Microsoft SQL Server instance to SQL Server on a Microsoft Azure virtual machine. The instance has 30 databased that consume a total of 2 TB of disk space.
The instance sustains more than 30,000 transactions per second.
You need to provision storage for the virtual machine. The storage must be able to support the same load as the on-premises deployment.
Solution: You create one storage account that has 30 containers. You create a VHD in each container. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: Each Storage Account handles up to 20.000 IOPS, and 500TB of data.
References: https://www.tech-coffee.net/understand-microsoft-azure-storage-for-virtual-machines/

NEW QUESTION 7
You have Microsoft SQL Server on a Microsoft Azure Virtual machine that has a 4-TB database.
You plan to configure daily backups for the database. A single full backup will be approximately 1.5 TB of compressed data.
You need to ensure that the last backups are retained. Where should you store the daily backups?

• A. Local storage
• B. Page blob storage
• C. Virtual disks
• D. Block blob storage.

Answer: D

Explanation: When backing up to Microsoft Azure blob storage, SQL Server 2021 supports backing up to multiple blobs to enable backing up large databases, up to a maximum of 12.8 TB. This is done through Block Blobs.
References:

NEW QUESTION 8
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 Dynamic Data Masking on the primary replica.
Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: SQL Database dynamic data masking does not encrypt the data. Transparent Data Encryption (TDE) would provide a solution.
Note: SQL Database dynamic data masking limits sensitive data exposure by masking it to non-privileged users.
Dynamic data masking helps prevent unauthorized access to sensitive data by enabling customers to designate how much of the sensitive data to reveal with minimal impact on the application layer.
References:
https://azure.microsoft.com/en-us/blog/how-to-configure-azure-sql-database-geo-dr-with-azure-key-vault/

NEW QUESTION 9
Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non- clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?

• A. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = CONVERT(DATE, GETDATE())
• B. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime = GETDATE()
• C. SELECT COUNT(*)FROM PurchasesWHERE CONVERT(VARCHAR, PurchaseTime, 112)=CONVERT(VARCHAR, GETDATE(), 112)
• D. SELECT COUNT(*)FROM PurchasesWHERE PurchaseTime >= CONVERT(DATE, GETDATE())AND PurchaseTime <DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))

Answer: D

Explanation: To compare a time with date we must use >= and > operators, and not the = operator.

NEW QUESTION 10
You administer a Microsoft SQL Server 2014 instance named SQL2012. You are in the process of migrating a database from a SQL Server 2008 instance named SQL2008 to the SQL2012 instance.
You have upgraded a database from the SQL2008 instance by using the side-by-side migration technique. You need to migrate the SQL Server logins from the SQL2008 instance to the SQL2012 instance.
What should you do?

• A. Back up the master database on the SQL2008 instanc
• B. Restore the master database on the SQL2012 instance
• C. Use the Transfer Logins task in a Microsoft SQL Server Integrated Services package
• D. Use sp_grantlogin
• E. Use xp_logininfo.

Answer: C

Explanation: sp_grantlogin creates a SQL Server login.

NEW QUESTION 11
You are a database developer of a Microsoft SQL Server 2014 database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.

You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?

• A. CREATE TABLE Customer(SourceID int NOT NULL IDENTITY,CustomerID int NOT NULL IDENTITY,CustomerName varchar(255) NOT NULL);
• B. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL PRIMARY KEY CLUSTERED,CustomerName varchar(255) NOT NULL);
• C. CREATE TABLE Customer(SourceID int NOT NULL PRIMARY KEY CLUSTERED,CustomerID int NOT NULL UNIQUE,CustomerName varchar(255) NOT NULL);
• D. CREATE TABLE Customer(SourceID int NOT NULL,CustomerID int NOT NULL,CustomerName varchar(255) NOT NULL,CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED(SourceID,CustomerID));

Answer: D

NEW QUESTION 12
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
You need to ensure that your backup will continue if any invalid checksum is encountered. Which backup option should you use?

• A. STANDBY
• B. Differential
• C. FULL
• D. CHECKSUM
• E. BULK_LOGGED
• F. CONTINUE_AFTER_ERROR
• G. SIMPLE
• H. DBO_ONLY
• I. COPY_ONLY
• J. SKIP
• K. RESTART
• L. Transaction log
• M. NO_CHECKSUM
• N. NORECOVERY

Answer: F

Explanation: The CONTINUE_AFTER_ERROR option, of the Transact-SQL BACKUP command, instructs BACKUP to continue despite encountering errors such as invalid checksums or torn pages.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION 13
You plan to install a Microsoft SQL Server 2014 instance.
The instance will support a database that has the following requirements: Store Excel workbooks on the file system.
Access the workbooks through Transact-SQL.
Include the workbooks in database backups.
During installation, you need to ensure that the requirements will be met. Which feature should you use?

• A. Excel Services
• B. FILESTREAM
• C. SQL Server Integration Services (SSIS)
• D. OpenXML

Answer: B

Explanation: FILESTREAM enables SQL Server-based applications to store unstructured data, such as documents and images, on the file system. Applications can leverage the rich streaming APIs and performance of the file system and at the same time maintain transactional consistency between the unstructured data and corresponding structured data.
References:https://docs.microsoft.com/en-us/sql/relational-databases/blob/filestream-sql-server

NEW QUESTION 14
You plan to deploy a Microsoft SQL Server database that will use FILESTREAM. The database will store 4 TB of FILESTREAM data on a single Windows partition.
You need to configure the hard disk that will support the FILESTREAM data. The solution must provide the fastest read and write access to the data.
How should you configure the disk? To answer, select the appropriate options in the answer area. NOTE: Each correct selection is worth one point.

Answer:

Explanation: File System: NTFS
8.3 filename support: Disabled Indexing: Disabled
NTFS is required.
Disable generation of 8.3 names on all NTFS volumes used for FILESTREAM data storage.
Check that search indexing is not enabled on FILESTREAM volumes, under the Volume Properties window, unchecking the “Allow files on this drive to have contents indexed in addition to file properties” box.
References:
https://blogs.msdn.microsoft.com/blogdoezequiel/2011/02/11/best-practices-on-filestreamimplementations/

NEW QUESTION 15
You manage a Microsoft SQL Server instance named SQL1 that has 32 gigabytes (GB) of total memory. The instance supports an app named App1 that only uses a single thread. App1 frequently queries the database using the same index. The operating system and App1 combined require 8 GB of memory to function.
You need to ensure that the SQL Server does not limit the performance of App1. What configuration option should you set?

• A. min memory per query to 4 GB
• B. index create memory to 16 GB
• C. max worker threads to 1
• D. max server memory to 16 GB

Answer: B

Explanation: The index creates memory option controls the maximum amount of memory initially allocated for sort operations when creating indexes. The default value for this option is 0 (self-configuring). If more memory is later needed for index creation and the memory is available, the server will use it; thereby, exceeding the setting of this option. If additional memory is not available, the index creation will continue using the memory already allocated.
References:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-indexcreate-memory-ser

NEW QUESTION 16
You administer a Microsoft SQL Server 2014 instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
The database is continually modified by users during business hours from Monday through Friday between 09:00 hours and 17:00 hours. Five percent of the existing data is modified each day.
The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database.
These data load operations must occur in the minimum amount of time.
A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours.
On Wednesday at 10:00 hours, the development team requests you to refresh the database on a development server by using the most recent version.
You need to perform a full database backup that will be restored on the development server. Which backup option should you use?

• A. NORECOVERY
• B. FULL
• C. NO_CHECKSUM
• D. CHECKSUM
• E. Differential
• F. BULK_LOGGED
• G. STANDBY
• H. RESTART
• I. SKIP
• J. Transaction log
• K. DBO ONLY
• L. COPY_ONLY
• M. SIMPLE
• N. CONTINUE AFTER ERROR

Answer: L

Explanation: COPY_ONLY specifies that the backup is a copy-only backup, which does not affect the normal sequence of backups. A copy-only backup is created independently of your regularly scheduled, conventional backups. A copy-only backup does not affect your overall backup and restore procedures for the database.
References:
https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql

NEW QUESTION 17
You administer a Windows Azure SQL Database database named Inventory that contains a stored procedure named p_AddInventory.
Users need to be able to SELECT from all tables in the database and execute the stored procedure. You need to grant only the necessary permissions.
What should you do?

• A. Grant EXECUTE permission on p_AddInventory to all user
• B. Grant VIEW DEFINITION to all users.
• C. Grant EXECUTE permission on p_AddInventory to all user
• D. Add all users to the db_datawriter role.
• E. Add all users to the db_owner role.
• F. Grant EXECUTE permission on p_AddInventory to all user
• G. Add all users to the db_datareader role.

Answer: D

Explanation: Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
References:https://technet.microsoft.com/en-us/library/ms188629(v=sql.90).aspx

NEW QUESTION 18
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 QUERY_OPTIMIZER_HOTFIXES option for the databases. Does the solution meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: QUERY_OPTIMIZER_HOTFIXES = { ON | OFF | PRIMARY } enables or disables query optimization hotfixes regardless of the compatibility level of the database. This is equivalent to Trace Flag 4199.
References:https://msdn.microsoft.com/en-us/library/mt629158.aspx

NEW QUESTION 19
You plan to deploy Microsoft SQL Server on a Microsoft Azure Virtual machine. The virtual machine will have a 30-TB database and will have 10 1-TB VHDs for the database.
You need to configure the storage to meet the following requirements:
Evenly distribute read and write operations across the VHDs.
Minimize the read and write time.
Which storage configuration should you use?

• A. a parity storage pool
• B. a simple storage pool
• C. a mirrored storage pool
• D. a striped volume
• E. a RAID-5 volume

Answer: D

Explanation: Data that is written to a striped volume is interleaved to all disks at the same time instead of sequentially. Therefore, disk performance is the fastest on a RAID 0 volume as compared to any other type of disk
configuration.
Reference:
https://support.microsoft.com/en-us/help/323433/how-to-establish-a-striped-volume-raid-0-inwindows-server-20