Extracting files from partitioned database

Question

The company I work for performs services for a couple of hundred customers, all have their own database with identical schemas.

We are planning to merge all of these databases into one, partitioned, database as it will make reporting across all customers easier.

Each table in the database has a customer_id column, so this seems to be the best data item to partition on.

As I understand it, following the merge each partition will have its own mdf and ldf files and these will contain all the data for one customer.

One reason that we have kept the databases separate up to now is that it makes copying a database to our own development environments for debugging or testing new functionality quick as each database is relatively small (100GB typically).

My question is, given that I can get the files for a single customer from the database server is it possible to copy them to my development SQL Server and create a database for that single customer?

Answer 1

As I understand it, following the merge each partition will have its own mdf and ldf files and these will contain all the data for one customer.

...

My question is, given that I can get the files for a single customer from the database server is it possible to copy them to my development SQL Server and create a database for that single customer?

That is wrong. There are no "partitioned databases" in SQL Server, there are partitioned tables and partitioned views. I suppose you tell us about partitioned tables.

Normally, every database has ONLY ONE log file, and even if for some reason some database has more than 1 log file, it has nothing to do with partitioning. So one more time, you misunderstand partitioning.

Partitions are NOT independent databases within your database, you cannot get out one partition by just copying the corrsponding .ndf file. You never can attach a single ndf file, it will never succeed.

And even if you'll try to make partial backup/restore you cannot restore database from one ndf backup without restoring PRIMARY filegroup

Answer 2

I think you are mixing two different concepts with each other and possibly even more.

Index/Table Partitioning

The feature of index/table partitioning is explained in the following document:

Reference: Partitioned Table and Index Concepts (Microsoft Docs)

In short ...

SQL Server supports table and index partitioning. The data of partitioned tables and indexes is divided into units that can be spread across more than one filegroup in a database. The data is partitioned horizontally, so that groups of rows are mapped into individual partitions. All partitions of a single index or table must reside in the same database. The table or index is treated as a single logical entity when queries or updates are performed on the data.

...and listed under the Benefits of Partitioning:

You can transfer or access subsets of data quickly and efficiently, while maintaining the integrity of a data collection. For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned.

This section might be leading you to believe that you could load lots of data faster into your centralised DEV database with backup/restore. However, this is only valid if you are talking about a data transfer and is not valid for a backup/restore scenario. Explanation follows in the Summary.

Files and Filegroups

Then you have the features encompassing Files and Filegroups which is explained in the following article:

Reference: Database Files and Filegroups (Microsoft Docs)

In short...

At a minimum, every SQL Server database has two operating system files: a data file and a log file. Data files contain data and objects such as tables, indexes, stored procedures, and views. Log files contain the information that is required to recover all transactions in the database. Data files can be grouped together in filegroups for allocation and administration purposes.

I won't go into further details here but instead carry on with the summary.

Summary

The whole database is split over the filegroups used for partitioning. It is true that the one filegroup may contain data for only one table partition (e.g. a single customer), but it is logically linked to the other filegroups split over the various *.mdf and *.ndf data files.

Restoring a single filegroup would require a roll-forward of all transaction log backups to bring the database back to a consistent state as discussed in the following article:

Reference: How to: Restore Files and Filegroups (Transact-SQL)

The transaction log backups, if applied, must cover the time when the files and filegroups were backed up until the end of log (unless ALL database files are restored).

This would however require you to restore the "Customer's FILEGROUP" (as per your idea) to a consistent state, which would include all the data (..in the transcastion logs...) for all the customers, because otherwise the database would be in an inconsistent state.

So partitioning data does not simplify a backup/restore scenario. You have to have the whole database in order to restore a specific filegroupd, but then you need all the transaction logs to ensure a consistent database.

In the end you have the whole database and nothing but the whole database.

Ha, but you quoted...

You can transfer or access subsets of data quickly and efficiently...

...but only on a data level. You can dump the contents of the partitioned table to a CSV file for future import into an adequate table, or start a data transfer between two databases (OLTP and OLAP) as can be seen from above original quote:

For example, an operation such as loading data from an OLTP to an OLAP system takes only seconds, instead of the minutes and hours the operation takes when the data is not partitioned

..but does this then contain all the (referential) data required to have a consistent data set? Presumably not.

Answer 3

As I mentioned in the comments on @sepupic's answer you can do design a database to use piece meal restore by a customer if each customer data is in a separate file group. But such solution will have a lot of limitations.

If you your primary goal is to simplify reporting across all customers I suggest you keep individual databases and try looking at technologies which can help you "aggregate" data for reporting instead of merging databases:

1. Federated Database (Server). It let you create views pointing to tables in different databases (or even servers) and query them as a single object: https://technet.microsoft.com/en-us/library/ms190381(v=sql.105).aspx
2. Analysis Services let you define a cube consisting from multiple partitions and each partition may have a different query pointing to a different database.

I there might be many more options.