What should you do to revert the CK_Catalog online database to the CK_catss_thursday snapshot?
You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run Windows Server 2003 and all clients run Windows XP Professional.
The Domain.com network contains a SQL Server 2005 database server named Certkiller -DB01. Certkiller -DB01 contains a large database named CK_Catalog which is updated every Monday and every Thursday. An online version of the CK_Catalog database is available to customers on the Domain.com e-Commerce Web site. Clive Wilson, the Webmaster of the Domain.com e-Commerce Web site is responsible for updating the online version of the CK_Catalog database. To optimize performance of the CK_Catalog online database, you create database snapshots at 5:30 PM every Monday and every Thursday. Each time you create a new snapshot, you delete the snapshot from the previous week. The two snapshots are named CK_catss_monday and CK_catss_thursday. One Tuesday morning Clive Wilson informs you that he imported the wrong data into the CK_Catalog online database. You need to revert the CK_Catalog online database to the CK_catss_thursday snapshot and allow Clive Wilson to import the correct data.
What should you do?
What are two possible ways to achieve this goal?
Domain.com uses SQL Server 2005. A user reports that an order processing application stopped responding during a transaction. You investigate and discover that the user’s session, which is session ID 85, is being blocked by an errant process. The session ID of the errant process is 101. You need to solve this blocking situation quickly so that the user can continue using the application without losing any work. What are two possible ways to achieve this goal? (Each correct answer presents a complete solution. Choose two.)
What should you do to create the appropriate objects that support the reports?
You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com include the administration of a SQL Server 2005 database named Certkiller -DB02.
Certkiller -DB02 provides the clients with their banking information. The CIO wants the clients see the investment reports that combine the information retrieved from Certkiller -DB02 with real-time investment information that comes from a Web service provided which is provided by a third party. You need to ensure that the investment information is current when the reports are executed. You need to create the appropriate objects that support the reports.
What should you do?
What should you do to determine the dynamic management view that should be used?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com encompass administrating the SQL Server 2005 database named Certkiller -DB01.
Certkiller -DB01 contains a table that consists of a number of indexes. It has come to your attention that data modification performance has degraded and that some of the indexes are not used. You have received instruction from the CIO to determine which indexes are not used by queries since the last time Certkiller -DB01 started. You need to determine the dynamic management view that should be used.
What should you do?
What actions should you take to make sure that availability during the implementation of any recommendations ?
You are employed as a database administrator at Domain.com. All servers on the Domain.com network run Windows 2000 Server and all client computers run Windows 2000 Professional.
Domain.com contains a SQL Server 2005 database server named Certkiller -DB01 that hosts a database named CK_Data. Domain.com also contains another SQL Server 2005 database server named Certkiller -DB02, which has the latest copy of CK_Data. The database CK_Data on Certkiller _DB01 runs 24/7.
During routine monitoring you detect a high number of full scans on Certkiller -DB01. You then come to the conclusion that extra indexes are needed on CK_Data. A workload file that is suitable for Database Engine Tuning Advisor (DTA) already exists.
You need to analyze the workload file by using DTA without compromising the performance on Certkiller -DB01 during the analysis. You also need to make sure that availability during the implementation of any recommendations suggested by the DTA is maintained.
What actions should you take?
What should you do to modify the query in order that the information is listed in the correct order?
You work as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties include administrating a SQL Server 2005 database named Certkiller -DB01. At Domain.com the technician appointed in marketing department is responsible for creating a list of e-mail addresses of clients from information stored in Certkiller -DB01. The CIO wants the following information to appear on the list:-
1. It should encompass the date on which every client was last contacted.
2. The information in the result set must have column names.
3. The list should be ordered by date of last contact, with the latest contact first.
4. The CK_LastContact column should be stored as a datetime.
5. The date should be displayed as MM/DD/YYYY.
A junior technician named Amy Walsh develops the following query.
SELECT email_address AS EmailAddress,
CONVERT(nvarchar, lastcontact, 101) AS CK_LastContact FROM Contact ORDER BY CK_LastContact DESC
As the administrator you test the query and discover that the information is displayed in the following order:
EmailAddress CK_LastContact mia@contoso.com 01/24/2003 amy@contoso.com 06/12/2005 kara@contoso.com 07/13/2004
You need to modify the query in order that the information is listed in the correct order and accomplish this without negatively affecting performance.
What should you do?
What Transact-SQL statement should Dean Austin use?
You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You have been informed by the CIO that the msdb database on a SQL Server 2005 computer is corrupted. This database should therefore be restored. A Domain.com employee named Dean Austin is employed as a junior technician in the Finance department. You inform him that the databases should be backed up daily. The backup files of the database are written to a network share. However, the file names do not clearly specify which databases are in every file. Dean Austin needs to locate the right backup file as quick as possible. You inform him that the first file in the list is named DB_Backup.bak.
What Transact-SQL statement should Dean Austin use?
What should you do to create the SQL Server 2005 instance for the Manufacturing_DB database?
You work as the database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. All servers on the Domain.com network run on Windows Server2003 and all client computers run Windows XP Professional. The Domain.com network contains two SQL Server 2005 database servers that are configured in a cluster with two nodes named NodeA and NodeB.
NodeA contains two physical hard disk drives, one of which hosts the Quorum resource and contains a single partition while the other contains two logical partitions. NodeB also has two physical hard disk drives each of which contain a single logical partition. None of the hard disks in the cluster have any unallocated space left. The SQL Server 2005 cluster currently hosts four database instances. Domain.com wants to implement another SQL Server 2005 instance for a database named Manufacturing_DB. Manufacturing_DB will store data from the Domain.com Manufacturing department. You have been instructed to create the SQL Server 2005 instance for the Manufacturing_DB database.
What should you do?
What should you do to minimize the impact of this operation on Certkiller -DB02 performance?
Domain.com has employed you as a database administrator. The Domain.com network consists of a single Active Directory domain named Domain.com. Your duties at Domain.com are the administrating of the SQL Server 2005 computer named Certkiller -DB02.
You have received several reports from clients stating that the time required to run their queries on Certkiller -DB02 is gradually increasing. You have been informed by the CIO to decide on which computer to run SQL Server Profiler as well as where to store the trace.
You are required to investigate this matter by using the SQL Server Profiler to trace all client queries. You need to minimize the impact of this operation on Certkiller -DB02 performance.
What should you do?
What should you do first to add the two new message types to the existing service?
You are employed as a database administrator at Domain.com. The Domain.com network consists of a single Active Directory domain named Domain.com. You have received instruction from the network CIO to create a Web-based application to manage data aggregation for reports. The Application that you create will connect to a SQL 2005 database named CK_Data and another page of your application has controls which are used to execute stored procedures in a database named CK_Sales.
Domain.com has an existing Service Broker connection between the CK_Data and CK_Sales databases. You have received additional instruction to add two new message types to the existing service. In each database, you create message types named CKReport and CKResult. You are required to add the two new message types to the existing service.
What should you do first?