Convert existing table to partitioned table - SQL Server 2017 I have a large busy database where 99.9% of data lives in one table. IOT devices report their time series data into this table.

This table has 7 billion rows and takes up around 3.5 TB of space. 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 minutes). I need a strategy to save this new data.

Here is idea 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)
Rename current large table to IotSensorData_Old, then rename empty IotSensorData_New to IotSensorData
Turn on data importers so data starts ingesting into this new table (again ID is concern here as it will start from 1)
Convert the old IotSensorData_Old data into the current table - I'm unsure exactly how to do this bit


Here is idea 2:


Turn off data importers
Convert the current table to partitioning (I understand indexes need to be rebuilt if I do this which would probably take over 24 hours)
Turn on importers


I'm looking for exact steps on how to do this with the least downtime

I'm using SQL Server 2017 Enterprise edition.
You are totally on the wrong idea here. PArtitioning is not really how to do that. Not saying that thee is no sense in it, but generally partitioning is about deleting data fast (i.e. a partition per year MAY bea good idea to get rid of 10 year old data in milliseconds).

All you need to do as simplest solution for your case is:


Add other drive
Make sure it is writable (partition, format etc.
Go into the db setup and add another db file to the filegroup.


You do not gain most of the advantages of multiple drives, but you would not with your solution either. This would require serious reformatting which I doubt you have the space for.

But a file group can have multiple files and SQL Server will automatically use them all. There is no need to partition for your particular problem.