Skip to content
Tags

,

Azure SQL Database is Use-less

2014/08/13

[This is part Seven of Top 10 Things You Will Hate About Azure SQL Database]

Top107

That never gets old.

While I enjoy the play on words, this just means that the USE statement is not allowed in Azure SQL Database. Three part naming ([ServerName].[DatabaseName].[ObjectName]) is not valid either. You can only run queries in the context of the database you are connected to, which will need to be considered during solution design.

While this limitation feels like a large one I know that in my T-SQL coding any time I use a three part name or a USE statement to cross databases it feels a little like a hack. It usually means we didn’t think of something at design time or we are trying to quickly add on to an existing project without building a new architecture. Also, if you think of the underlying Azure architecture this one makes a bit of sense. A single database is not guaranteed to even be on the same server as another database or possibly even in the same data center.

I’m not saying there are no uses cases for having data separated out in multiple databases. One example would be a read only database used by multiple applications for lookup data, but this could be easily handled on the application side using separate connection strings.

For Azure SQL databases another concern is database size. A Standard tier database is limited to 250GB, which in today’s world is not a lot of data. The solution is to stripe the data across multiple smaller databases to allow for many of terabytes of data in the cloud. This requires some coding on the application side to use the correct connection string, but works pretty well when combined with a catalog that stores the location of the records based on a partition key. Sound familiar? Despite the extra work it also helps with performance and allows for a very secure single tenant database design.

 

 

 

Advertisements

From → SQL Server

Leave a Comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s