UPGRADE:MCDBA Skills to MCITP DB Admin by Using MS SQL 2005

1.BACKGROUND Company Overview Southridge Video is a video rental company. The company has a main office and 250 video rental locations in North America. It also rents videos over the Internet, with delivery by mail. Planned Changes The company plans to develop a new mission-critical accounts database at the main office to maintain account information for the Internet rentals business. This database must automatically fail over when the primary server fails. In addition, the company wants to configure an additional server so that incorrect updates and deletions can be corrected using the information on the additional server. No single server failure should prevent the ability to correct data errors. Four new server computers will be available for this mission-critical accounts database. A new replication system will be implemented so that store employees can view the list of videos that are available at other company stores within 20 miles. Store employees should be able to update only their own store??s records. Problem Statements The database named rental_history is extremely large and is becoming slower as it increases in size. Investigation revealed that the appropriate indexes already exist in this database. The most common report includes the customer name and city from the customer table; the store name, city, and store number from the store table; the rental date from the store table; the rental date from the rental table; and the video name and genre from the videotitle table. Query statistics indicate that the joins that are required to run the other common reports, which frequently include fields from all tables, are negatively affecting the performance on this database. The rental_history database does not allow any changes to the data. There is amply time to import the data once per month. EXISTING DATA ENVIRONMENT Database Servers Southridge Video recently upgraded all of their SQL Server 2000 computers to SQL Server 2005 running on Microsoft Windows Server 20003. The SQL Server computers are described in the following table.

Correct:
2.You need to make recommendations for tracking usage on the acct1 database to fulfill the companys security requirements. What should you recommend?
A.Set up the Security Audit event category in SQL Trace to track the Audit Schema Object Access event class.
B.Create DDL triggers on all sensitive tables.
C.Set up the Security Audit event category in SQL Trace to track the Audit Database Object Management event class.
D.Create DML triggers on all sensitive tables.
Correct:A
3.You need to implement the new read-only copy of the internet_rentals database on SQL2 according to the companys performance and availability requirements. What should you do?
A.Install a new SQL Server 2005 computer. Create a new database on that server, and configure snapshot replication between internet_rentals and the new database.
B.Install a new SQL Server 2005 computer. Create a new database on that server, and configure merge replication between internet_rentals and the new database.
C.Create a new database on SQL2. Configure transactional replication between internet_rentals and the new database.
D.Install a new SQL Server 2005 computer. Create a new database on that server, and configure transactional replication between internet_rentals and the new database.
E.Install a new instance of SQL Server 2005 on SQL2. Create a new database, and configure transactional replication between internet_rentals and the new database.
Correct:D
4.A rental agent named Eric is a member of the Windows rental_agents group. He was inadvertently given more permissions than he should have as a member of the rental_agents group. You confirm that the other rental agents do not have excessive permissions. You use the EXECUTE AS statement to impersonate Eric, and you run six different SQL commands in the internet_rentals database. You need to identify which of the commands have results that show excessive permissions for Eric. Which two commands should you identify? (Each correct answer presents part of the solution. Choose two.)
A.SELECT name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name, 'OBJECT', 'SELECT') = 1;returns 10 rows
B.SELECT HAS_DBACCESS('internet_rentals');returns a value of 1
C.SELECT HAS_PERMS_BY_NAME('sa', 'LOGIN', 'IMPERSONATE'); returns a value of 0
D.SELECT IS_SRVROLEMEMBER ('serveradmin');returns a value of 1
E.SELECT name FROM sys.tables WHERE HAS_PERMS_BY_NAME(name, 'OBJECT', 'DELETE') = 1; returns 10 rows
Correct:D E
5.You need to improve the performance for the following query in the internet_rentals database. SELECT videotitle, upc_no, retailprice FROM srvideo.videotitle WHERE releasedate BETWEEN '05-01-2005' AND '05-30-2005' The scripts that were originally used to create the existing table and indexes that are used in the query are shown in the following code segment. Create table srvideo.videotitle (videoid int IDENTITY(1,1) primary key nonclustered videotitle nvarchar(100) not null , description nvarchar(255) null , videolanguage nvarchar(50) null , releasedate datetime null , isbn nvarchar(25) , upc_no nvarchar(25) , format nvarchar(25) , cost money , retailprice money) go create clustered index cl_videotitle on srvideo.videotitle (videotitle); You must not diminish the performance on other SELECT queries that are regularly performed. What should you do?
A.Create a nonclustered index on the retailprice column. Add the releasedate and videotitle columns as included columns.
B.Add a clustered index on the releasedate column.
C.Create a nonclustered index on the releasedate column. Add the videotitle, upc_no, and retailprice columns as included columns.
D.Create a nonclustered index on the releasedate column.
Correct:C
6.Drag Drop question

