Notification Services

SQL Server Notification Services, referred to as SQLNS, was meant to be an all-purpose message or messaging delivery system. Its architecture is based on a subscriber, subscriptions, one or more events, and a notification. If you have events whose occurrence you want messaged to a user, that user could receive a notification via PDA, SMS text, and other forms of delivery.

If you are familiar with Oracle’s BI Publisher application, what SQLNS is similar to is the scheduling and bursting of reports. The concept is that you can send versions of whatever information to whomever in various formats. Overall, SQLNS sounds like a fairly decent feature. The problem is that it was kind of a non-starter and it wasn’t widely adopted (not that this has ever happened to any of Oracle’s products…). So, going forward in releases of SQL Server 2008, SQLNS is not to be found. However, the functionality found in SQL Server 2005 can be used against or applied to 2008.

There are also some additional licensing requirements associated with SQLNS, just like there are with BI Publisher.

SQL Server Agent

The usefulness and utility of the SQL Server Agent cannot be overstated. So many things within SQL Server are and can be driven by the Agent. So much so that the Agent can be overused, and most of the issues regarding this concern security. The Agent is fairly secure within its own domain (i.e., server or instance it runs on or in), but is not so secure outside of that area. The running of the Agent, with respect to it running as a service, is often tied into the overall MSSQL service in the first place. As an analogy, Oracle supports the automatic starting of an instance via init.d scripts, so whatever is needed or associated with the Oracle instance can also be started along with the instance (listener, OEM, Database Control, etc.). The same holds true with the SQL Server instance and its agent service.

Outside of what it takes to run a database, pretty much all other maintenance, alert, logging, and notification tasks fall onto the shoulders of the Agent. Upon examining the sub items under the Agent in the Object Explorer tree, that notion should be fairly clear.

Jobs

All jobs, regardless of what they are or which category they fall under, can be run by the Agent. Backups, index maintenance, and reports run via stored procedures (or pasted in T-SQL statements/queries), to name a few, are examples of what the Agent executes for you.

When does the Agent perform a job? Much like Oracle Database, it is done in one of two ways: on a schedule or on an ad hoc basis. The creation of a job is very much like how Oracle jobs or schedules are created (conceptually, not so much syntax-wise). Right-click Jobs and create a new one. The dialog window that next appears is shown below (with Name filled in by me; otherwise it is blank).

All you have to do is work your way down the options in the “Select a page” pane. Once everything is filled in or entered, click the Script button in the top menu bar to generate a script. You’ll see many commands/statements, but they’re fairly readable and you will be able to see similarities between MSSQL and Oracle.

In Oracle, how would you view the status of job? Examining V$SCHEDULER_RUNNING_JOBS is one way, and navigating through Database Control is yet another. In MSSQL, you can fire off the Job Activity Monitor (whose location has changed in SQL Server 2008, by the way). The GUI display via Database Control is close to the tabular display MSSQL presents. Just like in Oracle, you can view the history of a job via Management Studio.

Alerts

One feature common to both systems is what you can do with how and when alerts are managed. When the 10g version of Enterprise Manager/Database Control came out, the event notification feature was a nice touch. Unfortunately, clearing alerts could be somewhat problematic and the interface was clunky. Not so with the Alerts feature within MSSQL. You get an alert, deal with it, it is recorded in a log file, and move on. You are not stuck with a visual display of a stale alert for some indeterminate amount of time.

For the interface, if your SQL Server instance is running via the service, and you have SQL Server Management Studio installed, you will have access to alert management. You are not going to see events like what is shown below in Database Control.

Here we see that the console is at least running as a web service.

Click Login and now we have an obscure Java error stack.

Anyway, SSMS has a drop down list of values for all kinds of alert events, some of which are shown below.

Further, alerts can also be set against a specific database, with <all databases> being the default. Moreover, if you prefer, you can flag an alert based on the error number or when the message text matches your criteria.

Operators

The concept of an Operator in SQL Server is slightly different from a schema in Oracle database. An operator can be a utility type of account that has nothing to do with data or other objects. It’s just a “person” who is set up to send messages for you. Once you have an alert (or other object, such as a job) configured, and there is a need to send a message based on an event or alert, the operator is basically the sending agent for you. A very common step in configuring a new MSSQL instance is setting up Database Mail. Hand-in-hand with database mail is the account that is going to be the sender in the message header.

Proxies

Expand the Proxies item to see the following list.

What do these items have in common? It may not be readily clear, but what they have in common is that these are all interfaces into features or areas outside of the MSSQL instance and its databases. A very common requirement in Oracle is a need to access something on the file system (or at least be able to run an OS command). You need some type of security context for this. Think about a directory object in Oracle. You create it, and then grant read/write to a user. Underneath all of this is the requirement that the “oracle” account (typically) has file permissions itself on that location. In that context, the oracle OS account is your proxy into the file system. Same thing here in MSSQL.

Do you have to create a proxy for each and every context? No. Right-click a proxy item and continue with creating a new proxy. In the wizard-like dialog window, you can actually create a proxy, which has access to more than one subsystem (select the Subsystem check box to select all items).

Just like many other features in Oracle, you probably wouldn’t want to be overly generous in adding unnecessary privileges (e.g., don’t grant DBA to all users kind of idea). Once a proxy has been defined, you then add a principal (what MSSQL roughly calls a login account). Users in the sysadmin group will have access to a proxy by default.

Error Logs

Error Logs is the very last item in the Object Explorer, but certainly not the least in terms of usefulness. At first blush, this looks like access to only the MSSQL Agent error logs, but upon selecting a log (current or otherwise), the interface in SSMS allows you to select other logs as well (the SQL Server log list also has a corresponding Current and Archive #X list).

For example, what you see under Windows NT are the same logs you can get to via Computer Management.

There’s nothing new about this, but it is a convenient shortcut and is something that Oracle definitely does not have (at least by default, not that you can’t hack into OS logs otherwise). Oh, and that drill of rolling alert logs in Oracle? This chore is much easier to deal with in MSSQL.

In Closing

Now that you’ve been exposed to how items in the Object Explorer tree in SQL Server map back into Oracle, a lot of the mystery behind or questions like “I have this in Oracle, where is that in SQL Server?” can be readily explained or answered. Knowing where features are and understanding how they work makes it that much easier to learn SQL Server. After that, it’s a matter of understanding some slight differences in SQL syntax, T-SQL code, and the data dictionary (lots of dynamic management views and built-in stored procedures).

Additional Resources

Oracle as a Data Source
Setting up Oracle as a Data Source for SQL Server
Configuring Oracle as a Data Source for SQL Server
MSDN About SQL Server Agent
OTN Scheduling Jobs
Microsoft SQL Server compared to Oracle
Oracle Technical Comparison of Oracle Database 11g and SQL Server 2008: Focus on Manageability

Back to DBAsupport.com