SQL Server Partitioned Views

Question

I have an OLTP application which stores the data in a database using MS SQL Server. Most of the data belongs to a user. For most of our tables we have Views selecting a subset of the corresponding table selecting the data of the user. Each user has its own login credentials managed automatically by us to support this mechanism.

If Scaling out should become necessary my approach would be to separate the data by users - data belonging to a user will stay together while data of different users might be distributed. Most of our queries are executed on the data of one user only - that’s why I think this approach makes sense.

The partitioning view documentation on MSDN aggregates the data of the partitioned views which would not be necessary in my case as I only want the data of one partition.

I search for an option that enables the partitioning without the need of any data access routing logic in the source code. I would like this to be done by SQL server.

Is view partitioning the best option for my case?

Answer 1

The difficult with this kind of post is that it easily becomes a little abstract, and I may be misunderstanding what you have in mind.

But it appears that you want to split your data across multiple servers, and set up a view that knows on which server the data is, without the user having to bother.

Indeed, SQL Server has a feature known as Federated Partitioned Views which has been in the product since SQL 2000. In theory it should do what you are asking for. But beware that although it has been in the product for a long time, it is not very widely used. And overall, anything that involves distributed queries tends to come with a lot of hassle, both with regards to security and performance. Myself, I would be very hesitant to rely on this feature, but rather I would first run a query to find on which server a specific user has its data, and then connect that server directly.

Note also that Federated Partitioned Views are only supported on Enterprise Edition. You can implement them on any edition, as there is no specific syntax for them. It is just that the rules to recognise the view as partitioned across server are only available with Enterprise Edition.