Archival Process in Database.

As a DBA or developer just because you do not think the data is needed may not mean that it can just be deleted. Many organizations have legal requirements to maintain the data.  Such kind of data we cannot directly delete from table. 

Here, in such a case Archival Process needed.

While Archiving data, some Preliminary items need to be Addressed.

• Data Analysis - Need to review the data in your application to determine the key tables and dependencies between the tables when archiving.  Be sure to also capture the distribution of data on a monthly, quarterly or yearly basis to understand how much data can be archived.

• Understand the Data Usage - Meet with the users to understand how they use the potentially archived data and if it is archived how the applications will need to change.  If you are not responsible for the application development, ensure you meet with the development team to ensure the changes are feasible.

• Justification for archiving the data is essential when taking on this Project.

• Automate the Data Archiving Process - Setup rules/SQL Job with the users to be able to automate the archive process so it occurs regularly on a weekly, monthly or quarterly basis as per the requirement.

Example :

According to need I have to keep only recent 6 months data in table. Other data should be archived.

Following Stored Procedure will use to achieve the above requirement.

CREATE PROCEDURE [dbo].[SP_ArchiveAll]
AS
BEGIN
DECLARE @Error INT

BEGIN TRANSACTION

  INSERT INTO FC_FLT_EntryArchive
   -- this is archival table
  (
    Site_ID,Status_Code,Active_Code,Priority_ID,ORS_ID,Lang_Code,NumPages,Comments,Created_On
  )
  SELECT Site_ID,Status_Code,Active_Code,Priority_ID,ORS_ID,Lang_Code,NumPages,Comments,Created_On
  FROM FC_FLT_Entry
  -- give the appropriete condition in where clause as per need
  WHERE YEAR(Created_On) <=  YEAR(DATEADD(MONTH, - 6, GETDATE())) AND
              MONTH(Created_On) <  MONTH(DATEADD(MONTH, - 6,GETDATE()))

  -- delete data from main table
  DELETE FROM FC_FLT_Entry
  WHERE YEAR(Created_On)<=YEAR(DATEADD(MONTH, - 6,GETDATE())) AND
        MONTH(Created_On)< MONTH(DATEADD(MONTH, - 6,GETDATE()))     
 
  -- keep the log to archival process
  INSERT INTO uhc_Archive_log(LogDate, Action,TableName, RecCount)
         VALUES(GETDATE(),'Delete','FC_FLT_Entry', @@rowcount)   
 
  -- following code capture if any error occures
  SET @Error = @@ERROR
  IF @Error <> 0 
    BEGIN
     GOTO LogError
    END
 
  COMMIT TRANSACTION
  GOTO ProcEnd 

  LogError:
  ROLLBACK TRANSACTION
 
  DECLARE @ErrMsg VARCHAR(500)
  SET @ErrMsg = 'Undefined Error'
  SELECT @ErrMsg = [description] FROM MASTER.dbo.sysmessages WHERE error = @Error
  INSERT INTO uhc_Archive_error_log (LogDate,Source,ErrMsg)
         VALUES (GETDATE(),'SP_ArchiveAll',@ErrMsg)
 
  ProcEnd:
END

Some Query Optimization Techniques

Performance of Query is most essential thing in project.

To increase the performance, complicated queries  need to be optimized.

Here, i posted some techniques to optimize complicated queries. Try with this techniques, it will definitely improves the performance.

Use 'With no lock'  if the query is much complicated. Used to avoid Dead Lock situations.

Use '<= '  and '>='  instead of 'Between'  and 'And'.

Make sure there is a need for any String Manipulation Functions in the SP, such as Ltrim, Rtrim, Substring, Cast, Convert etc…

Mostly try to eliminate Dynamic Queries.

Try to Merge 'If' conditions using 'And/Or',  if there are more 'If'  conditions in the SP.

In the 'Where'  clause please use the least number of record output condition at the top.

Try to not use any 'Order By' if not needed.

Do not use any 'sub queries'  instead use 'Joins'.

Do not use 'In/Not In'  functions instead use 'Or/And'.

Check whether all the fields in the select statement are being used in the report if not please remove them from SP.

