0

Due to a high amount of databases being created on my project and the rising cost per databases I had the following question:

Is there a way to convert multiple databases in SQL Server into one database with multiple schemas?

Thank you for helping out.

Improve this question
4
  • Wouldn't it simply shift your costs per database into costs per schema? Likely there will be more overhead managing a single database with multiple schemas representing multiple logical databases... I would question your motivations for doing such a thing.
    – blobbles
    Commented Aug 27, 2014 at 7:50
  • 1
    Hypothetically, are you not just shifting 8GB split over 8 databases of 1GB into 1 database of 8GB? Are you paying for hosting per database?
    – Mark Sinkinson
    Commented Aug 27, 2014 at 8:27
  • 1
    I'm using Microsoft Azure, where you pay per database. Changing to one database would lower our costs even if the size increases.
    – Jeffrey Rosselle
    Commented Aug 27, 2014 at 9:17
  • Note that it is not enough to convert all the schemas; every line of SQL code for all applications must be corrected to properly use multi-part names.
    – Pieter Geerkens
    Commented Aug 29, 2014 at 19:03

1 Answer 1

Reset to default
1

Yes, this can be done.

In the open source community, it has been done for years. In most situations, they just prefixed the tables.

I am not aware of a tool to simplify the process.

You are aware that you introduce new challenges.

  1. Permissions.
  2. Queries are more complicated and may lose some performance.

An alternative approach is to move to multi-tenant design. Add a new field to all tables. Let's call it ClientId. Add filtered indexes. Now all queries need to be filtered on ClientId. You still need to deal with any auto increment columns.

Improve this answer
2
  • Not sure I advocate a filtered index for every client (that's going to have an upper bound just in capacity specifications, but an earlier one in management overhead). Maybe the most demanding or largest ones.
    – Aaron Bertrand
    Commented Aug 27, 2014 at 18:19
  • @AaronBertrand, your comment is valid. It was a general idea.
    – Osa E
    Commented Aug 27, 2014 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.