Free Cheat-test Samples and Demo Questions Download
Adobe exams Adobe
Apple exams Apple
Avaya exams Avaya
Check Point exams Check Point
Cisco exams Cisco
Citrix exams Citrix
CIW exams CIW
CompTIA exams CompTIA
CWNP exams CWNP
EC-Council exams EC-Council
EMC exams EMC
Exin exams Exin
Fortinet exams Fortinet
GIAC exams GIAC
Hitachi exams Hitachi
HP exams HP
IBM exams IBM
Isaca exams Isaca
ISC exams ISC
ISEB exams ISEB
Juniper exams Juniper
LPI exams LPI
McAfee exams McAfee
Microsoft exams Microsoft
Oracle exams Oracle
PMI exams PMI
Riverbed exams Riverbed
SNIA exams SAP
Sun exams SAS
Symantec exams Symantec
VMware exams VMware
All certification exams

Microsoft 70-447 Exam - Cheat-Test.com

Free 70-447 Sample Questions:

Q: 1
You work as DBA at XXYYinc.com. You administer two Windows Server 2003 computers named XXYYinc A and XXYYinc B. You install SQL Server 2005 on both XXYYinc A and XXYYinc B to host a new company database. XXYYinc A hosts a read-write copy of the company database in which all changes are made. XXYYinc B subscribes to a publication on XXYYinc 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 XXYYinc A and XXYYinc 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 XXYYinc A named XXYYinc A and a local user account
on XXYYinc B named XXYYinc B. Configure XXYYinc A and XXYYinc 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.

Answer: B

Q: 2
You are a database administrator of two SQL Server 2005 computers named XXYYinc A and XXYYinc B. You have a Microsoft .NET application that has been modified so that it now accesses a database on XXYYinc B in addition to XXYYinc A. You do not want the user application to connect directly to XXYYinc B. You need to enable the data retrieval from XXYYinc B while maintaining the ability to assign different permissions to different users who use the .NET application. What should you do?

A. Change the .NET application to define a new server connection to XXYYinc B.
B. Configure a linked server on XXYYinc A to point to XXYYinc B.
C. Change the stored procedures called by the .NET application to include the OPENXML command.
D. Configure a linked server on XXYYinc B to point to XXYYinc A.

Answer: B

Q: 3
You are a database administrator for XXYYinc.com. Your company uses a different company's application that is based on SQL Server 2005 Standard Edition. The application executes a query that uses an index query hint. The index query hint is not suitable for your environment, but you cannot modify the query. You need to force the application to use a different query execution plan. What should you do?

A. Create a plan guide for the query.
B. Clear the procedure cache.
C. Create a new covering index on the columns that the query uses.
D. Update the statistics for all of the indexes that the query uses.

Answer: A

Q: 4
You are a database administrator for XXYYinc.com. Your SQL Server 2005 computer contains one user database that holds sales transaction information. Users report that the queries and stored procedures that they use every day are taking progressively longer to execute. You also notice that the amount of free disk space on the SQL Server computer is decreasing. You need to create a maintenance plan to correct the performance and storage problems. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

A. In the SQL Server Maintenance Plan Wizard, use the Check Database Integrity option.
B. In the SQL Server Maintenance Plan Wizard, use the Reorganize Index option.
C. In the SQL Server Maintenance Plan Wizard, use the Shrink Database option.
D. In the SQL Server Maintenance Plan Wizard, use the Clean Up History option.
E. In the SQL Server Maintenance Plan Wizard, use the Execute SQL Server Agent Job option.

Answer: B, C

Q: 5
You are a database administrator for XXYYinc.com. You are responsible for a SQL Server 2005 database that has several indexes. You need to write a stored procedure that checks the indexes for fragmentation. Which Transact-SQL statement should you use?

A. DBCC INDEXDEFRAG
B. SELECT * FROM sys.dm_db_index_physical_stats
C. SELECT * FROM sys.indexes
D. DBCC DBREINDEX

Answer: B

