NEW QUESTION 1
Your company has many Microsoft SQL Server instances hosted in a data center. You also manage five Microsoft Azure SQL Database instances that are hosted on a single server in Azure.
You need to minimize costs associated with Azure resources while maintaining the current performance levels of each Azure SQL Database instance.
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.

Answer:

Explanation: SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool

NEW QUESTION 2
DRAG DROP
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.

Answer:

Explanation: Box 1: Reporting
The user is to be created in the Reporting database.
Box 2: CREATE USER
Box 3: FOR LOGIN [BIAppUser]
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. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
References: https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure- database/

NEW QUESTION 3
You have a database named DB1 that contains a table named Table1. Tabe1 has a non-clustered index named index1.
You discover that index1 is corrupt. You need to repair index1.
Which statement should you execute?

• A. DBCC CHECKDB (‘db1’, REPAIR_FAST)
• B. ALTER INDEX indx1 ON table1 REBUILD WITH (ONLINE-ON)
• C. ALTER INDEX index1 ON table1 REORGANIZE
• D. DBCC CHECKDB (‘db1’, DATA_PURITY)

Answer: B

Explanation: If REBUILD is performed online (ON) the data in this table is available for queries and data modification during the index operation.

NEW QUESTION 4
You are deploying a Microsoft SQL Server database that will support a mixed OLTP and OLAP workload. The target virtual machine has four CPUs.
You need to ensure that reports do not use all available system resources. What should you do?

• A. Enable Auto Close.
• B. Increase the value for the Minimum System Memory setting.
• C. Set MAXDOP to half the number of CPUs available.
• D. Increase the value for the Minimum Memory per query setting.

Answer: C

Explanation: When an instance of SQL Server runs on a computer that has more than one microprocessor or CPU, it detects the best degree of parallelism, that is, the number of processors employed to run a single statement, for each parallel plan execution. You can use the max degree of parallelism option to limit the number of processors to use in parallel plan execution.

NEW QUESTION 5
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 6
You administer a Microsoft SQL Server 2014 instance named SQL2012 that hosts an OLTP database of 1 terabyte in size.
The database is modified by users only from Monday through Friday from 09:00 hours to 17:00 hours. Users modify more than 30 percent of the data in the database during the week.
Backups are performed as shown in the following schedule:

The Finance department plans to execute a batch process every Saturday at 09:00 hours. This batch process will take a maximum of 8 hours to complete.
The batch process will update three tables that are 10 GB in size. The batch process will update these tables multiple times.
When the batch process completes, the Finance department runs a report to find out whether the batch process has completed correctly.
You need to ensure that if the Finance department disapproves the batch process, the batch operation can be rolled back in the minimum amount of time. What should you do on Saturday?

• A. Perform a differential backup at 08:59 hours.
• B. Record the LSN of the transaction log at 08:59 hour
• C. Perform a transaction log backup at17:01 hours.
• D. Create a database snapshot at 08:59 hours.
• E. Record the LSN of the transaction log at 08:59 hour
• F. Perform a transaction log backup at08:59 hours.
• G. Create a marked transaction in the transaction log at 08:59 hour
• H. Perform a transaction log backup at 17:01 hours.
• I. Create a marked transaction in the transaction log at 08:59 hour
• J. Perform a transaction log backup at 08:59 hours.

Answer: C

Explanation: References:https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-snapshots-sql-server

NEW QUESTION 7
You have a server named server1-contoso.database.windows.net that has a Microsoft Azure SQL database.
You need to create a group of Azure SQL databases that share the resources on server1-contoso.database.windows.net.
Which cmdlet should you run before you create the database?

• 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: D

Explanation: The New-AzureRmSqlElasticPool cmdlet creates an elastic database pool for an Azure SQL Database.
SQL Database elastic pools are a simple, cost-effective solution for managing and scaling multiple databases that have varying and unpredictable usage demands. The databases in an elastic pool are on a single Azure SQL Database server and share a set number of resources at a set price. Elastic pools in Azure SQL Database enable SaaS developers to optimize the price performance for a group of databases within a prescribed budget while delivering performance elasticity for each database.

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

