You administer a SQL Server 2008 instance for your company. Your company has a team
of database administrators. A team of application developers create SQL Server 2008
Integration Services (SSIS) packages on the test server in a shared project. One of the
packages requires a fixed cache file. On completion of development, the packages will be
deployed to the production server. Only the database administrators can access the
production server. You need to ensure that the application developers can deploy the
project successfully to the production server. What should you do?

You administer a SQL Server 2008 infrastructure. You administer two SQL Server 2008
instances named Instance1 and Instance2. Instance1 contains the Sales database, and
Instance2 contains the Accounts database. A procedure in the Sales database starts a
transaction. The procedure then updates the Sales.dbo.Order table and the
Accounts.dbo.OrderHistory table through a linked server. You need to ensure that the
transaction uses a two-phase commit. What should you do?

You administer a SQL Server 2008 instance named Instance1 at the New York central site.
Your company has a sales team to fulfill purchase orders for customer requests. The sales
team uses portable computers to update data frequently in a local database. When the
portable computers connect to the central site, the local database must be synchronized
with a database named Sales. You plan to create a replication model to replicate the local
database to the Sales database. The replication model must meet the following
requirements: Data conflicts are handled when multiple users update the same data

independently. The sales team cannot update sensitive data such as product price. The
sales team can synchronize data at scheduled times and on demand also. You need to
identify the best model to replicate data by using minimum development efforts. What
should you do?

You are a professional level SQL Sever 2008 Database Administrator. It is reported by the
customers that the server performance degraded due to a newly implemented process.
Dynamic Management Views is utilized to confirm that no long running queries exist. The
operating system performance data should be correlated with the actual query execution
trace, and the least administrative effort should be utilized. Which action will you perform to
finish the task?

You are a professional level SQL Sever 2008 Database Administrator. The computer on
which the instance run has the following three features: 64-GB RAM, four quad-core
processors, and several independent physical raid volumes A transactional database will be
implemented on the instance. In addition, the database should have a high volume of
INSERT, UPDATE, and DELETE activities, creation of new tables is contained by the
activities. You need to maximize disk bandwidth and decrease the contention in the storage
allocation structures so as to have the performance of the database optimized. Which action
will you perform to finish the task?

You are a professional level SQL Sever 2008 Database Administrator. After a regular test,
you find that performance degradation is experienced by an instance for the three reasons:
Excessive CPU usage, Server processes paging and Deadlocks A monitoring solution
should be implemented to provide data, monitor and troubleshoot performance issues and
detailed deadlock information should be contained in the provided data. You should utilize
the least amount of administrative effort to finish the task. Which tool will you utilize to finish
the task?

You are a professional level SQL Sever 2008 Database Administrator. There are 30 branch
offices in DoubleSecurity Insurance, and in the branch offices, customer data are stored in
SQL Server 2008 databases. Customer data should be security compliant if it is stored
through multiple Database instances. You intend to utilize the Policy-Based Management
feature to design a strategy for custom policies. And the format of custom policies is XML
format. The requirements listed below should be satisfied. The company distributes custom
policies to all instances. In addition, the company enforces the policies on all instances. A
strategy should be thought out and the minimum amount of administrative effort should be
utilized. Which action should you perform to finish the task?

You are a professional level SQL Sever 2008 Database Administrator. All data changes are
implemented through stored procedures, and only the INSERT, UPDATE, or DELETE
statements are utilized by the procedures. A backup strategy should be implemented. The
business requirements listed below should be satisfied: Point-in-time recovery for failure
is supported by the backup strategy at any time of day. The least amount of disk space
should be utilized by the transaction log. Which action should you perform to finish the task?

You are a professional level SQL Sever 2008 Database Administrator. You are experienced
in managing databases in an enterprise-level organization, optimizing and sustaining the
database life cycle. In the company, your job is to implement solutions on security,
troubleshooting, deployment and optimization. A SQL Server 2008 infrastructure is
managed by you. A database is utilized by the instance, and the database is utilized by a
Web-based application. 15,000 transactions are processed by the application every minute.
A column is contained by a table in the database, and the column is utilized only by the

application. Sensitive data is stored in this column. The sensitive data should be stored with
the highest security level. In addition, the least amount of memory space and processor
time should be utilized. From the following four encryption types, which one should you
utilize?

You are a professional level SQL Sever 2008 Database. A new database application is
hosted by the instance. The security requirements should be designed for the application. A
unique login to the SQL Server 2008 server is assigned to each application user. Stored
procedures are included by the application database to execute stored procedures in the
MSDB database. SQLAgent jobs are scheduled by the stored procedures in the MSDB
database. Since you are the technical support, you are required to confirm that the stored
procedures in the MSDB database should be executed by utilizing the security context of
the application user. Which action should you perform?