SQL Server Insider

Exploring SQL Azure features and opportunities


Problem solve Get help with specific problems with your technologies, process and projects.

Are we there yet? The slow road to SQL Azure

SQL Azure offers full-blown SQL Server functionality in the cloud and could ease management and development. But whether it’s ready to fully replace SQL Server remains to be seen.

If an application doesn’t run on a smartphone or in the cloud, it seems that no one pays much attention to it. So it's only natural that Microsoft would push to put SQL Server Database Services in the cloud. Enter SQL Azure, Microsoft’s cloud-based SQL Server platform.

SQL Azure gives enterprises relational database management system (RDBMS) features at a fraction of the cost of buying hardware and licenses for their own in-house SQL Server instances. But is SQL Azure ready to replace a full-blown SQL Server installation? Recent Microsoft announcements hint about the second major release of SQL Azure, it’s still uncertain how much further product development needs to advance before the cloud technology can replace in-house SQL Server instances.

What’s to gain with SQL Azure?
SQL Azure offers true SQL Server functionality; you can create tables, views, indexes, stored procedures, triggers and functions. Security exists through logins as well as user and database roles. And you can execute complex queries that use standard features such as transactions or temp tables.

You also gain some basic functions for aggregation, math, date and time data as well as string manipulation with SQL Azure, in addition to a subset of the system stored procedures and views. Until recently, databases were limited to 10 GB, but a new 50 GB size is available for each database with support for special data types. Essentially, SQL Azure can be considered “SQL Server Lite” in the cloud.

The basics are in place to call SQL Azure a valid database platform, and since it’s built in the cloud, Microsoft provides valuable enterprise features such as high availability and scalability. SQL Azure databases run on top of a network of Windows Server and SQL Server technologies, which allows the service to replicate multiple copies of your data so you’re covered in the event of a failure. It also reduces administrative overhead for backup and recovery.

The same can be said for scalability. You can easily add databases for increased storage or throughput. And since the database exists on multiple servers, the more databases you have, the more processing power you can tap.

If you build your application to use ten 2 GB databases instead of one 20 GB database, you’ve added all the benefits of distributed computing to your applications. Because you can add and remove databases on demand and Microsoft only bills you for what you used on that day, you can implement a bursting scale-out model.

Take, for example, a fictional cellular company. Most of the year, the company’s system must handle requests for new phones, upgrades and account changes – all relatively low-impact requirements. Once a year, however, the coolest new smart phone is released and 600,000 people in one day log in to pre-order it – causing the system to fail.

With a traditional infrastructure, the cell phone company has two choices:

1. Grit their teeth and get though one or two busy days with several unhappy customers.

2. Build the system to a scale that can handle the busiest days but that leaves the company with a lot of idle capacity the other 363 days.

Option #2 means a great deal of expense for one day of activity. With SQL Azure, the company can add hundreds of databases for the one busy day and then cut back down to five databases after the hullabaloo ends. On-demand scaling saves money and can prevent aggravating customers on those busy days.

Diminishing workloads and performance gains
What does all this mean in terms of a typical DBA or developer workloads?

A typical DBA splits his time between varieties of administrative tasks – managing security, backing up databases, setting up fault-tolerance solutions, indexing and query tuning, index maintenance processes, new server installations and more.

Right out of the gate, DBAs using SQL Azure don’t have to worry about backups and high availability. Further, they don’t have to set up new SQL Servers or architect complex multiple server utilities to handle processing demands. This allows them to focus on security and tuning, which, in the end, are the things that end users notice most.

The responsibility of server administration is also removed, so no more server builds, racking, cabling, cooling or anything else that goes along with the physical servers. This frees up your systems administrators, or in some environment further frees up your DBA’s time.

Microsoft has also gone to great lengths to ensure that developers can experience a seamless transition to SQL Azure. The platform still uses T-SQL, a relational database model, and common connection libraries such as ADO.NET and ODBC.

And just like on-premise SQL Servers, Azure exposes TDS endpoints, allowing end users and applications to connect the same as they would with on-premise SQL Servers. Therefore, developers should be able to migrate existing applications onto a SQL Azure platform using their existing skill sets.

You can also supplement existing internal systems with SQL Azure. Microsoft’s Sync Framework allows you to maintain synchronization between on-site SQL Servers and SQL Azure databases. You can also use the framework to keep remote databases, such as a salesperson’s laptop, smart phones or other devices, in sync.

Does SQL Azure fit in your budget?
Microsoft’s change to the maximum SQL Azure database size brings about a change in the pricing structure. And SQL Azure comes in two flavors: Web Edition, with a max database size of 5 GB, and the Business Edition, which is 50 GB.

Web Edition costs $49.95 per month. Business Edition runs $99.99 per month per 10 GB. If you have a 50 GB database running on Business Edition, it will run you about $500 per month. When you consider that a SQL Server Standard Edition license runs about $7,000 per processor, plus hardware costs, $500 a month is not too bad.

With SQL Azure, you also have to pay for the data flowing through your system; a data transfer costs $.10 to bring data in and $.15 to push it out, per GB. You don’t pay extra for processing use or data transfers to other SQL Azure databases or Windows Azure systems.

Although the numbers look promising, SQL Azure has a few shortcomings compared to a full-blown SQL Server instance.

• You don’t have full access to all functions and systems views.

• You can’t perform backups or use technologies like log shipping or database mirroring in SQL Azure.

• You can’t partition tables across file groups.

The development platform is the source of another disadvantage. SQL Azure can’t distinguish development from production, so you pay the same to set up a development database in the cloud. The best way around this is to develop locally using SQL Server Developer Edition and then deploy to SQL Azure. Just be sure to limit yourself to the SQL Azure feature set and avoid using full-text search, for example.

In reality, some limitations are to be expected. In one sense, Microsoft takes away some of the management overhead so DBAs have more time to focus on application development and database tuning. But, on the other hand, Microsoft is still taking away some of your management, and that’s not always welcome..

Eric Johnson,
SQL Server MVP, is the co-founder of Consortio Services and the primary Database Technologies consultant. His background in IT ranges from operating systems and hardware to specialized applications and development. He has more than 10 years of IT experience, much of which deals with SQL Server and has managed and designed databases of all shapes and sizes. He is an active presenter on SQL Server topics for national technology conferences and has published various articles and books.

Article 3 of 3

Dig Deeper on SQL Server Migration Strategies and Planning

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.