Convert multiple databases into schemas SQL Server

Question

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.

• blobbles: 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.
• Mark Sinkinson: 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?
• Jeffrey Rosselle: I'm using Microsoft Azure, where you pay per database. Changing to one database would lower our costs even if the size increases.
• Pieter Geerkens: 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.

Answer 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.

• Aaron Bertrand: 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.
• Osa E: @AaronBertrand, your comment is valid. It was a general idea.