Microsoft Provisioning SQL Databases - 070-765 Exam Practice Test
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 segments in the answer area.
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 segments in the answer area.
Correct Answer:
Explanation:
B: First we create a login with the CREATE LOGIN command.
E: Then we add it to the sysadmin role.
1. To add a member to a fixed server role
2. In Object Explorer, connect to an instance of Database Engine.
3. On the Standard bar, click New Query.
Copy and paste the following example into the query window and click Execute.
ALTER SERVER ROLE diskadmin ADD [Domain\Juan] ;
GO
G: Finally we add a database user for the login we created.
References:
https://technet.microsoft.com/en-us/library/aa337562(v=sql.110).aspx
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?
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?
Correct Answer: D
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
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 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,E
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
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 LEGACY_CARDINALITY_ESTIMATION option for the databases.
Does the solution meet the goal?
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 LEGACY_CARDINALITY_ESTIMATION option for the databases.
Does the solution meet the goal?
Correct Answer: A
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
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?
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?
Correct Answer: A
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
You have a database named DB1 that contains a table named Tabie1. Table1 has a non-clustered index named index1.
You discover that index1 is corrupt.
You need to repair index1.
Which statement should you execute?
You discover that index1 is corrupt.
You need to repair index1.
Which statement should you execute?
Correct Answer: A
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
You have database that contains a 400-GB table that is read-only.
You need to enable the Stretch Database feature.
How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
You need to enable the Stretch Database feature.
How should you complete the statement? To answer, drag the appropriate values to the correct targets. Each value may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
NOTE: Each correct selection is worth one point.
Select and Place:
Correct Answer:
Explanation:
Section: Deploy and migrate applications
To configure an existing table for Stretch Database, run the ALTER TABLE command.
Here's an example that migrates the entire table and begins data migration immediately.
USE <Stretch-enabled database name>;
GO
ALTER TABLE <table name>
SET ( REMOTE_DATA_ARCHIVE = ON ( MIGRATION_STATE = OUTBOUND ) ) ;
GO
References:
https://docs.microsoft.com/en-us/sql/sql-server/stretch-database/enable-stretch-database-for-atable
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 section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to run a Transact-SQL script against a Microsoft Azure SQL database daily at 01:00.
Solution: You install the Elastic Database jobs PowerShell package, and then you create an Elastic Database job.
Does this meet the goal?
After you answer a question in this section, you will NOT be able to return to it. As a result, these questions will not appear in the review screen.
You need to run a Transact-SQL script against a Microsoft Azure SQL database daily at 01:00.
Solution: You install the Elastic Database jobs PowerShell package, and then you create an Elastic Database job.
Does this meet the goal?
Correct Answer: A
Vote an answer
You administer two Microsoft SQL Server 2014 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 plan to configure database mirroring.
You need to be able to create database mirroring endpoints on both servers.
What should you do?
Correct Answer: C
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).
You administer a Windows 2008 server hosting an instance of Microsoft SQL Server 2014 Standard Edition. The server hosts a database named Orders.
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?
Users report that a query that filters on OrderDate is taking an exceptionally long time. You discover that an index named IX_OrderDate on the CustomerOrder table is heavily fragmenteD.
You need to improve the performance of the IX_OrderDate index. The index should remain online during the operation.
Which Transact-SQL command should you use?
Correct Answer: C
Vote an answer
Explanation: Only visible for PassTestking members. You can sign-up / login (it's free).