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. And you’re in charge of SQL Server 2008 instance. Now you deploy a new database named TCK which manages large documents. The documents will be revised sometimes. Fast read access must be permitted by developing a table structure. Meanwhile, you have to reduce storage space requirements. So what should you do? (Each correct answer presents part of the solution. Choose two.)

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. Minimally logged operations are performed on the Funds database. You have to ensure that the database can be restored to a specific point in time. So what should you do to achieve this?

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.

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 SQL Server 2008 instance. Now you deploy a new database named TCK which manages large documents. The documents will be revised sometimes. Fast read access must be permitted by developing a table structure. Meanwhile, you have to reduce storage space requirements. So what should you do? (Each correct answer presents part of the solution. Choose two.)

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. And you are in charge of a SQL Server 2008 instance. You intend to design the security requirements for a new database application which uses a code segment.
The code segment includes the components bellow: A class definition which uses public static fields A method which accesses the registry on the SQL Server A method which accesses the file system on a network file server The code segment also uses SQL CLR integration and is implemented as a single assembly. So what should you do to deploy the application to the instance successfully?