I have a large busy database where 99.9% of data lives in 1 table
IOT devices report their time series data into this table
This table is on 7 billion rows and around 3.5TB
The Database is running out of space on its current drive.
I have another drive available that I would like to start saving new data for this one table
The challenge here is doing all this with no, or very little down time (max 30 mins)
I need a strategy to save this new data.
Here is idea 1:
1. create new table called IotSensorData_New which is same as the main table, but has partitioning turned on and lives on new drive (the ID identity seed is a concern here)
2. rename current large table to "IotSensorData_Old", then rename empty "IotSensorData_New" to "IotSensorData"
3. turn on data importers so data starts ingesting into this new table (again ID is concern here as it will start from 1)
4. convert the old the "IotSensorData_Old" data into the current table - i'm unsure exactly how to do this bit
Here is idea 2:
1. turn off data importers
2. convert the current table to partitioning (I understand indexes need to be rebuilt if I do this which would probably take forever.
3. turn on importers
I'm open to ideas and suggestion for the steps to take to achieve this
sql server 2017 ent