Microsoft-PRO Designing, Optimizing, and Maintaining a Database Administrative Solution Using Microsoft SQL Server 2008 Set 2

You are a professional level SQL Sever 2008 Database Administrator. Two SQL Server 2008 instances are managed by you, and they are respectively called Ins01 and Ins02. The Sales database is included in Ins01, while the Accounts database is contained by Ins02. A transaction is started by A procedure in the Sales database. And then, the Sales.dbo.Order table and the Accounts.dbo. OrderHistory table are updated by the procedure through a linked server. Since you are the technical support of the company, you are required to make sure that a two-phase commit is utilized by the transaction. Which action will you perform to finish the task?

You are a professional level SQL Sever 2008 Database Administrator. A new database application is hosted by the instance. The data security strategy should be designed for the application. The data should be duplicated to another instance of SQL Server 2008 by utilizing the Snapshot duplication. Since you are the technical support of the company, you should make sure that all stored data should be encrypted, and the minimum amount of administrative effort should be utilized. Which action will you perform?

You are a professional level SQL Sever 2008 Database Administrator. The solution is log-shipped for high-availability purposes. The data files of the database reside on drive D, while the transaction log files of the database reside on drive E. A restoring test plan should be implemented to satisfy the requirements listed below for the log-shipping solution. First, the secondary database is brought online in the shortest time. Secondly, the data is in a consistent state. Thirdly, the data loss is reduced to the least. The first step for the recovery test plan should be identified in the event of drive D failure. Which step should be performed?

You are a professional level SQL Sever 2008 Database Administrator. Your company wants to enforce naming standards for the database objects. A solution should be designed by you to enforce naming standards, and the least amount of administrative and programming efforts should be utilized. Which action should you perform?

You administer a SQL Server 2008 instance that hosts a large database. The following backup strategy is used for the database: A full database backup is performed at 02:00 hr every Monday. A differential backup is performed at 23:00 hr every day. A transaction log backup is performed on an hourly basis. A power failure on Thursday causes the SQL Server 2008 server to restart at 09:15 hr. Fifteen minutes after the server restarts, the users report that they are unable to execute certain queries that access customer data. You discover that the customer data is unmodified after the power failure. When you execute the DBCC CHECKDB command on the database, you receive the following error message: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data): Page (3421:169) could not be processed. See other errors for d etails. Table error: Object ID 2814307832, index ID 2, partition ID 83127819437122157, alloc unit ID 82134587923221126 (type In-row data), page (3421:169). Test (IS_OFF (BUF_IOERR, pBUF- > bstat)) failed. Values are 16928422 and -8. CHECKDB found 0 allocatio n errors and 2 consistency errors in table 'tbl_Customer' (object ID 2814307832). When you execute the sp_help 'tbl_customer' stored procedure you receive the following result set: index_name index_description PK clustered located on PRIMARY NCIX nonclustered located on PRIMARY You need to ensure that the data is available as quickly as possible with minimal effect on users. What should you do?

You administer a SQL Server 2008 infrastructure. The instance contains a database required for the day-to-day business of your company. Users experience slow response time when they execute reports. You plan to design a performance monitoring strategy that captures and stores the following data: Executed Transact-SQL statements and query activity. Blocking and deadlock information. Counters for disk, CPU, and memory. You need to implement the monitoring process by using the minimum amount of administrative effort. What should you do?

You administer a SQL Server 2008 infrastructure. Your company requires capacity planning information. You need to design a long-term infrastructure monitoring strategy. Which two tasks should you include in your design? (Each correct answer presents part of the solution. Choose two.)

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?

You are a professional level SQL Sever 2008 Database Administrator. A SQL Server 2008 instance at the New York central site is managed by you, and it is called Ins01. There is a sales team in your company, and their task is to finish purchase orders for customer requests. The sales team updates data regularly in a local database by utilizing their portable computers. When the central site is connected by portable computers, a database named Sales will be synchronized with the local database. A duplication model will be created to duplicate the local database to the Sales database. The requirements listed below should be satisfied by the duplication model: First, when the same data is updated by multiple users independently, there should be no data conflicts. Secondly, sensitive data such as product price cannot be updated by the sales team. Thirdly, the data should be synchronized by the sales team not only at scheduled times but also on demand. The best model should be identified to have data duplicated, and you should be reduced the development efforts to the least. Which action will you perform?

You are a professional level SQL Sever 2008 Database Administrator. A solution will be implemented to have hardware configurations obtained. The hardware configuration obtained should include the number of processors on a computer and the processor type of all SQL Server 2008 computers. The requirements listed below should be satisfied by the solution. The solution should be hosted on the central computer. And the hardware configurations for multiple servers should be verified bythe solution. A technology should be selected to satisfy the requirements, and the least amount of development effort should be utilized. Which action should you perform to finish the task?