How To SQL Server

Monitoring SQL Server using Alerts

There are many after market tools available to monitor health of SQL Server. But with help of built-in tools you can actually create some pretty neat solution by yourself too. And one of the most handy tool is “Alerts” in SQL Server. With help of Alerts you can monitor SQL Server Events, Various Performance Conditions and oddly WMI events !! And what great thing about it is you can setup it to notify users and / or run specific job (stored in SQL Agent) to handle specific event.

It is very simple to setup Alerts using SQL Server Management Studio (SSMS). Since Alert notification uses Database Mail, you need to have a valid mail profile in order to be able to sent notifications. You can actually use your gmail account for testing. While back I did post about it.

First you need to create an operator which will receive an alerts via Email or  Net Send or Pager. You can create new operator in SSMS –> SQL Server Agent –> Operators. Right click on Operators and select “New Operator”.

It has two simple parts, in 1st General part you provide way you want to get notification … be it Email, Net Send Address or Pager. Furthermore you can setup pager schedule. For demo, I will be using my Gmail account as operator email.

How To SQL Server

Send emails using Database Mail … from SQL Server

Back in Pre SQL Server 2005 editions of SQL Server there was nice feature called SQL Mail which basically allowed users to send email from within database server. But that system has many dependencies and major one was requirement of Microsoft Exchange… thus usage was severely limited to enterprise customers who has resources to setup such a system. When MSFT released SQL Server 2005 they introduced new feature called Database Mail which replaced SQL Mail … and they removed all kind of external dependencies. Now all you had to do was to provide just SMTP server and you are all set !!

I always thought that I have blogged about process to setup Database mail but it appears that I never did so today I am going to write about step-by-step process to setup / configure database mail. Since we have moved on from 2005 to 2008 to 2008R2 to 2012 …. I am going to use SQL Server 2012 edition but process remains same for previous editions too.

Database Mail can be found under “Management” group in SQL Server Management Studio. To configure Database Mail right click on it and select “Configure Database Mail” which will start configuration wizard. It will show bunch of options like “Set up Database Mail”, “Manage”, “Change system parameters”. Since we have not setup this before we are going to select first option, “Setup Database Mail by performing following tasks”. And with that selected click next.

SQL Server Tips

Policy Based Management in SQL Server

This great feature was introduced in SQL Server 2008 and since then it has been considered as one of “standard” method to administer database proactively. As the name suggests, it allows DBAs to create various policies to enforce certain rules to single database, to group of databases or to whole server… for example default database backup location has to be some particular drive or every new database has to be in full recovery mode or each SP that are created and that will be created has to have prefix “USP_” etc.

It is very common issue of collaborative database development that developers like to keep follow their own naming convention then industry standards or MSFT recommendations for database objects. And they usually choose proper names for any objects they create, based on function / purpose of that object. But sometimes they like to choose name which could effect how SQL Server Query Execution planner behaves and you might see performance impact. For example in case of Stored Procedures, use of prefix SP in front of the stored procedure name. Now, I am not an expert in performance tuning but I know basic thing that it causes Query Execution planner to look into system stored procedure list at first because SP keyword is reserved for system stored procedures.

So instead of routinely monitoring any change made by devs and modifying / rolling back their change for such a silly name, it is far more convenient to have SQL Server watch for any thing like that and if such event occurs simply fail the execution and let developer know about this mistake. And this is exactly what I am going to demo here Smile

Policy Based Management (aka PBM) is made of 3 components. Facets, Conditions and Policies. And the order it works is, you use existing facet in condition and based on that condition you create a policy to be implemented on either single database or whole server.

1) Facets : They are kind of hard to explain by simple definition. Think of it as a class of .NET which exposes various properties. For example, database has different properties like Size, Name, Status, Owner, etc.… And you can use any of these properties to define condition. See this post for more detailed definition of Facets. By default there are number of pre-defined facets exist in SQL Server and there is no option to create user defined facet. A typical facet for Store Proc looks something like below,

SQL Server Tips

Database Snapshots … The Good, Bad and Ugly

In SQL Server 2005 MSFT introduced very new feature … Database Snapshots … which is basically a read-only view of database at the time when snapshot was taken. Just as a side note…even at the time this feature was introduced in SQL Server, other age old DBMSes like Oracle and DB2 were already offering similar feature in their systems. Database Snapshots can prove really useful if used wisely or can bite you back if used without any thoughts … hence … The Good, bad and ugly … Open-mouthed smile

Another note before starting … unfortunately Database Snapshots are kind of toys for rich only Broken heart … which means you have to have Enterprise edition to use this feature. But since I have developer edition … I am more like a lottery winner Winking smile … because all Enterprise features are available in Developer edition … with that being said … let’s prepare staging environment.

I have a test database with few sample tables that I have derived from AdventureWorks tables. I created this database into a temp HDD created using one of my favorite RamDisk software. Which gives me ability to blow up that drive without any issue at any time (which exactly what I will do at the end of this demo in the Ugly part Open-mouthed smile). But say if you don’t have enough RAM you can use USB thumb drive as a place to restore database backup. I restore database with following command …

SQL Server Tips TSQL


RAISERROR is one of the most efficient method to raise user created exception based on various situations. And TRY CATCH block is perhaps the most known method for exception handling in any programing language. But it is a bit tricky to use both of them together due to manner in which they transfer control to next command.

Following the examples show typical behavior in different cases.

TRY CATCH with Error Severity <= 10. As you know error severity <= 10 are actually informational messages and they don’t raise any error.

begin try
select ‘from Try block 1’
raiserror(‘Error is Raised’ ,10 — Severity <= 10 ,1 ,N’number’ ,5) with log
select ‘from Try block 2’
end try

begin catch
select ‘from catch block’
end catch


How To SQL Server TSQL

Importing Fixed Position file into SQL Server

For any DBMS system (I know that it sounds redundant) data can come into variety of forms and formats .. say CSV, XML, another DBMS, tab limited file or fixed position file or any proprietary format (as long as you have data providers for them Open-mouthed smile). And most of connected DBMS systems have different approaches to import those kind of formats into database. One of that format is fixed position files, in many cases these finds of files gets generated by applications that uses flat file databases to store data.

In SQL Server there are three main ways to import these fixed position files into database. Using Bulk Insert tsql command, using BCP utility or using SSIS. In today’s post I will be showing way to import file using BULK INSERT. May be later on if I am feeling graceful, I will write about SSIS and BCP methods as well Open-mouthed smile. Ok, so for purposes of test, I have created a fixed position file using SSIS. If you don’t know how it is done is SSIS see this previous post for HOW TO create fixed width file in SSIS.


Exporting data from SQL Server to Fixed Position Flat file

Just as usual, this post is kind of “Bi-product” of my attempt to create a demo for another post in which I need to use fixed position file. At first I tried to create a fixed position file using BCP. BCP has query out option to export query results into a file, but I was unable to produce the fileformat which uses fixed position. I actually even tried to use format file to generate required format but I ran into many issues so finally I thought that it is more important to get results than means to achieve it (at least it is true in this case Open-mouthed smile) and I fired up Visual Studio to use SSDT.

Actually it is very easy to generate a fixed position file using SSIS. All you have to create is a simple Data Flow task and then add a SQL Server as source and a flat file as destination into that task. Only thing to be sure is configuring Flat File destination settings.