Herve Roggero

Upcoming Florida Windows Azure Events–Summary until June 2013

2013-04-05T08:26:14-12:00:00

So many things are shaking and moving in Florida that I thought I would provide a summary of the events I know about that involve Windows Azure at some level. Here is a partial list; please add a comment if you know of other events.

Event	Location	Date	URL
IT Camp	Fort Lauderdale	April 11 2013	https://msevents.microsoft.com/cui/EventDetail.aspx?EventID=1032541828&culture=en-US
Azure Roadshow for IT Pros	Pensacola	April 16 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543094&Culture=en-US&community=0
IT Camp	Tampa	April 18 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032541830&culture=en-us
West Palm Dev – Azure Media Services	West Palm Beach	April 23 2013	http://www.fladotnet.com/Reg.aspx?EventID=653
Azure Roadshow for IT Pros	Jacksonville	April 24 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543095&Culture=en-US&community=0
Azure Roadshow for IT Pros	Tampa	April 25 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543096&Culture=en-US&community=0
Azure Florida Association – Global Azure Bootcamp	Boca Raton	April 27 2013	http://www.eventbrite.com/event/5885964077#
Azure Roadshow for IT Pros	Pensacola	May 1 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543175&Culture=en-US&community=0
Make it Happen in the Cloud	Miami	May 8 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032547358&culture
Azure Roadshow for IT Pros	Orlando	May 8 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543176&Culture=en-US&community=0
Make it Happen in the Cloud	Tampa	May 22 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032547365&culture=en-us
Azure Roadshow for IT Pros	Fort Lauderdale	May 22 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID
Azure Florida Association	Online	May 29 2013	TO BE ANNOUNCED
Azure Roadshow for IT Pros	West Palm Beach	June 5 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543178&Culture=en-US&community=0
Azure Roadshow for IT Pros	Gainesville	June 12 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543179&Culture=en-US&community=0
Azure Roadshow for IT Pros	Jacksonville	June 17 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543180&Culture=en-US&community=0
Azure Roadshow for IT Pros	Tampa	June 18 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543181&Culture=en-US&community=0
Azure Roadshow for IT Pros	Miami	June 20 2013	https://msevents.microsoft.com/CUI/EventDetail.aspx?EventID=1032543182&Culture=en-US&community=0

About Herve Roggero

