The SQL server

Published: Last Edited:

This essay has been submitted by a student. This is not an example of the work written by our professional essay writers.

Part A

1. What are the benefits of configuring mail on database?

Microsoft SQL Server 2005 includes a new and improved method for message delivery to and from the SQL Server. This feature, known as Database Mail, allows SQL to send and receive messages through SMTP delivery. One of the many benefits of SQL's Database Mail service is that it will work with any SMTP service, regardless of whether or not it requires authentication. The Database Mail feature in SQL Server 2005 is a tool that allows you to generate and send email messages from your server

Database Mail uses SMTP for message delivery. Messages can be generated from within SQL and can include attachments from outside of the SQL environment. One of the primary benefits of the Database Mail feature is its ability to use any SMTP server to relay messages. This is a significant improvement over prior versions of SQL that use SQLMail and requires a MAPI-compliant mail server (such as Microsoft Exchange) and a MAPI client (such as Microsoft Outlook). Although SQLMail is still available in SQL 2005, it is there for backward compatibility only and will be removed in later versions of SQL.

Another benefit of Database Mail is that it allows you to configure authentication credentials if required by your SMTP server to forward messages and allows you to configure different servers for delivery, in case your preferred server is not available. SQL also uses an external executable, DatabaseMail90.exe, to handle message delivery to an SMTP server. This allows the SQL Server to isolate itself from the process that relays the messages to the SMTP server.

Database Mail uses the msdb database for storing configuration information about Database Mail, controlling access to the feature, and queuing messages until they are ready for delivery. Prior to configuring Database Mail, there are a couple of things you need to consider.

2. Explain the process of Managing Profiles and Accounts on sql server 2005.

Managing Profiles and Accounts

Profiles are commonly used as a unit of management for SMTP accounts. However, as mentioned earlier, there is no one-to-one relationship between the two. You can use the Database Mail Configuration Wizard, or you can use a series of stored procedures to create and delete profiles and accounts as needed. Because you've already been exposed to the different elements of the wizard, you should easily be able to fumble through the different pages to find what you need to configure the accounts and profiles you want. Stored procedures used to create and manage Database Mail accounts and profiles.


The first stored procedure you should know is sysmail_add_profile_sp. This stored procedure allows you to create a new profile to be used by the Database Mail service, and uses the following

Create a New Profile

The following example creates a new mail profile, and returns the integer value generated for the profile ID. Begin by declaring the variable for the profile_id:

DECLARE @profileID INT ;

EXECUTE msdb.dbo.sysmail_add_profile_sp

@profile_name = 'HumanResourcesMail',

@description = 'Mail Profile for the Human Resources team.'

@profile_id = @profileID OUTPUT;

SELECT @profileID;

Create a New Account

So, take a look at this in action. Use the following example to create a new account:


EXECUTE msdb.dbo.sysmail_add_account_sp

@account_name = 'Mail Sender',

@description = 'Generic Account for sending mail',

@email_address = [email protected]',

@display_name = 'SQL Database Mail Account',

@mailserver_name = '',

@username = 'MailSender',

@password = [email protected]',

@account_id = @accountID OUTPUT;

SELECT @accountID;

3. Briefly explain different mail sending options for SQL 2005.

After Database Mail has been enabled, you can use the sysmail_configure_sp stored procedure. The syntax of the sysmail_configure_sp stored procedure is as follows:

sysmail_configure_sp [@parameter_name =] 'name', [@parameter_value = ] 'value' , [ @description = ] 'description'

Similar to the options listed here, you can use the values in the following table for the parameters.

The sysmail_configure_sp stored procedure (as do many of the Database Mail stored procedures) lives in the msdb database. When executing these stored procedures, you'll have to qualify them from within your application or T-SQL statements. Use the following example to set the maximum file size for all attachments sent by Database Mail to 4MB:

EXECUTE msdb.dbo.sysmail_configure_sp 'MaxFileSize', '4194303', 'Max Size 4 MB'

Part B

4. Mention use and benefits of Event notifications?

Event notifications are database objects that send information about server and database events to a Service Broker. They execute in response to data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service. You can use event notifications to either log activity within a database, or to execute an action asynchronous to an event. They are designed to be an alternative to creating DDL triggers or using SQL Trace functions. Because event notifications run outside the scope of a transaction, they can be used inside a database application to respond to events without using any resources defined by the immediate transaction.

