Go to SQL azure

Cloud data

SQL azure Development

Microsoft Windows azure provides multiple data storage options, including windows azure storage and SQL azure. You can use either of the two methods in a specific project. Windows azure storage currently contains three storage structures: tables, queues, and blob.

SQL Azure is a relational data storage service in the cloud. One advantage of this product is the ability to use familiar relational development models, including many standard SQL Server languages (T-SQL), tools, and utilities. Of course, using easily understandable relational structures (such as tables, views, and stored procedures) in the cloud will also improve the efficiency of developers in using this new platform. Other advantages include reducing actual database management work (including server settings, maintenance, and security protection), as well as built-in support for reliability, high availability, and scalability.

This article will not introduce windows azure storage, nor compare these two storage modes. For more information about these optional storage methods, see Julie Lerman's "data points" Column (msdn.microsoft.com/magazine/ff796231) Written in July 2010 ). Note that the windows azure table is not a relational table. The main purpose of this emphasis is to help you understand the features included in SQL azure.

This article will discuss the differences between SQL Server and SQL azure. You need to have an in-depth understanding of these differences in order to make rational use of your current knowledge of SQL Server to handle SQL azure as a data source project.

If you are a beginner in cloud computing, you need to learn about the background of Windows azure before continuing to read this article. Start with the msdn developer cloud center (msdn.microsoft.com/ff0000142.

SQL azureGetting started

To start using SQL Azure, you must first set up an account. If you have subscribed to msdn, you can use up to three SQL azure databases (each with a maximum of 1 GB) as the developer sandbox within 16 months (for details, see msdn.microsoft.com/subscriptions/ee461076 ). To register a general SQL azure account (storage and data transmission fees are required), visit microsoft.com/windowsazure/offers /.

After registering an SQL azure account, the easiest way to access it is through the web portal SQL .azure.com. You must use the Windows Live ID associated with the Windows azure account to log on. After logon, you can create and install your server and start to develop applications.

Figure1Shows the SQL azure Web Management Portal example, where you can see the server and its associated database. You will notice that the web portal also contains a tab for managing firewall settings installed in your SQL azure.

 

Figure 1SQL azureDatabase summary information

When you create an SQL azure Server installation for the first time, a random string is assigned to it as the server name. You can also set the Administrator's username, password, geographical location of the server, and firewall rules when creating a server. You can select the installation location for SQL azure when creating a server. The system provides you with a location (Data Center) list from which you can select a location. If your application front-end is built in Windows Azure, You can associate the application installation with the SQL azure installation to place the two in the same geographic location.

By default, you cannot access your server. Therefore, you must create firewall rules for all client IP addresses. SQL azure uses port 1433, so make sure this port is also open to your client application. When connecting to SQL Azure, you need to useUsername @ servernameFormat username. SQL azure only supports SQL Server Authentication and does not support Windows authentication. Multiple Active result set (MARS) connections are also supported.

If the connection is inactive for 30 minutes, it will time out. In addition, if a query or transaction is run for a long time, or if the resource usage is excessive, the connection is also disconnected. In your application, the best development criterion for connections is to manually open, use, and close these connections, and add the retry logic for disconnected connections, avoid cache connections due to these behaviors. For more information about the client protocols supported by SQL Azure, see Steve Hale's blog ).

Another best practice is to encrypt your connection string to prevent man-in-the-middle attacks.

If you do not specify the database name in the connection string, you are connected to the primary database by default. In SQL Azure, you do not support using the T-SQL statement use to change the database, so you generally need to specify the database to be connected in the connection string (assuming you want to connect to a database other than the primary database ). The following is an example of an ADO. net connection:

1.            Server=tcp:server.ctp.database.windows.

2.            net; Database=<databasename>; User ID=user@server; Password=password; Trusted_Connection=False; Encrypt=true;

3.          

Set Database

After you successfully connect to the server for installation, you need to create one or more databases. Although you can use the SQL azure portal to create databases, you may want to use other tools, such as SQL Server Management studio 2008 R2. By default, you can create up to 149 databases for each SQL azure Server installation. If you need more databases, you must call the Windows azure business center to increase the limit.

When creating a database, You must select the maximum size. The current size setting (and billing) options are web edition or Business Edition. By default, web edition supports databases with a total size of 1 GB or 5 GB. Business Edition supports a database with a maximum size of 50 GB. The size can be determined by 10 Gb increments, that is, a database with a maximum size of 10 Gb, 20 GB, 30 GB, 40 GB, and 50 GB is supported.

When creating a database, you can use the maxsize keyword to limit the size of the database. After initial creation, you can use the alter database statement to change the size limit or version (web or business ). If you reach the size or capacity limit of the selected version, you will receive error code 40544. The database size does not include the primary database or database logs. For details about the size and pricing, see microsoft.com/windowsazure/pricing/# SQL.

When creating a new SQL azure database, you must realize that you are actually creating three copies of the database. This is intended to ensure high availability. These database copies are completely transparent to you. The new database is displayed as a unit for your use.

After creating a database, you can quickly obtain the database connection string information by selecting a database in the list on the portal and clicking the "connection string" button. You can also click the "test connection" button for the selected database to quickly test the connection through the portal. To make this test successful, you must enable the allow Microsoft Service to connect to this server option on the firewall rules tab on the SQL azure portal.

Create an application

You can use this data source to develop applications after setting accounts, creating servers, creating at least one database, and setting firewall rules to connect to the database.

Unlike windows azure data storage options (such as tables, queues, or blob), when you use SQL azure as the project data source, you do not need to install anything in the development environment. If you are using Visual Studio 2010, you can start directly without additional sdks, tools, or other content.

Although many developers choose to use Windows azure as the frontend and SQL azure as the backend, this configuration is not required. You can use any front-end client. You only need to have a supported Connection Library, such as ADO. Net or ODBC. This includes applications written in Java or PHP. Currently, you cannot connect to SQL azure through OLE DB.

If you use Visual Studio 2010 to develop an application, you can use the features provided in, view or create many types of objects directly from Visual Studio Server resource manager in the selected SQL azure database installation. These objects are tables, views, stored procedures, functions, and synonyms. You can also use this viewer to view data related to these objects. For many developers, it is enough to use Visual Studio 2010 as the main tool to view and manage SQL azure data.Figure2Is the View window of the server resource manager, which displays the local installation of the database and the cloud instance. You will find that the Tree nodes in the two views are slightly different. For example, there is no assembly node in cloud installation, because SQL azure does not support custom assembly.

 

Figure 2InVisual StudioView data connections in server Resource Manager

As mentioned above, another tool that can be used to process SQL Azure is SQL Server Management Studio (SSMs) 2008 R2. Compared with Visual Studio 2010, you can use SSMs 2008 R2 to access more SQL azure database operations. Both tools are used by myself, depending on the operation to be completed. An example of an operation provided by SSMs 2008 R2 and not available in Visual Studio 2010 is to create a new database using a T-SQL script. Another example is that it can easily perform index operations (create, maintain, delete, and so on ).Figure3Shows an example.

 

Figure 3UseSQL Server Management maxcompute 2008 r2ManagementSQL azure

SQL Server 2008 R2 is a data-layer application or DAC. The dac pac object integrates the SQL server or SQL azure database architecture and object in an entity. You can use Visual Studio 2010 (build) or SQL Server 2008 R2 SSMs (extract) to create a DAC from an existing database.

If you want to use Visual Studio 2010 to process DAC, you should first select the SQL server data layer application project type in Visual Studio 2010. In Solution Explorer, right-click the project name and click import data layer application ". The system opens a wizard to guide you through the import process. If you are using SSMs, you can right-click the database you want to use in the object Resource Manager, click "task", and then click "extract data layer application" to create a DAC.

The generated DAC is a compressed file that contains multiple T-SQL and XML files. You can right-click the. dacpac file and click "decompress" to use the content. SQL azure supports deleting, deploying, extracting, and registering DAC Pac,NoThey can be upgraded.

Another tool that can be used to connect to SQL Azure is the latest Community Technology Preview (CTP) version of the tool named "Houston. Houston is a management tool for SQL azure installation. It is based on Silverlight technology and does not require installation. When using Houston to connect to SQL azure for installation, You need to specify the location of the data center (as of this article, these data centers are located in north-central United States, South-central United States, Northern Europe, Central Europe, Asia Pacific and South Asia ).

Houston is in the early beta version and is currently released (for exampleFigure4As shown in. Houston supports processing tables, views, queries, and stored procedures in SQL azure database installations. You can access Houston from the SQL azure lab website at sqlazurelabs.com/houston.aspx.

 

Figure 4UseHoustonManagementSQL azure

Another tool that can be used to connect to the SQL azure database is sqlcmd (msdn.microsoft.com/library/ee336280 ). Although SQL azure supports sqlcmd, it does not support osql command line tools.

UseSQL azure

Now you have connected to SQL azure to install and create a new empty database. So what can I do with SQL azure? Specifically, you may want to know the restrictions on Object creation. After creating these objects, how do you fill these objects with data?

As I mentioned at the beginning of this article, SQL azure provides relational cloud data storage, but it is slightly different from the locally installed SQL Server in some features. Starting from creating an object, let's take a look at the key differences between the two.

You can use familiar methods to create the most common objects in the SQL azure database. All the most common relational objects (including tables, views, stored procedures, indexes, and functions) can be used. However, there are some differences in the object creation process. The following is a summary of these differences:

• SQL azure tables must contain clustered indexes. You can then create a non-clustered index on the selected table. You can create spatial indexes, but cannot create XML indexes.
• Heap tables are not supported.
• Supports CLR geospatial types (such as geography and geometry) and hierachyid data types. Other CLR types are not supported.
• Creating a view must be the first statement in batch processing. In addition, encrypted views (or stored procedures) cannot be created ).
• A function can be a scalar, inline, or multi-statement Table value function, but not a CLR function.

For a complete reference to T-SQL statements supported in the SQL azure section, see msdn.microsoft.com/library/ee336267in msdn.

Before creating an object, remember that if you do not specify another database in the connection string, you will connect to the primary database. In SQL Azure, you cannot use the use (database) statement to change a database. To connect to a database other than the primary database, you must specify the database in the connection string, as described above.

Data migration and Loading

If you plan to use an existing local database as the source data and structure to create SQL azure objects, you only need to use SSMs To Write appropriate DDL scripts and create these objects on SQL azure. Use the generate script wizard and set the "database engine script" option to "SQL azure ".

A simpler script generation method is to use the SQL azure migration wizard, which can be downloaded from codeplex at sqlazuremw.codeplex.com. With this convenient tool, you can generate a script to create an object, or use bcp.exe to load data through large-capacity replication.

You can also design an SQL server integration services (SSIS) package to extract and run DDM or DDL scripts. If you are using SSIs, the most common method is to design a DDL extraction package from the source database and write the DDL script for SQL azure, then execute the script on one or more SQL azure installations. You can also choose to load the relevant data as part of the package execution path. For more information about using SSIs, see msdn.microsoft.com/library/ms141026.

For DDL creation and data migration, pay attention to the SQL azure Data Synchronization Service (sqlazurelabs.com) in the CTP version ). You can see the actual effect of this service in the "use SQL azure Data Synchronization Service to provide geographic location replication for SQL azure Database" video on channel 9th. The address is tinyurl.com/2we4d6q. Currently, the SQL azure Data Synchronization Service uses the synchronization group (hub and Member Server) and the scheduled synchronization to work at various table levels in the database to be synchronized.

