When reading Transact-SQL documentation, I usually skip the Backus–Naur Form (BNF) at the top of the documentation and go directly to the samples. So, to add on to Cihan Biyikoglu blog post about the new SQL Azure database sizes available June 28, 2010, I want to show some samples of new CREATE DATABASE syntax.
You can still create a database without any parameters; this will generate the smallest database of the web edition:
CREATE DATABASE Test
This is the same as declaring:
CREATE DATABASE Test (EDITION=’WEB’, MAXSIZE=1GB)
The database created can hold up to 1 Gigabyte of data and then will return a 40544 error when trying to add more data. See Cichan’s blog post for more details.
You can also create a web edition database with a larger maximum size of 5 Gigabytes like this:
CREATE DATABASE Test (EDITION=’WEB’, MAXSIZE=5GB)
Business edition databases will start with a maximum size of 10 Gigabytes:
CREATE DATABASE Test (EDITION=’BUSINESS’)
However, they can be increased to a 50 Gigabytes using the maximum size parameter:
CREATE DATABASE Test (EDITION=’BUSINESS’, MAXSIZE=50GB)
The valid MAXSIZE settings for WEB edition are 1 and 5 GB. The valid options for BUSINESS edition are 10, 20, 30, 40, and 50 GB.
The good news with the new database sizes is that for some database sizes you are not billed for the maximum size until you use it. See the official pricing guide for more details. The table below tries to highlight this point.
Size In Use
0 – 1 GB
1 – 5 GB
5 – 10 GB
Most of the time you will know the database size you need before you deploy to SQL Azure, however if you are in a growth scenario you can start out with a web edition database and change it to business edition as it grows. This will save you some money. To change the database edition you can use the ALTER DATABASE Syntax like this:
ALTER DATABASE Test MODIFY (EDITION=’BUSINESS’, MAXSIZE=50GB)
Do you have questions, concerns, comments? Post them below and we will try to address them.
$500/mo for a 50GB DB... PLUS data transfer fees? Geez... that's way too steep!
John: Ok I will bite, what do you think would be a good rate?
Can we use the following syntax to go from the Business class DB to the Web class?
ALTER DATABASE myDB (EDITION=’WEB’, MAXSIZE=5GB)
1GB was too small when we signed up, but 10GB is a bit overkill. Are there any hardware differences between the web and business Azure levels?
1) You can use ALTER DATABASE to go between any combination of editions and sizes
2) There are no differences at this time.
Awesome! Thanks for the quick response!
I just tried to increase the size of my database using the Alter statement and got an error message. My database is called TestDb and I used:
ALTER DATABASE TestDb (EDITION=’BUSINESS’, MAXSIZE=50GB)
The error was
Incorrect syntax near '('.
Any idea what the problem is?
Never mind - I found the answer. The correct command is:
ALTER DATABASE TestDb MODIFY (MAXSIZE=50GB)
My database was already business edition.
Jane: Thanks for pointing out that error in the blog post. I have made a modification in the main text.
I think this is great!
Now we can start using this for our online Portal without worrying about scale up. Im really amazed at how fast SQL Azure is - but is there any benchmark for this?
I mean, is there any limit beyond which i will start facing performance bottlenecks in SQL Azure (think high read scenarios).
Roopesh: I can't think of one product or service in computer science that doesn't have limits. Yes, SQL Azure does have limits.
And going in the other direction, to query the EDITION and MAXSIZE values, use the built-in function DATABASEPROPERTYEX()
DATABASEPROPERTYEX ( DB_Name() , 'Edition' ) as [Edition],
DATABASEPROPERTYEX ( DB_Name() , 'MaxSizeInBytes') as [MaxSizeInBytes]
In the case of truncating/archiving/removing data to free up space there is a potential delay of up to 15 minutes before the space is available and the database is usable again - msdn.microsoft.com/.../ee336245.aspx
Is the same delay an issue when altering the database to automatically resize from code? If I run the command to resize the database how long will it take to process and make the space and database usable?