Microsoft-Upgrade Transition Your MCITP SQL Server 2005 DBA to MCITP SQL Server 2008 (2) Set 2

You are the administrator of your company network. There's a SQL Server 2008 instance which hosts a new database application in your company. Now you want to develop the security requirements for the application. Each application user has his own login to the SQL Server 2008 server. The application database executes stored procedures in the MSDB database in which the stored procedures schedule SQLAgent jobs. You must make sure that the the stored procedures in the MSDB database are executed by using the security context of the application user. So what should you do?

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance which contains a database named Funds. And the Funds database is being used in single-user mode. The backup strategy for the Finance database meets the following two requirements: 1. At 09:00 every day, full database backup to a file called funds.bak is performed. 2. Since 09:15 every day, a transaction log backup to a file named funds_HHMM.trn is performed every 15 minutes. A user came to report that a query deleted some important data by accident. The query was executed at 09:24. You are asked to recover the database to its original state. So which Transact-SQL statement(s) should you use to achieve this?

You are the administrator of your company network. And you're in charge of SQL Server 2008 instance. You intend to deploy a new database to the instance. The data will have lots of schemas and will be subject to frequent inserts and updates. One schema will contain plenty of reference data. In order to optimize backup performance, you have to design the physical database structure. So what should you do?

You are the administrator of your company network. And you're in charge of SQL Server 2008 instance. Now you intend to deploy a new database which asks for capacity requirements. It requires 280 GB for the database data file and 50 GB for the transaction log file. The storage array has six disk drives available for the database. The capacity of the six disk drives are all 100 GB and are attached to a redundant array of independent disks (RAID) controller which supports RAID levels 0,1, 5, and 10. You have to optimize the write performance of the transactional log. And you also have to protect the database and transaction log files if a drive failure occurs. You have to design the storage system. So which storage configuration should you use?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 infrastructure. You check and find that the performance of the instance is degrading. And this may because of deadlocks, excessive CPU usage or server processes paging. You have to work out a solution to monitor and identify performanceproblems. Your solution should Lead your way to certificates! provide information, including detailed deadlock information. You have to do this with as little administrative effort as possible. So which tool should you use?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 instance which contains a large mission-critical database. And the database experiences a high volume of DML activity. Now you find that the applications which link to the database make response very slowly. In order to collect information about index fragmentation, new potential indexes and unused or sparingly used indexes, you have to develop a monitoring process to do this. So what should you use?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 infrastructure. The instance contains a database named RCC. Uses report that when executing reports, it makes response quite slowly. So you want to develop a monitoring strategy to monitor the performance. The strategy captures and stores the data such as Counters for disk, CPU, and memory, blocking and deadlock information, and executed Transact-SQL statements and query activity. When running the monitoring process, you must use as little administrative effort as possible. So what should you do?

You are the administrator of your company network. And you're in charge of a SQL Server Lead your way to certificates! 2008 infrastructure. Now you want to obtain hardware configurations, such as the processor type of all SQL Server 2008 computers. So you have to develop a solution to achieve this. The solution you develop must be hosted on the central computer and can verify hardware configurations for multiple servers. You have to choose a technology that satisfy the two requirements above, while using as little development effort as possible. So what should you do?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 infrastructure. Now you are assigned a task to enforce naming standards for the database objects, so you have to design a solution to achieve this while using as little programming and administrative efforts as possible. So what should you do?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 infrastructure. The instance contains a database names PPR. Database PPR has a large table named PBT. The application queries only execute DML statements on the last two months data. Administrative audits are conducted monthly on data older than two months. In the database, you find some performance issues, such as the performance of the application queries against the PBT table is poor, the maintenance tasks against the database, including index defragmentation, run quite slowly. So what should you do to solve these problems while not having effect on sever performance. Lead your way to certificates!