# Friday, October 30, 2009

With the official release of SQL Azure less than three weeks away, we are starting to see mainstream vendor support for SQL Azure. Telerik’s OpenAccess ORM is no exception. With the Q3 release of OpenAccess next week, OpenAccess will have full support for SQL Azure, going further than the basic support available today that I demonstrated on my blog last month. Full wizard support, forward and reverse mapping, and of course data services support via the Telerik Data Services Wizard. Let’s take a look at the basics here.

Getting Started

To get up and running and show the mapping and LINQ support, I will open Visual Studio 2008 (or 2010) and create a simple Console Application named OpenAccess.Azure.Demo. The first step is to enable the project to use OpenAccess via the Enable Project Wizard. As part of the wizard you need to specify which database you are going to connect to. OpenAccess gives you many choices besides Microsoft SQL Server, and one of the native choices is Microsoft SQL Azure. After you select SQL Azure, you will need to provide your credentials (don’t forget to put the tcp: in front of your server name.)

image

After you connect and finish the wizard, it is time to do some mapping.

Mapping Database Objects

To map some database objects to persistent classes, choose the Reverse Mapping wizard. This will bring up the Reverse Mapping dialog where you can select which tables, views, and stored procedures you want to map. In this case I will just select all of the defaults and map all of Northwind (remember I migrated Northwind up to SQL Azure.) Now it is time to build a simple application.

image

Working with SQL Azure Data

Let’s write a LINQ statement to fetch all of the customers from one country and print it out to the console window. First tings first, you have to put in a using statement for OpenAccess:

using Telerik.OpenAccess;

Next we will create our LINQ statement. The LINQ statement will work like any LINQ statement in OpenAccess, there is nothing special for SQL Azure, this LINQ statement would work against SQL Server, MySQL, or Oracle.

   1:  static void Main(string[] args)
   2:  {
   3:      //data context
   4:      IObjectScope dat = ObjectScopeProvider1.GetNewObjectScope();
   5:      //LINQ Statement
   6:      var result = from c in dat.Extent<Customer>()
   7:                   where c.Country == "Germany"
   8:                   select c;
   9:      //Print out the company name
  10:      foreach (var cust in result)
  11:      {
  12:          Console.WriteLine("Company Name: " + cust.CompanyName);
  13:      }
  14:      //keep the console window open
  15:      Console.Read();
  16:  }

The LINQ statement uses the data context, or IObjectScope in line 4, has a simple LINQ statement on lines 6-8 to filter by the customers in Germany and then iterates those customers and prints them out to the console window in lines 10-13. The result is shown here:

image

Pretty basic application, however, you can see that Telerik OpenAccess has full support for SQL Azure. Next week I will show a more complete example.

Enjoy!

Technorati Tags: ,
posted on Friday, October 30, 2009 3:47:58 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Thursday, October 29, 2009

I am lucky to get to speak at many conferences in many different countries each year. A lot of people ask what it is like to be an international confrence speaker. For the most part, mind airport security and flight delays, it is the most fun you can have and still be working. You get to hang out with your peers all over the world and geek out. Then of course there is the partying…

Sometimes your friends and colleagues want to keep partying well after the bar is closed. At the last BASTA confrence in Germany last month, I retired around 2am well after the we shut down the bar. My colleagues lead by fellow Telerik employee Peter Brunner and thinktecture’s  Christian Weyer aka the tall German, decided to keep drinking and raided their minibars. When they exhausted their minibars, they came to my room.

Posing as housekeeping they come to my room in the middle of the night to wake me up. They even turn around my do not disturb sign to “come on in.” I knew it was not housekeeping, but I did not expect a party at my door. After I answer the door in my underwear, I give them a tour of my “roomlet” as well as drink a beer before kicking them out. Christian Weyer and I also play with my beer stein Oktoberfest hat that I won at Oktoberfest-it is not meant to fit on a normal human’s head, but it does fit Christian’s.

Daniel Walzenbach caught this all on film.

 

Technorati Tags: ,

posted on Thursday, October 29, 2009 3:19:35 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Wednesday, October 28, 2009

