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
Our community of experts have been thoroughly vetted for their expertise and industry experience.
The Distinguished Expert awards are presented to the top veteran and rookie experts to earn the most points in the top 50 topics.