1

Hope some of you might have faced this requirement:- I have desktop based product, which we are planning to take it to cloud. We will be using only one database in SQL-Azure and merging databases of all customers. For merging, we have following options :-

*. Having NewCustomerID and existing TableID as composite primary key.

pros - Easy to export db, as it will be easy to export related tables, with foreign key available as it is in master tables. - Push back from cloud to in-premise db is easily possible

cons - almost every table will have composite primary key and - doubt of having perf issue (after googling, many suggest, tht composite key shud not cause perf issues). EF code first might cause some issue here.

*. Having new auto-increment primary key, and still maintain, NewCustomerID, and OLD TableID key

pros - relationship between tables will be based on single key, and easy to write queries and EF code first relationships.

cons - one extra key and maintainence of old key

Please suggest your choice or if you fond altogether new way to deal with such scenario in sql azure

Jeff Foster
  • 43,770
  • 11
  • 86
  • 103
Anand
  • 4,523
  • 10
  • 47
  • 72

3 Answers3

2

pros - relationship between tables will be based on single key, and easy to write queries and EF code first relationships.

Incorrect, or not quite correct. Depends what you meant by "relationship". See below.

Having a single-column auto-incrementing primary key makes many things simpler and I could recommend it. But the referential integrity constraints in your scenario would still require the creation of the alternate composite (two-column) keys for it is those composite keys that would be involved in the constraint.

An example for the sake of clarity:

           Customer A might not have "Azure" in their colors table
           Customer B might have "Azure" in their colors table.

Now the rows from both customers' color tables have been combined into one table:

          COLORS
          id integer primary key
          customerid
          colorname

Thus, in a hypothetical PRODUCTS table, you'd need this:

           PRODUCTS
           id integer primary key
           customerid
           colorid

           alter table PRODUCTS add constraint
           FK_PRODUCTS_COLORS
           foreign key(colorid, customerid) references COLORS(id, customerid)

You could not simply do this:

           alter table PRODUCTS add constraint
           FK_PRODUCTS_COLORS
           foreign key(colorid) references COLORS(id)

for that would permit CUSTOMER A to use the row for "Azure".

Tim
  • 8,669
  • 31
  • 105
  • 183
  • The flaw you mentioned is technically possible (though we could hv avoided with our internal-mechanism of allowing customer to deal with his data always, but as u mentioned, better to have referential keys on composite keys. But with first option I have, I end-up having PK as composite key for "EVERY" table of my database in sqlAzure, is it ok? – Anand Jul 21 '11 at 05:22
  • Thanks again Tim,you guided me in right direction. For security reasons also it seems I have no other option but to go ahead with Composite Primary key in almost every db of sql azure. (and wait for sql federation service) – Anand Jul 21 '11 at 06:42
2

This is a growing need in SQL Azure. However one thing I would point out is that consolidating all your customer databases into a single SQL Azure database is not necessarily a scalable design. Indeed, cloud computing is about scale out, not scale up. As a result designing a database with the intent to scale up is a somewhat risky proposition. Note that SQL Azure has a built-in throttling mechanism that blocks long running queries, excessive use of resources and so forth; this throttling mechanism is in a way a safeguard against scale up design patterns.

When it comes to designing a database against SQL Azure, you essentially have the following options:

  • Consolidate customer records in a single database by customer ID, then plan to use SQL Azure Data Federation at a later time (note: some key limitations apply; availability of this feature is not clear just yet, but a public announcement is somewhat expected later this year). Read into article from Cihan

  • Use a scalability framework, such as the Enzo Framework to create shards as transparently as possible (disclaimer: I am the author of this technology). The technology allows you to store your customer databases in either different databases, database schemas, or a mix of both with future support for SQL Azure Data Federation. Read my white paper on the topic

  • No need to scale (all customer records fit in a single database instance and with future plan to scale) and performance will not be a concern, in which case you can use the methods described in your email (using a customer ID of some kind to separate records logically)

  • Rollup your own database schema separation logic in which each database schema contains tables of different customers; use a central table that points customer logins to the correct database schema. Read a high level description on MSDN here

Herve Roggero
  • 5,149
  • 1
  • 17
  • 11
  • Thanks Herve. Your links and all the points helped me in deeper thinking. I would go with 1st & 3rd option mentioned in you answer. – Anand Jul 21 '11 at 06:43
0

Disclaimer: I am assuming that this is a multi-tenancy problem.

I would recommend looking up this similar thread

You may also want to take a good look at SQL Azure Federation and this nice piece on SQL Azare Sharding

Community
  • 1
  • 1
IUnknown
  • 888
  • 7
  • 18