You can use Microsoft sync framework power pack for SQL azure to synchronize data between data sources and SQL azure installations. As of this article, the tool is still in the CTP version, which can be obtained from tinyurl.com/2ecw.ku. If you use this framework to perform subsequent or continuous data synchronization for the application, you may also want to download the relevant SDK.

What if your source database is larger than the maximum size of the SQL azure database installed? That is to say, it is greater than the absolute maximum value of 50 GB in Business Edition or greater than the smaller size limit set based on other program options.

Currently, if the size of your database exceeds the limit of the program, you must manually partition (or break) the data ). Microsoft has announced that it will launch an automatic partitioning tool for SQL azure in the future. Also, note that SQL azure does not support T-SQL Table Partitioning. You can use this free utility Enzo SQL shard (enzosqlshard.codeplex.com) to partition your data source.

Note the differences between SQL Server and SQL azure in data loading and data access.

A recently added feature is to use the "Database Replication" command to copy the SQL azure database. The syntax for cross-Server replication is as follows:

1.            CREATE DATABASE DB2A AS COPY OF Server1.DB1A

2.          

Supports T-SQL insert statements (but does not support updating views within an insert statement or providing locking Tips ).

For SQL azure installation executions, T-SQL DROP DATABASE AND OTHER DDL commands have more restrictions on data migration. In addition, the T-SQL restore and attach database commands are not supported. Finally, the T-SQL statement execute as (LOGIN) is not supported.

