SQL Server security best practice

Security! This is the word comes in mind of every concerned person when it come...

Change the Collation Settings in MS SQL Server

This post will show you how to change the collation settings in MS SQL Server for specific database...

Resolve collation conflict

In this post I will show you how you can resolve collation conflict error...

Book: SQL Server 2008 High Availability

In this book I have tried to cover every single piece of information that might requires for installing and configuring SQL Server HA option like Clustering, Replication, Log Shipping and Database Mirroring...

Why to recompile Stored Procedure

Generally, we create views and stored procedures (proc here after) ...


Community Techday | Surat | 28th November 2010

We are very pleased to announce Community Techday at Surat on 28th November 2010. We try to bring you the new products 

SQL Server is very popular and widely used RDBMS system in the SME and large Enterprises segment around the world for storing and managing their business critical data. Numerous applications are developed today with n-tier technologies, but they can be never be without database behind it. Recently Microsoft has announced the new version of SQL Server - SQL Server 2008 R2.

SharePoint 2010 is the business collaboration platform for enterprise and the web, Sharepoint makes it easier for people to work together. One can setup websites and manage their documents with few mouse clicks!!!

Windows 7 is a cool new version of Microsoft Windows for personal computers, including home and business desktops and laptops.

During this event we will learn the new features that are introduced with the new version of SQL Server 2008 R2, Sharepoint 2010 and Windows 7.  I would request you to kindly mark your calendar for Community Techday, 28th November 2010 in Surat.

You will have to visit the home page or the event page of the Surat User Group and get yourself register for free!!
Note: Feel free to share this detail to your friends and colleagues, Community Techday is the free event you don’t have to pay any fees to attend this event.


What is new in SQL Server - Denali

Dear Readers, I have a great news to share with you all!! 

A much awaited future version of SQL Server is announced - "Denali". This version has some new salient features like:

  • SQL Server AlwaysOn -  A new feature that will provide maximum uptime with simplify HA deployment, this feature will also supports new availability group option to improve availability of mission critical databases by supporting multiple secondary servers.
  • Column-Based Query Accelerator -  Increase query performance by ~10x 
  • IT Administration - Enhancements in server roles to manage separation of duties, and auditing will be available for all the editions.
  • Beyond Relational - FileTable, 2D Spatial support 
  • Juneau - A tool for Developers : Developers can easily work using new tool "Juneau", this will help developers to developing databases, BI and web solutions. It also provides environment for Declarative Database Definition, Dynamically Editing Existing Database
  • BI Enhancement: Drag and Drop interface
  • Analytic Enhancement: Analytics performance can be enhanced from the desktop to data center through in-memory column store technology
  • Data Integration Management: Denali introduces two component - Data Quality Services for knowledge driven data cleansing and Impact Analysis and lineage which will ensure that the right data is delivered to the right users and the right time.
You may find more information at http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx and if you like to review the new version of SQL Server you may, download Denali at - http://go.microsoft.com/fwlink/?LinkID=205500&clcid=0x409


How to Upgrade SQL Server

Couple of days back I have wrote an article on common issues we face while upgrading, and two methods to follow while upgrading SQL Server, this article also gives you some basic steps we need to follow post upgrades.  Here is an excerpts of the article:
Upgrading the SQL Server version can have a major impact the applications using the databses. For example, from SQL Server 2005 onwards you are required to use  col IS NULL instead of Col = NULL and so any applications using the later will break. Using  Upgrade Advisor for SQL Server is a good start for any SQL Server however we still need to address the entire  process of upgrading SQL Server to a new version. So What is the process  we should follow while upgrading SQL Server? Read full story....

-- Hemantgiri S. Goswami | www.sql-server-citation.com


Download MS SQL Server 2008 R2 Management Pack

Recently Microsoft has released MS SQL Server 2008 R2 Management Pack, it can monitor the Microsoft SQL Server components such as database engine, databases and SQL Server agents. MS SQL Server 2008 R2 Management Pack has few enhancements in this release including:

  • Simplified and streamlined customer configuration and deployment
  • Lower cost of operation due to improved health model and reduced false alarms
  • Meets low-privilege constraints needed for financial and medical industries
  • New rules, monitors, and knowledge improvments
  • Easy deployment
  • Discover and monitor SQL Server 2005, SQL Server 2008 and SQL Server 2008R2

You can download the SQL Server Management Pack here - Link

Hemantgiri S. Goswami |  www.sql-server-citation.com


Download SQL Server Developers Training Kit for free

There are tons of useful resources available for SQL Server but the link I am going to share with all of you today is really the greatest amongst - it's a SQL Server Developers Training Kit. Yes, MS has made the updated version available for we all and the best part is - it's free.

