Buck Woody : SQL Azure

How Does the Cloud Change a Database Administrator’s Job?

BuckWoody — Tue, 29 Jan 2013 15:08:32 GMT

I recently posted a blog entry on how cloud computing would change the Systems Architect’s role in an organization. In a way, the Systems Architect has the easiest transition to a new way of using computing technologies. In fact, that’s actually part of the job description. I mentioned that a Systems Architect has three primary vectors to think about for cloud computing, as it applies to what they should do:

Knowledge - Which options are available to solve problems, and what are their strengths and weaknesses.
Experience - What has the System Architect seen and worked with in the past.
Coordination - A system design is based on multiple factors, and one person can't make all the choices. There will need to be others involved at every level of the solution, and the Systems Architect will need to know who those people are and how to work with them.

The Database Administrator Role

But a Database Administrator (DBA) is probably one of the harder roles to think about when it comes to cloud computing. First, let’s define what a Database Administrator usually thinks about as part of their job:

Planning, Installing and Configuring a Database Platform
Planning, designing and creating databases
Planning, designing and implementing High Availability and Disaster Recovery for each database (HADR) based on requirements for its workload
Maintaining and monitoring the database platform
Implementing performance tuning on the databases based on monitoring
Re-balancing workloads across database servers based on monitoring
Securing databases platforms and individual databases based on requirements and implementation

That’s just a short list, and each of those unpacks into a larger set of tasks.

The issue is that I’ve never actually met a DBA that does all of those things, or just all of those things. Many times they do much more, sometimes the systems are so large they specialize on just a few of them.

And as you can see from the list, some of these areas are shared with other roles. For instance, in some shops, the DBA plans, purchases, sets up and configures the hardware for database servers. In others that’s done
by the Infrastructure Team. In some shops the DBA designs databases from software requirements, and in others the developers do that – or perhaps it’s done as a joint effort. The same holds true for database code – sometimes the
DBA does it, other times the developer, and still others it’s a shared task.

In fact, you could argue that there are few other roles in IT where the roles are so intermixed. Also, the DBA works with software the company develops, and software the company buys. They work with hardware, networking, security and software. There are certain aspects of design and tuning that are outside the purview of some of those things, and inside the others.

With all of these variables, simply telling a DBA that they should “use the cloud” is not the proper approach.

How the Cloud Changes Things

To be sure, the DBA has the same vectors as the Systems Architect. They need to educate themselves on the options within this new option (Knowledge), try a few test solutions out (Experience) and of course work with others on various parts of the implementation (Coordination). But it goes beyond that.

There are three big buckets of cloud computing, dealing with simply using a Virtual Machine (IaaS) to writing code without worrying about the virtualization or even the operating system (PaaS) and using software that’s already written and being delivered via an Application Programming Interface (API). Each of these has so many options and configurations that it’s often better to think about the problem you’re trying to solve rather than all of the technology within a given area - although some of that is certainly necessary anyway.

Database Platform Architecture

I’ll start with when the DBA should even consider cloud computing for a solution. Once again, it’s not an “all or nothing” paradigm, where you either run something on premises or in the cloud – it’s often a matter of selecting the right components to solve a problem. In my design sessions with DBA’s I break these down into three big areas where they might want to consider the cloud –and then we talk about how to implement each one:

Audiences
HADR
Data Services

Audiences

If the users of your database systems all sit in the same facility, you own the servers and networking, and the application servers are separate from the database server, it doesn’t usually make sense to take that database workload and place it on Windows Azure – or any other cloud provider. The latency alone prevents a satisfactory performance profile, and in some cases won’t work at all. It doesn’t matter if the cloud solution is cheaper or easier – if you’re moving a lot of data every second between an on-premises system and the cloud it won’t work well.

However – if your users are in multiple locations, especially globally, or you have a mix of company and external customer users, it might make sense to evaluate a shared data location. You still need to consider the implications of how much data the application server pushes back and forth, but you may be able to locate both the application server and SQL Server in an IaaS role. Assuming the data sent to the final client will work across public Internet channels, there may be a fit. There are security implications, but unless you have point-to-point connections for your current solution you’re faced with the same security questions on both options.

Your audience might also be developers looking for a way to quickly spin up a server and then turn it down when they are done, paying for the time and not the hardware or licenses. This is also a prime case for evaluating IaaS. And there are others that you'll find in your own organization as you work through the requirements you have.

Resources: Windows Azure Virtual Machines: http://www.windowsazure.com/en-us/manage/windows/tutorials/virtual-machine-from-gallery/ and Windows Azure SQL Server Virtual Machines: http://www.windowsazure.com/en-us/manage/windows/common-tasks/install-sql-server/

HADR

The next possible place to consider using cloud computing with SQL Server is as a part of your High Availability and Disaster Recovery plans. In fact, this is the most common use I see for cloud computing and the Database Administrator. The key is the Recovery Point Objective (RPO) and Recovery Time Objective (RTO). Based on each application’s requirements, you may find that using Windows Azure or even supplementing your current plan is
the right place to evaluate options. I’ve covered this use-case in more detail in another article.

References: SQL Server High Availability and Disaster Recovery options with Windows Azure: http://blogs.msdn.com/b/buckwoody/archive/2013/01/08/microsoft-windows-azure-disaster-recovery-options-for-on-premises-sql-server.aspx

Data Services

Windows Azure, along with other cloud providers, offers another way to design, create and consume data. In this use-case, however, the tasks DBA’s normally perform for sizing, ordering and configuring a system don’t apply.

With Windows Azure SQL Databases (the artist formerly known as SQL Azure), you can simply create a database and begin using it. There are places where this fits and others where it doesn’t, and there are differences, limitations and enhancements, so it isn’t meant as replacement for what you could do with “Full-up” SQL Server on a Windows Azure Virtual Machine or an on-premises Instance. If a developer needs an Relational Database Management
(RDBMS) data store for a web-based application, then this might be a perfect fit.

But there is more to data services than Windows Azure SQL Databases. Windows Azure also offers MySQL as a service, RIAK and MongoDB (among others) and even Hadoop for larger distributed data sets. In addition you can use Windows Azure Reporting Services, and also tap into datasets and data functions in the Windows Azure Marketplace.

The key for the DBA with this option is that you will have to do a little investigation this time, and potentially without a specific workload in mind this time. I think that’s acceptable thing to ask – DBA’s constantly keep up with data processing trends, and most will consider different ways to solve a problem.

References:

Windows Azure SQL Databases: http://www.windowsazure.com/en-us/home/features/data-management/

Windows Azure Reporting Services: http://www.windowsazure.com/en-us/manage/services/other/sql-reporting/

HDInsight Service (Hadoop on Azure): https://www.hadooponazure.com/

MongoDB Offerings on Windows Azure: http://www.windowsazure.com/en-us/manage/linux/common-tasks/mongodb-on-a-linux-vm/

Windows Azure Marketplace: http://www.windowsazure.com/en-us/store/overview/

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

BuckWoody — Tue, 08 Jan 2013 14:40:00 GMT