The developer ecosystem is starting to rally around SQL Azure, and that is a healthy thing. I stumbled across this neat SQL Azure query tool from Microguru today. It is called the Gem Query Tool for SQL Azure and you can download it here. It uses the .NET Framework 3.51 and is pretty lightweight. The cool thing is that it has a community edition that is free to use.

According to Micoguru the tool:

provides an intuitive user interface to connect to and work with SQL Azure databases. Gem Query Tool supports execution of any DDL and DML script supported by SQL Azure. To facilitate authoring of SQL queries, Gem Query Tool for SQL Azure displays tables and columns in your database.

I gave it a go today and it is simple to use. What stands out about this tool and why I highlight it after I highlighted a few other tools is the database browse schema feature. This is a basic feature that SQL Server’s own Management tool and the SQL Azure web site both lack. Gem Query allows you to log into one database (my only complaint is that there is not a “browse databases” feature) and select your tables or views. Once you click on one you can view the schema in a nice grid:

image

Access to this metadata is important after you have migrated some data since some of the data types or defaults may not be what you expect. I also like that when you click on a database object you have a “column” or “data” view. When you click on the “data” tab, you will have the option to select the Top 100, Top 1000 (pretty convenient) and All.

image

Gem Query has a very easy to use Query interface. While most other tools leave you on your own to write SQL, Gem Query gives you three windows with the SQL keywords supported by SQL Azure and a list of row returning database objects (tables, views, etc..) When you select a SQL command, it will put it into the query window for you, as it will for the objects. When you select an object you will also see the available columns and click on those to build your query. I wrote this very simple query using the builder:

 

   1:  SELECT CustomerID, ContactName, Country
   2:  FROM Customers 
   3:  ORDER BY Country 

image

 

Enjoy!

Technorati Tags:
posted on Wednesday, October 28, 2009 6:53:55 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Tuesday, October 27, 2009

The Telerik WCF Wizard that I have shown on this blog several times has been updated and is available on Telerik Labs for download.

Whenever Telerik puts something up on Telerik Labs we hope that some early adopters will try it out and give us feedback. We had an amazing demand for the Telerik OpenAccess WCF wizard and got tons of feedback. The most overwhelming piece of feedback is the name of the wizard: so as of this CTP, the wizard is now renamed the Telerik Data Services Wizard.

screen1

We made some key enhancements in this build for useability. The big thing is that you no longer have to be an ADMIN user to use the wizard. In addition, we made some of the navigation simpler as well as squashed some bugs (mostly around using LINQ statements in older versions of OpenAccess). Speaking of older versions of OpenAccess, the wizard will automatically detect what version of OpenAccess you have installed and auto-update itself to work with that version.

We’re working around the clock on a new build that will have Visual Studio integration and a new UI that is more consistent with the OpenAccess UI. A little further out, we will also include support for ADO.NET Data Services 1.5 CTP2 using the new “data service provider” interface available in Astoria 1.5. Once we are at that point, we will release this a full fledged beta with a target for the wizard to be part of OpenAccess proper by Q1 release next year at the latest.

Enjoy!

Technorati Tags: ,,,
posted on Tuesday, October 27, 2009 7:27:04 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Sunday, October 25, 2009

While visiting Sichuan Province in China this weekend, I traveled to the Wolong Nature Reserve. Wolong is the new home of the giant pandas after the deadly earthquake last April destroyed their last home (and unfortunately killed a panda).

Wolong is very special since it is the only place in the world where you can go into the habitat and feed and play with the pandas. While playing with the pandas, he bit me on my leg (and Kathleen on her finger!)

Now that is something that does not happen every day. No harm was done, except for a few black and blues. Of course the pandas were just playing! We all kissed and made up-after we fed them of course! :)

IMG_0687

Technorati Tags: ,
posted on Sunday, October 25, 2009 8:44:39 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, October 23, 2009

As you most likely know by now, SQL Azure is now on production servers and the team is feature complete for the PDC build. What is funny is that they did a migration in the middle of the night on Tuesday, right smack dab in the middle of the day in Bulgaria where I was demoing SQL Azure at DevReach. That lead to some funny demos crashing. What fun!

The good news is that SQL Azure now supports BCP, so you should be able to easier bulk load data on into SQL Azure. No more crazy work around like the ones I posted on this blog and on many others.

