How to find out SQL Version and Edition

There are many ways to find out SQL Server Version and Edition. I am sharing some of the most common ones.

 

1. SQL Commands

SELECT @@VERSION

Another Script:

SELECT SERVERPROPERTY (‘Edition’) as Edition,
SERVERPROPERTY(‘ProductLevel’) as SQLServicePackLevel,
SERVERPROPERTY(‘ProductVersion’) as SQLVersion

 

 

2. Using GUI – SSMS ->Object Explorer

Open SSMS and connect to SQL Instance, Right click sql instance and select properties.

here in the properties windows you can find out the SQL Version and Edition along with a lot of other useful details as well.

 

 

You can also find out the SQL Version (along with SP) when you connect NEW QUERY (in SSMS)

In the status bar below in New Query window (it show the SQL Version and Product Level)

 

 

SQL Server Version List

this list is taken from https://sqlserverbuilds.blogspot.ae/. This is one of the best places to refer for latest SQL releases and version details.

 

Hope this was helpful. !!!

 

 

 

 

Advertisements

How to Configure Database Mail

Database Mail is an enterprise solution for sending e-mail messages from the SQL Server Database Engine. Using Database Mail, your database applications can send e-mail messages to users.

Configuring Database Mail using SSMS is the easiest way. Please see below the steps

 

Navigate to the Management folder of the appropriate server, right-click on Database Mail, and select Configure Database Mail

1

 

Click Next to pass the welcome screen

2

 

select the option Set up Database Mail by performing the following tasks and click Next

3

If database mail has not yet been enabled for this SQL Server instance, then you will see the screen shown. If it has been enabled, you won’t see the screen. Click Yes and proceed

 

FeatureNotAVailable

 

A profile is a collection of one or more SMTP accounts that can be used by SQL Server to send messages. In other words, when SQL Server wants to send a message, the message is sent to the profile, and then the profile is responsible for seeing that the e-mail is actually delivered. For fault tolerance, a profile can include more than one SMTP account.

To create a new profile, you must enter a profile name, an optional description, and then add and configure one or more SMTP accounts.

Enter Profile Name and Optional Description and click Add to create and configure SMTP Account.

4

 

Database Mail Account page.

5

To create/configure database mail account you need to fill in the details accordingly

Account Name

Optional Description

Outgoing Mail Server (SMTP). This specifies attributes of the SMTP Server that will be sending the e-mail, including these six options:

E-mail address – the e-mail account that has been set up for use with SQL Server’s database mail (for example, dbteam@company.com).

Display name – the display name of the above e-mail address that is shown as part of an e-mail.

Reply e-mail – the e-mail address used if someone should reply to an e-mail sent from the e-mail address entered above. Database Mail can’t respond to e-mails it receives, so you can either leave this option empty, or add your own e-mail address, just in case someone should respond to an e-mail received from SQL Server.

Server name – the name of the SMTP mail server.

Port number – the port number used by your organization’s SMTP server

This server requires a secure connection (SSL) – some SMTP servers require that SSL be turned on for additional security

6

There are three authentication modes available. In this tutorial, we have used Anonymous authentication (This option is rarely used because it allows anyone to access the SMTP server).  So, select accordingly.

Click OK to return to New Profile page

 

 

7

click on Next to reach the Manage Profile Security screen. I already had two profiles configured, that is why the below screen shows two more profiles.

Now you have to assign the profile as either public or private. A private profile is only usable by specific users or roles, while a public profile allows any user or role (with access to msdb) to send mail.

8

Click Next to move on to the Configure System Parameters screen.

This option in the Database Mail Configuration Wizard allows you to set the values of specific Database Mail parameters for the profile. Leave them to default and click Next

9

 

Review of the summary screen, click on Finish

10

 

If the Configuring… screen reports success, then Database Mail has been successfully set up

11

 

Now, In order to check whether the configured Database mail is working, right-click on the Database Mail folder in SSMS and select Send Test E-Mail,

12

 

In Database Mail Profile, select the profile just created and in To, type in your email address and click on Send Test E-Mail.

13

 

 

14

Database Mail has been configure.

 

 

Configure a SQL Server Agent Operator

configure a user to receive notifications about Microsoft SQL Server Agent jobs

When we configure a Maintenance Plan to send an e-mail, created with either the Maintenance Plan Wizard or the Maintenance Plan Designer, we can’t enter an e-mail address directly into the Maintenance Plan. Instead, we configure e-mails to be sent to an operator.

An operator is an alias for a specific person (such as yourself), or a group (such as a DBA mail group). This alias is more than just a name; it is actually a set of attributes that include the operator’s name, the operator’s contact information, and the operator’s availability schedule.

 

To create a new operator, open SSMS, navigate to the SQL Server instance you wish to configure, open up the SQL Server Agent folder, navigate to the Operators folder, right-click on it and select New Operator

15

 

New Operator screen has lots of options, we will focus only on the three that are most important.
Name – enter the name of the person or group who you want to receive notification e-mails.
Enabled – this option is selected by default, and you don’t want to change it, otherwise you won’t be able to receive any notices from SQL Server.
E-mail name – enter your e-mail address, or the group’s e-mail address here.

 

16

 

That’s it. All the other options are optional, and you can use them if you want to, we have left them to blank. click on OK

 

How To configure SQL Server Agent to use Database Mail

 

  • In Object Explorer, expand a SQL Server instance.
  • Right-click SQL Server Agent, and then click Properties.
  • Click Alert System.
  • Select Enable Mail Profile.
  • In the Mail system list, select Database Mail.
  • In the Mail profile list, select a mail profile for Database Mail.
  • Restart SQL Server Agent.

 

 I hope this was helpful.

 

 

Monitoring through SQL Server Event Alerts

Maintaining 99.9 % Availability of database servers is one of the main goals of a DBA.  To achieve this goal, an enterprise monitoring tool is required. There are various third party monitoring tools available in the market. Such as

1. Red-gate SQL Monitor

2. Idera – SQL Diagnostic Manager

3. Microsoft- SCOM

4. BMC etc.

However, all these Monitoring tools are very expensive.  And most of the organizations can’t afford them. So, configuring SQL Server Event Alerts in SQL Agent is a free and easiest way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

The script configures SQL Server Alerts for severity 16 to 25.

Severity:

1

 

 

 

— Note: You need to configure Database Mail and Operator before run this script. In this script i used the operator name DB Team. You need to change the operator name accordingly.

Script:

EventAlertsScript____________________________________________________________________________________________________

–Script to configure SQL Server Event Alerts in SQL Agent

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 017′,
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 017′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 018′,
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 018′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 019′,
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 019′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 020′,
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 020′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 021′,
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 021′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 022′,
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 022′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 023′,
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 023′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 024′,
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 024′, @operator_name=N’DB Team’, @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 025′,
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 025′, @operator_name=N’DB Team’, @notification_method = 7
GO

_________________________________________________________________________________________________________________________________

This is how basic level of notification alerts you can configure for free.