Microsoft 70-764 Online Practice Questions and Exam Preparation

Microsoft 70-764 Online Questions & Answers

• Question 21:

  You are designing a Windows Azure SQL Database for an order fulfillment system. You create a table named Sales.Orders with the following script.

  

  Each order is tracked by using one of the following statuses:

  Fulfilled

  Shipped

  Ordered

  Received

  You need to design the database to ensure that that you can retrieve the following information:

  The current status of an order

  The previous status of an order.

  The date when the status changed.

  The solution must minimize storage.

  More than one answer choice may achieve the goal. Select the BEST answer.

  A. To the Sales.Orders table, add three columns named Status, PreviousStatus and ChangeDate. Update rows as the order status changes.
  B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.
  C. Implement change data capture on the Sales.Orders table.
  D. To the Sales.Orders table, add three columns named FulfilledDate, ShippedDate, and ReceivedDate. Update the value of each column from null to the appropriate date as the order status changes.
  B. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Status. Insert new rows into the table as the order status changes.

• Question 22:

  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.

  A company has a Microsoft SQL Server environment that has multiple databases. A database named DB1 has multiple online file groups. It is configured to use the full recovery model. A full backup is preformed nightly and transaction logs are performed on the hour. A large number of records are accidentally deleted at 17:20.

  You need to perform a point-in-time recovery. Which option should you use first?

  A. backup compression
  B. backup encryption
  C. file snapshot backup
  D. mirrored backup media sets
  E. SQL Server backup to URL
  F. SQL Server Managed Backup to Azure
  G. tail-log backup
  H. back up and truncate the transaction log
  G. tail-log backup

• Question 23:

  You administer a single server that contains a Microsoft SQL Server 2016 default instance.

  You plan to install a new application that requires the deployment of a database on the server. The application login requires sysadmin permissions.

  You need to ensure that the application login is unable to access other production databases.

  What should you do?

  A. Use the SQL Server default instance and configure an affinity mask.
  B. Install a new named SQL Server instance on the server.
  C. Use the SQL Server default instance and enable Contained Databases.
  D. Install a new default SQL Server instance on the server.
  B. Install a new named SQL Server instance on the server.

• Question 24:

  You administer all the deployments of Microsoft SQL Server 2016 in your company.

  You need to ensure that an OLTP database that includes up-to-the-minute reporting requirements can be off- loaded from the primary database to another server.

  You also need to be able to add indexes to the secondary database.

  Which configuration should you use?

  A. Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
  B. Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
  C. Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
  D. Two servers configured in different data centers SQL Server Availability Group configured in AsynchronousCommit Availability Mode
  E. Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
  F. SQL Server that includes an application database configured to perform transactional replication
  G. SQL Server that includes an application database configured to perform snapshot replication
  H. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
  F. SQL Server that includes an application database configured to perform transactional replication

• Question 25:

  You have a database named Saleshistory that records sales transactions for your organization. You create indexes in the database.

  The database has grown over time and now contains hundreds of indexes.

  You need to identify the indexes that are not being used.

  Which dynamic management object should you use?

  A. sys.dm_os_sys_info
  B. sys.dm_db_index_usage_stats
  C. sys.dm_db_index_operational_stats
  D. sys.dm_db_stats_properties
  B. sys.dm_db_index_usage_stats

• Question 26:

  You have 10 Microsoft SQL Server 2016 servers.

  You deploy a management data warehouse named DW1. You configure DW1 to gather all the performance data from the servers.

  You configure a Data Collector on a SQL server named SV1.

  You query the data warehouse on DW1 and discover that data from SV1 is unavailable.

  You need to ensure that you can review the performance data from SV1 when you query DW1.

  What should you do?

  A. Start the SQL Server Agent service on DW1.
  B. Execute the msdb.sp_syscollector_set_warehouse_connection_user stored procedure on SV1.
  C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.
  D. Start the SQL Server Agent service on SV1.
  C. Execute the msdb.sp_syscollector_enable_collector stored procedure on DW1.

