Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
veeramaga
Starting Member
5 Posts |
 Posted - 2006-10-17 : 08:00:09
|
| can anyone out there provide me with sample coding to create a archive database to store oltp data, dumping the data from OLTP into the database on an daily basis.i understand we have to write stored procedure for this, can anyone help with a sample code.Thanks,veeraIndia |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-17 : 08:46:33
|
| Seehttp://www.nigelrivett.net/SQLAdmin/LogShipping.htmlcan also use replication.Maybe triggers?Depends really on what you want to do.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-17 : 09:38:50
|
| What is your purpose, is it to have a cold standby server for disaster recovery or is it to store old data that isn't used anymore?--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
veeramaga
Starting Member
5 Posts |
Posted - 2006-10-17 : 13:52:00
|
| the main purpose is to have selected columns from one database tablesonto a history tables for future reference, this has to be scheduled on an daily basis.can anyone help with writing automated stored procedure for this scenario. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2006-10-17 : 13:58:14
|
| You haven't provided enough information for us to help yet. Please show us an example of what you want to do.Tara Kizer |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-10-17 : 14:38:58
|
| If it's just a snapshot at the end of the day then probably a stored procedure to extract the column called from the remote database to insert the data (depending on the size of the table).Otherwise maybe a trigger that saves the columns when they are changed.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2006-10-18 : 03:13:05
|
If I get you correctly you want to archive outdated data to increase performance on the active data? If this is the case you can do something like this:- Create a replica of you transaction table and name it "myTable_archive", and make sure you remove any IDENTITY specifications but keep all columns present
- Create a procedure that moves old records from "myTable" to "myTable_archive" and after the move delete them from "myTable" (make sure you don't mess up on the logic here so you delete rows that hasn't been moved)
- Create a view called "mytable_all" or something like this:CREATE VIEW mytable_all ASSELECT * FROM mytableUNION ALLSELECT * FROM mytable_allYou will probably know when you need only active data and when you need active and historic data so you should select from myTable and myTable_all acordingly, or if you have a clustered index that is always used you can stick to the view only because it will not hit the archive table unless needed
- Make sure to update statistics and do index defrags/reindexing on a regular basis
--Lumbago"Real programmers don't document, if it was hard to write it should be hard to understand" |
|
|
|
|
|
|
archive database