SQL Server 2008 MCITP 70-432 Exam Review

You are the administrator of a SQL Server 2008 instance.
All the instances run on their independent server. The three instances are respectively named InstanceA, InstanceB, and InstanceC. Between InstanceA and InstanceB, you configure a mirroring mission-critcal database. InstanceA currently acts as the Principal and InstanceC acts as the witness.
You intend to apply a patch to both servers as soon as possible, the patch needs to restart the server.
You must make sure that the database is online on the partner that currently does not apply the patch and during this time the database does not failover to the other partner.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
In the Services schema there is a table named Service.Table.
The Services.Table has to be moved from the Sales schema to a new schema named Costs.
Which Transact-SQL statement should you execute?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
which contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
05
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1,
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5
CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The table below shows the two tables of the Dworks database:
There are two partitions in the BillHistory table.
Partition1 is empty while partition2 contains all data.
You intend to move data from partition2 to the appropriate partition of the BillHistory table.
Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process to achieve this.
You have to make sure that at the end of every month the process can be repeated.

You are the administrator of a SQL Server 2008 instance.
You notice that the application has a poor performance.
On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.
You use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
According to the company requirement the names of all user-defined stored procedures must contain the prefix usp_ on all instances.
You have to make sure that stored procedures that do not contain this prefix cannot be created
by you.
What should you do?

You are the administrator of a SQL Server 2008 instance.
There is a n On-Line Analytical Processing (OLAP) database named in the instance.
The database contains a dimension table named Clients.
Ever hour backup of data of the Clients table is performed.
But the Clients table contains redundant data.
You must keep the disk space used to store the Clients table.
Which compression technology should you use?

You are the administrator of a SQL Server 2008 instance.
There is a text file which is named SQL01.log on the E: drive.
A SQL Server Agent job executes every 2 minutes and logs information to the text file.
You get a report from users saying that the sever doesn't make response.
As the technical support, you check and find that the SQL Server Agent service does not run and the SQL Server Agent job no longer functions.
You have to identify the reason why the SQL Server Agent service is unresponsive.
Which log should you check?

You are the administrator of a SQL Server 2008 instance.
A SQL Server Agent job is failing.
You find that the job history information is incomplete and seems to be truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
On DB1, quite few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be restored to a specific point in time.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
According to the company requirement, the names of all user-defined stored procedures
must contain the prefix usp_ on all instances. Besides this, you must make sure that stored procedures that do not contain this prefix cannot be created by you.
What should you do?

You are the administrator of a SQL Server 2008 instance.
The company CIO wants you to configure FILESTREAM data and the two requirements below must be met: you must enable FILESTREAM for file I/O streaming access; remote client computers must be able to have streaming access to FILESTREAM data.
You have to make sure that FILESTREAM data is enabled.
Which service should you configure?

You are the administrator of a SQL 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?

You are the administrator of a SQL Server 2008 with a named Instance01.
Instance01 contains a database which is named Dworks.
The bulk-logged recovery mode is used by the Dworks database.
You logship the Dworks database through a WAN link.
A scheduled job rebuilds the indexes of the Dworks database.
You find that the size of the log backups for log shipping has been enlarged by the job.
You have to reduce the size of the log backup.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB01.
The company CIO wants all the application developers can capture traces to troubleshoot the application that uses the database DB1, and they can perform this by using SQL Server Profiler.
But they must have given little permission as possible.
The company CIO assigns this task to you.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
You have to perform the backup of the default trace according to the requirement of your failure recovery plan.
The default trace is contained in the subdirectory.
You have to backup the subdirectory.
Which SQL Server database engine subdirectory should be backed up?

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
The backup strategy for DB1 has the following requirements:
every day at 9:00 full database backup to a file named cash.bak is performed;
At 9:15 a transaction log backup to a file named cash_HHMM.trn is performed every 15 minutes.
DB1 is being used in single-user mode.
Today a user reports that a query deleted some data by accident, the query was executed at 9: 25.
Since the deleted data is quite important, the data has to be restored to its original state.
You must achieve this goal.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance.
At present about 90 GB of data is stored in DB1.
You made some rough calculations that every day about 1 GB of data is modified or inserted.
You set the recovery model of DB1 to Simple.
According to the company requirements, during business hours, data loss of more than 120 minutes worth of transactions is unacceptable.
You have to choose a backup strategy which must comply with the following two requirements:
- Using as little disk space as possible
- Complying with the company requirements.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
The latest differential backup is performed at 15:30, the full back up was performed at 13:30,
and database snapshots were created at 16:30 and at 17:30.
The backups and the database snapshots are stored on a different disk from the database files.
At 17:05, the hard disk containing the database files fails at 17:02 hours.
You must restore DB1 while reduce data loss to the least.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
There is a database named DB1 in the instance.
There are data file and the transaction log file on the E: drive.
You find that there is only 6% spare space.
Both files have to be moved to the V: drive.
Which procedure should you use?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
A backup of DB1 is performed every day.
You have to minimize the size of the full database backup files of DB1.
Which Transact-SQL statement should you use?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
When you are absent, a user will use a login named Mary to log in and maintain the database snapshots. The user has to delete the database snapshots for DB1, so you have to give the appropriate permissions to the user.
Which database permission should you give the user?

You are the administrator of a SQL Server 2008 instance with a database named DB1 in the instance.
The DB1 database includes spatial data types. On DB1 database, you have to perform a database consistency check to include the spatial indexes. Besides this, you must make sure that you can reduce the effect on the database concurrency to the least.
Which Transact-SQL statement should you run?

You are the administrator of a SQL Server 2008 instance.
A SQL Server Agent job is failing.
You find that the job history information is incomplete and seems to be truncated after you review it.
All information produced by a job must be available for viewing.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance containing a database named DB1.
An application which is continuously connected uses DB1.
The application extensively uses the INSERT command and triggers the population of multiple tables. Now you notice that the application has a poor performance.
You doubt that this problem has something to do with blocking.
You have to monitor the state of the instance at regular intervals while not affecting the application performance further.
Which tool should you use?

