We have compiled most frequently asked SQL Server Interview Questions which will help you with different expertise levels.
SQL Server Interview Questions on Notification Services
Question 1.
What are notification services?
Answer:
Notification services help you to deliver a messaging application that can deliver customized messages to a huge group of subscribers.
In short, it’s a software application that sits between the information and the recipient.
Question 2.
(DB)What are the basic components of Notification services?
Answer:
Following are three basic components of SQL Server notification services:-
1. Events: – Events are triggers on which the user wants relevant information.
Example: – Stock exchange can have “stocks price down” events or a weather department has “heavy rainfall” events. _
2. Subscriptions: – Subscriptions are nothing but users showing interest in certain events and registering them to the events for information. For instance, a user may subscribe to a “heavy rainfall” event. In short subscription links the user and the event.
3. Notifications: – Notification is the actual action which takes place that is message is sent to the actual user who has shown interest in the event. Notification can be in various formats and to a variety of devices.
4. Notification engine: – This is the main coordinator who will monitor for any events; if any event occurs it matches with the subscribers and sends the notifications.
In short notification engine is the central engine which manages “Events”, “Subscriptions” and “Notifications”.
Question 3.
(DB)Can you explain the architecture of Notification Services?
Answer:
Note This will go more as a DBA question.
Following are the detail sections in SQL notification services:-
Notification Service application:- It’s a simple user application that will be used to add a subscription to the subscription database.
Event providers:- All events reside in Event providers. There are two event providers which are provided by default “File System watcher” and “SQL Server event provider”. “File System watcher” detects changes in operating system files. “SQL Server event provider” watches for SQL Server or analysis service database for change. You can also plug in custom event providers. When event providers find any change in the database they send the event in the “Event” table.
The generator:- checks the event database, when it finds any event it tries to match with the subscription and sends it to the notification database. So generator in short is the decision-maker between the subscribers and the events.
Distributor: – Distributor continuously pools the “Notification” database for any “Notification’s” to be processed. If the distributor finds any entry it retrieves it and formats it so that it can be delivered to the end recipient. Formatting is normally done using “XML” and “XSLT” for rendering purposes. After the formatting is done it is then pushed to the “distribution providers”. They are nothing but a medium of delivery. There are three built-in providers:-
- V SMTP provider
- V File provider
- V HTTP provider
Question 4.
(DB)Which are the two XML hies needed for notification services?
What are ADF and ACF XML files for?
Answer:
ADF (Application definition file) and ACF (Application configuration file) are core XML files that are needed to configure “Notification Services application”.
ACF file defines the instance name and the application’s directory path of the application. This is the application that runs the notification service.
ADF file describes the event, subscription, rules, and notification structure that will be employed by the Notification Services application.
After these files have been defined you have to load the ADF file using the command line utility or the UI provided by SQL Server 2005. Click on the server browser as shown below and expand the “Notification Services”, the right-click to bring up the “Notification Services” dialog box.
You will also have to code the logic to add a subscription here’s a small sample.
using Microsoft.SqlServer.NotificationServices;
using System.Text;
public class NSSubscriptions
{
private string AddSubscription(string instanceName, string
applicationName, string subscriptionClassName, string
subscriberId)
{
NSInstance myNSInstance = new NSInstance(instanceName);
NSApplication myNSApplication = new NSApplication
(myNSInstance, applicationName);
Subscription myNSSubscription = new Subscription
(myNSApplication, subscriptionClassName);
myNSSubscription.Enabled = true;
myNSSubscription.Subscriberld = subscriberld;
myNSSubscription [ "Emailid"] = "shiv__koirala@yahoo.com";
string subscriptionld = myNSSubscription.Add( );
return subscriptionld;
}
}
Note As this been an interview book it's beyond the scope of the book to go in to detail of
how to create notification, It's better to create a small sample using MSDN and get some fundamentals
clear of how practically "Notification services" are done. Try to understand thefdlformat of both the XML files.
Question 5.
(DB)What is Nscontrols command?
Answer:
For creating notification services you can either use the dialog box of notification service or use the command-line utility “Nscontrols” command. So just in short “Nscontrol” is a command-line tool that’s used to create and administer Notification Services applications.
Note You can referMSDNfor "Nscontrol" commands.
Question 6.
What are the situations you will use “Notification” Services?
Answer:
Note This question is to judge if you can practically map the use of notification service with real world.
If you have read the answers I am sure you can map to a lot of practical applications. But still in case you want some decent answer you can say about the weather forecast, stock ticker, etc. Let’s say you want a build an application where the user wants to be alerted on the mobile if the weather reaches XYZ degrees or PQR cm rainfall, Then you can create a notification service, provide the appropriate provider, and map it to the devices.