| Read more about this book |
(For more resources on Microsoft Azure, see here.)
SQL Azure is a subset of SQL Server 2008 R2 and, as such, the tools that are used with SQL Server can also be used with SQL Azure, albeit with only some of the features supported. The Microsoft tools can also be divided further between those that are accessed from the Visual Studio series of products and those that are not. However, it appears that with Visual Studio 2010, the SSMS may be largely redundant for most commonly used tasks.
Visual Studio related
From the very beginning, Visual Studio supported developing data-centric applications working together with the existing version of SQL Server, as well as MS Access databases, and databases from third parties. The various client APIs such as ODBC, OLEDB, and ADO.NET made this happen, not only for direct access to manipulate data on the server, but also for supporting web-facing applications to interact with the databases.
There are two versions, of particular interest to SQL Azure, that are specially highlighted as they allow for creating applications consuming data from the SQL Azure Server. Visual Studio 2008 SP1 and Visual Studio 2010 RC were released (April 12, 2010) recently for production. The new key features of the more recent update to SQL Azure are here: http://hodentek.blogspot.com/2010/04/features-galore-for-sql-azure.html. It may be noted that the SQL Azure portal provides the all-important connection strings that is crucial for connecting to SQL Azure using Visual Studio.
VS2008
Although you can access and work with SQL Azure using Visual Studio 2008, it does not support establishing a data connection to SQL Azure using the graphic user interface, like you can with an on-site application. This has been remedied in Visual Studio 2010.
VS2010
Visual Studio 2010 has a tighter integration with many more features than Visual Studio 2008 SP1. It is earmarked to make the cloud offering more attractive. Of particular interest to SQL Azure is the support it offers in making a connection to SQL Azure through its interactive graphic user interface and the recently introduced feature supporting Data-tier applications. A summary detail of the data tier applications are here: http://hodentek.blogspot.com/2010/02/working-with-data-gotten-lot-easier.html.
SQLBulkCopy for Data Transfer
In .NET 2.0, the SQLBulkCopy class in the System.Data.SqlClient namespace was introduced. This class makes it easy to move data from one server to another using Visual Studio. An example is described in the next chapter using Visual Studio 2010 RC, but a similar method can be adopted in Visual Studio 2008.SQL Server Business Development Studio (BIDS).
The Business Development Studio (BIDS) would fall under both SQL Server 2008 and Visual Studio. The tight integration of Visual Studio with SQL Server was instrumental in the development of BIDS. Starting off to a successful introduction in Visual Studio 2005 more enhancements were added in Visual Studio 2008, both to the Integration Services as well as Reporting Services, two of the main features of BIDS. BIDS is available as a part of the Visual Studio shell when you install the recommended version of SQL Server. Even if you do not have Visual Studio installed, you would get a part of Visual Studio that is needed for developing business intelligence-related integration services as well as reporting services applications.
SQL Server Integration Services
Microsoft SQL Server Integration Services (SSIS) is a comprehensive data integration service that superseded the Data Transformation Services. Through its connection managers it can establish connections to a variety of data sources that includes SQL Azure. Many of the data intensive tasks from onsite to SQL Azure can be carried out in SSIS.
SQL Server Reporting Services
SQL Server Reporting Services (SSRS) is a comprehensive reporting package that consists of a Report Server tightly integrated with SQL Server 2008 R2 and a webbased frontend, client software – the Report Manager. SSRS can spin-off reports from data stored on SQL Azure through its powerful data binding interface.
Entity Framework Provider
Like ODBC, OLE DB, and ADO.NET data providers Entity Framework also features an Entity Framework Provider although, it does not connect to SQL Azure like the others. Using Entity Framework Provider you can create data services for a SQL Azure database. .NET client applications can access these services. In order to work with Entity Framework Provider you need to install Windows Azure SDK (there are several versions of these), which provides appropriate templates.
Presently, the Entity Framework Provider cannot create the needed files for a database on SQL Azure. A workaround is adopted.
SQL Server related
The tools described in this section can directly access SQL Server 2008 R2. The Export/ Import Wizard cannot only access SQL Servers but also products from other vendors to which a connection can be established. Scripting support, BCP, and SSRS are all effective when the database server is installed and are part of the SQL Server 2008 R2 installation. SQL Server Integration Services is tightly integrated with SQL Server 2008 R2, which can store packages created using SSIS. Data access technologies and self-service business integration technologies are developing rapidly and will impact on cloud-based applications including solutions using SQL Azure.
SQL Server Management Studio
SQL Server Management Studio (SSMS) has been the work horse with the SQL Servers from the very beginning. SSMS also supports working with SQL Azure Server. SQL Server 2008 did not fully support SQL Azure, except it did enable connection to SQL Azure. This was improved in SQL Server 2008 R2 November-CTP and the versions to appear later.
SSMS also provides the SQL Azure template, which includes most of the commands you will be using in SQL Azure.
Import/Export Wizard
The Import/Export Wizard has been present in SQL Servers even from earlier versions to create DTS packages of a simple nature. It could be started from the command line using DTSWiz.exe or DTSWizard.exe. In the same folder, you can access all dts-related files. You can double-click Import and Export Data (32-bit) in Start | All Programs | Microsoft SQL Server 2008 R2.
You may also run the DTSWizard.exe from a DOS prompt to launch the wizard.
The Import/Export wizard may be able to connect to SQL Azure using any of the following:
- ODBC DSN
- SQL Server Native Client 10.0
- .NET Data Source Provider for SqlServer
The Import/Export wizard can connect to the SQL Azure server using ODBC DSN. Although connection was possible, the export or import was not possible in the CTP version due to some unsupported stored procedure.
Import/Export works with the .NET Framework Data Provider, but requires some tweaking.