Free Microsoft 070-450 Exam Dumps Questions & Answers

You are a professional level SQL Sever 2008 Database Administrator.
They are respectively named Ins01, Ins02, Ins03 and Ins04. A single database application is hosted by each instance. All four instances should be removed to a new SQL Server failover cluster. You can see the configuration of the four instances from the table listed below:

All the four instances are sufficiently optimized, and there are no spare CPU cycles or extra memory in
the instances.
All four databases will be hosted by the new cluster on a single virtual cluster IP address.
Since you are the technical support, you are required to set the new cluster to handle the workload of all the database applications with the least amount of hardware resources.
From the four cluster configuration, which one should be utilized?

• A.Four-node active/active/active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are contained by each node.
• B.Two-node active/active cluster should be utilized and a minimum of 9 processors and a 25-GB memory are contained by each node.
• C.Four-node active/active/active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are contained by each node.
• D.Two-node active/passive cluster should be utilized and a minimum of 18 processors and a 50-GB memory are contained by each node.

You are a professional level SQL Sever 2008 Database Administrator.
A mission-critical database is shared by the five sites.
According to the business requirements, the users at each site should be enabled to access and change data on all sites with minimal latency. In addition, data loss should be minimized if a server fails. A high-availability solution should be designed, and the business requirements should be satisfied.
Which action should be included in your solution?

• A.Log shipping to servers at two of the sites to offer read-only replications of data should be included.
• B.Failover clustering should be included.
• C.Peer-to-Peer replication should be included.
• D.Asynchronous database mirroring without a witness server should be included.

You administer a SQL Server 2008 infrastructure.
An instance contains a database that includes a large table named OrderDetails. The application queries
only execute
DML statements on the last three months data. Administrative audits are conducted monthly on data older
than three months.
You discover the following performance problems in the database. The performance of the application
queries against the
OrderDetail table is poor. The maintenance tasks against the database, including index defragmentation,
take a long time.
You need to resolve the performance problems without affecting the server performance.
What should you do?

• A.Create an additional table named OrderDetailsHistory for data older than three months. Use the following Transact-SQL statement. CREATE TRIGGER trgMoveData ON OrderDetails AFTER INSERT AS INSERT INTO OrderDetailsHistory SELECT * FROM OrderDetails WHERE DATEDIFF( m,OrderDate,GETDATE ()) > 3
• B.Create an additional table named OrderDetailsHistory for data older than three months. Partition the OrderDetails and OrderDetailsHistory tables in two parts by using the OrderDatecolumn. Create a SQL Server Agent job that runs every month and uses the ALTER TABLE...SWITCH Transact-SQL statement to move data that is older than three months to the OrderDetailsHistory table.
• C.Create an additional table named OrderDetailsHistory for data older than three months. Create a SQL Server Agent job that runs the following Transact-SQL statement every month. INSERT INTO OrderDetailsHistory SELECT * FROM OrderDetails WHERE DATEDIFF( m,OrderDate,GETDATE ()) > 3
• D.Create a database snapshot for the OrderDetails table every three months. Modify the queries to use the current snapshot.

You are planning to upgrade a database application that uses merge replication.
The table currently has a column type of UNIQUEIDENTIFIER and has a DEFAULT constratin that uses
the NEWID() function.
A new version of the application requires that the FILESTREAM datatype be added to a table in the
database.
The data type will be used to store binary files. Some of the files will be larger than 2 GB in size.
While testing the upgrade, you discover that replication fails on the articles that contain the FILESTREAM
data.
You find out that the failure occurs when a file object is larger than 2 GB.
You need to ensure that merge replication will continue to function after the upgrade.
You also need to ensure that replication occurs without errors and has the best performance.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Place the table that will contain the FILESTREAM data type on a separate filegroup.
• B.Use the sp_changemergearticle stored procedure and set the @stream_blob_columns option to true for the table that will use the FILESTREAM data type.
• C.Drop and recreate the table that will use the FILESTREAM data type.
• D.Change the DEFAULT constraint to use the NEWSEQUENTIALID() function.

You are designing a database solution by using SQL Server 2008.
The database has a large number of tables that have columns that contain sensitive data. The sensitive data must be protected.
You need to choose an encryption strategy that has the minimum effect on the performance of the server.
What should you do? (More than one answer choice may achieve the goal. Select the BEST answer.)

• A.Use Transparent Data Encryption (TDE).
• B.Use Bitlocker Encryption.
• C.Use Encrypting File System.
• D.Use Cell-Level Encryption.