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.

  • 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 Aug 27 '14 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 Aug 27 '14 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 Aug 27 '14 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 Aug 29 '14 at 19:03
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.

  • 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 Aug 27 '14 at 18:19
  • @AaronBertrand, your comment is valid. It was a general idea. – Osa E Aug 27 '14 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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