Windows Azure Platform Experience (2): SQL Azure

Windows Azure Platform has Windows Azure, SQL Azure, Azure appfabric three parts.

SQL Azure is a cloud relational database. Your data: Manage anytime, anywhere. SQL Azure is a fully relational database in the cloud.

In this section we experience SQL Azure, the prerequisite software:

• Microsoft SQL Server R2
• Windows Azure Platform Account

There are several steps to this experience, namely:

• Initialize SQL Azure
• Connect to SQL Azure
• Configure SQL Azure Database security
• Create a database object in SQL Azure

Initialize SQL Azure

First we use the account login SQL Azure portal, the URL is http://sql.azure.com, you can see the following page. We click on the project name.

The terms of Use page appears. After reading, if you agree to accept these terms, click "I accept" and start using the actual SQL Azure database.

The Create Server page appears. We'll start by creating an actual server. In the case of server types, the server we are familiar with is probably a physical server or a virtual server, where we create a virtual server, the SQL Azure server. This means that it is a combination of databases that are logically grouped together, rather than physically combined. Specify the administrator user name password and the database group in which SQL Azure datacenter you want to create the server. If you are in mainland China, you can choose East Asia. I select the "East Asia" Data Center and click Create Server.

When I am done, the Server Details page appears, which shows the details of the login to this server and the existing database as well as the firewall configuration. The top of the page provides a unique ID and a combination of ". Database.windows.net" is the server address. At the bottom of the page, we can see all the databases that have been created. There is now only a default SQL master database for storing other database records, and we have not created any databases yet. Then all the databases I created are in this server account.

Click on "CREATE Database", and the following page appears, where I will name it "Lyjdb" and choose the version type and database size. These are the choices you buy, the Web version has 1G and 5G two capacity, business version has 10G, 20G, 30G, 40G, 50G large capacity. Since there is no limit to this account, I choose the minimal configuration experience.

Once created, we can click on the "Connection Strings" connection string. SQL Azure provides us with two kinds of database connections:

We clicked the "Test Connectivity" button at the bottom, entered the username and password, clicked on the connection and prompted the following error box. Because by default, SQL Azure security settings do not allow any computer, server, or client to connect to SQL Azure.

At this point we click on the "Firewall Settings" (Firewall settings) tab.

You will find that the SQL Azure security settings do not allow the Microsoft service to connect to the server or database by default, and I select "Allow Microsoft Services access to this service" ( Allow Microsoft services to access this server) check box to allow connections. The site refreshes, and a new rule is created for me, a specific microsoftservices entry appears, allowing the Microsoft service itself to connect so that we can connect.

Return to the Databases tab, click the Test Connectivity button at the bottom, enter your user name and password, and you can start the test connection and see that the connection is successful.

Connect to SQL Azure1. Firewall settings

Open SQL Server Management Studio R2, log in with the specified user name and password, and connect to this server. Note You must use the R2 version to connect to the SQL Azure database in the cloud. However, signing in to SQL Azure is slightly different from signing in to a login with an on-premises SQL database method. In addition to specifying the user name you need to add the "@" symbol, then the server name is the only part of the ID.

Click the "Connect" button and we try to connect. The following dialog box prompts the connection to fail because SQL Azure security settings do not allow any computer, server, or client to connect to SQL Azure by default.

We need to configure the firewall, go back to the portal, and go to the "Firewall Settings" (Firewall Settings) tab, configure the firewall rule to allow a specific connection to pass, and create a rule for my local. This allows us to actually connect locally through SQL Server Management Studio R2. Enter the rule name and IP address or IP range. The system has identified the IP address I am connecting to.

After the creation is complete, a new rule appears in the firewall rule.

2. Interacting with SQL Azure

Switch to SQL Server Management Studio R2 try to connect again, this time everything is set up, and now the connection is successfully implemented, it connects the actual SQL Azure instance. Please note that this server has a slightly different icon. It shows a small blue database symbol indicating that this is an azure connection, not a standard on-premises SQL Server connection. Go to the database group and you'll see the LYJDB database we just created.

I can now interact with SQL Server Management Studio R2 and SQL Azure database in much the same way I use an on-premises database, which is how I used to work in Management studio R2. However, the design experience is slightly different because we are using a cloud-based database. Therefore, it only has some of the features of SQL Server 2008, for example, we cannot get a drag-and-drop design surface. Most are using the actual SQL scripting language to specify the schema of the table.

Right-click on the database and perform regular script operations, such as the Select command. Executing this command, and viewing the results shown at the bottom, is very similar to our use of an on-premises database. So, for those standard operations, this is very similar to the way I used and executed it.

3. Connect to the master database

We perform a "SELECT @ @version" Query to view the version of SQL Azure. You can see that it returns a message: SQL Azure is derived from Microsoft SQL Server 2008.

We used the master database to query the sys.databases view, and we were able to see all the databases created on our server.

4. Create a new database

We use management Studio R2 to create a new database in SQL Azure. This is a very simple step. In SQL Azure, we use a simple CREATE DATABASE execution statement that will immediately create a new database.

Execute "CREATE DATABASE yjingleedb"

Check the database on the server again, and note that the Yjingleedb database we just created is in this list.

5. Connect to a new database

In order to connect to the new database, we must disconnect the current connection and reopen the new connection. Because in SQL Azure, our databases are not necessarily stored on the same physical server.

This is a bit different from SQL Server, where we can use the using statement to change the database, in SQL Azure, we need to reconnect to the physical server cluster node client to make sure that reconnecting to SQL Azure is correct. We close the Query tab and click the Change connection icon.

Login with the same account, because we want to connect to the database, so we need to click the option button, specify the "Yjingleedb" database.

The connection succeeds, we call the Db_name () function to see the current database name.

Configure SQL Azure Database security

We will configure the security of the Yjingleedb database. Learn how to create and configure a new logged-on user for a database.

We use the server username and password to log in to the default SQL Azure database and create a login user.

We then use the server user name and password to log back in to the Yjingleedb database. We create a login user for the Yjingleedb database and add the user to the db_owner role, that is, the database has operation rights. The following two lines of script are executed sequentially.

We'll create it and use this new account to log into the Yjingleedb database.

Executes a query that validates the current user.

Create a database object in SQL Azure 1. Create a simple table

We create a simple table with only a single column of type int. It is important to note that the tables inside SQL Azure must have a clustered index. If not, you cannot insert any data. The clustered index is not necessarily a primary key column. For performance reasons, it is best to be on a different column.

Then insert three data, then execute the query and retrieve the data.

2. Creating complex tables and indexes

This time I create a Customer table, then add an index, and finally insert a piece of data and query it.

3. Check the query plan

Let's look at querying data from the Customer table to experience SQL Azure's query plan. We first perform some T-SQL insert 10000 rows of data comparison differences.

Query plan for 1 rows of data:

Query plan for 10000 rows of data

Call the "Ctrl+l" Visualization:

Copyright NOTICE: This article for Bo Master http://www.zuiniusn.com original article, without Bo Master permission not reproduced.

Windows Azure Platform Experience (2): SQL Azure