Please check the Execution Plan after making the changes to find out the difference between the usage.

To Enable The Service To Envoked From Remote Machine.

If we would like to invoke the service from other machine, by default, it would display the message "The test form is only available for requests from the local machine."

To enable the Service to be invoked from remote machine, we need to add the following settings to the Web.Config file of the Web Service Application.

<system.web>
     <webServices>
           <protocols>
                <add name="HttpGet"/>
                <add name="HttpPost"/>  
           </protocols>
     </webServices>
</system.web>

Credit : Renugopal A.P.

Difference Between Two Dates... But Excluding Satureday and Sunday.

Hi Friendz,

When I was newbie in SQL, issue came in report from one project. Client want to calculate the difference between 2 dates (in hours) but excluding Saturday and Sunday. 

The default DateDiff function in SQL Server is easy to use but it gives clear difference. 

Syntax :

Datediff(datepart , start_date , end_date)

Way to use DateDiff function :

select DateDiff(minute, '2009-12-31 23:59:59.9999999', '2010-01-01 00:00:00.0000000')

For more details visit :

http://msdn.microsoft.com/en-us/library/ms189794.aspx

But for the above problem is not useful.

Definitely we need to create function which fulfills our requirement.

So here you go..

Create the following function in Database.

Create Function dbo.fn_GetHoursExcludeSatSun(@FromDate DateTime,@ToDate DateTime)
Returns bigint
As
Begin

 Declare @ldt   datetime;   
 Declare @dtdiff bigint;
 Declare @cnt  bigint; 
 Declare @Thrs  bigint; 
 Declare @Thrs1  bigint; 
 Declare @frmhrs  bigint;
 Declare @tohrs  bigint;
 Declare @tmpFrmDate   datetime;  

  if DateName(dw,@FromDate) ='Saturday' or DATENAME(dw,@FromDate) ='Sunday'
  begin
      Set @frmhrs = 0
  end
  else
   begin
     Set @frmhrs = DateDiff(hh,convert(datetime,convert(varchar(10),@FromDate,101)),@FromDate)
   end

   if DateName(dw,@TODate) ='Saturday' or DATENAME(dw,@TODate) ='Sunday'
   begin
     Set @tohrs = 0
   end
   else
     begin
       Set @tohrs = DateDiff(hh,convert(datetime,convert(varchar(10),@TODate,101)),@TODate)
     end

    set @Thrs=0
    set @dtdiff=0
    set @cnt=1
 

    select @dtdiff=datediff(dd,convert(datetime,@FromDate),convert(datetime,@ToDate))
 

   set @dtdiff=@dtdiff
   set @ldt=convert(datetime,@FromDate)
   set @tmpFrmDate=convert(datetime,@FromDate)
 

        While (@cnt<=@dtdiff)
        Begin
          if DateName(dw,@ldt) ='Saturday' or DATENAME(dw,@ldt) ='Sunday'
          begin
               set @Thrs1=1
          end
        else
        begin
              set @Thrs = @Thrs + datediff(hh,@ldt,DATEADD(dd,@cnt,@FromDate))
       end
 

       select @ldt=DATEADD(dd,@cnt,@FromDate)  
      set @cnt=@cnt+1
   end 
    set @Thrs = @Thrs - @frmhrs
    set @Thrs = @Thrs + @tohrs
  Return   @Thrs
End

How to use this function :

select dbo.fn_GetHoursExcludeSatSun(First_Date,Second_Date) 

It is possible to use the column names (with datatype 'datetime') instead of First_Date, Second_Date.

SQL Server Replication - Key Concept

Here are the some Key Concept about SQL Server Replication what I learn in last few dez...

Definition of Replication : 

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Key Roles in SQL Server Replication :

i.     Publisher

ii.    Distributor

iii.   Subscriber

Publisher :

              Publisher is the sever/database that sends the data to another server/database. In simple words, is the source of data.

Distributor :

             Distributor is the server that manages the flow of data through the replication system. It contains the Distribution Database also.

Subscriber :

             Subscriber is the server/database that receives the data from source i.e. from Publisher.

Types of Replication :

1.   Snapshot Replication

2.  Transactional Replication

3.  Merge Replication

