Convert existing table to partitioned table - SQL Server 2017

Question

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:

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 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 over 24 hours)
3. 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.

Answer 1

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.

• Quade: thanks TomTom, so if I add the new drive to sql server, it will "see" this new drive and start writing data to it ? does this apply for re-building indexes ? will it use the new drive for that?
• TomTom: Tables, indicex etc. do not exist on files, they exist on FILE GROUPS. So adding a file to a file group is all that is needed. And that file can exist everywhere. That said, you loose a LOT of advantages of just adding a file, like splitting the IO load (all old data is on the old drive).
• Quade: Thanks, i'm a little unclear what you are suggesting, should i be considering adding a file and something else that supports splitting the load?
• TomTom: I asugest adding a file to the filegroup. There is a manual that GLADLY will help you understand the term filegroup.