Microsoft Provisioning SQL Databases - 070-765 Exam Questions
QUESTION NO: 1
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time.
What should you do?
You administer a Microsoft SQL Server 2014 server. One of the databases on the server supports a highly active OLTP application.
Users report abnormally long wait times when they submit data into the application.
You need to identify which queries are taking longer than 1 second to run over an extended period of time.
What should you do?
Correct Answer: E
QUESTION NO: 2
You need to ensure that a user named Admin2 can manage logins.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer Area
You need to ensure that a user named Admin2 can manage logins.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Answer Area
Correct Answer:
Explanation:
Step 1: CREATE LOGIN
First you need to create a login for SQL Azure, it's syntax is as follows:
CREATE LOGIN username WITH password='password';
Step 2, CREATE USER
Step 3: LOGIN
Users are created per database and are associated with logins. You must be connected to the database in where you want to create the user. In most cases, this is not the master database. Here is some sample Transact-SQL that creates a user:
CREATE USER readonlyuser FROM LOGIN readonlylogin;
Step 4: loginmanager
Members of the loginmanager role can create new logins in the master database.
References:
https://azure.microsoft.com/en-us/blog/adding-users-to-your-sql-azure-database/
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-logins
QUESTION NO: 3
You administer a SQL Server 2014 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 the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
You administer a SQL Server 2014 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 the Sales role, including UserA, is disallowed to select from any of the tables in the Customers schema.
Which Transact-SQL statement should you use?
Correct Answer: J
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 4
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases.
Which powershell cmdlet should you run?
You need to create an Elastic Database job to rebuild indexes across 10 Microsoft Azure SQL databases.
Which powershell cmdlet should you run?
Correct Answer: E
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 5
You administer a SQL Server 2014 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 the following requirements are met:
Which Transact-SQL statement should you use?
You administer a SQL Server 2014 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 the following requirements are met:
Which Transact-SQL statement should you use?
Correct Answer: E
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 6
You plan to migrate a Microsoft sql server instance between physical servers.
You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts.
Solutions: You restore the model database.
Does the solution meet the goal?
You plan to migrate a Microsoft sql server instance between physical servers.
You must migrate the metadata associated with the database instance.
You need to ensure that the new instance retains the existing jobs and alerts.
Solutions: You restore the model database.
Does the solution meet the goal?
Correct Answer: B
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 7
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the PARAMETER_SNIFFING option for the databases.
Does the solution meet the goal?
Note: This question is part of a series of questions that present the same scenario. Each question in the series contains a unique solution. Determine whether the solution meets stated goals.
You manage a Microsoft SQL Server environment with several databases.
You need to ensure that queries use statistical data and do not initialize values for local variables.
Solution: You enable the PARAMETER_SNIFFING option for the databases.
Does the solution meet the goal?
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 8
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You create a SQL Server Agent job by using the following statement.
You need to send an email message if the job fails.
Which stored procedure should you use?
You have Microsoft SQL Server on a Microsoft Azure virtual machine.
You create a SQL Server Agent job by using the following statement.
You need to send an email message if the job fails.
Which stored procedure should you use?
Correct Answer: A
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 9
You have been hired as a Database Consultant by ABC.com to design a SQL Server 2014 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2014 Online Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2014 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?
You have been hired as a Database Consultant by ABC.com to design a SQL Server 2014 database solution.
You are tasked with designing a scale-out and high-availability SQL Server 2014 Online Transaction Processing (OLTP) database solution that will maintain copies of data across two server instances.
Your solution must provide scale-out of read operations by distributing the reads from clients across two SQL Server 2014 nodes. The data in both SQL Server nodes needs to be indexed.
What should you include in your solution?
Correct Answer: E
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 10
You have an on-premises Microsoft SQL Server instance.
You create a database named DB1.
You need to configure DB1 to support In-Memory OLTP.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE; Each correct selection is worth one point.
You have an on-premises Microsoft SQL Server instance.
You create a database named DB1.
You need to configure DB1 to support In-Memory OLTP.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE; Each correct selection is worth one point.
Correct Answer: B,C
Explanation: Only visible for Pass4Test members. You can sign-up / login (it's free).
QUESTION NO: 11
A company has an on-premises Microsoft SQL Server environment with a SQL Server named SQL01.
You need to create a local sysadmin account on SQL01 NAMED Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
A company has an on-premises Microsoft SQL Server environment with a SQL Server named SQL01.
You need to create a local sysadmin account on SQL01 NAMED Admin1.
How should you complete the Transact-SQL statements? To answer, select the appropriate Transact-SQL statements? To answer, select the appropriate Transact-SQL segments in the answer area.
Correct Answer:
Reference:
https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/connect-to-sql-server-when-system-administrators-are-locked-out?view=sql-server-2017
