Set - 6

Question 1 :

There are two types of subscriptions in SQL Server replication. What are they?

Answer :

The two types of subscriptions are push and pull.

Question 2 :

What is the scope of the control permission in SQL Server 2005?

Answer :

It allows the grantee the equivalent of ownership in that they have all permissions and can grant them to others.
The Control permission is equivalent to assigning ownership of the securables. All available permissions are granted to the principal, and they the principal in turn can grant those permissions to others.

Question 3 :

When testing connectivity in SQL Server, you use which utility in conjunction with the readpipe utility to establish a connection?

Answer :

The makepipe utility is used with the readpipe utility to test connectivity only for named pipes connections. This utility has been deprecated in SQL Server 2005.

Question 4 :

In replication, what is each set of the source data that is replicated from the source server called?

Answer :

Each set of source data that is replicated is called an article.

Question 5 :

In SSIS, what is the difference between output columns and external columns?

Answer :

External columns represent the meta data of external data sources and output columns are used be data flow source adapters.
Output columns are used by all data-flow source adapters and transformations. They are not used by destination adapters. Put simply, they represent the metadata of the data flowing out of the component. External columns represent the metadata of external data sources and destinations. As such, only source & destination adapters have external columns because these are the only components whose buffered pipeline data interacts with these external sources and destinations.

Question 6 :

You want to be sure your database server if properly secured. You have two instances, a default instance and a named instance installed. Which ports do you need to open?

Answer :

1433 and a specific port after configuring the named instance to use that specific port
Named instances must be configured to use a specific port and that port then opened in the firewall.

Question 7 :

You have a relatively new SQL Server 2005 and msdb is corrupt. How can you fix this database without a restore? (data loss is acceptable) ?

Answer :

Stop the server and restart it with a trace flag to allow system databases to be detached. Then copy a new version from the /INSTALL folder.
By starting SQL Server with trace flag 3608 you can detach the msdb database. You can copy a new initial version from the installation CD.

Question 8 :

In SQL Server 2005 Integration Services, if you want to import a flat file very quickly that contains only integer data, what type of parsing should you use?

Answer :

Fast Parse
The fast parse mode that is set on columns for a flat file source connection can import a limited set of data types extremely quickly.

Question 9 :

In a new default SQL Server 2005 installation, what is the status of the dedicated administrator connection?

Answer :

Enabled for local connections only.
The dedicated adminstrator connection is enabled for local connections only by default.

Question 10 :

In the REPEATABLE READ isolation level, what phenomena is still possible?

Answer :

Phantom rows
The answer is Phantoms rows. A phantom row refers to the situation where you execute a DML statement that retrieves data, and another process may add new rows the result set. For example:
We return the following rows:

You are guaranteed to get back at least these rows, and no other user may delete these rows (the rows are locked with a shared lock) This is what is referred to as a repeatable read. However, a user might add another row:

To prevent phantoms, use the SERIALIZABLE isolation level.

Question 11 :

You are designing a new server that will hold 2 instances of SQL Server 2005 in a consolidation project. One of the existing servers has 4GB of RAM and the other has 2GB. You do not want either instance to feel memory pressure, so you specify a new server with 8GB of RAM. What else should you configure on these instances?

Answer :

Min and Max memory for each instance
If you are designing multiple instances, you will want to specify the amount of RAM that each has access to. If you do not do this, performance can vary dramatically as the servers compete for memory.

Question 12 :

You have specified that a SQL Server 2005 login must respect the password policy on a Windows XP host. Which of the following are valid passwords for the user Kendall?

Answer :

All of the above are valid
On a Windows XP host, the password policy is not enforced because the appropriate API call is not available on the operating system.

Question 13 :

A certificate is which type of security mechanism?

Answer :

Asymmetric Key
A certificate is an asymmetric key that provides encryption in addition to authentication of an entity.

Question 14 :

Which of the following services is instance-aware?

Answer :

