SSIS 2012 – Introduction to Windows Azure SQL Databases

Throughout our coverage of SQL Server 2012 Integration Services published on this forum, we have been dealing mainly with the traditional data management model, where the entire SQL Server environment resides on-premise. This once predominant paradigm is obviously changing, with hosted solutions (in the form of both private and public clouds) becoming increasingly common. In order to properly account for this trend, we turn our attention to the role that SSIS can serve in a mixed environment, where a part of the SQL Server estate is located in Windows Azure.

Let’s start by briefly reviewing existing data-oriented services available as part of the Microsoft Cloud Services offering. Its current portfolio consists of the following choices:

  • SQL Database service (formerly referred to as SQL Azure Database) – constitutes an example of the Platform as a Service (PaaS) solution, which eliminates the overhead associated with installation and maintenance of host operating systems as well as provisioning of SQL Server instances (effectively minimizing overall cost). Consequently, you can save some money and fully concentrate on data administration and application development-specific tasks, as long as you are willing to accept somewhat limited sizing (up to 150 GB) and functionality, as well as the implications of the shared computing model (where you are not able to allocate the desired amount of processing resources), forcing you to resort to scaling out (rather than scaling up) methodology when dealing with performance issues.
  • SQL Server in Windows Azure Virtual Machines – gives you the ability to manage your own operating systems, hosting dedicated instances of SQL Server serving as an example of the Infrastructure as a Service (IaaS) solution. As the result, you have access to the entire SQL Server 2008 R2 or SQL Server 2012 feature set, including Integration Services, Analysis Services, Reporting Services, Full Text Indexing, or Broker Service, which are not part of SQL Database offering. There are also less obvious differences that distinguish the VM-based approach from its PaaS counterpart, such as support for OLE-DB or CLR integration (for a more comprehensive list, refer to MSDN Library). In addition, scaling-up is a possibility (a subject to the largest supported VM size limit, which currently translates into 8 virtual CPUs, 14 GB of RAM, 16 TB of disk space, and 800 MB/s of network bandwidth) as a way of addressing performance bottlenecks.

It is worth mentioning that Windows Azure also supports other data storage types, such as Tables (facilitating non-relational data sets consisting of key/value pairs and associative arrays) as well as Blobs intended for non-structured data (such as video, audio, or images). All services offer built-in resiliency, with a single primary and one or more replicas within the same datacenter (which you choose during their creation from 10 locations scattered across 3 continents) and with the availability governed by 99.9% uptime Service Level Agreement. However, there is an important distinction in the way such redundancy is implemented. With SQL Database, this applies to database instances, while in the case of Windows Azure Virtual Machines, duplication happens on the operating system level (which you can further enhance – at an extra cost – by taking advantage of AlwaysOn avaiability groups). In addition, both SQL Database and SQL Server instances running in Windows Azure Virtual Machines can be administered by using standard database management utilities such as SQL Server Management Studio and SQL Server Data Tools (although the administrative limitations inherent to each service obviously apply).

Now that we have a better understanding of service options available to us, let’s explore the process of creating and managing them in more detail, starting with SQL Databases (which are considerably easier to implement). First, you need to obtain a Windows Azure account (you can request a free trial from http://www.windowsazure.com). This will allow you to sign in to the Management Portal. Its main window features a navigation pane including an entry labeled SQL Databases. Once you select it, you will be presented with a rather enthusiastic sounding banner stating You have no SQL databases. Create one to get started! If you switch to the adjacent Servers view, you will see a similar message announcing You have no SQL database servers. Create one to get started! Just like in a traditional implementation, each SQL Database in Windows Azure is associated with a specific SQL Server instance. Such instance can be instantiated independently or as part of database creation (both of these actions are available from the same interface, by switching between Databases and Servers views). Each server relies on SQL Authentication to validate incoming connections, so you will be prompted to specify Login Name and Password for the administrative account (assigned the sysadmin fixed server role). Note that this cannot be a standard built-in security principal (e.g. Administrator) or SQL login (e.g. sa). You also need to designate the region where the server should be hosted by selecting it from the corresponding listbox. Finally, you have the ability to Allow Windows Azure Services to access the server (a single checkbox) that makes SQL Databases hosted on the server accessible via the Windows Azure Management Portal (which is likely what will want to take advantage of, so make sure to keep this option enabled). The resulting instance gets assigned a unique name automatically, which remains constant throughout its existence and is displayed in the Servers view in Management Portal.

With the SQL Server instance in place, you are ready to configure it to allow remote connectivity from your local computer. To accomplish this, click on a horizontal white arrow appearing next to the instance name. In the resulting window, switch to the Configure view. You will find there the list of Allowed IP addresses to which you can add the current client IP address, conveniently displayed at the top of the page (note that this is a public IP address of the external interface on your Internet-facing edge device from which the SQL Server instance receives connection requests). Next, switch to the Dashboard view and take a note of the Manage URL entry, which essentially consists of the https:// prefix, followed by the name of a newly created SQL Server instance, with the .database.windows.net suffix.

At this point, we can connect to the Azure-based SQL Server instance by using SQL Server Management Studio. In the Connect to Server dialog box, select Database Engine in the Server type listbox, type in the fully qualified instance name (without the https:// prefix) in the Server name textbox, choose SQL Server Authentication type and type in the credentials you defined in the Azure Management Portal during instance creation. The target should appear in the Object Explorer window with three top level folders including Databases, Security, and Management. In order to create a new database, invoke the New Database option from the context sensitive menu of the Databases folder. This will trigger a new query window, displaying the CREATE DATABASE <Database_Name, sysname, Database_Name> T-SQL statement, which you can leverage by replacing the Database_Name parameter with a name you want to assign to the new database. Such setup will result in default settings, including Web Edition and 1 GB maximum database size. If these are not appropriate, you can employ the EDITION and MAXSIZE parameters to enforce a different outcome (for example, CREATE DATABASE DB1 (EDITION=’BUSINESS’, MAXSIZE=50GB) would yield a Business Edition database with the maximum size of 50 GB). Alternatively, you can return to the SQL Databases page in Windows Azure Management Portal and use the Create a SQL Database link in the Databases view. The latter approach allows you to simply pick the desired edition (Web or Business) and the maximum size (1 GB or 5 GB for Web and from 10 GB to 150 GB in case of Business) from the list of available options. Regardless of the creation method, you also have the ability to modify both parameters afterwards. This can be accomplished by using settings in the Scale view of the target database within Windows Azure Management Portal or by running the ALTER DATABASE T-SQL statement with the MODIFY clause against it.

This concludes the sequence of steps resulting in the creation of a SQL Database in Windows Azure. We will leverage this setup in an upcoming article to demonstrate how we can utilize SQL Server Integration Services in a hybrid environment, with the mix of on-premise and cloud-based databases.

See all articles by Marcin Policht

Marcin Policht
Marcin Policht
Being a long time reader, I'm proud to join the crowd of technology gurus gathered here. I have a fair share of Microsoft exams behind me, a couple of acronyms after my name - MCSE, MCSD, and MCT, decent familiarity with majority of MS BackOffice products (SMS, SQL, Exchange, IIS), programming and scripting languages (VB, C++, VBScript with wsh) and several years of practical experience with Windows environment administration and engineering. My focus these days is on SQL Server and Windows 2000, and I'll attempt to share the most interesting experiences with these products.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles