Multiple Umbraco instances in a single database

[Philip Coupar]

The new pricing structure for SQL Azure means that it would be ideal to have multiple umbraco instances using the same database, ideally with each instance using its own schema.  I have only has a brief look at the code so I am not sure how easy this would be to implement.

[Aaron Powell]

TL;DR: It should work.

 

 

I’m not a database expert by any means but looking through the Umbraco codebase there is nowhere that Umbraco is hard-coded to look for any particular schema, be it `dbo` or be it something else.

What I believe is the reason that `dbo` is used is that that is the default schema for pretty much every database user people create. If you were to create a user and change their default schema then it will create using the default as specified.

Here’s an example of a table create from Umbraco:
CREATE TABLE [umbracoNode] ([id] INTEGER NOT NULL IDENTITY(1,1),

[trashed] BIT NOT NULL CONSTRAINT [DF_umbracoNode_trashed] DEFAULT ('0'),

[parentID] INTEGER NOT NULL,

[nodeUser] INTEGER NULL,

[level] INTEGER NOT NULL,

[path] NVARCHAR(150) NOT NULL,

[sortOrder] INTEGER NOT NULL,

[uniqueID] UniqueIdentifier NULL,

[text] NVARCHAR(255) NULL,

[nodeObjectType] UniqueIdentifier NULL,

[createDate] DATETIME NOT NULL CONSTRAINT [DF_umbracoNode_createDate] DEFAULT (getdate()))

 

Notice there is no schema defined so it should be determined the user you connect as.

 

I just did a quick test with Umbraco 7.1.8, SQL Server and a custom user with `umbraco` as the default schema. Unfortunately I wasn’t having much luck, it kept crashing on some FK violations but that could that I didn’t properly setup my user account (and I’ve never tried/found a need for multiple schemas and default schema setups).

--
You received this message because you are subscribed to the Google Groups "Umbraco development" group.
To unsubscribe from this group and stop receiving emails from it, send an email to umbraco-dev...@googlegroups.com.
To post to this group, send email to umbra...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/umbraco-dev/441fc72f-2f13-43c3-9448-9f39f03d5983%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Sebastiaan Janssen]

FWIW I just managed to do this, see SQL scripts below.

It's important to note that some package that install tables have the "dbo" schema hardcoded (SEOChecker for example), so they will not install until that is fixed.

It's also very important to note that SQLAzure has limits on "Database Throughput Units" (DTUs) which you should monitor when doing this, you might use those "units" (whatever they are exactly) more than you think with multiple sites connecting to it. I would also not put more than a few sites in one database as each site will be competing for resources and connections to that db.

The instructions below also work for plain old SQL server in case anyone is interested. These scripts create three users with the schemas "dev", "stage" and "live". Each user only has access to their own schema (access to "VIEW DEFINITION" is denied for the schemas the user doesn't own).

Connect to your SQL server with a user that has permissions to access the master database. Select the master database from the dropdown in SQL Management Studio (`use [master]` does not work on SQL Azure).

Execute these queries:

CREATE LOGIN u1 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'

CREATE LOGIN u2 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'

CREATE LOGIN u3 WITH PASSWORD = 'Mdfjd$sakj943857l7sdfh##30'

Then use the dropdown again to go to the database you want to use for this and execute the following queries:

CREATE USER u1

GO

CREATE SCHEMA dev AUTHORIZATION u1

GO

ALTER USER u1 WITH DEFAULT_SCHEMA = dev

GO

CREATE USER u2

GO

CREATE SCHEMA stage AUTHORIZATION u2

GO

ALTER USER u2 WITH DEFAULT_SCHEMA = stage

GO

CREATE USER u3

GO

CREATE SCHEMA live AUTHORIZATION u3

GO

ALTER USER u3 WITH DEFAULT_SCHEMA = live

GO

ALTER ROLE db_datareader ADD MEMBER u1

GO

ALTER ROLE db_datawriter ADD MEMBER u1

GO

ALTER ROLE db_ddladmin ADD MEMBER u1

GO

ALTER ROLE db_securityadmin ADD MEMBER u1

GO

ALTER ROLE db_datareader ADD MEMBER u2

GO

ALTER ROLE db_datawriter ADD MEMBER u2

GO

ALTER ROLE db_ddladmin ADD MEMBER u2

GO

ALTER ROLE db_securityadmin ADD MEMBER u2

GO

ALTER ROLE db_datareader ADD MEMBER u3

GO

ALTER ROLE db_datawriter ADD MEMBER u3

GO

ALTER ROLE db_ddladmin ADD MEMBER u3

GO

ALTER ROLE db_securityadmin ADD MEMBER u3

GO

DENY SELECT, VIEW DEFINITION ON schema::stage TO u1

GO

DENY SELECT, VIEW DEFINITION ON schema::live TO u1

GO

DENY SELECT, VIEW DEFINITION ON schema::dev TO u2

GO

DENY SELECT, VIEW DEFINITION ON schema::live TO u2

GO

DENY SELECT, VIEW DEFINITION ON schema::dev TO u3

GO

DENY SELECT, VIEW DEFINITION ON schema::stage TO u3