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 which contains various SQL Server Agent jobs. The jobs use seven shared schedules to run each job every day. You have to make sure that a job named Job1 doesn’t run Wednesdays any longer. So what should you do to achieve this?

You are the administrator of your company network. Now you’re in charge of a SQL Server 2008 instance.
A user called Jack is running a query and he reports that he is waiting for it to complete. So you have to identify whether the query is blocked. So which tool should you use to achieve this?

You are the administrator of your company network. You are reviewing and configuring the security of a SQL Server 2008 instance. The instance includes database DB1.The security audit policy requires that only successful and failed logon attempts are recorded in log files, besides this, if records cannot be written to the log files, the SQL Server instance must be shut down. You have to configure the SQL Server instance to be coordinated with the security audit policy. Which Transact-SQL statements should you run?

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. 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. You are in charge of a SQL Server 2008 instance. A management data warehouse uses the data collector to collect performance data. You intend to maintain the management data warehouse. You have to implement a process that routinely gathers and uploads data in the management data warehouse on different schedules. What data collection process should you implement?

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 instance.
You have to make sure that the names of all user-defined stored procedures contain the prefix usp_ on all instances. Besides this, you have to make sure that you cannot create stored procedures that do not contain this prefix. So what should you do?

You are the administrator of your company network. You are in charge of three SQL Server 2008 instances named Instance1, Instance2, and Instance3. Each of them runs on a separate server.
Between Instance1 and Instance2, a mission-critical database is mirrored. At present Instance1 currently acts as the Principal, while Instance3 acts as the witness. You intend to apply a patch on both servers. The server needs to restart itself. You have to identify the sequence of steps necessary to ensure that the patching process is completed with the minimum time and the database is online on the partner that currently does not apply the patch. Besides this two, you have to ensure that the database does not failover to the other partner during this time.So what should you do?