Another new feature, which took me a while to discover when all of my unit tests failed, is a firewall. You can now specify which IP Addresses can access your SQL Azure account. At first this kind of pissed me off, but I like that I can completely control who can access my account. (Especially since Microsoft will start charging soon for SQL Azure.) To set your allowed IP Addresses, just click on the “Firewall” tab in the SQL Azure manager and add a range of IP Addresses and you will be in good shape.

image

Anyway, SQL Azure support is improved in SQL Server Management Studio, however, you still can’t use the object explorer. I reviewed SQL Azure manager a week or two ago, and that still works as advertised against the new production builds.

image

If you are working with Visual Studio 2010 beta2 that shipped this week, you can use a refreshed version of the SQL Azure Explorer. The 0.2.2 version is built specifically for VS 2010 b2 and is very stable, the author on codeplex marked it now as a beta. After you download and install SQL Azure Explorer, you have to go into Visual Studio and enable extensions. You can do this via Tools|Extension Manager. You will see that SQL Azure Explorer is disabled and extensions are not available. You have to click on the hyperlink and then check the allow button in the dialog that follows. After you close and reopen Visual Studio, you will see SQL Azure Manager as the first item in the Tools menu.

image

There have been some major speed improvements to the login and object view process. Also the ability to create and drop databases, users, etc. A nice feature (that was also in the last version but I did not see it) is that you can parse your query and even format your TSQL. Pretty cool.

image

Technorati Tags:
posted on Friday, October 23, 2009 4:15:02 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, October 19, 2009

The European Tour continues! I am speaking on REST and Data Access at the SDC in the Netherlands this week. Hope to see you there.

image

posted on Monday, October 19, 2009 4:12:23 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Thursday, October 15, 2009

I’ll be speaking at the CodeCamp in Cracow, Poland on Saturday. Not only will I be doing my Scrum and Data Access talks, but another speaker got sick and I will be doing a .NET Rock Interview with Richard and Carl. Should be a great amount of fun! See you there.

image

posted on Thursday, October 15, 2009 5:42:34 PM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Saturday, October 10, 2009

On Monday and Tuesday I will speaking at DevReach in Sofia, Bulgaria. I’ll be doing my Data Access Hacks and Shortcuts and RESTful applications with Microsoft Tools sessions as well as the Daily Scrum talk with Remi Caron.

Chris Sells is doing a keynote on Oslo and Central Europe DPE lead Luka Debeljak will be doing a keynote on Azure. Should be great. Hope to see you there.

image

posted on Saturday, October 10, 2009 6:24:12 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Friday, October 09, 2009

Here is a another pretty cool find for managing SQL Azure databases. The SQL Azure Explorer for Visual Studio 2010 Beta 1 is a neat little add-in that allows you to explore your SQL Azure databases as well as run SQL statements.

Once you have the tool installed, just fire up Visual Studio Beta 1 (I assume when Beta 2 ships the tool will be updated) and you can access the tool from the Tools|SQL Azure Explorer menu option. You have to provide your SQL Azure login credentials (you may have to put as the login your username@servername)

image

After you have logged in, you will see the Object Explorer tree. You can drill into the database objects or select the top 100 rows of a table or view.

image

You can also right click on the database and say “Open SQL Editor” and type in SQL Statements against your SQL Azure database.

image

If you are using Visual Studio 2010 beta 1 and spend a lot of time in Visual Studio, you may want to give the SQL Azure Explorer a try since you won’t have to leave Visual Studio.

Enjoy!

Technorati Tags:
posted on Friday, October 09, 2009 4:15:54 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Thursday, October 08, 2009

Last month I showed how to manually migrate schema and data from SQL Server 2008 to SQL Azure. I also mentioned the SQL Azure Migration Wizard developed by George Huey in that post. Since then George has updated the tool (currently on 0.2.7) and it now has some great features that make it far more compelling than creating and fixing scripts generated by SQL Server.

To get started, the tool will allow you to select a SQL Server database and then ask you to select which objects to migrate.

image

It will then do an analysis of your database and attempt to fix the problems. For example, if you have a table with a TEXT column, it will automatically make it a varchar(max) and will also unbind XML schemas and UDTs. It will remove keywords (like PAD_INDEX as shown below) not supported by SQL Azure.