You are the administrator of a SQL Server 2008 instance.
You upgraded this instance to SQL Server 2008.
You configure the Agent service to use the LocalSystem account.
There is a file named Orderdata on a remote network share.
A job accesses this Orderdata file by using a CMDExec step.
The job step fails to complete execution after the upgrade.
You have to make sure that the job can complete execution, so you have to configure the job step.
Which action should you perform?

You are the administrator of a SQL Server 2008 instance.
A user reports that when he runs a query, it takes a quite long time and it is still unfinished.
As the IT support, you have to verify whether the query is blocked.
Which tool should you use?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You have set the recovery model of DB1 to Full.
At 2:00 There is a full database backup of all the user databases every day.
Every quarter the transaction log backup is performed.
Every 4 hours a differential backup is occurred.
You are going to perform a full backup of DB1 at 11:00.
When you perform this back up, you must make sure that the overall backup will not be affected. What's more, you have to restore procedures for DB1.
You must make sure that the backup files are restored in proper sequence.
Which Transact-SQL statement should you use?

You are the administrator of a SQL Server 2008 instance.
You get an order from your company CIO to identify whether a database integrity check (DBCC CHECKDB) was run for a particular database.
Which log file should you examine?

You are the administrator of a SQL Server 2008 instance.
Users report that they are unable to connect to the named instances.
You check and verify that they can only connect to the default instance.
You also ensure that all SQL Server instances run normally.
You have to start the service which is required to connect to the named instances.
Which service should you start?

You are the administrator of a SQL Server 2008 instance.
There are user-defined stored procedures.
You have to ensure two things.
First the names of all user-defined stored procedures must contain the prefix usp_ on all instances; secondly stored procedures wich do not contain this prefix cannot be created by you.
What should you do to ensure these two?

You are the administrator of a SQL Server 2008 instance.
A new SQL Agent job has been created by you. The job includes a Windows PowerShell job step which transfer data between servers by using the SQLCmd utility. If the job fails, you have to make sure that an operator named ReactGroup is informed by receiving an e-mail.
What action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
There is a stored procedure. The stored procedure implements a database maintenance process.
A SQL Server Agent job should be created to that runs the stored procedure. Besides this,
You have to make sure that after it is completed successfully, the job is removed.
What should you do?

You are the administrator of a SQL Server 2008 instance which contains multiple Agent jobs.
The jobs run each job on each day of the week by using seven shared schedules.
You have to make sure that a job named Job4 doesn't run Wednesday.
Which action should you perform to achieve this goal?

You are a database developer for your company.
You use the following Transact-SQL code.
SELECT e.Resume,j.EmployeeID FROM
JobCandidate j, Employee e
WHERE j.EmployeeID =* e.EmployeeID
You need to rewrite this code to be ANSI compliant.
How should you rewrite the code?

You are the administrator of a SQL Server 2008 instance.
There is a management data warehouse that uses the data collector to collect performance data. You intend to maintain the management data.
You want to implement a data collection process.
The process can gather and upload data in the management data warehouse on different schedules.
Which process should you implement?

You are the administrator of a remote SQL Server 2008 instance.
You notice that the application has a poor performance.
On the remote SQL Server instance, you capture a workload of the remote instance to a trace table by using SQL Profiler.
You have to use the Database Engine Tuning Advisor to analyze the workload of the remote SQL Server instance on a local SQL Server instance.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
According to the company requirement, you have to move the application from Microsoft SQL Server 2000 to Microsoft SQL Server 2008.
You have to monitor the SQL Server instance to record the use of features.
These features will be discontinued. What should you do?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has two filegroups: one is a filegroup named History that contains historical data stored in the G: volume; the other is the PRIMARY filegroup that contains the On-Line Transaction Processing (OLTP) data stored in the F: volume.
The backup strategy of DB1 has three requirements:
- first, the History filegroup should be backed up at the end of each working day;
- second, the PRIMARY filegroup should be backed up five times a day;
- third, the transaction log should be backed up every quarter.
You get a report at 13:00 saying that the volume which contains the History filegroup fails.
As the technical support, you have to restore the database, and you must reduce the data loss to the least as soon as possible.
What should you do?

You are the administrator of a SQL Server 2008 instance.
Your company CIO assigns you a task.
You have to check the performance of the SQL Server 2008 instance.
You have to find out which common language runtime (CLR) queries takes the longest-running time.
Which dynamic management view should you choose to use?

You are a database developer for your company.
You are developing a sales report for a given time period.
The report must list all sales representatives and their position based on their total sales for the period. If two sales representatives had the same total sales, they should be assigned the same position. A sample output is shown in the following data:
SALESREPID SALES POSITION
456 123,456 1
765 123,456 1
983 103,762 3
106 101,101 4
You need to create the report to fulfill the requirements.
Which operator should you use?

You are the administrator of a SQL Server 2008 instance which hosts several applications in your company.
A job named DeliveryList is created by you.
This job requires a file to be written to a file server.
Because the job cannot access the file server, it fails to run.
You intend to configure the SQL Server Agent service so only the SQL Server Agent service has read and write access to the file server.
Which account type should you use?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
On DB1, quite a few logged operations are performed.
Now according to the requirement of the company CIO, you must validate that the database can be restored to a specific point in time.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
There is a SQL Server 7.0 database.
You upgrade the database to the SQL Server instance.
According to the company requirement, you have to make sure that suspect pages can be detected in the database.
Which action should you perform to achieve this goal?

You are a database developer for your company.
You are developing a stored procedure that must accept a parameter of the xml data type and store the data in a relational table structure.
You need to write the stored procedure to fulfill the requirements.
Which functionality should you use?

You are a database developer for your company.
You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure.
This cursor must have a fixed membership where the order of the rows does not change.
The data in the rows that are retrieved through the cursor should be updatable.
These updates should be viewable after they are made.
While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor.
You need to create the stored procedure to fulfill these requirements.
Which option should you use in the DECLARE CURSOR statement?

