- (Exam Topic 2)
You administer a Microsoft SQL Server 2012 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 implement log shipping of the financial database to another SQL Server 2012 instance.
You decide to failover to this secondary database.
You need to ensure that all transactions will be replicated to the secondary database. Which backup option should you use?
Correct Answer:
K
- (Exam Topic 5)
Which of the following files keeps track of all transactions that occur in an SQL Server database?
Correct Answer:
A
References:
https://msdn.microsoft.com/en-us/library/ms190925(v=sql.110).aspx
- (Exam Topic 3)
You are a database administrator for a Microsoft SQL Server 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?
Correct Answer:
B
Peer-to-peer replication provides a scale-out and high-availability solution by maintaining copies of data across multiple server instances, also referred to as nodes. Built on the foundation of transactional replication, peer-to-peer replication propagates transactionally consistent changes in near real-time. This enables applications that require scale-out of read operations to distribute the reads from clients across multiple nodes. Because data is maintained across the nodes in near real-time, peer-to-peer replication provides data redundancy, which increases the availability of data.
Although peer-to-peer replication enables scaling out of read operations, write performance for the topology is like that for a single node.
References:
https://docs.microsoft.com/en-us/sql/relational-databases/replication/transactional/peer-to-peer-transactional-rep
- (Exam Topic 5)
You are the data steward for a Business Intelligence project.
You must identify duplicate rows stored in a SQL Server table and output discoveries to a CSV file. AData Quality Services (DQS) knowledge base has been created to support this project.
You need to produce the CSV file with the least amount of development effort. What should you do?
Correct Answer:
C
References:
http://msdn.microsoft.com/en-us/library/hh213052.aspx http://msdn.microsoft.com/en-us/library/ff877917.aspx
http://msdn.microsoft.com/enus/library/microsoft.masterdataservices.services.datacontracts.knowledgebase.aspx http://msdn.microsoft.com/en-us/library/bb895263.aspx
- (Exam Topic 5)
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.
Solution:
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
Does this meet the goal?
Correct Answer:
A