A database mail account

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.

Setting up an Account

A Database Mail account identifies how SQL Server 2005 should communicate with an SMTP server. The account specifies how email should be formatted and sent. A single account will identify a single SMTP server, and authentication method. An account used for Database Mail does not correspond to a SQL Server login account.

Setting up a Profile and Associating it with Accounts

Before you can send Database Mail to an SMTP server identified in an account, you need to associate the account with a profile, and grant access to the profile. Database Mail profiles are used to improve mail security. There are two kinds of profiles, public and private. A public profile is available to anyone that has been given access to the msdb database and is a member of the DatabaseMailUserRole in the msdb database, whereas a private profile can only be used by specific users that have been granted access to the private profile. A mail profile can be associated with one or more accounts.

You can manage profiles using the Database Mail Configuration Wizard, or use some T-SQL command similar to the following to set up a mail profile:

Create a Database Mail profile

Q3: Briefly explain different mail sending options for SQL 2005.

  1. @profile_name : name of the profile the stored procedure will use.
  2. @recipients: list of email address that will receive your message.
  3. @copy_recipients : the same as using the CC field in standard mail client.
  4. @body :text of the message
  5. @Attachments: It attaches the files or some important documents to your mail.
  6. @Sensitivity: It shows that the data or the document that it is confidential and it must be opened with great care.
  7. @Sensitivity and Importance: It tells about the sensitive nature of the document or the file.

Part B

Q4: Mention use and benefits of Event notifications?

Event notifications execute in response to a variety of Transact-SQL data definition language (DDL) statements and SQL Trace events by sending information about these events to a Service Broker service.

Event notifications can be used to do the following:

  • Log and review changes or activity occurring on the database.
  • Perform an action in response to an event in an asynchronous instead of synchronous manner.

Event notifications can offer a programming alternative to DDL triggers and SQL Trace.

Event notifications run asynchronously, outside the scope of a transaction. Therefore, unlike DDL triggers, event notifications can be used inside a database application to respond to events without using any resources defined by the immediate transaction.

Unlike SQL Trace, event notifications can be used to perform an action inside an instance of SQL Server in response to a SQL Trace event.

When an event notification is created, one or more Service Broker conversations between an instance of SQL Server and the target service you specify are opened. The conversations typically remain open as long as the event notification exists as an object on the server instance. In some error cases the conversations can close before the event notification is dropped. These conversations are never shared between event notifications. Every event notification has its own exclusive conversations. Ending a conversation explicitly prevents the target service from receiving more messages, and the conversation will not reopen the next time the event notification fires.

Event data can be used by applications that are running together with SQL Server to track progress and make decisions. For example, the following event notification sends a notice to a certain service every time anALTER TABLEstatement is issued in theAdventureWorkssample database.

Q5: Explain multi-server jobs?

To create a multiserver environment, use the Master Server Wizard. For information about using the Master Server Wizard, seeHow to: Make a Master Server (SQL Server Management Studio). Full Secure Sockets Layer (SSL) encryption and certificate validation are enabled for connections between master servers and target servers by default. For more information, seeSetting Encryption Options on Target Servers.

The wizard takes you through the following steps:

  • Checking the security settings for the SQL Server Agent service and the SQL Server service on all servers intended to become target servers.
    We recommend that both services should be running in Microsoft Windows domain accounts. For more information, seeChoosing the Right SQL Server Agent Service Account for Multiserver Environments.
  • Creating a master server operator (MSXOperator) on the master server.
    MSXOperator is the only operator that can receive notifications for multiserver jobs.
  • Starting the SQL Server Agent service on the master server.
  • Enlisting one or more servers as target servers.

If you have a large number of target servers, avoid defining your master server on a production server. Otherwise, target server traffic can slow performance on your production server. If you also forward events to a dedicated master server, you can centralize administration on one server. For more information, seeManaging Events.

Q6: what are the advantages of JOB and how JOB is created?

Ajob is a specified series of operations performed sequentially by SQL Server Agent. A job can perform a wide range of activities, including running Transact-SQL scripts, command prompt applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. Jobs can run repetitive or schedulable tasks, and they can automatically notify users of job status by generating alerts, thereby greatly simplifying SQL Server administration.

To create a job, a user must be a member of one of the SQL Server Agent fixed database roles or thesysadminfixed server role. A job can be edited only by its owner or members of thesysadminrole.

Jobs can be written to run on the local instance of SQL Server or on multiple instances across an enterprise. To run jobs on multiple servers, you must set up at least one master server and one or more target servers. To create a job:

  1. In theObject Explorer, connect to an instance of the SQL Server Database Engine and then expand that instance.
  2. ExpandSQL Server Agent.
  3. Right-clickJobs, and then clickNew Job.
  4. On theGeneralpage, in theNamebox, type a name for the job.
  5. Clear theEnabledcheck box if you do not want the job to be run immediately following its creation. For example, if you want to test a job before it is scheduled to run, disable the job.
  6. In theDescriptionbox, enter a description of what the job does. The maximum number of characters is 512.

Q7: What is the advantage of proxies

Ans: These sites offer a simple and quick solution if you want to browse 'banned' sites using the school's network. By passing through such a site, the school's servers don't recognize the site's domain name as 'banned,' and so should allow you to surf your social networks to your heart's content.

Proxy account support allows for a large number of user accounts to exist in a SQL Server database (which scales much better than Active Directory) to be mapped to smaller number of Windows accounts stored in Active Directory, and still leverage Windows ACL security.

Using proxy accounts minimizes the administration overhead of configuring ACLs, because fewer Windows accounts are used for granting access to your site. Using proxy accounts also improves performance for your site, because SQL Server is much faster than Active Directory. However, using proxy accounts is inherently less secure than storing all user accounts in Active Directory.

Using proxy accounts can be useful if a site has a large user base that it does not want to keep in AD, however, you want to leverage Windows authentication to secure your site resources using access control lists (ACLs).

User accounts are kept in a database, such as SQL Server, which scales better than Active Directory, and a much smaller number of Windows accounts are kept in Active Directory, which are used as proxy accounts. Hence a large number of users, typically millions for this scenario, can be mapped to a small number of proxy accounts. This also eases the management of permissions due to a smaller number of Windows accounts in which to configure ACLs. Each user can be marked as belonging to a specific role, which is used map a user to a particular proxy account.

Advantages of Using Proxy Accounts

  • Better Performance: SQL Server authenticates users much faster than active Directory.
  • Scalability: If you exceed the maximum recommended number of users per Active Directory domain, around 10 million users, you would need to add additional Active Directory partitions or additional domains. This complicates the manageability of Active Directory. You can store many more users in a single SQL server, simply by adding more hard drive space or processors to a single machine.
  • Lower total cost of ownership: Even though Active Directory can store many more objects, for performance reasons in e-business scenarios, it is recommended that you store no more than three million objects/users per domain in Active Directory.

Commerce Server provides partitioning support across multiple forests to scale Active Directory. This has been tested up to 10 million objects/users, although there is no hard-coded limit.

SQL Server can store many more user accounts, per machine, than active directory. This lowers the total cost of ownership if you are storing more than 10 million Windows user accounts.

* Easier to manage: Because users access your site using the credentials of the proxy account for their role, and because you can map millions of users to a few roles, the task of setting ACLs on your site resources is greatly reduced.