image

After you connect to SQL Azure, the tool makes a script for you. You have the option to run the script as is, or you can save it to disk, or you can modify it in the window and run it in real time. The scripts have good comments in there telling you about some potential problems like XML data types, etc. After you play with the script, you can execute it and the tool will let you know the success or failure of the script.

image

Now that you have migrated the schema, the hard part is over. Next you can migrate the data. The easiest way to do this so far is via Red Gate SQL Compare as I showed last time or via scripting the data via the SQL Server script wizard. To get there right click on a database in SQL Server 2008’s SQL Server Management Studio and select Tasks|Generate Scripts. After you select your database you are brought to the Choose Script Options dialog. Turn off all of the other options except “Script Data” and choose on the next screen only the tables you have moved using the SQL Azure Migration Wizard.

image

After you choose the tables to move over, you can then choose to send the script to a new window. You will notice that the only thing that was scripted was the INSERT INTO statements.

image

Now select the tab that contains your query and then choose Query|Connection|Change Connection from the main menu. Enter in the login credentials you have for your SQL Azure account and then click on the Options tab and enter in the name of the database you are connecting to. After you connect you can attempt to run your query. Just make sure that your script will be compatible with SQL Azure before you run it. Meaning if you removed any XML data types, etc, using the Migration tool, you will have to do the same to your script. There is always some cleanup to run, but is pretty straight forward. For really large databases, you may want to highlight sections of the script and run it a table or so at a time to prevents timeouts to SQL Azure. You will also have to make sure that you arrange the script to sequence the INSERTs to coincide with the foreign keys constraints. SQL Server is smart enough to put Order before Order Details, but not all objects does it do this for you.

image

When you are done you can view your database using the SQL Azure Manager I talked about yesterday.

image

Enjoy!

Technorati Tags:
posted on Thursday, October 08, 2009 5:52:46 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Wednesday, October 07, 2009

As I showed on this blog a few weeks ago, SQL Server Management Studio support for SQL Azure leaves a lot to be desired. No changing databases, no object explorer, and no easy way to connect. Good thing that Maarten Balliauw and Juliën Hanssens built the SQL Azure manager. It is available here. It is listed as a proof of concept and “highly unstable” but after a few days of playing around with it, I have encountered almost no problems. The tool is available only as a click once application that runs online with no install, so you can try it out risk free.

Maarten and Julien describe the tool as: SQL Azure Manager is a community effort to quickly enable connecting to your SQL Azure database(s) and perform basic tasks…

That is an understatement, the tool is great and saves lots of time. After the brief install, I was promoted with the login screen and I put in my login credentials and was off to the races.

image

The first thing you notice is that out of the box we have two much needed features, the ability to change databases and a full fledged object explorer. You can expand to see Tables and Views, but not Stored Procedures.

image

I have a stored procedure in this database and can’t see it in the Object Explorer, so I decided to make my fist query a simple one:

select * from sys.objects
Where type='P'

A pretty awesome feature is that you can hit F5 just like you would in SSMS and get the results as you can see here. I was able to write several TSQL queries and browse the results without any problem. When you right click on a table it will give you the option to Select the top 100, Select All, or delete the object. Pretty cool.

image

Another cool feature is that you can drag a column up to the top to automatically sort. Here I sort by City in the Customers table.

image

My only issue was that after a while, the tool times out, I suspect that this is a SQL Azure thing.

The tool makes it very easy to delete objects and databases as well as create new database. (Careful with the delete database feature!!!)

While this is a 0.1.7 release, it is worth playing with. It will save you lots of time when managing your SQL Azure databases. The roadmap to a 1.0 release is to convert to WPF, move the app to codeplex, and make it more stable.

Thanks to my colleague Aditya Pendharkar at Telerik for finding this gem of a tool.

Technorati Tags:
posted on Wednesday, October 07, 2009 3:44:35 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, October 05, 2009