Answer:

Explanation: Box 1: Domain Account
The service startup account defines the Microsoft Windows account in which SQL Server Agent runs and its network permissions. SQL Server Agent runs as a specified user account. You select an account for the SQL Server Agent service by using SQL Server Configuration Manager, where you can choose fromthe following options:
* Built-in account. You can choose from a list of the following built-in Windows service accounts: Local System account.
* This account. Lets you specify the Windows domain account in which the SQL Server Agent service runs.
Box2: Domain users
Microsoft recommends choosing a Windows user account that is not a member of the Windows Administrators group.
Box 3: Managed Service Accounts
When resources external to the SQL Server computer are needed, Microsoft recommends using a Managed Service Account (MSA), configured with the minimum privileges necessary.
Note: A Managed Service Account (MSA) can run services on a computer in a secure and easy to maintain manner, while maintaining the capability to connect to network resources as a specific user principal.
References:https://msdn.microsoft.com/en-us/library/ms191543.aspx

Topic 7, Mix Questions Set
Topic 7, Mix Questions Set

NEW QUESTION 9
You have a SQL Server 2021 database named DB1.
You plan to import a large number of records from a SQL Azure database to DB1.
You need to recommend a solution to minimize the amount of space used in the transaction log during the import operation.
What should you include in the recommendation?

• A. The bulk-logged recovery model
• B. The full recovery model
• C. A new partitioned table
• D. A new log file
• E. A new file group

Answer: A

Explanation: Compared to the full recovery model, which fully logs all transactions, the bulk-logged recovery model minimally logs bulk operations, although fully logging other transactions. The bulk-logged recovery model protects against media failure and, for bulk operations, provides the best performance and least log space usage.
Note: The bulk-logged recovery model is a special-purpose recovery model that should be used only intermittently to improve the performance of certain large-scale bulk operations, such as bulk imports of large amounts of data.
References: https://technet.microsoft.com/en-us/library/ms190692(v=sql.105).aspx

NEW QUESTION 10
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 30 storage accounts that each has one container. You create a VHD in each container. Does this meet the goal?

• A. Yes
• B. No

Answer: A

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 11
You administer a Microsoft SQL Server 2014.
A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60.
You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

• A. EXEC sp_who 60
• B. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
• C. EXEC sp_helpdb 60
• D. DBCC INPUTBUFFER (60)

Answer: A

Explanation: sp_who provides information about current users, sessions, and processes in an instance of the Microsoft SQL Server Database Engine. The information can be filtered to return only those processes that are not idle, that belong to a specific user, or that belong to a specific session.
Example: Displaying a specific process identified by a session ID EXEC sp_who '10' --specifies the process_id;
References:https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-who-transact-

NEW QUESTION 12
You administer a SQL Server 2014 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?

• A. A domain user
• B. The BUILTINSYSTEM account
• C. A local user with Run as Service permissions
• D. The SQLBrowser account

Answer: A

Explanation: References:
https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/install/create-a-new-sql-server-failover-cluster-s

NEW QUESTION 13
Note: This questions 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.
Your company has several Microsoft Azure SQL Database instances.
Data encryption should be allowed to be implemented by the client applications that access the data. Encryption keys should not be made available to the database engine.
You need to configure the database. What should you implement?

• A. transport-level encryption
• B. cell-level encryption
• C. Transparent Data Encryption
• D. Always Encrypted
• E. Encrypting FileSystem
• F. BitLocker
• G. dynamic data masking

Answer: A

Explanation: Using encryption during transit with Azure File Shares
Azure File Storage supports HTTPS when using the REST API, but is more commonly used as an SMB file share attached to a VM.
HTTPS is a transport-level security protocol.

