70-451 – Designing a Database Solution – SQL Server Components


Identify which SQL Server Components to use to support business requirements.  This objective may include but is not limited to: SQL Server Agent, DB Mail, Service Broker, Full-Text Search, Distributed Transaction Coordinator (DTC), Linked Servers.

My Take

I believe that this objective is based upon realizing when to use the various sql server components.  With at least two of these areas being later objectives for the designing a database solution requirements, I think it is pretty safe to just look into what each of these are and where I think I will use them.  Even with this being the case, I still ought to attempt to look at syntax and use conditions for each as a reminder. 

SQL Server Agent

SQL Server agent is a service that executes processes which can be run on a schedule, in response to a specific event or on demand.  These processes are stored as jobs, with one or more job steps.  Each step can perform a wide range of functions, from running T-SQL scripts to SSIS packages to Operating System commands.

As a side note, I use SQL Server Agent all the time to set up the nightly, weekly, monthly and yearly jobs for our environment.  I feel that I know this portion of the tool fairly well and that I can answer most questions of the roles relating to the services and the permissions associated with it (including proxies and credentials, etc).  However, I am fairly certain that I missed at least one question relating to the SQL Agent on the Maintenance and Implementation exam, so it would be good to look over the miscellaneous details at least one more time.

DB Mail

Database Mail allows for sending email messages from within SQL Server.  With this it is possible to email the results of queries on a scheduled basis, send emails on alerts from within the database or email files as attachments from various network resources.

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users. The messages can contain query results, and can also include files from any resource on your network. Database Mail is designed for reliability, scalability, security, and supportability.

Service Broker

Service Broker is a message and queuing system that can be used for asynchronous programming to communicate between the same database, the same instance or other servers entirely in a secure and reliable fashion.

I have a problem with service broker in that I know what the basic concepts are, much like I have a decent idea of what a distributed program is, but I have not really gone beyond the basic tutorials to grasp how it is that I would do well to use this service (See the Typical Uses page). Intrinsically, I feel like it would be really good for me to get a good grasp of this tool. I have seen a few sessions either on it or using it over the past year or two at the various SQL Saturdays.

I will be taking a bit of a deeper look at this topic later this week when I get to the Service Broker requirement of the designing a database solution. Hopefully this will help it stick a little more.

Full-Text Search

Full Text Search is a way of querying text data in either text based columns, or binary data stored that is stored a well known format, which is based on synonyms and nuances of the language such as various inflections and plurality.

I will be taking a bit of a deeper look at this topic later this week when I get to the Full Text Search requirement of the designing a database solution.

Distributed Transaction Coordinator (DTC)

Distributed Transaction Coordinator (DTC) is a windows service included with the component services administrative tool which is used to ensure successful and complete transactions through distributed transactions across heterogeneous resources (i.e. separate systems or applications). The DTC provides the coordinator role to ensure that all resources have completed the requisite steps prior to committing across all of the resources, helping to achieve ACID properties on all transactions.

As this is not a direct portion of SQL Server I have probably neglected learning about it in the past. I know that I should be looking into enabling this for using the shared connection feature of SSIS so that I can string together the various statements within a single unit of work, rather than opening myself up to potentially completing only a portion of a task.

Linked Servers

Linked Servers allow resources on different instances or servers to be used as though they reside on the same server through the use of OLE DB providers via distributed queries.

This entry was posted in SQL and tagged , . Bookmark the permalink.

1 Response to 70-451 – Designing a Database Solution – SQL Server Components

  1. Pingback: MCITP 70-451 Links Page | Destination: Change

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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