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

Explanation:
The Default Domain Group Policy object requires that passwords are changed every 42 days. In order to correct this problem you have to change the password in the Active Directory AND configure the new password on both you SQL Servers to the newly changed password.

Question 2.
You are a database administrator of two SQL Server 2005 computers named ITCertKeys A and ITCertKeys B. You have a Microsoft .NET application that has been modified so that it now accesses a database on ITCertKeys B in addition to ITCertKeys A. You do not want the user application to connect directly to ITCertKeys B. You need to enable the data retrieval from ITCertKeys 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 ITCertKeys B.
B. Configure a linked server on ITCertKeys A to point to ITCertKeys B.
C. Change the stored procedures called by the .NET application to include the OPENXML command.
D. Configure a linked server on ITCertKeys B to point to ITCertKeys A.

Answer: B

Explanation: 
SQL Server lets you access external data sources from your local Transact-SQL code. You need to define a linked server for each external data source you want to access and then configure the security context under which your distributed queries will run. After you create a linked server, you can use the Transact-SQL OPENQUERY function to execute your distributed queries.

Question 3.
You are a database administrator for ITCertKeys.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

Over the past few years, Microsoft SQL Server has increased its presence in the industry and has reduced its TCO. This reduced TCO is a direct result, primarily, of the numerous self-tuning mechanisms built into Microsoft(r) SQL Server. These mechanisms automatically perform tasks that would otherwise have to be performed by experienced database administrators. One such mechanism is the cost-based optimizer (CBO) that is used to dynamically generate query execution plans. The CBO probes several system-wide resource states and employs many complex, heuristical algorithms to generate the best possible plan for a given query and the underlying table and index structures. This mechanism works well for the vast majority of user queries, but there are times when experienced users need to force a particular query plan, based on some prior knowledge or insights into future uses.

Question 4.
You are a database administrator for ITCertKeys.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

Explanation: 
When you indexes are not heavily fragmented you can reorganize indexes, which uses few system resources and runs automatically online. In SQL Server 2005, certain operations such as large delete operations or -one-time data loads might leave database files larger than they need to be. SQL Server 2005 enables a DBA to shrink each file within a database to remove unused pages and regain disk space.

Question 5.
You are a database administrator for ITCertKeys.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

Explanation: 
The index_physical_stats function takes five parameters: database_id, object_id, index_id, partition_id, and mode. This function returns row size and fragmentation information.

Question 6.
You work as DBA at ITCertKeys.com. You administer two SQL Server 2005 computers named ITCertKeys A and ITCertKeys B. ITCertKeys A and ITCertKeys B contain a copy of a database named Sales. The database is replicated between ITCertKeys A and ITCertKeys 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 ITCertKeys A becomes corrupted. You are unable to repair the database. The Sales database on ITCertKeys B is unaffected. You need to return the Sales database on ITCertKeys 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 ITCertKeys B. Restore the backup to ITCertKeys 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 ITCertKeys B. Copy the database file to ITCertKeys A, and attach the database on both servers.

Answer: A

Explanation: 
Detaching the database will not apply to the minimal impact on users as it will be offline during copy. Restoring only the latest transaction log will not give you a working database and restoring last full backup and all transaction logs after that will not apply to the restore normal operations as quickly as possible part of the requirements.

Question 7.
You are a database administrator for ITCertKeys.com. The company runs a popular database-driven Web site against a SQL Server 2005 computer named ITCertKeys B. You need to ensure a quick response time and appropriate audit trail in the event that ITCertKeys 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

Explanation: 
Using alerts, System Monitor tracks specific events and notifies you of these events as requested. An alert log can monitor the current performance of selected counters and instances for objects in SQL Server. When a counter exceeds a given value, the log records the date and time of the event. An event can also generate a network alert. Characteristic for a typical DoS attack is a large number of Bytes/sec. Characteristic for a DDoS attack is also a large number of Bytes/sec but also a large number of connections made from a large number of hosts.
 
Question 8.
You are a database administrator for ITCertKeys.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

Explanation: 
In SQL Server 2005 you can implicitly define the execution context of the following user-defined modules: functions (except inline table-valued functions), procedures, queues, and triggers. By specifying the context in which the module is executed, you can control which user account the SQL Server 2005 Database Engine uses to validate permissions on objects that are referenced by the module. This provides additional flexibility and control in managing permissions across the object chain that exists between user-defined modules and the objects referenced by those modules. Permissions must be granted to users only on the module itself, without having to grant them explicit permissions on the referenced objects. Only the user that the module is running as must have permissions on the objects accessed by the module.
Syntax:
Functions (except inline table-valued functions), Stored Procedures, and DML Triggers
{ EXEC | EXECUTE } AS { CALLER | SELF | OWNER | 'user_name' }

Question 9.
You are a database administrator for ITCertKeys.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

Explanation: 
You have to use a SSIS package to accomplish this but you can not use the package built for your development environment as the table names differ.

Question 10.
You are a database administrator for ITCertKeys.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

Explanation: 
In order to restore from the most recent set of backups, you have to first ensure that you can restore all transactions that have occurred between the time of the backup and the time when the database went corrupt. In order to accomplish this you should backup the transaction log.

	Web www.certsbraindumps.com