SQL Azure is a database service in the cloud on Microsoft’s Windows Azure platform well-suited for web facing database applications as well as a relational database in the cloud.
The present version mostly deals with the component analogous to a database engine in a local, on-site SQL Server. Future enhancements will host the other services such as Integration Services, Reporting Services, Service Broker, and any other yet-to-be defined services. Although these services are not hosted in the cloud, they can leverage data on SQL Azure to provide support. SQL Server Integration Services can be used to a great advantage with SQL Azure for data movement, and highly interactive boardroom quality reports can be generated using SQL Azure as a backend server.
Infrastructure features
SQL Azure is designed for peak workloads by failover clustering, load balancing, replication, and scaling out, which are all automatically managed at the data center. SQL Azure’s infrastructure architecture is fashioned to implement all of these features.
High availability is made possible by replicating multiple redundant copies to multiple physical servers thus, ensuring the business process can continue without interruption. At least three replicas are created; a replica can replace an active copy facing any kind of fault condition so that service is assured. At present, the replicated copies are all on the same data center, but in the future, geo-replication of data may become available so that performance for global enterprises may be improved. Hardware failures are addressed by automatic failover.
Enterprise data centers addressed the scaling out data storage needs, but incurred administrative overheads in maintaining the on-site SQL Servers. SQL Azure offers the same or even better functionality without incurring administrative costs.
How different is SQL Azure from SQL Server?
SQL Azure (version 10.25) may be viewed as a subset of an on-site SQL Server 2008 (version 10.5) both exposing Tabular Data Stream (TDS) for data access using T-SQL. As a subset, SQL Azure supports only some of the features of SQL Server and the T-SQL feature set. However, more of T-SQL features are being added in the continuous upgrades from SU1 to SU5. Since it is hosted on computers in the Microsoft Data Centers, its administration—in some aspects—is different from that of an on-site SQL Server.
SQL Azure is administered as a service, unlike on-site servers. The SQL Azure server is not a SQL Server instance and is therefore administered as a logical server rather than as a physical server. The database objects such as tables, views, users, and so on are administered by SQL Azure database administrator but the physical side of it is administered by Microsoft on its data centers. This abstraction of infrastructure away from the user confers most of its availability, elasticity, price, and extensibility features. To get started with SQL Azure, you must provision a SQL Azure Server on Windows Azure platform as explained in the After accessing the portal subsection, later in the article.
SQL Azure provisioning
Provisioning a SQL Azure Server at the portal is done by a mere click of the mouse and will be ready in a few minutes. You may provision the storage that you need, and when the need changes, you can add or remove storage. This is an extremely attractive feature especially for those whose needs start with low storage requirements and grow with time. It is also attractive to those who may experience increased load at certain times only.
SQL Azure databases lie within the operational boundary of the customer-defined SQL Azure Server; it is a container of logical groupings of databases enclosed in a security firewall fence. While the databases are accessible to the user, the files that store the relational data are not; they are managed by the SQL Azure services.
A single SQL Azure Server that you get when you subscribe, can house a large number (150) of databases, presently limited to the 1 GB and 10 GB types within the scope of the licensing arrangement.
• What if you provision for 1 GB and you exceed this limit?
Then either you provision a server with a 10 GB database or get one more 1 GB database. This means that there is a bit of due diligence you need to do before you start your project.
• What if the data exceeds 10 GB?
The recommendation is to partition the data into smaller databases. You may have to redesign your queries to address the changed schema as cross-database queries are not supported. The rationale for using smaller databases and partitioning, lies in its agility to quickly recover from failures (high availability/fault tolerance) with the ability to replicate faster while addressing the issue of covering a majority of users (small business and web facing). However, responding to the requests of the users, Microsoft may provide 50 GB databases in the future (the new update in June 2010 to SQL Azure Services will allow 50 GB databases).
• How many numbers of SQL Azure Servers can you have?
You can have any number of SQL Azure Servers (that you can afford) and place them in any geolocation you choose. It is strictly one server for one subscription. Presently there are six geolocated data centers that can be chosen. The number of data centers is likely to grow. Best practices dictate that you keep your data nearest to where you use it most, so that performance is optimized. The SQL Azure databases, being relational in nature, can be programmed using T-SQL skills that are used in working with on-site SQL Servers. It must be remembered though, that the SQL Azure Servers are not physical servers but are virtual objects. Hiding their physical whereabouts but providing adequate hooks to them, helps you to focus more on the design and less on being concerned with files, folders, and hardware problems. While the server-related information is shielded from the user, the databases themselves are containers of objects similar to what one finds in on-site SQL Servers such as tables, views, stored procedures, and so on. These database objects are accessible to logged on users who have permission.
After accessing the portal
To get started with SQL Azure Services, you will need to get a Windows Azure platform account, which gives access to the three services presently offered. The first step is to get a Windows Live ID and then establish an account at Microsoft’s Customer Portal. In this article, you will be provisioning a SQL Azure Server after accessing the SQL Azure Portal.
Server-level administration
Once you are in the portal, you will be able to create your server for which you can provide a username and password. You will also be able to drop the server and change the password. You can also designate in which of the data centers you want your server to be located. With the credentials created in the portal, you will become the server-level principal; the equivalent of sa of your server. In the portal, you can also create databases and firewall fences that will only allow users from the location(s) you specify here. The user databases that you create here are in addition to the master database that is created by SQL Azure Services; a repository of information about other databases. The master database also keeps track of logins and their permissions. You could get this information by querying the master for sys.sql_logins and sys.database views.
If you are planning to create applications, you may also copy the connection strings that you would need for your applications, which are available in the portal. You would be typically using the Visual Studio IDE to create applications. However, SQL Azure can be used standalone without having to use the Windows Azure service. Indeed some users may just move their data to SQL Azure for archive.
Once you have provisioned a server, you are ready to create other objects that are needed besides creating the databases. At the portal, you can create a database and set up a firewall fence, but you will need another tool to create other objects in the database.
Setting up firewall rules
Users accessing SQL Azure Server in the Cloud need to go through two kinds of barriers. Firstly, you need to go through your computer’s firewall and then go in through the firewall that protects your SQL Azure Server. The firewall rules that you set up in the portal allow only users from the location you set up for the rule, because the firewall rules only look at the originating IP address.
By default, there are no firewall rules to start with and no one gets admitted. Firewall rules are first configured in the portal. If your computer is behind a Network Address Translation (NAT) then your IP address will be different from what you see in your configuration settings. However, the user interface in the portal for creating a firewall discovers and displays the correct IP address most of the time.
A workaround is suggested here for those cases in which your firewall UI incorrectly displays your IP Address: http://hodentek.blogspot.com/2010/01/firewall-ip-address-setting-in-sql.html.
Firewalls can also be managed from a tool such as SSMS using extended stored procedures in SQL Azure. They can be managed programmatically as well from Visual Studio.
In order for you to connect to SQL Azure, you also need to open your computer’s firewall, so that an outgoing TCP connection is allowed through port 1433 by creating an exception. You can configure this in your computer’s Control Panel. If you have set up some security program, such as Norton Security, you need to open this port for outgoing TCP connections in the Norton Security Suite’s UI.
In addition, your on-site programs accessing SQL Azure Server and your hosted applications on Windows Azure may also need access to SQL Azure. For this scenario, you should check the checkbox Allow Microsoft Services access to this server in the firewall settings page.
The firewall rule only checks for an originating IP address but you need to be authenticated to access SQL Azure. Your administrator, in this case the server-level principal, will have to set you up as a user and provide you with appropriate credentials.
Administering at the database level
SQL Azure database administration is best done from SSMS. You connect to the Database Engine in SSMS, which displays a user interface where you enter the credentials that you established in the portal. You also have other options to connect to SQL Azure (Chapter 3, Working with SQL Azure Databases from Visual Studio 2010 and Chapter 4, SQL Azure Tools). In SSMS, you have the option to connect to either of the databases, the system-created master or the database(s) that you create in the portal. The Object Explorer displays the server with all objects that are contained in the chosen database. What is displayed in the Object Explorer is contextual and the use of the USE statement to change the database context does not work. Make sure you understand this, whether you are working with Object Explorer or query windows. The server-level administrator is the ‘top’ administrator and he or she can create other users and assign them to different roles just like in the on-site SQL Server. The one thing that an administrator cannot do is undertake any activity that would require access to the hardware or the file system.
Role of SQL Azure database administrator
The SQL Azure database administrator administers and manages schema generation, statistics management, index tuning, query optimization, as well as security (users, logins, roles, and so on). Since the physical file system cannot be accessed by the user, tasks such as backing up and restoring databases are not possible. Looking at questions and concerns raised by users in forums, this appears to be one of the less appealing features of SQL Azure that has often resulted in remarks that ‘it is not enterprise ready’. Users want to keep a copy of the data, and if it is a very large database, the advantages of not having servers on the site disappear as you do need a server on-site to back up the data. One suggested recommendation by Microsoft is to use SQL Server Integration Services and bulk copying of data using the SQLCMD utility.
SQL Azure databases
These databases are no different from those of on-site SQL Server 2008 except that the user database node may not have all the nodes of a typical user database that you find in the on-site server. The nodes Database Diagrams, Service Broker, and Storage will be absent as these are not supported. In the case of the system database node, only the master will be present. The master in SQL Azure is a database that contains all information about the other databases.
You can only access the SQL Server with SQL Server Authentication, whereas you have an additional option, Windows Authentication in the case of an on-site SQL Server. All the allowed DDL, DML operations can be programmed using templates available in SSMS. Some of the more common ones, as well as access to the template explorer, which provides a more complete list, are detailed later in the chapter.
User administration and logins
Security is a very important aspect of database administration and it is all the more important in the case of the multi-tenant model used in hosting SQL Azure to control access.
The server-level administrator created in the portal is the top level administrator of SQL Azure Server. While he can create other databases in the portal, he will have to create other database objects including users and their login, using the SSMS.
Server-level administration
The master database is used to perform server-level administration, as the master database keeps records of all logins and of the logins that have permission to create a database. You must first establish a connection to the master database while creating a New Query to carry out tasks to CREATE, ALTER, or DROP LOGINS or DATABASES. The server-related views: sys.sql_logins and sys.databases can be used to review logins and databases. Whenever you want to change the context of a database, you have to login to the database using the Options in the SSMSs UI, Connect to Server.
Creating a database using T-SQL is extremely simple as there are no file references to be specified and certain other features that are not implemented. The following syntax is for creating a database in an on-site SQL Server instance:
CREATE DATABASE database_name
[ON
[ PRIMARY ] [ <filespec> [ ,…n ]
[ , <filegroup> [ ,…n ] ]
[ LOG ON { <filespec> [ ,…n ] } ]
]
[ COLLATE collation_name ]
[ WITH <external_access_option> ]
]
[;]
To attach a database
CREATE DATABASE database_name
ON <filespec> [ ,…n ]
FOR { ATTACH [ WITH <service_broker_option> ]
| ATTACH_REBUILD_LOG }
[;]
<filespec> ::=
{
(
NAME = logical_file_name ,
FILENAME = { ‘os_file_name’ | ‘filestream_path’ }
[ , SIZE = size [ KB | MB | GB | TB ] ]
[ , MAXSIZE = { max_size [ KB | MB | GB | TB ] | UNLIMITED } ]
[ , FILEGROWTH = growth_increment [ KB | MB | GB | TB | % ] ]
) [ ,…n ]
}
<filegroup> ::=
{
FILEGROUP filegroup_name [ CONTAINS FILESTREAM ] [ DEFAULT ]
<filespec> [ ,…n ]
}
<external_access_option> ::=
{
[ DB_CHAINING { ON | OFF } ]
[ , TRUSTWORTHY { ON | OFF } ]
}
<service_broker_option> ::=
{
ENABLE_BROKER
| NEW_BROKER
| ERROR_BROKER_CONVERSATIONS
}
Create a database snapshot
CREATE DATABASE database_snapshot_name
ON
(
NAME = logical_file_name,
FILENAME = ‘os_file_name’
) [ ,…n ]
AS SNAPSHOT OF source_database_name
[;]
Look how simple the following syntax is for creating a database in SQL Azure:
CREATE DATABASE database_name
[(MAXSIZE = {1 | 10} GB )]
[;]
However, certain default values are set for the databases, which can be reviewed by issuing the query after creating the database:
SELECT * from sys.databases
Managing logins
After logging in as a server-level administrator to master, you can manage logins using CREATE LOGIN, ALTER LOGIN, and DROP LOGIN statements. You can create a password by executing the following statement for example, while connected to master:
CREATE LOGIN xfiles WITH PASSWORD = ‘@#$jAyRa1’
You need to create a password before you proceed further. During authentication, you will normally be using Login Name and Password, but due to the fact that some tools implement TDS differently, you may have to append the servername part of the fully qualified server name <servername>.<database name>.<windows>.<net> to the Username like in login_name@<servername>. Note that both <login_name> and <login_name>@<servername> are valid in the Connect to Server UI of SSMS.
Connecting to SQL Azure using new login
After creating a new login as described here, you must confer database-level permissions to the new login to get connected to SQL Azure. You can do so by creating users for the database with the login.
Logins with server-level permissions
The roles loginmanager and dbmanager are two security-related roles in SQL Azure to which users may be assigned, that allows them to create logins or create databases. Only the server-level principal (created in the portal) or users with loginmanager role can create logins. The dbmanager role is similar to the dbcreator role and users in this role can create databases using the CREATE DATABASE statement while connected to the master database.
These role assignments are made using the stored procedure sp_addrolemember as shown here for users, user1 and user2. These users are created while connected to master using, for example:
CREATE USER User1 FROM LOGIN ‘login1’;
CREATE USER User2 FROM LOGIN ‘login1’;
EXEC sp_addrolemember ‘dbmanager’, ‘User1’;
EXEC sp_addrolemember ‘loginmanager’, ‘User2’;
Migrating databases to SQL Azure
As most web applications are data-centric, SQL Azure’s databases need to be populated with data before the applications can access the data. More often, if you are trying to push all of your data to SQL Azure, you need tools. You have several options, such as using scripts, migration wizard, bulk copy (bcp.exe), SQL Server Integration Services, and so on. More recently (April 19, 2010 update) Data-tier applications were implemented for SQL Azure providing yet another option for migrating databases using both SSMS as well as Visual Studio.
Troubleshooting
There may be any number of reasons why interacting with SQL Azure may not always be successful. For example, there may just be a possibility that the service level agreement that assures 99.99 percent may not actually be possible, there may be a problem of time-out that is set for executing a command, and so on. In these cases, troubleshooting to find out what might have happened becomes important. Herein, we will see some of the cases that prevent interacting with SQL Azure and the ways and means of troubleshooting the causes.
• Login failure is one of the common problems that one faces in connecting to SQL Azure. In order to successfully login:
°°You need to make sure that you are using the correct SSMS.
°°Make sure you are using SQL Server Authentication in the Connect to Server dialog box.
°°You must make sure your login name and password (type in exactly as you were given by your administrator) are correct. Password is case sensitive. Sometimes you may need to append server name to login name.
°°If you cannot browse the databases, you can type in the name and try.
If your login is not successful, either there is a problem in the login or the database is not available.
If you are a server-level administrator you can reset the password in the portal. For other users the administrator or loginmanager can correct the logins.
• Service unavailable or does not exist.
If you have already provisioned a server, check the following link: http:// http://www.microsoft.com/windowsazure/support/status/servicedashboard. aspx, to make sure SQL Azure Services are running without problem at the data center.
Use the same techniques that you would use in the case of SQL Server 2008 with network commands like Ping, Tracert, and so on. Use the fully qualified name of the SQL Azure Server you have provisioned while using these utilities.
• You assume you are connected, but maybe you are disconnected.
You may be in a disconnected state for a number of reasons, such as:
°°When a connection is idle for an extended period of time
°°When a connection consumes an excessive amount of resources or holds onto a transaction for an extended period of time
°°If the server is too busy
Try reconnecting again. Note that SQL Azure error messages are a subset of SQL error messages.
T-SQL support in SQL Azure
Transact-SQL is used to administer SQL Azure. You can create and manage objects as you will see later in this chapter. CRUD (create, read, update, delete) operations on the table are supported. Applications can insert, retrieve, modify, and delete data by interacting with SQL Azure using T-SQL statements.
As a subset of SQL Server 2008, SQL Azure supports only a subset of T-SQL that you find in SQL Server 2008.
The supported and partially supported features from Microsoft documentation are reproduced here for easy reference.
The support for Transact-SQL reference in SQL Azure can be described in three main categories:
• Transact-SQL language elements that are supported as is
• Transact-SQL language elements that are not supported
• Transact-SQL language elements that provide a subset of the arguments and options in their corresponding Transact-SQL elements in SQL Server 2008
The following Transact-SQL features are supported or partially supported by SQL Azure:
• Constants
• Constraints
• Cursors
• Index management and rebuilding indexes
• Local temporary tables
• Reserved keywords
• Stored procedures
• Statistics management
• Transactions
• Triggers
• Tables, joins, and table variables
• Transact-SQL language elements
• Create/drop databases
• Create/alter/drop tables
• Create/alter/drop users and logins
• User-defined functions
• Views
The following Transact-SQL features are not supported by SQL Azure:
• Common Language Runtime (CLR)
• Database file placement
• Database mirroring
• Distributed queries
• Distributed transactions
• Filegroup management
• Global temporary tables
• Spatial data and indexes
• SQL Server configuration options
• SQL Server Service Broker
• System tables
• Trace flags
T-SQL grammar details are found here: http://msdn.microsoft.com/en-us/ library/ee336281.aspx.
Microsoft encourages failover clustering to provide fault tolerance and redundancy for Exchange Server. However, neither the client access server nor the hub transport server roles support failover clustering in Exchange 2010 — only mailbox servers may be clustered.
Windows Management and Scripting 