• Question 27:

  You plan to integrate an on-premises Microsoft SQL Server environment with Microsoft Azure.

  You need to create the authentication object so that you can connect to Azure.

  Which Windows PowerShell command or commands should you run?

  A. $SecureString = ConvertTo-SecureString “Pa$$w0rd” -AsPlainText -Force New-SqlCredential -Name “AzureCred” -Identity “AzureStorage” -Secret $SecureString
  B. Invoke-Sqlcmd "CREATE EXTERNAL DATA SOURCE MyAzureStorage WITH (LOCATION = `wasbs://[email protected]/', CREDENTIAL = Pa$$w0rd)"
  C. Invoke-Sqlcmd "CREATE USER Azure_Active_Directory_principal FROM EXTERNAL PROVIDER WITHOUT LOGIN"
  D. New-SqlAzureVaultColumnMasterKeySettings -KeyUrl "https://myvault.vault.contoso.net:443/keys/CMK/4c05f1a41b12488f9cba2ea964b6a700"
  A. $SecureString = ConvertTo-SecureString “Pa$$w0rd” -AsPlainText -Force New-SqlCredential -Name “AzureCred” -Identity “AzureStorage” -Secret $SecureString

• Question 28:

  DRAG DROP

  A table named Table1 has a clustered columnstore index. The table contains over 10 billion records. This represents 12 years of sales data. The table is stored in a single filegroup.

  You drop the existing columnstore index. You allocate 13 new filegroups with files large enough to store the entire table.

  You need to implement a new columnstore index that supports maximum data compression for data older than 2 years.

  Which four 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.

  Select and Place:

  

  

  ---

  Step 1: Create a nonclustered index by year

  Step 2: Define a partitioning function

  A partition function must first be created in a CREATE PARTITION FUNCTION statement before creating a partition scheme.

  Step 3: Define a partitioning scheme

  The CREATE PARTITION SCHEME command creates a scheme in the current database that maps the partitions of a partitioned table or index to filegroups. The number and domain of the partitions of a partitioned table or index are

  determined in a partition function.

  Step 4: Create a partitioned clustered columnstore index. Use columnstore_archive data...

  For partitioned tables and indexes, you can configure the compression option for each partition, and the various partitions of an object do not have to have the same compression setting.

  For partitioned columnstore tables and columnstore indexes, you can configure the archival compression option for each partition, and the various partitions do not have to have the same archival compression setting.

  The partition scheme must already exist in the database when you create a columnstore index.

  References:

  https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017

  https://docs.microsoft.com/en-us/sql/t-sql/statements/create-partition-scheme-transact-sql?view=sql-server-2017

• Question 29:

  You have two databases named DB1 and DB2 that are located on the same server.

  You plan to create a stored procedure named SProc1 in DB1. SProc1 will query a table named Table2 in DB2. You need to recommend a solution to ensure that SProc1 can access Table2 without granting users direct access to Table2.

  What should you include in the recommendation? More than one answer choice may achieve the goal. Select the BEST answer.

  A. Contained databases
  B. Application roles
  C. Cross-database ownership chaining
  D. Digital certificates
  B. Application roles

• Question 30:

  You maintain Microsoft SQL Server instances named SVR1 and SVR2 that are hosted on two different servers. You configure log shipping between the two instances as follows:

  DB1 on SVR1 is configured as the primary database.

  DB1 on SVR2 is configured as the secondary database for DB1 on SVR1.

  No monitoring server is configured.

  You need to monitor error log messages about the copy job.

  What are two possible ways to achieve this goal? Each correct answer presents a complete solution. NOTE: Each correct selection is worth one point.

  A. On SVR1, run the following Transact-SQL statement: SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail.
  B. Use the job Activity Monitor in SQL Server Management Studio by connecting to SVR1
  C. View the Log Shipping Report in SQL Server Management Studio by connecting SVR1.
  D. Use the Job Activity Monitor in SQL Server Management Studio by connecting to SVR2.
  E. On SVR2 run the following Transact-SQL statement: SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail.
  C. View the Log Shipping Report in SQL Server Management Studio by connecting SVR1.
  E. On SVR2 run the following Transact-SQL statement: SELECT * FROM msdb.dbo.log_shipping_monitor_error_detail.