Exam Code: 70-447 (Practice Exam Latest Test Questions VCE PDF)
Exam Name: UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass 70-447 Exam.
2021 Mar 70-447 Study Guide Questions:
Q65. You need to review the current storage system and decide on the best configuration for the tempdb database on Exambible A to improve tempdb performance. Your solution must ensure that company requirements and policies are fulfilled. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Create a RAID-1 set, and move tempdb to this drive set.
B. Create a RAID-0 set, and move tempdb to this drive set.
C. Set the starting size of tempdb to 3 GB.
D. Create a RAID-0 set, and move tempdb and the user database with the largest index to this drive set.
E. Disable automatic file growth on tempdb.
Q66. You are a database administrator for Exambible.com. Users report that the performance of a SQL Server 2005 application seems slow. You verify that the SQL Server computers CPU utilization, disk utilization, and memory utilization are normal. You also verify that the network is operating normally. You need to examine more detailed performance information for the appropriate instance of SQL Server 2005. What should you do?
A. Examine the distribution of the statistics in the databases that are hosted by the instance.
B. Examine the recovery model of the tempdb database.
C. Examine the appropriate dynamic management views (DMVs).
D. Examine the output of a statistics update command.
Q67. You are a database administrator for Exambible.com. You manage a database named Invoicing. The Invoicing database is backed up with a full backup nightly and transaction log backups once every two hours from 08:00 to 17:00. You implement database snapshots for the Invoicing database. A database snapshot is created each day at 07:00. At 09:30 today, a user inadvertently deleted all of the invoices that were entered into the CurrentInvoice table yesterday. None of these invoices had been changed today prior to the deletion. It is now 11:45 and many other changes have occurred in the database. You need to recover the lost rows with as few administrative steps as possible while minimizing data loss. What should you do?
A. Use the SELECT subquery in the INSERT statement to move the deleted rows from yesterday mornings database snapshot to the CurrentInvoice table.
B. Restore last nights Full Backup and all transaction log backups until the 10:00 backup.
Use the STOP AT statement on all restores to stop the restores at 09:29.
C. Use the SELECT subquery in the INSERT statement to move the deleted rows from this mornings database snapshot to the CurrentInvoice table.
D. Restore last nights Full Backup and all transaction log backups until the 10:00 backup.
Use the STOP AT statement on the last restore to stop the restore at 09:29.
Q68. You are planning the configuration of Exambible A. You need to use the most appropriate Windows account for the SQL Server service. Which account should you use?
A. a domain user account
B. a local user account
C. the NETWORK SERVICE account
D. the LOCAL SYSTEM account
Q69. You are the database administrator for Exambible.com. You have a single processor in your SQL Server 2005 computer. You run System Monitor and discover that your total processor utilization is averaging 95 percent. You need to use System Monitor to find out which application or service is causing the problem. What should you do?
A. Add each instance for Processor: % Privileged Time.
B. Add the _total instance for Process: % Processor Time.
C. Add each instance for Process: % Processor Time.
D. Add the _total instance for Processor: % Privileged Time.
E. Add the _total instance for Processor: % Processor Time.
Up to the immediate present 70-447 exam answers:
Q70. You need to design a method for improving performance for the reports that are run against the Orders database. What should you do?
A. Run the reporting queries by using the NOLOCK query hint.
B. Run the reporting queries by using the SERIALIZABLE transaction isolation level.
C. Use log shipping to copy the Orders data to a different SQL Server computer, and ensure that the reporting queries are run against the copied data.
D. Set up continuous replication to copy the reporting data to a different SQL Server computer, and ensure that the reporting queries are run against the copied data.
Q71. You work as DBA at Exambible.com. You administer a SQL Server 2005 computer named Exambible A. The SQL Server service and the SQL Server Agent service are configured to start automatically. You need to configure Exambible A to audit all user names and application names that attempt to access information from Exambible A. Exambible A must perform auditing at all times, and the results must be kept for 10 years. What should you do?
A. Create a SQL Server Agent job to run the SQL Trace stored procedure. Configure the job to start when the SQL Server Agent service starts.
B. Set the Login auditing setting to Both failed and successful logins.
C. Disable the C2 Audit Tracing setting on the server properties.
D. Configure the server authentication mode as SQL Server and Windows Authentication mode.
Q72. You are the database administrator of a SQL Server 2005 computer running SQL Server 2005 Enterprise Edition. You support a database named OnlineSales. This database exists on the following three filegroups. The primary filegroupFG1, a read/write filegroup FG2, a read/only filegroup with historical data. The database is set to the Full Recovery model. You discover that a file from FG1 has been corrupted. FG1 is offline. All other filegroups are online. You need to restore FG1 while minimizing the time when no data is available. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A. Restore the transaction logs since the most recent full or filegroup backup.
B. Restore the most recent full database backup.
C. Restore the most recent backup of the FG1 filegroup.
D. Restore the FG2 filegroup.
E. Restore the most recent backup of the Primary filegroup.
Q73. You are the administrator of a SQL Server 2005 computer named SQL1. SQL1 stores confidential customer information in a database named Customers. The company's written security policy states that transmission of customer data between client computers and servers must always take place in a manner that supports authentication, non-repudiation, and encryption. Client computers that do not support the company's written security policy are denied data access. You need to ensure that the Customers database is in compliance with the company's security policy. What should you do?
A. Install a certificate on SQL1 from a Trusted Root Certification Authority.
B. Configure SQL1 to use a self-signed certificate.
C. Configure the local security policy of SQL1 to use the IPSec Server policy.
D. Encrypt all tables in the Customers database.
E. Enable C2 auditing on SQL1.
Q74. You are a database administrator for Exambible.com. You run SQL Server Profiler by using the SP:Recompile event class. You notice a large number of recompiles occurring. You need to identify the possible cause of the large number of recompiles. What is the most likely cause of the problem?
A. A database administrator created one new stored procedure.
B. Ad hoc batches are not being retrieved from the procedure cache.
C. The sp_executesql batches are not being retrieved from the procedure cache.
D. A database administrator executed UPDATE STATISTICS statements on all tables that are referenced by the most common stored procedures.
Top Quality 70-447 book:
Q75. You are designing the backup strategy for the BusinessData database. You need to ensure that the backup strategy fulfills the business requirements for minimizing data loss and database downtime. Which three backup methods should your strategy include? (Each correct answer presents part of the solution. Choose three.)
A. differential backup every three hours
B. full database backup once per day
C. transaction log backup every three hours
D. transaction log backup every 30 minutes
E. full database backup every 30 minutes
F. differential backup once per day
Q76. You work as DBA at Exambible.com. You administer two Windows Server 2003 computers named Exambible A and Exambible B. You install SQL Server 2005 on both Exambible A and Exambible B to host a new company database. Exambible A hosts a read-write copy of the company database in which all changes are made. Exambible B subscribes to a publication on Exambible A and is only used for reporting. A Windows domain administrator provides you with a domain user account named SQLSRV to use as the security context for the SQL Server services. A password policy of 42 days exists in the Default Domain Group Policy object (GPO). You install the database on Exambible A and Exambible B and configure replication. Everything works fine for six weeks, but then all SQL Server services fail. You need to correct the problem. What should you do?
A. Ask the Windows domain administrator to grant the Log on as service right to the SQLSRV domain user account.
B. Configure the SQLSRV domain user account with a new strong password. Configure the new password in the properties of each SQL Server service that failed.
C. Create a local user account on Exambible A named Exambible A and a local user account on Exambible B named Exambible B. Configure Exambible A and Exambible B to run under the context of the appropriate local user account.
D. Ask the Windows domain administrator to grant the SQLSRV domain user account membership in the Domain Admins group.
Q77. You are a database administrator for Exambible.com. The company uses a SQL Server 2005 database that includes a table named Inventory. The table contains a column named Price. A company policy states that the value in the Price column cannot be decreased by more than 10 percent in any single database operation. Updates to the Price column are made by various means, including by using ad hoc queries. You need to ensure that this company policy is enforced. What should you do?
A. On the Price column, create a foreign key constraint to a table that contains valid prices.
B. On the Price column, create a check constraint that requires a specified minimum value.
C. Create a trigger that rolls back changes to the Price column that violate company policy.
D. Create a stored procedure that disallows changes to the Price column that violate company policy.
Q78. You are a database administrator for Exambible.com. You create a SQL Server Integration Services (SSIS) package that contains confidential information. The package will be used by only two company employees. The package is stored in the msdb database. You need to ensure that only the appropriate employees can execute the package. You must accomplish this goal by using the least amount of administrative effort. What should you do?
A. Configure the package to use the Encrypt all with user key package protection level.
B. Configure the package to use the Encrypt all with password package protection level.
C. Save the package to a file. Encrypt the file by using the Encrypting File System (EFS).
D. Save the package to a SQL Server 2005 computer that has an IPSec Secure Server policy applied.
Q79. You are a database administrator for Exambible.com. You notice that the following Transact-SQL query is performing very slowly.
SELECT VideoTitle, UpcNum, RetailPrice, ReleaseDate FROM Srvideo.VideoTitle WITH (INDEX(0))
WHERE ReleaseDate BETWEEN '20050401' AND '20050510' There is a clustered index on the VideoTitle column. There is a nonclustered index on the ReleaseDate column that includes the UpcNum and RetailPrice columns. When you query the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table, the avg_fragmentation_in_percent is 10 percent. The graphical execution plan for the slow query is shown in the exhibit.
You need to ensure that the query performs quickly. What should you do?
A. Remove the query hint from the query.
B. Re-create the index on only the ReleaseDate column.
C. Rebuild all indexes on the VideoTitle table.
D. Change the query hint to force the optimizer to force a clustered index seek or a scan.
Q80. You are a database administrator for Exambible.com. You are creating a SQL Server 2005 database that will contain many tables to store contact information for customers, partners, and vendors. You need to ensure consistency in the data types that are used in all columns in the database tables. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
A. Create a DDL trigger that alerts you when the schema in a table changes.
B. Create a CHECK constraint to enforce values allowed in the columns in the database tables.
C. Create CLR user-defined types for use in the tables.
D. Create a DML trigger that alerts you when the data in a table changes.
E. Create Transact-SQL user-defined data types for use in the tables.