Event notifications operate independently of whether or not the transaction commits. They can also be used to perform an action inside an instance of SQL Server in response to a SQL Trace event. Every event notification has its own exclusive Service Broker conversation between an instance of SQL Server and the target service you specify. The conversations usually remain open as long as the event notification still exists on the server. Ending a conversation prevents the target service from receiving more messages, and the conversation will not reopen when the event notification fires again. Event information is an XML data type that provides information about when the event occurs, the object it affects, the batch statement involved, and more. This data can be used by applications that help SQL Server track progress and make decisions.

When designing an event notification, you must define both the scope of the notification and the statement or batch that raises the notification. For example, the event notification can occur as a response to a statement made on all objects in the AdventureWorks database. You can also define the scope as being server-wide, such as triggering event notifications when new databases or logins are created.

5. Explain multi-server jobs?

SQL Server also supports the ability to create and manage jobs on one server that can be run on multiple SQL Servers. This functionality grants you the ability to administer and control multiple servers at once. This can be beneficial when performing system-level tasks, such as backing up the system databases, or controlling database-level tasks like replication. Multi-server jobs are configured by first defining a master server. This master server acts as the source for all jobs that will be run on multiple target servers (see Figure 8-29). When defining a multi-server configuration, be aware that although you can enlist multiple target servers on which remote jobs will run, not every multi-server enabled job will run on all target servers. In fact, you can specify which target servers a multi-server job will run. The downside to this is that each target server can only have one master server. Plan your multi-server job configuration carefully.

There are a few things you need to know about setting up multi-server jobs:

  • Jobs running on multiple servers that have steps running under a proxy account use the proxy account on the target server. Ensure that you have a proxy server on both the master and target server that has the same access and permissions.
  • Each target server can have only one server for all jobs.
  • If you are going to change the name of a target server, you must remove it from the master server, through a process known as defecting, and then re-enlist it after the name change.
  • When removing a multi-server configuration, first defect all target servers before decommissioning the master.

6. what are the advantages of JOB and how JOB is created?

Jobs are really at the core of the SQL Server Agent service. Jobs are operations that perform through a sequence of steps that run Transact-SQL scripts, launch command-prompt applications, ActiveX script tasks, replication tasks, and a variety of other tasks. Each task is defined as a separate job step. Part of the design of the job system is to build each task so that you can build dependencies and workflows between the job steps. A very simple example of this would be a backup job that ran nightly, and then emailed an administrator to inform him or her that the job was complete. The simplicity and complexity of a job is dependent on what you need it to do.

Creating a New Job

Begin by creating a new job in SQL Server Management Studio. For this example, you're going to populate only the most basic information about the job from the General properties page. Feel free to browse through the other property pages in this exercise, but be aware that the configurable elements in those pages are covered later in this chapter.

  1. In Object Explorer, expand SQL Server Agent.
  2. Right-click Jobs and select New Job.
  3. In the New Job dialog box (see Figure 8-16), enter Simple Backup as the job name.
  4. Leave the Owner as the default.
  5. Select Database Maintenance in the Category drop-down list.
  6. In the description, enter Simple Backup Job. Test 1.
  7. Remove the check next to Enabled.
  8. Click OK.

7. What is the advantage of proxies?

SQL Server Agent properties allow you to execute specific job steps with a different security account. This allows you greater flexibility over your application and maintenance designs. It also allows you to create job steps that can be executed by users whose security context would normally prohibit them from running a task. The benefit of this is that the user who creates the job need only have access to the proxy account. The user does not need to create credentials, users, or be given elevated permissions to execute a job step. You can create proxies for the following types of job steps:

  • ActiveX Script
  • CmdExec
  • Replication Distributor
  • Replication Merge
  • Replication Queue Reader
  • Replication Snapshot
  • Replication Transaction-Log Reader
  • Analysis Services Command
  • Analysis Services Query
  • SSIS Package Execution

There is also a folder for creating and managing unassigned proxies. Note that a single proxy can be used for multiple task types, if needed.

Creating a New Proxy

Take a look at the process for creating a new proxy.

  1. In Object Explorer, expand SQL Server Agent.
  2. Expand Proxies and select ActiveX Script.
  3. Right-click ActiveX Script and select New Proxy.
  4. Enter ScriptRunner as the proxy name (see Figure 8-28).
  5. Enter ActiveXProxy as the Credential name.
  6. Ensure that ActiveX Script is selected under "Active to the following subsystems."
  7. Alternately, add additional subsystems, or use the Principals page to identify SQL Logins, server roles, or msdb database roles that can reference this proxy in job creation.
  8. Click OK.