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) ...


What is a Log Shipping?

High Availability is a hot topic today and need for a business critical applications, as a DBA we always try to cope up with such situation where High Availability and Continuity comes into picture their are technologies that you can use for; Log Shipping is one of them.
Have you ever had a chance to set up Log Shipping? What are the benefits of using Log Shipping over Clustering or Replication? How ever it *depends* upon your environment and requirement at given time. Refer below link for Log Shipping...

Log Shipping Article


What is Log Shipping?

What is Log Shipping?
Log Shipping is one of the solutions for High Availability for business critical application. Log Shipping is first introduced in SQL Server 7.0. Log Shipping; as name defines does the shipping of Transaction Log to another server; it does exact replica of the database; it does backup of Transaction Log and restores it on a Secondary /Stand by Server which keeps database on secondary server in sync with the database on Primary Server. In case of failure work loss happens only for the time being copy of T-Log backup and restoring back to Secondary/Stand by Server. Stand by / Secondary server which can act as a primary server in case of failure. SQL Server DBA has to perform some manual tasks to bring it online for business.

Why to do Log Shipping?
There are other options like Clustering and Replication for High Availability of Business Critical Application, so why do we go for Log Shipping?
Well, there are reasons for choosing Log Shipping against Cluster and Replications:

  1. Setting up: It is very easy to create a Log Shipping, only thing you need to identify is you must have two servers with SQL Server Enterprise Edition and a database(s) which is critical to your business environment.

  2. Manageability: It is easy to manage Log Shipping comparing to Replication and Cluster Server, generally Log Shipping works well and you don't feel panic with its monitoring or maintenance work.

  3. Act as a Reporting Server: Stand by or Secondary Server can act as a Reporting Server in your organization, your database which is being log shipped can be used for Read Only queries or I would rather say it can act as a Reporting Server. The thing need to be consider here is it will be unavailable at the time of Restoration of Transaction Log e.g. if your Transaction Log backup takes 10 minutes of Restoration Time and you does Log Shipping every 60 minutes then your Reporting/Secondary/Stand by Server will be un-available for 10 minutes.

  4. Can use multiple database(s)/server(s): You can use multiple server(s) as a Stand by or Secondary Server for different purpose, e.g. you can create two Stand by server(s) one for Reporting and another for High Availability

  5. Cheaper then cluster: If you go for a Cluster Server you will have to meet software and Hardware requirement; I mean you need identical hardware to setup clustering where as in case of Log Shipping you don't need to have exactly the same set of hardware for Primary and Secondary or Stand by server.

  6. Best solution for physical dispersed location: Data Center: In your environment you are having a cluster server configured at your Data Center for high availability so that if in case of any failure or damaged you can recover your data back, imagine what if your data center itself got damaged!!!!!! In this type of scenario Log Shipping does good job you can configure Log Shipping to Dr Server which is at different physically location and if your local Data Center / Server got damaged you may still recover your data.
    You can do this thing using Replication too; in case of replication again you have to select Snapshot/Transactional/Snapshot Replication, now as you may use Log Shipping you would rather go for Snapshot Replication, you have to monitoring it regularly whether it is running or not!!! Where as with Log Shipping it generally runs smoothly and even if it caught an error it will tell you exact where it breaks so fixing a Log Shipping is not difficult.

How to do Log Shipping?

Here, I've tried describing Prerequisites and steps for setting up Log Shipping.


  1. Database must be in Full or Bulk-Logged Recovery Model.
    It is required that Database has to be in Full or Bulk-Logged Recovery Model,
    One can perform T-Log backup only when Database Recovery Model is set to Full or Bulk-Logged, here is a brief on recovery model.

Recovery Model Benefit Work Loss Type of Recovery 
Simple Keeps T-Log in small size, can perform bulk copy operation If needed you have to restore Full backup or Differential backup  Can done up to the last backup time 
Bulk-Logged Minimal log space occupation, can perform bulk operationIf the log is damaged or bulk operation is done after last T-Log backup Can done up to the last backup time 
Full No work lost due to damaged data file, Point-In-Time recoveryNormally none, only if you lost your most recent T-Log backupPoint-In-Time 

  1. Windows Account used to setup Log Shipping must have SQL Server System Admin rights on both servers.
    It will not break Log Shipping if you the password of Account has changed, because it needs to change at the same time on both the servers else Log Shipping will fails.

  2. Create a share on Primary Server and Secondary Server with change and write permissions for Windows Account User which is used for SQL Server Agent.

  3. You may Restore Full Database on Secondary Server manually or it can be done via Log Shipping installation itself at the time of setup process (Ensure this database on Secondary Server must be restored with NORECOVERY or STANDBY option).
    It is required to Restore Database with NORECOVERT | STANDBY because if the database restored in recovered/write enabled mode you can not apply another T-Log.