Data access and Programming

Now, we will discuss general programming considerations when processing cloud data. First, you need to consider where to set the development environment. If you have subscribed to msdn and can process databases smaller than 1 GB, you can only use cloud installation (sandbox) for development. If so, migration from a local environment to the cloud environment will not have any problems. If you do not subscribe to msdn, you can use a general SQL azure account to develop cloud instances directly (the most likely is to use copies of production databases in the cloud ). Of course, development directly from the cloud is not suitable for all situations.

If you select the locally installed SQL Server database as the data source for development, you must develop a mechanism to synchronize local installation and cloud installation. You can use the method described above for synchronization, and the tools being developed, such as data sync services and sync framework, are exactly for this situation.

As long as you only use supported features, it is easy to convert your application from the locally installed SQL Server to the SQL azure Database: simply change the connection string in the application.

Whether you set the development environment locally or in the cloud, you need to understand the programming differences between SQL Server and SQL azure. I have introduced the differences between T-SQL and connection strings. In addition, all tables must have at least clustered indexes (not supported by heap tables ).

As mentioned above, the use statement used to change the database is not supported. This also means that it does not support distributed (cross-database) transactions or queries, nor does it support linked servers.

When using the SQL azure DatabaseNoOther options available include:

• Full-text index
• CLR custom type (but supports built-in CLR geometry and geography)
• Rowguid (uniqueidentifier type supported for newid functions)
• XML column Index
• Filestream Data Type
• Sparse Column