Correct:
Green choice6---->Yellow Choice1
Green choice4---->Yellow Choice4
Green choice3---->Yellow Choice2
Green choice1---->Yellow Choice3
7.You need to implement encryption for SQL1 based on the companys technical requirements. What should you do?
A.Implement the Server (Request Security) IPSec policy on SQL1.
B.Enable encrypted connections for the SQL Server instance with the ForceEncryption option enabled.
C.Create the acct1 database, and encrypt all data.
D.Enforce the use of Kerberos authentication on SQL1.
Correct:B
8.You need to review the current storage system and decide on the best configuration for the tempdb database on SQL1 to improve tempdb performance. Your solution must ensure that company requirements and policies are fulfilled. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Create a RAID-1 set, and move tempdb to this drive set.
B.Create a RAID-0 set, and move tempdb to this drive set.
C.Set the starting size of tempdb to 3 GB.
D.Create a RAID-0 set, and move tempdb and the user database with the largest index to this drive set.
E.Disable automatic file growth on tempdb.
Correct:A C
9.BACKGROUND Company Overview Proseware, Inc., is a technical content development company that creates documentation and training materials for various software vendors. The company currently has 26 full-time employees at its central office in Seattle, and uses more than 100 external contract writers who work from home in various locations around the world. Planned Changes The company plans to implement a new, Web service-based solution that will facilitate the process by which remote contract writers submit work. Currently, all work is submitted by e-mail as Microsoft Word document attachments, and it is stored in file shares on a file server named FP2. The new solution will allow work to be submitted to a Web service in WordML format and then stored in an xml column in a new database named proseware_projects. The Web service will be hosted on an existing Web server in the perimeter network, and it will impersonation to access the database server. A design for the new application solution is shown in the following diagram. (The diagram can also be viewed by clicking the Case Study Exhibits button.)

