The latest Microsoft MCSA 70-765 exam dumps is in pass4itsure.com. We have many years of exam experience,
all of our exam databases are updated throughout the year, and pass4itsure Microsoft exam experts will modify the content
at any time based on customer feedback. 100% Guarantee. Easily pass the Microsoft MCSA 70-765 Exam, please select
Pass4itsure 70-765 PDF or 70-765 VCE. Microsoft’s official Testing center has released the following
questions and answers: https://www.pass4itsure.com/70-765.html
[PDF] Free Microsoft 70-765 dumps download from Google Drive:
https://drive.google.com/open?id=1ajP4QS_qHeOgaYPbL7mmkwpngg3QLkSh
[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1VBDzuasBbmByXUKyUaZejR3hFzTke722
Exam 70-765: Provisioning SQL Databases – Microsoft:
https://www.microsoft.com/en-us/learning/exam-70-765.aspx
Pass4itsure offers the latest Microsoft 70-765 practice test free of charge (32Q&As)
QUESTION 1
You are a database administrator for a Microsoft SQL Server 2014 environment.
You want to deploy a new application that will scale out the workload to at least five different SQL Server instances.
You need to ensure that for each copy of the database, users are able to read and write data that will then be synchronized between all of the database instances.
Which feature should you use?
A. Database Mirroring
B. Peer-to-Peer Replication
C. Log Shipping
D. Availability Groups
Correct Answer: B
Explanation
Explanation/Reference:
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication
propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in
near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
References:https://docs.microsoft.com/en-us/sql/relational- databases/replication/transactional/peer-to-peer-transactional-replication
QUESTION 2
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
Correct Answer: E
Explanation
Explanation/Reference:
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
QUESTION 3
You administer a Microsoft SQL Server database named Sales. The database is 3 terabytes in size.
The Sales database is configured as shown in the following table.
You discover that all files except Sales_2.ndf are corrupt.
You need to recover the corrupted data in the minimum amount of time.
What should you do?
A. Perform a file restore.
B. Perform a transaction log restore.
C. Perform a restore from a full backup.
D. Perform a filegroup restore.
Correct Answer: A
Explanation
Explanation/Reference:
In a file restore, the goal is to restore one or more damaged files without restoring the whole database.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/file- restores-simple-recovery-model
QUESTION 4
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
Correct Answer: A
Explanation
Explanation/Reference:
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
QUESTION 5
You plan to deploy an on-premises SQL Server 2014 database to Azure SQL Database.
You have the following requirements:
Maximum database size of 500 GB
A point-in-time-restore of 35 days
Maximum database transaction units (DTUs) of 500
You need to choose the correct service tier and performance level. Which service tier should you choose?
A. Standard S3
B. Premium P4C. Standard SO
D. Basic
Correct Answer: B
Explanation
Explanation/Reference:
You should choose Premium P4. The Premium tier is the highest Azure SQL Database tier offered. This tier is used for databases and application that require the highest level of performance and recovery. The P4 level supports a maximum
of 500 DTUs, a maximum database size of 500 GB, and a point-in-time-restore to anypoint in the last 35 days.
QUESTION 6
You administer a single server that contains a Microsoft SQL Server 2014 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.
Correct Answer: B
Explanation
Explanation/Reference:
References: https://docs.microsoft.com/en-us/sql/sql-server/install/work-with-multiple- versions-and-instances-of-sql-server
QUESTION 7
You manage an on-premises Microsoft SQL server that has a database named DB1.
An application named App1 retrieves customer information for DB1.
Users report that App1 takes an unacceptably long time to retrieve customer records.
You need to find queries that take longer than 400 ms to run.
Which statement should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Correct Answer: B
Explanation
Explanation/Reference:
Total_worker_time: Total amount of CPU time, reported in microseconds (but only accurate to milliseconds), that was consumed by executions of this plan since it was compiled.
QUESTION 8
You administer a Microsoft SQL Server 2014 server. The MSSQLSERVER service uses a domain account named CONTOSO\SQLService.
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 CONTOSO\SQLService account to the Perform Volume Maintenance Tasks local security policy.
E. Add the CONTOSO\SQLService account to the Server Operators fixed server role.
F. Enable snapshot isolation.
Correct Answer: CD
Explanation
Explanation/Reference:
How To Enable Instant File Initialization
http://msdn.microsoft.com/en-us/library/ms175935.aspx
QUESTION 9
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. Use the Randomized encryption type
B. Modify the connection string for applications.
C. Auto-generate a column master key.
D. Use the Azure Key Vault.
Correct Answer: D
Explanation
Explanation/Reference:
There are two high-level categories of key stores to consider – Local Key Stores, and Centralized Key Stores.
Centralized Key Stores – serve applications on multiple computers. An example of a centralized key store is Azure Key Vault.
Local Key Stores
References: https://docs.microsoft.com/en-us/sql/relational- databases/security/encryption/create-and-storecolumn-master-keys-always-encrypted
QUESTION 10
You administer a Microsoft SQL Server 2014 database.You need to ensure that the size of the transaction log file does not exceed 2 GB.
What should you do?
A. Execute sp_configure ‘max log size’, 2G.
B. use the ALTER DATABASE…SET LOGFILE command along with the maxsize parameter.
C. In SQL Server Management Studio, right-click the instance and select Database Settings. Set the maximum size of the file for the transaction log.
D. in SQL Server Management Studio, right-click the database, select Properties, and then click Files. Open the Transaction log Autogrowth window and set the maximum size of the file.
Correct Answer: B
Explanation
Explanation/Reference:
You can use the ALTER DATABASE (Transact-SQL) statement to manage the growth of a transaction log file
To control the maximum the size of a log file in KB, MB, GB, and TB units or to set growth to UNLIMITED, use the MAXSIZE option. However, there is no SET LOGFILE subcommand.
References: https://technet.microsoft.com/en-us/library/ms365418(v=sql.110).aspx#ControlGrowth
QUESTION 11
You plan to install Microsoft SQL Server 2014 for a web hosting company.
The company plans to host multiple web sites, each supported by a SQL Server database.
You need to select an edition of SQL Server that features backup compression of databases, basic data integration features, and low total cost of ownership.
Which edition should you choose?
A. Express Edition with Tools
B. Standard Edition
C. Web Edition
D. Express Edition with Advanced Services
Correct Answer: B
Explanation
QUESTION 12
You are the database administrator for your company. Your company has one main office and two branch offices. You plan to create three databases named DB1, DB2, and DB3 that will be hosted on one Azure SQL Database server. You
have the following requirements:
The main office must be able to connect to all three databases. The branch offices must be able to connect to DB2 and DB3. The branch offices must not be able to access DB1.
You need to configure transparent data encryption (TDE) for DB1. Which two actions should you perform?
Each correct answer presents part of the solution.
A. Run CREATE CERTIFICATE certl WITH Subject = TDE Cert1 on DB1.
B. Connect to DB1.
C. Run ALTER DATABASE DB1 SET ENCRYPTION ON;.
D. Connect to the master database.
E. Run CREATE MASTER KEY on the master database.
Correct Answer: BC
Explanation
Explanation/Reference:
You should connect to DB1. To encrypt DB1, you connect directly to DB1. When you connect to DB1.
You use your dbmanager or administrative credentials. You should run ALTER DATABASE DB1 SET ENCRYPTION ON. You use the ALTER DATABASE DB1 SET ENCRYPTION ON statement to encrypt the database. This is the statement
that turns on TDE for Azure SQL Database.
QUESTION 13
You create a new Microsoft Azure subscription.
You need to create a group of Azure SQL databases that share resources.
Which cmdlet should you run first?
A. New-AzureRmAvailabilitySet
B. New-AzureRmLoadBalancer
C. New-AzureRmSqlDatabaseSecondary
D. New-AzureRmSqlElasticPoolE. New-AzureRmVM
E. New-AzureRmSqlServer
F. New-AzureRmSqlDatabaseCopy
G. New-AzureRmSqlServerCommunicationLink
Correct Answer: D
Explanation
Explanation/Reference:
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 (elastic Database Transaction Units (eDTUs)) 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.
References: https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-pool
QUESTION 14
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
Correct Answer: B
Explanation
Explanation/Reference:
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/
QUESTION 15
You have a database named DB1 that uses simple recovery mode.
Full backups of DB1 are taken daily and DB1 is checked for corruption before each backup.
There was no corruption when the last backup was complete.
You run the sys.columns catalog view and discover corrupt pages.
You need to recover the database. The solution must minimize data loss.
What should you do?
A. Run RESTORE DATABASE WITH RECOVERY.
B. Run RESTORE DATABASE WITH PAGE.
C. Run DBCC CHECKDB and specify the REPAIR_ALLOW_DATA_LOSS parameter.
D. Run DBCC CHECKDB and specify the REPAIT_REBUILD parameter.
Correct Answer: B
ExplanationExplanation/Reference:
A page restore is intended for repairing isolated damaged pages. Restoring and recovering a few individual pages might be faster than a file restore, reducing the amount of data that is offline during a restore operation.
RESTORE DATABASE WITH PAGE
Restores individual pages. Page restore is available only under the full and bulk-logged recovery models.
References: https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements- transact-sql
QUESTION 16
You administer a Microsoft SQL Server 2014 database.
You have a SQL Server Agent job instance that runs using the service account. You have a job step within the job that requires elevated privileges.
You need to ensure that the job step can run using a different user account.
What should you use?
A. a schedule
B. an alert
C. an operator
D. a proxy
Correct Answer: D
Explanation
Explanation/Reference:
A SQL Server Agent proxy defines the security context for a job step. A proxy provides SQL Server Agent with access to the security credentials for a Microsoft Windows user. Each proxy can be associated with one or more subsystems. A
job step that uses the proxy can access the specified subsystems by using the security context of the Windows user. Before SQL Server Agent runs a job step that uses a proxy, SQL Server Agent impersonates the credentials defined in the
proxy, and then runs the job step by using that security context.
References:https://technet.microsoft.com/en-us/library/ms189064(v=sql.105).aspx
QUESTION 17
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));
Correct Answer: D
Explanation
QUESTION 18
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
Correct Answer: B
Explanation
Explanation/Reference:
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-failureconditionlevel-property-settings
QUESTION 19
You administer a Microsoft SQL Server 2016 instance.
You need to configure a new database to support FILETABLES.
What should you do? Choose all that apply.
A. Disable FILESTREAM on the Database.
B. Enable FILESTREAM on the Server Instance.
C. Configure the Database for Partial Containment.
D. Create a non-empty FILESTREAM file group.
E. Enable Contained Databases on the Server Instance.
F. Set the FILESTREAM directory name on the Database.
Correct Answer: BDF
Explanation
QUESTION 20
You administer a Microsoft SQL Server 2014 database instance. You create a new user named UserA.
You need to ensure that UserA is able to create SQL Server Agent jobs and execute SQL Server agent jobs owned by UserA
To which role should you add UserA?
A. DatabaseMailUserRole
B. ServerGroupAdministratorGroup
C. SQLAgentUserRole
D. Securityadmin
Correct Answer: C
Explanation
Explanation/Reference:
SQLAgentUserRole is the least privileged of the SQL Server Agent fixed database roles. It has permissions on only operators, local jobs, and job schedules. Members of SQLAgentUserRole have permissions on only local jobs and job
schedules that they own.
Members can create local jobs.
References:https://docs.microsoft.com/en-us/sql/ssms/agent/sql-server-agent-fixed- database-roles
QUESTION 21
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
Correct Answer: D
Explanation
Explanation/Reference:
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-2003
QUESTION 22
You are a database developer for an application hosted on a Microsoft SQL Server 2014 server. The database contains two tables that have the following definitions:
Global customers place orders from several countries. You need to view the country from which each customer has placed the most orders.
Which Transact-SQL query do you use?
A. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY COUNT(OrderAmount) DESC) AS
RnkFROM OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
B. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM(SELECT
C. CustomerID, c.CustomerName, o.ShippingCountry, RANK()OVER (PARTITION BY CustomerIDORDER BY COUNT(o.OrderAmount) ASC) AS RnkFROM Customer cINNER JOIN Orders oON c.CustomerID = o.CustomerIDGROUP BY
c.CustomerID,
D. CustomerName, o.ShippingCountry) cs WHERE Rnk = 1
E. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,RANK() OVER (PARTITION BY CustomerIDORDER BY OrderAmount DESC) AS RnkFROM
OrdersGROUP BY CustomerID, ShippingCountry) AS oON c.CustomerID = o.CustomerIDWHERE o.Rnk = 1
F. SELECT c.CustomerID, c.CustomerName, o.ShippingCountry FROM Customer cINNER JOIN(SELECT CustomerID, ShippingCountry,COUNT(OrderAmount) DESC) AS OrderAmountFROM OrdersGROUP BY CustomerID,
ShippingCountry) AS oON
G. CustomerID = o.CustomerIDORDER BY OrderAmount DESC
Correct Answer: A
Explanation
Explanation/Reference:
Use descending (DESC) ordering.
To order by the number of orders we use ORDER BY COUNT(OrderAmount). Finally a WHERE close is needed: WHERE o.Rnk = 1
QUESTION 23
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
Correct Answer: A
Explanation
Explanation/Reference:
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).
References: https://www.mssqltips.com/sqlservertip/3088/explanation-of-sql-server-io-and- latches/
QUESTION 24
You are the administrator for a SQL Server 2016 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.
Correct Answer: D
Explanation
Explanation/Reference:
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
QUESTION 25
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 have Microsoft SQL Server on a Microsoft Azure virtual machine that has a database named DB1.
You discover that DB1 experiences WRITE_LOG waits that are longer than 50 ms.
You need to reduce the WRITE_LOG wait time.Solution: Add additional log files to tempdb.
Does this meet the goal?A. Yes
B. No
Correct Answer: B
Explanation
Explanation/Reference:
In SQL Server, if we have a transactional based system and find a high WRITELOG wait type this is a performance bottleneck and can cause the transaction log file to grow rapidly and frequently.
It is being recommended to SQL server users that they must archive the log files on a separate disk for getting better performance.
References: https://atdhebuja.wordpress.com/2016/06/20/resolving-sql-server-transaction- log-waits/
QUESTION 26
You administer all the deployments of Microsoft SQL Server 2014 in your company. You have two servers in the same data center that hosts your production database.
You need to ensure that the database remains available if a catastrophic server failure or a disk failure occurs.
You also need to maintain transactional consistency of the data across both servers.
You need to achieve these goals without manual intervention.
Which configuration should you use?
A. Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
B. SQL Server that includes an application database configured to perform transactional replication
C. Two servers configured in the same data centerA 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 centersSQL Server Availability Group configured in Synchronous-Commit Availability Mode One server configured as an Active Secondary
E. Two servers configured in the same data centerSQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
F. Two servers configured in different data centersSQL Server Availability Group configured in Asynchronous-Commit Availability Mode
G. SQL Server that includes an application database configured to perform snapshot replication
H. Two servers configured on the same subnetSQL Server Availability Group configured in Synchronous-Commit Availability Mode
Correct Answer: H
Explanation
Explanation/Reference:
Always On availability groups supports two availability modes–asynchronous-commit mode and synchronous-commit mode
Synchronous-commit mode emphasizes high availability over performance, at the cost of increased transaction latency.
References:https://docs.microsoft.com/en-us/sql/database-engine/availability- groups/windows/availability-modes-always-on-availability-groups#SyncCommitAvMode
QUESTION 27
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 the backup size is as small as possible.
Which backup should you perform every two hours?
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
Correct Answer: J
Explanation
Explanation/Reference:
Minimally, you must have created at least one full backup before you can create any log backups. After that, the transaction log can be backed up at any time unless the log is already being backed up.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup- restore/transaction-log-backups-sql-server
QUESTION 28
You have an on-premises Microsoft SQL server that has a database named DB1. DB1 contains several tables that are stretched to Microsoft Azure.
From SQL Server Management Studio (SSMS), a junior database administrator accidentally deletes several rows from the Azure SQL database and breaks the connection to Azure.
You need to resume Stretch Database operations.
Which two stored procedures should you use? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. sys.sp_rda_reconcile_batch
B. sys.sp_rda_reconcile_indexes
C. sys.sp_rda_reauthorize_db
D. sys.sp_rda_reconcile_columns
E. sys.sp_rda_set_rpo_duration
Correct Answer: CD
Explanation
Explanation/Reference:
sys.sp_rda_reauthorize_db restores the authenticated connection between a local database enabled for Stretch and the remote database.
If you have accidentally deleted columns from the remote table, run sp_rda_reconcile_columns to add columns to the remote table that exist in the Stretch- enabled SQL Server table but not in the remote table.
QUESTION 29
You have a server named Serverl that is hosted in an Azure virtual machine. Server1 contains the following:
One instance of SQL Server 2016 Enterprise
10 databases
500 stored procedures
You have a database named Database1 that is hosted on Server1.
Database1 contains 100 queries that are executed dynamically from web applications.
You plan to remove data from the procedure cache on Database1.
You have the following requirements:
Changes to Database1 must not affect other databases that are hosted on Server1 Changes to Database1 must not affect the performance of queries that are stored in other databases.
The solution must minimize administrative effort. You need to remove the data from the procedure cache as quickly as possible.
What should you do?
A. Run DBCC FREEPROCCACHE.
B. Run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database 1.
C. Run DBCC DROPCLEANBUFFERS.D. Write a script that iterates through each stored procedure definition and add WITH RECOMPILE to the definition.
Correct Answer: B
Explanation
Explanation/Reference:
You should run ALTER DATABASE SCOPED CONFIGURATION CLEAR PROCEDURE CACHE in the context of Database! This statement lets you change the settings of a database without affecting other databases that are installed on the
instance of SQL Server 2016.
QUESTION 30
You use Microsoft SQL Server 2014 to develop a database application. You need to implement a computed column that references a lookup table by using an INNER JOIN against another table.
What should you do?
A. Reference a user-defined function within the computed column.
B. Create a BEFORE trigger that maintains the state of the computed column.
C. Add a default constraint to the computed column that implements hard-coded values.
D. Add a default constraint to the computed column that implements hard-coded CASE statements.
Correct Answer: A
Explanation
Explanation/Reference:
A common way to define a computed column is by using a user-defined function (UDF) to encapsulate the calculation logic.
References:https://blogs.msdn.microsoft.com/sqlcat/2011/11/28/a-computed-column- defined-with-a-user-defined-function-might-impact-query-performance/
QUESTION 31
HOTSPOT
Background
You manage the Microsoft SQL Server environment for a company that manufactures and sells automobile parts.
The environment includes the following servers: SRV1 and SRV2. SRV1 has 16 logical cores and hosts a SQL Server instance that supports a mission-critical application. The application has approximately 30,000 concurrent users and relies
heavily on the use of temporary tables.
The environment also includes the following databases: DB1, DB2, and Reporting. The Reporting database is protected with Transparent Data Encryption (TDE). You plan to migrate this database to a new server. You detach the database
and copy it to the new server.
You are performing tuning on a SQL Server database instance. The application which uses the database was written using an object relationship mapping (ORM) tool which maps tables as objects within the application code. There are 30
stored procedures that are regularly used by the application.
After reviewing the plan cache you have identified that a large number of simple queries are using parallelism, and that execution plans are not being kept in the plan cache for very long.
You review the properties of the instance (Click the Exhibit button).
Exhibit:
You need to set the size of the log files for the tempdb database on SRV1.
How should you complete the Transact-SQL statement? To answer, select the appropriate Transact-SQL segments in the answer area.
Explanation
Explanation/Reference:
The ALTER DATABASE with MODIFY FILE command can make a file size bigger (but not smaller).
Example:
ALTER DATABASE AdventureWorks2012
MODIFY FILE
(NAME = test1dat3,
SIZE = 200MB);
Note: MODIFY FILE
Specifies the file that should be modified. Only one <filespec> property can be changed at a time. NAME must always be specified in the <filespec> to identify the file to be modified.
If SIZE is specified, the new size must be larger than the current file size.
References: https://docs.microsoft.com/en-us/sql/relationaldatabases/security/encryption/move-a-tdeprotected-database-to-another-sql-server
QUESTION 32
HOTSPOT
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.
Explanation
Explanation/Reference:
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-practiceson-filestreamimplementations/
Conclusion:
Welcome here, above we provide free Microsoft MCSA 70-765 exam dumps and 70-765 PDF. I believe it must be very helpful to you,
free content can only help you to open the first step of learning.
If you want to pass Microsoft MCSA 70-765 for your first exam, select: Pass4itsure 70-765 PDF + 70-765 VCE mode.
100% effective, easy to pass the 70-765 exam: https://www.pass4itsure.com/70-765.html
[PDF] Free Microsoft 70-765 dumps download from Google Drive:
https://drive.google.com/open?id=1ajP4QS_qHeOgaYPbL7mmkwpngg3QLkSh
[PDF] Free Full Microsoft dumps download from Google Drive:
https://drive.google.com/open?id=1VBDzuasBbmByXUKyUaZejR3hFzTke722
Pass4itsure Promo Code 15% Off
related: http://www.downloadzpdf.com/latest-upload-cisco-210-260-dumps-exam/
