Microsoft TS:MS SQL Server 2008,Implementation and Maintenance Sample Questions:

1. You administer a SQL Server 2008 instance.
You use the SQL Server Import and Export Wizard to export data from SQL Server to a
Microsoft Excel file.
You save the package definition to a file.
You need to reexecute the package from the command line.
Which utility should you use?

A) bcp.exe
B) dtexec.exe
C) dta.exe
D) sqlmaint.exe

2. You administer a SQL Server 2008 instance that runs on a Windows Server 2003 computer. The instance uses mixed authentication mode.
You need to ensure that the SQL Server 2008 authenticated logins follow the same password complexity rules that are enforced by Windows Server 2003. You also need to ensure that the password complexity rules continue to be enforced.
Which two actions should you perform? (Each correct answer presents part of the solution. Choose two.)

A) Use the ALTER LOGIN ... HASHED statement to modify all logins.
B) Create an SQL Server Agent job that runs periodically to raise an alert if a rule violation is detected.
C) Use the ALTER LOGIN ... CHECK_POLICY = ON statement to modify all logins.
D) Use Policy-Based Management to create a policy that prevents any violation of these rules.
E) Use the ALTER LOGIN ... CHECK_EXPIRATION = ON statement to modify all logins.

3. You administer a SQL Server 2008 instance.
You need to find out the number of users who access the SQL Server instance by using Windows System Monitor.
Which performance object should you capture?

A) SQLServer:Exec Statistics
B) SQLServenBuffer Manager
C) SQLServer:Access Methods
D) SQLServer:General Statistics

4. You administer a SQL Server 2008 instance. A database developer named User1 views the definitions of all database objects in a database to read data from all user-defined tables, views, and table-valued functions. You need to set the required permissions for User1. You also need to ensure that the same permissions can be granted to other developers by executing minimum number of Transact-SQL statements. Which Transact-SQL statements should you execute?

A) CREATE ROLE Developers;GRANT VIEW DEFINITION TO Developers;GRANT SELECT TO Developers;EXEC sp_addrolemember 'Developers'. 'User1';
B) GRANT VIEW ANY DEFINITION TO User1;EXEC sp_addrolemember 'db_datareader', 'User1';
C) CREATE ROLE Developers;EXEC sp_addrolemember 'sp_dbdatareader'. 'Developers';EXEC sp_addrolemember 'sp_dbddladmin\ 'Developers';EXEC sp_addrolemember 'Developers', 'User1';
D) CREATE ROLE Developers;GRANT CONTROL TO Developers;EXEC sp_addrolemember 'Developers', 'User1';

5. You administer a Microsoft SQL Server 2008 R2 instance.
Mirroring has been configured between two servers.
You need to ensure that automatic failover will occur when the principal server fails
What should you do?

A) Create a witness server to monitor the mirroring session.
On both partner servers, use the ALTER DATABASE statement along with the SET WITNESS clause.
B) Implement Policy Based Management.
Enable the Server Performance management facet.
C) Create a witness server to monitor the mirroring session.
On the principal server, use the ALTER DATABASE statement along with the SET WITNESS clause.
D) Create a Microsoft SQL Server Integration Services package on the partner server to monitor the principal server.

Solutions: