Johan Åhlén

Johan Åhlén's blog about life, universe and everything.

SQL Azure some more tips and tricks

This article is a follow up to my earlier article with tips on SQL Azure.

Data-tier applications (DACs)

There are two main options on how to deploy your database solutions to SQL Azure, unless you want to do it manually. These options are to use Data-tier applications or to use the SQL Azure Migation Wizard. The latter is an open source tool that copies your database (including the data) in any direction. Data-tier applications however is a new feature in SQL Server 2008 R2 and SQL Azure that enables you to develop a database in Visual Studio like you would develop a C# applications or similar. Existing databases can easily be converted to a Data-tier applications as long as you don't use any features that are not supported (such as XML).

Deploying a Data-tier application in SQL Azure

You can't deploy it from Visual Studio. Instead build your project, connect to SQL Azure through SQL Server Management Studio, right-click on your instance and choose "Deploy Data-tier Application..." in the menu. Click Next in the wizard and browse to your .dacpac file.

Upgrading a Data-tier application in SQL Azure is a manual process. You'll need to deploy it under a new name, copy the data manually from the old database and rename it. How to rename a SQL Azure database? Issue the T-SQL statement: "ALTER DATABASE database_name MODIFY NAME = new_database_name".

To add logins and users in your Data-tier application you need to create the login with the MUST_CHANGE password option.

CREATE LOGIN [username] WITH PASSWORD='' MUST_CHANGE, CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

You map it to a database user in the same way as "ordinary" SQL Server:

CREATE USER [username] FOR LOGIN [username] WITH DEFAULT_SCHEMA=[dbo]

Then you need to add it to one or more roles by executing this T-SQL statement within your database (make sure you don't execute it within the master database):

EXEC sp_addrolemember N'my_db_role', N'username'

Finally you need to assign a password and enable the login by executing the following T-SQL statement on the master database.

ALTER LOGIN [username] WITH PASSWORD='mypassword'
GO
ALTER LOGIN [username] ENABLE

Changing the edition of your SQL Azure database or increasing the size

You can increase or decrease the size of the database or change the edition at any time. Just issue the following T-SQL statement on the master database.

ALTER DATABASE database_name
MODIFY (MAXSIZE = x GB, EDITION = 'x')

Edition can currently be either web or business. Database size can currently be either 1, 5, 10, 20, 30, 40 or 50 GB.

Resizing or changing the edition of your database takes almost no time so you can easily start with a smaller database and increase it on demand (like you should be able to with a cloud database).

Posted: Nov 05 2010, 08:23 AM by jahlen
Filed under: ,

Comments

No Comments