Now we will go in details with each type of replication.

Snapshot Replication :

            Snapshot Replication is suited for replicating the data that changes infrequently and the size of replicated data is not very large. Data size must be consider first. With this type of Replication, all replicated data (generally called as Replica) will be copied from Publisher database to the Subscriber database on a periodic basis. It does not monitor to the updates of data on publisher database.

Transactional Replication :

            Transactional Replication is typically used when data changes frequently or the size of replicated data is high. With the Transactional Replication, SQL server captures all changes that were made in Article (tables) and stores Insert, Update, Delete statements in the distribution database. Then the changes are send to Subscriber from the Distribution database and applied in the same order. This type of replication gives the high throughput, scalability as well as availability.

Merge Replication :

             Merge replication is somehow difficult. It makes possible autonomous changes to replicate data on Publisher and on the Subscriber. Merge Replication is primarily designed for Mobile Applications or distributed server applications that have possible data conflicts.

Replication Agents :

            Replication Agents are manifested through the SQL Server Agent Jobs, but they are simply executable files.
We will concentrate on the agents which Transactional replication use. Following are the snapshot agents used for Transactional Replication.
1.  Snapshot Agent
2.  Log Reader Agent
3.  Distribution Agents

Snapshot Agent :

             Snapshot agent is the replication agent that creates the snapshot files, stores the snapshot on Distributor and keep the track of information about status in the Distribution database. This agent can be administrated by using SQL Server Enterprise Manager.

Log Reader Agent :

             The Log Reader Agent is responsible for moving transactions marked for replication from the Transaction Log on Publisher to the Distribution database.

Distribution Agent :

              This agent is responsible for moving Snapshot Jobs from teh Distribution database to the Subscribers and moves all the transactions waiting to be distributed to subscriber.

Some Necessary Conditions :

• Only members of the Sysadmin server role can setup and configure replication
• SQLServerAgent  Service (MSSQLServer Service) need to be start first.
• Should allocate the sufficient disk space in snapshot folder.
• Should allocate the sufficient disk space for Distribution database. 

Example : Transactional Replication

Following is process to setup and configure the Transactional Replication.

1.  Register new remote server in which will be replicated.

2.  Enter the name of remote server and specify Remote Login Mapping.

3.  You will receive the following, if all is correct.

 4.  Select Tools Menu -->; Replication -->; Configure Publishing and Subscribers

 

5.  This will launch the Configure Publishing and Distribution Wizard

6.  We have to create the distributor first. Select the Next button to create the Distributor.

7.  Now, you can choose the default settings or set the distribution database name and location, enable other Publishers, or set another settings. Click the Next button.

8.  Click the Finish button.

9.  Now, SQL Server creates the distribution database, enables publishing, and setting up the distributor

10. Because we installed CHIGRIK_A_U as Distributor, the Replication monitor has been added to the console tree on the CHIGRIK_A_U server.

11. Now, we are ready to start creating publications and articles. Select Tools -->; Replication --> Create and Manage Publications

12. You will see Create and Manage Publications dialog box:

13. Choose pubs database and click the Create Publication button.

14. This will launch the Create Publication wizard.         

15. Select Transactional publication and click Next button.

16.  You can allow immediate-updating subscriptions on this step. If you choose this option then all changes will be applied at the Subscriber and Publisher simultaneously.

17. Choose Subscriber types on this step. If all subscribers are Microsoft SQL Server subscribers, choose the first option.

18.  Select tables or stored procedures to publish and click Next button.

19.  Choose publication name and description on this step.

20.  You can define data filters or set another options on this step.

21.  Click Finish button to create the publication.

22. If publication was created successfully, you will receive the below message.

23.  Create new push subscription now.

24. Click Next button.

25.  Choose subscribers. Click Next button.

26.  Choose destination database. It's pubs database in this example.

27.  If you want to have continuous replication that will send the data as soon as possible, you can choose Continuously option. Otherwise, choose schedule option. Select the Continuously option and click the Next button.

28.  Initialize subscription if it's needed.

29.  Click Next button.

30.  Click Finish button to create the subscription.   

         

Now, you will receive the following....

  

Here, Replication is set Successfully..!!