Q: 6
You work as DBA at XXYYinc.com. You administer two SQL Server 2005 computers named XXYYinc A and XXYYinc B. XXYYinc A and XXYYinc B contain a copy of a database named Sales. The database is replicated between XXYYinc A and XXYYinc B by using transactional replication. A full backup of each database is performed every night. Transaction log backups are performed every hour. Replication latency is typically less than two minutes. One afternoon, the Sales database on XXYYinc A becomes corrupted. You are unable to repair the database. The Sales database on XXYYinc B is unaffected. You need to return the Sales database on XXYYinc A to normal operation as quickly as possible. You must ensure a minimum loss of data and minimal impact to users of either server. What should you do?

A. Perform a full database backup on XXYYinc B. Restore the backup to XXYYinc A.
B. Restore the most recent full database backup and all transaction logs made since the full backup was made.
C. Restore only the most recent transaction log backup.
D. Detach the Sales database on XXYYinc B. Copy the database file to XXYYinc A, and attach the database on both servers.

Answer: A

Q: 7
You are a database administrator for XXYYinc.com. The company runs a popular database-driven Web site against a SQL Server 2005 computer named XXYYinc B. You need to ensure a quick response time and appropriate audit trail in the event that XXYYinc B experiences excessive traffic due to denial-of-service (DoS) attacks. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A. Configure the new performance alert to start a SQL Server Profiler trace.
B. Create a new performance alert to monitor the Current Bandwidth counter.
C. Configure the new performance alert to start a Network Monitor capture.
D. Create a new performance alert to monitor the Bytes Total/sec counter.

Answer: C, D

Q: 8
You are a database administrator for XXYYinc.com. One of the databases on a SQL Server 2005 computer contains a stored procedure. Users run this stored procedure to import data into a table. The stored procedure needs to use the TRUNCATE TABLE command before importing new data into the table. However, the users who run the stored procedure do not have permission to truncate the table. You need to provide a way for the stored procedure to truncate the table before it imports new data. What should you do?

A. Configure the stored procedure to use the EXECUTE AS command.
B. Configure the stored procedure to be owned by the same database user as the table.
C. Assign the users DELETE permission in the table.
D. Add the users to the db_datawriter fixed database role.

Answer: A

Q: 9
You are a database administrator for XXYYinc.com. You have separate SQL Server 2005 development and production environments. You use the Business Intelligence Development Studio to create a SQL Server Integration Services (SSIS) package in your development environment. Then, you use the SSIS package to import data into your development environment from one of your company's trading partners. You need to deploy the SSIS package to your production environment. Your production environment uses different table names than your development environment. What should you do?

A. Save the SQL Server Integration Services (SSIS) package to a file. Copy the file to the production server. Configure the SSIS package on the production server to use the new file.
B. Back up the master database and restore it to the production server. Rename the appropriate tables inside the master database.
C. Create a SQL Server Integration Services (SSIS) package configuration. Build a deployment utility. Copy the deployment folder for your SSIS project to your production server. Execute the manifest file.
D. Back up the msdb database and restore it to the production server. Rename the appropriate tables inside the msdb database.

Answer: C

Q: 10
You are a database administrator for XXYYinc.com. You notice that one of the data files on a SQL Server 2005 computer is corrupted. You need to restore the database from the most recent set of backups. You want to perform this task as quickly as possible, with a minimum loss of data. What should you do first?

A. Restore the most recent transaction log backup.
B. Restore the most recent full database backup.
C. Perform a full database backup.
D. Perform a transaction log backup.

Answer: D

Q: 11
You are a database administrator for XXYYinc.com. A SQL Server 2005 computer named XXYYinc A has a database named Inventory. XXYYinc A is responsible for aggregating manufacturing part numbers from your company's trading partners. The manufacturing part numbers are stored in the Product table in the Inventory database. Every night, data is sent as text files from each trading partner to XXYYinc A. You need to import data and ensure that no duplicate manufacturing part numbers exist in the data imported from the text files that are stored in the Inventory database on XXYYinc A. You want to achieve this goal with the minimum amount of impact on your company's trading partners and your IT department. What should you do?

A. Ensure that each of your company's trading partners uses unique key values for manufacturing part numbers. Then, import the text files into the Product table.
B. Ensure that each text file is stored in an Extensible Markup Language (XML) file. Use Extensible Stylesheet Language Transformations (XSLT) to automatically remove duplicates before data is imported into the Product table.
C. Import the data from each text file into a staging table. Write a query to include a HAVING clause to remove duplicate values before inserting results into the Product table.
D. Place a unique index on the PartNum column.