Herve Roggero, Windows Azure MVP in South Florida, works for AAJ Technologies (http://www.aajtech.com) and is the founder of Blue Syntax Consulting (http://www.bluesyntax.net). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626).

Windows Azure IT Roadshow Lab I

2013-03-20T06:27:58-12:00:00

This lab was created for the Windows Azure IT Roadshow in Florida (first session). It covers the basics of creating a Windows Server 2012 virtual machine, a SQL Server 2012 virtual machine and a SQL Database. Some of the steps will help you configure the firewalls properly and show you how to stripe two blobs for increased performance.

Sign-up for 90-day trial

If you haven’t done so, open a 90-trial account now. The rest of this lab requires you to have an Azure subscription.

Note: You will need a Live ID to sign-up for the 90-day trial and a credit card. The credit card will not be charged until you upgrade your trial to an ongoing Azure plan.

To sign up for the 90-trial, visit http://aka.ms/try-azure

Create Windows Server 2012 Virtual Machine

Create a new Network

- Go to https://manage.windowsazure.com/

- Login using your Live ID

- You will see a screen similar to this

- Click on Networks

- Click on NEW (at the bottom) and select Custom Create

- Enter the details of this new Network service and specify an affinity group, then click the right arrow (>) to go to the next step
Name: EastUSNet
Affinity Group: <create new affinity group>
Region: East US
Affinity Group Name: EastUSAffinity

- In the Address Space and Subnets screen, leave the default values to setup a 10. network address space and a subnet. Click the right arrow (>).

- In the DNS Server and Local Networks, leave the settings empty to use the Microsoft internal DNS servers. Click on the completion button at the bottom to create the network.

- You now have a network created under your subscription.

Create a Windows Server 2012 Virtual Machine

- Click on Virtual Machines on the left bar

- Click on Create a Virtual Machine

- Select From Gallery

- Select Windows Server 2012 Datacenter

- Click Next ->

- Enter a name for the machine (mywindows2012vm), a password for the Administrator account and click next
MAKE SURE THE PASSWORD IS STRONG ENOUGH.

- Click Next ->

- Enter a DNS Name (this name must be unique). Make sure the EastUSNet network is selected. Leave the default for the storage account (create automatically). Leave the standalone VM setting default.

- Click Next ->

- Create an Availability Set (call it PROD-AVAIL)

- Click OK

At this time you are creating a new virtual machine. You will see its status as “Provisioning…”. This may take a few minutes. So far, you have created the following:

· A VM running Windows Server 2012 called mywindows2012vm

· A network called EastUSNet

· An Availability Set called PROD-AVAIL

· A storage account for your VM (name starts with portalvhd…)

· An Affinity Group called EastUSAffinity (part of the network definition)

Reminder:

· The availability set allows Azure to spread servers across fault and upgrade domains

· An affinity group helps Azure keep services running nearby for improved performance

Reminder:

· A storage account has specific performance targets

· A VM’s C:\ contains the binaries, and are stored in a blob, within a blob container, within a storage account

· Other blobs will be created as attached drives to the VMs and stored in this storage account

Additional Tasks:

- Explore the Windows Azure portal and look at your storage account; click on the storage account and click on Containers

- Explore the content of the vhds container. You will see a 30GB blob (note the name of the blob)

- Explore the Virtual Machine; click on Disks and see the name of the disk; it is the name of the blob

Create SQL Server 2012 IaaS

- Click on Virtual Machines on the left bar

- Click on NEW (bottom left)

- Select From Gallery

- Select Windows SQL Server 2012 SP1 Evaluation

- Enter a name for the machine (mysql2012vm), a password for the Administrator account and click next
MAKE SURE THE PASSWORD IS STRONG.

- Click Next ->

- Enter a unique DNS name, select the previously created storage account, and select the EastUSNet network

- Click Next ->

- Click OK (do not create an availability set)

Notes

At this point a second machine has been created, running SQL Server. The two servers are linked through the same network, and thus they both share the same affinity group.

Add 2 Drives to SQL Server VM

- Select the SQL Server VM from the list and select Attach Empty Disk from the menu (bottom)

- Enter a 1 for the size of the disk (this operation will add a 1GB disk to the VM for our demo)
MAKE SURE TO LEAVE THE CACHING SETTING TO NONE. THIS IS THE PREFERRED OPTION FOR DATA DISKS.

- Click OK

- Wait for the operation to complete

- Repeat the operation to add another 1GB drive

We now have added two disks to our SQL Server virtual machine. Let’s connect to the machine and finish the configuration.

Create Stripe in O/S

In this section we will create the stripes in the O/S directly. Striping the disks is not required to make SQL Server work in IaaS; however it is a way to improve I/O performance for your database when using RAID 1 (mirror). Because mirroring requires two or more disks, you must use a Small VM instance at a minimum to stripe your disks.

- Remote Desktop into your Virtual Machine (click on from the portal). Ignore the security warnings.

- Log in to your machine

- Start disk manager

- Right-click on one of the disks and choose New Striped Volume

- Create a Log and a Data directory on the newly created F drive

Configure O/S Firewall (TCP:1433)

The database virtual machine is almost ready; you will need to configure TCP 1433 in order to allow other virtual machines to connect to this database.

- Open the Windows Firewall (advanced settings)

- Add a rule allowing TCP, port 1433 to connect

o Right-click on Inbound Rules and select New Rule

o Select Port then click Next

o Select TCP and enter 1433 in the Port and click Next.

o Select Allow the Connection. Click Next. Click Next again.

o Enter a name for the rule, like “SQL Server Connection” and click Finish

Enable ICMP (firewall) for PING

Last but not least, allow the ICMP protocol so that you can PING this server.

- Inside the Windows Firewall (advanced settings) add a new rule

- Select Custom to create a custom rule

- Select All Programs and click Next

- Under Protocols and Ports select ICMP v4

- Select Customize, choose “specific ICMP Types” and check echo request

- Click OK and Next

- Leave the defaults and click Next, Next, Next

- Enter a name for the rule, like PING Connection and click Finish

Create SQL Database Instance

In this Lab you will create a SQL Database instance and connect to it from the SQL Server VM. This will allow you to manage your SQL Database instance from SQL Server.

- Go to the Windows Azure Management Portal

- Select SQL Database on the left

- Click on NEW and choose Custom
Make sure you select “New SQL Database server”. Enter a name for your database.
Select a 1GB database. Click Next ->.

- Enter the server level admin user name and password. Make sure to select East US for this example. Leave the “Allow Windows Azure Services To Access The Server” checked.
Click OK.

- Click on your newly created database and explore the dashboard

Create a local database

In this step we will create a database on the stripped drive.

- Login to your SQL Server VM

- Open SQL Server Management Studio

- Open a Query Window (master will be selected by default)

- Right-click on the database server on the left and create a new database

o Make sure to specify the F:\ drive as the storage drive for your database files (in the Log and Data directories you created earlier)

Connect to SQL Database from SQL Server

In this step we will login to the SQL Server VM and create a SQL Database instance.

- Login to your SQL Server VM

- Open SQL Server Management Studio

- Open a Query Window

- Change the connection to the SQL Database server on the master database

o Right-click in the command window and select Change Connection…

o Enter the full server name of SQL Database and the uid/pwd you created earlier

- Switch context to the database created previously (sqldb001)

o You cannot run the USE command; use the dropdown containing the list of databases instead

- Run a command to verify your connection to the cloud database

SELECT GETDATE()

Sample Pricing Comparison: On-Premise vs. Private Hosting vs. Cloud Computing

2013-02-25T07:18:56-12:00:00

Last week I blogged about some of the basic pricing differences between Amazon AWS and Microsoft’s Windows Azure (http://wblo.gs/da7) and concluded that while there was a price difference, it was important to review the benefits and gaps of each platform to make the right decision. In that comparison, using the assumptions documented on the blog post, Windows Azure was a more affordable option thanks to its Windows Azure SQL Database service offering.

In this blog post, I will compare what it would take to perform the same project on a virtualized environment (on your own hardware) and hosted in a collocation environment (private hosting). As with the previous cost comparison blog, a few assumptions are necessary to fix certain parameters that could otherwise significantly impact the results.

General Assumptions:

The project requires new licenses
The project needs to achieve high availability up to 99.95%
The project does not need disaster recovery
Data center quality hardware is used for this project using similar specifications than those used in the cloud estimates

On-Premise Assumptions:

The project requires new hardware (servers) but will run on an existing infrastructure (routers, firewalls…)
The project will use an existing network storage (SAN) for the database (for database clustering)
The same physical machine will host both the database server and the IIS virtual machines
Use virtualization with Windows Server 2012
Maintenance cost is about 20% per year on top of hardware and software costs

Private Hosting Assumptions:

Use simpler VMs for web hosting and dedicated servers for the database
Assume clustering VMs is possible for SQL Server

The project specifications, as provided in the previous blog post, are as follows:

SQL Server database, Enterprise Edition, 10GB of storage, 1CPU, 1 million requests, 10GB per month of data tx
10 Websites running ASP.NET, 1CPU, 1TB of data tx out to Internet per month
2 Middle-tier Servers running .NET, 2CPUs
Reporting Services - 10 reports run daily, 1GB of data out to Internet per month

On-Premise Self Hosted Costs

Let’s go shopping! First we need to find the hardware that makes sense for this project. Because I want to virtualize my solution I will purchase a server with two 8-cores (the project requires about 16 cores). I selected two HP ProLiant DL360p Gen8, rack mountable 1U 1-way and 2 8-core CPUs (Xeon E5-2650), 32GB RAM and a gigabit LAN. On HP.com this would amount to about $8,500 in total server cost including attached disk storage sufficient for our needs (500GB) although the database will be stored on an existing network storage (SAN).

If you had everything else in our infrastructure, this would be it. No need for additional hardware or software. However, if you were building this environment from scratch you may need to purchase additional items, such as a rack, a firewall, a load balancer, power, cooling and so forth. To simply the cost estimates we are assuming that we won’t need this hardware because we already have it.

From a licensing standpoint we need to purchase SQL Server Enterprise Edition and Windows Server 2012 Standard Edition in order to virtualize. SQL Server Enterprise Edition costs about $6,500 per core (with a purchase requirement minimum of 4 cores per processor), so $26,000 in all. Note however that we need to purchase a second license in order to obtain a clustered environment for Active Always-On high availability since this is a requirement; so the SQL Server licenses come up to $52,000. Pricing information for SQL Server can be found here: http://www.microsoft.com/en-us/sqlserver/editions/2012-editions/enterprise.aspx. Windows Server 2012 Standard edition costs about $880 for every 2 virtual machine, so we would need to purchase a total of 8 standard edition licenses for our 16 virtual machines, or about $7,000. Information about Windows Server 2012 pricing can be found here: http://www.microsoft.com/en-us/server-cloud/windows-server/buy.aspx.

So far, we will need a budget of about $67,500 including hardware, software licenses and storage. In addition to this cost, we are adding about 20% of support costs yearly to account for general maintenance; this accounts for hardware support and monitoring. You will see this cost reflected in the pricing summary.

Note however that our configuration, although pretty robust, does not have a lot of redundancy, outside of the database server itself. Because all our virtual machines live on the same underlying host (the hardware itself) a physical failure would affect our system as a whole. For high availability, we need to include a second server, load balanced with the first one, and distribute the virtual machines equally across the two servers. In this scenario, if one of the servers failed completely, all the virtual machines on that server would fail over to the other one, and the system would continue to function. To simplify the cost structure we will assume that adding this level of redundancy, that will get us closer to the type of availability available with cloud providers, we will need to add one more server and double our Windows licenses (we keeping the same SQL Server licenses because we are placing the active mirror on the second server).

The summary of the estimated costs involved in running an on-premise highly available solution for our project will be a little less than $100,000 as shown below. Note that these are estimated costs and assume that you already have an infrastructure in place with enough capacity to accommodate the new hardware. I am also assuming that you have a storage unit available (a Storage Attached Network or SAN) on which the database can be stored so that you can run a cluster. If these assumptions do not hold true, you can expect significantly higher costs.

Private Hosting Costs

If instead you were to use a hosting provider instead of purchasing the hardware yourself, what would be different?

The first difference is that you would incur a monthly cost; so you would not need to purchase the hardware itself nor would you need to purchase the Windows Server operating system. You would however still need to purchase SQL Server Enterprise Edition. You would also probably avoid the extra 20% maintenance discussed previously because all the activities related to this item are performed by the hosting provider.

With the private hosting option it is more difficult to estimate the costs of high availability. This estimate does not include greater availability than what is offered by the vendor for IIS servers. Generally speaking it may be easier to achieve high availability with a hosting provider than on-premise, but this depends partially on the maturity of the hosting provider and the service options available. So keep in mind that to achiever a similar availability than with the other options additional costs may apply.

A quick estimate on 1and1.com (http://1and1.com) with 16 CPUs (14 Virtual Server XL and 2 Dedicated Standard Applications L) runs at about $10,000 per year. It is difficult to obtain a quote online that matches closely the hardware specifications set earlier; it may be possible to reduce the hosting costs for the web servers, but the SQL Server configuration may be more costly because we need a shared storage option to run the cluster. So consider this monthly cost as a rough estimate to give you a sense of the costs involved when renting servers with a private hosting provider.

Other considerations

The first thing that may not be obvious is that purchasing hardware and software takes time, effort and requires expert resources to configure. So once we have the hardware, we still need to hire consultants (or involve the engineering team) to configure the servers and install the software, which could cost a few thousands of dollars extra. From a go to market standpoint, it could take weeks before the infrastructure is ready to be used on the actual project (with on-premise virtualization), possibly delaying important initiatives. And from a capital standpoint, it would take close to $99,000 to just get started with on-premise virtualization and about $50,000 with dedicated hosting, which ties up capital that could otherwise be used on other initiatives.

You may also need to build a development environment, a test environment and a production environment, which could further increase the total project cost. Although you wouldn’t need all the licenses and the hardware that is needed for production, building a development environment and a test environment can cost thousands of dollars.

Although it is important to compare costs, you also need to incorporate certain decisions that could discard public cloud adoption, such as the sensitivity of the data itself, compliance issues and many other aspects that would prevent a move to the cloud.

3-Year Cost Comparison

So let’s put all these numbers side by side with the cloud costs previously calculated for the same project, over a three-year period, assuming no hardware or software upgrade needs to take place. In the previous blog, we identified that the total monthly cost of running the environment was $1,491 per month for Windows Azure and $1,832 per month for Amazon.

In summary a three-year cost analysis for this environment would look like the following:

Keep in mind that these costs can vary significantly based on your currently licensing agreements and available hardware. If you currently have an enterprise license agreement for example, the cost of your virtualized environment would probably be significantly lower. Here are the major assumptions we added related to our on-premise installation:

No additional upgrades are needed within the 3-year window
An existing network storage exists for the database or is provided by the hosting provider
Your data center or hosting provider has enough space to accommodate the new hardware
The necessary network components are already in place (firewalls, load-balancers, rack, routers…)

Conclusion

As shown in this fictitious project, running a highly available solution on an internal datacenter, or hosted in at a private hosting provider, requires a higher financial commitment, both at the initiation of the project and over a three-year window. With Amazon and Windows Azure this project would cost about half of the total cost compared to a self-hosted virtualized environment and would provide a significantly higher degree of freedom to upgrade, enhance or even terminate the project on demand. These benefits are not obvious when looking at costs alone only but are important enough to mention.

The table below attempts to summarize some of the most important factors that came into focus for this project. Note that depending on the project requirements the table below would display different results, so this is not an attempt to generalize a complex decision making process, but rather to give you a summary of what was discussed so far. In particular, the 99.95% availability is easiest to achieve with cloud providers because it is “out of the box”. However if you needed to achieve 99.99% or higher, it may be significantly more difficult to achieve with cloud providers than with the other options.

As discussed previously, although hosting your own virtualized environment (on-premise or with a private hosting provider) is more expensive it can provide other benefits that are not easy to achieve with cloud providers, such as systems that have compliance requirements (PCI, HIPAA) and systems with high throughput requirements. Nevertheless cloud providers are improving on both fronts progressively. Some cloud providers have provisions for compliance initiatives and high throughput needs can sometimes be mitigated with higher scalability provisions in the cloud.

About Herve Roggero

Azure Roadshow for IT Pros in Florida

2013-02-17T15:20:28-12:00:00

Hi – this is HUGE!!! Microsoft (Blain Barton) is planning a roadshow in Florida starting in March 2013 until June 2013. We will visit many cities (twice), so if you haven’t signed up yet, now is the time! Space is running short already! I will be one of the presenters at most of the locations, along with Adnan Cartwright, MVP, (http://www.fisg.us/). We will talk IaaS (Infrastructure as a Service) in Microsoft Azure, so don’t miss out!

Here are some key links for you:

http://aka.ms/blainbarton
http://aka.ms/msproducts
http://aka.ms/try-azure

The first topics will include Windows Server 2012 Virtual Machines, Early Experts Workshop and Microsoft Virtual Academy; the second visit will include System Center SP1 with Windows Server 2012.

And here is the schedule along with registration links.

http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073135	Orlando FL	3/6/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073136	Fort Lauderdale FL	3/7/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073137	West Palm Beach FL	3/21/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073138	Gainesville FL	4/3/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073139	Miami FL	4/4/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073142	Pensacola FL	4/16/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073140	Jacksonville FL	4/24/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073141	Tampa FL	4/25/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073165	Pensacola FL	5/1/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073158	Orlando FL	5/8/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073159	Fort Lauderdale FL	5/22/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073160	West Palm Beach FL	6/5/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073161	Gainesville FL	6/12/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073163	Jacksonville FL	6/17/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073164	Tampa FL	6/18/2013
http://www.microsoft.com/click/services/Redirect2.ashx?CR_CC=200177638&CR_EAC=300073162	Miami FL	6/20/2013

About Herve Roggero

Ready for .NET Code Camp in South Florida?

2013-02-08T02:58:45-12:00:00

Here we go again! South Florida Code Camp is about to start tomorrow, Saturday Feb 9 2013, at NOVA University in Fort Lauderdale. I have seen this event grow every year; over 900 people were registered last year. It’s a HUGE event for the Microsoft developer community in South Florida. You can still register here: http://www.fladotnet.com/codecamp/

If you look at the Agenda you will see some really interesting speakers and topics. I would like to specially give a shout-out at some of the new speakers this year and a few other speakers/topics you might want to attend. First, the new speakers:

Mir Majeed – Mir works at AAJ Technologies and will speak about Windows Phone 8 Development at 4PM. Outstanding topic!!!
Syed Ali – Syed works at AAJ Technologies as well and will talk about TFS and Scrum methodology at 4PM; if you work in Scrum projects with TFS, or are considering to do so, listen in!
Qasim Mehmood – Also from AAJ Qasim will speak about SharePoint 2013 features at 2:40PM; don’t miss this one if you are working on SharePoint.

You also have some really cool topics/speakers, including:

Jeff Barnes – Working for Microsoft, Jeff will speak about Mobile application development at 2:40PM. Cool presenter; cool topic.
Jason Milgram (MVP) – Jason, from Linxter, will speak about Windows Azure media services at 1:20PM. Interesting topic for sure…
Herve Roggero (MVP) – Yes… it’s me! I now work for AAJ Technologies. Come join me at 11:10am to discuss Windows Azure scalability in detail.
Jonas Stawski (MVP) – At 9:50, an interesting talk about Jonas on Building cross-platform mobile applications.

Here we go!!! Here are some of my recommendations. You also have some notable speakers including John Papa, Scott Klein and many others.

About Herve Roggero

Chapter Review: The Utility Database (by Chris Shaw) and how it applies in Cloud Computing

2013-01-03T15:50:51-12:00:00

Sometimes you end up reading a great chapter and feel like writing a review about it… well here it is! Chris Shaw (@SQLShaw, http://chrisshaw.wordpress.com ) wrote a chapter in the PRO SQL Server 2012 Practices book published by Apress (http://www.apress.com/databases/sql-server/9781430247708). Chris wrote about one of his habits as a SQL Server consultant: the Utility Database. At first I wasn’t sure what this was going to be about. But after reading the first few lines I could immediately relate to my past experience as a DBA. In a nutshell, DBAs constantly face the same dreaded question: “what just happened?”. It could be failed backup operations, sudden poor performance, jobs not running… and so forth.

Chapter Overview

The chapter about the Utility Database helps you understand how to properly build a monitoring mechanism that watches your database(s) and helps you identify problems before they occur. The premise of Chris’ chapter is that you are better off being proactive than reactive when database issues occur. Because this is a book on SQL Server Chris walks you through many of the concerns that apply on that platform specifically, including AlwaysOn, Clustering, Jobs and so forth. He shows you how to structure your tables depending on the type of data you are collecting and how to configure your system to start data collection.

Generally speaking Chris recommends that you evaluate and monitor important areas such as (partial list):

Data Growth
Security
Error Logs
Indexing
Fail Overs
Disk Space

Chris also discusses the importance of KPI (Key Performance Indicators). KPIs are not systems metrics; they are considered business metrics. However identifying specific business metrics and keeping track of them can be just as important as environment metrics. Last but not least Chris tells us that every database system implementation is different, so the recommendations in the book should be used a guidelines and customized to you own needs.

Utility Database for Windows Azure SQL Databases

Equipped with Chris’ recommendations we can discuss how they apply to cloud computing, and more specifically to SQL Database. With SQL Database, many of the concerns that make sense for SQL Server do not apply. For example you can’t monitor the disk space or the CPU on SQL Database. Although certain concepts do not apply, others only apply to SQL Database and not to SQL Server, such as Federations, Error Logs (different kinds), data consumption (used for billing) and firewall settings to name a few. And because performance is just as critical in the cloud due to the throttling behavior of SQL Database, indexing and data growth as extremely important for monitor. So even if Chris’ chapter is strictly covering SQL Server, many of the concepts and recommendations apply with SQL Database.

In addition to indexing and long running stored procedures and T-SQL statements, some of the information you may want to track in SQL Database specifically include:

Database Size
Event Table (in master: sys.database_connection_stats and sys.event_log)
Federations (history data)
Version Engine
Firewall settings

There is one minor detail to discuss however: how to collect the information. The chapter discusses how the Utility database is deployed and how to use Scheduled Jobs to run scripts automatically. In essence, the Utility database is deployed on every server in your farm and a job runs stored procedures on every database in SQL Server.

The first issue is that there is no such thing as SQL Agent in the cloud, so you can’t run scripts natively in the cloud. But don’t despair. Microsoft recently announced support for Linked Servers from an on-premise SQL Server database to one or more SQL Database instances. So now you can create jobs locally in SQL Server and execute stored procedures located in SQL Database instances.

The second issue is that there isn’t a concept of a “database server” in SQL Database; a SQL Database server in a logical grouping of database, each of which can be found on different servers. But that’s not really an issue by itself; you can simply create the Utility Database within each database by creating its objects in a separate schema container (let’s call it the Utility schema). This way the Utility Database is stored within each database; the scheduled jobs will need to programmatically enumerate through each database in the cloud (which is possible by querying the sys.databases table in master) and run the stored procedures inside the Utility schema.

If you are using Federations the concept is the same; simply deploy the Utility schema to every federation member, et Voila! Your job would also need to detect all the federation members programmatically, but that not a problem because you can access this information by querying sys.federations.

In summary, the Utility Database presented by Chris Shaw has many benefits for both on-premise SQL Server and cloud SQL Database environments. While some of the concerns are the same, you may need to customize the Utility Database accordingly to adapt Chris’ work to SQL Database instances.

About Herve Roggero

Herve Roggero, Windows Azure MVP in South Florida, is the founder of Blue Syntax Consulting, a company specialized in cloud computing products and services. Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626). For more information on Blue Syntax Consulting, visit http://www.bluesyntax.net/.

PRO SQL Database for Windows Azure

2012-12-28T08:13:09-12:00:00

After months of work, rework, revisions and reviews, the second edition of the APress book PRO SQL Database for Windows Azure is out! You can get it on Amazon (Paperback or Kindle) or through Barnes & Nobles (NOOK version).

All the screenshots and instructions were changed from the first edition (PRO SQL Azure) to demonstrate the use of SQL Database using the new portal. However, above and beyond the graphics, the new edition covers the new SQL Database management portal, Federations, a complete rewrite of the Azure Reporting Services, SQL Data Sync and Windows Azure Mobile Services.

Although this book isn’t heavy, it is packed with valuable information for DBAs and .NET developers too. You will find a lot of tips and useful explanations that are based on actual hands-on experience with this technology.

I would also like to thank Thomas Larock (@SQLRockstar) for his professional review on the book itself, and his generous blog review about the release of the book.

Scalable Distributed Workload with Azure Queues

2012-12-27T08:57:23-12:00:00

If you develop an application for the cloud, such as Microsoft Azure, you may need to create a distributed workload. Distributing workload in the cloud is one of the fundamental aspects of scalability, because you can breakdown the work into smaller pieces and use multiple machines to process the workload. A good workload distribution technique exposes the following important characteristics:

- The more machines you use, the faster the overall workload will be processed
- An increase of machines to process the workload scales in a linear fashion
- When a machine crashes another one can pick the load from where the other one left off

Architecture Overview

Let’s first review the overall architecture of a workload distribution topology. Let’s assume we have a Distribution Service (DS), which is responsible for preparing the overall workload (for example, we need to backup 100 databases). The workload is broken down into 100 individual requests; each request is then saved into an Azure Queue (possibly as an XML). The Azure Queue is the favorite tool for workload distribution because it guarantees that only 1 service can retrieve a message at a time, and there are built-in recovery mechanisms in case of failure of the processing service (PS).

The Processing Service (PS) is a Worker Role responsible for reading items from an Azure Queue and perform the operation requested. Depending on the amount of work involved, you could code your PS to process a single request at a time, or process multiple messages at the same time. Each PS could have more than one processing thread, if needed. Finally, you could deploy multiple PSs to further increase the throughput of the workload.

The following diagram shows a simple logical deployment model that implements a single DS and multiple PSs to process the workload. From a physical deployment standpoint, the DS could live inside the same worker role as the PS, on a different thread. This works well if the DS and the PS do not compete too much for the same resources. If the DS has significant pre-processing needs, the DS should reside on its own worker role. In the following sections we will improve upon this architecture to handle DS high availability and Azure scalability targets.

Throughput

To calculate the maximum throughput of your distributed workload using the architecture above, let’s assume the DS does not have any bottlenecks and does not need to scale. Let’s further assume that you can processing 10 (T) parallel threads in each PS (each thread consuming 1 message at a time), using 1 PS instance (X) and it takes 5 seconds (S) to process each message. This gives you T * X / S messages per second maximum throughput, or up to 2 messages per second. If you deploy your PS worker role a second time, you now can perform 4 messages per second. Generally speaking, this model is linear in nature as long as you don’t share any resource. However, as you will see further below, you are sharing the same Azure Storage Account and Azure Queue, so after a certain point, you reach a throughput plateau unless you improve the design.

Also note that smaller processing times makes a more scalable system. It is usually better to have many small requests than a few large ones; it is usually easier to handle failure/retry for smaller units of work, and the system is overall more responsive when it has to deal with smaller retries than large ones. So choosing what your unit of work should be is fundamental. As a general rule, I try to select workloads that are a few seconds in duration and do not depend on the outcome of other workloads, or the sequencing of work units.

Azure Scalability Target

As discussed above, the simple workload distribution model scales well up to a point; specifically, Windows Azure introduces performance limitations on various services, and scalability targets on its storage account. As a result, the scalability of the architecture discussed so far is bound by Azure performance boundaries.There are ways to design around those limitations however. Let’s take a look.

The new scalability targets for an Azure Queue is 2,000 messages per second. Note that this is not a guarantee; it is a performance target. You also have other limitations worth noting: 60MB/Sec per partition (in our case that would be the Azure Queue) and 20,000 transactions per Storage Account (and up to 15GB/sec per storage account). See this link for more information: http://blogs.msdn.com/b/windowsazure/archive/2012/11/02/windows-azure-s-flat-network-storage-and-2012-scalability-targets.aspx.

So in order to go beyond these scalability targets we need to adjust our design and allow our DS and PS to communicate through more than one storage account and, as a result, more than one Azure Queue. It may be possible to use more than one Azure Queue in each storage account, however for simplicity we are creating one queue per storage account in this design. This design implies that the DS creates messages across multiple storage accounts/queues, and that each PS reads from each available queue too, in a round robin mechanism. Over time, the system is able to write and read from multiple queues progressively and can process an even much larger number of items. This technique is called Storage Account Sharding and allows distributed systems to scale significantly.

By default, each Windows Azure account (Live ID) can have up to 20 storage accounts (you may need to call Microsoft Support to obtain 20 storage accounts). So technically, this approach still has a scalability limit. Nevertheless, it is easy to open additional Microsoft Azure Accounts and continue to create additional storage accounts.

DS Redundancy / Locking

There is one more item to consider: how to make the DS highly available. In theory, all you need to do is to deploy the DS to multiple worker roles, and you naturally achieve redundancy. In our design however, this could create a problem because each DS instance is unaware of the other; so this could lead to duplicate messages in the Azure Queues. To go around this issue, you simply need to implement a shared locking mechanism that allows each DS to determine if it needs to run the distribution, of if it should sleep and try again later. If one DS goes down, any other DS will be able to pick up the work going forward.

The easiest way to implement a shared locking mechanism that relies on a wake up call (let’s say, run every 60 seconds), is to use an Azure Table. The Azure Table is shared by each DS, and a single entity representing the workload is created with a NextTimeToRun datetime property. Each DS then attempts to read then update the entity; by default, the update will fail if any of the properties has changed since it was read. So only 1 DS will successfully update the Azure Table at a time, and thus be responsible for running the next distribution.

Ultimately, the proposed distributed workload architecture would look like this. Note that the Azure Table will not be used extensively in this architecture,

Note that this architecture can be further improved. For example this approach doesn’t prescribe the handling of poison messages, nor does it handle how to efficiently store data in SQL Database when dealing with large amounts of data. However, this should provide a good start for a distributed architecture in the cloud.

Other Use Cases

We talked about the DS being a worker role (or multiple worker roles) running on a schedule. However the DS could be a client facing application for example, not running on any specific schedule. If you deploy an application to thousands of workstations, each workstation could act as a DS and could publish data in the Azure Queues directly. This represents a specific use case of the scenario described previously. Here how this use case would look like:

About Herve Roggero

Herve Roggero, Windows Azure MVP in South Florida, is the founder of Blue Syntax Consulting, a company specialized in cloud computing products and services. Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" from Apress and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626). For more information on Blue Syntax Consulting, visit www.bluesyntax.net.

Backup SQL Database Federation

2012-11-08T08:12:41-12:00:00

One of the amazing features of Windows Azure SQL Database is the ability to create federations in order to scale your cloud databases. However until now, there were very few options available to backup federated databases. In this post I will show you how Enzo Cloud Backup can help you backup, and restore your federated database easily. You can restore federated databases in SQL Database, or even on SQL Server (as regular databases).

Generally speaking, you will need to perform the following steps to backup and restore the federations of a SQL Database:

Backup the federation root
Backup the federation members
Restore the federation root
Restore the federation members

These actions can be automated using: the built-in scheduler of Enzo Cloud Backup, the command-line utilities, or the .NET Cloud Backup API provided, giving you complete control on how you want to perform your backup and restore operations.

Backing up federations

Let’s look at the tool to backup federations. You can explore your existing federations by using the Enzo Cloud Backup application as shown below. As you can see, the federation root and the various federations available are shown in separate tabs for convenience. You would first need to backup the federation root (unless you intend to restore the federation member on a local SQL Server database and you don’t need what’s in the federation root). The steps are similar than those to backup a federation member, so let’s proceed to backing up a federation member.

You can click on a specific federation member to view the database details by clicking at the tab that contains your federation member. You can see the size currently consumed and a summary of its content at the bottom of the screen.

If you right-click on a specific range, you can choose to backup the federation member. This brings up a window with the details of the federation member already filled out for you, including the value of the member that is used to select the federation member. Notice that the list of Federations includes “Federation Root”, which is what you need to select to backup the federation root (you can also do that directly from the root database tab). Once you provide at least one backup destination, you can begin the backup operation. From this window, you can also schedule this operation as a job and perform this operation entirely in the cloud. You can also “filter” the connection, so that only the specific member value is backed up (this will backup all the global tables, and only the records for which the distribution value is the one specified). You can repeat this operation for every federation member in your federation.

Restoring Federations

Once backed up, you can restore your federations easily. Select the backup device using the tool, then select Restore. The following window will appear. From here you can create a new root database. You can also view the backup properties, showing you exactly which federations will be created.

Under the Federations tab, you can select how the federations will be created. I chose to recreate the federations and let the tool perform all the SPLIT operations necessary to recreate the same number of federation members. Other options include to create the first federation member only, or not to create the federation members at all.

Once the root database has been restored and the federation members have been created, you can restore the federation members you previously backed up. The screen below shows you how to restore a backup of a federation member into a specific federation member (the details of the federation member are provided to make it easier to identify).

Conclusion

This post gave you an overview on how to backup and restore federation roots and federation members. The backup operations can be setup once, then scheduled daily.

Faster, Simpler access to Azure Tables with Enzo Azure API

2012-10-29T03:43:49-12:00:00

After developing the latest version of Enzo Cloud Backup I took the time to create an API that would simplify access to Azure Tables (the Enzo Azure API). At first, my goal was to make the code simpler compared to the Microsoft Azure SDK. But as it turns out it is also a little faster; and when using the specialized methods (the fetch strategies) it is much faster out of the box than the Microsoft SDK, unless you start creating complex parallel and resilient routines yourself. Last but not least, I decided to add a few extension methods that I think you will find attractive, such as the ability to transform a list of entities into a DataTable. So let’s review each area in more details.

Simpler Code

My first objective was to make the API much easier to use than the Azure SDK. I wanted to reduce the amount of code necessary to fetch entities, remove the code needed to add automatic retries and handle transient conditions, and give additional control, such as a way to cancel operations, obtain basic statistics on the calls, and control the maximum number of REST calls the API generates in an attempt to avoid throttling conditions in the first place (something you cannot do with the Azure SDK at this time).

Strongly Typed

Before diving into the code, the following examples rely on a strongly typed class called MyData. The way MyData is defined for the Azure SDK is similar to the Enzo Azure API, with the exception that they inherit from different classes. With the Azure SDK, classes that represent entities must inherit from TableServiceEntity, while classes with the Enzo Azure API must inherit from BaseAzureTable or implement a specific interface.

// With the SDK
public class MyData1 : TableServiceEntity
{
    public string Message { get; set; }
    public string Level { get; set; }
    public string Severity { get; set; }
}

// With the Enzo Azure API
public class MyData2 : BaseAzureTable
{
    public string Message { get; set; }
    public string Level { get; set; }
    public string Severity { get; set; }
}

Simpler Code

Now that the classes representing an Azure Table entity are defined, let’s review the methods that the Azure SDK would look like when fetching all the entities from an Azure Table (note the use of a few variables: the _tableName variable stores the name of the Azure Table, and the ConnectionString property returns the connection string for the Storage Account containing the table):

// With the Azure SDK
public List<MyData1> FetchAllEntities()
{
     CloudStorageAccount storageAccount = CloudStorageAccount.Parse(ConnectionString);
     CloudTableClient tableClient = storageAccount.CreateCloudTableClient();
     TableServiceContext serviceContext = tableClient.GetDataServiceContext();

     CloudTableQuery<MyData1> partitionQuery =
        (from e in serviceContext.CreateQuery<MyData1>(_tableName)
        select new MyData1()
        {
           PartitionKey = e.PartitionKey,
           RowKey = e.RowKey,
           Timestamp = e.Timestamp,
           Message = e.Message,
           Level = e.Level,
           Severity = e.Severity
           }).AsTableServiceQuery<MyData1>();

       return partitionQuery.ToList();
}

This code gives you automatic retries because the AsTableServiceQuery does that for you. Also, note that this method is strongly-typed because it is using LINQ. Although this doesn’t look like too much code at first glance, you are actually mapping the strongly-typed object manually. So for larger entities, with dozens of properties, your code will grow. And from a maintenance standpoint, when a new property is added, you may need to change the mapping code. You will also note that the mapping being performed is optional; it is desired when you want to retrieve specific properties of the entities (not all) to reduce the network traffic. If you do not specify the properties you want, all the properties will be returned; in this example we are returning the Message, Level and Severity properties (in addition to the required PartitionKey, RowKey and Timestamp).

The Enzo Azure API does the mapping automatically and also handles automatic reties when fetching entities. The equivalent code to fetch all the entities (with the same three properties) from the same Azure Table looks like this:

// With the Enzo Azure API
public List<MyData2> FetchAllEntities()
{
       AzureTable at = new AzureTable(_accountName, _accountKey, _ssl, _tableName);
       List<MyData2> res = at.Fetch<MyData2>("", "Message,Level,Severity");
       return res;
}

As you can see, the Enzo Azure API returns the entities already strongly typed, so there is no need to map the output. Also, the Enzo Azure API makes it easy to specify the list of properties to return, and to specify a filter as well (no filter was provided in this example; the filter is passed as the first parameter).

Fetch Strategies

Both approaches discussed above fetch the data sequentially. In addition to the linear/sequential fetch methods, the Enzo Azure API provides specific fetch strategies. Fetch strategies are designed to prepare a set of REST calls, executed in parallel, in a way that performs faster that if you were to fetch the data sequentially. For example, if the PartitionKey is a GUID string, you could prepare multiple calls, providing appropriate filters ([‘a’, ‘b’[, [‘b’, ‘c’[, [‘c’, ‘d[, …), and send those calls in parallel. As you can imagine, the code necessary to create these requests would be fairly large. With the Enzo Azure API, two strategies are provided out of the box: the GUID and List strategies. If you are interested in how these strategies work, see the Enzo Azure API Online Help. Here is an example code that performs parallel requests using the GUID strategy (which executes more than 2 t o3 times faster than the sequential methods discussed previously):

public List<MyData2> FetchAllEntitiesGUID()
{
    AzureTable at = new AzureTable(_accountName, _accountKey, _ssl, _tableName);
    List<MyData2> res = at.FetchWithGuid<MyData2>("", "Message,Level,Severity");
    return res;
}

Faster Results

With Sequential Fetch Methods

Developing a faster API wasn’t a primary objective; but it appears that the performance tests performed with the Enzo Azure API deliver the data a little faster out of the box (5%-10% on average, and sometimes to up 50% faster) with the sequential fetch methods. Although the amount of data is the same regardless of the approach (and the REST calls are almost exactly identical), the object mapping approach is different. So it is likely that the slight performance increase is due to a lighter API. Using LINQ offers many advantages and tremendous flexibility; nevertheless when fetching data it seems that the Enzo Azure API delivers faster. For example, the same code previously discussed delivered the following results when fetching 3,000 entities (about 1KB each). The average elapsed time shows that the Azure SDK returned the 3000 entities in about 5.9 seconds on average, while the Enzo Azure API took 4.2 seconds on average (39% improvement).

With Fetch Strategies

When using the fetch strategies we are no longer comparing apples to apples; the Azure SDK is not designed to implement fetch strategies out of the box, so you would need to code the strategies yourself. Nevertheless I wanted to provide out of the box capabilities, and as a result you see a test that returned about 10,000 entities (1KB each entity), and an average execution time over 5 runs. The Azure SDK implemented a sequential fetch while the Enzo Azure API implemented the List fetch strategy. The fetch strategy was 2.3 times faster. Note that the following test hit a limit on my network bandwidth quickly (3.56Mbps), so the results of the fetch strategy is significantly below what it could be with a higher bandwidth.

Additional Methods

The API wouldn’t be complete without support for a few important methods other than the fetch methods discussed previously. The Enzo Azure API offers these additional capabilities:

- Support for batch updates, deletes and inserts
- Conversion of entities to DataRow, and List<> to a DataTable
- Extension methods for Delete, Merge, Update, Insert
- Support for asynchronous calls and cancellation
- Support for fetch statistics (total bytes, total REST calls, retries…)

For more information, visit http://www.bluesyntax.net or go directly to the Enzo Azure API page (http://www.bluesyntax.net/EnzoAzureAPI.aspx).

About Herve Roggero

Herve Roggero, Windows Azure MVP, is the founder of Blue Syntax Consulting, a company specialized in cloud computing products and services. Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" from Apress and runs the Azure Florida Association (on LinkedIn: http://www.linkedin.com/groups?gid=4177626). For more information on Blue Syntax Consulting, visit www.bluesyntax.net.