最新的Microsoft Administering Microsoft SQL Server 2012/2014 Databases - 070-462免費考試真題
You administer a Microsoft SQL Server database.
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.
You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.
Which Transact-SQL statement should you use?
You create an availability group named haContosoDbs. Your primary replica is available at Server01\Contoso01.
You need to configure the availability group to prevent data loss. In the event of a database failure, the designed secondary database must come online automatically.
Which Transact-SQL statement should you use?
正確答案: C
You are creating a SQL Server Master Data Services (MDS) model for a company.
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company.
Which hierarchy type should you use?
The source data for the company is stored in a single table that contains the manager-to-subordinate relationships.
You need to create a hierarchy representing the organizational structure of the company.
Which hierarchy type should you use?
正確答案: B
You use a contained database named ContosoDb within a domain.
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
You need to create a user who can log on to the ContosoDb database. You also need to ensure that you can port the database to different database servers within the domain without additional user account configurations.
Which type of user should you create?
正確答案: C
You want to simulate read, write, checkpoint, backup, sort, and read-ahead activities for your organization's SQL Server 2012 deployment.
Which of the following tools would you use to accomplish this goal?
Which of the following tools would you use to accomplish this goal?
正確答案: A
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 all transactions will be replicated to the secondary database.
Which backup option should you use?
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 all transactions will be replicated to the secondary database.
Which backup option should you use?
正確答案: L
You develop a database for a travel application.
You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information.
What should you do?
You need to design tables and other database objects.
You create the Airline_Schedules table.
You need to store the departure and arrival dates and times of flights along with time zone information.
What should you do?
正確答案: H
You have a server named SQL1 that hosts a reporting database named REPORTING. REPORTING contains data that is also stored in a production database.
You discover many blocking locks in REPORTING. The blocked queries are SELECT statements. The queries that hold the blocking locks are INSERT statements that wait on the WRITELOG wait type.
You need to prevent the INSERT statements from creating the blocking locks. The solution must also minimize the amount of time the INSERT statements wait on the WRITELOG wait type.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.
You discover many blocking locks in REPORTING. The blocked queries are SELECT statements. The queries that hold the blocking locks are INSERT statements that wait on the WRITELOG wait type.
You need to prevent the INSERT statements from creating the blocking locks. The solution must also minimize the amount of time the INSERT statements wait on the WRITELOG wait type.
Which two database settings should you modify? To answer, select the appropriate settings in the answer area.
正確答案:
Explanation
Is Read Committed Snapshot On: True
Snapshot isolation enhances concurrency for OLTP applications.
Understanding Snapshot Isolation and Row Versioning
Once snapshot isolation is enabled, updated row versions for each transaction are maintained in tempdb. A unique transaction sequence number identifies each transaction, and these unique numbers are recorded for each row version. The transaction works with the most recent row versions having a sequence number before the sequence number of the transaction. Newer row versions created after the transaction has begun are ignored by the transaction.
The term "snapshot" reflects the fact that all queries in the transaction see the same version, or snapshot, of the database, based on the state of the database at the moment in time when the transaction begins. No locks are acquired on the underlying data rows or data pages in a snapshot transaction, which permits other transactions to execute without being blocked by a prior uncompleted transaction. Transactions that modify data do not block transactions that read data, and transactions that read data do not block transactions that write data, as they normally would under the default READ COMMITTED isolation level in SQL Server. This non-blocking behavior also significantly reduces the likelihood of deadlocks for complex transactions.
References:
https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/snapshot-isolation-in-sql-server
You administer a Microsoft SQL Server 2012 server that has multiple databases.
You need to ensure that users are unable to create stored procedures that begin with sp_.
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.)
You need to ensure that users are unable to create stored procedures that begin with sp_.
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.)
正確答案:
Explanation
Step 1: Create a Condition named StoredProcNamingConvention by using the Stored Procedure facet that has a single expression. Set the Field to @Name, Operator to NOT LIKE, and Value to 'sp[_]%'.
Step 2: Create a Policy named StoredProcNamingPolicy Set the Check condition to StoredProcNamingConvention and Evaluation Mode to On Change: Prevent Step 3: Enable StoredProcNamingPolicy Policies are created and managed by using Management Studio. The process includes the following steps:
* Select a Policy-Based Management facet that contains the properties to be configured.
* Define a condition that specifies the state of a management facet.
* Define a policy that contains the condition, additional conditions that filter the target sets, and the evaluation mode.
* Check whether an instance of SQL Server is in compliance with the policy.
Evaluation modes
There are four evaluation modes, three of which can be automated:
* On demand. This mode evaluates the policy when directly specified by the user.
* On change: prevent. This automated mode uses DDL triggers to prevent policy violations.Important:If the nested triggers server configuration option is disabled, On change: prevent will not work correctly.
Policy-Based Management relies on DDL triggers to detect and roll back DDL operations that do not comply with policies that use this evaluation mode. Removing the Policy-Based Management DDL triggers or disabling nest triggers, will cause this evaluation mode to fail or perform unexpectedly.
* On change: log only. This automated mode uses event notification to evaluate a policy when a relevant change is made.
* On schedule. This automated mode uses a SQL Server Agent job to periodically evaluate a policy.
You administer a Microsoft SQL Server 2012 database that contains a table named AccountTransaction.
You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.
You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.
Which Transact-SQL batch should you use?
You discover that query performance on the table is poor due to fragmentation on the IDX_AccountTransaction_AccountCode non-clustered index.
You need to defragment the index. You also need to ensure that user queries are able to use the index during the defragmenting process.
Which Transact-SQL batch should you use?
正確答案: C