NEW QUESTION 14
HOTSPOT
You need to ensure that a user named Admin2 can manage logins.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer Area

Answer:

Explanation: Step 1: CREATE LOGIN
First you need to create a login for SQL Azure, it's syntax is as follows: CREATE LOGIN username WITH password='password';
Step 2, CREATE USER Step 3: LOGIN
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 4: loginmanager
Members of the loginmanager role can create new logins in the master database.
References:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/ https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins

NEW QUESTION 15
You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSOSQLService.
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.

• A. Restart the SQL Server Agent Service.
• B. Disable snapshot isolation.
• C. Restart the SQL Server Service.
• D. Add the CONTOSOSQLService account to the Perform Volume Maintenance Tasks local security policy.
• E. Add the CONTOSOSQLService account to the Server Operators fixed server role.
• F. Enable snapshot isolation.

Answer: CD

Explanation: How To Enable Instant File Initialization References:
http://msdn.microsoft.com/en-us/library/ms175935.aspx

NEW QUESTION 16
You administer a Microsoft SQL Server 2014 database named Contoso on a server named Server01.
You need to write messages to the Application Log when users are added to or removed from a fixed server
role in Server01.
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: G

Explanation: The SQL Server Audit feature enables you to audit server-level and database-level groups of events and individual events.
Audits can have the following categories of actions:
Server-level. These actions include server operations, such as management changes, such as in this question, and logon and logoff operations.
Database-level. These actions encompass data manipulation languages (DML) and data definition language (DDL) operations.
Audit-level. These actions include actions in the auditing process.
References:
http://technet.microsoft.com/en-us/library/cc280663(v=sql.105).aspx

NEW QUESTION 17
Settings Value VM size D3
Storage Location Drive E Storage type Standard Tempdb location Drive C
The workload on this instance has of the tembdb load.
You need to maximize the performance of the tempdb database.
Solution: You use a GS- Series VM and store the tempdb database on attached Premium storage. Does this meet the goal?

• A. Yes
• B. No

Answer: B

Explanation: For VMs that support Premium Storage (DS-series, DSv2-series, and GS-series), we recommend storing TempDB on a disk that supports Premium Storage with read caching enabled. There is one exception to this recommendation; if your TempDB usage is write-intensive, you can achieve higher performance by storing TempDB on the local D drive, which is also SSD-based on these machine sizes.
References:
https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performan

NEW QUESTION 18
You administer a Microsoft SQL Server 2014 environment. One of the SQL Server 2014 instances contains a database named Sales.
You plan to migrate Sales to Windows Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)

• A. Set database containment to AZURE.
• B. Enable server property contained database authentication.
• C. Disable server property cross db ownership chaining.
• D. Set database containment to PARTIAL.
• E. Disable server property contained database authentication.
• F. database containment to FULL.

Answer: BD

Explanation: A contained database is a database that is isolated from other databases and from the instance of SQL Server that hosts the database.
B: In the contained database user model, the login in the master database is not present. Instead, the authentication process occurs at the user database, and the database user in the user database does not have an associated login in the master database.
SQL Database and SQL Data Warehouse support Azure Active Directory identities as contained database users.
D: The contained database feature is currently available only in a partially contained state. A partially contained database is a contained database that allows the use of uncontained features.
References: https://docs.microsoft.com/en-us/sql/relational-databases/databases/contained-databases

NEW QUESTION 19
You manage a Microsoft SQL Server environment in a Microsoft Azure virtual machine. You must enable Always Encrypted for columns in a database.
You need to configure the key store provider.
What should you do?

• A. Manually specify the column master key.
• B. Modify the connection string for applications.
• C. Auto-generate a column master key.
• D. Use theWindows certificate store.

Answer: D

Explanation: Always Encrypted supports multiple key stores for storing Always Encrypted column master keys. A column master key can be a certificate stored in Windows Certificate Store.
References:https://msdn.microsoft.com/en-us/library/mt723359.aspx