SQL Data Archive

Storing obsolete data reduces the performance of a database server. A well-designed archival strategy minimizes the performance problems that are caused by maintaining excess data.

Archiving is the process of getting rid of obsolete data from the main production databases. By archiving data, you can improve query performance, decrease the disk space usage and reduce the maintenance window. From a performance point of view, if a production database has obsolete data that is never or rarely used, query execution can be time-consuming. This is because queries also scan the obsolete data. To improve query performance, you can move the obsolete data from the production database to another server.

Some of the main reasons for archive:

•  Improve query performance

•  Decrease the disk space usage

•  Faster backup and restore times

•  Easier manageability

•  Better user experience

By archiving old data, you can keep your databases smaller and more manageable. With smaller tables, your index rebuilds times and backup/restore times will be under control.When designing an archival strategy, you should first determine how much data can be archived, then choose an appropriate structure for the archival data, and finally create a plan for archiving the data.

There is no built-in command or tool for archiving databases. It’s depending on the business needs.

Method 1:

Create a separate script file that will move data from live database to Archive database and delete data from live database.

For example we have records from 2000 to 2013.We can keep data from 2005 to till date and move older data to archive database, maintain a table for archive dates and change the SP in such a way. When we pass dates to SP, it will check with the dates in Archive date table and if the date is less than the archive date then bring the data from archive database otherwise get data from live database.

Method 2:

SQL Partition

Partitioning is a physical database design technique. Partitioning refers to splitting what is logically one large table into smaller physical pieces.Main goal is to reduce the amount of data read for particular SQL operations so that overall response time is reduced.

Partition function can only be created in Enterprise edition of SQL Server. Only Enterprise edition of SQL Server supports partitioning.

References :

http://www.extremeexperts.com/sql/yukon/ArchivingData.aspx

http://vyaskn.tripod.com/sql_archive_data.htmhttp://vyaskn.tripod.com/sql_archive_data.htm

http://www.mssqltips.com/sqlservertip/1121/archiving-data-in-sql-server/

Generate and write to excel file in C#

This article provides a very simple method to generate excel and write data to spreed sheet.

Below is a sample code snippet showing a method to generate a excel and write data to spreed sheet.I have used Microsoft.Office.Interop.Excel to do it using C#

public void GenerateExcel()

        {

            try

            {

                Microsoft.Office.Interop.Excel._Application app = new Microsoft.Office.Interop.Excel.Application();

                if (app == null)

                {

                    MessageBox.Show("EXCEL could not be started. Check that your office installation.");

                    return;

                }

                // creating new WorkBook within Excel application

                Microsoft.Office.Interop.Excel._Workbook workbook = app.Workbooks.Add(Type.Missing);

                // creating new Excelsheet in workbook

                Microsoft.Office.Interop.Excel._Worksheet worksheet = null;

                // see the excel sheet behind the program

                // get the reference of first sheet. By default its name is Sheet1.

                // store its reference to worksheet

                worksheet = workbook.Sheets["Sheet1"];

                worksheet = workbook.ActiveSheet;

                if (worksheet == null)

                {

                    MessageBox.Show("Worksheet could not be created. Check that your office installation and project references are correct.");

                }

                // changing the name of active sheet

                worksheet.Name = "Test sheet";

                DataTable dtTest = new DataTable();

                // Add column headings...

                int iCol = 0;

                foreach (DataColumn c in dtTest.Columns)

                {

                    iCol++;

                    worksheet.Cells[1, iCol] = c.ColumnName;

                    // Adding Borders

                    worksheet.Cells[1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

                    //adding Interior color

                    worksheet.Cells[1, iCol].Interior.Color = Color.Yellow;

                }

                // for each row of data...

                int iRow = 0;

                foreach (DataRow r in dtTest.Rows)

                {

                    iRow++;

                    // add each row's cell data...

                    iCol = 0;

                    foreach (DataColumn c in dtTest.Columns)

                    {

                        iCol++;

                        worksheet.Cells[iRow + 1, iCol] = r[c.ColumnName];

                        // Adding Borders

                        worksheet.Cells[iRow + 1, iCol].Borders.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

                        //Change the number format

                        worksheet.Cells[iRow + 1, iCol].NumberFormat = "0.00";

                    }

                }

                app.Visible = true;

                MessageBox.Show("Completed!", "Export");

            }

            catch (Exception ex )

            {

                

                MessageBox.Show(ex.Message, "Error");

            }