Now, you will create a user database in SQL Azure and set up firewall rules so that you can access SQL Azure from your ground-based, on-site location. While creating a user database makes use of the SQL Azure portal, these can be done from your ground-based SSMS, or other utilities that support T-SQL. Database and firewall rules can also be programmatically created from Visual Studio 2008 SP1 or Visual Studio 2010 (Beta 2 as well as RC).
As seen, when we accessed SQL Azure Services from the portal, the master database is created during the provisioning process in the portal and user databases can also be created in the portal.
SQL Azure presently provides creating two Types of databases, a web type of 1 GB and a business type of 10 GB. Each provisioned SQL Azure Server can have a total of 150 databases including the master.
Following are the steps to create a database in the portal:
- Click on the Databases tab in the SQL Azure Server. Click on Create Database.
- This pops up a small opaque dialog, as shown in the following screenshot, that allows you to create a 1 GB or 10 GB database, which requires a name. You may provide a name of your choice. You may also cancel this operation if you like.
- Type in a name and click Create after picking the database type from the drop-down.
- Herein, a 1 GB database with the name Bluesky was chosen. Sometimes when you click create, you may get an error message telling you to try again. When successful, you will see that the 1 GB, Bluesky database is added to your list of databases, which was initially empty, as shown in the next screenshot. At this point, you can create other databases of either type from here.
- When you need to access a SQL Azure database programmatically from an application, you need Connection Strings to establish a connection. These strings can be displayed by using the Connection Strings button at the bottom of the previous screen.
- Click on Bluesky to highlight it and click on Connection Strings.
- This displays the following opaque screen. There are two ways in which you can connect, using ODBC or ADO.NET. Review this carefully and notice that for SQL Azure, a SQL Server Login is required with a User ID and a Password as specified in these strings. All author-specific items are shown pixelated.
- You can easily drop a database as well by just clicking Drop Database after highlighting the database.
You can alter the database size (up and down) by using the following statement, which you can run only in SSMS:
ALTER DATABASE database_name
{
MODIFY (MAXSIZE = {1 | 10} GB)
}
[;]
More recently, after the release of SQL Server 2008 R2, SQL Azure databases can also be renamed.
If you were to access the Bluesky database from your SSMS now, you would not be able to connect to the server and you would get an error message as shown earlier. This is because no firewall rules have been set up. We will now set up an IP address from which SQL Azure can be accessed. It may be noted that creating a user database is not a necessary condition for setting up a firewall fence. It can be done soon after provisioning the server by completing the following steps:
- Click the Firewall Settings tab. Click the Add Rule button to open an opaque window.
- You will notice that the window already has an IP address (Your IP address: 68.83.182.13). This IP address happens to be the IPv4 address of the author's service provider. Although the screen seems to require a range, you can set up a rule by just typing the same IP address in the IP Range boxes and a Name (of your choice), as shown in the following screenshot:
- Although this should take effect in five minutes, longer durations have been experienced.
- Then, click on Submit. The IP setting tabbed page gets updated, as shown in the following screenshot. With this setting, you will be able to connect to the SQL Azure Server from your SSMS, or other similar utility.
- Although just one rule was added, you can add any number of rules each with a name and a range. The following screenshot shows a number of IP addresses set up for a server on SQL Azure (December 2009 version), this feature remains unchanged in this version of SQL Azure. You can make the SQL Azure accessible from any IP address by using a very wide range. However, you must use caution to restrict the range to those you would use. Placing a check mark for the checkbox Allow Microsoft Services access to this server resulted in the rule MicrosoftServices with the range 0.0.0.0 - 0.0.0.0.
- The rule TakaHana in the previous screenshot was added to enable access from a location in Japan. Note that some ISPs reveal only the link-local IPv6 addresses (as happened in the case of the location in Japan) and not an IPv4 address. However, the Firewall Settings UI is only configured for IPv4, and for link-local addresses; it may come up with a wrong IP address, as shown here with my notes:
In this case, the easiest way to get the corresponding IPv4 is to obtain it by connecting to SQL Azure from SSMS as explained here: http://hodentek.blogspot.com/2010/01/firewall-ip-address-setting-in-sql.html and not rely on what the Firewall Settings UI displays.
Also note that it is just as easy to edit or delete a rule using the buttons at the bottom of this screen. When you try editing the rule you will see that you can only change the IP address range and not the name.
After setting up a database and an IP address you will be able to connect to SQL Azure from your local machine using SSMS as described next.
The following are the IP ranges of the various data centers:
- United States (North/Central): 65.52.0.0/21, 65.52.8.0/21, 65.52.16.0/21, 65.52.24.0/21, 207.46.203.64/27, 207.46.203.96/27, 207.46.205.0/24
- Europe (North): 94.245.88.0/21, 94.245.104.0/21, 65.52.64.0/21, 65.52.72.0/21, 94.245.114.0/27, 94.245.114.32/27, 94.245.122.0/24
- Asia (Southeast): 111.221.80.0/21, 111.221.88.0/21, 207.46.59.64/27, 207.46.59.96/27