70-764 Exam
Questions Ask for 70-462 exam preparation
Our pass rate is high to 98.9% and the similarity percentage between our 70-462 exam preparation study guide and real exam is 90% based on our seven-year educating experience. Do you want achievements in the Microsoft 70-462 exam dumps exam in just one try? I am currently studying for the Microsoft 70-462 questions and answers exam. Latest Microsoft 70-462 exam preparation Test exam practice questions and answers, Try Microsoft 70-462 exam preparation Brain Dumps First.
Q1. You administer a single server that contains a Microsoft SQL Server 2012 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.
Answer: B
Explanation:
I would have gone with Contained Databases, but the application requires sysadmin permissions.
Q2. DRAG DROP
You are configuring a new Microsoft SQL Server Always On Availability Group. You plan to configure a shared network location at \\DATA-CI1\SQL.
You need to create an availability group listener named AGL1 on port 1433.
In which order should you perform the actions? To answer, move all actions from the list of actions to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: Launch the Failover Cluster Manager and..
To support theAlways On availability groups feature, ensure that every computer that is to participate in one or more availability groups meets requirements including:
* Ensure that each computer is a node in a WSFC (Windows Server Failover Clustering).
Step 2: Add andconfigure the replica and…
All the server instances that host availability replicas for an availability group must use the same SQL Server collation.
Step 3: Enable the SQL Server 2021 Always On Availability Group feature.
Enable the Always On availability groups feature on each server instance that will host an availability replica for any availability group. On a given computer, you can enable as many server instances for Always On availability groups as your SQL Server installation supports.
Step 4: Create the Always On Availability Group and..
Using Transact-SQL to create or configure an availability group listener
Step 5: Select the Full data synchronization method and… References:
Q3. You have a database named DB1 that stores more than 700 gigabyte (GB) of data and serves millions of requests per hour.
Queries on DB1 are taking longer than normal to complete. You run the following Transact-SQL statement:
SELECT * FROM sys.database_query_store_options
You determine that the Query Store is in Read-Only mode.
You need to maximize the time that the Query Store is in Read-Write mode. Which Transact-SQL statement should you run?
A. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = ALL)
B. ALTER DATABASE DB1SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 50)
C. ALTER DATABASE DB1SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
D. ALTER DATABASE DB1SET QUERY_STORE (QUERY_CAPTURE_MODE = NONE)
Answer: C
Explanation:
Stale Query Threshold (Days): Time-based cleanup policy that controls the retention period of persisted runtime statistics and inactive queries.
By default, Query Store is configured to keep the data for 30 days which may be unnecessarily long for your scenario.
Avoid keeping historical data that you do not plan to use. This will reduce changes to read- only status. The size of Query Store data as well as the time to detect and mitigate the issue will be more predictable. Use Management Studio or the following script to configure time-based cleanup policy:
ALTER DATABASE [QueryStoreDB]
SET QUERY_STORE (CLEANUP_POLICY = (STALE_QUERY_THRESHOLD_DAYS = 14));
References:https://docs.microsoft.com/en-us/sql/relational-databases/performance/best-practice-with-the-query-store
Q4. DRAG DROP
You are the database administrator for a Microsoft SQL Server instance. You develop an Extended Events package to look for events related to application performance.
You need to change the event session to include SQL Server errors that are greater than error severity 15.
Which five Transact-SQL segments should you use to develop the solution? To answer, move the appropriate Transact-SQL segments from the list of Transact-SQL segments to the answer area and arrange them in the correct order.
Answer:
Explanation:
Step 1: ALTER EVENT SESSION Contoso1 ON SERVER
Step 2: ADD EVENT …
Step 3: (ACTION ... Step 4: WHERE...
Step 5: ) GO
Example: To start an Extended Events sessions in order to trap SQL Server errors with severity greater than 10,just run the following script:
CREATE EVENT SESSION [error_trap] ON SERVER
ADD EVENT sqlserver.error_reported (
ACTION
(package0.collect_system_time,package0.last_error,sqlserver.client_app_name,sqlserver.c lient_hostname,sqlserver.database_id,sqlserver.database_name,sqlserver.nt_username, sqlserver.plan_handle,sqlserver.query_hash,sqlserver.session_id,sqlserver.sql_text,sqlser ver.tsql_frame,sqlserver.tsql_stack,sqlserver.username)
WHERE ([severity]>10)
)
ADD TARGET package0.event_file (
SET filename=N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\XEvents\error_trap.xel'
) WITH (
STARTUP_STATE=OFF
) GO
Q5. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that data changes are sent to a non-SQL Server database server in near real time.
You also need to ensure that data on the primary server is unaffected. Which configuration should you use?
A. • SQL Server that includes an application database configured to perform transactional replication
B. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
C. • Two servers configured in different data centers
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
• One server configured as an Active Secondary
D. • SQL Server that includes an application database configured to perform snapshot replication
E. • 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
F. • Two servers configured on the same subnet
G. • Two servers configured in a Windows Failover Cluster in the same data center
• SQL Server configured as a clustered instance
H. • 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
Answer: A
Original Answer is A and I'll agree as it's the only one using Transactional Replication.
Must be to do with "You need to ensure that data changes are sent to a non-SQL Server database server in
near real time. "
Q6. Note: This question is part of a series of questions that use the same scenario. For your convenience, the scenario is repeated in each question. Each question presents a different goal and answer choices, but the text of the scenario is exactly the same in each question in this series.
You have five servers that run Microsoft Windows 2012 R2. Each server hosts a Microsoft SQL Server instance. The topology for the environment is shown in the following diagram.
You have an Always On Availability group named AG1. The details for AG1 are shown in the following table.
Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMain that is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
Instance4 and Instance5 are not part of AG1. Instance4 is engaged in heavy read-write I/O. Instance5 hosts a database named StagedExternal. A nightly BULK INSERT process loads
data into an empty table that has a rowstore clustered index and two nonclustered rowstore
indexes.
You must minimize the growth of the StagedExternal database log file during the BULK INSERT operations and perform point-in-time recovery after the BULK INSERT transaction. Changes made must not interrupt the log backup chain.
You plan to add a new instance named Instance6 to a datacenter that is geographically distant from Site1 and Site2. You must minimize latency between the nodes in AG1.
All databases use the full recovery model. All backups are written to the network location
\\SQLBackup\. A separate process copies backups to an offsite location. You should minimize both the time required to restore the databases and the space required to store backups. The recovery point objective (RPO) for each instance is shown in the following table.
Full backups of OperationsMain take longer than six hours to complete. All SQL Server backups use the keyword COMPRESSION.
You plan to deploy the following solutions to the environment. The solutions will access a database named DB1 that is part of AG1.
The wait statistics monitoring requirements for the instances are described in the following table.
You need to reduce the amount of time it takes to backup OperationsMain. What should you do?
A. Modify the backup script to use the keyword SKIP in the FILE_SNAPSHOT statement.
B. Modify the backup script to use the keyword SKIP in the WITH statement
C. Modify the backup script to use the keyword NO_COMPRESSION in the WITH statement.
D. Modify the full database backups script to stripe the backup across multiple backup files.
Answer: D
Explanation:
One of the filegroup is read_only should be as it only need to be backup up once. Partial backups are useful whenever you want to exclude read-only filegroups. A partial backup resembles a full database backup, but a partial backup does not contain all the filegroups. Instead, for a read-write database, a partial backup contains the data in the primary filegroup, every read-write filegroup, and, optionally, one or more read-only files. A partial backup of a read-only database contains only the primary filegroup.
From scenario: Instance1 experiences heavy read-write traffic. The instance hosts a database named OperationsMainthat is four terabytes (TB) in size. The database has multiple data files and filegroups. One of the filegroups is read_only and is half of the total database size.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/partial-backups-sql-server
Q7. 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 are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
Clients connect to databases by using line-of-business applications. Developers connect by using SQL Server Management Studio (SSMS).
You need to provide permissions to a service account that will be used to provision a new database for a client.
Which permission should you grant?
A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin
Answer: C
Explanation:
Members of the dbcreator fixed server role can create, alter, drop, and restore any database.
References:https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/server-level-roles
Q8. You administer a Microsoft SQL Server 2012 instance.
After a routine shutdown, the drive that contains tempdb fails.
You need to be able to start the SQL Server. What should you do?
A. Modify tempdb location in startup parameters.
B. Start SQL Server in minimal configuration mode.
C. Start SQL Server in single-user mode.
D. Configure SQL Server to bypass Windows application logging.
Answer: B
Explanation:
http://msdn.microsoft.com/en-us/library/ms186400.aspx
http://msdn.microsoft.com/en-us/library/ms345408.aspx
Q9. You administer all the deployments of Microsoft SQL Server 2012 in your company.
You need to ensure that an OLTP database that uses a storage area network (SAN) remains available if any of the servers fail.
You also need to minimize the amount of storage used by the database. Which configuration should you use?
A. • Two servers configured in different data centers
• One server configured as an Active Secondary
B. • SQL Server that includes an application database configured to perform transactional replication
C. • 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
D. • Two servers configured in different data centers
• SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
E. • 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
F. • Two servers configured on the same subnet
• SQL Server Availability Group configured in Synchronous-Commit Availability Mode
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
Answer: H
Q10. 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 are the database administrator for a company that hosts Microsoft SQL Server. You manage both on-premises and Microsoft Azure SQL Database environments.
You have a user database named HRDB that contains sensitive human resources data. The HRDB backup files must be encrypted.
You need to grant the correct permission to the service account that backs up the HRDB database.
Which permission should you grant?
A. DDLAdmin
B. db_datawriter
C. dbcreator
D. dbo
E. View Database State
F. View Server State
G. View Definition
H. sysadmin
Answer: G
Explanation:
Restoring the encrypted backup: SQL Server restore does not require any encryption parameters to be specified during restores. It does require that the certificate or the asymmetric key used to encrypt the backup file be available on the instance that you are restoring to. The user account performing the restore must have VIEW DEFINITION permissions on the certificate or key.
References: https://docs.microsoft.com/en-us/sql/relational-databases/backup- restore/backup-encryption