You are the administrator of your company network. And you are in charge of a SQL Server 2008 instance. Now you intent to deploy a third-party database application which uses stored procedures to the instance. And the stored procedures are created by using SQL CLR integration. You should perform a configuration on the application so that the EXTERNAL_ACCESS code can access security setting. You must make sure that when you deploy the database to the instance, there’s no loss of functionality. So what should you do first?

You are the administrator of your company network. There’s a SQL Server 2008 instance which hosts a large sales database in your company. The database uses the following backup strategies: A transaction log backup is performed hourly.
A differential backup is performed every day.
A full database backup is performed once a week.
You intend to run an end-of-year batch process which will change about four percent of data within the database. And the process takes about two hours to run. If something unexpected happens, such as the Sales department disapprove the process, you must make sure that the batch operation can be rolled back as soon as possible. What should you do before starting the batch process?

You are the administrator of your company network. There’s a SQL Server 2008 instance which hosts a large database in your company. The database uses the following backup strategy. On Saturday you execute a batch process, this process lasts from 21:00 to 22:50. Since a user has modified some data at 21:03 on the same day, the batch process is invalidated. So what should you do to restore the database to its origin state while using the minimal time?

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. 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. 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 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 that contains a database named DBx1. DBX1 contains a stored procedure called ProcedureX1 and a table named TableX1. By using a sp_executesql Transact-SQL statement, ProcedureX1 selects data from TableX1. As you know, there’s a rule in your company, which is users are not permitted to access tables directly in any database.When ProcedureX1 is run by users, the following exception pops up: “Msg 229, Level 14, State 5, Line 1The SELECT permission was denied on the object ‘Table1’, database ‘DB1’, schema ‘dbo’.” Now you must enable the user to execute ProcedureX1 successfully, while you have to make sure that it will not break the rule of the company. So what should you do to achieve this?

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.