70-465 Exam Questions
164 real 70-465 exam questions with expert-verified answers and explanations. Page 1 of 4.
- Question #1Design and Implement a High Availability and Disaster Recovery Solution for SQL Server / Database Administration
Drag and Drop Question You need to recommend a backup process for an Online Transaction Processing (OLTP) database. The process must meet the following requirements: - Ensure that...
SQL Server Backup and RecoveryOLTP High AvailabilityLog ShippingRecovery Models - Question #3Design and implement database solutions for Azure SQL Database
Drag and Drop Question You are planning to deploy a database to Windows Azure SQL Database. You need to design a stored procedure to update rows. The stored procedure must meet the...
Stored ProceduresTransactionsError HandlingConcurrency - Question #4Design and implement database solutions for SQL Server
You are designing a monitoring application for a new SQL Server 2014 instance. You need to recommend a solution to generate a report that displays the 10 most frequent wait types t...
performance monitoringwait statisticsDMVs - Question #5Plan and implement a migration and upgrade strategy for SQL Server instances and databases while maintaining high availability and minimizing downtime.
Drag and Drop Question You have two servers named SQL1 and SQL2 that have SQL Server 2012 installed. SQL1 contains a database that is mirrored asynchronously to SQL2. The database...
SQL Server UpgradeDatabase MirroringHigh AvailabilityRolling Upgrade - Question #6Design and implement database solutions for SQL Server
You deploy a database by using SQL Server 2012. The database contains a table named Table1. You need to recommend a solution that meets the following requirements: - Stores the mos...
table partitioningstorage optimizationdata lifecycle management - Question #7Monitoring and Managing a SQL Server Environment - includes configuring alerts, triggers, and performance monitoring tools to meet operational requirements (Microsoft SQL Server 2014 certification: 70-462 / 70-464 domain)
Drag and Drop Question You plan to deploy SQL Server 2014. Your company identifies the following monitoring requirements for the database: - An e-mail message must be sent when a u...
SQL Server Agent AlertsLogon TriggersSQL Server MonitoringDatabase Administration - Question #8Design database security solutions
You are creating a database that will store usernames and passwords for an application. You need to recommend a solution to store the passwords in the database. What should you rec...
data encryptionpassword securityfile system security - Question #9Design and Implement Database Objects – specifically selecting appropriate data types to minimize storage footprint in Azure SQL Database schema design.
Drag and Drop Question You have a SQL Azure database named Database1. You need to design the schema for a table named table1. Table1 will have less than one million rows. Table1 wi...
Azure SQL DatabaseData TypesSchema DesignStorage Optimization - Question #10Implementing Database Objects / Managing Database Storage and Performance - specifically designing and implementing partitioned tables in SQL Server (commonly tested in Microsoft certifications such as DP-300 or 70-462/70-464).
Drag and Drop Question You need to recommend the actions that are required to partition a table. In which order should the four actions be performed? To answer, move the actions fr...
Table PartitioningSQL Server StorageDatabase DesignFilegroups - Question #11Design database security solutions
You have a SQL Server 2014 environment That contains 20 servers. The corporate security policy states that all SQL Server 2014 instances must meet specific security standards. You...
policy-based managementsecurity compliancemulti-server management - Question #12Design and implement database objects - specifically selecting appropriate SQL Server technologies for unstructured file storage and query performance optimization
Drag and Drop Question You are designing a database for a university. The database will contain two tables named Classes and Enrollment that have the following specifications: - Cl...
FileTableTable PartitioningFILESTREAMSQL Server Storage - Question #13Design and implement database solutions for SQL Server
You deploy a database by using SQL Server 2014. The database contains a table named Table1. You need to recommend a solution to track all of the deletions executed on Table1. The s...
change data capturedata auditingdata tracking - Question #14Design and implement database solutions for SQL Server
You have four databases that are accessed by using an Online Transaction Processing (OLTP) application. The databases are stored on a server named SQL1 that has SQL Server 2014 ins...
AlwaysOn Availability Groupshigh availabilitydisaster recoveryread replicas - Question #15Design and implement database solutions for SQL Server
You are troubleshooting an application that runs a query. The application frequently causes deadlocks. You need to identify which transaction causes the deadlock. What should you d...
deadlock troubleshootingextended eventsperformance monitoring - Question #16Design and implement database objects - specifically optimizing index storage strategies for partitioned tables in SQL Server to maximize query performance (Microsoft SQL Server 2012 / 70-465 or 70-462 certification domain: Database Design and Optimization)
Drag and Drop Question You have a server named SQL1 that has SQL Server 2012 installed. SQL1 hosts a database named Database1. Database1 contains a table named Table1. Table1 is pa...
SQL Server PartitioningIndex StrategyAligned vs Nonaligned IndexesDatabase Performance Optimization - Question #17Manage and Monitor SQL Server Instances - specifically identifying appropriate monitoring tools for system resource usage and deadlock detection in SQL Server 2014
Drag and Drop Question You plan to deploy SQL Server 2014. Your company identifies the following monitoring requirements: - Tempdb must be monitored for insufficient free space. -...
SQL Server MonitoringDynamic Management ViewsSQL Trace and Deadlock AnalysisTempdb Management - Question #18Design and implement database solutions for SQL Server
You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly avai...
high availabilitycost optimizationSQL Server editions - Question #19Design and implement database solutions for SQL Server
You are designing a database named DB1. Changes will be deployed to DB1 every Wednesday night. You need to recommend a strategy to deploy the changes to DB1. The strategy must meet...
database deploymentdatabase snapshotsrollback strategies - Question #20Design and implement database objects - specifically recommending appropriate transaction isolation levels to balance data consistency requirements with maximum concurrency in SQL Server 2014 (typically aligned with Microsoft 70-465 or 70-462 certification objectives).
Drag and Drop Question You plan to deploy SQL Server 2014. You are designing two stored procedures named SP1 and SP2 that have the following requirements: - Prevent data read by SP...
SQL Server Isolation LevelsConcurrency ControlStored ProceduresTransaction Management - Question #21Design and implement a security strategy for SQL Server, including authentication, authorization, and auditing mechanisms
Drag and Drop Question You are designing an authentication strategy for a new server that has SQL Server 2014 installed. The strategy must meet the following business requirements:...
SQL Server AuthenticationLogon TriggersLogin AuditingSecurity Strategy - Question #22Design and implement database solutions for SQL Server
You are designing a SQL Server database for an order fulfillment system. You create a table named Sales.Orders by using the following script: Each order is tracked by using one of...
database designdata modelingstatus tracking - Question #23Design and implement database solutions for SQL Server
You have two SQL Server 2012 instances named SQLDev and SQLProd. You plan to create a new database on SQLProd that will use SQL Server Authentication. You need to ensure that when...
contained databasedatabase portabilitySQL Server Authenticationuser management - Question #24Design and implement database solutions for SQL Server
Your company has offices in Seattle and Montreal. The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. The servers are located in separat...
AlwaysOn Availability Groupsautomatic failoversynchronous commithigh availability - Question #25Implementing SQL Server Security - specifically encryption mechanisms for data in transit and data at rest in SQL Server 2014 deployments
Drag and Drop Question You plan to deploy SQL Server 2014. You identify the following security requirements for the deployment: - Users must be prevented from intercepting and read...
SQL Server SecurityData EncryptionTDESSL/Network Encryption - Question #26Design and implement database solutions for SQL Server
You have a database named DB1. You plan to create a stored procedure that will insert rows into three different tables. Each insert must use the same identifying value for each tab...
sequence objectsidentity columnsstored proceduresdata generation - Question #27Design and implement database solutions for SQL Server
You plan to create a database. The database will be used by a Microsoft .NET application for a special event that will last for two days. During the event, data must be highly avai...
resource managementprocessor affinityperformance optimizationSQL Server configuration - Question #28Design and implement database solutions for Azure SQL Database
You are building a stored procedure for a SQL Azure database. The procedure will add multiple rows to a table. You need to design the stored procedure to meet the following require...
explicit transactionserror handlingXACT_ABORTstored procedures - Question #29Design and implement database solutions for SQL Server
You have a SQL Server 2012 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 amoun...
transaction logbulk-logged recovery modeldata importrecovery models - Question #30Design and implement database solutions for SQL Server
You have two SQL Server instances named SQLDev and SQLProd that have access to various storage media. You plan to synchronize SQLDev and SQLProd. You need to recommend a solution t...
data-tier applicationsDAC Pacschema synchronizationdatabase deployment - Question #31Design and implement database solutions for SQL Server
Your company has offices in Seattle and Montreal. The network contains two servers named Server1 and Server2 that have SQL Server 2012 installed. Server1 is located in the Seattle...
AlwaysOn Availability Groupsasynchronous commitmanual failoverhigh availability - Question #32Design and implement database solutions for SQL Server
You have a server that has SQL Server 2014 installed. The server contains 100 user databases. You need to recommend a backup solution for the user databases. The solution must meet...
maintenance plansbackup strategydatabase backupsSQL Server Agent - Question #33Design and implement database solutions - specifically recommending appropriate transaction isolation levels to balance data consistency requirements with maximum concurrency in Azure SQL environments.
Drag and Drop Question You plan to deploy a database to SQL Azure. You are designing two stored procedures named USP_1 and USP_2 that have the following requirements: - Prevent dat...
SQL AzureTransaction Isolation LevelsConcurrency ControlDatabase Design - Question #34Design and implement database solutions for SQL Server
You have a SQL Server 2014 instance named SQL1. SQL1 creates error events in the Windows Application event log. You need to recommend a solution that will run an application when S...
SQL Server Agentalertsjobsevent log monitoring - Question #35Design and implement database objects - specifically optimizing column storage using sparse and computed column features in SQL Server 2012 (Exam 70-465: Designing Database Solutions for SQL Server)
Drag and Drop Question You plan to deploy SQL Server 2012. You must create two tables named Table 1 and Table 2 that will have the following specifications: - Table1 will contain a...
SQL Server Storage OptimizationSparse ColumnsComputed ColumnsTable Design - Question #36Manage and Maintain SQL Server Databases - including implementing database policies, automating maintenance tasks, and understanding SQL Server feature capabilities
Drag and Drop Question You plan to deploy a database by using SQL Server 2014. Your company identifies the following requirements for the database: - The name of all stored procedu...
Policy-Based ManagementSQL Server Maintenance PlansDatabase AdministrationSQL Server 2014 - Question #38Design and implement database solutions for SQL Server
You manage a SQL Server 2014 instance that contains a database named DB1. Users report that some queries to DB1 take longer than expected. Although most queries run in less than on...
performance monitoringDMVsI/O performancedatabase files - Question #39Design database security solutions
You are creating a database that will store usernames and credit card numbers for an application. You need to recommend a solution to store and resuse the credit card numbers in th...
data encryptionsensitive datacredit card securitydata at rest - Question #40Design and implement database solutions for SQL Server
You need to recommend an isolation level for usp_UpdateOrderDetails. Which isolation level should you recommend?
transaction isolation levelsconcurrencydata consistencystored procedures - Question #41Design and implement database solutions for SQL Server
You need to recommend a solution for the deployment of SQL Server 2014. The solution must meet the business requirements. What should you include in the recommendation?
SQL Server deploymentFailover Clusteringhigh availabilitySQL Server 2014 - Question #42Design and implement database solutions for SQL Server
You need to recommend a solution to improve the performance of usp.UpdateInventory. The solution must minimize the amount of development effort. What should you include in the reco...
performance optimizationtable variablesstored proceduresT-SQL - Question #43Design and implement database solutions for SQL Server
You need to recommend a disk monitoring solution that meets the business requirements. What should you include in the recommendation?
disk monitoringDMVsperformance monitoringI/O performance - Question #44Design database security solutions
You need to recommend a solution for Application1 that meets the security requirements. What should you include in the recommendation?
Stored procedure securityCode signingDatabase permissions - Question #45Design database security solutions
You need to recommend a feature to support your backup solution. What should you include in the recommendation?
Transparent Data EncryptionData at rest encryptionBackup security - Question #46Design database security solutions
You need to recommend a solution to allow application users to perform UPDATE operations on the database tables. The solution must meet the business requirements. What should you r...
Stored procedure securityEXECUTE ASPermission management - Question #47Design and implement database solutions for SQL Server
You need to recommend a database reporting solution that meets the business requirements. What should you include in the recommendation?
SQL Server Data CollectorPerformance monitoringReporting infrastructure - Question #48Design and implement database solutions for SQL Server
You need to recommend a solution to synchronize Database2 to App1_Db1. What should you recommend?
Transactional replicationData synchronizationReplication - Question #49Design and implement database solutions for SQL Server
During performance testing, you discover that database INSERT operations against the Inventory table are slow. You need to recommend a solution to reduce the amount of time it take...
Index partitioningInsert performancePerformance tuningNonclustered indexes - Question #50Design and implement database solutions for SQL Server
You need to recommend changes to the ERP application to resolve the search issue. The solution must minimize the impact on other queries generated from the ERP application. What sh...
CollationString comparisonSearch performanceData types - Question #51Design and implement database solutions for SQL Server
You need to recommend a solution that addresses the index fragmentation and index width issue. What should you include in the recommendation? (Each correct answer presents part of...
Clustered index designIndex widthIndex fragmentationPerformance optimization - Question #52Design and implement database solutions for SQL Server
You need to recommend a solution that addresses the concurrency requirement. What should you recommend?
Concurrency controlDeadlock preventionTransaction managementStored procedure design