You are the administrator of a SQL Server 2008 instance.
You configure the SQL Server instance to use the -T1222 and -T1204 trace flags during startup.
You have to make sure that your failure recovery plan performs backup of the use of the trace flags.
Which action should you perform to achieve this goal?

You are the administrator in charge of two instances both of which run on the same computer.
One is a SQL Server 2008 instance; another is a SQL Server 2005 instance.
There is a database named DB1 in the SQL Server 2008 instance.
DB1 uses the Fulltext indexes.
Several records that include the word "root" are added to DB1.
An empty resultset is returned when the Fulltext index is queried for the word "root."
You have to make sure the query can return records that contain the word "root".
What should you do?

You are the administrator of a SQL Server 2008 instance.
You make sure that all SQL Server instances are consistently configured for naming conventions, security settings, force index creation and avoidance of data fragmentation.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The Dworks database has a table named Orderthings. The Orderthings table is partitioned on the OrderId column. The first partition contains integer values greater than 100,000, while the second partition contains integer values between 1 and 100,000.
You have to add a new partition.
The new partition should contain integer values greater than 200,000.
What should you do?

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
You get a report from users saying that DB1 meets deadlock problems.
As the technical support, you have to capture the deadlock information to the SQL Server error log.
What should you do?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named Dworks in the instance.
The Dworks database has a table named Orderthings. According to the company requirement, you have to export all data from the Orderthings table to a file.
During the export, you must make sure that the data export process is saved for reuse and a Microsoft Office Open XML document format is used.
What should you do?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database which is named Sellings in the instance.
The Sellings database contains a table named Productions.
The table is used to stores information about all types of products.
The Productions table is often queried by users on the basis of the RadioSize column.
The RadioSize column contains the NULL value for all products other than Radios.
Currently no index exists on the RadionSize column.
According to the requirement of the company CIO, you have to optimize the query performance and reduce the effect on the disk space to the least.
Which action should you perform to achieve this goal?

You work in a Organization which is named Wiikigo Corp.
The Organization uses SQL Server 2008.
You are the administrator of the Organization database.
Now you are in charge of a SQL Server 2008 instance.
Look at the following query:
SELECT s.*, i.*
FROM SensitiveTbl AS s
INNER JOIN InsensitiveTbl AS i
ON i.OrganizationName = s.OrganizationName
You use the above query to join two tables on a column named OrganizationName.
The following error is returned when you execute the query.
"Msg 468, Level 16, State 9, Line 17 Cannot resolve the collation conflict between 'SQL_Latin1_General_CP1_CS_AS' and 'SQL_Latin1_General_CP1_CI_AS' in the equal to operation."
The ON clause of the query has to be modified so that it can perform a case-sensitive joinsuccessfully.
What should you do?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is a database named OrderIn in the instance.
The OrderIn database contains a table which is named OrdeSend.
A column named SendSite which is of the Geography data type has been added to the OrdeSend. The OrderSend table contains no indexes.
On the SentSite point, you have to create a spatial index.
What should you do first?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The server has a very large database named DB1.
An application which is constantly available uses DB1.
Now you get a report from users, in the report users complain that server has a poor performance. As the technical support, you have to improve the performance of the application by using the Database Engine Tuning Advisor.
Besides this, you must make sure that the action of analyzing the workload will not affect the performance of the production server.
What should you do?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
Today you notice that applications that run on the server have poor performances.
You doubt that this has something to do with table scans.
You have to capture the appropriate information by using an appropriate Windows System Monitor object.
Which performance object should you use?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
The server hosts databases for several mission-critical applications.
Microsoft SQL Server Management Studio executes queries and it has some effect.
Now you intend to limit the effect by using the Resource Governor.
You have to make sure that queries initiated through SQL Server Management Studio is less than
20 percent of CPU utilization, besides this, you must make sure that queries initiated by the missioncritical
applications can consume 100 percent of CPU utilization when required.
Which action should you perform to achieve this goal?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
You are going to use the data collector to gather performance data periodically on all instances.
You must store all collected data in the same database.
This database is hosted on a single instance. Every five hours, you have to collect and load performance data in the management data warehouse.
Which data collection process should you implement?

You are the administrator of a SQL Server 2008.
According to the company requirement, you are designing a consolidated repository of performance data.
You must make sure that the four requirements below are met:
1. the data collector is used to gather performance information
2. a single database stores performance information for all instances
3. performance information that is older than 15 days is deleted
4. reduce the administrative effort to manage performance to the least.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008, with a report often executed during business time.
There is a stored procedure that is used as the data source for the report.
You get a report from users saying that they receive the data returned by the report but the data is inconsistent.
As the technical support, you check and find that phantom reads cause this problem.
You have to make sure that consistent data is returned by the report while not affecting other users.
Which action should you perform to achieve this goal?

You are the administrator of a SQL 2008 two cluster-ready nodes.
You intend to perform an installation of a SQL Server 2008 mission-critical cluster.
Constant availability is needed by the mission-critical cluster.
You have to configure the SQL Server cluster to failover and reduce the service disruption to the least.
Which failover option should you use?

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
You are log shipping the Adventureworks database to a remote SQL Server 2008 instance.
The primary SQL Server instance required a restart during the weekend.
But you notice that log shipping has stopped working after the weekend.
You have to check log shipping of the Adventureworks database and find out the problem.
What should you do?

You are the administrator of a SQL Server 2008 with two instances.
The two instances are respectively named InstanceA and InstanceB.
There is a database named Dworks on InstanceA.
You move the Dworks database to InstanceB.
A user use a SQL Server login named Mary to access the database on InstanceA with the password "Tk@87#34".
On InstanceB, you create the same SQL Server login.
The user tries to use the SQL Server login Mark to access the Dworks database on InstanceB.
But the user gets an error message, according to the indication of the message, the access to the Dworks database has been denied.
You have to make sure that the user can access the Dworks database on InstanceB successfully.
On InstanceB, which Transact-SQL statements should you execute?

You are the administrator of a SQL Server 2008 cluster in a high security environment,
You intend to configure and use encrypted connections for the clustered virtual SQL Server.
You have to install a certificate.
The cetification will be used for encryption.
What should you do?