Analysis Services
Of these, only Analysis services is instance-aware.

Question 15 :

What What does the term ACID refer to?

Answer :

An acronym that describes concurrency support in a database
The answer is an acronym that describes concurrency support in a database. ACID is an acronym for the following four terms: * Atomicity - Every operation within a transaction is treated as a singular operation; either all of its data modifications are performed, or none of them is performed.
* Consistency - Once the transaction is completed, the system must be left in a consistent state.
* Isolation - It means that the operations within a transaction must be suitably isolated from other transactions. In other words, no other transactions should see data in the intermediate state, within the transaction, until it is finalized. This is done by using locks.
* Durability - Once a transaction is competed, all changes must be persisted as requested. The modifications should persist in total even in the event of a system failure.

Question 16 :

What does this return?
SELECT USER_NAME() -- Returns Andy
EXECUTE AS TechPreparation

Answer :

The execution context switches can be nested, so changing to TechPreparation, then Steve, then issuing a Revert will return you to the context of TechPreparation. This works in the same manner as a stack.

Question 17 :

In building a PDF report in Reporting Services 2005, images that are originally stored in jpg format are rendered in jpg format. What format are images rendered in if they were stored originally in another format?

Answer :

Images stored originally in formats other than jpg are rendered in png format.

Question 18 :

What does the tablediff utility do?

Answer :

This utility will report the differences in data between two tables.
This utility will report the data differences between two tables. They must be alike in structure and give back the T-SQL to bring the data in the tables to be the same for most datatypes.

Question 19 :

Which severity levels of errors can be corrected by a user (as opposed to those that an administrator or Microsoft must work to fix.) ?

Answer :

Severity levels 11-16 are correctable by users. Below 11, these are informational warnings and not raised as system errors. Errors above 16 must have an administrator or Microsoft correct.

Question 20 :

What is the granularity in seconds of the timestamp datatype?

Answer :

The timestamp datatype does not store time values.
The answer is the timestamp datatype does not store time values. The better name for this datatype is rowversion, and it is actually a database-wide unique varbinary value. When you have a rowversion column in a table, the value of the rowversion column changes for each modification to each row. The value in the rowversion column is guaranteed to be unique across all tables in the datatype.

Question 21 :

You are setting up the backup scheme for your SQL Server 2005 server and want to setup nightly full backups and hourly log backups in the Maintenance Plans subsystem. How many plans must you setup?

Answer :

Since you have two separate schedules for the maintenance operations, you will need to have two separate plans to handle this need. Each plan can only be executed on one schedule, so one is needed for a single daily execution, the nightly full backups, and another for the hourly log backups.

Question 22 :

You have installed one new assembly on your SQL Server 2005 server and are wondering if it is being used on the production server. How can you easily monitor to see if this assembly is being used?

Answer :

You can monitor the Assembly Load event in a trace.
While you could scan trace results for the names of functions and procedures using the assembly, it is possible that you may not know all the places the assembly is being called from. There is a CLR Load event class that will capture an event when a CLR class is executed.

Question 23 :

You wish to ensure you can recover your SQL Server 2005 database to a point in time if necessary. Which recovery models can you use?

Answer :

Only the Full recovery model supports point in time restoration of your database.

Question 24 :

Does a root element in an XML document necessarily contain all the content for a well-formed document?

Answer :

By definition, the root element is required in a well-formed XML document and it contains all other content nested inside it.

Question 25 :

Which of the following datatypes can be represented in a SQL_VARIANT datatype?

Answer :

None of the above.
The SQL Variant type can store all datatypes except varchar(max), varbinary(max), xml, text, ntext, rowversion/timestamp (thought the data of a rowversion can be stored in a binary(8), which can be stored in a sql_variant) and sql_variant (it may seem strange that you can't store a variant in a variant, but all this is saying is that the sql_variant data type doesn't actually exist as such—SQL Server chooses the best type of storage to store the value you give to it).