Today I went through the process of oData enabling one of my SQLAzure databases. One of the advantages of using SQLAzure is that it is easy (almost) to enable oData access. The following was the process I went through for your use, as I didn't see a similar tutorial.
First, I recommend starting at this page on the oData site (it is where I started):
From this page you can go to the oData Service Portal, which is at this URL: https://www.sqlazurelabs.com/ which looks like the following:
Click on the OData Service for SQL Azure tab. Upon your first time in, you'll see a registration screen as follows. Accept the terms and continue.
When you get in, you'll be presented with a form to enter your SQL Azure database credentials:
Press connect, and upon successful connection, you will be presented with a list of specific databases fromt that server for you to oData enable. Select your database, and the 'Enable OData' checkbox, and you will be presented with a request to specify access to the database. The default is no anonymous access:
You can select any user in your database if you want anonymous access. For now I'm using 'dbo':
At this point, you have actually completed oData enabling of your database. You can access the data at the URL provided in the portal on the screen. As an example, if I click the link above, I get the following result:
I only have one table in this database, named HistoricalErcotPrices. If I was to append /HistoricalErcotPrices to the URL, I would get the following:
Not incredibly useful, but if you view source, you can see the data:
Very nice indeed!
If you want an app to visualize your oData feed, there is the Silverlight OData Explorer, which looks like the following once you enter your feed url:
SQL Azure Database
- Highly scaled out relational database as a service
- Accessible via TDS
- SOAP/REST/ADO.NET/EDM/HTTP/HTTPs
- On premise access via SSRS
Customer value props:
- Self provisioning and capacity on demand
- Symmetry with on promesis database
- Automatic high availability and fault-tolerance
- Automated DB maintenance
Provisioning:
- Account as zero or more servers, billing instrument
- Each server contains one or more database
- Metadata
- Unit of security
- Unit of geo-location
- Logical grouping of DBs
- Each database has standard objects: unit of consistency, users, tables, views, indices, etc..
Free period ends 2/1/2010
Future:
- Improve tools
- Improve operational model
- Improve application programming model
Saas:
- Mention of provisioning API's outside of the portal
- Template databases
- Metadata tracking
- Additional billing scenarios
Single unit for deployment and upgrades
Data Synch
Upgrade and downgrade options between SKU's
Read only databases
Scale out support:
- Today
- workload partitioning
- but cluster managmeent is difficult
- Scale out addressed today:
- high availability
- Zero admin
- No downtime
- Elastic resources
- Pay as you grow
- No friction provisioning
- Never run out of hardware
- Future
- More scale out
- Dynamic database splits
- Ability to merge databases
- Improved schema management across groups of databases
- Additional database size options
- multiple database connection management
- Support for fan-out query
- Recent requests from customers
- Profillers, DMVs
- Spatial data types
- Full text support
- Change tracking
- CLR
- BI
- Encryption
- WIF
Evolution of Azure
- 2008:
- App hosting, two roles, queue based communication, partial trust asp.net
- storage: blobs, tables, queues
- Desktop sdk: cloud simulation
- Service mgmt portal: vip swap upgrades, automatic os servicing
Coming soon:Paid usage Feb 2010
New api exposed and updates IP/Port values
Direct inter-role communications
RoleEnvironment.Changed
RoleEnvironment.Changing
Secure certificate store in the cloud
Logging and diagnostics
Random writes to blobs
Disk drives (February)
Geo-replication
Secondary indexes on tables
Service Management API
In-place rolling upgrade
Azure pricing is more simple than Amazon
- 10c/GB in and 15c/GB out, regardless of service
- Data withing data center is unmetered
- Between data centers is at full rate
Compute
- 12c/h deployed instances (ready state+)
- Same charge regardless of
- Instance is actually running
- In staging or production
Storage
- 15c/GB/m
- 1c per 10,000 REST Calls
- CDN charges (TBD)
- Storage is pro-rated by average daily amount
- Blocks cost money even if not PUT (don't orphan blocks)
- Overhead can be expensive in tables
- Entity pairs, name / values, names also take space
Storage
- 1c / 10000 REST calls
- Appears inconsequential, but hand add up
- Using a queue is at least 3 transactions (enqueue, deque, delete)
- Batch transactions to reduce cost
SQL Azure
- $9.99 / m for 1GB
- $99.99 / m for 10GB
SQL Azure - Instance Based
- Prorated by time (midnight UTC)
- CPU like other roles
- No transaction charge, but beware of throttle
SQL Azure - Accounting
- Included in cac: shema/objects
- ... missed rest too fast
SQL Azure - Determining Usage
Session State
- Cheaper in SQL Azure than in tables due to transactions
Pricing Tips
- Bandwidth: compress response, minify/optimize (aptimize.com)
- Reduce resource size: compress data in blog storage
- User affinity groups to geolocate services
Compute
- Add or remove instances as required
- start same number of instances in staging
- warm up instances
- swap deployments
- more (went too fast)
Storage
- Batch requests
- Model usage
- Compress blog data
- Set content-encoding when putting blob
- use caching to reduce request transaction count
- use CSS sprites and Data URIs to reduce transaction count
Where do static resources live:
- In web role? incurs additional free but resource consuming requests
- Blob: storage cost and transaction cost
Use CSS sprites
- Can massively reduce round-trips and transaction counts in Azure
SQL Azure
- Vertically partition out large data columns
- Support dynamic partitioning if possible
- Consider just-in-time partitioning
- Pull archive data out of the cloud to cheaper on premise storage or to azure storage
- Manipulate db from cloud using System.Data.SqlClient.SqlBulkCopy
Why Partition?
- Classic: Data volume, work load,
- Cloud: cost, elasticity
Horizontal Partitioning (Sharding)
- Spread data across similar nodes
- Achieve massive scaleout
- Intra-partition queries easy
- Cross-partition hard
Vertical Partitioning
- Spread data across dis-similar nodes
- Frequent data in expensive indexed storage
- Large in cheap storage
- Retrieving all data required more than one query
Hybrid
- Combination of horz and vert
Table Storage Key Points
- Partitions auto balanced
- Partition key and row key = primary key
- Distributed queries priced on transaction not cpu, so less costly than things like EC2
- Continuation tokens
- queries without partition keys need these
- helps with cross partition results
- each call with the token is a transaction
- Key columns can be up to 1kb, but 260 is practical limit due to URIs
- Row key = partition key => just one partition (and no continuation tokens issued)
Horizontal partitioning - SQL Azure
- For example, first char of last name is the heuristic for partitioning
- Partition for
- Data volume > 10GB
- Transaction throttle (non-deterministic) always code for retry
- All partitioning is up to the developer
- Partitions are not auto balanced
Choosing a partition key
- Natural keys (last name, ssn, ...)
- Modulo
Hashes
- Project one distribution into another
- Use a function that is a random distribution
- Do not use a crypto hash (overkill on CPU)
- Plenty of examples: tinyurl.com/part-hash
- Be careful of using object.GetHashCode() ( boxing might give different hashes for the same value when hashed more than once)
- Lots of hash stuff on codeplex
Partition stability over time
- May need to change partition scheme
- Two options: repartition all data, or versioning partition scheme
Vertical partitioning
- Balance performance vs cost
- SQL Azure
- Fully indexable
- No query transaction charges
- $9.99/GB
- Azure storage
- ... missed this - slides went too fast
- Duplicated data can lower transaction costs on data
Azure tables != RDBMS
- Storage is cheap
- Cross-partition queries are resource intensive
Modeling Azure Tables
- Currently no secondary indexes
- build indexes yourself
- If associated data is small enough
- Save additional queries
- Duplicate data with each index
- Lots of worker roles to massage data into indexes
Summary
- Partition Data Key to scale cloud apps
- Horiz partition for scale out
- Vertical for cost/performance
- Choose appropriate keys
- All requests go through the load balancer
- Idempotency provided through compensating messages to other queues to provide for replay
- Generally try to build for idempotency
- CRUD is generally not idempotenty, but using integrity keys can be
- issue is with data changing underneath
- Azure queues do not participate in DTS
- Poison Message Handling / Zombie Messages - write message id's to persistent store
- Make sure poison test is at the top of your processing, but should be handled prior to any other code
- Therefore, another worker role that does this before passing messages to other roles, or
- in a base class or your worker role
- dynamic work type in message to route to specific workers
- Key points for dynamic workers
- smart polling model (each poll costs $$)
- use app domains to separate loaded types
- MapReduce pattern
- reduce large problem to small pieces, process, aggregate results
- very parallelizable
- map -> group -> reduce
- Generally processor intensive / ram light
- Summary
- Use async
- Use queues, but make sure for idempotency and compensation
- watch for poison messages
- Dynamic worker provide scalability
If you've ever worked with Silverlight applications that accept user input, you've probably dealt with setting focus to various controls. In that case you may know this already. If you haven't then this should definitely save you some hassles.
There is an issue with Silverlight having their controls receive initial focus. As an example, take a look at the following silverlight application. This is the first page of the application and it has a text box that I would like to have the focus currently in:
I've actually called the TextBox.Focus() method in the load event of this UserControl, but it doesn't have the focus. What gives?
Well, the problem turns out to be that the Silverlight object in the HTML itself has not received the input focus from the browser, and hence the control in the Silverlight app do not receive the focus as it is somewhere else in the browser. As a matter of fact if you click anywhere on the Silverlight app (lets say in the gray areas) the browser will give the Silverlight control the focus, which then gives it to the specified text box.
So how do we enforce that the Silverlight application receive focus from the browser when it is started? That turns out to be quite simple and involves just a couple lines of JavaScript in the web page serving the Silverlight application:
What you do is add the OnPluginLoaded event property on the silverlight control, and put the script shown as in the head tag. Net time (and every time from now on), your control will get the focus when the application starts:
Yeah!
During the building of the WCF service mentioned in my previous post, I came across an error when trying to access the WCF service when running it within the local development fabric. It worked fine when just running the web role, and when deployed into Azure. So, this led me to investigating if the IIS as used by the local development fabric to see if it was not configured to serve .SVC files.
One of the first things that I did to investigate was to just navigate to the service in the browser with a brand new service project that I put together. When doing this, I received the following:
There should be a page showing the information about the service, instead a 403.3 error. Likewise, when stepping through the debugger, similar errors could be seen when checkin the exceptions.
After a bit of googling, the solution to this turns out to be the following. Apparently, WCF is not configured properly with IIS. This feels very similar to the problem when ASP.NET sometimes is not configured properly, in that the solution is very similar.
To solve the problem, navigate to the windows communication foundation folder as shown here, and run the "ServiceModelReg.exe -i" command.
After running that program, you will see output similar to the following and the problem will then be solved.
In my previous post I showed how to rewrite urls which where applied to .asmx web service proxies. In the last couple of days I needed to convert WCF client proxies for Silveright to do the same thing. Turns out this is very similar (if not identical), but I found a new syntax for doing it which makes things a little bit easier. Here is an example:
The ManagementServiceClient class is the proxy class generated for the WCF service. The change is to use the App.Current.Host.Source variable and append the path to the .svc file. Works like a champ and looks real nice.