Permissions
You will need to be to identify those permissions which are reserved for the SA only,
those which are dbo only, those which are transferable to other logins, group permissions,
chains of permissions, what permissions can be assigned for a database, table, view, and
procedure. You'll also need to know how impersonation works and how that affects
permissions and objects within the database. You need know which logins can impersonate
other users.Devices
How to create a database device and resizing restrictions for a device. Also
understand the role of a default device and how to designate one.
Databases
Know how to change database ownership. Understand what each of the database options are
and what they allow you to do. Understand the create database syntax and all of the
associated options. Know that databases are created in increments of megabytes. The
default database size for new databases is a server level configuration option. Also
understand the following: a data page is 2K size, an extent is 8 data pages, an allocation
unit is 32 extents, and a database is 2 allocation units. Each table that is created is
allocated its own extent. A single extent can not contain multiple tables. This means that
if you create a database that is two megabytes in size, that database can be filled up
simply by creating 128 tables without ever adding a single row of data. Know how to
expand a database.
Backup and restore
The backup and restore questions run the gamut of all of the commands and options you will
encounter.
1. Syntax for dump and load of a database or transaction log
2. Syntax for creating and dropping a backup device
3. What types of backup devices can be created
4. What options are available for the dump transaction statement and what do each of these
mean
5. What commands or procedures should be run before and after a backup to insure the
integrity of the backup
6. What option is used with the create database statement to enable the immediate
reloading of a database from a backup
7. How database options and minimally logged commands such as truncate, select into, and
BCP effect your ability to do transaction log dumps
8. The syntax for disk reinit and disk refit and what role these two commands play in
recovering a database
Replication
The replication questions are relatively straightforward.
1. Which replication model to apply given a specific business scenario.
2. The role of the publisher and the subscriber
3. The distribution database
4. How to clean out and reset replication in the event of an error
5. The main tables involved in replication, which database they reside in, and what
information they contain
BCP
What BCP is, it's function in your environment, and the syntax for the BCP command
SQL Mail
The SQL mail questions will test your knowledge of the mail sending and processing
extended stored procedures and their syntax.
Client libraries
The questions concerning client libraries are probably the most difficult ones on the
entire exam. These will test your ability to deploy SQL Server based applications in
virtually every environment possible. Do not expect to find questions on how to set up
simple connections. You'll need to understand the following
1. Setting up a named pipes connection and testing it (makepipe and readpipe)
2. The multi-protocol library and data encryption
3. Configuring client libraries on a Macintosh client
4. Configuring client libraries within a Banyan Vines environment
General
1. Error handling
2. Creating encrypted objects
3. Isolation levels and the effect on locking
4. SQL-DMO
5. ODS
6. SQL-DMF
7. ODBC
8. DB-Lib
9. Global variables
70-027 SQL Server Implementation 1 2 3