Answer: C

Q: 12
You are a database administrator for XXYYinc.com. The company has a SQL Server 2005 computer named XXYYinc A. A database on XXYYinc A stores sales history data for the company's Web-based order system. Twenty of the business analysts in the company create ad hoc queries against the database. The performance of XXYYinc A is routinely slow. You need to find out which business analyst is causing the problem. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)

A. Run the sp_who system stored procedure.
B. Use the Activity Monitor to view the activity of specific business analysts.
C. Run SQL Trace system stored procedures to gather database activity.
D. Create a trace that uses a predefined template by using SQL Server Profiler.

Answer: C, D

Q: 13
You are a database administrator for XXYYinc.com. Your company owns a chain of 10 retail stores. Each retail store maintains point-of-sale transactions on its own SQL Server 2005 computer, in a database table named Sales. The Sales table also contains sales data from other stores to enable customer returns to any of the 10 retail stores. The sales data is refreshed from the main office to each retail store hourly. A trigger named trg_Coupon on the Sales table is used to generate sales coupons based on customer sales and buying patterns. You need to configure replication between the server in each retail store and a central server in the main office by using the fewest number of steps. Replication does not have to be in real time. What should you do?

A. Use transactional replication between the server in each retail store and the central server in the main office.
B. Set up multiple merge publications, one at each retail store and one on the central server in the main office.
C. Use snapshot replication. Configure the trg_Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option.
D. Use merge replication. Configure the trg_Coupon trigger on the server in each retail store to use the NOT FOR REPLICATION option.

Answer: D

Q: 14
You are a database administrator for XXYYinc.com. The company hosts SQL Server 2005 databases for subscription-based customers. New customer databases are created frequently. Databases are removed when customers subscriptions end. Company managers want to track who creates each database and when each database is created. To store this information, you create a SQL Server database named Logging. You need to ensure that the appropriate information is written to the Logging database. What should you do?

A. Use the SQL Trace stored procedures to log database creation activity.
B. Create a stored procedure. Configure the stored procedure to write the appropriate information to the Logging database.
C. Create a DDL trigger that runs when a new customer database is created. Configure the DDL trigger to write the appropriate information to the Logging database.
D. Configure Service Broker to run a stored procedure that writes the appropriate information to the Logging database.

Answer: C

Q: 15
You are a database administrator for XXYYinc.com. You are responsible for managing 10 SQL Server 2005 computers that run Microsoft Windows Server 2003, Enterprise Edition. The company's Microsoft Active Directory administrators handle all Group Policy object (GPO) deployments. The Active Directory administrators have deployed a security template named SQL05_Security.inf that has the appropriate settings to meet the company's security policy. You need to identify the existing configuration of each SQL Server 2005 computer to ensure that it meets the company's security policy. What should you do?

A. Use Network Monitor on each SQL Server 2005 computer to capture a detailed report of the types of network traffic on the local network adapter.
B. Use the Performance Logs and Alerts snap-in to create counter logs for the Network Interface performance object.
C. Use the Security Template snap-in to analyze the SQL05_Security.inf security template.
D. Use the Security Configuration and Analysis tool on each SQL Server 2005 computer to identify discrepancies between system settings and database settings.

Answer: D

Q: 16
You are a database administrator for XXYYinc.com. A software developer in the company is running the following query against a SQL Server 2005 database. SELECT Surname FROM Employees WHERE UPPER(Surname) LIKE 'COR%' The software developer reports that the query runs quickly on a test database that has a small number of rows. However, the query runs very slowly on the production database that has millions of rows. The Surname column stores data in mixed case, by using case-sensitive collation. But the query needs to perform a case-insensitive search. You need to improve the performance of this query. However, you do not have permission to change the collation orders. And you cannot modify the application code that adds rows to the database. Which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)

A. Create an index on the Surname column.
B. Modify the query to include the following Transact-SQL statement.
SELECT Surname FROM Employees WHERE Surname LIKE UPPER('cor%')
C. Execute the following Transact-SQL statement. ALTER TABLE Employees
ADD UpperSurname AS UPPER(Surname)
D. Execute the following Transact-SQL statement. ALTER TABLE Employees
ADD UpperSurname AS CAST(Surname AS nvarchar)
E. Create an index on the UpperSurname column.
F. Modify the query to include the following Transact-SQL statement.
SELECT Surname FROM Employees WHERE UpperSurname LIKE 'COR%'

Answer: C, E, F

Q: 17
You are a database administrator for XXYYinc.com. Your SQL Server 2005 database contains a table named Customer with a column named PostalCode in it. New
PostalCode values are added regularly. You need to ensure that values contained in the PostalCode column are verified. You need to retrieve, from the Postal Service in each country in which you have customers, the domain of values for the PostalCode column. Rapid response time for data access is of primary concern. What should you do?

A. Implement a database trigger that looks up the postal code data from a server maintained by the Postal Service.
B. Import the Postal Service data on a nightly basis into a PostalCode table. Then, configure a foreign key on the Customer table to the PostalCode table.
C. Configure a CHECK constraint on the PostalCode field of the Customer table to allow only valid values.
D. Implement a CLR trigger that looks up the postal code data by using a Web service offered by the Postal Service.
E. Configure a new PostalCode data type and ensure that it conforms to the Postal Service specifications for the format for each country.

Answer: B

Q: 18
You are a database administrator for XXYYinc.com. A user named XXYYinc belongs to the Windows SalesManagers group. XXYYinc needs a Reporting Services report to display annual sales information by territory. A user named Joe reports to XXYYinc and belongs to the Windows Sales group. He needs a report that is limited to his sales territory, but still displays the same detailed sales data that is on XXYYinc's report. You need to design a secure solution that meets the reporting needs of the users and that consumes the minimal amount of server resources. What should you do?

A. Design a report for XXYYinc that includes two data regions. One data region is used for summary sales data. The other data region is used for the detailed sales data for each sales representative. Then, design a report for Joe that displays the detailed sales data.
B. Design two reports for XXYYinc. One report includes the territory information, and one report includes the detailed sales data. Then, design a report for Joe that displays the detailed sales data for his territories.
C. Design a report for XXYYinc that includes the territory information. Then, design a report for Joe that includes detailed sales data for his territories. Finally, configure Jack Kings report so that it includes the data in Joes report.
D. Design a parameterized report for XXYYinc that includes all territory information. Then, design a linked report for Joe that links to XXYYincs report.

Answer: D

Q: 19
You are a database administrator for XXYYinc.com. You receive alerts reporting that several transactions on your SQL Server 2005 database have terminated due to a deadlock error. You need to find out the causes of the deadlocks. What should you do?

A. Use System Monitor to trace the Application instance of the Number of Deadlocks/sec counter in the SQL Locks object.
B. Use the sys.dm_tran_locks dynamic management view (DMV).
C. Run the Database Engine Tuning Advisor (DTA) and implement the recommendations.
D. Run SQL Server Profiler and create a trace with the Deadlock graph event group, and extract deadlock events.

Answer: D

Q: 20
You are a database administrator for XXYYinc.com. You are configuring a new SQL Server 2005 computer named XXYYinc A. XXYYinc A will run Reporting Services. It will also be configured to automatically perform database backups and other maintenance tasks. There are no other SQL Server computers in the network environment. All access to XXYYinc A will be made by using XXYYinc As DNS name. You need to disable any unnecessary services on XXYYinc A. Which service or services should you disable? (Choose all that apply.)

A. Microsoft Distributed Transaction Coordinator
B. Internet Information Services
C. SQL Server Agent
D. SQL Server Browser

Answer: A, D

Q: 21
You work as DBA at XXYYinc.com. You administer a SQL Server 2005 computer named XXYYinc A. XXYYinc A is a member of a Microsoft Active Directory domain. You do not have any rights or privileges to perform domain administration. However, you have been granted membership in the local Administrators group on XXYYinc
A. You perform most of the management of XXYYinc A from your administrative workstation. However, for security reasons, you want to track all attempts for interactive logons and network connections to XXYYinc A. What should you do?

