1. What are the benefits of configuring mail on database.
Database Mail, a new addition to the SQL Server 2005 database engine, is as simple to use as it is useful. Destined to be the replacement for SQL Mail, Database Mail uses a Simple Mail Transfer Protocol (SMTP) server to send e-mails rather than using the MAPI accounts that SQL Mail required. This allows your organization to send e-mails with attachments, e-mail query results, attach query results, and format HTML e-mails. It also gives you the ability to set many other configuration settings without requiring you to have an Exchange Server or configuring any type of MAPI workaround.
The advantages of Database Mail
Besides being totally SMTP based, Database Mail has many other advantages:
It has multiple security features for sending e-mail, such as a filter for what attachment extensions can be sent and a governor for attachment size.
2. Explain the process of Managing Profiles and Accounts on sql server 2005
Managing Profiles and Accounts
In Database Mail, the Account holds information that the database engine uses to send e-mail messages. An Account holds information for only one e-mail server, such as the account name, e-mail address, reply-to e-mail address, server name or IP address, and some optional security settings.
To send a Database Mail e-mail, a Profile must be used. A Profile is set up of one or more Accounts. This Profile-Account setup is very useful for a couple of reasons. It allows you to associate more than one Account to a profile, which means that you can associate more than one e-mail server to a profile. So, when you try to send an e-mail, each Account for the profile is tried until the message is successfully sent, which is great in case one or more of your SMTP servers is unavailable. It also allows you to develop your application code for sending e-mails without worrying about changing the Profile name for different environments. You can use the same Profile name for your Development and Production environments; the only difference is that the Accounts contained in the profiles will be different.
It is time to take a look at how to set up a Database Mail account. For our example, I will assume that you are sitting in front of a development machine for which you have sysadmin access. If you are not, you will need to be a member of the DatabaseMailUserRole in the msdb database.
The following script sets up some variables that I will use throughout the example. Note that the entire script will be run in the context of the msdb database, where Database Mail objects are stored.
USE msdb
GO
DECLARE @ProfileName VARCHAR(255)
DECLARE @AccountName VARCHAR(255)
DECLARE @SMTPAddress VARCHAR(255)
DECLARE @EmailAddressVARCHAR(128)
DECLARE @DisplayUser VARCHAR(128)
Here I am setting up our ProfileName, AccountName, STMP server name, and the name that will display in the From field in the e-mail.
SET @ProfileName = 'DBMailProfile';
SET @AccountName = 'DBMailAccount';
SET @SMTPAddress = 'mail.yoursmtpserver.com';
SET @EmailAddress = '[email protected]';
SET @DisplayUser = 'The Mail Man';
The script in Listing A does some clean up work, so if I run the script again, I won't have to worry about errors.
The following section adds our Account, Profile, and Profile-Account association to the system.
EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = @AccountName,
@email_address = @EmailAddress,
@display_name = @DisplayUser,
@mailserver_name = @SMTPAddress
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = @ProfileName
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = @ProfileName,
@account_name = @AccountName,
@sequence_number = 1 ;
Now that everything is set up, I will send a test e-mail.
EXEC msdb.dbo.sp_send_dbmail
@recipients=N'[email protected]',
@body= 'Test Email Body',
@subject = 'Test Email Subject',
@profile_name = @ProfileName
To see if the message was sent successfully, I can run a query on the sysmail_allitems system view.
SELECT * FROM sysmail_allitems
Q.3 Briefly explain different mail sending options for SQL 2005.
When you first read about the mail system in SQL Server, you might get a little confused. That's because there are actually two uses of mail within the system. These two mail subsystems have separate uses, identities, and configurations. Unfortunately, many articles don't explain this, so you begin to feel a little like the blind men describing the elephant. To make matters even more confusing, SQL Server 2005 adds another type of mail system, called database mail. Today, we'll take a look at the mail system in SQL Server and clear everything up. There's a lot more than what I'm able to cover in this tutorial, so make sure you check the resources I have at the end of this article.
The mail subsystem used in both SQL Server 2000 and 2005 is called SQL Mail. Once you configure your mail client and the server, SQL Server is able to send and receive mail. I'll show you how to do that setup in a moment. This mail system has some disadvantages, however, since it requires Microsoft Outlook, and works best with a Microsoft Exchange server. This means you have to buy and set up a client on the server where SQL Server is installed. In fact, it's even a bit more onerous than that. I've had the best results for SQL Mail when I used Outlook 2000, which of course is no longer current and has a few security issues.
Another disadvantage with SQL Mail is that it can affect the behavior of SQL Server when it has a problem. It runs "in process," which is computer-speak for working within the same software resources as another process. The upshot is that it could potentially de-stabilize or your freeze your SQL Server if you get into a mail problem. I've had this happen to me from time to time.
But SQL Mail has one very powerful feature that the later version (Database Mail) doesn't: the ability to read mail, not just send it. There are certainly security implications for this feature, but I have used it in the past and found it very useful.
SQL Server 2005 introduces Database Mail, which addresses the stability and security issues, and adds some handy features as well. For one thing, it does not require any mail client software at all, and another advantage is that it runs out of process. In SQL Server 2005, you might have a mail issue, but it won't trouble the server. As I mentioned, it doesn't have the ability to read and process mail, but it is more secure because of it.
Both of the previous examples deal with mail within the database engine. But SQL Server also enables the SQL Server Agent to send mail, which is a good thing since most of the maintenance you'll do uses Agent. When the maintenance fails, succeeds or completes it can send you mail. Not only that, but alerts, jobs and other Agent features can also use e-mail.
3. Let's take a look at setting up mail in SQL Server and then configure Agent mail. After that we'll explore how you can use e-mail with SQL Server.
4. Mention use and benefits of Event notifications?
Event Notifications is a feature similar to DDL triggers but with slightly different capabilities. The most important difference is that Event Notifications uses SQL Service Broker to asynchronously deliver messages, rather than working in the scope of the transaction, as do DDL triggers. This means that whether or not the transaction completes, the Event Notification will still be delivered. Likewise, it means that Event Notifications have no control over the transaction, and cannot be used to restrict operations-no rollback is possible within a notification.
Like DDL triggers, Event Notifications can fire on database or server-level DDL operations. However, Event Notifications also has the capability of firing on trace events. For instance, an Event Notification can be created that will fire any time a deadlock event occurs. This capability offers a lot of flexibility for capturing server events, which was only possible via traces previously.
Also similar to DDL triggers, Event Notifications uses the EVENTDATA function for programmatic access to information about the event that fired the notification.
5. Explain multi-server jobs?
Multiserver jobs is the process of automating administration across multiple instances of Microsoft® SQL Server™.
Use multiserver jos if you:
With multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to and receives events from target servers. A master server stores the central copy of job definitions for jobs run on target servers. Target servers connect periodically to their master server to update their list of jobs to perform. If a new job exists, the target server downloads the job and disconnects from the master server. After the target server completes the job, it reconnects to the master server and reports the status of the job.
For example, if you administer departmental servers across a large corporation, you can define:
Write this backup job one time on the master server and then enlist each departmental server as a target server. In this way, all the departmental servers run the same backup job even though you defined it only one time.
Multiserver jobs features are intended for members of the sysadmin role. However, a member of the sysadmin role on the target server cannot edit the operations performed on the target server by the master server. This security measure prevents job steps from being accidentally deleted and operations on the target server from being interrupted.
6 .what are the advantages of JOB and how JOB is created?
Sql server jobs::
In the day-to-day operations of running a business, employees can get ca::ught up in more than one project, as is often the case.
We may be IT professionals but there is a limit to the amount of work that we can handle in one day and still stay on track.
With the amount of multitasking that employers feel their employees should be able to handle increasing with each passing day, the need to find a way to help themselves get the job done becomes increasingly more important.
Finding help and ways to get solutions into production even faster can make the day less stressful thereby making it more productive in the long run.
In the daily routine of a database administrator or a database developer, SQL Server has some functionality that can make life simpler and more dependable in the long run: SQL Server jobs.
How can the ability to create a job to handle routine tasks make the life of a database administrator or developer and how are SQL Server jobs created?
A SQL Server job is a collection of steps executed by the database engine by SQL Server Agent.
The job can perform many different functions within it that can save time and effort on the part of employees.
For example, a job can be created to import a daily update file internally or externally via an FTP server.
Another job can be configured to handle routine maintenance tasks as well as handling one-time production updates that may be needed in the future.
A job has many different options that can be configured during its creation in order to perform the duties that is needed of it.
Creating jobs :::::
In order to manage the jobs within a SQL Server database, use Enterprise Manager, and expand the Management node in Enterprise Manager.
Under the Management node is a node entitled SQL Server Agent. Expand this node and you will see the Jobs item that is used to represent the jobs within the system.
To see the jobs currently in the system, select Jobs. See the following illustration for an example of the steps described above::
The window on the left will show the jobs currently in the system. This window displays a lot of information about the SQL Server jobs currently in the system that can prove valuable.
The first column lists the name of the job that was entered when it was created.
The second column lists the category of the job that was assigned at the time of the job's creation. This helps to clarify the jobs and organize the jobs in the system.
The next column tells whether or not the job is enabled in the system.
If you wish to disable or enable a job in SQL Server, right click on the job and select on the Disable Job or Enable Job option from the popup menu.
The next column tells whether or not the job is runnable in the system.
After that there is a column, which tells whether or not the job is scheduled to run in the system.
The status column alerts the user to what is currently going on with the job. If the job is running it alerts the user to what step it is in currently.
The last run status column tells the status of the job's last run and when that occurrence took place.
The final column shown tells when the next scheduled run is to take place.
To see the properties of a job in the window, right click on the job and select Properties from the popup menu or double click on the job. Either of these actions will bring up the following screen in order to set and/or view options contained in that job.
7 What is the advantage of proxies
There are many benefits of using a proxy server. First, the client machine can exchange data with the remote server without making a direct connection. This way, the client's real internet address will not be known to the remote server. This is sometimes called anonymizing because it makes the client anonymous A second advantage is that when the proxy server itself is able to serve the request made by the client, it will not contact the remote server any more. So the load on the remote server will be reduced by using a proxy server. This type of proxy servers are called caching servers.
Big organizations (or even countries) sometimes use proxy servers to control access to the Internet. A large bank may use a proxy server that only allows connections to other websites relevant to banking. The proxy server might however block access to Websites offering free email or serving pornographic material. It might also block access to file sharing applications. Limiting access to specific content on the internet is also called internet filtering.
