Operator in sql server agent

Operators is nothing but it is a groups or teams or people that can receive the message or alert (electronic notification) when sql jobs has failed or completed or any alerts have been raised.

 Read Alert here

 With the help of Operator sql server agent notify to the group or teams.

Learn how to create Operator in sql server agent step by step

Open sql server management studio (SSMS).

Start the sql server agent.

In sql server agent you will get the Operator.

Now click on New Operator.

You will get the new window New Operator.

Select General tab.

Write the name of operator and check the Enable check box.

Select the Notification options. Below are the notification options are available

Ø  E-Mail

Ø  Net send

Ø  Pager

Now we can schedule our Operator according to our business hours.

                

  

Now go to Notification tab. You will set on which action you want to notify the operator either Alert or Jobs. Select the option you will get the list of jobs or Alerts.

You can select the notification options (Email, Pager or net send).

Click ok. Now operator is created.

  

You can also create operator using sql script. You can generate the sql script.

Click on the script and select Script Action to new Query Window. You will get below sql query.

USE [msdb] GO EXEC msdb.dbo.sp_add_operator @name=N'My First Operator',               @enabled=1,               @weekday_pager_start_time=80000,               @weekday_pager_end_time=180000,               @saturday_pager_start_time=80000,               @saturday_pager_end_time=180000,               @sunday_pager_start_time=80000,               @sunday_pager_end_time=180000,               @pager_days=127,               @email_address=N'bageshkumarsinghmca@gmail.com' GO EXEC msdb.dbo.sp_add_notification @alert_name=N'My Database Alert',  @operator_name=N'My First Operator', @notification_method = 1 GO

Now operator is ready to use.

 Now Enjoy. 

Alert in sql server agent

With the help of alert we can automatically notify database administrators or groups when unexpected error occurs. We can notify by below media

Ø  E-Mails

Ø  Pager

Ø  Net  send

Let’s start step by step to create Alert in sql server agent

Open the sql server and start sql server agent.

Right click on the Alert and select new Alert.

You will get New Alert window. Write the Alert name and select Enable check box.

Select Alert Type. There is bellowing type of Alert.

Ø  Sql server event alert: -

If you are creating alert for like fatal error or sql syntax errors or hardware related issue or etc the select this type of alert.

Ø  Sql server performance condition alert :-

If you are creating alert for sql server performance related alert like CPU load or free disk space etc then use this type of alert.

Ø  WMI event alert  :-

If you are creating alert for Windows Management instruments (system related information) the go with WMI events.

I am selected sql server event Alert.

Now we can select database name on which we want create Alert. We can also create it on all databases.

Select Error number or severity.

You can check the message text and write your message.

Now go the response tab.

If you want to assign the Alert to the particular job the check the Execute job and select the job name.

Don’t worry if you are able to create job. We will learn how to create jobs in sql server agent in next post.

In same way if you want to notify to the operator you can select that operator. In next post we will learn how to create operator.

Select Options

In this section you select the medium to send the alert notification. You can also write the additional notification message in the text box.

Now click ok. Alert is created.

If you want to generate the script of this alert the click on the script.

You will get the below script.

USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'My Database Alert',               @message_id=0,               @severity=1,               @enabled=1,               @delay_between_responses=0,               @include_event_description_in=1,               @notification_message=N'my first job alert.',               @event_description_keyword=N'Sql job failed',               @job_id=N'00000000-0000-0000-0000-000000000000' GO

You can create the Alert by two ways. GUI or using script.  Alert has been created.

Now it is ready to use.

Hope this will be help you.

Change data capture (CDC) in sql server

CDC helps to track insert, update and delete activity on table’s data.

We can enable CDC in two levels.

Database level

Table level

When we enabling the CDC it create CDC schema, CDC User, Meta data tables and other objects (system stored procedure and jobs).

Step by Step enabling CDC on Database level

1. Open SSMS.
2. I am applying CDC on Test database.
3. Open the sql query editor.

EXEC sys.sp_cdc_enable_db

Run the stored procedure.

Once it executed successfully. You we get the message like command completed successfully.

Now CDC is enabled on the “Test” database.

Once CDC enabled on Database it create some tables on System tables.

Ø  Cdc.captured_column

Ø  Cdc.change_table

Ø  Cdc_ddl_history

Ø  Cdc.index_columns

Ø  Cdc.Isn_time_mapping

Ø  Dbo.systransschemas

And it also creates some system stored procedure.

Step by Step to enable on table.

For enabling CDC on table level we use below stored procedure

Exec sys.sp_cdc_enable_table @source_schema=N'dbo', @source_name=N'Table_Name', @role_name=NULL

# I am enabling CDC on emp table in Test database.

Execute the above sp.

It create a table on System table

cdc.dbo_emp_CT

Stored procedure also create sql server Agent job.

Ø  cdc.Test_capture

Ø  cdc.test_cleanup

Now we enable CDC on both level database as well as Table level.

Now it’s time to test.

Insert Operation

In Emp table now I have two records.

Now I am inserting one record on this table.

INSERT INTO EMP ([EmpID],[Name],[Address],[Mobile])   VALUES ('3','Kumar','Pune','8888802459')

Now we can see that in cdc.dbo_emp_CT one record inserted.

Update Operation

Now I am updating one record. I am changing the address of Kumar pune to Mumbai.

You are seeing two records. One is having old value and second cone having updated records.

Delete operation

Now I am deleting one record from the table.

See deleted record store on 4^th row.

Now you are seeing the different value on  _$operation column. It show the operation which is performed on table.

Number	operation
1	Delete statement
2	Insert statement
3	Value before update statement
4	Value after update statement

cdc.captured_columns Table

 It store the information columns of the table on which we enable CDC.

SELECT [object_id]       ,[column_name]       ,[column_id]       ,[column_type]       ,[column_ordinal]       ,[is_computed]   FROM [Test].[cdc].[captured_columns]

cdc. change_tables

It store the name of table (list of table name) on which we enables the CDC.

SELECT [object_id]       ,[version]       ,[source_object_id]       ,[capture_instance]       ,[start_lsn]       ,[end_lsn]       ,[supports_net_changes]       ,[has_drop_pending]       ,[role_name]       ,[index_name]       ,[filegroup_name]       ,[create_date]       ,[partition_switch]   FROM [Test].[cdc].[change_tables]

Hope this will help you. Thanks!!!

Configure Database mail in Sql server 2014

Database Mail is a way to sending e-mail messages from the SQL Server Database Engine. Using this we can send a mail to the user. Message may have report summary or any other information.

Let’s start to configure the database mail in sql server.

Open sql server connect to database engine.

Go to Management

In Management you will see Database Mail. Right click on this.

You will get database mail configuration wizard

Click next

If you are configuring first time select first radio button.

Click next

Create new profile and write the profile name and description.

Click on Add. You will get a new window new database mail account.

Create a new account.

Set up account name and details.

If you are using organization server then you gave the details of organization mail server and port number. If you are using gmail then

Server name is smtp.gmail.com

Port number is 587

And select SSL secure connection.

For SMTP Authentication better to use basic authentication.

Provide the user name and password.

Click ok.

Now new SMTP account will be added.

Click next

Manage your profile. If there are multiple account the set one as default profile.

Click next.

In system parameter you can set the above properties like Account retry attempts , Max file size, Prohibited attachment etc.

Click next.

Now configuration has been completed.

You will Configuration report.

Now time to test and send the mail using database mail

Right click on database mail.

Click on Send Test E-Mail

Write to whom you want to send the mail.

Click on Send Test E-Mail

Open the gmail.

Mail body

Hope this article helps you. Thanks!!!