You are the administrator SQL 2008 instance with a database named DB1.
The company stores clients data in DB1.
Clients can access their profile data by using a Web application.
You must ensure the security of the customer data, you must ensure that even if the backup media is lost, data files, log files and subsequent backups and so on are quite safe.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
There is an internal application which uses Analysis Services and Reporting Services.
According to the requirement of company security policy, the surface area for all the deployed components of SQL Server 2008 should be configured.
You have to implement the security policy and devise a method to evaluate the security policy against other database servers.
What should you do?

You are the administrator of a SQL Server 2008 instance with a database named Service with
a database user named ServiceGreatUser.
A SQL Server login named ServiceGreatLogin maps to ServiceGreatUser in the Service database.
According to the requirement of the company CIO, the database user can only perform the two tasks below:
- the database user executes all stored procedures that currently exist in the Sales database
- executes all stored procedures that will be created in the Sales database.
What should you do to ensure this?

You are the administrator of a SQL Server 2008 instance.
There is a Windows group named Wiikigo\Service. According to the security policy, members of this group are allowed to set up new connections to the SQL Server instance.
But this is can only be done during office time from 8:00-20:00.
Other users may connect to the SQL Server instance any time.
You write the following Transact-SQL statements:

CREATE TABLE Security.RestrictedLogonHours (
Id in NOT NULL IDENTITY(1,1),
GroupName sysname NOT NULL,
RestrictedTimeStart time NOT NULL,
RestrictedTimeStop time NOT NULL,
CONSTRAINT RestrictedLogonHours_pk
PRIMARY KEY CLUSTERED(Id)
);

INSERT INTO Security.RestrictedLogonHours (
GroupName,
RestrictedTimeStart,
RestrictedTimeStop
)
VALUES (
'CONTOSO\Sales',
CAST('07:00' AS time),
CAST('19:00' AS time)
);

You need to implement the company security policy which is stored in the RestrictedLogonHours table.
What should you do?

You are the administrator of a SQL Server 2008 instance.
There is a database developer who is named UserJack.
UserJack views the definitions of all database objects in a database to read data from all user-defined tables, views, and table-valued functions.
For UserJack, you have to assign the required permissions.
Besides this, you must make sure that other developers can also be given the same permissions, but this should be achieved by executing as little Transact-SQL statements as possible.
Which Transact-SQL statements should you execute?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
The DB1 database contains the following stored procedure. (Line numbers are useded for reference only.)
01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Service.Table') IS NOT NULL
04 DROP TABLE Service.Table;
06 CREATE TABLE Service.Table (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
11 ...
12 GO
The following exception is raised when a user tries to invoke Procedure1, "Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You should grant the user access to execute Procedure1, you must assign only the required permissions.
Which action should you perform?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 has a table which is named Table01 and a stored procedure named Procedure01.
Procedure01 choose data from Table01 by using a sp_executesql Transact-SQL statement.
You company security rules forbid users to access tables directly in any database.
Look at the exception below:
"Msg 229, Level 14, State 5, Line 1 The SELECT permission was denied on the object 'Table01', database 'DB1', schema 'dbo'."
The exception is raised when Procedure01 is executed by users.
You have to make sure that a user can successfully execute Procedure1 complying with the company rules.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
DB1 is available to a Windows group which is named WIIKIGO\Supervisors.
A Windows user named User1 is a member of the WIIKIGO\Supervisors group. WIIKIGO\Supervisors is a member of the db_owner role in the DB1 database.
You have to make sure that the SQL Server instance is (not?) available to User1.
In the DB1 database, which Transact-SQL statement(s) should you execute?

You are the administrator of a SQL Server 2008.
There are queries that have an estimated execution cost less than 3.
You intend to configure the instance, make it use a single thread for these queries.
Which sp_configure configuration option should you set?

You are the administrator of a SQL Server 2008.
According to the company requirement, you intend to perform an installation of an instance of SQL Server Reporting Services (SSRS) to the same machine.
Now your company CIO wants version histories of all deployed reports can be kept.
As a technical support, what should you do to ensure this?

You are the administrator of a default SQL Server 2005 instance.
For a new application, you have to install a SQL Server 2008 instance on the server.
Your company CIO assigns this task to you.
You have to make sure that the respective certified third-party applications have access to both database instances.
You have to achieve this goal without changing the existing application environments.
What should you do to achieve this goal by using as little database administrative effort as possible?

You are the administrator of SQL Server 2008 instance.
There is a single Database Mail profile. Reports are sent to all the company employees by using the Mail profile. During office hours, a large volume of reports are sent by the sp_send_dbmail stored procedure.
Now you notice that it takes a long time for the reports to be sent to the company manager.
As the technical support, you have to speed up the delivery.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You put forward a database maintenance plan.
The maintenance plan rebuilds indexes, checks database integrity, writes a report to a text file.
The company requires that when the maintenance plan executes abnormally, the operators can be notified by an e-mail message.
You've been assigned this task.
So you have to make modifications on the SQL Server instance.
What should you do?

You are the administrator of a SQL Server 2008 instance with a database named Finance.
The Finance database currently contains 100 GB of data. On an average, 1 GB of data is modified or inserted daily.The recovery model of the Finance database is set to Simple.
Business requirements specify that data loss of more than two hours worth of transactions during business hours is unacceptable.
You need to select a backup strategy that uses the least amount of disk space by adhering to the business requirements.
What should you do?

You are the administrator of a SQL Server 2008 instance named Instance01.
Instance01 contains a database which is named SellingHelp.
There isthe Products table in the SellingHelp database. Now according to the company requirement, the Products table has to be replicated to the SQL Server instances installed in laptops computers.
You have to create a Replication topology to perform this.
The Products table is updated from Instance01 when the laptops reconnect to the corporate network.
The Products table on Instance01 is frequently updated between reconnections.
You have to make sure that you can implement the Replication topology along with the Subscription type successfully and meanwhile reduce the bandwidth usage to the least.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance.
According to the requirement of the company CIO, users on the SQL Server instance must be able to use the OPENROWSET() function to query remote data source.
Which sp_configure configuration option should you configure?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
At the database a small percentage of data is modified everyday.
Ever day in the morning, you have to create a read-only copy of the database for reporting purposes and make sure that you use as little disk space as possible.
In the options below, which strategy should you use?

You are a database developer for your company.
You are writing a query that will search a resume text column for specified skills.
The results must include both synonyms and exact matches.
You need to write the query to fulfill the requirements.
Which Transact-SQL function should you use?

You are a database developer for your company.
You are developing a report that will return the customer name and phone number.
Customers might have a business number, a mobile number, and a fax number.
The report must return the first nonnull phone number for each customer.
You need to write the query that returns the required customer information for the report.
What should you use?

You are a database developer for your company.
You are creating a stored procedure that will use a Transact-SQL cursor to share the result set with other statements in the stored procedure.
This cursor must have a fixed membership where the order of the rows does not change.
The data in the rows that are retrieved through the cursor should be updatable.
These updates should be viewable after they are made. While implementing this cursor, you need to minimize memory usage regardless of the resulting effect on the speed of the cursor.
You need to create the stored procedure to fulfill these requirements.
Which option should you use in the DECLARE CURSOR statement?

You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks.
The AdventureWorks database contains the Products table.
You create a Merge Replication topology and a Publication to replicate the Products table to the SQL Server instances at remote locations.
The Publication has a 21-day retention period.
When a user returns from a one-month vacation, she discovers that her database does not contain the most recent data.
The Windows Event log states the following error message:
"Replication: expired subscription dropped."
You need to obtain the most recent data in the database of the user.
You also need to ensure that future data changes are appropriately replicated.
What should you do?

You are the administrator of three SQL Server 2008 instances named Instance1, Instance2, and Instance3.
Each of the three instances runs on a separate server.
A mission-critical database is mirrored between Instance1 and Instance2.
Instance3 acts as the witness. Instance1 currently acts as the Principal.
You plan to apply a patch to both servers.
The patch requires a restart of the server.
You need to find out the sequence of steps necessary to ensure that the following requirements are met:
1. The patching process is completed in the shortest possible time.
2. The database is online on the partner that currently does not apply the patch.
3. The database does not failover to the other partner during this time.
What should you do?

You are the administrator of multiple SQL Server 2008 instances.
You are designing a consolidated repository of performance data.
You need to ensure that the following requirements are met:
1. The data collector is used to gather performance information.
2. A single database stores performance information for all instances.
3. Performance information that is older than 14 days is deleted.
4. Administrative effort to manage performance data is minimized.
What should you do?

You are the administrator of a SQL Server 2008 instance. .
You plan to maintain a management data warehouse that collects performance data by using the data collector.
You need 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 a SQL Server 2008 instance.
Users report that the performance of the application is poor.
You use SQL Profiler to capture a workload of the remote instance to a trace table on the remote SQL Server instance.
You need to analyze the workload of the remote SQL Server instance on a local SQL Server instance by using the Database Engine Tuning Advisor.
What should you do?

You are the administrator of a SQL Server 2008 instance.
A user named Mary reports that she is waiting for a query to complete.
You need to ascertain whether the query is blocked.
Which tool should you use?

You are the administrator of a SQL Server 2008 instance that runs on a computer that hosts several applications.
You configure the SQL Server Agent service to run by using the SERVER1\AGENT account.
You create a job named MailingList that requires a file to be written to a file server.
The job fails to run because it does not have appropriate access to the file server.
You plan to configure the SQL Server Agent service.
You need to perform the configuration such that only the SQL Server Agent service has read and write access to the file server.
Which account type should you use?

You are the administrator of a SQL Server 2008 instance with a database named Sales.
The Sales database has a table named Products that stores information about all types of products. Users frequently query the Products table based on the TelevisionSize column.
The TelevisionSize column has the NULL value for all products other than Televisions.
There is currently no index on the TelevisionSize column.
You need to improve the query performance by ensuring that the effect on the disk space is minimized.
What should you do?

You are the administrator of a SQL Server 2008 instance.
The instance contains an On-Line Analytical Processing (OLAP) database along with a dimension table named Customers.
The data of the Customers table is updated every hour.
The Customers table contains redundant data.
You need to conserve the disk space used to store the Customers table.
Which compression technology should you use?

You are the administrator of a SQL Server 2008 instance with a database named AdventureWorks.
The AdventureWorks database contains two tables as shown in the following table:
1. The Orders table contains a table partition for each month.
2. The OrderHistory table contains two table partitions; one partition contains all data and the other partition is empty.
3. You plan to develop a process to move data from the partition that contains data for the oldest month of the Orders table to the appropriate partition of the OrderHistory table.
4. You need to ensure that the process can be repeated at the end of every month.
What should you do?

You are the administrator of a SQL Server 2008 instance with a database named Finance.
Minimally logged operations are performed on the Finance database.
You need to verify that the database can be restored to a specific point in time.
What should you do?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
You have set the recovery model of DB1 to Full.
The table below shows the backup strategy for DB1.
Your company stores the system databases, user database data, and log files on separate disks.
A user report that the hard disks which store the user database data files have failed at 15:00 on Wednesday. According to the requirement of the company CIO, you have to restore DB1 from the database backups, and you must reduce the data loss as soon as possible.
Which action should you perform to achieve this goal?

Day Sunday Weekdays Weekdays
Type of Backup Full backup Differential backup Transaction Log backup
Time 01:00 22:00 8:00 12:00 16:00

You are the administrator of a SQL Server 2008 instance.
The instance contains a database named Finance.
The recovery model of the Finance database is set to Full.
You deploy a new process that modifies 10,000 records from the Accounts table at 19:00 hours daily.
You need to ensure that any modification to the data can be reverted without the database going offline.
Which strategy should you implement?

You maintain a SQL Server 2008 instance.
You create a new SQL Agent job that includes a Windows PowerShell job step.
The PowerShell job step uses the SQLCmd utility to transfer data between servers.
You need to ensure that an operator named ResponseTeam is notified by an e-mail message if the job fails.
What should you do?

You are the administrator of a SQL Server 2008 instance.
The instance has databases for a finance application and a manufacturing application.
You want to put some limitation on the maximum CPU query time allowed by the applications, so you configure the Resource Governor to achieve this.
But you notice that certain reports do not execute successfully any longer on the manufacturing application.
You have to allow the manufacturing application to consume more CPU time, so you have to change the Resource Governor configuration.
Which Resource Governor component should you configure?

You are the administrator of a SQL Server 2008 instance.
For some reason, the company wants to view the number of users who access the SQL Server instance. The company CIO assigns this task to you.
You have to use Windows System Monitor to find out the concrete number.
Which performance object should you capture?

You are the administrator of a SQL Server 2008 instance with a database named Dworks.
The Dworks database has a table named Bills which contains several indexes and a great amount of rows.An enterprise Web environment is supported by the database. On the Bills table 28% of the indexes have been fragmented.
You have to defragment the indexes, making sure that you reduce the effect on database availability to the least.
In the ALTER INDEX statement, which option should be included?

You are the administrator of a SQL Server 2008 instance with a database named named Dworks.
The table below shows the two tables of the Dworks database:
There are two partitions in the BillHistory table.
Partition1 is empty while partition2 contains all data.
You intend to move data from partition2 to the appropriate partition of the BillHistory table.
Partions2 contains data for the oldest month of the Bills table.
You are going to develop a process toachieve this.
You have to make sure that at the end of every month the process can be repeated.
Which actions should you take?

Table Name Function
Bills Store bill information of the current year
BillHistory Store bill information of the last years

You are the administrator of a SQL Server 2008 instance.
The company wants to export data from SQL Server to a Microsoft Excel file.
The company assigns this task to you.
You perform this by using the SQL Server Import and Export Wizard.
After this, you save the package definition to a file.
Now you have to reexecute the package from the command line.
Which utility should you choose to use?

You are the administrator of a SQL Server 2008 instance with a database named named Dworks.
The Dworks database contains a table named BillDetails which has a clustered primary key named BillId on the BillId column.
The BillDetails table has a single XML column named GoodsDetails.
The XML column has an XML index and XML data are stored in this XML column.
A new column named GoodsID is added to the BillDetails table.
GoodsID must be included in the primary key.
Which action should you perform to achieve this goal?

You are the administrator of a SQL Server 2008 instance with a database named DB1.
According to the requirements of the security audit policy only successful and failed logon attempts are recorded in log files.
if records cannot be written to the log files, the SQL Server instance is shut down.
You have to make the SQL Server instance comply with the security audit policy by performing
the configuration.
Which Transact-SQL statements should you run?

Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS).
Which is correct?

