Apart from the four system databases, we already knew, Microsoft has introduced a new database…
Once you finally signed up for the SQL Azure account, you can start creating your database. There are some major differences from an onpremises database that you have to remember.
1. First of all, the USE command is not supported in SQL Azure so you have to change the connection to switch between the databases.
2. Second, you need to be in the master database to create a database and a login. After the database is created, you need to change connection to create a database user.
3. Third, SQL Azure only support the CREATE DATABASE statement partially and you have some new options to use
4. Fourth, as SQL Azure doesn’t support all options, you’ll get some defaults, like collation, recovery model, read committed snapshot etc.
But wait a minute!
Do you really get a default collation without the possibility to change it? Well, that issue is solved in the December 2011 release. Okey, you want to start using SQL Azure and you are ready to get going.
First of all, you have to sign up for a SQL Azure account and enter your credit card information at this site: http://www.microsoft.com/windowsazure/sqlazure/
Lets create your first database (with a login and a database user). You can connect to your SQL Azure server through SQL Server management studio or the management portal. Personally I preferre SQL server management studio that has a more powerfull GUI with autocomplete etc. –create a login, must be a strong password CREATE LOGIN [azurelogin] WITH PASSWORD=N’Wordpass123′; GO –create the database CREATE DATABASE MyDB (MAXSIZE=1 GB, EDITION=‘WEB’); — maxsixe <=150 GB, edition = web/business, no other options are availiable GO SELECT * FROM sys.databases; — take a look at all the default options defined
As you can see, there are a lot of settings that you can’t change and you get by default. One of the nice benefits with the SQL Azure database it that you don’t need to bother about the database files and where they should be placed for optimal performance. On the other hand, you have absolutely no control over the files or file groups or even the autogrowth sizes and you have to depend on the SQL Azure platform to handle your database as good as possible. Next step is to create a database user, but as you can’t use the USE statement in SQL Azure, you actually need to open a second connection to the newly created database. In the new connection you can create the database user from your login. CREATE USER [sqlug_user] FROM LOGIN [sqlug]; — this has to be executed in a connection connected to the right database Simple as that, you have created your first database and assigned a new database user. Tomorrow you can read another SQL Azure tip.
If you like to know more about any version of SQL server, feel free to contact us at SQL service and our SQL server consultants will help your.