# Tuesday, January 18, 2011

S.A. Developer.net

January 2011 – Building RESTful Applications with the Open Data Protocol

Please join us for an interesting session presented by Stephen Forte

Overview:

Applications today are expected to expose their data and consume data-centric services via REST. In this session we discuss what REST is and have an overview of WCF Data Services and see how we can REST enable your data using the Open Data Protocol (OData). Then you will learn how to leverage existing skills in Visual Studio, LINQ and data access to customize the behavior, control-flow, security model and experience of your data service. We will then see how to enable data-binding to traditional ASP.NET controls as well as Silverlight. We’ll look at other consumers of oData including SQL Server PowerPivot for Excel, wrapping up the session by talking about consuming other OData based applications in .NET as well talk about a non-Microsoft client. This is a very demo intensive session..

Speaker Bio – Stephen Forte:

Stephen is the Chief Strategy Officer of Telerik, a leading vendor in .NET components. He sits on the board of several start-ups including Triton Works and is also a certified scrum master. Prior to this he was CTO and co-founder of Corzen, Inc, a New York based provider of online market research data for Wall Street Firms and before that CTO of Zagat Survey in New York City as well as co-founder of the New York based software consulting firm The Aurora Development Group. Stephen is the Microsoft Regional Director for the NY Metro region, an MVP, INETA speaker and co-moderator and founder of the NYC .NET Developer User Group. He speaks regularly at industry conferences around the world and has written several books on application and database development. He currently Stephen has an MBA from the City University of New York.

Date: 27th January, 2011
Time: 18:15 for 18:30
Venue: Microsoft Cape Town (Please note the address below)
Golf Park 3, Engen House, Raapenberg Road, Mowbray
Please RSVP (!) at http://sadevct201101.eventbrite.com/

posted on Tuesday, January 18, 2011 5:46:09 AM (Eastern Standard Time, UTC-05:00)  #    Comments [0] Trackback
# Friday, September 04, 2009

SQL Data Services underwent a massive change with the last CTP, eliminating the entity bag container, whatever they called it thingie and moved to a fully cloud-based relational model. Another major change was that it was given a new and more appropriate name: SQL Azure. You can get the CTP and have access to what I call SQL Server lite in the cloud. Since SQL Azure supports the fully relational model along with stored procedures and views, you can connect to SQL Azure with a regular old ADO.NET connection string like the following one, allowing you to code against SQL Azure with .NET the same way you did with plain old SQL Server.

Server=tcp:tpzlfbclx1.ctp.database.windows.net;Database=Northwind_Lite;User ID=Stevef;Password=myPassword;Trusted_Connection=False;

Once you are all signed up for the CTP you can go into the web based admin tools and create a database. I created a database called Northwind and another one called Northwind_Lite for testing.

image

To be honest, I am not sure what else you can do in the web interface. So you have to connect via SQL Management Studio to create your database schema. There is the first problem. SQL Azure does not support the object explorer view that you get in SQL Management Studio, so you will have to hack a little bit.

Connecting to SQL Azure with SQL Server Management Studio

This is not as easy as it sounds. :) Since you can’t connect through the object explorer, you will have to open a new TSQL Query window.

image

In the log in dialog, enter in the server name from the CTP’s connection string and the user name and password that you choose to administer the CTP.

image

SQL Azure does not support the “Use” statement, or the ability to change databases on your connection. So you have to cheat and use some of the advanced options when logging in. You can do this by selecting the “Options >>” button on the log in dialog and then selecting “Connection Properties”. Under the Connect to database option, you have to select the database that you want to work with, since the default will be the Master database and most likely you will not be building any applications using the Master database.

image

After you connect you will get an error about the inability to apply connection settings, which you can ignore.

image

You will notice right away that there is nothing in your database as the following SQL statement will show:

select * from sys.objects

We now have to migrate some database objects from our SQL Server database to SQL Azure.

Migrating Existing SQL Server Objects to a SQL Azure Database