You are mastering the company database called TesterDB;
The database includes a database that uses SQL Server log shipping.
Log shipping will happen half a hour.
You should make the full backup.
In order to cut the net bandwidth utilization when maintenance every night.
Which is the correct answer?

You are mastering the company database, Users complaints that the SQL Server 2008 application is running slowly.
You should check the information of the CPU utilization, disk utilization, and memory utilization. The network should be considered.
The detailed information should be checked for the proper example of SQL Server2008.
Which is the correct answer?

You maintain an instance of Microsoft SQL Server 2008.
The instance contains a database named Finance.
The recovery model of the Finance database is set to Full.
A full database backup of all the user databases is performed at 02:00 hours daily.
The transaction log backup occurs every 15 minutes.
A differential backup is performed every 4 hours.
You plan to perform a full backup of the Finance database at 11:00 hours.
You need to ensure that the backup is performed without affecting the overall backup and restore procedures for the Finance database.
You also need to ensure that the backup files are restored in proper sequence.
Which Transact-SQL statement should you use?

You are mastering the company database, and managing 20 SQL Server 2005 computers which are operated by other administrators that are asked to meet company service level agreements (SLAs) on querying response time.
Some of the method on controlling query response times on the servers should be told for these administrators.
Which is the correct answer?

You are managing a SQL Server 2008 instance which includes some applications for Home.com.
You set the SQL Server Agent service to implement through utilizing the SerTest\Test account.
You create a job named MailingTester that allows a file to be stored to a log server.
The job could not run normally because it could not own proper access to the log server.
You decide to set the service of SQL Server Agent.
You should make sure that you could run the settings such that the SQL Server Agent service owns all access to the log server.
Which is the correct answer?

You are the administrator of a SQL Server 2008 instance.
You determine that you need more information about SPID 56.
Which of these do you do?

You administer a SQL Server 2008 instance.
The instance contains a database named DB1.
You plan to allow all the application developers to use SQL Server Profiler to capture traces to troubleshoot the application that uses the database DB1.
You need to grant the minimum necessary permission to the application developers.
What should you do?

You are a database administrator for your company.
The company uses a SQL Server 2008 database that includes a table named Inventory.
The table contains a column named Price. A company policy states that the value in the Price column cannot be decreased by more than 10 percent in any single database operation.
Updates to the Price column are made by various means, including by using ad hoc queries.
You need to ensure that this company policy is enforced.
What should you do?

You maintain a SQL Server 2008 instance that contains a database named DB1.
DB1 stores customer data for the company.
The customers use a Web application to access their profile data.
You need to protect the customer data such that data files, log files, and subsequent backups are as secure as possible even if the backup media is lost.
Your solution must not affect the Web application or impact performance.
What should you do?

You administer a SQL Server 2008 instance that contains a database named DB1.
A table named Sales.Table1 exists in the Sales schema.
You need to move the Sales.Table1 table to a new schema named Billing.
Which Transact-SQL statement should you execute?

You are a database administrator for your company.
There are four automated testing areas for finished items in the company's manufacturing floor. SQL Server 2008 is used to reserve testing results for every testing area.
You should create a replication method to make sure that test results flow from the testing areas to the SQL Server quickly.
The results of testing will be used on reporting.
Which is the correct answer?

