In SQL Server 2005, you want to monitor your
transactional replication and determine how long it
takes for a transaction to move from the publisher to a
subscriber. What would you use to help you do this?
You can use tracer tokens in SQL Server 2005 replication
to measure latencies. These are small amounts of data
written into your transaction logs.
How many credentials can one login be mapped to?
A credential can be mapped to many logins, but a login
can only be mapped to one credential.
You are looking to secure your publisher to prevent
unauthorized logins from accessing the data through
replication. Where do you add the list of logins that
Add them to the Publication Access List in the
All access to publications is controlled with the
Publication Access List (PAL), which is similar to the
Windows ACLs. A list is created for each publication
that is set up.
What does the ERROR_PROCEDURE() function return?
The name of the stored procedure that caused an error.
The ERROR_PROCEDURE() function is placed in the catch
block of a TRY..CATCH construct and returns the name of
the procedure that caused an error to be thrown.
Credentials in SQL Server 2005 are used to access what?
Resources outside the SQL Server
A credential is used to access resources outside of SQL
Server. It contains the authentication information
needed for accessing these resources.
Which of the following is an invalid file name for a
None of the above
Explanation from BOL: SQL Server 2005 does not enforce
the .mdf, .ndf, and .ldf file name extensions, but these
extensions help you identify the different kinds of
files and their use.
What does @@options return?
The current SET options for the connection.
The @@options variable returns a list of the options set
for the current connection. This is returned as integer
and each bit in the integer represents an option.
You have setup forwarding for your SQL Server 2005
Service Broker queues to another server. You edit a
route to move a queue to a new server. What do you need
to do now to ensure you can recover the routes in the
event of a disaster?
Back up the msdb database.
The routes for forwarding are stored in msdb.sys.routes.
If you back up the msdb database, the routes will be
You are looking to setup a large Notification Services
application and you are planning on a 40GB database.
When installing Notification Services, the creation
fails. What is the likely problem?
Notification Services installation has a 10 minute
timeout and the database creation probably took longer
The Notification Services timeout for database creation
is 10 minutes. If the database takes longer to create,
the installation of the instance fails. You need to
reduce the size of the database for installation and
then increase it when you are done.
You have enabled AWE for your SQL Server 2005 server and
allocated 4GB or RAM on one of your servers. An ASP.NET
application on the same server is feeling memory
pressure and you want to release some memory and reduce
SQL Server to 3GB or RAM. How can you do this?
Change the max amount of memory allocated and shut down
and restart SQL Server to have it take affect.
Once memory is allocated through AWE, it cannot be
released unless the SQL Server is restarted.
You are troubleshooting a Service Broker application and
find that messages are remaining on the queue. You
determine that there is no active queue monitor. What
should you do?
Use the ALTER QUEUE statement to turn activation on.
One of the troubleshooting steps if messages remain in
the queue is to check for an active queue monitor. If
one is not on, then it needs to be activated. The ALTER
QUEUE statement is used to change this.
You are implementing replication across the Internet for
a large bank that wishes to move up to date pricing
information to an analyst's laptop. They are concerned
about spoofing of their main site by criminals. What can
you do to secure the replication solution with SQL
Set the encryption level to verify the certificate is
issued by a trusted authority.
Since you are asked to use replication, a replication
solution is to use encryption for the connection and
verify the certificate being used is from a trusted
authority. Setting the encrpytion level to 2 will do
In SQL Server 2005, you are looking to implement
full-text search. One of the tables you are looking to
index stores Mircrosoft Word documents in a
varbinary(max) column. Can you use Full-text search to
index this column?
You can use full-text search for formatted data such as
Word that contains text stored in a varbinary column.
Page Numbers : 1