Correct:
10.You need to design a high-availability strategy for the proseware_projects database. Your design must fulfill all business and technical requirements. Which strategy should you use?
A.log shipping
B.failover clustering
C.database mirroring
D.replication
Correct:C
11.You are designing security for the HTTP endpoint for the proseware_projects application. You need to identify which login or logins should be granted the CONNECT permission on the HTTP endpoint. Which login or logins should you use? (Choose all that apply.)
A.BUILTIN\UsersA local Windows group containing the PROSEWARE\DomainUsers domain global group
B.ContractWritersA local Windows group containing the PROSEWARE\Contractors domain global group
C.PMsA local Windows group containing the PROSEWARE\ProjectManagers domain global group
D.EdsA local Windows group containing the PROSEWARE\Editors domain global group
Correct:B
12.You are creating a management plan for the proseware_biz database. You need to specify the action that must be taken before manually inserting data through a bulk load process. Your plan must fulfill the availability and recoverability business requirements, and it also must require minimal administrative effort. Which action should you specify?
A.Create a full backup.
B.Create a differential backup.
C.Configure replication.
D.Create a database snapshot.
Correct:D
13.You need to specify the changes to the existing firewall configuration settings that are required by the HTTP endpoint that will be used by the proseware_projects database. What should you do?
A.Create a Web publishing rule on ISA2 that allows HTTP traffic on port 5017 from IIS1 to the SQL Server computer.
B.Create a Web publishing rule on ISA1 that allows HTTP traffic on port 5017 from IIS1 to the SQL Server computer.
C.Create a Web publishing rule on ISA2 that allows HTTPS traffic on port 5017 from IIS1 to the SQL Server computer.
D.Create a Web publishing rule on ISA1 that allows HTTPS traffic on port 5017 from IIS1 to the SQL Server computer.
Correct:D
14.BACKGROUND Company Overview Trey Research is a nonprofit organization that provides research support to many organizations in the United States. The company has a single office, which is located in Chicago. Planned changes The company plans to implement a new SQL Server 2005 failover cluster. This cluster will have one instance for each of three databases that are currently on a mainframe computer. The three databases are named Research, Parts, and Engineering. These databases will hold mission-critical data. The configuration will use a four-node failover cluster to provide an N+1 configuration. This cluster will be named SQLCLUST. When these databases are moved to SQLCLUST, a new identity column will be added to most tables in all of the databases to create a surrogate primary key. When the databases move to production, the seed value should be reset for all identity columns. New Web services-based applications will be designed to access the Parts, Engineering, Customer, and Bids databases. Users will access these applications by using their Windows credentials. The company plans to reduce database administration cost by consolidating existing SQL Server computers. The company wants to minimize the number of SQL Server computers and SQL Server instances. The company plans to create a Bid_Archive database. All rows that contain information on completed bids that do not result in a new or extended contract should be moved immediately from the Bids database to Bis_Archive. This information should be available for retrieval, but changes should not be made to the Bid_Archive database. EXISTING DATA ENVIRONMENT Databases They Research currently has eight databases, as shown in the following table.

Correct:
15.You need to review the list of expected services for SQLCLUST and disable any nonessential services. Which two services should be disabled? (Each correct answer presents part of the solution. Choose two.)
A.Cluster service
B.World Wide Web Publishing service
C.SQL Server Agent service
D.Messenger service
E.Server service
Correct:B D
16.You configure the new server named SQLCLUST with test versions of the Research, Engineering, and Parts databases. The data in these databases is for test purposes only and is not current. You need to move the new Research, Engineering, and Parts databases from the test stage to the production stage as efficiently as possible. Prior to making the databases available to users, which three actions should you perform? (Each correct answer presents part of the solution. Choose three.)
A.Use the DELETE statement to remove all test data from the three databases.
B.Run scripts on the production server to build all tables, views, and stored procedures.
C.Schedule one or more jobs to move all data from the mainframe computer databases to the new databases.
D.Build all indexes, and update the statistics on all tables.
E.Manually move all data from the mainframe computer to the new databases by using the SELECT INTO command.
Correct:C D E
17.You need to configure the security for the HR database to support current security needs, while minimizing the impact of future security changes that might be caused by user and table additions. Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)
A.Grant the SELECT permission on all current tables to the public database role.
B.Create an HR_Readers role, add all employees in the HR department to this role, and grant the SELECT permission on all current tables to this role.
C.Add all employees to the db_datareader database role.
D.Add all hiring managers to the db_datawriter database role.
E.Create a Hiring_Managers role, add all hiring managers to this role, and grant the role the SELECT, INSERT, UPDATE, and DELETE permissions on the five tables the hiring managers need to manage.
Correct:B E
18.Drag Drop question

