Looking through the skills measured by the 70-451, the very first component measured has you “Identify which SQL Server components to use to support business requirements.” To do this you’ll need to be able to recognize the strengths of the different components of SQL Server. In my last 70-451 article I covered SQL Server Agent. This time I’d like to cover a few more of these components by studying some business requirements, and choose the component or components that would meet those requirements.
Scenario 1
- Import data from various data sources such as Excel, SQL Server 2000, 2005 and 2008 as well as some flat files.
- Profile the source data before it is import.
- Provide collaboration and offline capabilities to mobile users.
- Allow mobile users to use heterogeneous data stores.
Let’s look at the first requirement import data from multiple sources. This should jump off the page at you, screaming Integration Services. That’s exactly what it was built for! While you could set up linked server to pull in the SQL data, if you wanted to process all the data sources the same way, Integration Services is the solution!
Next, Profiling the source data before import. Personally I’d put this in Integration services as well. My usual method for ETL (Exchange, Transform, and Load) is import the data in a “raw” format. That means I just pull it into tables without any data types (straight NVARCHAR), no keys, constraints, indexes, etc. Just pull the data in. If you can’t pass this step, then your process requires a manual intervention.
Once you’ve got the raw data imported, analyze your data. Test your constraints against the raw data, do any of them fail? If so, can you apply automatic corrections? If not, fail, and alert an operator.
Once you’ve analyzed all your constraints, then load the data to your production tables. Until you’ve “scrubbed” your data, don’t try to apply Analysis Services to it. That’s just overhead.
Now when I see the mobile requirement I thought about my days with AvantGo. A company whose sole mission was to provide data to disconnected (or occasionally connected) mobile devices. The solution I was contemplating was building SQL Express versions of the database to allow users to take the data with them, and then build a process to read disconnected edits back into the main database through some type of merge replication. But for the 70-451 you need to select options that are components of SQL Server, not design your own.
That led me to “choose” the Microsoft Sync Framework. It’s key goal is to help you build offline capabilities and mobile collaboration with multiple data sources. If you’re not intimately familiar with it, which I’m not, I would suggest reading the overview data sheet to get an idea of what it’s useful for. That’s the whole point of this first topic in the 70-451, be able to choose the right tool for the job, not necessarily build those solutions.
Scenario 2
In our next scenario, choose the tools you would give you the following capabilities:
- Executes SQL Server Integration Services (SSIS) packages
- Executes Transact-SQL
- Schedules tasks
- Sends alerts
You did read my last article, right?
Just checking. All of those requirements can be met by using the SQL Server Agent.
Scenario 3
Next, you’re creating an application for the order processing department.
- The application will send e-mail notifications to sales representatives when specific orders for priority customers are shipped.
- When an order from a priority customer that is over $1000.00 is shipped, the application must e-mail the customer’s assigned sales representative and include details of the order as an e-mail attachment.
- E-mail should be sent using Simple Mail Transfer Protocol (SMTP).
- Impact on the SQL server should be minimized.
As soon as you see requirements for sending mail from SQL Server, you should immediately think of Database Mail. During the exam, if you see an option for SQL Server Mail and Database Mail, just remember that Database Mail is the 2005+ replacement for SQL Server Mail.
In this scenario, the fact that it requires sending mail through SMTP is a hint they want you to use Database Mail, since SQL Mail used MAPI (Messaging Application Programming Interface). Some other key notes you’ll want to keep in mind when deciding Database Mail versus SQL Mail: Database Mail can be encrypted, Database Mail requires Service Broker to be running, SQLMail requires a version of Outlook (or some other MAPI client) to be installed on the server. That could cause you security issues if you’re not careful!
Another word about SQL Mail, As of SQL Server 2008 R2, it is officially deprecated. All new development needs to use Database Mail. While you could still use it in 2008 R2, there is some information out there that leads me to believe it’s completely gone in Denali!
On a completely different note, if you see these kinds of requirements and you see SQL Server Alerts as an option, skip that. When you’re wanting to send mail based on logic (T-SQL), you want Database Mail, if you want to send email based on server status or errors, then you might be talking about SQL Server Alerts. SQL Server Alerts provide automatic notification when specific errors or events occur on the SQL server. When a server error or event occurs, it is recorded in the Windows application log. SQL Server Agent reviews the application log entries and fires alerts defined for the recorded events.
Next time I’m going to cover some scenarios that would require you to know something about Service Broker and Full-Text searching. Can you think of any times you had to know about different components of SQL Server and apply them to a business need? If so, please share them. These scenarios can help others learn when to apply a component, and when not to apply that component.
If you have any questions about prepping for the 70-451, please pass them along…I’m here to help!