Designing Database Solutions for Microsoft SQL Server
Question No: 61 – (Topic 5)
You have a server named Server1 that has 16 processors.
You plan to deploy multiple instances of SQL Server 2014 to Server1.
You need to recommend a method to allocate processors to each instance. What should you include in the recommendation?
More than one answer choice may achieve the goal. Select the BEST answer.
Windows System Resource Manager (WSRM)
Max Degree of Parallelism
Answer: A Explanation:
CPU affinity management through Windows System Resource Manager is not recommended for SQL Server multi-instance management. Instead, use the processor affinity settings in SQL Server.
Reference: Server Properties (Processors Page)
Question No: 62 DRAG DROP – (Topic 5)
You are the senior database administrator at Contoso, Ltd. You manage a SQL Server 2014 Instance, with multiple databases used for reporting.
You have recently hired a junior database administrator. You want this person to be able to
view the database structures on the server, but you do not want him or her to be able to make changes or see the data in the tables.
The new hire#39;s login credentials are as follows: Login name: JFree
You want the new hire to be required to change his password on his next login.
The code that is produced should execute no matter the initial database context in which it is started.
You need to write the code required to give the new hire only the desired access, using the smallest number of steps. Develop the solution by selecting and arranging the required code blocks in the correct order. You may not need all of the code blocks.
Applies to: SQL Server 2008 through SQL Server 2014.
Applies to SQL Server logins only. If this option is included, SQL Server prompts the user for a new password the first time the new login is used.
The VIEW DEFINITION permission lets a user see the metadata of the securable on which the permission is granted. However, VIEW DEFINITION permission does not confer access to the securable itself. For example, a user that is granted only VIEW DEFINITION permission on a table can see metadata related to the table in the sys.objects catalog view. However, without additional permissions such as SELECT or CONTROL, the user cannot read data from the table.
CREATE LOGIN (Transact-SQL) ALTER SERVER ROLE (Transact-SQL)
Question No: 63 HOTSPOT – (Topic 5)
You use SQL Server 2014. You create a table within a database by using the following DDL:
The following table illustrates a representative sample of data:
The system is expected to handle 50 million orders a month over the next five years. You have been instructed by your Team Lead to follow best practices for storage and
performance in the utilization of SPARSE columns.
Which columns should you designate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
Sparse columns are ordinary columns that have an optimized storage for null values.
Sparse columns reduce the space requirements for null values at the cost of more overhead to retrieve nonnull values. Consider using sparse columns when the space saved is at least 20 percent to 40 percent.
Question No: 64 DRAG DROP – (Topic 5)
You have an SQL Server 2014 server.
You plan to create four stored procedures that will use transactions. The stored procedures will be configured as shown in the following table.
You need to recommend an isolation level for each stored procedure. The solution must support the concurrency strategy of each stored procedure and must minimize locks.
What should you recommend? To answer, drag the appropriate isolation levels to the correct stored procedures. Each isolation level 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.
The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.
SP2: REPEATABLE READ
Specifies that statements cannot read data that has been modified but not yet committed by other transactions and that no other transactions can modify data that has been read by the current transaction until the current transaction completes.
SP3: READ COMMITTED
Specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads.
SERIALIZABLE specification include:
/ No other transactions can modify data that has been read by the current transaction until the current transaction completes.
Question No: 65 – (Topic 5)
You deploy a database by using SQL Server 2014. The database contains a table named Table1.
You need to recommend a solution to track all of the deletions executed on Table1. The solution must minimize the amount of custom code required.
What should you recommend?
Change data capture
Master Data Services
Answer: A Explanation:
Change data capture is designed to capture insert, update, and delete activity applied to SQL Server tables, and to make the details of the changes available in an easily consumed relational format. The change tables used by change data capture contain columns that mirror the column structure of a tracked source table, along with the metadata needed to understand the changes that have occurred.
Reference: About Change Data Capture (SQL Server)
Question No: 66 DRAG DROP – (Topic 5)
You are planning to deploy a database to Windows Azure SQL Database.
You need to design a stored procedure to update rows. The stored procedure must meet the following requirements:
->If the update fails, an error must be raised to the application and the update must
->The stored procedure must be designed to maximize concurrency.
What should you include in your design?
To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Box 1: Begin an explicit transaction. Box 2: Perform an update in a try block.
Box 3: Read the @@ROWCOUNT system variable.
Box 4: Raise an error and roll back the transaction if the row count is less than 1. Box 5: Commit the transaction in a finally block.
Read Committed is SQL Server#39;s default isolation level.
eturns the number of rows affected by the last statement.
Using TRY…CATCH in a transaction
The following example shows how a TRY…CATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.
– Generate a constraint violation error. DELETE FROM Production.Product WHERE ProductID = 980;
END TRY BEGIN CATCH SELECT
ERROR_NUMBER() AS ErrorNumber
,ERROR_SEVERITY() AS ErrorSeverity
,ERROR_STATE() AS ErrorState
,ERROR_PROCEDURE() AS ErrorProcedure
,ERROR_LINE() AS ErrorLine
,ERROR_MESSAGE() AS ErrorMessage;
IF @@TRANCOUNT gt; 0 ROLLBACK TRANSACTION; END CATCH;
IF @@TRANCOUNT gt; 0 COMMIT TRANSACTION; GO
Question No: 67 DRAG DROP – (Topic 5)
You plan to deploy SQL Server 2012.
You are designing two stored procedures named USP_1 and USP_2 that have the following requirements:
->Prevent data read by USP_1 from being modified by other active processes.
->Prevent USP_2 from performing dirty reads.
You need to recommend the isolation level for each stored procedure. The solution must maximize concurrency.
Which isolation levels should you recommend?
To answer, drag the appropriate isolation level to the correct stored procedure in the answer area.
This isolation level includes the guarantees given by SNAPSHOT isolation level. In addition, REPEATABLE READ guarantees that for any row that is read by the transaction, at the time the transaction commits the row has not been changed by any other transaction. Every read operation in the transaction is repeatable up to the end of the transaction.
Committed Read is SQL Server#39;s default isolation level. It ensures that an operation will never read data another application has changed but not yet committed.
Question No: 68 – (Topic 5)
You are the new database administrator for a SQL Server 2014 instance.
You conduct an assessment on the instance and determine that the auto create statistics setting on the database named DB1 has been turned off. You see no evidence that any maintenance has been occurring.
You want to set up monitoring to see if query performance is being affected.
You need to set up a monitoring process that will capture any cases where statistics could have been useful if they existed.
What should you do?
Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the primary key columns in the database.
Use the missing_column_statistics extended event.
Query the sys.statistics system view to see all cases where the statistics were last needed.
Write a query using the sys.dm_db_missing_index_group_stats DMV Joining to sys.indexes, filtering on is_hypothetical.
Answer: B Explanation:
The Missing Column Statistics event class indicates that column statistics that could have been useful for the optimizer are not available.
By monitoring the Missing Column Statistics event class, you can determine if there are statistics missing for a column used by a query. This can cause the optimizer to choose a less efficient query plan than expected.
Reference: Missing Column Statistics Event Class
Question No: 69 – (Topic 5)
You are creating a database that will store usernames and credit card numbers for an application.
You need to recommend a solution to store and reuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
Transparent Data Encryption (TDE)
Encrypting File System (EPS)
Answer: B Explanation:
If we are going to encrypt credit card number for storage, then we should have Data Encryption Key (DEK) for encrypting the credit card number.
Question No: 70 – (Topic 5)
You are the administrator for a SQL Server 2014 instance that stores the data for an online transaction processing sales system.
The company takes full backups every week; differential backups on the days with no full backups; and hourly transaction backups. These backups are stored on a backup server in the company#39;s data center.
Every week, the company places the full backup on a tape and sends it to a third-party backup storage system.
The company is worried that a disaster might occur that could destroy their computer center and cause them to lose orders.
You need to determine the best method for providing the smallest amount of data loss and downtime without leasing or purchasing additional physical locations.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
Set up SQL Server Always On with a SQL Azure database as a replica.
Set up SQL Server Always On by using a SQL Server on a Windows Azure Virtual Machine.
Put the differential backup on tape and send it to the third-party backup storage system.
Use the Microsoft SQL Server Backup to Microsoft Windows Azure Tool to direct all backups to a different geographical location.
Answer: D Explanation:
SQL Server 2012 was the first version to provide the ability to back up databases to the Cloud, and SQL Server 2014 improves on the process.
Microsoft SQL Server Backup to Windows Azure Tool enables backup to Windows Azure Blob Storage and encrypts and compresses SQL Server backups stored locally or in the cloud.
Smart, Secure, Cost-Effective: SQL Server Back Up to Windows Azure – SQL Server Team Blog – Site Home – TechNet Blogs