Ans.Besides being totally SMTP based, Database Mail has many other advantages:
- It runs outside of the database engine, so the stress placed on the database engine is minimal.
- It is cluster aware and fully supported in a clustered environment.
- Its profiles allow for the redundant use of SMTP servers.
- It allows you to send a query text as a parameter to the stored procedure, which will execute the query and send the results in the e-mail.
- The messages are sent asynchronously via a Service Broker queue, so you will not have to wait for a response when sending an e-mail.
Q2.Explain the process of Managing Profiles and Accounts on sql server 2005Ans.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. In this section, you learn about the stored procedures used to create and manage Database Mail accounts and profiles.sysmail_add_profile_sp .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 syntax:Q 4.Mention use and benefits of Event notifications?
Ans.Event notification is a term used in conjunction with communications software for linking applications that generate small messages (the "events") to applications that monitor the associated conditions and may take actions triggered by events.
Event notification is an important feature in modern database systems (used to inform applications when conditions they are watching for have occurred), modern operating systems (used to inform applications when they should take some action, such as refreshing a window), and modern distributed systems, where the producer of an event might be on a different machine than the consumer, or consumers. Event notification platforms are normally designed so that the application producing events does not need to know which applications will consume them, or even how many applications will monitor the event stream.
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.
Q 5. Explain multi-server jobs?
Ans. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.
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.
Ans. 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.
- In Object Explorer, expand SQL Server Agent.
- Right-click Jobs and select New Job.
- In the New Job dialog box (see Figure 8-16), enter Simple Backup as the job name.
- Leave the Owner as the default.
- Select Database Maintenance in the Category drop-down list.
- In the description, enter Simple Backup Job. Test 1.
- Remove the check next to Enabled.
- Click OK.
Q7. What is the advantage of proxiesAns. Advantages of Using Proxy
- 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.
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
- 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.