22 October, 2020
Most Recent 70-462 Free Practice Exam For Administering Microsoft SQL Server 2012 Databases Certification
Testking 70-462 Questions are updated and all 70-462 answers are verified by experts. Once you have completely prepared with our 70-462 exam prep kits you will be ready for the real 70-462 exam without a problem. We have Improve Microsoft 70-462 dumps study guide. PASSED 70-462 First attempt! Here What I Did.
Also have 70-462 free dumps questions for you:
Question 1
- (Exam Topic 3)
You plan to install Microsoft SQL Server 2012 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?
You plan to install Microsoft SQL Server 2012 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?
Question 2
- (Exam Topic 3)
You administer a Microsoft SQL Server 2012 server along with a Microsoft Azure SQL Database database. For both servers, you need to grant users the ability to create logins and create databases.
You need to determine which permission to grant users in each instance.
Which permission or permissions should you choose? (To answer, drag the appropriate permission or permissions to their corresponding instance type or types in the answer area.
Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
Does this meet the goal?
You administer a Microsoft SQL Server 2012 server along with a Microsoft Azure SQL Database database. For both servers, you need to grant users the ability to create logins and create databases.
You need to determine which permission to grant users in each instance.
Which permission or permissions should you choose? (To answer, drag the appropriate permission or permissions to their corresponding instance type or types in the answer area.
Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
Does this meet the goal?
Question 3
- (Exam Topic 5)
You have a server named SPS1 that has Microsoft SharePoint Server 2013 installed. You have several Power View reports that are created in Microsoft Excel 2016. You need to configure SPS1 to display the Power View reports.
Which two SQL Server components should you install on SPS1? Each correct answer presents part of the solution.
You have a server named SPS1 that has Microsoft SharePoint Server 2013 installed. You have several Power View reports that are created in Microsoft Excel 2016. You need to configure SPS1 to display the Power View reports.
Which two SQL Server components should you install on SPS1? Each correct answer presents part of the solution.
Question 4
- (Exam Topic 3)
You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales.
You plan to migrate Sales to Microsoft Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
You administer a Microsoft SQL Server 2012 environment. One of the SQL Server 2012 instances contains a database named Sales.
You plan to migrate Sales to Microsoft Azure SQL Database. To do so, you need to implement a contained database.
What should you do? (Each correct answer presents part of the solution. Choose all that apply.)
Question 5
- (Exam Topic 1)
You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
Which two actions should you perform? Each correct answer presents part of the solution.
You administer a Microsoft SQL Server database that has Trustworthy set to On. You create a stored procedure that returns database-level information from Dynamic Management Views.
You grant User1 access to execute the stored procedure.
You need to ensure that the stored procedure returns the required information when User1 executes the stored procedure. You need to achieve this goal by granting the minimum permissions required.
Which two actions should you perform? Each correct answer presents part of the solution.
Question 6
- (Exam Topic 1)
You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.
The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.
You need to migrate the database without affecting the production environment.
Which three 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.)
Solution:
Does this meet the goal?
You administer a Microsoft SQL Server 2012 environment that contains a production SQL Server 2005 instance named SQL2005 and a development SQL Server 2012 instance named SQL2012.
The development team develops a new application that uses the SQL Server 2012 functionality. You are planning to migrate a database from SQL2005 to SQL2012 so that the development team can test their new application.
You need to migrate the database without affecting the production environment.
Which three 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.)
Solution:
Does this meet the goal?
Question 7
- (Exam Topic 3)
You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?
You administer a SQL Server 2012 database instance.
You need to configure the SQL Server Database Engine service on a failover cluster. Which user account should you use?
Question 8
- (Exam Topic 5)
Which of the following is a valid upgrade path for SQL Server 2012?
Which of the following is a valid upgrade path for SQL Server 2012?
Question 9
- (Exam Topic 5)
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server database server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from the Regions table. Which Transact-SQL statement should you use?
Note: This question is part of a series of questions that use the same set of answer choices. An answer choice may be correct for more than one question in the series.
You administer a SQL Server database server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales.
UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that UserA is disallowed to select from the Regions table. Which Transact-SQL statement should you use?
Question 10
- (Exam Topic 1)
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?
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?
Question 11
- (Exam Topic 4)
Which of the following is the default instance name in an SQL Server 2012 installation?
Which of the following is the default instance name in an SQL Server 2012 installation?
Question 12
- (Exam Topic 1)
You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster. Node B is primary, and Node A is secondary.
You need to install a security patch on both nodes.
You need to ensure that the following requirements are met:
✑ Both nodes receive the update.
✑ Downtime is minimized.
✑ No data is lost.
Which three 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.)
Solution:
Starting with the update to Microsoft SQL Server 2012 Service Pack 1, customers with SQL Server 2012 failover cluster instances can greatly benefit from the fully automated update process provided by Cluster-Aware Updating (CAU).
CAU causes minimal down time for SQL Server 2012 client connectivity. While the primary node is being updated, the SQL Server clustered role (also called the resource group) goes through two failovers. The first failover happens before updating the primary node when CAU brings the original primary node into maintenance mode. During the update process on the original primary node, the SQL clustered role is temporarily brought online on a new primary node. The second failover happens once the update process is complete on the original primary node, and CAU fails back the SQL Server clustered role to the original primary node and brings it online.
Does this meet the goal?
You administer a Microsoft SQL Server instance. You use a two-node SQL Server failover cluster. Node B is primary, and Node A is secondary.
You need to install a security patch on both nodes.
You need to ensure that the following requirements are met:
✑ Both nodes receive the update.
✑ Downtime is minimized.
✑ No data is lost.
Which three 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.)
Solution:
Starting with the update to Microsoft SQL Server 2012 Service Pack 1, customers with SQL Server 2012 failover cluster instances can greatly benefit from the fully automated update process provided by Cluster-Aware Updating (CAU).
CAU causes minimal down time for SQL Server 2012 client connectivity. While the primary node is being updated, the SQL Server clustered role (also called the resource group) goes through two failovers. The first failover happens before updating the primary node when CAU brings the original primary node into maintenance mode. During the update process on the original primary node, the SQL clustered role is temporarily brought online on a new primary node. The second failover happens once the update process is complete on the original primary node, and CAU fails back the SQL Server clustered role to the original primary node and brings it online.
Does this meet the goal?
Question 13
- (Exam Topic 2)
You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers. What should you do?
You administer two Microsoft SQL Server 2012 servers. Each server resides in a different, untrusted domain. You plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers. What should you do?
Question 14
- (Exam Topic 4)
Which of the following is required in order for a differential backup to be useful for recovery purposes?
Which of the following is required in order for a differential backup to be useful for recovery purposes?
Question 15
- (Exam Topic 5)
You administer a Microsoft SQL Server database.
Every Sunday afternoon, a process inserts a large volume of data. Users generate reports and modify additional data throughout the week.
You need to create a backup strategy that:
✑ minimizes the size of the transaction log,
✑ minimizes the size of the backups, and
✑ ensures a 24-hour recovery point objective (RPO).
Which strategy or strategies should you use? (To answer, drag the appropriate strategy or strategies to their corresponding task or tasks in the answer are answer: choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
Recovery Model: Full recovery model
Only the Full recovery model provides a 24-hour recovery point objective. Sunday backup: Full backup
Nightly backup: Transactional log backup
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server
Does this meet the goal?
You administer a Microsoft SQL Server database.
Every Sunday afternoon, a process inserts a large volume of data. Users generate reports and modify additional data throughout the week.
You need to create a backup strategy that:
✑ minimizes the size of the transaction log,
✑ minimizes the size of the backups, and
✑ ensures a 24-hour recovery point objective (RPO).
Which strategy or strategies should you use? (To answer, drag the appropriate strategy or strategies to their corresponding task or tasks in the answer are answer: choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
Recovery Model: Full recovery model
Only the Full recovery model provides a 24-hour recovery point objective. Sunday backup: Full backup
Nightly backup: Transactional log backup
References:
https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server
Does this meet the goal?
Question 16
- (Exam Topic 5)
Which of the following is NOT a hardware requirement for SQL Server 2012?
Which of the following is NOT a hardware requirement for SQL Server 2012?
Question 17
- (Exam Topic 5)
You administer a Microsoft SQL Server instance that contains a database of confidential data. You need to enable Transparent Data Encryption.
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.
Solution:
The steps to setup TDE are: Step 1: Create a master key
We must first create the master key. It must be created in the master database. Example:
USE master; GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '';
go
Step 2: Create or obtain a certificate protected by the master key
Once the master key is created, we will go ahead and create the actual certificate in the master database, not the user database.
Example:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
USE AdventureWorks2012; GO
Step 3: Create a database encryption key and protect it by the certificate
Now, we must utilize our USE command to switch to the database, the user database, that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database.
Example: USE GO
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert; GO
Step 4: Set the database to use encryption Example:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON; GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
Does this meet the goal?
You administer a Microsoft SQL Server instance that contains a database of confidential data. You need to enable Transparent Data Encryption.
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.
Solution:
The steps to setup TDE are: Step 1: Create a master key
We must first create the master key. It must be created in the master database. Example:
USE master; GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = '
go
Step 2: Create or obtain a certificate protected by the master key
Once the master key is created, we will go ahead and create the actual certificate in the master database, not the user database.
Example:
CREATE CERTIFICATE MyServerCert WITH SUBJECT = 'My DEK Certificate'; go
USE AdventureWorks2012; GO
Step 3: Create a database encryption key and protect it by the certificate
Now, we must utilize our USE command to switch to the database, the user database, that we wish to encrypt. Then we create a connection or association between the certificate that we just created and the actual database.
Example: USE
CREATE DATABASE ENCRYPTION KEY WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Cert; GO
Step 4: Set the database to use encryption Example:
ALTER DATABASE AdventureWorks2012
SET ENCRYPTION ON; GO
References:
https://docs.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption
Does this meet the goal?
Question 18
- (Exam Topic 4)
You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3.
You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?
You have recently removed a SQL 2012 Database Engine instance from a computer running the Windows Server 2008 R2 operating system. Prior to the removal of the instance, you had configured affinity so that the default instance used CPU 0 and 1 and the second instance used CPU 2 and 3.
You want to ensure that the default instance can use all processors available to the host. Which of the following commands would you use to accomplish this goal?
Question 19
- (Exam Topic 1)
You administer a Microsoft SQL Server instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
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?
You administer a Microsoft SQL Server instance that contains a financial database hosted on a storage area network (SAN).
The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
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?
Question 20
- (Exam Topic 5)
You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?
You administer a Microsoft SQL Server failover cluster that contains two nodes named Node A and Node B. A single instance of SQL Server is installed on the cluster.
An additional node named Node C has been added to the existing cluster.
You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?
Question 21
- (Exam Topic 5)
You administer a database named SalesDb that has users named UserA, UserB, and UserC.
You need to ensure that the following requirements are met:
✑ UserA must be able to provide Windows logins access to the database.
✑ UserB must be able to select, update, delete, and insert data to the database tablets.
✑ UserC must be able to create new tables and stored procedures.
You need to achieve this goal by granting only the minimum permissions required.
To which fixed database role or roles should you add the users? (To answer, drag the appropriate user or users to the correct database role or roles. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
UserA: db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins.
UserB: db_datareader, db_datawriter
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. UserC: db_ddladmin
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
Does this meet the goal?
You administer a database named SalesDb that has users named UserA, UserB, and UserC.
You need to ensure that the following requirements are met:
✑ UserA must be able to provide Windows logins access to the database.
✑ UserB must be able to select, update, delete, and insert data to the database tablets.
✑ UserC must be able to create new tables and stored procedures.
You need to achieve this goal by granting only the minimum permissions required.
To which fixed database role or roles should you add the users? (To answer, drag the appropriate user or users to the correct database role or roles. Answer choices may be used once, more than once, or not at all. Answer targets may be used once or not at all. Additionally, you may need to drag the split bar between panes or scroll to view content.)
Solution:
UserA: db_accessadmin
Members of the db_accessadmin fixed database role can add or remove access for Windows logins, Windows groups, and SQL Server logins.
UserB: db_datareader, db_datawriter
Members of the db_datareader fixed database role can run a SELECT statement against any table or view in the database.
Members of the db_datawriter fixed database role can add, delete, or change data in all user tables. UserC: db_ddladmin
Members of the db_ddladmin fixed database role can run any Data Definition Language (DDL) command in a database.
Does this meet the goal?
Question 22
- (Exam Topic 5)
Which of the following SQL Server 2012 features generates numeric values at a defined interval and order?
Which of the following SQL Server 2012 features generates numeric values at a defined interval and order?
Question 23
- (Exam Topic 5)
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserB? Select all that apply.
You administer a database that has User A, B and C.
User A must be able to create new tables and stored procedures, User B must be able to select, update, delete and insert data.
User C must be able to provide windows logins to the database
To which role or roles should you add to UserB? Select all that apply.
Question 24
- (Exam Topic 3)
You administer a Microsoft SQL Server database named Contoso that contains a single user-defined database role named BillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?
You administer a Microsoft SQL Server database named Contoso that contains a single user-defined database role named BillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permissions for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?
Question 25
- (Exam Topic 3)
You are a database developer for an application hosted on a Microsoft SQL Server 2012 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?
You are a database developer for an application hosted on a Microsoft SQL Server 2012 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?
Question 26
- (Exam Topic 2)
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?
You are creating an application that will connect to the AgentPortal database by using a SQL login named AgentPortalUser. Stored procedures in the database will use sp_send_dbmail to send email messages.
You create a user account in the msdb database for the AgentPortalUser login. You use the Database Mail Configuration Wizard to create a Database Mail profile. Security has not been configured for the Database Mail profile.
You need to ensure that AgentPortalUser can send email messages. What should you do?
Question 27
- (Exam Topic 5)
You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?
You administer a Microsoft SQL Server 2012 database named Contoso that contains a single user-defined database role namedBillingUsers.
All objects in Contoso are in the dbo schema.
You need to grant EXECUTE permission for all stored procedures in Contoso to BillingUsers. Which Transact-SQL statement should you use?
Question 28
- (Exam Topic 1)
You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks.
You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA.
Which three 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.)
Solution:
Does this meet the goal?
You administer two Microsoft SQL Server 2012 servers named ServerA and ServerB. You use a database named AdventureWorks.
You need to prepare the AdventureWorks database for database mirroring. ServerB will act as the mirror in a mirroring partnership along with ServerA.
Which three 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.)
Solution:
Does this meet the goal?
Question 29
- (Exam Topic 3)
You administer several Microsoft SQL Server 2012 servers.
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
What should you do? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Solution:
The Evaluation Mode defines the combination of 1) when the policy will be evaluated and 2) what the system will do in case of policy violation. The following evaluation modes are supported:
Does this meet the goal?
You administer several Microsoft SQL Server 2012 servers.
You want to run scheduled checks to confirm that the databases on the servers are not configured to AutoShrink.
You need to create a policy to check the condition.
What should you do? (To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.)
Solution:
The Evaluation Mode defines the combination of 1) when the policy will be evaluated and 2) what the system will do in case of policy violation. The following evaluation modes are supported:
Does this meet the goal?
Question 30
- (Exam Topic 5)
You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes. You install another server that has SQL Server installed.
Which three additional 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.
Solution:
Step 1: Add a new availability replica in asynchronous mode.
Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode.
Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-f
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-avail
Does this meet the goal?
You have a two-node SQL Server 2014 cluster that has an AlwaysOn availability group configured in synchronous mode.
You plan to provide a reporting solution by using a third node in the cluster.
You need to add the third node. The solution must prevent any impact on the performance of database writes. You install another server that has SQL Server installed.
Which three additional 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.
Solution:
Step 1: Add a new availability replica in asynchronous mode.
Asynchronous-commit mode minimizes transaction latency on the secondary databases but allows them to lag behind the primary databases, making some data loss possible.
Incorrect: Add a new availability replica in synchronous mode.
Synchronous-commit mode ensures that once a given secondary database is synchronized with the primary database, committed transactions are fully protected. This protection comes at the cost of increased transaction latency.
Step 2: Set Readable Secondary to Read-intent only.
For the secondary role, select a new value from the Readable secondary drop list, as follows: Read-intent only
Only read-only connections are allowed to secondary databases of this replica. The secondary database(s) are all available for read access.
Step 3: Configure read-only routing.
Note: Read-only routing refers to the ability of SQL Server to route qualifying read-only connection requests to an available Always On readable secondary replica (that is, a replica that is configured to allow read-only workloads when running under the secondary role). To support read-only routing, the availability group must possess an availability group listener. Read-only clients must direct their connection requests to this listener, and the client's connection strings must specify the application intent as "read-only." That is, they must be read-intent connection requests.
References:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-read-only-routing-f
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/overview-of-always-on-avail
Does this meet the goal?