How to Handle SQLServer Archive
Hello i need help to handle BigData
I have a Production BDD that got a lot of information.
Around 50 table but some of them are requested really few time.
I have a table history that record every change in other table with:
This table is BIG
SELECT COUNT(*) FROM Changes; SELECT COUNT(*) FROM Changes c Left Join ChangeSets cs on cs.IdChangeSet = c.ChangesetID Where cs.Date > '2016-12-18';
Execute in 22 minute….
174 011 299 Rows
2 4048 318 Rows less than a month
And I don’t need it a lot just some check sometime when I need to watch what the client did to cause a bug.
I would like to pass every data older than one month in a new database with the same History table that could be then requested when I need info.
Any way to do it automatically?
A best tool practise for this?
Is it necessary?(I don’t have infrastructure for handle big data and my company don’t want to upgrade a lot so I must find better way to make my SQL Server lighter)
It look like because in a test db after remove all older than 1 month the query where way faster on the db (about the history table of course).
I don’t usually check what a client have done 5 month ago but need to keep it for legal and security reason
PS: I also restore a lot of db for test and debug. and it’s longer if the db is bigger. So archive in another db would also help me to restore them faster for test purpose