Optional Component : Monitoring / Witness Server 

Identify your Monitor Server which will look after for your Primary Server and Secondary Server's Activity.
It is better to use separate server which monitors the activity of Primary and Secondary Server so that you can get the alert even if server itself won't works.

Primary Server 

  1. Create a Share with read/write permission for domain user which we'll be using to startup SQLServerAgent

  1. Create a Full Database backup for

  2. Backed up T-Log on the folder you have created earlier to store the log backup
    You can create a maintenance plan to take a T-Log backup for regular interval and then add it as a Step in job to automate this process

  1. Please ensure that the duration must enough to copy the log backup file to Secondary Server.
    Suppose we are taking T-Log backup each 1/2 hour and if the backup file is big enough which takes more then 1/2 to copy over the network share, it will again creates a new T-Log backup which will then occupies recourse unnecessarily.

  2. Copy T-Log backup file to Secondary Server's shared folder

  3. Delete the T-Log backup file after it copied to the Secondary Server's shared folder.

  1. Ensure you have selected Allow Database to assume primary role option so that if in future requirement of role reversal can be possible easily.
    If in case we need to change the role of primary server or secondary server to act as Primary or Secondary this option should be used while setting up Log Shipping.

  2. Ensure that you have set up proper threshold values for Destination Database for Out of Sync, Load Time Delay, File Retention Period and History Retention Period option.
    These options are self explanatory: Out of Sync=used by witness server to send alert if the specified time has been exceeded between Last T-Log backup on primary and secondary server, Load Time Delay= Destination Database waits before it restores the T-Log, File Retention Period= Time elapse till file gets deleted and History Retention Period= Time till we retain History details

Secondary Server 

  1. Check if the Database is already exists on Secondary Server, if it is already their drop it.

  1. Restore a Full Database Backup for LogShDB

  1. Restore a T-Log for LogShDB on the secondary server which is copied on the shared folder of Secondary Server;

  1. Ensure you have given STANDBY or NORECOVERY option while restoring T-Log backup for LogShDB.

You may find log shipping is a very use full yet simple to configure, use and manage, it is cheaper and easily manageable solution for High Availability of your data. It is a good solution over clustering (because it is costly; requires identical hardware to setup cluster) and easily manageable, easy troubleshooting against replication as it clearly says where it breaks. Generally Log Shipping works very smooth once it is configured. Even if you have to re-configure it from the scratch it is easy job to do.



Logging and Data Storage Algorithms in MS SQL Server

Ever wonder how SQL Server Logs data in T-Log and Data Files? What algorithm it uses!!! What is ARIES (Algorithm for Recovery and Isolation Exploiting Semantics), refer Microsoft Knowledge Base Article http://support.microsoft.com/kb/230785 which gives the complete details how it works!! With the detail explanation on each steps and terms.


Do Win a Lottery - Money from Microsoft

Now a days I have seen too many emails in my inbox fwd by friends and unknown persons claiming that THEY WON $$$$$ BECAUSE THEY FWD THIS TO THIS MANY PEOPLE, SOME ARE ASKING FOR ADVANCE MONEY TO GET YOU MORE MONEY and this type of emails are fake / fraud emails don't ever forward them and do not believe in this kind of emails. This is now confirmed by Microsoft Officials so ignore this type of emails and don't ever believe on it. Refer below link for more


Hemantgiri S. Goswami


Download free Database Tools

At times we need to analyze the Size of the Database, or have to search the Object or Content in the Database, we always query system tables or create a script for self and some times we uses sp_depends,Query Analyzer and EM or SSMS but it has some limitations.

To reduce this effort we often try some third party softwares and we know that they comes at cost. Mohamed Bouarroudj of  SQLDBTOOL has 3 software to help us out and yes they are FREE!!! There are 3 tools he has developed SQLDBDiff, SQLDBSearch and SQLDBSize.

1. SQLDBDiff:  compares the schema and the data of SQL Server databases. SQLDBDiff supports SQL Server 2008, 2005 and 2000. Download SQLDBDiff from here 

2. SQLDBSearch:  find any object by its name or content in one or many SQL Server databases. SQLDBSearch supports SQL Server 2005 and 2000. Download SQLDBSearch from here

3. SQLDBSize: display size used or reserved by tables, indexes, databases, transaction logs and physical files graphically. Download SQLDBSize from here