Want to know features? Want to lear more about experience? Study . Gat a success with an absolute guarantee to pass Microsoft 70-765 (Provisioning SQL Databases (beta)) test on your first attempt.
Free demo questions for Microsoft 70-765 Exam Dumps Below:
NEW QUESTION 1
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:
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 ChangeDat
- B. Update rows as the order status changes.
- C. Create a new table named Sales.OrderStatus that contains three columns named OrderID, StatusDate, and Statu
- D. Insert new rows into the table as the order status changes.
- E. Implement change data capture on the Sales.Orders table.
- F. 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.
Explanation: This stores only the minimal information required.
NEW QUESTION 2
You plan to migrate a Microsoft SQL Server workload from an on-premises server to a Microsoft Azure virtual machine (VM). The current server contains 4 cores with an average CPU workload of 6 percent and a peak workload of 10 percent when using 2.4Ghz processors.
You gather the following metrics:
You need to design a SQL Server VM to support the migration while minimizing costs.
For each setting, which value should you use? To answer, select the appropriate storage option from each list in the answer area.
NOTE: Each correct selection is worth one point.
Explanation: Data drive: Premium Storage Transaction log drive: Standard Storage TempDB drive: Premium Storage
Note: A standard disk is expected to handle 500 IOPS or 60MB/s. A P10 Premium disk is expected to handle 500 IOPS.
A P20 Premium disk is expected to handle 2300 IOPS. A P30 Premium disk is expected to handle 5000 IOPS.
VM size: A3
Max data disk throughput is 8x500 IOPS
NEW QUESTION 3
You are a database administrator for a Microsoft SQL Server 2014 database named AdventureWorks2012. You create an Availability Group defined by the following schema. (Line numbers are included for reference
You need to implement an AlwaysOnAvailablity Group that will meet the following conditions:
Production transactions should be minimally affected. The secondary server should allow reporting queries to be performed. If the primary server goes offline, the secondary server should not automatically take over.
Which Transact-SQL statement should you insert at line 06?
- A. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS = READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433') PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE,READ_ONLY_ROUTING_LIST = NONE)
- B. AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
- C. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =READ_ONLY,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
- D. AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,FAILOVER_MODE = MANUALSECONDARY_ROLE (ALLOW_CONNECTIONS =YES,READ_ONLY_ROUTING_URL = 'TCP://SecondaryServer:1433')
Explanation: As production transaction should be MINIMALLY affected we should use asynchronous-commit mode.
NEW QUESTION 4
You administer a Microsoft SQL Server 2014 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements: CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!'
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; BACKUP CERTIFICATE TDE_Certificate TO FILE = ''d:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key',
ENCRYPTION BY PASSWORD = 'MyPassword1!'); CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location.
A hardware failure occurs and so a new server must be installed and configured.
After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database.
You need to be able to restore the database.
Which Transact-SQL statement should you use before attempting the restore?
- A. ALTER DATABASE Master SET ENCRYPTION OFF;
- B. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer'WITH PRIVATE KEY (FILE = 'D:\TDE_Certificate.key',DECRYPTION BY PASSWORD = 'MyPassword1!');
- C. CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate'; USE Orders;CREATE DATABASE ENCRYPTION KEYWITH ALGORITHM = AES_256ENCRYPTIONBY SERVER CERTIFICATE TDE_Certificate;
- D. CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer';
Explanation: The CREATE CERTIFICATE command adds a certificate to a database in SQL Server. Creating a certificate from a file
The following example creates a certificate in the database, loading the key pair from files. Code
USE AdventureWorks2012; CREATE CERTIFICATE Shipping11
FROM FILE = 'c:\Shipping\Certs\Shipping11.cer'
WITH PRIVATE KEY (FILE = 'c:\Shipping\Certs\Shipping11.pvk', DECRYPTION BY PASSWORD = 'sldkflk34et6gs%53#v00');
NEW QUESTION 5
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.
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.
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:
This database was migrated from SQL Server 2012 to SQL Server 2016. 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
This database was migrated from SQL Server 2012 to SQL Server 2016. 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
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 6
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 a development environment.
You need to provide storage to the environment that minimizes 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
NEW QUESTION 7
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));
NEW QUESTION 8
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 tuning the performance of a virtual machines that hosts a Microsoft SQL Server instance. The virtual machine originally had four CPU cores and now has 32 CPU cores.
The SQL Server instance uses the default settings and has an OLTP database named db1. The largest table in db1 is a key value store table named table1.
Several reports use the PIVOT statement and access more than 100 million rows in table1.
You discover that when the reports run, there are PAGELATCH_IO waits on PFS pages 2:1:1, 2:2:1, 2:3:1, and 2:4:1 within the tempdb database.
You need to prevent the PAGELATCH_IO waits from occurring. Solution: You add more files to db1.
Does this meet the goal?
- A. Yes
- B. No
Explanation: From SQL Server’s perspective, you can measure the I/O latency from sys.dm_os_wait_stats. If you consistently see high waiting for PAGELATCH_IO, you can benefit from a faster I/O subsystem for SQL Server.
A cause can be poor design of your database - you may wish to split out data located on 'hot pages', which are accessed frequently and which you might identify as the causes of your latch contention. For example, if you have a currency table with a data page containing 100 rows, of which 1 is updated per transaction and you have a transaction rate of 200/sec, you could see page latch queues of 100 or more. If each page latch wait costs just 5ms before clearing, this represents a full half-second delay for each update. In this case, splitting out the currency rows into different tables might prove more performant (if less normalized and logically structured).
NEW QUESTION 9
You have an on-premises server that runs Windows Server 2012 R2. The server has a Microsoft SQL Server 2016 instance that has one user database. The database is 2 TB.
Your company has a Win32 application installed on 1,000 computers. The application connects to the database by using a network name of server1.contoso.local.
You need to migrate the database to SQL Server 2016 on a Microsoft Azure virtual machine that runs Windows Server 2016. The solution must minimize outages to the application.
What should you do?
- A. Copy the database files and update the records in DNS.
- B. Implement an availability group and update the records in DNS.
- C. Implement database mirroring and update the records in DNS.
- D. Implement database mirroring and change the connection string.
Explanation: SQL Server high availability and disaster recovery (HADR) technologies that are supported in Azure include: References:
NEW QUESTION 10
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:
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>;
ALTER TABLE <table name>
SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ; GO
NEW QUESTION 11
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 Add-AzureRmMetricAlertRule cmdlet and specify the –MetricName ‘Network Out’ parameter.
Does this meet the goal?
- A. Yes
- B. No
NEW QUESTION 12
You have a SQL Server 2016 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
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.
NEW QUESTION 13
You administer a Microsoft SQL Server 2014 database.
The database contains a Product table created by using the following definition:
You need to ensure that the minimum amount of disk space is used to store the data in the Product table. What should you do?
- A. Convert all indexes to Column Store indexes.
- B. Implement Unicode Compression.
- C. Implement row-level compression.
- D. Implement page-level compression.
NEW QUESTION 14
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
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.
NEW QUESTION 15
You have on-premises database server.
The database files for a user database are configured as shown in the following table:
You plan to migrate the on-premises database server to Microsoft SQL Server on a Microsoft Azure virtual machine.
You need to provision storage for the virtual machine to meet the following requirements: Support the same configurations as the on-premises database server.
Provide a Service Level Agreement (SLA) for performance. Minimize costs.
Which type of storage should you provision for each file type? To answer, drag the appropriate storage types to the correct file types. Each storage type 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.
Explanation: Standard Storage has varying latencies and bandwidth and is only recommended for dev/test workloads. Production workloads should use Premium Storage.
NEW QUESTION 16
You use Resource Manager to deploy a new Microsoft SQL Server instance in a Microsoft Azure virtual machine (VM) that uses Premium storage. The combined initial size of the SQL Server user database files is expected to be over 200 gigabytes (GB). You must maximize performance for the database files and the log file.
You add the following additional drive volumes to the VM:
You have the following requirements:
You need to deploy the SQL instance.
In the table below, identify the drive where you must store each SQL Server file type. NOTE: Make only one selection in each column. Each correct selection is worth one point.
Explanation: Enable read caching on the disk(s) hosting the data files and TempDB.
Do not enable caching on disk(s) hosting the log file. Host caching is not used for log files.
Topic 2, Manage databases and instances
Recommend!! Get the Full 70-765 dumps in VCE and PDF From Certleader, Welcome to Download: https://www.certleader.com/70-765-dumps.html (New 209 Q&As Version)