SQL Server table paritioning on production databases

Question

HI,

I want to get some expert feedback on table partitioning.

Use case:

1. Purge old customer data or when a customer is no longer there
2. To be able to move customers across other tenant databases.

This leads to a solution using table partitioning using a customer id type column (identity).

i) The proposed solution is to implement table partition using that key on all tables so that purging can be managed easily by dropping/truncaing a partition when a customer will not longer needs to be the system.
ii) Since this is a live environment, one option could be to migrate the tables to a newly created DB where the structure will be identical, apply partition and then migrate the data to the newly created tables.
iii) This will benefit to push only the required data since there are stale data on the prod tables.
iv) The downtime will be minimal since most of the data will be migrated to the new tables and flip the switch when all data is moved.

Would appreciate any feedback.

Thanks.

Answer 1

It is very difficult to comment on something with no knowledge about the system, business requirements etc. Moving a customer from one tenant to another, sounds suspect to me, but I don't know what "tenant" means here.

Generally, when you decide to for partitioning, you need to consider does the partition key appear in all queries? If you have queries there WHERE or join condition does include the partitioning key, you can expect these queries to take a hit.

As for the migration path you outline, it is again difficult to evaluate without further knowledge of the system. But keep in mind that if the system while you are running migration to the new database(s), you will need to deal with data changes that occurring the migration process. This requires careful consideration, and not the least careful testing.