The default sorting rules are always used for databases. To adjust sorting rules, use the T-SQL collate statement to set column-level sorting rules to the desired values.

Finally, you cannot use the SQL event probe or database Optimization Wizard for the SQL azure database.

Some important tools used to optimize and monitor SQL azure include:

• SSMs query optimizer, used to view detailed information about an estimated or actual query execution plan and client statistics
• Some dynamic management views can monitor the running status and status
• The Entity Framework connects to SQL azure after creating an initial model and a ing file by connecting to a local copy of the SQL azure database.

Depending on the application type you have developed, you can use SSAs, SSRS, SSIs, or workbook. You can also use these products as users of SQL azure database data. You only need to use the method described earlier in this article to connect to the SQL azure server and the selected database.

It is also important to fully understand the transaction behavior in SQL azure. As mentioned above, only local transactions (in the same database) are supported. In addition, the only transaction isolation level that can be used by databases hosted on SQL Azure is read committed snapshot. With this isolation level, the reader obtains the latest data when the statement is executed.

SQL azure does not detect update conflicts. This is also known as the optimistic concurrency model, because of the loss of updates, non-repeated reading and phantom. Of course, dirty reads will not occur.

Database Management

In general, when using SQL Azure, the Administrator's role is only a logical installation and management. The actual management work is performed by the platform. From a practical point of view, this means that you do not need to purchase, install, fix, maintain, or protect the actual server. There is no actual location to actually place files, logs, tempdb, and so on. Therefore, T-SQL commands use <database>, filegroup, backup, restore, or snapshot are not supported.