You administer a SQL Server 2008 instance that contains a database named DB1.
The DB1 database contains the following stored procedure. (Line numbers are included for reference only.)
When a user named User1 attempts to invoke Procedure1, the following exception is raised:
"Msg 262, Level 14, State 1, Procedure Procedure1, Line 5 CREATE TABLE permission denied in database 'DB1'."
You need to provide User1 access to execute Procedure1 by allocating only the required permissions.
What should you do?

01 CREATE PROCEDURE Sales.Procedure1
02 AS
03 IF OBJECT_ID('Sales.Tables1') IS NOT NULL
04 DROP TABLE Sales.Table1;
05
06 CREATE TABLE Sales.Table1 (
07 Id int PRIMARY KEY CLUSTERED,
08 Name varchar(100);
09 );
10
11 ...
12 GO

You are managing a SQL Server 2008 instance for Home.com.
You use a Microsoft SQL Server 2008 Analysis Services (SSAS) instance.
A data mining structure should be deployed which contains the Microsoft Clustering mining model. From Scalable K-Means to Non-scalable K-Means.
You should alter the attribute of the mining model.
Which is the correct answer?

You are mastering the company database, including the SQL Server 2005 database which include a table called Production.
Product. The table has more than 50 columns
The most common queries are listed below Transact-SQL statements.
--QUERY 1SELECT [Name], [ProductLine], [ListPrice], [Class], [Style] FROM [Production]. [Product]WHERE [ProductLine] = 'T' --QUERY 2SELECT * FROM [Production]. [Product] ORDER BY [Name]
When you are minimizing the space required for the indexes.
You should select the best indexes to maintain the most common searched to the Production. Product table Which is the correct answer?

You are mastering the company database, with SQL Server 2008 computer named SQLTEST1
The master database on SQLTEST1 is down.
Some SQL Server Agent jobs should be included in the SQLTEST1.
The whole database should be backed up every day.
You rebuild and restore the master database.
You should make sure that SQLTEST1 functions as it did before the database lost.
Which is the correct answer?

You are mastering the company database called Testing;
you should back up the Testing database fully.
You do transaction log once every two hours from 08:00 to 17:00.
You should run database snapshots for the Testing database.
You should develop database snapshot every day at 07:00.
At 8:30 the user removes the whole information on the Testing database which was entered intothe CurrentSpeaker table yesterday. Deletion is the first step for the database.
It is now 11:45 and many other changes have happened in the database.
You should find solution to solve this problem .
Which is the correct answer?

You are mastering the company database,
you should check the log of the SQL Server 2008 Enterprise Edition everyday, and you get the information on the torn page errors of the database.
In order to fine the solution of the torn page, which is the correct answer?

You administer a SQL Server 2008 instance.
The instance contains various SQL Server Agent jobs that use seven shared schedules to run each job on each day of the week.
You need to ensure that a job named Job5 no longer runs on Thursdays.
What should you do?

You administer a SQL Server 2008 instance.
You have a stored procedure that implements a database maintenance process.
You need to create a SQL Server Agent job that runs the stored procedure.
You also need to ensure that the job is removed after successful completion.
What should you do?

You maintain a SQL Server 2008 instance that contains a database named Finance.
The data file and the transaction log file are located on the E: drive.
The E: drive has only 5 percent available space.
You need to move both files to the V: drive.
Which procedure should you use?

You are mastering the company database, with SQL Server 2008 computer named SQLDB.
The function of the SQLDB is to perform transaction log backups and so on.
SQL1 should be reset by another administrator using the tool of SQL Server Surface Area Configuration.
You should see that SQL1 no longer runs the maintenance tasks automatically.
On SQLDB, You should make sure the tasks of the maintenance are finished without men
Which is the correct answer?

You are mastering the company database,
On a SQL Server 2008, you find out that one of the data files computer is broken.
You should reserve the database which is from the most recent configurations of backups.
In order to cut the lost, you should find the method as quickly as possible.
Which is the correct answer?

Your workstation has installed Microsoft SQL Server Management Studio (SSMS), and not installed Microsoft Business Intelligence Development Studio (BIDS).
You should design a package, which has the following features:
- The package should be transactional.
- The package should be optimized for 10 tables.
- The package should be stored safely in the msdb database of a server which is remote.
Which is correct?

You maintain a SQL Server 2008 instance.
You find a SQL Server Agent job is failing.
When you review the job history information, you notice that the job history information is incomplete and appears to be truncated.
You need to ensure that all information produced by a job is available for viewing.
What should you do?

You administer a SQL Server 2008 instance that contains a database named SpatialDB.
The SpatialDB database includes spatial data types.
You need to perform a database consistency check on SpatialDB to include the spatial indexes. You also need to ensure that the effect on the database concurrency is minimized.
Which Transact-SQL statement should you execute?

You are mastering the company database; there is the transaction information which is for company's Web-based order system in the database of the company.
The database is set to utilize the full recovery model.
Between the hours of 20:00 and 04:00, you get the fewest orders; one hour should be accepted for the risk when the system is running Database backups should be run as quickly as possible.
You should create the solution to avoid this kind of risk.
What should you do?

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales.
The database is replicated between SQL1 and SQL2 by using transactional replication.
A full backup of each database is performed every night.
Transaction log backups are performed every hour.
Replication latency is typically less than two minutes.
One afternoon, the Sales database on SQLTEST1 becomes corrupted.
You are unable to repair the database.
The Sales database on SQLTEST2 is unaffected.
You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

You maintain a SQL Server 2008 Enterprise Edition instance that contains a database named Finance.
You need to reduce the size of the full database backup files of the Finance database.
Which Transact-SQL statement should you use?

You are managing a SQL Server 2008 instance which includes a database called TesterDB for Home.com.
The data file and the transaction log file which are located on the D: drive that owns only 10 percent useful space.
You should make sure that you move both files to the G: drive.
Which is the correct answer?

