MCTS 70 – 433 working with additional SQL server components

Working with additional SQL server components

Integrate database mail

Database mail is disabled by default but can be configured and enabled from the Database Mail object within the Management node; database mail requires service broker be running on the MSDB database.

Database Mail can have multiple SMTP servers defined for fault tolerance and uses the stored procedure: msdb.dbo.sp_send_dbmail to send the E-Mail.

Implement full-text search

A full-text index requires there be a full-text catalog defined on the database where you’re performing the search. There can only be one full-text index per table and a single-column based unique key must exist.

CREATE FULLTEXT INDEX ON Schema.Table (column|columns) 
KEY INDEX (PK_Table_Column) ON FullTextCatalogName

To view the population status of a full-text index query:



SELECT * FROM sys.dm_fts_index_population

To view the columns included in a full-text index query:

SELECT * FROM sys.fulltext_index_columns

To verify what should be returned in a full-text query use:

SELECT * FROM sys.dm_fts_parser('query_string',lcid,stoplist_id,accent_sensitivity)

The query string can use the search conditions defined below. The lcid can be found by querying:

SELECT * FROM sys.fulltext_language ORDER BY lcid

The stoplist_id can be found by querying:

SELECT * FROM sys.fulltext_indexes

The CONTAINS predicate returns exact matches to your query; the CONTAINSTABLE is similar but also returns relevance rank and full-text key.

  • Exact word or phrase match
  • a synonym match for a word
  • a conjugated match of a word e.g. write would also return writes, wrote and written
  • a series of characters at the beginning of a word
  • a word near another word

CONTAINS also has two arguments: IncludedColumns (column_name, column_list, * and language) and search condition (

  • simple term i.e. “some text”
  • prefix term i.e. “some text*”
  • generation term i.e. INFLECTIONAL OR THESAURUS e.g.
  • proximity term i.e. NEAR or tilde e.g. term1 NEAR or ~ term2
  • weighted term i.e. ISABOUT e.g.
ISABOUT(comfort weight (.8), safety weight (.2), performance weight (.9)

This would search for comfort, safety and performance with each term given a relative weight, the results would be ranked by relevance  *CONTAINSTABLE ONLY*

  • logical operators i.e. AND (&), AND NOT (&!) or OR (|)


e.g. CONTAINS(schema.table.[column, columns or *], ‘Search conditon’, Language)

The FREETEXT and FREETEXTTABLE predicate returns values that match the meaning of the search condition not simply an exact match. The FREETEXT predicate uses:

  • The word breaker to split the search string into words
  • stemmer to create inflectional words (present and past tense etc.)
  • thesaurus to location additional words synonyms.

FREETEXT has the following arguments:

  • column|columns|*
  • freetext string (if the string is enclosed in quotes then a phrase search is performed thus stemming and thesaurus lookups are not performed)
  • language LCID
SELECT * FROM schema.table WHERE FREETEXT(column|columns|*,'freetext string')
STOPLISTS contain stop or noise words such as the, a, on, in etc. You can create and modifiy custom STOPLISTS.

Implement scripts using Windows PowerShell and SMOs

To use the PowerShell SQL cmdlets type sqlps from a Windows PowerShell prompt or create a shortcut to c:\Program Files (x86)\Microsoft SQL Server\100\Tools\Binn\SQLPS.exe

The SQL PowerShell hierarchy is as follows:

SQLSERVER:[database engine|policy management|registrations|data collection utility| data-tier]\[Computer Name]\[Instance Name]\[Server level components such as databases, logins etc.]\[Database Name]\[Database level components such as tables and schemas]

To run a query using SQL PowerShell use INVOKE-SQLCMD; e.g.

INVOKE-SQLCMD -Database AdventureWorks -Query "SELECT * FROM Person.Person"

To get all stored procedures and their parameters:

Get-ChildItem | % {write-host $_.Name "has parameters of: " $_.Parameters}

Implement service broker solutions

In order to use service broker functionality for a database you must first create a database master encryption key e.g.



The other options for service broker are: NEW_BROKER – this creates a new identifier, ERROR_BROKER_CONVERSATIONS – ends all conversations with an error, HONOR_BROKER_PRIORITY – determines whether service broker gives a higher predence to messages with a higher priority.

Service broker SERVICES are a named group of tasks that require MESSAGES to be sent; each service has a QUEUE to hold incoming messages; this queue needs a CONTRACT defined in order to accept messages. A QUEUE is simply a table; each row being a message within the queue. Ideally this QUEUE would be placed on a dedicated filegroup and it is considered best practice to define the schema the QUEUE belongs to. 

If the SERVICE initiates communication it does not need a CONTRACT.

CREATE QUEUE [queueName]

A CONTRACT determines what MESSAGE TYPES a service uses to accomplish certain tasks; a CONTRACT can also define which participants can send specific MESSAGE TYPES. The default MESSAGE TYPE does not validate the contents of the message; if you require validation a custom MESSAGE TYPE is required.


VAILDATION = [validationType];

CREATE CONTRACT [contractName]
messageName2 SENT BY ANY 


ON QUEUE queueName;

ROUTES determine where MESSAGES will be delivered to; by default every DATABASE contains a default route which is where all messages with no ROUTE definition will end up. 

Service broker endpoints enable SQL server to send and receive messages via TCP/IP; the endpoint can also control what is sent and received and also provides transport security.

Track data changes

Change tracking allows you to track inserts, updates, deletes made against particular tables within a database; these changes can be seen by using the CHANGETABLE function.

The default retention period for maintaining changes is 2 days.

If change tracking is enabled then you cannot drop a primary key constraint or join the table to a partitioned table without disabling change tracking first.

Example of change tracking using the AdventureWorks database here

Database-level audit specification allows you to define which objects should be audited for particular statement use by particular roles.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s