Microsoft 70-447 Real Exam Questions
UPGRADE: MCDBA Skills to MCITP Database Administrator by Using Microsoft SQL Server 2005
1: 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 Answers: A
2: 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 Answers: D
3: 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 Answers: D E
4: 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 Answers: C
5: 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 Answers: B
6: 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 Answers: A C
Download | Password: certificatexam.com