Announcing Updates to Windows Azure SQL Database - Azure のブログ

We are excited to announce that we have recently deployed new Service Updates to Windows Azure SQL Database that provide additional functionality. Specifically, the following new features are…

We are excited to announce that we have recently deployed new Service Updates to Windows Azure SQL Database that provide additional functionality. Specifically, the following new features are now available:

SQL Server support for Linked Server and Distributed Queries against Windows Azure SQL Database.
Recursive triggers are now supported in Windows Azure SQL Database.
DBCC SHOW_STATISTICS is now supported in Windows Azure SQL Database.
Ability to configure Windows Azure SQL Database firewall rules at the database level.

SQL Server Support for Linked Server and Distributed Queries against Windows Azure SQL Database

It is now possible to add a Windows Azure SQL Database as a Linked Server and then use it with Distributed Queries that span the on-premises and cloud databases. This is a new component for database hybrid solutions spanning on-premises corporate networks and the Windows Azure cloud.

SQL Server box product contains a feature called “Distributed Query” that allows users to write queries to combine data from local data sources and data from remote sources (including data from non-SQL Server data sources) defined as Linked Servers. Previously Windows Azure SQL Databases didn’t support distributed queries natively, and needed to use the ODBC-to-OLEDB proxy, which was not recommended for performance reasons. We are happy to announce that Windows Azure SQL Databases can now be used through “Distributed Query”. In practical terms, every single Windows Azure SQL Database (except the virtual master) can be added as an individual Linked Server and then used directly in your database applications as any other database.

The benefits of using Windows Azure SQL Database include manageability, high availability, scalability, working with a familiar development model, and a relational data model. The requirements of your database application play an important role in deciding how it would use Windows Azure SQL Databases in the cloud. You can move all of your data at once to Window Azure SQL Databases, or progressively move some of your data while keeping the remaining data on-premises. For such a hybrid database application, Windows Azure SQL Databases can now be added as linked servers and the database application can issue distributed queries to combine data from Windows Azure SQL Databases and on-premise data sources.

Here’s a simple example explaining how to connect to a Windows Azure SQL Database using Distributed Queries:

—— Configure the linked server
— Add one Windows Azure SQB DB as Linked Server
EXEC sp_addlinkedserver
@server=‘myLinkedServer’, — here you can specify the name of the linked server
@srvproduct=”,
@provider=‘sqlncli’, — using SQL Server native client
@datasrc=‘myServer.database.windows.net’, — add here your server name
@location=”,
@provstr=”,
@catalog=‘myDatabase’ — add here your database name as initial catalog (you cannot connect to the master database)

— Add credentials and options to this linked server
EXEC sp_addlinkedsrvlogin
@rmtsrvname = ‘myLinkedServer’,
@useself = ‘false’,
@rmtuser = ‘myLogin’, — add here your login on Azure DB
@rmtpassword = ‘myPassword’ — add here your password on Azure DB
EXEC sp_serveroption ‘myLinkedServer’, ‘rpc out’, true;

—— Now you can use the linked server to execute 4-part queries
— You can create a new table in the Azure DB
exec (‘CREATE TABLE t1tutut2(col1 int not null CONSTRAINT PK_col1 PRIMARY KEY CLUSTERED (col1) )’) at myLinkedServer

— Insert data from your local SQL Server
exec (‘INSERT INTO t1tutut2 VALUES(1),(2),(3)’) at myLinkedServer

— Query the data using 4-part names
select * from myLinkedServer.myDatabase.dbo.myTable

More information on Linked Servers and Distributed Queries is available here.

Recursive Triggers

A trigger may now call itself recursively if this option is set to on (Default) for a particular database. Just like SQL Server 2012, the option can be configured via the following query:

ALTER DATABASE YOURDBNAME SET RECURSIVE_TRIGGERS ON|OFF;

For full information on recursive triggers, see the SQL Server 2012 Books Online Topic.

DBCC SHOW_STATISTICS Now Supported

DBCC SHOW_STATISTICS displays current query optimization statistics for a table or indexed view. The query optimizer uses statistics to estimate the cardinality or number of rows in the query result, which enables the query optimizer to create a high quality query plan. For example, the query optimizer could use cardinality estimates to choose the index seek operator instead of the index scan operator in the query plan, improving query performance by avoiding a resource-intensive index scan. For more information click here.

Ability to Configure SQL Database Firewall Rules at the Database Level

Previously Windows Azure SQL Database firewall rules could be set only at the server level, either through the management portal or via T-SQL commands. Now, firewall rules can be additionally set at the more granular database level, with different rules for different databases hosted on the same logical SQL Database server. For more information click here.

For questions or more technical information about these features, you can post a question on the SQL Database MSDN Support Forum.