Many of the existing SQL Server tools & utilities can be used with SQL Azure as well. In this post we will provide an overview and in future posts we will take a closer look into these tools & utilities.
SQLCMD
The sqlcmd utility lets you enter TSQL statements, system procedures, and script files at the command prompt, in Query Editor in sqlcmd mode, in a Windows script file, or in an operating system (cmd.exe) job step of a SQL Server Agent job.
SQL Server Management Studio
SQL Server Management Studio combines graphical tools with a number of rich script editors to provide access to SQL Server to developers and administrators. The SQL Server 2008 R2 November CTP version of SQL Server Management Studio can be used to access, configure, manage and administer SQL Azure. However previous versions of SQL Server Management Studio are not supported.
Scripts Wizard
You can use the Generate and Publish Scripts Wizard to transfer a database from a local computer to SQL Azure. The Generate and Publish Scripts Wizard creates Transact-SQL scripts for your local database and the wizard uses them to publish database objects to SQL Azure. There is a new engine-type parameter for SQL Azure. When you are scripting a database and the destination is SQL Azure, set the engine parameter to SQL Azure so that the Generate and Publish Scripts Wizard creates Transact-SQL that is compatible with SQL Azure.
SQL Server Integration Services - SSIS
You can transfer data to SQL Azure by using SQL Server 2008 Integration Services (SSIS). As of the SQL Azure October CTP and the SQL Server 2008 R2 November CTP, there have been enhancements made to enable bulk copy support.
BCP
You can transfer data to SQL Azure by using the bulk copy utility (BCP.exe). The bcp utility bulk copies data between an instance of Microsoft SQL Server and a data file in a user-specified format. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files.
SQL Server Analysis Services - SSAS
On-premise SQL Server Analysis Services can consume data that is stored in SQL Azure, however SQL Server Analysis Services itself is not supported.
SQL Server Reporting Services - SSRS
On-premise SQL Server Reporting Services can consume data that is stored in SQL Azure, however SQL Server Reporting Services is not supported.
SQL Server Management Objects - SMO
A partial set of SQL Server Management Objects (SMO) are supported by SQL Azure. If you use one that is not supported, SQL Server will throw an exception.
Monday, November 9, 2009
Friday, November 6, 2009
T-SQL Features Supported by SQL Azure
Transact-SQL is a language that contains commands used to administer instances of SQL Server including creating and managing all objects in an instance of SQL Server, and inserting, retrieving, modifying, and deleting all data in tables. Applications can communicate with an instance of SQL Server by sending Transact-SQL statements to the server.
Transact-SQL is an extension of the language defined in the SQL standards published by the International Standards Organization (ISO) and the American National Standards Institute (ANSI).
Microsoft SQL Azure Database supports a subset of T-SQL for SQL Server.
The support for T-SQL reference in SQL Azure can be described in three main categories:
T-SQL Features Supported or Partially supported by SQL Azure :
Microsoft SQL Azure Database supports a subset of T-SQL for SQL Server.
The support for T-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.
T-SQL Features 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 such as
- Create/drop databases
- Create/alter/drop tables
- Create/alter/drop users and logins and so on.
- User-defined functions
- Views, including sys.synonyms view
- 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 Not supported
Thursday, November 5, 2009
SQL Azure Firewall
To help protect your data by default, all access to your SQL Azure server is blocked by the SQL Azure firewall; connection attempts originating from the Internet or Windows Azure will not be able to reach your SQL Azure server. In order to begin using your SQL Azure server, you must go to the SQL Azure portal and specify one or more firewall settings that enable access to your SQL Azure server. Use the firewall settings to specify which IP address ranges from the Internet are allowed, and whether or not Windows Azure applications can attempt to connect to your SQL Azure server.
Connection attempts from the Internet and Windows Azure must first pass through the SQL Azure firewall before they can reach your SQL Azure server, as shown in the following diagram.
Connecting from Windows Azure
When an application from Windows Azure attempts to connect to your SQL Azure server, the SQL Azure firewall looks for a specific firewall setting that indicates whether Windows Azure connections are allowed.
A firewall setting with starting and ending address equal to 0.0.0.0 indicates that Windows Azure connections are allowed. If the connection attempt is not allowed, the request does not reach the SQL Azure server.
Creating the First Firewall Rule
In order to connect to your SQL Azure server for the first time, the first firewall setting must be specified using the SQL Azure portal. To begin configuring the firewall, on the SQL Azure portal, click the Firewall Settings tab on the Server Information page.
The SQL Azure portal also allows you to remove firewall settings. For more information about managing firewall settings
Connection attempts from the Internet and Windows Azure must first pass through the SQL Azure firewall before they can reach your SQL Azure server, as shown in the following diagram.
Connecting from the Internet
When a computer attempts to connect to your SQL Azure server from the Internet, the SQL Azure firewall checks the originating IP address of the request against the full set of firewall settings. If the IP address of the request is not within one of the ranges specified, the connection attempt is blocked and does not reach the SQL Azure server.Connecting from Windows Azure
When an application from Windows Azure attempts to connect to your SQL Azure server, the SQL Azure firewall looks for a specific firewall setting that indicates whether Windows Azure connections are allowed.
A firewall setting with starting and ending address equal to 0.0.0.0 indicates that Windows Azure connections are allowed. If the connection attempt is not allowed, the request does not reach the SQL Azure server.
Creating the First Firewall Rule
In order to connect to your SQL Azure server for the first time, the first firewall setting must be specified using the SQL Azure portal. To begin configuring the firewall, on the SQL Azure portal, click the Firewall Settings tab on the Server Information page.
Wednesday, November 4, 2009
Provisioning SQL Azure
The Microsoft SQL Azure provisioning process prepares and configures the hardware and software required to maintain your data at a Microsoft data center. The SQL Azure provisioning model describes the logical hierarchy of your Windows Azure Platform account, SQL Azure servers, and databases; it enables you to provision your data storage rapidly.
To use SQL Azure, you must first create a Windows Azure Platform account, which allows you to access all the services, such as Windows Azure, .NET Services, and SQL Azure. The Windows Azure Platform account is used to set up and manage your subscriptions and to bill for consumption of the Azure services.
Once the Windows Azure Platform account is created, you can use the SQL Azure portal to create one SQL Azure server. To provision additional SQL Azure servers, create additional subscriptions. The SQL Azure portal provides a user interface that you can use to provision servers and logins, and to quickly create databases.
The following diagram demonstrates the relationship between a Windows Azure Platform account, the SQL Azure servers, and databases.
As shown in the diagram, each Windows Azure Platform account can be associated with multiple SQL Azure servers (by using a different Windows Azure Platform subscription for each). Each SQL Azure server can be associated with one or more databases.
Servers
A SQL Azure server is a logical group of databases and acts as a central administrative point for multiple databases. Each SQL Azure server includes logins similar to those in instances of SQL Server on your premises.
Each SQL Azure server has a fully qualified unique domain name, which is produced during the SQL Azure provisioning process. For example servername.ctp.database.windows.net, servername refers to the name of the SQL Azure server:
Databases
Each SQL Azure server can contain multiple databases. In each database, you can create tables, views, indices, stored procedures, and other familiar database objects. In order to create a new database, you can either use the SQL Azure portal or the CREATE DATABASE (SQL Azure Database) statement (this will be covered in future blog post). The screenshot below shows the SQL Azure portal to create the database.
The provisioning process creates a master database automatically. The master database maintains a list of databases and keeps a track of logins, and which logins have permission to create databases or other logins. (This master database is equivalent to the master database in a new on-premise instance of SQL Server.) The master database also provides SQL Azure usage metrics that you can view.
Logins
During the provisioning process, SQL Azure creates a login for you that is the server-level principal of your SQL Azure server. This server-level principal is equivalent to the sa login in SQL Server. Additional SQL Azure databases and logins can then be created in the server, as needed. For more information on the sa login in an on-premise SQL Server instance.
Server-level administration for logins and databases in SQL Azure differs from an on-premise instance of SQL Server. More information, on this will be covered in future blog posts.
SQL Azure provides the same set of security principals that are available in SQL Server, such as:
Related Blog Posts
Introduction to SQL Azure
Getting Started with SQL Azure
SQL Azure Architecture Overview
How-To Access SQL Azure Data
Provisioning SQL Azure
To use SQL Azure, you must first create a Windows Azure Platform account, which allows you to access all the services, such as Windows Azure, .NET Services, and SQL Azure. The Windows Azure Platform account is used to set up and manage your subscriptions and to bill for consumption of the Azure services.
The following diagram demonstrates the relationship between a Windows Azure Platform account, the SQL Azure servers, and databases.
Servers
A SQL Azure server is a logical group of databases and acts as a central administrative point for multiple databases. Each SQL Azure server includes logins similar to those in instances of SQL Server on your premises.
Each SQL Azure server has a fully qualified unique domain name, which is produced during the SQL Azure provisioning process. For example servername.ctp.database.windows.net, servername refers to the name of the SQL Azure server:
Databases
Each SQL Azure server can contain multiple databases. In each database, you can create tables, views, indices, stored procedures, and other familiar database objects. In order to create a new database, you can either use the SQL Azure portal or the CREATE DATABASE (SQL Azure Database) statement (this will be covered in future blog post). The screenshot below shows the SQL Azure portal to create the database.
Logins
During the provisioning process, SQL Azure creates a login for you that is the server-level principal of your SQL Azure server. This server-level principal is equivalent to the sa login in SQL Server. Additional SQL Azure databases and logins can then be created in the server, as needed. For more information on the sa login in an on-premise SQL Server instance.
Server-level administration for logins and databases in SQL Azure differs from an on-premise instance of SQL Server. More information, on this will be covered in future blog posts.
SQL Azure provides the same set of security principals that are available in SQL Server, such as:
- SQL Server logins: Used to authenticate access to SQL Azure at the server level.
- Database users: Used to grant access to SQL Azure at the database level.
- Database roles: Used to group users and grant access to SQL Azure at the database level.
Related Blog Posts
Introduction to SQL Azure
Getting Started with SQL Azure
SQL Azure Architecture Overview
How-To Access SQL Azure Data
Provisioning SQL Azure
Tuesday, November 3, 2009
How-To Access SQL Azure Data
There are several ways to incorporate SQL Azure in your applications, however there two application patterns to access the SQL Azure Data, there are accessing through
1. On-Premises Applications
In Scenario B on the right, your application code is hosted in the Windows Azure and your database resides in SQL Azure. Your application can use the same client libraries to access your database(s) in SQL Azure as are available in Scenario A. There are many different types of applications that you can host in the Windows Azure Platform.
The Scenario B client premises may represent an end user's Web browser that is used to access your Web application. The Scenario B client premises may also be a desktop or Silverlight application that uses the benefits of the Entity Data Model and the ADO.NET Data Services client to access your data that is hosted in SQL Azure.
For more information about the SQL Azure architecture, see SQL Azure Architecture Overview blog post.
Hosting Your Data in the Cloud
The appearance and functionality of your SQL Server database experience is unchanged when you use SQL Azure instead of an on-premise instance of SQL Server. There are some minor differences that include Transact-SQL changes and login management. More information will be posted in future blog posts.
When hosting only your data in the Microsoft data center, it is important to consider the network latency between SQL Azure and the on-premise servers running your application code. Network latency to SQL Azure is higher than it would be if the database were also on your premises and you may need to account for this in your application code.
One way to minimize the network latency of requests to the database is to additionally host your application in the Windows Azure Platform.
Hosting Your Application in the Cloud
Hosting applications in Windows Azure is optional. Based on your needs, you can migrate your applications to or from Windows Azure.
Hosting your application code in Windows Azure is beneficial to the performance of your application because it minimizes the network latency associated with your application's data requests to SQL Azure.
For example, you may decide to create a Web-based user interface in Windows Azure for your database application that is hosted in SQL Azure. In this scenario, the client code and data is hosted in the same physical data center, so network latency is minimal between the corresponding servers. Clients will still have to traverse the Internet to reach your database application, but the network traffic between the client code and data is much faster because those numerous data requests have a much shorter distance to travel.
- On-Premises Applications
- Hosted Applications residing in Cloud
In a traditional on-premise application, the application code and database are located in the same physical data center. SQL Azure and the Azure Services Platform offer many alternatives to that architecture. The following diagram demonstrates two generalized alternatives available for how your application can access data with SQL Azure.
In Scenario A on the left, your application code remains on the premises of your corporate data center, but the database resides in SQL Azure. Your application code uses client libraries to access your database(s) in SQL Azure. Client library transfers data using tabular data stream (TDS) over a secure sockets layer (SSL).
2. Hosted Applications residing in CloudIn Scenario B on the right, your application code is hosted in the Windows Azure and your database resides in SQL Azure. Your application can use the same client libraries to access your database(s) in SQL Azure as are available in Scenario A. There are many different types of applications that you can host in the Windows Azure Platform.
The Scenario B client premises may represent an end user's Web browser that is used to access your Web application. The Scenario B client premises may also be a desktop or Silverlight application that uses the benefits of the Entity Data Model and the ADO.NET Data Services client to access your data that is hosted in SQL Azure.
For more information about the SQL Azure architecture, see SQL Azure Architecture Overview blog post.
Hosting Your Data in the Cloud
The appearance and functionality of your SQL Server database experience is unchanged when you use SQL Azure instead of an on-premise instance of SQL Server. There are some minor differences that include Transact-SQL changes and login management. More information will be posted in future blog posts.
When hosting only your data in the Microsoft data center, it is important to consider the network latency between SQL Azure and the on-premise servers running your application code. Network latency to SQL Azure is higher than it would be if the database were also on your premises and you may need to account for this in your application code.
One way to minimize the network latency of requests to the database is to additionally host your application in the Windows Azure Platform.
Hosting Your Application in the Cloud
Hosting applications in Windows Azure is optional. Based on your needs, you can migrate your applications to or from Windows Azure.
Hosting your application code in Windows Azure is beneficial to the performance of your application because it minimizes the network latency associated with your application's data requests to SQL Azure.
For example, you may decide to create a Web-based user interface in Windows Azure for your database application that is hosted in SQL Azure. In this scenario, the client code and data is hosted in the same physical data center, so network latency is minimal between the corresponding servers. Clients will still have to traverse the Internet to reach your database application, but the network traffic between the client code and data is much faster because those numerous data requests have a much shorter distance to travel.
Monday, November 2, 2009
SQL Azure Architecture Overview
Let’s look into Microsoft’s SQL Azure Architecture. SQL Azure is hosted on servers running SQL Server technologies in Microsoft data centers. From an architectural perspective, there are four distinct layers of abstraction that work together to provide a relational database for your application to use:
1. The Client Layer
The client layer resides closest to your application, and is used by your application to communicate directly with SQL Azure. The client layer can reside on-premise in your datacenter or be hosted in Windows Azure. Because SQL Azure provides the same tabular data system (TDS) interface as SQL Server, you can use familiar tools and libraries to build client applications for data that is in the cloud.
2.The Services Layer
The services layer functions as a gateway between the client layer and the platform layer, where the data resides. The services layer provides three functions: provisioning, billing and metering, and connection routing.
The services layer provisions the databases that you specify with your Windows Azure Platform account. The billing and metering aspect of the services layer enables multi-tenant support by providing monitoring and billing for database usage based on individual Windows Azure Platform accounts. SQL Azure is built on a scalable platform involving numerous physical servers; this layer handles all the connections routing between your application and the physical servers where your data resides.
The SQL Azure fabric is a distributed computing system composed of tightly integrated networks, servers, and storage. It enables automatic failover, load balancing, and automatic replication between physical servers.
Management services monitor the health of individual servers and enable automated installation of service upgrades and software patches.
4.Infrastructure Layer
The infrastructure layer represents the IT administration of the physical hardware and operating systems that support the services layer.
- The Client Layer
- The Services Layer
- The Platform Layer
- The Infrastructure Layer.
The client layer resides closest to your application, and is used by your application to communicate directly with SQL Azure. The client layer can reside on-premise in your datacenter or be hosted in Windows Azure. Because SQL Azure provides the same tabular data system (TDS) interface as SQL Server, you can use familiar tools and libraries to build client applications for data that is in the cloud.
2.The Services Layer
The services layer functions as a gateway between the client layer and the platform layer, where the data resides. The services layer provides three functions: provisioning, billing and metering, and connection routing.
The services layer provisions the databases that you specify with your Windows Azure Platform account. The billing and metering aspect of the services layer enables multi-tenant support by providing monitoring and billing for database usage based on individual Windows Azure Platform accounts. SQL Azure is built on a scalable platform involving numerous physical servers; this layer handles all the connections routing between your application and the physical servers where your data resides.
3. The Platform Layer
The platform layer includes the physical servers and services that support the services layer. The platform layer consists of many instances of SQL Server, each of which is managed by the SQL Azure fabric. The SQL Azure fabric is a distributed computing system composed of tightly integrated networks, servers, and storage. It enables automatic failover, load balancing, and automatic replication between physical servers.
Management services monitor the health of individual servers and enable automated installation of service upgrades and software patches.
4.Infrastructure Layer
The infrastructure layer represents the IT administration of the physical hardware and operating systems that support the services layer.
Sunday, November 1, 2009
Getting Started with SQL Azure
There are 4 simple steps to get started with SQL Azure
- Download the Windows Azure Platform Training Kit : The Windows Azure Platform Training Kit includes a comprehensive set of technical content including samples, demos, hands-on labs, and presentations that are designed to help you learn how to use the Windows Azure platform. These labs cover Windows Azure, .NET Services, SQL Azure, and Live Services
- Create a new SQL Azure account and register for the CTP : The SQL Azure Database CTP is available for free. Click Here to create your account with your Live ID, and we will send you an invitation code. (Due to high demand for the SQL Azure Database CTP, there may be some delays in receiving your invitation codes as we onboard new participants.)
- Log in : Visit https://sql.azure.com/ and sign in with your Live ID, then enter your invitation code.
- Create your SQL Azure Server (and databases)
Saturday, October 31, 2009
Introduction to SQL Azure
Microsoft SQL Azure Database is a cloud-based relational database service built on SQL Server technologies. It provides a highly available, scalable, multi-tenant database service hosted by Microsoft in the cloud. SQL Azure Database helps to ease provisioning and deployment of multiple databases. Developers do not have to install, setup, patch or manage any software. High availability and fault tolerance is built-in and no physical administration is required. SQL Azure Database supports Transact-SQL(T-SQL). Customers can use existing knowledge in T-SQL development and a familiar relational data model for symmetry with existing on-premises databases. SQL Azure Database can help reduce costs by integrating with existing toolsets and providing symmetry with on-premises and cloud databases.
Uses of SQL Azure Database:
- Build scalable, custom web applications, which is especially a need for small to mid-size businesses, hobbyist, and startups
- Build packaged line-of-business applications, which is attractive to traditional, SaaS ISVs and custom developers
- Build corporate departmental applications
- Consolidate multiple data sources in the Cloud and enable secure access from multiple locations, desktop and/or devices
Benefits of SQL Azure Database:
- No need to install or patch software or other physical administration
- Automatic high availability and fault tolerance
- Simple provisioning and deployment of multiple databases
- Scale databases up or down based on business needs
Multi-tenant - Integration with SQL Server and tooling including Visual Studio
- Support for T-SQL based familiar relational database model
SQL Azure Database Features
- Relational Database Management Services (RDBMS)
- Creating, accessing and manipulating tables, views, indexes, roles, stored procedures, triggers, and functions
- Execute complex queries and joins across multiple tables
- Insert, Update, and Delete
- Constraints
- Transactions
- Temp tables
- Basic functions (aggregates, math, string, date/time)
- A subset of the existing SQL Server built-in stored procedures and system views
- Support for tracking billable metrics in real time and for historical analysis
Programmability
- Managed ADO.NET data access
- Native ODBC
- Support for PHP
Tools
- Deployment Support for running SQL configuration scripts via
- SQLCMD Provision Logical Servers and Databases through the SQL Azure Database account portal
More Information:
http://www.azure.com/
Learn more about SQL Azure on MSDN
Transact-SQL (T-SQL)
Subscribe to:
Posts (Atom)