The key benefits are:
  • How to build applications with unique and new features available in SQL Server 2008 and SQL Server 2008 R2
  • It contains #55 videos, # 32 presentations, #39 demos and #24 hands-on-labs
  • Content for SQL Server beginner developers
Doesn't it seems a bundle full of great resource on SQL Server 2008? Here is the complete list of what it includes if you still wants to check before you download it from here.

Hemantgiri S. Goswami


Download SQL Server 2008 R2 Best Practices Analyzer

Recencly Microsoft has released the Best Practices Analyzer (BPA) for SQL Server 2008 R2. BPA is a tool which helps Administrators to audit there local and remote SQL Server instances sagainst system events, logins, permissions and metadata for recommendation if it doesn't follow MS best practices for SQL Server.

But, you have to have pre-requsite installed on your system before you install BPA for SQL Server 2008 R2, they are:

Note: There are some issues reported related to installing, you may find the work around by Adam W. Saxton and in the Insturction section.


Download free eBook on SQL Server 2008 R2

In this quarter Microsoft Press has gift a free eBook on "Introduction to SQL Server 2008 R2", a book written by Microsoft SQL Server MVP Ross Mistry and Stacia Misner.

This book has 10 Chapters and 216 pages which covers
  • Enhancements in SQL Server 2008 R2
  • Multi Server Administration
  • Data Tier Application
  • HA and Virtualization Enhancements
  • Server Consolidation and Monitoring 
  • Data Warehousing
  • Master Data Services
  • Event Processing with StreamInsigh
  • SSRS Enhancements
  • Self-Service Analysis using PowerPivot
Isn't this list looking great? I just have downloaded this book in PDF format, alternatively you can download this book in XPS format as well.


Virtualization – a way to reduce IT Costs!

A month ago I and my friend were discussing IT budget, and it becomes very interesting when we started discussing virtualization. Let me brief you the case: with the growing business usage of IT infrastructure increases and that increases the usage of energy and space. Apart from this, there is another side; are those resources getting utilized at optimum level? Well, at some cases yes but in most of the cases it doesn't - source.
One would think if this really requires? I would say, yes, it is required. Let's take an example here; company Ax Inc is managing its 200 servers at its datacenter at Bangalore. Let's say a single server which utilized at 60% consumes 350 watts, now let us calculate -:
Up to 1002.90290
101 to 2003.60360
201 to 3004.20420
301 to 4004.45222.5
Above 4014.70-- NA --
Bill generated for single server
                                   (Figure 1)
Further, calculating this amount for 200 servers would make 258500/- INR per month (US$ 5609.45 per month, as of 23-Feb-2010 according to CurrenciesDirect). If we also consider the bill for cooling systems and miscellaneous expenses it would be much more. In addition, add licensing costs, resources which manage and maintain this IT Infrastructure will add some extra burden.

Is there a solution for this? Yes, there is a solution for this – Virtualization. Virtualization is great technology which helps us to utilize our IT infrastructure at the max and lower down the costs. You must be wondering how this can be possible, and if it is; how to implement this solution!!!  As I said above, our discussion was turned very interesting – the solutions are to implement "Virtualization". Virtualization – a methodology of dividing the resources of a computer into multiple execution environments, by applying one or more concepts or technologies such as hardware and software partitioning , time-sharing, partial or complete machine simulation, emulation, quality of services, and many other.
There are bunch of benefits that are attached to with implementing Virtualization, and related product i.e. lowering power consumption, ease of deployment, maintenance and updates, and consolidation. Let me brief you about each.

Lowering power consumption: As I shown you in the above calculation in figure 1, 200 servers would generate whooping electricity bill - 31,02,000/- (2585000*12) annually. This is just an example and that to without considering cooling system.

Now, if you have hardware with Hyper-V you will be able to have your multiple servers on it. You can even host with Linux. And, naturally with lower numbers of servers your electricity bill goes down significantly.

Ease of Deployment: With the use of Microsoft Virtual Server Manager, System Center Virtual Machine Manager you will be able to optimized your physical and virtual resources.
  • Centralized virtual machine deployment and management
  • Intelligent placement of virtual machines
  • Integrated performance and resource optimization
  • Fast physical to virtual and virtual to virtual conversion
  • Leverage and extend existing storage infrastructure
  • Scriptable using PowerShell
  • Live migration
  • Hot add/remove of VHDs, pass-through disks
  • Server consolidation