You are managing your company database.
You manage a SQL Server 2005 database called ContractTEST, which is down.
You operate a restore by using the following Transact-SQL script.
RESTORE DATABASE ContractTESTFROM contracts_bu_deviceWITH CONTINUE_AFTER_ERROR, CHECKSUM, RECOVERY
After the restore, the SQL Server logs show errors in some page restores.
What you should do is to repair the database pages, which are down, and facilitates repair by using the fastest method.
Which is the correct answer?

Yon maintain a SQL Server 2008 instance that contains a database named Finance.
The backup strategy for the Finance database specifies the following requirements:
- Full database backup to a file named finance.bak is performed at 08:00 hours daily.
- A transaction log backup to a file named finance_HHMM.trn is performed every 15 minutes starting at 08:15 horns.
The Finance database is being used in single-user mode.
At 08:40 hours, a user reports that some important data was accidentally deleted by a query that was executed at 08:23 horns.
You need to restore the database to its original state.
Which Transact-SQL statement(s) should you use?

You are managing a SQL Server 2008 computer called SQLTEST1.
You should configure the SQL Server service and the SQL Server Agent service to start automatically, and set SQLTEST1 to audit all user names and application names that plan to get information from SQLTEST1.
SQLTEST1 must operate auditing always, and you should keep the results for 10 years.
Which is the correct answer?

You are managing two SQL Server 2008 computers called SQLTEST1 and SQTEST2, which include a copy of a database named Sales.
The database is replicated between SQL1 and SQL2 by using transactional replication.
A full backup of each database is performed every night.
Transaction log backups are performed every hour.
Replication latency is typically less than two minutes.
One afternoon, the Sales database on SQLTEST1 becomes corrupted.
You are unable to repair the database.
The Sales database on SQLTEST2 is unaffected.
You need to return the Sales database on SQLTEST1 to normal operation as quickly as possible.
You must ensure a minimum loss of data and minimal impact to users of either server.
What should you do?

You are mastering the company database;
you should use the SQL Server 2005 database which enables business users to operate both ad hoc and predefined searches.
You doubt that some searched will cost too many resources.
You should clarify which searches cost the most resources.
You decide to finish this aim as quickly as possible.
Which is the correct answer?

You administer a SQL Server 2008 instance that contains a database named Sales.
A SQL Server login named SalesAppLogin maps to a database user named SalesAppUser in the Sales database.
You need to ensure that the database user can perform only the following tasks:
- Execute all stored procedures that currently exist in the Sales database.
- Execute all stored procedures that will be created in the Sales database.
What should you do?

You are mastering the company database, with a SQL Server 2008 computer called SQLTEST1.
There are three examples of SQL Server in the SQLTEST1.
The ability to connect to the dedicated administrative connection on every example of SQL Server should be included in the disaster recovery plan of the company.
You find that you could connect to the connection on SQLTEST1 default example while testing this ability; you could not connect to the dedicated connection on the two other examples on SQLTEST1.
You should clarify that the examples are implementing and that client applications should be able to approach them.
You should make sure that no other administrators are attempting to connect to any dedicated connections on SQLTEST1.
You should also make sure that you could connect to the dedicated connection on all three examples.
Which is the correct answer?

You are mastering the company database.
During the development, you find that Transact- SQL query below is running slowly:
SELECT VideoTitle, UpcNum, RetailPrice, Release Date FROM Srvideo.
VideoTitle WITH (INDEX(0)) WHERE ReleaseDate BETWEEN '20050401' AND '20050510'
A clustered index exists on the VideoTitle column.
There is a nonclustered index on the ReleaseDate column which contains the UpcNum and RetailPrice columns. The result of the avg_fragmentation_in_percent is 30 percent, when you search the sys.dm_db_index_physical_stats dynamic management function (DMF) or the VideoTitle table,
In order to find this method to solve this problem, which is the correct answer?

You are mastering the company database; there are 15 retail stores in your company.
On its own SQL Server 2008 computer, every retail store keeps transactions of point-of-sale in a database table called Saling. The Saling table also includes sales information from other stores to enable customer returns to any of the 15 retail stores.
You could refresh the data in the main office to every retail store every hour.
You should use trigger called trg_Coupon to produce sales coupons based on customer sales and buying patterns.
Through using the fewest number of steps, you should set replication between the server in every retail store and a middle server in the mainoffice. In real time.
You should not have the Replication.
Which is the correct answer?

You are mastering the company database,
You should check the log of the a SQL Server 2005 Enterprise Edition everyday,
You get the information on the torn page errors of the database.
In order to fine the solution of the torn page,
Which is the correct answer?

You are the administrator of a Microsoft Windows Server 2003 computer.
Your company purchases a new enterprise sales application that runs SQL Server 2008.
The application uses stored procedures that include the EXECUTE AS clause and that use Microsoft Windows accounts.
The company's written security policy states that all enterprise applications must run under the context of a service account that requires the minimum amount of privileges.
You need to configure the SQL Server service torun under the appropriate context for the installation of the new enterprise sales application.
Under which context should the SQL Server service run?

You work in a company which is named Wiikigo Corp.
The company uses SQL Server 2008.
You are the administrator of the company database.
Now you are in charge of a SQL Server 2008 instance.
There is an Internet Information Services application.
This application will use anonymous access to access the SSAS instance.
You must make sure that the application can access the SSAS instance.
What should you do?

You maintain a SQL Server 2008 instance.
You have an existing database maintenance plan that performs the following tasks:
- Rebuilds indexes.
- Checks database integrity.
- Writes a report to a text file.
You need to ensure that an e-mail message is sent to the operators when the maintenance plan fails to execute successfully.
What should you modify?

You administer two SQL Server 2008 instances named Instance1 and Instance2.
A database named Customers resides on Instance1.
You move the Customers database from Instance1 to Instance2.
A SQL Server login named User1 that has the password "111999" is used by a user to access the database on Instance1.
You create the same SQL Server login on Instance2.
The user attempts to access the Customers database on Instance2 by using the SQL Server login User1. However, the user receives an error message which indicates that the access to the Customers database is denied.
You need to ensure that User1 can access the Customers database.
Which Transact-SQL statements should you execute on Instance2?