I have just completed an amazing 8 day journey to the village of Chyangba in a remote mountain area of Nepal. Chyangba is a village of about 55 homes inhabited by the ethnic group called Sharpa. Most of you will know Sherpas as the folks who climb up to Mt. Everest, and several famous Sherpas come from Chyangba. My friend and guide in 2003 and 2008, Ngima Sherpa, comes from Chyangba and I was going to visit him. In addition, I was working with a charity called Education Elevated to help fix up a school and set up a library. While in Chyangba, I worked on the school and library for 4 days.

Getting to Chyangba

Getting to Chyangba is not easy. We had to fly on a 16 seat Twin Otter from Kathmandu to Phaplu. Phaplu has an “airport” consisting of a dirt strip and a dude with binoculars and a radio. After landing in Phaplu we trek a few hours (mostly in the dark) to our camp.

IMG_0329

Our camp was visited by some local kids in the morning and had great views of the valley. We then trek the whole next day and finally arrive in Chyangba.

Visiting the School and Library

Upon arrival, all the school children were lined up waiting for us. We then walked around the school and library for a few hours and took hundreds of photos. Imagine hiking for 7 hours and going directly into a photo shoot. :)

IMG_0372

The kids are super cute.

IMG_0490

Project Planning

We start to size up the job ahead of us. Here is a photo of an empty room we will convert into the school library.

IMG_0376

Being geeks we decide to be agile and use the scrum methodology. We decided we would re-assess the situation twice a day and see how far we get. We took stock of what furniture we had in the building (school desks, etc) and since we are MVP geeks, we decided to use a GUID system (globally uniquely identifier for Tanya and my mom, the only two non-techies I know who read my blog.)  We put the benches into four categories: good enough, reinforce,  take apart and put back together with some new wood, and ask Roger (the scrum master).  Here is a photo of a school bench with GUID # 8.

IMG_0418

Getting to Work

Roger the carpenter and general contractor (and scrum master) worked wonders. We computer geeks just hung around and he told us what to do. Before I knew it I was taking apart school chairs, benches, desks, etc, and rebuilding them. I got pretty good with a hammer.

IMG_0472

We continued for a few days, constantly reassessing. I did not think we could fix all of the furniture in the four days we had as well as build a library (shelves, tables, and desks.) But Roger kept us on target. He did have electricity from 9:30am to about 2pm each day and was able to use a power saw. Awesome. But the kids were attracted to it like moths to the light, so I had to distract them by balancing wood on my head. As the week progressed I got better and was able to balance an entire bench on my head while standing on one foot (in the Dancing Shiva position for you Yogis.)

IMG_3193

The kids started to imitate me.

IMG_0487

Sprints 6 and 7

We did two sprints a day. Sprint 6 was on day 3 and we (mostly Roger) installed the shelves. We brought about 100 lbs of books and started to stack the shelves. After that some of us read to the kids and helped them practice counting in English.

IMG_0465

Sprint 7 was awesome. We gave out all of the school uniforms to the kids. (In Nepal you can’t go to school if you don’t have a uniform.)

IMG_0496

After we give out the uniforms, the kids all ran to change and then do a little dance for us. After we celebrate and I teach some of the kids the fist bump.

IMG_0504

Leaving :(

After spending the last few hours with the kids and helping them read and count, we departed for a final meal at our campsite. The Sherpas cooked us a chocolate cake, I have no idea how they did that over a campfire. We then went to one of the local's house for a party and drank the local drinks: Chang and Roxi. They are evil drinks. Apparently it is a Sherpa custom to refill your drink immediately after you take a sip. I have no idea how much Chang I drank, but I think I can still feel it. We then turned the house into a Sherpa Disco and danced the night away to local music. (Sherpas can get down.)

The next day we had a final going away ceremony with the whole village and they put tons of Buddhist koda and flowers on us. Since we were mostly going down, we trekked the whole way back to Phaplu in one day. We treated ourselves to $5 a night hotel rooms and flew back to Kathmandu the next day.

IMG_0539

This was a great experience, we spent a week in a local village, a village not even on the map, and made a difference. For geeks, we did the best we could-which was far more than I thought we could do. I hope that the tech community can donate a lot in small amounts, it only takes $10 to buy a school uniform or a few books so a kid can go to school. You can donate here. :)

Technorati Tags: ,
posted on Monday, October 05, 2009 8:30:25 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback