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:

  • Deciding on the size of RAM needed for my database runs on SQL server (windows 64 bit)
  • SQL Recursive CTE 'where-used' / BOM explosion
  • SQL Where clause Coalese vs ISNULL VS Dynamic
  • How to create composite partition in SQL Server?
  • How to properly convert accented characters using FOR XML
  • Case statement not correctly matching expected values
    • IdElement

    • TypeElement

    • ColumnChange

    • OldValue

    • NewValue

    • Author

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.