Database

19-Feb-08 6:08

Hi all,

I have two tables one is live real time where data is updated every 30 minutes.

I created another duplicate table for backup purposes.Hence i want this back up to have the data of the live table every 2 hour.

In short i have two table one live table and another to keep a backup of the data on the live table every two hours.

Basically, i want to have the two tables having the same data before the 30 minutes update on the live table.

How could i go about this problem?

Thank you

modified on Tuesday, February 19, 2008 12:29 PM

Mark J. Miller19-Feb-08 7:50

19-Feb-08 7:50

Are you actually using the backup table? By differentiating between live and backup I'm assuming you're not. In that case, why not place the table in it's own filegroup and backup that filegroup on a schedule that meets your needs? If you need to rollback you can restore the filegroup by itself.

kibromg19-Feb-08 9:19

19-Feb-08 9:19

Thank you for your response.Its much appreciated.
The back_up table is used with some application for reporting purposes.Instead of saying back_up i should probably say another copy of live table every 2 hour.

In other words, To have two tables will have the same data as it updates every two hours.Within two hours live table will have more data until the update is done.

Thank you

Mark J. Miller19-Feb-08 11:16

19-Feb-08 11:16

Does the data in the production table change at all (UPDATE/DELETE)? Or is it always new data (INSERT)?

One answer is to use Red Gate's SQL Data Compare. It's pretty cheap and you can write a script to sync the tables every two hours.

You could dump/refresh the backup table.

Method 1:
This would work much like using the Red Gate tool.

1) Add a timestamp column to the livedata table and a binary column to the backupdata table
2) Compare and update existing data (UPDATE b SET column = live.column FROM backupdata b INNER JOIN livedata live ON live.ID = B.ID WHERE live.stamp <> b.stampcopy)
3) Insert all data from livedata that doesn't exist in backupdata

Method 2:

1) Create a logging table that records which records in livedata have changed and the change that was made (INSERT, UPDATE, DELETE).

2) Create a trigger on livedata would add a record to the logging table including ID, Action, ActionTime. (ActionTime could be a timestamp or datetime).

3) Write a script that reads the log table, and syncs the backupdata table on a schedule based on the ID and the action with the oldest ActionTime.

Method 3:

TRUNCATE TABLE backupdata<br />
INSERT INTO backupdata (columns)<br />
SELECT columns FROM livedata

Method 4:

CREATE TABLE backupdata_temp (<br />
 columns.....<br />
)<br />
<br />
INSERT INTO backupdata_temp (columns)<br />
SELECT columns FROM livedata<br />
<br />
DROP TABLE backupdata<br />
<br />
EXEC sp_rename ......

Either way make sure you wrap the whole thing in a transaction and set the logging mode to BULK LOGGED before you do it and then change the logging mode back when you're done. Also, if you use method 3, make sure you recreate any indexes you had that were on the backup table.

The choice of methods will depend on the availability requirements for the backup table and the amount of data you're working with.

kibromg19-Feb-08 23:21

19-Feb-08 23:21

Hi Mark,

Thank you once again Mark.Its has been very helpfull.I am only INSERTING new records to the live table.No Update or Delete.

Which method will best fit this case?

Thank you.

kibromg19-Feb-08 23:48

19-Feb-08 23:48

Hi Mark,

I would like to use the First step.Does it mean that i have to create a relationship between Live Table and Backup_table.As you have mentioned INNER JOIN.

Thank you.

Mark J. Miller20-Feb-08 6:54

20-Feb-08 6:54

INNER JOIN does not require an explicit relationship, so no you have an implicit relationship due to the fact that backuptable will have the same primary key as livetable. You will be able to join the table using the primary key from each table. But if you are only doing inserts then there are two solutions:

1)

INSERT INTO backuptable (columns)<br />
SELECT columns FROM livetable L WHERE NOT EXISTS(SELECT * FROM backuptable WHERE ID = L.ID)<br />

2) If you are using an IDENTITY (aka AutoIncrement) column as your primary key you could also do this

INSERT INTO backuptable (columns)<br />
SELECT columns FROM livetable WHERE ID > (SELECT MAX(ID) FROM backuptable)

The second will be faster as long as there is an index on ID for both tables (which if you have created them correctly and assigned them as primary keys then there will be).

APPLICATION TO RUN EVERY FEW SECONDS

kibromg21-Feb-08 5:27

Advice sought, how do I best handle saving an array in a SQL database?

21-Feb-08 5:27

Hi All,

I a page in ASP.net i want this page to run every two hours with out any manual intervation.
How could i do it please?

Inshort,i want the application to execute itself every two hours.

Thank you.

Per Sderberg19-Feb-08 5:06

Per Sderberg

19-Feb-08 5:06

Hi all,
I'm developing an application in Visual Studio 2005 using VB.NET. Now I would like to store the variables in a SQL database. Each member of the databse has roughly 1.000 values to store. But most of them are in arrays like this:
dim results(6, 50) as string

The VB-programme generates the values and thus I thought it would be possible to declare an array in the Database declaration like Results(index1, index2) and then index1.max = 6 and index2.max = 50, something like that.

And then I could add it like this:

myCommand.Parameters.AddWithValue("@mResults", Results)

I'm concerned for how to describe the input variables in the SQL Database. As far as I could tell it seems not possible?
I'm thinking that I will still have to give each and everyone of the 300 values a description, if it will allow nulls etc.

If I still have to do it that way then I guess a loop like this will also do the work:

for i as integer = 1 to 6

for j as integer = 1 to 50

myCommand.Parameters.AddWithValue("@mResults" & i.ToString & j.ToString, Results(i, j))

next

next

An other idea is of course to save the array as a text with delimiters between the values.

Or is there any smart way that I'm unaware of to store the arrays?
Any advice would be appriciated. I see a lot of work to first declare the 1.000 values and its equivalent code and so on. Of course, I'm planning to describe the arrays as seperate tables with the identical primary key.

Best, Per

Re: Advice sought, how do I best handle saving an array in a SQL database?

Mark J. Miller19-Feb-08 8:26

19-Feb-08 8:26

There are a few ways to save arrays to SQL Server, but none of them built in (that will change for SQL 2008 with table-value parameters).

It would be easier to answer your question if I knew what your target schema is. You mention separate tables and a large number of variables, so I'm picturing (based on your array declaration) that we're looking at 6 tables with 50 columns each (plus primary key??).

You can submit a delimited array to SQL Server as a varchar parameter (varchar(max) if you're using 2005) and then write a user defined function to parse it in the database (I'd recommend writing a CLR function if you're using SQL 2005 as it would be much, much faster).

Here's a link to an article I wrote on passing arrays to SQL Server. http://www.codeproject.com/KB/database/TableValuedFnsAsArrays.aspx[^]

As for parsing the delimited string the article only addresses arrays with a single dimension. You'll need to alter the code to handle multiple dimensions, but it can be done without too much trouble. Or you can submit the data as 6 separate queries (one for each of the major dimensions).

A method not mentioned in the article is sending XML instead of a delimited string, I can't imagine it would be faster than the methods in the article, but it's an alternative nonetheless. I know I've seen an article about it here on codeproject, but I don't remember which one.

Without more information it's difficult to be more specific.