SQL Server Database Mail Configuration Step by Step

    SQL Server Database DB Mail Configuration Step by Step:

This Article will help Configuring SQL Server Database mail in SQL Server for Alerts and Notifications.

Database Mail is very Useful and powerful ability in SQL Server. Send email in SQL Server is one of the best ability for monitoring SQL Server

One can configure SQL Server Database Mail so that it sends you important notifications and alerts for example

1. Alerts for any high severity errors, corruptions etc..
2. Notifications Agent jobs Success or failure
3. You can also use Database Mail to email query results and reports

Configuring Database Mail and Enabling it on the SQL Server Agent for Alerts can be done by Read the rest

Prince2 Project Management Simplified Revision

PRINCE2: (Project Management IN Controled Environment)  Prince2 has Foundation & Practitioner Exams. Prince2 certification value is recognized everywhere in industry.

Project: A PROJECT is a temporary organization that is created for the purpose of delivering one or more business products according to an agreed Business Case.

Many Project Management Software are available in market to use with Prince2 methodologies like MS Project Server or MS Project Management Software etc… There are some free tools also available for project management methodologies like Prince2 

Below is some prince2 revision notes which is very useful. Even for a dba project management that the projects which come in, below Prince2 foundation revision is very helpful

Key features of PRINCE2:

•Focus on business justification
•Defined … Read the rest

In Memory Optimized Tables SQL 2014

    In Memory Optimized Tables SQL 2014

What are Memory Optimized Tables in SQL Server?
Memory Optimized tables are newly introduced in SQL Server and they are fully ACID compliant data storage structures, which stores the data mainly within the system memory(RAM), allowing users to access data very very quickly compared to the stored File system stored Table.

The transactional consistency is maintained in Memory Optimized Tables in SQL Server 2014 by:

In-Memory Row Versioning:
Memory Optimized tables maintains various versions of the same row dispersed over the in-memory data structure of the table which allows the reading and writing of the rows concurrently without transactional inconsistency issues. It is equivalent to SNAPSHOT ISOLATION LEVEL where row-versioning handles conflicts by throwing … Read the rest

SQL Server 2014 Compared to older versions

 

SQL Server 2014 Compared to older versions

 

 

Features Feature Description SQL Server 2014 SQL Server 2012 SQL Server 2008ÿR2 SQL Server 2008
Performance In-memory OLTP* Yes No No No
In-memory ColumnStore* Yes Yes No No
Buffer pool extension to SSD Yes No No No
Resource Governor Yes Yes Yes Yes
Availability AlwaysOn* Yes Yes No No
Enhanced virtualization support and live migration Yes Yes Yes No
Security Transparent data encryption* Yes Yes Yes Yes
Backup encryption support Yes No No No
Fine-grained auditing Yes Yes Yes Yes
Separation of duties Yes Yes No No
Cloud-readiness Backup to Microsoft Azure Yes Yes No No
Disaster recovery to Microsoft Azure Yes No No No
Optimized VM images in Microsoft
Read the rest

SQL Server 2014 released..New features

SQL Server 2014 released… Let’s discover what’s new in SQL Server 2014 now. There are versions in SQL Server out of which Express version is still free . Download SQL Server from Microsoft Site or your company volume licensing depending on your version you require.

New features will be listed here which are really fantastic….

Note: Database Compatibility Level of 90 compatibility level is not valid in SQL Server 2014.

I will also post some examples of how to use these features in coming days…
Database Engine Feature Enhancements  For more info click here for microsoft site
Memory-Optimized Tables: In-Memory OLTP is a memory-optimized database engine integrated into the SQL Server engine.

Buffer Pool Extension: The Buffer Pool Extension provides … Read the rest

CTE vs Temp Table vs Temp variables

Temp tables: they are just like any normal tables except that they are created in the TempDB database. They persist until dropped, or until the connection that created them disappears. They are visible in the procedure that created them and any procedures that it calls. Just like normal tables and have primary keys, constraints, indexes and column statistics which are kept for the table which makes query execution really fast in most cases.

Temp tables have space assigned to them in the TempDB database and are generally accessed only from memory, unless the server is under memory pressure or the amount of data in the table is large.

Table Variables: These tables behave very much like other variables in their … Read the rest

Temporary Variables vs Temporary Tables SQL Server 2008/2005

Myth: A table variable is a memory-only structure.
This is not True. A Temp table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables.

Note: If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).

Normally whether to go with Temp Variables or Temp Tables depends on:
•The number of rows that are inserted to the table.
•The number of recompilations the query is saved from.
•The type of queries and their dependency on indexes and statistics for performance.
Some other important advantages of temporary tables:
•Non-clustered … Read the rest

SSRS Queries for a DBA or Developers to monitor Reporting Server

The Script to Check Reports and Subcriptions with Sorted results per last Run Date

SELECT c.Name AS ReportName
, rs.ScheduleID AS JOB_NAME
, s.[Description]
, s.LastStatus
, s.LastRunTime
FROM ReportServer..[Catalog] c
JOIN ReportServer..Subscriptions s ON c.ItemID = s.Report_OID
jOIN ReportServer..ReportSchedule rs ON c.ItemID = rs.ReportID
AND rs.SubscriptionID = s.SubscriptionID
order by 5

To Check Latest Running reports with Full details of who is using reports sevices at what time and which report,…. etc..

SELECT [InstanceName]
,b.name
,[UserName]
,[RequestType]
,[Format]
,[Parameters]
,convert(varchar(10),[TimeStart],103)+' '+convert(varchar(8),[TimeStart],108) as TimeStart
,convert(varchar(10),[TimeEnd],103) +' '+convert(varchar(8),[TimeEnd],108) as TimeEnd
,[TimeDataRetrieval]
,[TimeProcessing]
,[TimeRendering]
,[Source]
,[Status]
,[ByteCount]
,[RowCount]
FROM [ReportServer].[dbo].[ExecutionLog] a
inner join [ReportServer].[dbo].[Catalog] b
on a.reportid=b.itemid
Read the rest

Parameter Sniffing in Stored Procedures in SQL Server 2008/2008 R2

Parameter Sniffing in Stored Procedures in SQL Server 2005/2008/2008 R2
Today let me cover one of a good topic on Parameter Sniffing in Stored Procedures:

Paramater Sniffing is not a unexpected behaviour when passing parameters into Store Procedure.


CREATE procedure [dbo].[Usp_Test_SP]
@Category as [nvarchar](60)=null
as
select Category ,Description,WorkOrder,Date Added from TestTable
where (sc.session_class_desc like @Category +'%' or @Category is null)

Say about SP causes a index seek + bookmark lookup then this compiled plan will be cached and reused for subsequent executions of the procedure.

But when sometime if rows returned are very high like 30% of Table rows then compiled plan may result in Full table scan and this is stored for future use.

and thus some executions will … Read the rest

Automating SFTP Downloads using WinScp Free Tool

Today’s article is about how to Automating SFTP Downloads using WinScp Free Tool:

This will be helpfull in many situations of which one is if you have your database hosted in Cloud by Hosted Services then for running reports etc. linking this database to another which is hosted locally, would mean you will need to automate SFTP downloads for that database and run restore script to Restore Database Automatically.

Procedure:

1. Install the WinScp Free Tool.
2. Access your FTP Site using SFTP(I used implicit FTP). If you cant access this then check your Firewall and see if this is blocked. Add exceptions to Firewall as needed.
3. Store a Session inside WinScp with Login and password credentials for Automating … Read the rest