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.
Answer : A
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)
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's login credentials are as follows:
Login name: JFree -
Password: Jx672$qse -
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.
You use SQL Server 2014. You create a table within a database by using the following
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.
* SP1 SNAPSHOT
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.
* SP4: SERIALIZABLE
SERIALIZABLE specification include:
/ No other transactions can modify data that has been read by the current transaction until the current transaction completes.
You deploy a database by using SQL Server 2014. The database contains a table named
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?
Answer : A
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)
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 be discarded.
-> 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's default isolation level.
eturns the number of rows affected by the last statement.
* Using TRYCATCH in a transaction
The following example shows how a TRYCATCH block works inside a transaction. The statement inside the TRY block generates a constraint violation error.
BEGIN TRY -
-- Generate a constraint violation error.
DELETE FROM Production.Product -
WHERE ProductID = 980;
END TRY -
BEGIN CATCH -
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 > 0 -
IF @@TRANCOUNT > 0 -
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.
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?
Answer : B
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
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.
Answer : B
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.
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'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.
Answer : D
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
You create a stored procedure that retrieves all of the rows from a table named Table1.
You need to recommend a solution to ensure that all of the statements in the stored procedure can be executed if another transaction is modifying rows in Table1 simultaneously.
What should you recommend?
Answer : A
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.
Reference: Transaction Isolation Levels
You plan to deploy a database to SQL Azure.
You must create two tables named Table 1 and Table 2 that will have the following specifications:
-> Table1 will contain a date column named Column1 that will contain a null value approximately 80 percent of the time.
-> Table2 will contain a column named Column2 which is the product of two other columns in Table2.
Both Table1 and Table2 will contain over one million rows.
You need to recommend which options must be defined for the columns. The solution must minimize the time it takes to retrieve data from the tables.
Which options should you recommend?
To answer, drag the appropriate options to the correct column in the answer area.
You need to recommend the actions that are required to partition a table.
In which order should the four actions be performed?
To answer, move the actions from the list of actions to the answer area and arrange them in the correct order.
You have a database hosted on SQL Server 2012 R2. The database contains 5 million rows.
You need to recommend a repeatable method to migrate the database to SQL Azure.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
Answer : A
SQL Server Integration Services -
Most flexibility -
Data Transfer Efficiency: Good -
/ SSIS can be used to perform a broad range of data migration tasks. SSIS provides support for complex workflow and data transformation between the source and destination.
It is a good choice to transfer of data for databases that require many changes to work on
Microsoft Azure SQL Database. You can use SSIS data transfer packages with another mechanism for transferring the database schema, such as a Data-tier Application package.
SSIS for Azure and Hybrid Data Movement
Not D: Generate Scripts Wizard -
Has explicit option for Azure SQL Database scripts generation
Data Transfer Efficiency: Poor -
Good for smaller database -
/ Using the Generate Scripts wizard to migrate a SQL Server database to Azure SQL
Database should be limited to:
Teams who have experience with the wizard.
Migrating simple databases that need few schema changes to run on Azure SQL
Database. The scripts generated from the source database can be modified before being used to create the new version of the database on Azure SQL Database, but using a database project in the SQL Server Data Tools has richer support for making schema changes.
Migrating small databases that do not have much data. The wizard generates scripts that use insert statements instead of bulk copies to transfer the data. The insert statements can be throttled when the tables contain too much data, and are not as fast as bulk copies.
You have a server named SQL1 that has SQL Server 2014 installed. SQL1 hosts a database named Database! Database1 contains a table named Table1. Table1 is partitioned across five filegroups based on the TransactionType field.
The schema of Table1 is configured as shown in the following table.