One of the use-cases for a cloud solution is to serve as a Disaster Recovery option for your on-premises servers. I’ll explain one particular use-case in this entry, specifically using Windows Azure “IaaS” or Virtual Machines as a Recovery Solution for SQL Server (more detail here: http://www.windowsazure.com/en-us/home/features/virtual-machines/). In future installments I’ll explain options for other workloads such as Linux and Windows Servers, SharePoint and other solutions. Some architectures also allow for using Windows Azure SQL Database (Formerly SQL Azure) in recovery scenarios; I’ll cover that separately.

Using Azure as a Disaster Recovery site gives you a range of options, uses world-wide datacenters that you can pick from, and does not require traditional licensing and maintenance paths. You can also integrate the offsite data into other uses, such as reporting (in some cases) or to leverage within other applications. However, the cost-model is different, so make sure you do your homework to ensure that it makes sense to use a cloud provider for safety. You may find that it is cheaper, more expensive, or that you require a mix of technologies and options to get the best solution.

NOTE: The Microsoft Windows Azure platform evolves constantly. That means new features and capabilities, as well as security, optimizations and more improve on a frequent basis. As with any cloud provider, ensure that you check the date of this post to ensure you are within six months or so. If the date is longer than that, then check each of the “Details” links to ensure you are working with the latest information.

The options you have range from simple off-site storage for database backups to systems that your users can access when your primary options are offline. To select which options to use, evaluate the databases you want to protect, and then create your Recovery Point Objectives (RPO) and Recovery Time Objectives (RTO) for each workload. Those two vectors will provide the starting point for each choice you make.

NOTE: If you’re not familiar with RPO and RTO on a database system, learn those terms carefully before designing a recovery solution – on any platform. RPO and RTO are business/technology terms, and are not vendor or platform-specific. http://wikibon.org/wiki/v/Recovery_point_objective_-_recovery_time_objective_strategy

The range of protection you have is very similar to the on-premises options for SQL Server (on-premises details here: http://msdn.microsoft.com/en-us/library/ms190202.aspx), with the primary limitation being bandwidth. While Microsoft has the largest connections we can get into our datacenters, depending on where your systems are and their connection to the Internet, you will need to consider how much data you transfer, and how often. For backup files, a single, larger transfer is acceptable, using Log Shipping or Database Mirroring, smaller, more frequent transfers are preferable.

Another limitation is controlling the hardware on the Windows Azure Virtual Machine. That means hardware-based clustering isn’t possible, as of this writing. You’re also limited to the size of the Virtual Machines that Windows Azure (or any other cloud provider) offers. It’s important to keep in mind that you’re building a Disaster Recovery solution, not necessarily a full Highly-Available system. The difference is that in this case DR provides a means to recover and operate at a more limited fashion than a full on-premises HA (with matching hardware and licenses) involves. Storage, however, isn’t as affected. You can mount large amounts of storage on a Windows Azure Virtual Machine, so it’s more memory and CPU that you need to consider for your solution.

The final consideration is security. There are two aspects in security that you need to consider: data security and authentication and access. For the first consideration, the Windows Azure system does hold multiple certifications and attestations that you can find here: . In some cases those certifications are agreements on the part of security each party will hold liability for; so it’s important to carefully read and understand what the agreement states. There are also methods of encrypting data (such as the backups) using your own certificates or hardware devices and then storing them externally. This means no one can easily un-encrypt your data.

For the authentication portion, you can create a secure “tunnel” between your network and Windows Azure. This involves a certificate that is installed on your hardware firewall at your facility, and an agent that is enabled with the same certificate on Windows Azure. This gives you a “point to point” connection, encrypted but over a public connection. From there you can use Active Directory to connect the authentication for the systems involved in the DR solution.

Backups

The First and most simple DR solution using Windows Azure is to store your backup files (*.bak) in Windows Azure storage. Windows Azure Storage is triple-redundant across multiple fault-domains within a single datacenter, and then all three copies are replicated to a geographically separate (although data-sovereignty same) location. That translates to six copies of data stored remotely. In case of a disaster, you connect to storage, download the images, and restore them to a new server. The server can have the same name or different, and unless you’re using contained databases, you’ll need to re-create and re-authorize the security accounts needed for the database.

Note that you also have the option of using an “appliance”, which is a piece of hardware you install at your facility which will act as a backup device or share location (or both). The device handles the encryption, de-duplication and compression for the files, and then stores those files on Windows Azure. More information on that option is here: http://www.storsimple.com/

RPO: As of last backup

RTO: (Time of transfer from Windows Azure + Time of Restore to New System + Bringing System Online with User Accounts) - Time of Backup

References:

More detail on storing files on Windows Azure: http://sqlblog.com/blogs/sqlos_team/archive/2013/01/24/backup-and-restore-to-cloud-simplified-in-sql-server-2012-sp1-cu2.aspx

Free Client: http://azurestorageexplorer.codeplex.com/

Database Mirroring

Database Mirroring is a deprecated feature in SQL Server, which means it will be removed in a future release. It is, however, still supported in SQL Server 2012, and it can be used between on-premises SQL Server Instances and Windows Azure VM’s. Using connection strings and .NET languages, clients can actually point to the partner server automatically.

The granularity of this solution is at the individual database level. Machines can retain their individual identities. You can use certificates to connect the systems, or you can use the point-to-point solution and Active Directory.

There are limitations, however. You won’t use a Listener in this configuration, and you’ll be using Asynchronous mode. If you are not running in the same Active Directory, you’ll also need to factor in the time to re-create and tie out those accounts when calculating the RTO value.

RPO: As of last good synchronization

RTO: (Time of failure + Time of client redirect to New System ) - Time of last good synchronization

References:

A complete tutorial on setting up this configuration is here: http://msdn.microsoft.com/en-us/library/jj870964.aspx

Log Shipping

Another feature available for DR in a Hybrid fashion is using Log Shipping, which also protects your system at a database level. Log shipping involves an automated log backup of your database, and the log is copied and then applied at the secondary server. Because the log file is copied to a Windows share, this solution requires both networking access and an Active Directory integration.

RPO: As of last good log backup application to the secondary system

RTO: (Time of failure + Time of manual client redirect to New System + Time of Manual Failover ) - Time of last good log backup

References:

Log Shipping information is here: http://technet.microsoft.com/en-us/library/ms187103.aspx

AlwaysOn Availability Groups

SQL Server 2012 introduces a new set of features called “AlwaysOn” that encompass many of the HA/DR features in previous releases. One feature within that set is called “Availability Groups”, and with certain caveats that feature is available for a Hybrid on-premises to Windows Azure VM solution.

AlwaysOn requires a Windows Cluster (WFSC), which is where the caveats come into play. You’re able to set up a multi-subnet WSFC cluster, but you won’t have access to the Availability Group Listener function, so you need to consider the client reconnection.

RPO: As of last good synchronization

RTO: (Time of failure + Time of manual client redirect to New System + Time of Manual Failover ) - Time of last good log backup

References:

A complete tutorial on setting up this configuration is here: http://msdn.microsoft.com/en-us/library/jj870959.aspx

The Windows Azure Software Development Kit (SDK) and the Windows Azure Training Kit (WATK)

BuckWoody — Wed, 12 Sep 2012 13:40:40 GMT

Windows Azure is a platform that allows you to write software, run software, or use software that we've already written. We provide lots of resources to help you do that - many can be found right here in this blog series. There are two primary resources you can use, and it's important to understand what they are and what they do.

The Windows Azure Software Development Kit (SDK)

Actually, this isn't one resource. We have SDK's for multiple development environments, such as Visual Studio and also Eclipse, along with SDK's for iOS, Android and other environments. Windows Azure is a "back end", so almost any technology or front end system can use it to solve a problem.

The SDK's are primarily for development. In the case of Visual Studio, you'll get a runtime environment for Windows Azure which allows you to develop, test and even run code all locally - you do not have to be connected to Windows Azure at all, until you're ready to deploy.

You'll also get a few samples and codeblocks, along with all of the libraries you need to code with Windows Azure in .NET, PHP, Ruby, Java and more.

The SDK is updated frequently, so check this location to find the latest for your environment and language - just click the bar that corresponds to what you want:

http://www.windowsazure.com/en-us/develop/downloads/

The Windows Azure Training Kit (WATK)

Whether you're writing code, using Windows Azure Virtual Machines (VM's) or working with Hadoop, you can use the WATK to get examples, code, PowerShell scripts, PowerPoint decks, training videos and much more. This should be your second download after the SDK. This is all of the training you need to get started, and even beyond. The WATK is updated frequently - and you can find the latest one here:

http://www.windowsazure.com/en-us/develop/net/other-resources/training-kit/

There are many other resources - again, check the http://windowsazure.com site, the community newsletter (which introduces the latest features), and my blog for more.

Windows Azure Database (SQL Azure) Development Tip

BuckWoody — Wed, 15 Aug 2012 14:55:44 GMT

When you create something in the cloud, it's real, and you're charged for it. There are free offerings, and you even get free resources with your Microsoft Developer Network (MSDN) subscription, but there are limits within those. Creating a 1 GB database - even with nothing in it - is a 1 GB Database. If you create it, drop it, and create it again 2 minutes later, that's 2 GB of space you've used for the month. Wait - how do I develop in this kind of situation?

With Windows Azure, you can simply install the free Software Development Kit (SDK) and develop your entire application for free - you need never even log in to Windows Azure to code. Once you're done, you simply deploy the app and you start making money from the application as you're paying for it.

Windows Azure Databases (The Artist Formerly Known As SQL Azure) is a bit different. It's not emulated in the SDK - because it doesn't have to be. It's just SQL Server, with some differences in feature set. To develop in this environment, you can use SQL Server, any edition. Be aware of the feature differences, of course, but just develop away - even in the free "Express" or LocalDB flavors - and then right-click in SQL Server Management Studio to script objects. Script the database, but change the "Advanced" selection to the Engine Type of "SQL Azure". Bing.

Although most all T-SQL ports directly, one thing to keep in mind is that you need a Clustered Index on every table. Often the Primary Key (PK) is a good choice for that.

In the Cloud, Everything Costs Money

BuckWoody — Tue, 10 Jul 2012 12:55:50 GMT

I’ve been teaching my daughter about budgeting. I’ve explained that most of the time the money coming in is from only one or two sources – and you can only change that from time to time. The money going out, however, is to many locations, and it changes all the time. She’s made a simple debits and credits spreadsheet, and I’m having her research each part of the budget. Her eyes grow wide when she finds out everything has a cost – the house, gas for the lawnmower, dishes, water for showers, food, electricity to run the fridge, a new fridge when that one breaks, everything has a cost. She asked me “how do you pay for all this?” It’s a sentiment many adults have looking at their own budgets – and one reason that some folks don’t even make a budget. It’s hard to face up to the realities of how much it costs to do what we want to do.

When we design a computing solution, it’s interesting to set up a similar budget, because we don’t always consider all of the costs associated with it. I’ve seen design sessions where the new software or servers are considered, but the “sunk” costs of personnel, networking, maintenance, increased storage, new sizes for backups and offsite storage and so on are not added in. They are already on premises, so they are assumed to be paid for already.

When you move to a distributed architecture, you'll see more costs directly reflected. Store something, pay for that storage. If the system is deployed and no one is using it, you’re still paying for it. As you watch those costs rise, you might be tempted to think that a distributed architecture costs more than an on-premises one.

And you might be right – for some solutions. I’ve worked with a few clients where moving to a distributed architecture doesn’t make financial sense – so we didn’t implement it. I still designed the system in a distributed fashion, however, so that when it does make sense there isn’t much re-architecting to do.

In other cases, however, if you consider all of the on-premises costs and compare those accurately to operating a system in the cloud, the distributed system is much cheaper. Again, I never recommend that you take a “here-or-there-only” mentality – I think a hybrid distributed system is usually best – but each solution is different. There simply is no “one size fits all” to architecting a solution.

As you design your solution, cost out each element. You might find that using a hybrid approach saves you money in one design and not in another. It’s a brave new world indeed.

So yes, in the cloud, everything costs money. But an on-premises solution also costs money – it’s just that “dad” (the company) is paying for it and we don’t always see it. When we go out on our own in the cloud, we need to ensure that we consider all of the costs.

Management and Monitoring Tools for Windows Azure

BuckWoody — Tue, 03 Jul 2012 13:10:00 GMT

(Last updated on 01/15/2013)

With such a large platform, Windows Azure has a lot of moving parts. We’ve done our best to keep the interface as simple as possible, while giving you the most control and visibility we can. However, as with most Microsoft products, there are multiple ways to do something – and I’ve always found that to be a good strength. Depending on the situation, I might want a graphical interface, a command-line interface, or just an API so I can incorporate the management into my own tools, or have third-party companies write other tools.

While by no means exhaustive, I thought I might put together a quick list of a few tools you can use to manage and monitor Windows Azure components, from our IaaS, SaaS and PaaS offerings. Some of the products focus on one area more than another, but all are available today. I’ll try and maintain this list to keep it current, but make sure you check the date of this post’s update – if it’s more than six months old, it’s most likely out of date. Things move fast in the cloud.

The Windows Azure Management Portal

The primary tool for managing Windows Azure is our portal – most everything you need is there, from creating new services to querying a database. There are two versions as of this writing – a Silverlight client version, and a newer HTML5 version. The latter is being updated constantly to be in parity with the Silverlight client.

There’s a balance in this portal between simplicity and power – we’re following the “less is more” approach, with increasing levels of detail as you work through the portal rather than overwhelming you with a single, long “more is more” page.

You can find the Portal here: http://windowsazure.com (then click “Log In” and then “Portal”)

Windows Azure Management API

You can also use programming tools to either write your own interface, or simply provide management functions directly within your solution. You have two options – you can use the more universal REST API’s, which area bit more complex but work with any system that can write to them, or the more approachable .NET API calls in code.

You can find the reference for the API’s here: http://msdn.microsoft.com/en-us/library/windowsazure/ee460799.aspx

All Class Libraries, for each part of Windows Azure: http://msdn.microsoft.com/en-us/library/ee393295.aspx

PowerShell Command-lets

PowerShell is one of the most powerful scripting languages I’ve used with Windows – and it’s baked into all of our products. When you need to work with multiple servers, scripting is really the only way to go, and the Windows Azure PowerShell Command-Lets allow you to work across most any part of the platform – and can even be used within the services themselves. You can do everything with them from creating a new IaaS, PaaS or SaaS service, to controlling them and even working with security and more.

You can find more about the Command-Lets here: http://wappowershell.codeplex.com/documentation (older link, still works, will point you to the new ones as well)

We have command-line utilities for other operating systems as well: https://www.windowsazure.com/en-us/manage/downloads/

Video walkthrough of using the Command-Lets: http://channel9.msdn.com/Events/BUILD/BUILD2011/SAC-859T

System Center

System Center is actually a suite of graphical tools you can use to manage, deploy, control, monitor and tune software from Microsoft and even other platforms. This will be the primary tool we’ll recommend for managing a hybrid or contiguous management process – and as time goes on you’ll see more and more features put into System Center for the entire Windows Azure suite of products.

You can find the Management Pack and README for it here: http://www.microsoft.com/en-us/download/details.aspx?id=11324

SQL Server Management Studio / Data Tools / Visual Studio

SQL Server has two built-in management and development, and since Version 2008 R2, you can use them to manage Windows Azure Databases. Visual Studio also lets you connect to and manage portions of Windows Azure as well as Windows Azure Databases.

You can read more about Visual Studio here: http://msdn.microsoft.com/en-us/library/windowsazure/ee405484

You can read more about managing Windows Azure Subscriptions with Visual Studio here: http://fabriccontroller.net/blog/posts/manage-your-subscriptions-with-the-windows-azure-tools-for-visual-studio/

You can read more about the SQL tools here: http://msdn.microsoft.com/en-us/library/windowsazure/ee621784.aspx

Vendor-Provided Tools

Microsoft does not suggest or endorse a specific third-party product. We do, however, use them, and see lots of other customers use them. You can browse to these sites to learn more, and chat with their folks directly on how they support Windows Azure.

Cerebrata: Tools for managing from the command-line, graphical diagnostics, graphical storage management - http://www.cerebrata.com/

Quest Cloud Tools: Monitoring, Storage Management, and costing tools - http://communities.quest.com/community/cloud-tools

Paraleap: Monitoring tool - http://www.paraleap.com/AzureWatch

Cloudgraphs: Monitoring too - http://www.cloudgraphs.com/

Opstera: Monitoring for Windows Azure and a Scale-out pattern manager - http://www.opstera.com/products/Azureops/

Compuware: SaaS performance monitoring, load testing - http://www.compuware.com/application-performance-management/gomez-apm-products.html

SOASTA: Penetration and Security Testing - http://www.soasta.com/cloudtest/enterprise/

LoadStorm: Load-testing tool - http://loadstorm.com/windows-azure

New Relic: Application Monitoring - http://newrelic.com/azure

AppDynamics: Application Monitoring - http://www.appdynamics.com/azure.php

Manage Engine's Application Monitor: http://www.manageengine.com/products/applications_manager/windows-azure-monitoring.html

I ran across this blog entry that deals with storage clients - your mileage may vary, but he has some screen-shots and his impressions: http://cloud.dzone.com/articles/windows-azure-blob-storage

Open-Source Tools

This is probably the most specific set of tools, and the list I’ll have to maintain most often. Smaller projects have a way of coming and going, so I’ll try and make sure this list is current.

Windows Azure MMC: (I actually use this one a lot) http://wapmmc.codeplex.com/

Windows Azure Diagnostics Monitor: http://archive.msdn.microsoft.com/wazdmon

Azure Application Monitor: http://azuremonitor.codeplex.com/

Azure Web Log: http://www.xentrik.net/software/azure_web_log.html

Cloud Ninja:Multi-Tennant billing and performance monitor - http://cnmb.codeplex.com/

Cloud Samurai: Multi-Tennant Management- http://cloudsamurai.codeplex.com/

Azure Storage Explorer: Storage management - azurestorageexplorer.codeplex.com

If you have additions to this list, please post them as a comment and I’ll research and then add them. Thanks!

Windows Azure – Write, Run or Use Software

BuckWoody — Wed, 13 Jun 2012 22:47:00 GMT

Windows Azure is a platform that has you covered, whether you need to write software, run software that is already written, or Install and use “canned” software whether you or someone else wrote it. Like any platform, it’s a set of tools you can use where it makes sense to solve a problem.

You can click on the graphic below for a larger picture of these components, or download a poster with more details here.

The primary location for Windows Azure information is located at http://windowsazure.com. You can find everything there from the development kits for writing software to pricing, licensing and tutorials on all of that.

I have a few links here for learning to use Windows Azure – although it’s best if you focus not on the tools, but what you want to solve. I’ve got it broken down here into various sections, so you can quickly locate things you want to know. I’ll include resources here from Microsoft and elsewhere – I use these same resources in the Architectural Design Sessions (ADS) I do with my clients worldwide.

There is also a great video series on Cloud Fundamentals here, if you have some time to watch them. It's a great series that covers a lot of ground.

Write Software

Also called “Platform as a Service” (PaaS), Windows Azure has lots of components you can use together or separately that allow you to write software in .NET or various Open Source languages to work completely online, or in partnership with code you have on-premises or both – even if you’re using other cloud providers. Keep in mind that all of the features you see here can be used together, or independently. For instance, you might only use a Web Site, or use Storage, but you can use both together. You can access all of these components through standard REST API calls, or using our Software Development Kit’s API’s, which are a lot easier. In any case, you simply use Visual Studio, Eclipse, Cloud9 IDE, or even a text editor to write your code from a Mac, PC or Linux.

Components you can use:

Azure Web Sites: Windows Azure Web Sites allow you to quickly write an deploy websites, without setting a Virtual Machine, installing a web server or configuring complex settings. They work alone, with other Windows Azure Web Sites, or with other parts of Windows Azure. Read more about deciding to use Web Sites or Roles.

Web and Worker Roles: Windows Azure Web Roles give you a full stateless computing instance with Internet Information Services (IIS) installed and configured. Windows Azure Worker Roles give you a full stateless computing instance without Information Services (IIS) installed, often used in a "Services" mode. Scale-out is achieved either manually or programmatically under your control.

Storage: Windows Azure Storage types include Blobs to store raw binary data, Tables to use key/value pair data (like NoSQL data structures), Queues that allow interaction between stateless roles, and a relational SQL Server database.

Other Services: Windows Azure has many other services such as a security mechanism, a Cache (memcacheD compliant), a Service Bus, a Traffic Manager and more. Once again, these features can be used with a Windows Azure project, or alone based on your needs.

Windows Azure Mobile Services: A simple framework service which enables you to quickly develop the back-end for mobile services. For the front-end, check out the iOS SDK, news about the Android SDK, and the Windows Phone SDK.

Various Languages: Windows Azure supports the .NET stack of languages, as well as many Open-Source languages like Java, Python, PHP, Ruby, NodeJS, C++ and more.

Use Software

Also called “Software as a Service” (SaaS) this often means consumer or business-level software like Hotmail or Office 365. In other words, you simply log on, use the software, and log off – there’s nothing to install, and little to even configure. For the Information Technology professional, however, It’s not quite the same. We want software that provides services, but in a platform. That means we want things like Hadoop or other software we don’t want to have to install and configure.

Components you can use:

Kits: Various software “kits” or packages are supported with just a few clicks, such as Umbraco, Wordpress, and others.

Windows Azure Media Services: Windows Azure Media Services is a suite of services that allows you to upload media for encoding, processing and even streaming – or even one or more of those functions. We can add DRM and even commercials to your media if you like. Windows Azure Media Services is used to stream large events all the way down to small training videos.

High Performance Computing and “Big Data”: Windows Azure allows you to scale to huge workloads using a few clicks to deploy Hadoop Clusters or the High Performance Computing (HPC) nodes, accepting HPC Jobs, Pig and Hive Jobs, and even interfacing with Microsoft Excel.

Windows Azure Marketplace: Windows Azure Marketplace offers data and programs you can quickly implement and use – some free, some for-fee.

Run Software

Also known as “Infrastructure as a Service” (IaaS), this offering allows you to build or simply choose a Virtual Machine to run server-based software.

Components you can use:

Persistent Virtual Machines: You can choose to install Windows Server, Windows Server with Active Directory, with SQL Server, or even SharePoint from a pre-configured gallery. You can configure your own server images with standard Hyper-V technology and load them yourselves – and even bring them back when you’re done. As a new offering, we also even allow you to select various distributions of Linux – a first for Microsoft.

Windows Azure Connect: You can connect your on-premises networks to Windows Azure Instances.

Storage: Windows Azure Storage can be used as a remote backup, a hybrid storage location and more using software or even hardware appliances.

Decision Matrix

With all of these options, you can use Windows Azure to solve just about any computing problem. It’s often hard to know when to use something on-premises, in the cloud, and what kind of service to use.

I’ve used a decision matrix in the last couple of years to take a particular problem and choose the proper technology to solve it. It’s all about options – there is no “silver bullet”, whether that’s Windows Azure or any other set of functions. I take the problem, decide which particular component I want to own and control – and choose the column that has that box darkened. For instance, if I have to control the wiring for a solution (a requirement in some military and government installations), that means the “Networking” component needs to be dark, and so I select the “On Premises” column for that particular solution. If I just need the solution provided and I want no control at all, I can look as “Software as a Service” solutions.

Security, Pricing, and Other Info

Security: Security is one of the first questions you should ask in any distributed computing environment. We have certification info, coding guidelines and more, even a general “Request for Information” RFI Response already created for you.

Pricing: Are there licenses? How much does this cost? Is there a way to estimate the costs in this new environment?

New Features: Many new features were added to Windows Azure - and you can keep up to date with community information released monthly here: http://blogs.msdn.com/b/davidmcg/

Windows Azure Cookbooks: Great resource for architecture solutions - http://www.notsotrivial.net/blog/category/Architecture.aspx

Support: Software Support on Virtual Machines, general support, support plans

Hands-On Labs: http://msdn.microsoft.com/en-us/jj618399

Windows Azure Capability Discussion Presentation and Windows Azure Solution Implementer Guide and Windows Azure Business Priorities Guide

Windows Azure End to End Examples

BuckWoody — Tue, 29 May 2012 13:45:59 GMT

I’m fascinated by the way people learn. I’m told there are several methods people use to understand new information, from reading to watching, from experiencing to exploring.

Personally, I use multiple methods of learning when I encounter a new topic, usually starting with reading a bit about the concepts. I quickly want to put those into practice, however, especially in the technical realm. I immediately look for examples where I can start trying out the concepts. But I often want a “real” example – not just something that represents the concept, but something that is real-world, showing some feature I could actually use.

And it’s no different with the Windows Azure platform – I like finding things I can do now, and actually use. So when I started learning Windows Azure, I of course began with the Windows Azure Training Kit – which has lots of examples and labs, presentations and so on. But from there, I wanted more examples I could learn from, and eventually teach others with. I was asked if I would write a few of those up, so here are the ones I use.

CodePlex

CodePlex is Microsoft’s version of an “Open Source” repository. Anyone can start a project, add code, documentation and more to it and make it available to the world, free of charge, using various licenses as they wish. Microsoft also uses this location for most of the examples we publish, and sample databases for SQL Server.

If you search in CodePlex for “Azure”, you’ll come back with a list of projects that folks have posted, including those of us at Microsoft. The source code and documentation are there, so you can learn using actual examples of code that will do what you need. There’s everything from a simple table query to a full project that is sort of a “Corporate Dropbox” that uses Windows Azure Storage.

The advantage is that this code is immediately usable. It’s searchable, and you can often find a complete solution to meet your needs. The disadvantage is that the code is pretty specific – it may not cover a huge project like you’re looking for. Also, depending on the author(s), you might not find the documentation level you want.

Link: http://azureexamples.codeplex.com/site/search?query=Azure&ac=8

Tailspin

Microsoft Patterns and Practices is a group here that does an amazing job at sharing standard ways of doing IT – from operations to coding. If you’re not familiar with this resource, make sure you read up on it. Long before I joined Microsoft I used their work in my daily job – saved a ton of time. It has resources not only for Windows Azure but other Microsoft software as well.

The Patterns and Practices group also publishes full books – you can buy these, but many are also online for free. There’s an end-to-end example for Windows Azure using a company called “Tailspin”, and the work covers not only the code but the design of the full solution. If you really want to understand the thought that goes into a Platform-as-a-Service solution, this is an excellent resource.

The advantages are that this is a book, it’s complete, and it includes a discussion of design decisions. The disadvantage is that it’s a little over a year old – and in “Cloud” years that’s a lot. So many things have changed, improved, and have been added that you need to treat this as a resource, but not the only one. Still, highly recommended.

Link: http://msdn.microsoft.com/en-us/library/ff728592.aspx

Azure Stock Trader

Sometimes you need a mix of a CodePlex-style application, and a little more detail on how it was put together. And it would be great if you could actually play with the completed application, to see how it really functions on the actual platform.

That’s the Azure Stock Trader application. There’s a place where you can read about the application, and then it’s been published to Windows Azure – the production platform – and you can use it, explore, and see how it performs.

I use this application all the time to demonstrate Windows Azure, or a particular part of Windows Azure.

The advantage is that this is an end-to-end application, and online as well. The disadvantage is that it takes a bit of self-learning to work through.

Links: Learn it: http://msdn.microsoft.com/en-us/netframework/bb499684 Use it: https://azurestocktrader.cloudapp.net/

Pay in the future should make you think in the present

BuckWoody — Tue, 10 Apr 2012 13:53:50 GMT

Distributed Computing - and more importantly “-as-a-Service” models of computing have a different cost model. This is something that sounds obvious on the surface but it’s often forgotten during the design and coding phase of a project.

In on-premises computing, we’re used to purchasing a server and all of the hardware infrastructure and software licenses needed not only for one project, but several. This is an up-front or “sunk” cost that we consume by running code the organization needs to perform its function. Using a direct connection over wires you’ve already paid for, we don’t often have to think about bandwidth, hits on the data store or the amount of compute we use - we just know more is better. In a pay-as-you-go model, however, each of these architecture decisions has a potential cost impact. The amount of data you store, the number of times you access it, and the amount you send back all come with a charge. The offset is that you don’t buy anything at all up-front, so that sunk cost is freed up. And financial professionals know that money now is worth more than money later. Saving that up-front cost allows you to invest it in other things.

It’s not just that you’re using things that now cost money - it’s that the design itself in distributed computing has a cost impact. That can be a really good thing, such as when you dynamically add capacity for paying customers. If you can tie back the cost of a series of clicks to what a user will pay to do so, you can set a profit margin that is easy to track.

Here’s a case in point: Assume you are using a large instance in Windows Azure to compute some data that you retrieve from a SQL Azure database. If you don’t monitor the path of the application, you may not know what you are really using. Since you’re paying by the size of the instance, it’s best to maximize it all the time. Recently I evaluated just this situation, and found that downsizing the instance and adding another one where needed, adding a caching function to the application, moving part of the data into Windows Azure tables not only increased the speed of the application, but reduced the cost and more closely tied the cost to the profit.

The key is this: from the very outset - the design - make sure you include metrics to measure for the cost/performance (sometimes these are the same) for your application. Windows Azure opens up awesome new ways of doing things, so make sure you study distributed systems architecture before you try and force in the application design you have on premises into your new application structure.

“I could use a little help here” or “I can do it myself, thank you” for Cloud Projects

BuckWoody — Tue, 03 Apr 2012 13:20:55 GMT

Windows Azure allows you to write code in languages within the .NET stack, you can use Java, C++, PHP, NodeJS and others. Code is code - other than keeping things stateless, using a Web or Worker Role in Azure is not all that different from working with an on-premises system.

However….

Working in a scalable, component-based stateless architecture that can use federated security is not all that common for many developers. Some are used to owning the server, scaling up, and state-full paradigms that have a single security domain. Making the transition whilst trying to create a new software application or even port a previous one can be daunting. Sure, we have absolutely tons of free training, kits, videos, online books and more to learn on your own, but some things like architecture can be pivotal as you move along.

So the question is, should you just strike out on your own for a Cloud project, or get Microsoft Consulting Services or another partner to work with you on your first one? I use a few decision points to help guide the projects I assist in.

Note: I’m a huge fan of having help that ends up giving you training and leaves you in charge. If you do engage with someone to help you, make sure you keep this clear and take more and more ownership yourself as the project progresses.

How much time do you have?

Usually the first thing I ask is about the timeline for the project. It doesn’t matter how skilled you are, if you have a short window to get things done it’s better to get help - especially if this is your first cloud project. Having someone that knows the platform well can save you amazing amounts of time. If you have longer, then start with the training in the link above and once you feel confident, jump in.

How complex is the project?

If there are a lot of moving parts, it’s best to engage a partner. The reason is that certain interactions - particularly things like Service Bus or Data Integration - can be quite different than what you may have encountered before.

How many people do you have?

I have a “pizza rule” about projects I’ve used in my career - if it takes over two pizzas to feed everyone on the project, it’s too big and will fail. That being said, one developer and a one-week deadline does not a good project make, usually. It’s best to have at least one architect (or someone in that role) guiding the project along, and at least two developers to work on a cloud project. That’s a generalization of course, since I’ve seen great software on Azure with one developer writing code all by herself, but for more complex projects, more (to a point) is better. The nice thing about bringing on a partner is that you don’t have to hire them full time - they help you and then they go away.

How critical is the project?

There’s no shame in using some help. If the platform is new, if the project is large and complex, and if it is critical to the business, you should engage a partner. That’s regardless of Cloud or anything else - get some help. You don’t want to hit your company’s bottom line in a negative way, but you have to innovate and get them a competitive advantage. Do your research, make sure the partner is qualified to help you, and get it done.

Don’t let these questions scare you off. There are lots of projects you can implement on Windows and SQL Azure with nothing other than the Software Development Kit (SDK) that you get for free with Windows Azure. And assistance comes in many forms - sometimes just phone support, a friend you can ask. Microsoft Consulting Services or any of our great partners. You can get help on just the architecture piece or have them show you how to write the code. They’ll get involved as little or as much as you like.

SQL Azure and Trust Services

BuckWoody — Tue, 27 Mar 2012 12:32:17 GMT

Microsoft is working on a new Windows Azure service called “Trust Services”. Trust Services takes a certificate you upload and uses it to encrypt and decrypt sensitive data in the cloud. Of course, like any security service, there’s a bit more to it than that. I’ll give you a quick overview of how you can use this product to protect data you send to SQL Azure.

The primary issue with storing data in the cloud is that you are in an environment that isn’t under your control – in fact, that’s the benefit of being in a distributed computing environment in the first place. On premises you’re able to encrypt data you don’t want anyone else to see, using various methods such as passwords (not very strong) or certificates (stronger). When you use a certificate, it’s vital that you create (or procure) and protect it yourself.

When you store data remotely, regardless of IaaS, PaaS or SaaS, you don’t own the machines where the data lives. That means if you use a certificate from the cloud vendor to encrypt the data, you have to trust that the data won’t be accessed by the vendor. In some cases having a signed agreement with the vendor that they won’t access your data is sufficient, in other cases that doesn’t meet the requirements your system has for security.

With the new Trust Services service, the basic process is that you use a Portal to create a Trust Server using policies and other controls. You place a X.509 Certificate you create or procure in that server. Using the Software development Kit (SDK), the developer has access to an Application Layer Encryption Framework to set fields of data they want to encrypt. From there, the data can be stored in SQL Azure as a standard field – only it is encrypted before it ever arrives. The portion of the client software that decrypts the data uses the same service, so the authenticated user sees the data if they are allowed to do so. The data remains encrypted “at rest”.

You can learn more about this product and check it out in the SQL Azure labs at Microsoft Codename "Trust Services"

Java Resources for Windows Azure

BuckWoody — Tue, 13 Mar 2012 00:45:00 GMT

Windows Azure is a Platform as a Service – a PaaS – that runs code you write. That code doesn’t just mean the languages on the .NET platform – you can run code from multiple languages, including Java. In fact, you can develop for Windows and SQL Azure using not only Visual Studio but the Eclipse Integrated Development Environment (IDE) as well.

Although not an exhaustive list, here are several links that deal with Java and Windows Azure:

Resource	Link
Windows Azure Java Development Center	http://www.windowsazure.com/en-us/develop/java/
Java Development Guidance	http://msdn.microsoft.com/en-us/library/hh690943(VS.103).aspx
Running a Java Environment on Windows Azure	http://blogs.technet.com/b/port25/archive/2010/10/28/running-a-java-environment-on-windows-azure.aspx
Running a Java Environment on Windows Azure	http://blogs.technet.com/b/port25/archive/2010/10/28/running-a-java-environment-on-windows-azure.aspx
Run Java with Jetty in Windows Azure	http://blogs.msdn.com/b/dachou/archive/2010/03/21/run-java-with-jetty-in-windows-azure.aspx
Using the plugin for Eclipse	http://blogs.msdn.com/b/craig/archive/2011/03/22/new-plugin-for-eclipse-to-get-java-developers-off-the-ground-with-windows-azure.aspx
Run Java with GlassFish in Windows Azure	http://blogs.msdn.com/b/dachou/archive/2011/01/17/run-java-with-glassfish-in-windows-azure.aspx
Improving experience for Java developers with Windows Azure	http://blogs.msdn.com/b/interoperability/archive/2011/02/23/improving-experience-for-java-developers-with-windows-azure.aspx
Java Access to SQL Azure via the JDBC Driver for SQL Server	http://blogs.msdn.com/b/brian_swan/archive/2011/03/29/java-access-to-sql-azure-via-the-jdbc-driver-for-sql-server.aspx
How to Get Started with Java, Tomcat on Windows Azure	http://blogs.msdn.com/b/usisvde/archive/2011/03/04/how-to-get-started-with-java-tomcat-on-windows-azure.aspx
Deploying Java Applications in Azure	http://blogs.msdn.com/b/mariok/archive/2011/01/05/deploying-java-applications-in-azure.aspx
Using the Windows Azure Storage Explorer in Eclipse	http://blogs.msdn.com/b/brian_swan/archive/2011/01/11/using-the-windows-azure-storage-explorer-in-eclipse.aspx
Windows Azure Tomcat Solution Accelerator	http://archive.msdn.microsoft.com/winazuretomcat
Deploying a Java application to Windows Azure with Command-line Ant	http://java.interoperabilitybridges.com/articles/deploying-a-java-application-to-windows-azure-with-command-line-ant
Video: Open in the Cloud: Windows Azure and Java	http://channel9.msdn.com/Events/PDC/PDC10/CS10
AzureRunMe	http://azurerunme.codeplex.com/
Windows Azure SDK for Java	http://www.interoperabilitybridges.com/projects/windows-azure-sdk-for-java
AppFabric SDK for Java	http://www.interoperabilitybridges.com/projects/azure-java-sdk-for-net-services
Information Cards for Java	http://www.interoperabilitybridges.com/projects/information-card-for-java
Apache Stonehenge	http://www.interoperabilitybridges.com/projects/apache-stonehenge
Channel 9 Case Study on Java and Windows Azure	http://www.microsoft.com/casestudies/Windows-Azure/Gigaspaces/Solution-Provider-Streamlines-Java-Application-Deployment-in-the-Cloud/400000000081

SQL Azure - Requiring Encrypt=True

BuckWoody — Tue, 06 Mar 2012 13:43:11 GMT

(Many thanks to Peter Gvozdjak and Dan Benediktson here at Microsoft who worked with me on this issue and provided the bulk of information for this post)

Recently I had a customer inquire about some performance tuning he wanted to do for SQL Azure, and as part of that he found that it was possible to remove the “Encrypt=True” setting on the ADO.NET connection to SQL Azure. We have always stated that the connections to SQL Azure are encrypted, so being able to remove this string surprised him. (More on that reference here: http://msdn.microsoft.com/en-us/library/windowsazure/ff394108.aspx)

It is true that all connections to SQL Azure are encrypted - whether you use the Encrypt=True string or not. We’ll force the connection to encrypt even if you don’t, or we won’t route it. However, you do want to use that string, for a couple of reasons.

Whenever you include the Encrypt=True string, the connection will require that your client validate the Certificate that SQL Azure presents, to ensure that key is the one used by Microsoft. If you don’t include that string, it’s possible - not probable, but possible - that someone could set up a false DNS to cause your certificate to be validated elsewhere.

So don’t give the bad guys a way in - there is no performance gain (other than perhaps if the bad DNS is in your own building!) by leaving it off. Follow the best practice of using Encrypt=True.

There’s more on connection management for things like retries and so on here: http://social.technet.microsoft.com/wiki/contents/articles/sql-azure-connection-management.aspx

Big Data - A Microsoft Tools Approach

BuckWoody — Mon, 20 Feb 2012 21:16:00 GMT

(As with all of these types of posts, check the date of the latest update I’ve made here. Anything older than 6 months is probably out of date, given the speed with which we release new features into Windows and SQL Azure)

I don’t normally like to discuss things in terms of tools. I find that whenever you start with a given tool (or even a tool stack) it’s too easy to fit the problem to the tool(s), rather than the other way around as it should be.

That being said, it’s often useful to have an example to work through to better understand a concept. But like many ideas in Computer Science, “Big Data” is too broad a term in use to show a single example that brings out the multiple processes, use-cases and patterns you can use it for.

So we turn to a description of the tools you can use to analyze large data sets. “Big Data” is a term used lately to describe data sets that have the “Four V’s” as a characteristic, but I have a simpler definition I like to use:

Big Data involves a data set too large to process in a reasonable period of time

I realize that’s a bit broad, but in my mind it answers the question and is fairly future-proof. The general idea is that you want to analyze some data, and using whatever current methods, storage, compute and so on that you have at hand it doesn’t allow you to finish processing it in a time period that you are comfortable with. I’ll explain some new tools you can use for this processing.

Yes, this post is Microsoft-centric. There are probably posts from other vendors and open-source that cover this process in the way they best see fit. And of course you can always “mix and match”, meaning using Microsoft for one or more parts of the process and other vendors or open-source for another. I never advise that you use any one vendor blindly - educate yourself, examine the facts, perform some tests and choose whatever mix of technologies best solves your problem.

At the risk of being vendor-specific, and probably incomplete, I use the following short list of tools Microsoft has for working with “Big Data”. There is no single package that performs all phases of analysis. These tools are what I use; they should not be taken as a Microsoft authoritative testament to the toolset we’ll finalize for a given problem-space. In fact, that’s the key: find the problem and then fit the tools to that.

Process Types

I break up the analysis of the data into two process types. The first is examining and processing the data in-line, meaning as the data passes through some process. The second is a store-analyze-present process.

Processing Data In-Line

Processing data in-line means that the data doesn’t have a destination - it remains in the source system. But as it moves from an input or is routed to storage within the source system, various methods are available to examine the data as it passes, and either trigger some action or create some analysis.

You might not think of this as “Big Data”, but in fact it can be. Organizations have huge amounts of data stored in multiple systems. Many times the data from these systems do not end up in a database for evaluation. There are options, however, to evaluate that data real-time and either act on the data or perhaps copy or stream it to another process for evaluation.

The advantage of an in-stream data analysis is that you don’t necessarily have to store the data again to work with it. That’s also a disadvantage - depending on how you architect the solution, you might not retain a historical record. One method of dealing with this requirement is to trigger a rollup collection or a more detailed collection based on the event.

StreamInsight - StreamInsight is Microsoft’s “Complex Event Processing” or CEP engine. This product, hooked into SQL Server 2008R2, has multiple ways of interacting with a data flow. You can create adapters to talk with systems, and then examine the data mid-stream and create triggers to do something with it. You can read more about StreamInsight here: http://msdn.microsoft.com/en-us/library/ee391416(v=sql.110).aspx

BizTalk - When there is more latency available between the initiation of the data and its processing, you can use Microsoft BizTalk. This is a message-passing and Service Bus oriented tool, and it can also be used to join system’s data together than normally does not have a direct link, for instance a Mainframe system to SQL Server. You can learn more about BizTalk here: http://www.microsoft.com/biztalk/en/us/overview.aspx

.NET and the Windows Azure Service Bus - Along the same lines as BizTalk but with a more programming-oriented design are the Windows and Windows Azure Service Bus tools. The Service Bus allows you to pass messages as well, and opens up web interactions and even inter-company routing. BizTalk can do this as well, but the Service Bus tools use an API approach for designing the flow and interfaces you want. The Service Bus offerings are also intended as near real-time, not as a streaming interface. You can learn more about the Windows Azure Service Bus here: http://www.windowsazure.com/en-us/home/tour/service-bus/ and more about the Event Processing side here: http://msdn.microsoft.com/en-us/magazine/dd569756.aspx

Store-Analyze-Present

A more traditional approach with an organization’s data is to store the data and analyze it out-of-band. This began with simply running code over a data store, but as locking and blocking became an issue on a file system, Relational Database Management Systems (RDBMs) were created. Over time a distinction was made between data used in an online processing system, meant to be highly available for writing data (OLTP) and systems designed for analytical and reporting purposes (OLAP).

Later the data grew larger than these systems were designed for, primarily due to consistency requirements. In analysis, however, consistency isn’t always a requirement, and so file-based systems for that analysis were re-introduced from the Mainframe concepts, with new technology layered in for speed and size.

I normally break up the process of analyzing large data sets into four phases:

Source and Transfer - Obtaining the data at its source and transferring or loading it into the storage; optionally transforming it along the way
Store and Process - Data is stored on some sort of persistence, and in some cases an engine handles the acquisition and placement on persistent storage, as well as retrieval through an interface.
Analysis - A new layer introduced with “Big Data” is a separate analysis step. This is dependent on the engine or storage methodology, is often programming language or script based, and sometimes re-introduces the analysis back into the data. Some engines and processes combine this function into the previous phase.
Presentation - In most cases, the data wants a graphical representation to comprehend, especially in a series or trend analysis. In other cases a simple symbolic representation, similar to the “dashboard” elements in a Business Intelligence suite. Presentation tools may also have an analysis or refinement capability to allow end-users to work with the data sets. As in the Analysis phase, some methodologies bundle in the Analysis and Presentation phases into one toolset.

Source and Transfer

You’ll notice in this area, along with those that follow, Microsoft is adopting not only its own technologies but those within open-source. This is a positive sign, and means that you will have a best-of-breed, supported set of tools to move the data from one location to another. Traditional file-copy, File Transfer Protocol and more are certainly options, but do not normally deal with moving datasets.

I’ve already mentioned the ability of a streaming tool to push data into a store-analyze-present model, so I’ll follow up that discussion with the tools that can extract data from one source and place it in another.

SQL Server Integration Services (SSIS)/SQL Server Bulk Copy Program (BCP) - SSIS is a SQL Server tool used to move data from one location to another, and optionally perform transform or other processes as it does so. You are not limited to working with SQL Server data - in fact, almost any modern source of data from text to various database platforms is available to move to various systems. It is also extremely fast and has a rich development environment. You can learn more about SSIS here: http://msdn.microsoft.com/en-us/library/ms141026.aspx BCP is a tool that has been used with SQL Server data since the first releases; it has multiple sources and destinations as well. It is a command-line utility,and has some limited transform capabilities. You can learn more about BCP here: http://msdn.microsoft.com/en-us/library/ms162802.aspx

Sqoop - Tied to Microsoft’s latest announcements with Hadoop on Windows and Windows Azure, Sqoop is a tool that is used to move data between SQL Server 2008R2 (and higher) and Hadoop, quickly and efficiently. You can read more about that in the Readme file here: http://www.microsoft.com/download/en/details.aspx?id=27584

Application Programming Interfaces - API’s exist in most every major language that can connect to one data source, access data, optionally transforming it and storing it in another system. Most every dialect of the .NET-based languages contain methods to perform this task.

Store and Process

Data at rest is normally used for historical analysis. In some cases this analysis is performed near real-time, and in others historical data is analyzed periodically. Systems that handle data at rest range from simple storage to active management engines.

SQL Server - Microsoft’s flagship RDBMS can indeed store massive amounts of complex data. I am familiar with a two systems in excess of 300 Terabytes of federated data, and the Pan-Starrs project is designed to handle 1+ Petabyte of data. The theoretical limit of SQL Server DataCenter edition is 540 Petabytes. SQL Server is an engine, so the data access and storage is handled in an abstract layer that also handles concurrency for ACID properties. You can learn more about SQL Server here: http://www.microsoft.com/sqlserver/en/us/product-info/compare.aspx

SQL Azure Federations - SQL Azure is a database service from Microsoft associated with the Windows Azure platform. Database Servers are multi-tenant, but are shared across a “fabric” that moves active databases for redundancy and performance. Copies of all databases are kept triple-redundant with a consistent commitment model. Databases are (at this writing - check http://WindowsAzure.com for the latest) capped at a 150 GB size limit per database. However, Microsoft released a “Federation” technology, allowing you to query a head node and have the data federated out to multiple databases. This improves both size and performance. You can read more about SQL Azure Federations here: http://social.technet.microsoft.com/wiki/contents/articles/2281.federations-building-scalable-elastic-and-multi-tenant-database-solutions-with-sql-azure.aspx

Analysis Services - The Business Intelligence engine within SQL Server, called Analysis Services, can also handle extremely large data systems. In addition to traditional BI data store layouts (ROLAP, MOLAP and HOLAP), the latest version of SQL Server introduces the Vertipaq column-storage technology allowing more direct access to data and a different level of compression. You can read more about Analysis Services here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/analysis-services.aspx and more about Vertipaq here: http://msdn.microsoft.com/en-us/library/hh212945(v=SQL.110).aspx

Parallel Data Warehouse - The Parallel Data Warehouse (PDW) offering from Microsoft is largely described by the title. Accessed in multiple ways including using Transact-SQL (the Microsoft dialect of the Structured Query Language), This is an MPP appliance scaling in parallel to extremely large datasets. It is a hardware and software offering - you can learn more about it here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/data-warehousing/pdw.aspx

HPC Server - Microsoft’s High-Performance Computing version of Windows Server deals not only with large data sets, but with extremely complicated computing requirements. A scale-out architecture and inter-operation with Linux systems, as well as dozens of applications pre-written to work with this server make this a capable “Big Data” system. It is a mature offering, with a long track record of success in scientific, financial and other areas of data processing. It is available both on premises and in Windows Azure, and also in a hybrid of both models, allowing you to “rent” a super-computer when needed. You can read more about it here: http://www.microsoft.com/hpc/en/us/product/cluster-computing.aspx

Hadoop - Pairing up with Hortonworks, Microsoft has released the Hadoop Open-Source system - including HDFS and a Map/Reduce standardized software, Hive and Pig - on Windows and the Windows Azure platform. This is not a customized version; off-the-shelf concepts and queries work well here. You can read more about Hadoop here: http://hadoop.apache.org/common/docs/current/ and you can read more about Microsoft’s offerings here: http://hortonworks.com/partners/microsoft/ and here: http://social.technet.microsoft.com/wiki/contents/articles/6204.hadoop-based-services-for-windows.aspx

Windows and Azure Storage - Although not an engine - other than a triple-redundant, immediately consistent commit - Windows Azure can hold terabytes of information and make it available to everything from the R programming language to the Hadoop offering. Binary storage (Blobs) and Table storage (Key-Value Pair) data can be queried across a distributed environment. You can learn more about Windows Azure storage here: http://msdn.microsoft.com/en-us/library/windowsazure/gg433040.aspx

Analysis

In a “Big Data” environment, it’s not unusual to have a specialized set of tasks for analyzing and even interpreting the data. This is a new field called “data Science”, with a requirement not only for computing, but also a heavy emphasis on math.

Transact-SQL - T-SQL is the dialect of the Structured Query Language used by Microsoft. It includes not only robust selection, updating and manipulating of data, but also analytical and domain-level interrogation as well. It can be used on SQL Server, PDW and ODBC data sources. You can read more about T-SQL here: http://msdn.microsoft.com/en-us/library/bb510741.aspx

Multidimensional Expressions and Data Analysis Expressions - The MDX and DAX languages allow you to query multidimensional data models that do not fit well with typical two-plane query languages. Pivots, aggregations and more are available within these constructs to query and work with data in Analysis Services. You can read more about MDX here: http://msdn.microsoft.com/en-us/library/ms145506(v=sql.110).aspx and more about DAX here: http://www.microsoft.com/download/en/details.aspx?id=28572

HPC Jobs and Tasks - Work submitted to the Windows HPC Server has a particular job - essentially a reservation request for resources. Within a job you can submit tasks, such as parametric sweeps and more. You can learn more about Jobs and Tasks here: http://technet.microsoft.com/en-us/library/cc719020(v=ws.10).aspx

HiveQL - HiveQL is the language used to query a Hive object running on Hadoop. You can see a tutorial on that process here: http://social.technet.microsoft.com/wiki/contents/articles/6628.aspx

Piglatin - Piglatin is the submission language for the Pig implementation on Hadoop. An example of that process is here: http://blogs.msdn.com/b/avkashchauhan/archive/2012/01/10/running-apache-pig-pig-latin-at-apache-hadoop-on-windows-azure.aspx

Application Programming Interfaces - Almost all of the analysis offerings have associated API’s - of special note is Microsoft Research’s Infer.NET, a new language construct for framework for running Bayesian inference in graphical models, as well as probabilistic programming. You can read more about Infer.NET here: http://research.microsoft.com/en-us/um/cambridge/projects/infernet/

Presentation

Lots of tools work in presenting the data once you have done the primary analysis. In fact, there’s a great video of a comparison of various tools here: http://msbiacademy.com/Lesson.aspx?id=73 Primarily focused on Business Intelligence. That term itself is now not as completely defined, but the tools I’ll show below can be used in multiple ways - not just traditional Business Intelligence scenarios. Application Programming Interfaces (API’s) can also be used for presentation; but I’ll focus here on “out of the box” tools.

Excel - Microsoft’s Excel can be used not only for single-desk analysis of data sets, but with larger datasets as well. It has interfaces into SQL Server, Analysis Services, can be connected to the PDW, and is a first-class job submission system for the Windows HPC Server. You can watch a video about Excel and big data here: http://www.microsoft.com/en-us/showcase/details.aspx?uuid=e20b7482-11c9-4965-b8f0-7fb6ac7a769f and you can also connect Excel to Hadoop: http://social.technet.microsoft.com/wiki/contents/articles/how-to-connect-excel-to-hadoop-on-azure-via-hiveodbc.aspx

Reporting Services - Reporting Services is a SQL Server tool that can query and show data from multiple sources, all at once. It can also be used with Analysis Services. You can read more about Reporting Services here: http://www.microsoft.com/sqlserver/en/us/solutions-technologies/business-intelligence/reporting-services.aspx

Power View - Power View is a “Self-Service” Business Intelligence reporting tool, which can work with on-premises data in addition to SQL Azure and other data. You can read more about it and see videos of Power View in action here: http://www.microsoft.com/sqlserver/en/us/future-editions/business-intelligence/SQL-Server-2012-reporting-services.aspx

SharePoint Services - Microsoft has rolled several capable tools in SharePoint as “Services”. This has the advantage of being able to integrate into the working environment of many companies. You can read more about lots of these reporting and analytic presentation tools here: http://technet.microsoft.com/en-us/sharepoint/ee692578

This is by no means an exhaustive list - more capabilities are added all the time to Microsoft’s products, and things will surely shift and merge as time goes on. Expect today’s “Big Data” to be tomorrow’s “Laptop Environment”.

The Data Scientist

BuckWoody — Tue, 15 Nov 2011 15:00:18 GMT

A new term - well, perhaps not that new - has come up and I’m actually very excited about it. The term is Data Scientist, and since it’s new, it’s fairly undefined. I’ll explain what I think it means, and why I’m excited about it.

In general, I’ve found the term deals at its most basic with analyzing data. Of course, we all do that, and the term itself in that definition is redundant. There is no science that I know of that does not work with analyzing lots of data. But the term seems to refer to more than the common practices of looking at data visually, putting it in a spreadsheet or report, or even using simple coding to examine data sets.

The term Data Scientist (as far as I can make out this early in it’s use) is someone who has a strong understanding of data sources, relevance (statistical and otherwise) and processing methods as well as front-end displays of large sets of complicated data. Some - but not all - Business Intelligence professionals have these skills. In other cases, senior developers, database architects or others fill these needs, but in my experience, many lack the strong mathematical skills needed to make these choices properly.

I’ve divided the knowledge base for someone that would wear this title into three large segments. It remains to be seen if a given Data Scientist would be responsible for knowing all these areas or would specialize. There are pretty high requirements on the math side, specifically in graduate-degree level statistics, but in my experience a company will only have a few of these folks, so they are expected to know quite a bit in each of these areas.

Persistence

The first area is finding, cleaning and storing the data. In some cases, no cleaning is done prior to storage - it’s just identified and the cleansing is done in a later step. This area is where the professional would be able to tell if a particular data set should be stored in a Relational Database Management System (RDBMS), across a set of key/value pair storage (NoSQL) or in a file system like HDFS (part of the Hadoop landscape) or other methods. Or do you examine the stream of data without storing it in another system at all?

This is an important decision - it’s a foundation choice that deals not only with a lot of expense of purchasing systems or even using Cloud Computing (PaaS, SaaS or IaaS) to source it, but also the skillsets and other resources needed to care and feed the system for a long time. The Data Scientist sets something into motion that will probably outlast his or her career at a company or organization.

Often these choices are made by senior developers, database administrators or architects in a company. But sometimes each of these has a certain bias towards making a decision one way or another. The Data Scientist would examine these choices in light of the data itself, starting perhaps even before the business requirements are created. The business may not even be aware of all the strategic and tactical data sources that they have access to.

Processing

Once the decision is made to store the data, the next set of decisions are based around how to process the data. An RDBMS scales well to a certain level, and provides a high degree of ACID compliance as well as offering a well-known set-based language to work with this data. In other cases, scale should be spread among multiple nodes (as in the case of Hadoop landscapes or NoSQL offerings) or even across a Cloud provider like Windows Azure Table Storage. In fact, in many cases - most of the ones I’m dealing with lately - the data should be split among multiple types of processing environments. This is a newer idea. Many data professionals simply pick a methodology (RDBMS with Star Schemas, NoSQL, etc.) and put all data there, regardless of its shape, processing needs and so on.

A Data Scientist is familiar not only with the various processing methods, but how they work, so that they can choose the right one for a given need. This is a huge time commitment, hence the need for a dedicated title like this one.

Presentation

This is where the need for a Data Scientist is most often already being filled, sometimes with more or less success. The latest Business Intelligence systems are quite good at allowing you to create amazing graphics - but it’s the data behind the graphics that are the most important component of truly effective displays.

This is where the mathematics requirement of the Data Scientist title is the most unforgiving. In fact, someone without a good foundation in statistics is not a good candidate for creating reports. Even a basic level of statistics can be dangerous. Anyone who works in analyzing data will tell you that there are multiple errors possible when data just seems right - and basic statistics bears out that you’re on the right track - that are only solvable when you understanding why the statistical formula works the way it does.

And there are lots of ways of presenting data. Sometimes all you need is a “yes” or “no” answer that can only come after heavy analysis work. In that case, a simple e-mail might be all the reporting you need. In others, complex relationships and multiple components require a deep understanding of the various graphical methods of presenting data. Knowing which kind of chart, color, graphic or shape conveys a particular datum best is essential knowledge for the Data Scientist.

Why I’m excited

I love this area of study. I like math, stats, and computing technologies, but it goes beyond that. I love what data can do - how it can help an organization. I’ve been fortunate enough in my professional career these past two decades to work with lots of folks who perform this role at companies from aerospace to medical firms, from manufacturing to retail.

Interestingly, the size of the company really isn’t germane here. I worked with one very small bio-tech (cryogenics) company that worked deeply with analysis of complex interrelated data.

So watch this space. No, I’m not leaving Azure or distributed computing or Microsoft. In fact, I think I’m perfectly situated to investigate this role further. We have a huge set of tools, from RDBMS to Hadoop to allow me to explore. And I’m happy to share what I learn along the way.

Buck Woody : SQL Azure

How Does the Cloud Change a Database Administrator’s Job?

The Database Administrator Role

How the Cloud Changes Things

Database Platform Architecture

Audiences

HADR

Data Services

Microsoft Windows Azure Disaster Recovery Options for On-Premises SQL Server

Backups

Database Mirroring

Log Shipping

AlwaysOn Availability Groups

Other Solution Options

The Windows Azure Software Development Kit (SDK) and the Windows Azure Training Kit (WATK)

The Windows Azure Software Development Kit (SDK)

The Windows Azure Training Kit (WATK)

Windows Azure Database (SQL Azure) Development Tip

In the Cloud, Everything Costs Money

Management and Monitoring Tools for Windows Azure

The Windows Azure Management Portal

Windows Azure Management API

PowerShell Command-lets

System Center

SQL Server Management Studio / Data Tools / Visual Studio

Vendor-Provided Tools

Open-Source Tools

Windows Azure – Write, Run or Use Software

Write Software

Use Software

Run Software

Decision Matrix

Security, Pricing, and Other Info

Windows Azure End to End Examples

CodePlex

Tailspin

Azure Stock Trader

Pay in the future should make you think in the present

“I could use a little help here” or “I can do it myself, thank you” for Cloud Projects

SQL Azure and Trust Services

Java Resources for Windows Azure

SQL Azure - Requiring Encrypt=True

Big Data - A Microsoft Tools Approach

Process Types

Processing Data In-Line

Store-Analyze-Present

Source and Transfer

Store and Process

Analysis

Presentation

The Data Scientist