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

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance which contains a database named DBx1. There's a table calledd TPV in DBx1 database. This table is used to store information on all kinds of TPV cars. Users query the TPV table based on the BikeSize column frequently. Besides the NULL value for bikes, the BikeSize column also has the NULL value for all products. At present no index is related to the BikeSize column. Now you have to improve the query performance, meanwhile you must reduce the effect on the disk space to the least. So what should you do to ahieve this?

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance which contains a database named DGTASK. There're two tables in the DGTASK database, the table below displays this:The OrderHistory table includes two table partitions; one partition is empty while the other one contains all data. The Orders table includes a table partition for each month.Now you intend to create a process to perform a migration. You want to move data from the partition which contains data for the oldest month of the Orders table to the right partition of the OrderHistory table. So what should you do to make sure that the process can be repeated in the middle of each month.

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance which contains a database named DGTASK. There's a table called TPV in DBx1 database. You add a column called TPXC to the TPV table. The TPXC column is of the Geography data type. There is no index in the TPV table. So what should you do first in order to create a spatial index on the TPXC column?

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance, each of which hosts a single database application. Now you want to perform a migration, move all the following four instances to a new SQL Server failover cluster which will hosts all four databases on a single virtual cluster IP address. The following table displays the configuration of the four instances:The four instances have been optimized and they have got no extra memory or free CPU cycles. Now you have to perform a configuration on the new cluster, so that it is able to undertake the workload of all the database applications while using as little hardware resources as possible. So which cluster configuration should you use?

You are the administrator of your company network. You are in charge of a SQL Server 2008 infrastructure. There's an instance that hosts a database named MAJOR1 which includes Filestream data. Users must be able to access this database uninterruptedly with no data loss. You have to carry out a high-availability solution for the site. So which solution should you use?

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance which is one of three servers in a peer-to-peer transactional replication topology. The publication has a table called SRB which contains 220 GB of data across multiple partitions. Regularly 12 GB of data is loaded to the SRB tableby a batch process. You have to reduce the latency by developing a replication strategy. So what should you do?

You are the administrator of your company network. You are in charge of a SQL Server 2008 instance. The SQL Server Import and Export Wizard creates SQL Server 2008 Integration Services (SSIS) packages. And now you intent to deploy the packages which will use stored user names and passwords to link to external data. You must prevent users who are not authorized to access the user names and passwords. So what should you do to ensure this?

You are the administrator of your company network. You maintain a SQL Server 2008 instance which contains a database named Taskgo. Now deadlock problems always occur on the Taskgo database. So what should you do to capture the deadlock information to the SQL Server error log?

You are the administrator of your company network. And you're in charge of a SQL Server 2008 infrastructure. Now you manage a SQL Server 2008 instance named Instance PK1 at the Chicago central site. In your company, there're several departments, such as sales department and service department. The sales department is in charge of dealing with the purchase orders according to the requirements of customers. In a local database, the sales team uses laptop to update data very often. The local database must be in synchrony with a database named Selling when the laptop link to the Chicago central site. You intend to replicate the local database to the Selling database by developing a replication model which must satisfy the requirements below: 1. The sales team can synchronize data at scheduled times and on demand also. 2. The sales department cannot update sensitive data such as origin of the products. 3. When losts of users update the dame data independently, data conflicts are solved. You have to choose the best model to replicate data while using as little development efforts as possible. So what should you do?

You are the administrator of your company network. There's a SQL Server 2008 instance which hosts a large database in your company. Now you're in charge of the instance. The database uses the backup strategy below:At 01:00 every Tuesday, the database performs a full backup. At 23:00 every day, the database performs a differential backup. Every hour a transaction log backup is performed. The SQL Server 2008 server restarts at 09:20 because the electricity goes off on Wednesday. About a quarter later after the sever restarts, you find that the users can't run some queries that access customer data. And you check and find that after the electricity failure, the customer data is unmodified. When running the DBCC CHECKDB command on the database, the error message below pops up: 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 details. 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 allocation errors and 2 consistency errors in table 'tbl_Customer' (object ID 2814307832). When running the sp_help 'tbl_customer' stored procedure, you get the result set below:-----------------------------------------------PK clustered located on PRIMARYNCIX nonclustered located on PRIMARY So what should you do to enable users to access the data. you must achieve this quickly and have little effect on users.