I have a DB of around 3.5TB in size
It stores time series IOT data
99.9% of data resides in 1 table (approx 7 billion rows)
Currently the table is not partitioned and it sits of one NVME drive
I'm fast running out of space (20GB per day growth)
So I need to do the following (without any downtime - maybe 1 hour max)
1) convert existing data on current hard drive to be partitioning by month (creating as many partitions as there is data)
2) create a new partition on new NVME (E: drive) and start saving April 2020 data on this NEW drive (and all new partitions) ....all old data will live on old drive.
3) each new month should create a new partition and automatically put data into the new partition
Here is a some info about the workload:
-Data older than 30 days is not saved to database, and no UPDATES are ever done, just inserts and deletes.
-Data retention differs - customers choose from 1,3,6, 24 month data retention, I have a script which deletes old data, it can cause blocking on inserts, so partitioning solution needs to ensure this does not happen
-Clustered index is currently on SensorId, DateTime, EventId, PkId (the pkid bit is to make it unique)... when i switched specificity so DateTime was first, it wasn't as performant, but this might be something that i have to do???
-All indexes are heavily fragmented as i'm scared to death about down time (its 24/7 app with no maintenance window)
Can someone please help me attack this as we dont have long left for space and new month approaching.
Any pitfalls or plan of action I should follow.
My main concern is down time, and rebuilding indexes with such little space remaining
sql server 2017 ent