MICROSOFT NEWS: 70-459 Exam Questions has been Updated Today! Get Latest 70-459 VCE and 70-459 Book Instantly! Welcome to Download the Newest Braindump2go 70-459 VCE&70-459 PDF Dumps: http://www.braindump2go.com/70-459.html (125 Q&As)
Instant Download 70-459 PDF Files! New Updated 125 Exam Questions and Answers help 100% Exam Pass! 70-459 Certification Get Quickly!
Exam Code: 70-459
Exam Name: Transition Your MCITP: Database Administrator 2008 or MCITP: Database Developer 2008 to MCSE: Data Platform
Certification Provider: Microsoft
Corresponding Certifications: MCP, MCSA, MCSE
70-459 Dump,70-459 PDF,70-459 VCE,70-459 Braindump,70-459 Book,70-459 Certification,70-459 Exam Dumps,70-459 Exam Questions,70-459 eBook,70-459 Practice Exam,70-459 Practice Test,70-459 Preparation,70-459 Study Guide,70-459 Study Material,70-459 Training Kit
QUESTION 101
Drag and Drop Question
You have a SQL Server 2012 database named Database1.
Database1 hasa data file named database1_data.
mdf and a transaction log file named database1_Log.ldf.
Database1_Data.mdf is 1.5 GB. Database1_Log.ldf is 1.5 terabytes.
A full backup of Database1 is performed every day.
You need to reduce the size of the log file.
The solution must ensure that you can perform transaction log backups in the future.
Which code segment should you execute? To answer, move the appropriate code segments from the list of code segments to the answer area and arrange them in the correct order.
Answer:
Explanation:
http://technet.microsoft.com/en-us/library/ms190757.aspx
http://technet.microsoft.com/en-us/library/ms189493.aspx
http://technet.microsoft.com/en-us/library/ms365418.aspx
http://technet.microsoft.com/en-us/library/ms189272.aspx
http://technet.microsoft.com/en-us/library/ms179478.aspx
QUESTION 102
You have a SQL Server 2012 database named Database1.
You execute the following code:
You insert 3 million rows into Sales.
You need to reduce the amount of time it takes to execute Proc1.
What should you do?
A. Run the following:
ALTER TABLE Sales ALTER COLUMN OrderDate datetime NOT NULL;
B. Change the WHERE clause to the following:
WHERE OrderDate BETWEEN CAST(@date1,char(10))
AND CAST(@date2,char(10))
C. Remove the ORDER BY clause from the stored procedure.
D. Run the following:
DROP INDEX IX_Sales_OrderDate;
GO
CREATE INDEX IX_Sales_OrderDate ON Sales(OrderDate);
GO
Answer: D
Explanation:
http://www.c-sharpcorner.com/UploadFile/skumaar_mca/good-practices-to-write-the-stored-procedures-in-sqlserver/
QUESTION 103
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 Database.
Which method should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Create a SQL ServerIntegration Services (SSIS) package, and then run the package.
B. Back up the database, and then restore the database.
C. Extract a data-tier application, and then import the application.
D. Generate scripts to create all of the all database objects and all of the data, and then execute
the scripts by using SQL Azure.
Answer: A
Explanation:
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 complexworkflow 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 fortransferring the database schema, such as a Data-tier Application package.
SSIS for Azure and Hybrid Data Movement
QUESTION 104
You are the new database administrator for aSQL 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 upmonitoring 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?
A. Create a SQL Server Agent job to execute DBCC SHOWSTATISTICS on each of the
primary key columns in the database.
B. Use the missing_column_statistics extended event.
C. Query the sys.statistics system view to see all cases where the statistics were last needed.
D. 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.
Missing Column Statistics Event Class
QUESTION 105
You are troubleshooting an application that runs a query.
The application frequently causes deadlocks.
You need to identify the isolation level used by the query when a deadlock occurs.
What should you do? More than one answer choice may achieve the goal. Select the BEST answer.
A. Query the sys.dm_exec_requests dynamic management view.
B. Create a trace in SQL Server Profiler that contains the Deadlock graph event.
C. Query the sys.dm_exec_sessions dynamic management view.
D. Enable trace flag 1222, and then view the SQL Server error log.
Answer: C
Explanation:
* sys.dm_exec_sessions
Returns one row per authenticated session on SQL Server. sys.dm_exec_sessions is a server-scope view that shows information about all active user connections and internal tasks.
Include the column:
– transaction_isolation_level
– smallint
– Transaction isolation level of the session.
– 0 = Unspecified
– 1 = ReadUncomitted
– 2 = ReadCommitted
– 3 = Repeatable
– 4 = Serializable
– 5 = Snapshot
– Is not nullable.
– sys.dm_exec_sessions (Transact-SQL)
QUESTION 106
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 resuse the credit card numbers in the database.
What should you recommend? More than one answer choice may achieve the goal. Select the BEST answer.
A. Data encryption
B. Transparent Data Encryption (TDE)
C. Encrypting File System (EPS)
D. Data hashing
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.
http://msdn.microsoft.com/en-us/library/bb934049.aspx
QUESTION 107
You have a table named ORDERS that contains 10,514,003 Orders.
The ORDERS table has an IDENTITY (1,1) column named ORDERID.
ORDERID is the UNIQUE CLUSTERED INDEX and PRIMARY KEY for the table.
The first ORDERID is 1. There are no missing ORDERIDs in the set.
Based on table usage patterns, you decide to use partitioning on this table based off of the ORDERID column.
You need to create the following partitions:
Which code should you use to create the partition function?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: A
Explanation:
http://msdn.microsoft.com/en-us/library/ms187802.aspx
QUESTION 108
Drag and Drop Question
You administer a SQL Server 2014 instance.
The server is capable of 10000 IO/second (IOPS). During the time period when the second process executes, the disk IO can reach 7000 IOPS,and CPU use can average 30% over the eight processors.
The first process summarizes the day’s activity executed by a login of [SummaryReportLogin]. The second process submits transactions executed by a login of [ETLLogin].
A Resource Governor classifierfunction has been created to return WG_Low for connections from the [ETLLogin] and [SummaryReportLogin].
You need to set up the Resource Group and Workgroup Pools on the instance.
You have the following requirements:
– Both processes must never use more than 50 percent of the CPU at any one time.
– The number of active queries that these processes can execute simultaneously should be limited to a maximum of 10.
– The Summary Report Login process must always achieve the minimum IOPS required to be minimally affected during executing the ETLLogin processes.
Develop the solution by selecting and arranging the required code blocks in the correct order.
You may not need all of the code blocks.
Answer:
Explanation:
CREATE WORKLOAD RESOURCE POOL
* Resource pools. A resource pool, represents the physical resources of the server.
You can think of a pool as a virtual SQL Server instance inside of a SQL Server instance.
* Workload groups. A workload group serves as a container for session requests that have
similar classification criteria. A workload allows for aggregate monitoring of the sessions, and
defines policies for the sessions. Each workload group is in a resource pool.
* CAP_CPU_PERCENT =value
Specifies a hard cap on the CPU bandwidth that all requests in the resource pool will receive.
Limits the maximum CPU bandwidth level to be the same as the specified value. value is an
integer with a default setting of 100. The allowed range for value is from 1 through 100.
* MIN_IOPS_PER_VOLUME =value
Specifies the minimum I/O operations per second (IOPS) per disk volume to reserve for the
resource pool.
* GROUP_MAX_REQUESTS =value
Specifies the maximum number of simultaneous requests that are allowed to execute in the
workload group. value must be a 0 or a positive integer.
QUESTION 109
Hotspot Question
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 youdesignate as SPARSE? To answer, mark each column as SPARSE or NOT SPARSE in the answer area.
Answer:
Explanation:
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 110
Drag and Drop Question
You are the senior databaseadministrator 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.
Answer:
Explanation:
MUST_CHANGE
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.
Braindump2go Regular Updates of Microsoft 70-459 Preparation Materials Exam Dumps, with Accurate Answers, Keeps the Members One Step Ahead in the Real 70-459 Exam. Field Experts with more than 10 Years Experience in Certification Field work with us.
FREE DOWNLOAD: NEW UPDATED 70-459 PDF Dumps & 70-459 VCE Dumps from Braindump2go: http://www.braindump2go.com/70-459.html (125 Q&A)