2

MSSQL database has previously been archived on another database. Approximately 70m rows (includes sales information.)

Now, I am asked to move this archived data to live database. Because, customer can't see order history older the 3 years.

Is it reasonable to do this? I think it will be slow. Are there other solutions?

  • Live DB size : 250GB
  • Archive DB size : 50GB
  • CPU : 8 Core
  • Memory : 64GB
  • Version : Microsoft SQL Server 2016 (SP1)
Improve this question
12
  • Why can't the customer view the data in the archived db? Are they using an application that can only read from X table and the archived data is in Y table? What kind of abilities do you have to alter the database / table / procedures? Is high availability involved at all? (In case you have to deal with clustering and fail over, since you would have to ensure the instance failing over has enough will get enough power from the server hosting two active nodes on one server.)
    – Shaulinator
    Commented Jul 10, 2018 at 13:29
  • Why would it be slow, given the data is suitably indexed in the first place?
    – Philᵀᴹ
    Commented Jul 10, 2018 at 13:31
  • @Shaulinator Yes and I have all the privileges (sysadmin). Not have high availability, just I using aws database migration service.
    – Yunus UYANIK
    Commented Jul 10, 2018 at 13:40
  • What is the workload like on the live DB? Off the top of my head, I think you could partition the table and integrate the old data into it. Alternatively you can create a view to join the old + new table together and still partition and index both tables. Columnstore indexes could help here too after the data is integrated.
    – Shaulinator
    Commented Jul 10, 2018 at 13:43
  • 1
    I feel like the application should be able to tell if the data they are requesting is X days old, go to Table1 otherwise go to Table2. A view more or less does that logic for you, but you have to beware of the performance impact and index accordingly. Columnstore indexes are also allowing people to run OLTP systems with a hybrid data warehouse piece attached for analysis. Nonclustered columnstore index on a disk-based heap or B-tree index.
    – Shaulinator
    Commented Jul 10, 2018 at 13:59

2 Answers 2

Reset to default
1

If I were to implement the migration, I would ensure to have a good planned indexing. First of all, move the historical data into history tables, having the same structure as the current working tables, then create indexes for big groups: For example, including a YEAR column and making sure this one is a mandatory filter when consulting the data.

As an option, you can create views to union historical with current data. But if I were you, I would implement a whole HISTORICAL REPORT feature in the app; having an AGREEMENT with your customer about: HOW MANY YEARS are SET as the current data.

But, again, a good indexing plan, is one of the best practices you can do when restoring the old database.

Improve this answer
2

If there's no duplicate data to worry about, adding another 50 GB of data to a 250 GB database is not going to matter much, especially since the business requirements need the archived data in the live database.

Make sure you have good covering indexes. Perhaps you can import the historic data into a separate filegroup and then mark it as read-only. Perhaps you could use a table partition.

There are lots of options to think of. @AMG's answer covers some more business-related questions for your ongoing SLA as well.

Improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.