StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POAzure Database Design, partition out data tables vs federations?
primarykey
Id
15274020
data
AcceptedAnswerId
15280818
AnswerCount
3
ClosedDate
CommentCount
0
CommunityOwnedDate
CreationDate
2013-03-07T14:40:18.277
FavoriteCount
0
LastActivityDate
2013-03-07T20:12:38.713
LastEditDate
2013-03-07T16:20:35.757
LastEditorUserId
972891
OwnerUserId
972891
ParentId
0
PostTypeId
1
Score
2
ViewCount
647
LastEditorDisplayName
text
Body
I am looking at the viability of moving our .NET web application to Azure. The multi-role setup to handle the web server aspect seems more than capable. I have reservations with regards to the Azure Database performance and adopting the right scale-out strategy for our needs. We use a single database comprising of 120 tables, 80% of the transactions run on 10 tables. The remainder are used for various account level settings and global references. The largest table consists of 5 million rows and is manipulated using a set of triggers on the remaining 9 of the big tables, each of which in-turn holds anywhere between 500k and 250k rows. My initial thoughts were as follows: <ol> <li>Move the largest table into it's own database instance and reference it using a SYNONYM. I now realise Azure DB doesn't appear to support SYNONYMS across database instances.</li> <li>Use Federations to dissipate the workload across more Azure DB instances. Our DB is only 5GB so perhaps this is a premature option?</li> <li>Use a higher spec Virtual Machine with SQL to serve the database.</li> </ol> I appreciate there are many unknowns here and I don't expect a definitive answer, I'm just looking to see what experience the Stackoverflow community can offer. Additional information <ul> <li>Current setup: Single SQL Server 2008 R2 instance with a single database of 120 tables running on a decent spec multi-core server with 12 GB RAM.</li> <li>Current performance is very good, to the extent that we could trade some in for scalability.</li> <li>Database is growing by 10% per month and relies heavily on relational data, triggers and complex stored procs, making it difficult to use Azure tables as an alternative.</li> </ul>
Tags
<performance><database-design><azure><azure-sql-database><sharding>
Title
Azure Database Design, partition out data tables vs federations?
singulars
PostAcceptedAnswerId
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USQFDev
UserOwnerUserId
1. USQFDev
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
 singulars
 PostTypePostTypeId
 PTAnswer
2. PO
 singulars
 PostTypePostTypeId
 PTAnswer
3. PO
 singulars
 PostTypePostTypeId
 PTAnswer
VotesPostIdCreationDate
1. VO
 singulars
 PostPostId
 POAzure Database Design, partition out data tables vs federations?
 UserUserId
 This table or related slice is empty.
 VoteTypeVoteTypeId
 VTUpMod
CommentsPostId
1. This table or related slice is empty.

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.