Database Reference
In-Depth Information
other objects using T-SQL. Some of this information was covered briefly in Chapter 1, but this section
reviews it as a primer for upcoming chapters.
Databases
When you create databases in a non-Azure environment, you have to know and specify a lot in the
CREATE
statement. For example, you must provide the path and physical object names of the database
and log files, as well as the database size and optionally the maximum size and file growth. A
CREATE
DATABASE
statement in a non-Azure environment might look like the following:
CREATE DATABASE [MyDatabase] ON PRIMARY
( NAME = N'MyDatabase', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDatabase.mdf' , SIZE = 32768KB , MAXSIZE =
UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'MyDatabase_log', FILENAME = N'C:\Program Files\Microsoft SQL
Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\MyDatabase_log.ldf' , SIZE = 92864KB , MAXSIZE =
2048GB
, FILEGROWTH = 10%)
GO
As you've learned in the past few chapters, with Azure you don't have to worry about hardware and
where objects are located and how hardware is provisioned, which makes statements such
CREATE
DATABASE
much simpler. In Chapter 1, you learned that you must connect to the master database first;
then, all you need to do is issue the following statement:
CREATE DATABASE MyDatabase
You can optionally supply two parameters with the
CREATE DATABASE
statement:
MAXSIZE
, which
specifies the maximum size of the database; and
EDITION
, which specifies the edition of the database
(Web or Business). Here's an example:
CREATE DATABASE MyDatabase (MAXSIZE= 10 GB, EDITION= 'Business')
In the current release of SQL Azure, two database sizes are available for the Web edition: 1GB and
5GB. For the Business edition, available sizes range from 10GB to 50GB, in increments of 10GB. If
MAXSIZE
is set to a value of 1GB or 5GB and
EDITION
isn't specified, the database edition is automatically
set to Web. If
MAXSIZE
is set to a value between 10BG and 50GB and
EDITION
isn't specified, the database
edition is automatically set to Business. If neither
MAXSIZE
nor
EDITION
is specified, a Web database of
1GB is created.
SQL Azure doesn't support additional arguments that you're used to using when working with SQL
in a local environment. For example, SQL Azure doesn't support parameters that relate to working with
physical files, such as
filespec
and
filegroup
. You also can't attach or detach a database. Other
unsupported arguments are covered in depth in Appendix B.
Logins and Users
Now that you've created a database, you need to grant access to other users. This section discusses
creating logins and users, and allowing others to access the SQL Azure environment. Creating logins and
users is similar to doing so locally; but as you found out when creating databases, there are T-SQL
limitations.
When granting access to another user, you must first create the login for that user. Then, you create
the user based on that login. A login is used for authentication; a user account is used for database
access and permission validation.
