Avatar of websss
websssFlag for Kenya

asked on 

help moving to partitioning

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
Microsoft SQL Server

Avatar of undefined
Last Comment
Máté Farkas
Avatar of CompProbSolv
CompProbSolv
Flag of United States of America image

I'm not an SQL expert, so can't address your question directly.  I do have a suggestion, though.  Is it really necessary to have the speed of an NVME drive at the moment?  I'd consider buying a 8T conventional drive, move the data over to it, and buy yourself time to deal with the issue.  You could even use the original copy to test the process of splitting up the data without risk of data loss as the live data will be on the 8T drive.
Avatar of websss
websss
Flag of Kenya image

ASKER

Thanks, its a cloud server so dont have this option
ASKER CERTIFIED SOLUTION
Avatar of Máté Farkas
Máté Farkas
Flag of Hungary image

Blurred text
THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Microsoft SQL Server
Microsoft SQL Server

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.

171K
Questions
--
Followers
--
Top Experts
Get a personalized solution from industry experts
Ask the experts
Read over 600 more reviews

TRUSTED BY

IBM logoIntel logoMicrosoft logoUbisoft logoSAP logo
Qualcomm logoCitrix Systems logoWorkday logoErnst & Young logo
High performer badgeUsers love us badge
LinkedIn logoFacebook logoX logoInstagram logoTikTok logoYouTube logo