It would be cool if there were some easy way to migrate your databases to SQL Azure in this CTP. There is not. I suspect that in future CTPs this will not be a problem. But for now, you have to get creative. Some hacks and shortcuts are in order.

To get started, let’s just copy over one table. To do this, open your local SQL Server in the object explorer. Drill down to the Northwind database and drill down into the Customers table. Right click and select Script Table as|CREATE To|Clipboard and you will have a nice CREATE TABLE statement on your clipboard.

 

image

Then paste the TSQL into the Query Window that is connected to your SQL Azure database. Here is what my generated TSQL looks like:

   1:  USE [Northwind]
   2:  GO
   3:   
   4:  /****** Object:  Table [dbo].[Customers]    Script Date: 09/04/2009 03:35:38 ******/
   5:  SET ANSI_NULLS ON
   6:  GO
   7:   
   8:  SET QUOTED_IDENTIFIER ON
   9:  GO
  10:   
  11:  CREATE TABLE [dbo].[Customers](
  12:      [CustomerID] [nchar](5) NOT NULL,
  13:      [CompanyName] [nvarchar](40) NOT NULL,
  14:      [ContactName] [nvarchar](30) NULL,
  15:      [ContactTitle] [nvarchar](30) NULL,
  16:      [Address] [nvarchar](60) NULL,
  17:      [City] [nvarchar](15) NULL,
  18:      [Region] [nvarchar](15) NULL,
  19:      [PostalCode] [nvarchar](10) NULL,
  20:      [Country] [nvarchar](15) NULL,
  21:      [Phone] [nvarchar](24) NULL,
  22:      [Fax] [nvarchar](24) NULL,
  23:   CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
  24:  (
  25:      [CustomerID] ASC
  26:  )
  27:  WITH
  28:   (
  29:  PAD_INDEX  = OFF, 
  30:  STATISTICS_NORECOMPUTE  = OFF, 
  31:  IGNORE_DUP_KEY = OFF, 
  32:  ALLOW_ROW_LOCKS  = ON, 
  33:  ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
  34:  ) 
  35:  ON [PRIMARY]
  36:   
  37:  GO
  38:   

We already know that SQL Azure does not support USE, so eliminate lines 1&2 and press F5. You will see that line 5 also is not supported, so eliminate that and keep going by pressing F5 again. You will see that ANSI_NULLs, PAD_INDEX, ALLOW_ROW_LOCKS, ALLOW_PAGE_LOCKS, and ON [PRIMARY] are not supported, so you will have to eliminate them as well. Your new trimmed down SQL Azure SQL script looks like this:

   1:  SET QUOTED_IDENTIFIER ON
   2:  GO
   3:  CREATE TABLE [dbo].[Customers](
   4:      [CustomerID] [nchar](5) NOT NULL,
   5:      [CompanyName] [nvarchar](40) NOT NULL,
   6:      [ContactName] [nvarchar](30) NULL,
   7:      [ContactTitle] [nvarchar](30) NULL,
   8:      [Address] [nvarchar](60) NULL,
   9:      [City] [nvarchar](15) NULL,
  10:      [Region] [nvarchar](15) NULL,
  11:      [PostalCode] [nvarchar](10) NULL,
  12:      [Country] [nvarchar](15) NULL,
  13:      [Phone] [nvarchar](24) NULL,
  14:      [Fax] [nvarchar](24) NULL,
  15:   CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED 
  16:  (
  17:      [CustomerID] ASC
  18:  )WITH 
  19:      (STATISTICS_NORECOMPUTE  = OFF, 
  20:          IGNORE_DUP_KEY = OFF) 
  21:  ) 
  22:  GO
  23:   

Run this and you will have a new Customers table! Unfortunately there is no data in there, but we will get to that soon.

image

If you are moving a lot of tables and foreign key constraints, etc, you should use the SQL Azure Migration Wizard developed by George Huey. This tool,available on codeplex, will assist you in migrating your SQL Server schemas over to SQL Azure. Wade Wegner blogged about it here, including an instructional video.

Unfortunately there is no such tool for migrating data that I know of. Time for the next hack.

Migrating Data from SQL Server to SQL Azure

I thought that maybe I can cheat the same way I altered the connection settings and use SSIS to migrate the data. I choose the ADO.NET option and entered in all of the data, but it bombed. Then I tried my old reliable tool, Red Gate’s SQL Data Compare. No go. But it was worth a try, since it got me thinking. I created a new database locally called “Azure_Staging” and ran the same CREATE TABLE script there, creating a blank Customers table. I then ran SQL Data Compare using the full Customer table in Northwind as my source and my newly created blank Customer table in Azure_Staging as the destination.

Of course SQL Data Compare found 91 missing rows and I launched the Synchronization Wizard.

image

Click through it and on the 3rd page, click on the “View SQL Script…” button and copy and paste the generated SQL.

image

Copy and paste just the 91 INSERT INTO statements into your SQL Azure Query Window and run it. Now we have data in SQL Azure!

image

Unfortunately this is not the best situation, having to manually create some TSQL scripts, but this is an early CTP. I am sure that future CTPs will make this much easier.

posted on Friday, September 04, 2009 4:19:51 AM (Eastern Daylight Time, UTC-04:00)  #    Comments [0] Trackback
# Monday, November 03, 2008

I have been a fan of the cloud since Amazon first released its first APIs. We have been waiting for Microsoft to enter in the cloud space and we have been seeing some stuff drip out over the last year, Astoria (while not cloud, it is a RESTful service that allows us to be cloud ready), Live Mesh (which people confuse as a consumer offering, but actually is a development platform), and SQL Server Data Services (SSDS).

Last week at PDC, Microsoft spoke abut Windows Azure, its cloud services platform. It will consist of web application and service hosting, .NET Services (basically the BizTalk services stack), storage, and data services (SSDS, or now just SDS). Some developers at the PDC were like “this is like the ASP model ten years ago, Azure is doomed to fail.” So the question is, will Azure succeed where the ASP model failed?

The ASP model was about a generic application hosted by an ISP and sold as a service to you. Picture instead of using ADP for your accounting software, you would log onto the ADP web site and use it as a service. This model did not completely fail, but it did not deliver on its mission. It was also a lot of .com hype and about 10-15 years ahead of its time with both the technology and the business acceptance.

While things like Live Services and hosted Exchange is part of Azure, Azure is not about ASP, but about Hosting your app, services, and data in the cloud. There is a need for this: Amazon EC2 and S3 are quite successful, even with the crowd that you think would never put their data in the cloud: Banks. It will take time, but companies will buy into this paradigm and make the shift. The first thing to go into the cloud in masse will be hosted Exchange, then file server capabilities, then applications, then data. Small businesses will go first. It may take years for the shift to be complete, but it will happen. It just makes sense to have your applications hosted in the cloud, why bother and worry about the infrastructure. Infrastructure will be a commodity by 2012. By then most new apps will be hosted in the cloud or using the cloud infrastructure for .NET Services or data.

Only 12 years too late! During the .com era, when I was a CTO of a large .com, I spent 65% of my time worrying about the infrastructure (bandwidth, RAID arrays, load balancing, switches, etc.) Years later at Corzen to support our spidering engines, I focused on infrastructure about 50% (only reason why it was lower than .com era was due to virtualization.) Now I need more bandwidth, more load balancing, it is just a click of a button. Sure it is not going to be this easy, but even if it delivers on 50% of its vision, it will reduce my focus on infrastructure by 95%.

.NET Services (formerly BizTalk Services) in the cloud will get adopted by developers as it matures and as apps get moved to the cloud. SQL Services will get adopted in version 2 when you can do more relational features just as tables, joins, views, etc, instead of the “flexible entities” approach of the first release.

Bottom line is that Azure will succeed, but it will take time for the world to catch up to Microsoft’s vision. Amazon (and to some degree Google) have paved the way.

posted on Monday, November 03, 2008 9:56:29 AM (Eastern Standard Time, UTC-05:00)  #    Comments [2] Trackback