Self Test :-
1. Which SQL Server 2008 edition supports IA64?
A. Enterprise and Developer editions
2. How many instances can be created in SQL Server 2008 Standard Edition?
3. How many SQL Server default instances can be installed on a single?
No clustered server?
4. Which of the following editions has all the features and functionality but is not
licensed for production?
A. Developer Edition
5. Which SQL Server 2008 edition is best suited for mobile applications?
A. Compact Edition.
6. Which SQL Server 2008 edition provides the most Analysis Services?
A. Enterprise Edition
7. Which tool should be on an existing SQL Server 2000 or 2005 database
Instance prior to upgrading to SQL Server 2008?
A. Upgrade Advisor Tool
8. Which of the following is a command line utility used to run PowerShell
command and scripts?
9. Which of the following editions does not support any Reporting Services
A. Express Edition
10. Which of the following accounts has the least privilege when used as a service
A. Local user account
11. Which of the following accounts should be used to perform a SQL Server
2008 installation or upgrade?
A. Administrator account
12. In which of the following directories are the default instance database data
and log files located?
A. <drive>:\Program Files\Microsoft SQL Server\MSSQL10.
13. What additional software is required for SQL Server’s Business Intelligence
A. Microsoft Internet Explorer 6 SP1 or later
14. Which of the following is the SQL Server component that provides the services
for creating, processing, and managing OLAP and data mining?
A. Analysis Services
15. Which of the following is a scalability feature in SQL Server Enterprise
16. In which directory are the installation log files found?
A. <drive>:\Program Files\Microsoft SQL Server\\100\Setup Bootstrap\LOG\Files\
17. Which system database is hidden to SQL Server Management Studio and is
used to persist a read-only version of system objects?
18. Which is the compatibility version associated with SQL Server 2008?
19. Which SQL Server tool is used to check that SQL Server component services
A. SQL Server Configuration Manager
20. Which type of file contains data and database objects such as tables and
A. Data files
1. You are setting up security for your new SQL Server 2008 installation.
Management is concerned about security. What approach should you take to
ensure security settings are optimal?
A. Use SQL Server Configuration Manager to secure the installation
2. You have been tasked with setting up standards for your SQL Server 2008
installation. You need to enforce a table naming convention. What is the best
way to accomplish this in your SQL Server 2008 environment?
A. Create a Declarative Management Framework policy
3. You have been asked to create a backup of your production database and
restore it on a development server. Your production server is using the full
recovery model. Full backups are taken Monday and Wednesday. Transaction
log backups are taken every hour. Today is Friday. The backup needs to be
created as quickly as possible. What’s the fastest way to get the latest database
copy while minimizing impact to production?
A. Create a Copy Only Backup. Use that to restore to development.
4. You have a SQL Server 7.0 database and would like to move it to a new
SQL Server 2008 instance. The database is part of an FDA-validated system
and cannot be changed at all? How can this be accomplished?
A. You must upgrade the database to SQL 2005 or greater.
5. You have an application that is being upgraded from SQL Server 2005 to
SQL Server 2008. You notice that some stored procedures are not working
correctly. An excerpt is as follows:
FROM Territories, Region
WHERE territories.regionid *= region.regionid
What should you do to resolve the issue?
A. The join syntax is incorrect. Replace with left join.
6. Your disk is almost full on the transaction log drive for a database server.
How can you resolve this issue?
A. Change the mode to simple and shrink the log
7. You want to enforce a standard naming convention for stored procedures.
What’s the best way to do this in SQL Server 2008?
A. Use the SQL Server 2008 Declarative Management Framework
8. You want to enforce a standard naming convention for tables and stored
procedures. Your company has two SQL 2008 Servers and 60 SQL 2005
Servers. You need to use the same solution on all servers. What’s the best way
to do this in SQL Server 2005 and SQL Server 2008?
A. Create a DDL trigger for all servers
9. You have a database table with a varchar(600) field in it. Most of the records
in the table have a null value for this field. How can you save space?
A. Move the data into a second table
10. You have a database table with a FileStream field in it. Most of the records
in the table have a null value for this field. What’s the best way to save
A. Move the data into a second table
B. Use sparse columns
C. Use the SQL Server 2008 Declarative Management Framework
D. None of the above
11. You need to store images in for a Web site using SQL Server 2008. How can
you accomplish this?
A. Use a FileStream data type, and the images will be stored on disk
12. You are responsible for a system that is used for both online transaction
processing (OLTP) and reporting. When reports run on the server, the OLTP
process slows way down. How can you allow reports to be run on the server
and minimize impact to the OLTP processes?
A. Use the Resource Governor
13. You are creating an application to track crime in different locations
throughout a large city. What data type could prove useful for storing location
data (longitude and latitude)?
14. You are running out of space on the drive used to store backups. All of the
servers use the same network location. What can you do to save space with
your backups while maintaining the same number of backups?
A. Use compressed backups
15. You need to store sensitive data in your SQL Server database. The application
has already been written and works fine. What’s the easiest way to do this
without having to change your application?
A. Use transparent data encryption
16. Within your application, you need to log all changes to one table. DDL
and DML changes must be logged. What’s the best approach to solve this
A. Use the built-in auditing capability
17. You have a server that supports Hot Add CPU. The current CPU utilization
is 95 to 100 percent most of the time. The server is mission-critical and cannot
be shut down. SQL Server 2008 Standard Edition is installed. What should
A. Schedule an outage and add another CPU to the server
18. You are contemplating using data compression on a table. You would like to
know how much space this will save. How can you determine the savings?
A. Use sp_estimate_data_compression_savings
19. You have a server that supports Hot Add Memory. Performance is sluggish,
and you believe adding more memory will help. The server is mission-critical
and cannot be shut down. SQL Server 2008 Standard Edition is installed.
What should you do?
A. Schedule an outage and add memory to the server.
20. You have a SQL Server 2008 installation, and you want to create a high availability
Solution. What are the ideal approach(es) to solve this problem?
Frequently Asked Questions
Q: Do I have to install a default instance?
A: No. You can install named instances on a server without having a default instance.
Q: If a named instance uses dynamic port allocation how will I know which port
to connect to each time?
A: The SQL Browser service needs to be running on the SQL Server. It will
return the correct port to your connection without you having to
Q: Why shouldn’t I change a SQL Server service account using the services applet?
A: Changing the service account using SQL Server Configuration Manager will
assign all the file system permissions and advanced user rights needed for
SQL Server to work properly. Using the services applet won’t do this and could
cause problems when the service is next started.
Q: Should I change SQL Server configuration settings using the Management
Studio interface or with sp_configure?
A: The end result is the same so it doesn’t really matter. However, sp_confgure has
many more options than the interface and is easy to script so it tends to be the
popular choice with DBAs.
Q: I’ve enabled AWE on my 32-bit SQL Server but still can’t see more than 2 GB
of RAM. What should I do?
A: First, check that you’re running the Enterprise or Datacenter version of Windows.
Then check in c:\boot.ini for the /PAE switch which enables Windows to
see the extra RAM. If that all looks fine then check that the service account has
the ‘lock pages in memory’ user right. If it doesn’t there will be an error logged to
the SQL Server errorlog on startup.
Q: What is considered to be a Principal in SQL Server 2008?
A: Any entity that requests SQL Server Resources.
Q: What is the definition of “The Principle of Least Privilege”?
A: Only give permissions that are needed. Giving more access violates this
Q: Can you modify Server-level Roles?
A: No, Server-level Roles are fixed and cannot be modified.
Q: Can you create your own Database-level Roles?
A: Yes, Database-level Roles can be created to better fit a user’s security
Q: What is the most secure Authentication Mode and why?
A: The Windows Authentication Mode is more secure because it is managed by
the Windows operating system and subject to additional Windows password
Q: What SQL Server tool is used to enable the DatabaseMail feature in
SQL Server 2008?
A: The Surface Configuration Tool.
Q: When working with SQL Server services such as SQL Server Agent, which tool
do you use?
A: SQL Server Configuration Manager.
Q: When setting up SQL Server services that need to perform tasks outside of the
local server, which account type should you use?
A: Domain Service Accounts.
There are two types of database-level roles, fixed database roles that are predefined
in the database and flexible database roles that you can create.
The fixed database-level roles are:
db_owner – Can drop the database as well as permission to perform m all
configuration and maintenance tasks.
db_security_admin – Can modify role membership and manage permissions.
Please be careful when adding principals to this role; an unintended privilege
escalation could result.
db_accessadmin – Can add or remove database access for Windows logins,
Windows groups, and SQL Server logins.
db_backupoperator – Can back up the database.
db_ddladmin – Can run any Data Definition Language command.
db_datawriter – Can add, delete, or change data in all user tables.
db_datareader – Can read all data from all user tables.
db_denydatawriter – Will deny permission in the database to add, modify,
or delete any data in the user tables.
db_denydatareader – Will deny permission in the database to read any data
in the user tables.
These objects can be helpful when working with Database-level roles:
sp_helpdbfixedrole – Returns a list of the fixed database roles.
sp_dbfixedrolepermission – Displays the permissions of a fixed database role.
sp_helprole – Returns information about the roles in the current database.
sp_helprolemember – Returns information about the members of a role
in the current database.
sys.database_role_members – Returns one row for each member of each
IS_MEMBER – Indicates whether the current user is a member of the
specified Microsoft Windows group or Microsoft SQL Server database role.
CREATE_ROLE – Creates a new database role in the current database.
ALTER_ROLE – Changes the name of a database role.
DROP_ROLE – Removes a role from the database.
sp_addrole – Creates a new database role in the current Ent database.
sp_droprole – Removes a database role from the current database.
sp_addrolemember – Adds a database user, database role, Windows login,
or Windows group to a database role in the current database.
sp_droprolemember – Removes a security account from a SQL Server role
in the current database.