Correct:
Green choice2---->Yellow Choice3
Green choice3---->Yellow Choice5
Green choice1---->Yellow Choice4
19.You need to design a solution to allow the accounting managers to review accounting changes in compliance with the companys business requirements. What should you do?
A.Set up snapshot replication to a new database named Accounting_Changes. Schedule the snapshot to be sent at close of business every Friday.
B.Set up transactional replication to a new database named Accounting_Changes. Create a job to remove all rows that have an inserted date that is more than eight days old.
C.Create a job to create a database snapshot at close of business every Friday. Create another job to remove snapshots that are more than two weeks old.
D.Create a job to create a database snapshot at close of business daily. Create another job to remove snapshots that are more than eight days old.
Correct:D
20.BACKGROUND Company Overview A.Datum Corporation is an independent software vendor that has a worldwide customer base. The company sells its software through a combination of an e-commerce Web site and a telephone-based ordering system.. Planned Changes The company plans to upgrade all of its database servers to SQL Server 2005. The BusinessData database will be redesigned to improve manageability, and a new database named MarketingAnalysis will be created to allow marketing analysts to generate reports from sales data. The most common report that will be generated will retrieve the names of products that are sold and the cities where the customers live. Problem Statements The company wants to improve the security of its data and wants to minimize the risk of unauthorized data access or malicious attack. On several occasions, customers were unable to palace an order because the database server was unavailable for an extended period of time.. EXISTING DATA ENVIRONMENT Databases The HRData database contains employee data, including sensitive information such as salary and employee reviews. The BusinessData database contains product and sales data. Analysis indicates that each user trpically accesses only a subset of the tables in the database. This subset is related to the department in which the user works. Business analysis indicates that approximately 4,000 new records are added to the BusinessData database each day, and that 90 percent of records are not modified after they are entered. No managed objects currently exist in any database. Database Servers The company currently has a single default instance of SQL Server 2000 in which the HRData database and the BusinessData database are stored. The SQL Server instance is installed on a server named SQL1. Written company policy states that only default instances of SQL Server should be used and that no named instances should be installed. The company plans to deploy a hardware RAID solution in the next six months. However, no disk redundancy solution is currently implemented. Database Client Computers Data in the HRData database is accessed through an ASP. NET application. The application runs in the security context of a Windows user named HRApp and uses Windows Authentication to connect to SQL Server. The BusinessData database is accessed by users in the sales department, who use Windows Authentication to connect to SQL Server. In addition, users in the software development team need to access the BusinessData database form client computers that run either a Microsoft Windows operating system or another operating system. Users on client computers that do not run Windows use SQL Server Authentication to connect to SQL Server. All client applications are configured to connect to the SQL Server instance. Therefore, users do not need to browse for a server. EXISTING INFRASTRUCTURE Network Infrastructure All computers are connected to a TCP/IP-based network. The network is configured as shown in the following diagram. (The diagram can also be viewed by clicking the Case Study Exhibit button.)

Correct:
21.You are planning the backup strategy for the BusinessData database. You need to include a strategy for backup redundancy. Which backup redundancy strategy should you include?
A.Back up the database to a disk backup device, and then back up the database to a tape device.
B.Use different backup devices for full backups and transaction log backups.
C.Back up only the filegroups that are not stored in a RAID disk array.
D.Use a mirrored media set to back up the database to multiple backup devices of the same type.
Correct:D
22.You are designing the interim disk availability solution for SQL1. You plan to use Windows RAID functionality. You need to identify the most appropriate RAID configuration to fulfill the availability requirements. Which RAID configuration should you use?
A.Create a mirrored volume for the system files and database logs, and a stripe set with parity for the data files.
B.Create a mirrored volume for the system files and database logs, and a stripe set without parity for the data files.
C.Create a mirrored volume for the system files, database logs, and data files.
D.Create a stripe set with parity for the system files, database logs, and data files.
Correct:A
23.For the MarketingAnalysis database, you are adding functionality that sends the results of a query in an e-mail message to the marketing supervisors when new sales data has been entered. You need to implement a method that fulfills this requirement and minimizes the security risks. Which method should you use?
A.Database Mail
B.SQL Mail
C.a managed stored procedure
D.a command line program that is called by xp_cmdshell
Correct:A
24.Drag Drop question

Correct:
Green choice5---->Yellow Choice1
Green choice4---->Yellow Choice2
Green choice2---->Yellow Choice3
Green choice3---->Yellow Choice4
25.You are designing a data archival strategy for order data. The placements of data and partitions for the Sales.Orders table at the end of July are shown in the following display. On which filegroup should you locate partition 2 of the Sales.OrderArchive table?

A.FG1
B.FG2
C.FG3
D.FG4
Correct:B
26.Coho Vineyard & Winery BACKGROUND Company Overview Coho Vineyard & Winery began as a small business that supplied wine to local retailers and restaurants. The company has grown substantially, and it now sells wine to customers all over the world. The company is based in California. The main office is located in San Francisco, and the vineyard is 60 miles to the north. Users at the vineyard access the Sales database by using an ASP.NET Web application. Customers order cases of wine by mailing or faxing a sales order form, and the details are entered manually into the database. Planned Changes The company recently decided to create a customer relationship management (CRM) application that will use a SQL Server 2005 database. Customer data that is currently held in the Sales database will be moved to the new CRM database, and a stored procedure will be added to the Sales database to retrieve customer data from the CRM database by using the security credentials of the original caller. The CRM database will be central to all company operations. The most frequently accessed table will be the CustomerData.Customers table. The definition of this table is shown in the following code statement. CREATE TABLE CustomerData.Customers ( Customer ID int PRIMARY KEY CLUSTERED, FirstName nvarchar (25) NOT NULL, LastName nvarchar (25) NULL, MideleName nvarchar (25) NULL ) During the company??s growth, small departmental database have been created to store specific kinds of data. The company plans to migrate these databases to SQL Server 2005 and centralize them into a single data center that will include the Sales and CRM databases. The company recently created a central IT department. The IT department has deployed a Windows Server Update Services (WSUS) server named WSUS1 to keep all computers in the main office up-to-date with the latest service packs and patches. All computers in the main office will be configured to download updates from this server. Problem Statements The company uses log shipping to provide a redundant copy of the Sales database on a second server. This secondary server must be configure to allow the swapping of log shipping roles. The two servers have not previously swapped log shipping server roles. EXISTING DATA ENVIRONMENT Databases The Sales database is configured for log shipping. The risk of minor data loss in the event of server failure is tolerable because sales orders can be manually re-entered from the paper forms. There are three small departmental databases. Each of these databases has only a small number of users. These databases are described in the following table.

Correct:
27.You plan to create a unique nonclustered index on the CustomerID, LastName, and FirstName columns of the CustomerData.Customers table in the CRM database. To aid in estimating the space requirements for the index, you identify the following variables: Estimated number of rowsClustered index key Number of columns in the indexFixed data sizeNumber of variable-length columnsAverage variable-length data size You need to identify the most significant additional variable that will increase the estimated index size. Which additional variable should you identify?
A.current number of rows
B.null bitmap
C.fill factor
D.automatically generated uniqueifier value
Correct:C
28.You need to identify the most appropriate archiving strategy for the SalesData.Orders table in the Sales database. The archiving strategy must have the minimum impact on performance. What should you do?
A.Create a table named ArchivedData.Orders. Partition the SalesData.Orders table and its indexes, and use MERGE, SPLIT, and SWITCH functions every three months to move old order data into ArchivedData.Orders.
B.Create a table named ArchivedData.Orders. Every three months use an INSERT statement to copy data from the SalesData.Orders table into ArchivedData.Orders, and then use a DELETE statement to remove archived data from SalesData.Orders.
C.Create a database snapshot of the Sales database every three months, and then use a DELETE statement to remove archived data from the SalesData.Orders table.
D.Back up the Sales database to tape every three months, and then use a DELETE statement to remove archived data from the SalesData.Orders table.
Correct:A
29.Drag Drop question

Correct:
Green choice1---->Yellow Choice2
Green choice1---->Yellow Choice3
Green choice2---->Yellow Choice1
Green choice2---->Yellow Choice4
30.You are reviewing the code that is used to retrieve order data in the ASP.NET Web application. You need to update the application to meet the security requirements. What should you do?
A.Create a view to retrieve the order data, and replace the table name with the view name in the ASP.NET code.
B.Create an HTTP endpoint that has BATCHES enabled, and modify the ASP.NET code to connect to the HTTP endpoint.
C.Create a SQL Server login that has a complex password, and modify the ASP.NET code to connect by using the SQL Server login.
D.Create a stored procedure to retrieve the order data, and modify the ASP.NET code to call the stored procedure by using parameters.
Correct:D