A. Configure the SQL Server service on XXYYinc A to audit all successful and failed logon attempts.
B. Run the SQL Server Profiler and use a standard default template. C. Edit the local security policy of XXYYinc
A. Then, configure success and failure auditing on the Audit logon events setting.
D. Create a Group Policy object (GPO) that is configured for success and failure auditing of the Audit account logon events setting. Ask the domain administrator to link the GPO to the object containing XXYYinc A.

Answer: C

Q: 22
You work as DBA at XXYYinc.com. You administer a SQL Server 2005 computer named XXYYinc A. XXYYinc A is configured to automatically perform transaction log backups, database integrity checks, and other maintenance tasks on a regular basis. Another administrator uses the SQL Server Surface Area Configuration tool to reconfigure XXYYinc A. You notice that XXYYinc A no longer performs the automated maintenance tasks. You need to ensure that the maintenance tasks on XXYYinc A are completed automatically. What should you do?

A. Manually perform a full backup of the msdb database.
B. Reconfigure the server so that the SQL Server Agent service starts automatically.
C. Reconfigure XXYYinc A to use Windows Integrated authentication.
D. Reconfigure the SQL Server service to log on by using an administrative user account.

Answer: B

Q: 23
You are the database administrator for XXYYinc.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.

Answer: C

Q: 24
You are a database administrator for XXYYinc.com. The company uses a SQL Server 2005 database that includes a table named Customers. The table includes a column named EmailAddress that contains string data. Company policy specifies a required format for data in the EmailAddress column. Changes to the EmailAddress column are made several ways, including by using ad hoc queries. You need to ensure that the data in the EmailAddress column is properly formatted. Your solution must provide the best possible performance when thousands of rows are added to the database. What should you do?

A. On the EmailAddress column, create a default constraint that specifies a string in the proper format.
B. Write a stored procedure that disallows changes to the EmailAddress column that violate company policy.
C. On the EmailAddress column, create a check constraint that uses a user-defined function to validate the format of the data.
D. Write a trigger that rolls back changes to the EmailAddress column that violate company policy.

Answer: C

Q: 25
You are a database administrator for XXYYinc.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.

Answer: C

Q: 26
You work as DBA at XXYYinc.com. You administer a Microsoft Windows Server 2003 computer named Server1. SQL Server 2005 is installed on Server1 to host a database named MedDB. The MedDB database serves as the backend database for a new client/server application named MedDAT A. Installation of the MedDATA application creates the database schema, application roles, and administrative accounts. It also sets all of the default permissions. You need to establish a permissions baseline for all objects in the MedDB database. What should you do?

A. Use the SQL Server 2005 Management Studio to review the permissions of the default database objects.
B. Query the sys.server_permissions table on Server1.
C. Query the sys.database_permissions and sys.database_principals tables in the MedDB database.
D. Execute the sp_helplogins system stored procedure for each of the default users that the MedDATA application creates.

Answer: C

Q: 27
You are a database administrator for an electronics distribution company. You are documenting your company's requirements for data quality. You need this information so that you can store part numbers, components, and stock levels from your company's trading partners in your SQL Server 2005 environment. You need to determine the standards for data quality. You need to consult with the business analysts and other database administrators (DBAs) in your organization to do so. What are three possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose three.)

A. Establish metrics for measuring data quality.
B. Identify how to keep data as small as possible. C. Identify how to standardize data elements.
D. Identify how to monitor the quality of your data.
E. Have your company's trading partners be responsible for their own data quality.

Answer: A, C, D

Q: 28
You are a database administrator for XXYYinc.com. Developers need access to create, alter, and drop tables in your SQL Server 2005 database. A list of production tables is maintained in a table named Valid_Production_Tables in the database. You need to be able to automatically roll back any commands that drop or alter current production tables. You need to do this while minimizing the processor resources being used. What should you do?

A. Use a DML trigger to execute the FOR UPDATE, DELETE command on the Valid_Production_Tables table.
B. Use Event Notification to execute the FOR DDL_TABLE_EVENTS command.
C. Use Event Notification to execute the FOR ALTER_TABLE, DROP_TABLE command.
D. Use a DDL trigger to execute the FOR DROP_TABLE, ALTER_TABLE command.

Answer: D


© 2014 Cheat-Test.com, All Rights Reserved