SQL proxy is not supported on SQL azure. In addition, you cannot (or do not need to) Configure replication, log transfer, database images, or clusters. If you need to locally maintain a copy of the SQL azure Architecture and Data Synchronization, you can use the tools discussed earlier for data migration and synchronization. These tools are two-way. You can also use the database Copy command.

In addition to data synchronization, what tasks does the Administrator need to perform on SQL azure installation?

You still need to perform logical management. This includes security and performance management tasks. In addition, you also need to monitor the use of capacity and related costs. To help you complete these tasks, SQL azure provides a public "status history" Panel to display the current service status and recent history records (Figure5Displays the history example). The address of the Panel is in microsoft.com/windowsazure/support/status/servicedashboard.aspx.

 

Figure 5SQL azureStatus history

By default, SQL azure has high security standards. It enforces SSL encryption for all allowed (through firewall rules) client connections. Server-level logon and database-level users and roles are secure. SQL azure does not have server-level roles. Encryption of connection strings is the best practice. In addition, you can use Windows azure certificates to increase security. For more information, see blogs.msdn.com/ B /sqlazure/archive/2010/09/07/10058942.aspx.

In terms of performance, SQL azure can automatically terminate long-running transactions and idle connections (over 30 minutes ). Although you cannot use SQL event probes or trace tags to optimize performance, you can use the SQL query optimizer to View query execution plans and client statistics. You can also use standard T-SQL methods for statistics management and index optimization.

In addition, some dynamic management views (covering database, execution, or transaction information) can be used in database management ). The options include SYS. dm_exec_connections, _ requests, _ sessions, _ tran_database_transactions, _ active_transactions, and _ partition_stats. For a complete list of dynamic management views supported by SQL Azure, see msdn.microsoft.com/library/ee336238.aspx1_dmv.

There are also some new views, such as SYS. database_usage and SYS. bandwidth_usage. These views show the number, type, and size of the database, and the bandwidth usage of each database, so that the administrator can understand the billing status of SQL azure.Figure6An example is displayed. In this view, the unit of quantity is kb. You can use the following command to monitor the space used:

1.            SELECT SUM(reserved_page_count) * 8192 FROM sys.dm_db_partition_stats

2.          

 

Figure 6SQLQuery bandwidth usage

You can also learn about the current cost of installing SQL azure through the SQL azure portal. You only need to click the "billing" link in the upper-right corner of the screen.

Learn more

To learn more about SQL Azure, we recommend that you download the Windows azure training toolkit, including SQL azure practice courses, white papers, and videos. This training kit is available from microsoft.com/downloads/details.aspx? Familyid = 413e88f8-5966-4a83-b309-53b7b77edf78.

In addition, you can refer to the blog blogs.msdn.com/ B /sqlazure/ of the SQL azure team and the msdn.microsoft.com/windowsazure/sqlazureof the msdn SQL azure Developer Center.

If you want to preview the upcoming SQL azure feature, visit SQL azure lab sqlazurelabs.com.

Http://msdn.microsoft.com/zh-cn/magazine/gg309175.aspx

SQL azure test environment

Http://www.sqlazurelabs.com/

Purchase SQL azure official service

Http://www.microsoft.com/windowsazure/

SQL azure trial application

Http://www.microsoft.com/windowsazure/getstarted/default.aspx