Bytes and Bites of Internet
Posts tagged SQL Server
Monitoring SQL Server using Alerts
Jan 16th
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.
Send emails using Database Mail … from SQL Server
May 19th
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.
Policy Based Management in SQL Server
Feb 15th
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 
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 Saturday # 184
Feb 7th
One of my favorite event for which I always had to travel just too far .. either at NY or at MA for past few years … but this year CT is going to have it’s very own SQL Saturday. For anyone who have not heard about this great event then think of it as a code camp exclusively concentrated on SQL Server and it’s components like SSRS, SSIS and SSAS. In past two years I have been to SQL Saturdays of Boston twice and both of them were just great. Only thing that I really hated was drive of about 4 hours all the way to Waltham but once you are there you will realize that it is well worth of it 😀 …
But this year, SQL Saturday team has finally decided to have SQL Saturday event in CT at Quinnipiac University on March 2nd… which happen to be very close to my home. As always they have 5 tracks … DBA, Dev, BI, New Version of SQL Server (this time it is 2012) and Personal dev. No doubt all three … DBA, Dev and BI has really great session and speakers … and I am really not sure where I will go .. because all sessions seems great to me 🙁
Just as a side note they have $10 fee for lunch … or think it as an entry fee … but if you have been to any similar event then you know that it is worth more then that 😀 …
All details about time and session and registration can be found on SQL Saturday website.
That’s it for now … See you at the event …
Database Snapshots … The Good, Bad and Ugly
Oct 28th
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 … 
Another note before starting … unfortunately Database Snapshots are kind of toys for rich only 
… 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 
… 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 ). 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 …
RAISERROR with TRY … CATCH block
Sep 9th
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.
[sql]
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
[/sql]
Importing Fixed Position file into SQL Server
Sep 3rd
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 
). 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 . 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.