Maintenance and Updates: The real beauty is that it makes administrator's life easy. With the use of System Center Configuration Manager and Operation Manager you will be able to easily do our maintenance and updates easily.
  • Comprehensive application and service level monitoring
  • Update management
  • Software distribution
  • Operating system deployment
  • Ease of Backup
Conclusion: Virtualization helps a lot to save energy by consuming less power, save resources, and make IT administrator's life easy by server consolidation, deployment of patches and updates and even migration. As I have said in my previous post about embedding security to your product or piece of code, you may have the same tight security mechanism implemented with Virtualization- refer the document. Microsoft's own websites like MSDN and TechNet which has 1~3 million hits a day runs 100% on Hyper – V.
Cumulatively all this effort saves a lots of money by reducing IT costs, this makes real sense to me because it makes my life easy and as IT cost is down my client is happy with the solution.

Further reading:


Virtual Labs:




Embed security and secure your data

Security!! A word – that everybody want, whatever it may for - a life, money or a job. In an IT world this term is often used, and asked by the client, the customer or the end user because it is closely attached to the information they are providing or storing or exchanging with each other. It can create a chaos if an ounce of the information is leaked or spread publicly; refer the case about classified US Military info available, reported in July-2007 by Network World.  

So, what we really have to care about that every piece of work that we do should be secure - by default, and this applies to every technology. That means whatever we work on, we should follow and implement the security policies to build the robust and secure product. Recently, I was researching about security and I found a link about Microsoft Security Development Lifecycle (SDL). It is since 2004 that Microsoft has made SDL mandatory to follow for its entire product range.  I would advise you to follow the same when you make a product for a business environment, communication software such as messenger or dealing with the sensitive information. 

Since 2004, most of the product from Microsoft has built-in features to tighten security. This applies to SQL server as well; you can see that SQL Server 2005 has great security features i.e. you can enforce password policy on your database server to ensure that the passwords that your users are crating are strong. Apart from this   now it is not necessary to assign system administrator rights to run profile, the only permission user requires is ALTER TRACE to run profiler. You may refer my article on how to secure SQL server for more pointers.

Microsoft publish security bulletin every month which will give advice you if you required installing any patch on your system. And to make this procedure easy for Windows operating systems and Office product Microsoft is offering Security Compliance Management Toolkit Series. Also to keep your system free from Viruses or malware Microsoft Assessment and Planning (MAP) toolkit is useful.

So, all in all we all should be more cautious when we are dealing with sensitive data and should improve by following SDL.


Common Mistakes in SQL Server - Part 5

Last to Last week we discussed how Null Values can cause a trouble in Common Mistakes in SQL Server – Part 4. This I will discuss about Auto Growth feature of SQL Server, and how it can impacts us.

What is Auto Growth?

Auto Growth is a feature that allowed database files (primary, secondary and log) to expand when database file becomes full - without manual intervention.

Auto Growth feature is really useful when we do not want to increase the size of database files manually. There are two ways you can set auto growth – using SQL Server Management Studio (SSMS here after) and T-SQL. Auto Growth can be configured – In Percent and In Megabytes.

How Auto Growth can impact us?
Now that we know what Auto Growth feature in SQL Server is; I’ll describe how this feature can impact us badly if we do not  configured this option carefully . Below is the table that will give you an idea what would happen if database file reaches to its threshold value and it needs to expand!!
See the calculation below when Auto Growth option is configured with in Percentage:

Configured to grow "In Percentage"

File Size (before growth)
Growth - 10%
File Size (after growth)
1st time
2nd time
3rd time
4th time
5th time
6th time
7th time
8th time
9th time
10th time

You will notice that the database growth is exponential when it is configured with Percentage i.e. 10%.  This is because this is calculated in cumulative manner; the value I have used here is 20 times lower than what we used to work on production systems generally.  If we set Auto Growth in Percentage it will occupy our HD space unnecessarily and creates fragmentation. Moreover when database files are expanding you will notice high volume in CPU spikes and I/O cycle.   
What is the best practice for Auto Growth option?
Best practice is to configure Auto Growth option in static value. See the sample calculation below:

File Size (before growth) MB
Growth - 500MB
File Size (after growth) MB
1st Time
2nd Time
3rd Time
4th Time
5th Time
6th Time
7th Time
8th Time
9th Time
10th Time

Database grows in a controlled manner when Auto Growth is configured with the static/fix value in comparison to Percentage i.e. 10%.  The value I have used here is 20 times lower than what we used to work on production systems generally.   

It is true that now a day’s storage costs are lower down for desktops or home systems but it is really very costly when it comes to servers and data centers and this scenario will just add overhead to costing for storage solutions. So, the best bet is to configure Auto Growth with static value.
I would advise to configure Auto Growth option to static value only.