SQL Azure Migration Wizard - first impression

When generating a script from an on-premise source database to be executed against a SQL Azure in the cloud, there are numerous issues that have to be changed either in the source database or in the generated script. Most of these issues are mentioned in the discussions group

René had the kindness to point me to a tool on CodePlex called the “SQL Azure Migration Wizard”. It’s very early alpha, but when commercial tools like RedGate SQL Compare not supporting SQL Azure (yet?), this migration wizard is a decent second best. You can find the tool here: http://sqlazuremw.codeplex.com/

I tried to move a small database (50 tables, 400 stored procedures and some ASP.NET membership tables/sprocs/views). My own database migrated very smoothely over to SQL Azure, with the exception of non-us collations.

In my create table scripts I have command like this “…nvarchar(x) NOT NULL COLLATE Danish_Norwegian_CI_AS”. When SQL Azure is officially released, it’s expected to support non-us collations, but not at the column level. For now trying to set non-us collations will result in the following error when using string functions in stored procedures: “Cannot resolve collation conflict between 'Latin1_General_CS_AS' and ' Danish_Norwegian_CI_AS ' in equal to operation”. For now, until SQL Azure is released, I solved this by adding the following to the TableStatement element in the “NotSupportedByAzureFile.Config” file: NotSupported Text="\sCOLLATE Danish_Norwegian_CI_AS\s" ReplaceWith=" " SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Removed COLLATE Danish_Norwegian_CI_AS to use database default collation"
This text replacement is executed on the generated script before the script is executed against SQL Azure.

Here are some other issues I ran into:
• Tables worked fine to a new empty database, but to an existing database I got several errors because a column default allready existed.
• Table hints with the WITH keyword is no longer supported. In the ASP.NET membership stored procedures there a statemets like this: “…FROM dbo.aspnet_Users u(NOLOCK)” and this must be changed to “…FROM dbo.aspnet_Users u WITH (NOLOCK)”. Sometimes the source database has the WITH keyword and sometimes its missing, the table alias varies and there’s 15 or so different table hints to look for. Since I’m not the sharpest RegEx head, I fixed this in the source database instead of trying to invent a fancy regex to fix the generated script.
• Data types ‘image’ and ‘ntext’ was replaced for tables, but not for stored procedures and since there’s no specific config section for stored procedures, I use generic TSQL section the following to the TSQL section in the “NotSupportedByAzureFile.Config” file:
NotSupported Text="\sntext" ReplaceWith=" nvarchar(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [ntext] with [nvarchar](max)"
NotSupported Text="\simage" ReplaceWith=" varbinary(max)" SeverityLevel="0" ReplaceString="true" DisplayWarning="true" WarningMessage="Replaced column type [image] with [varbinary](max)"

Wednesday, September 02, 2009 7:16:17 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Azure | S+S | SaaS

Moving to SQL Azure

Moving to SQL Azure Last week I finally got the long awaited invitation for the SQL Azure CTP. That is a real SQL server (with tables and stored procedures as we know them) in the cloud. The featureset is limited, but This is a real SQL sever and not the SDS ACE model, with SOAP and REST interfaces, that was announced as last PDC and that is now being phased out. See SQL Data Services RDBMS Model for details. With pricing starting at $9.99, no initial HW/data center cost and an option to get “auto high availability” it can be a very attractive offering for some scenarios.

Code near vs. code far There are two models that you can work with – “code near” og ”code far”. In the former model, you have you application an the database in the same datacenter in the cloud, i.e. both IIS and SQL as Azure services from MS. Without a relational SQL Azure and ”code near” i think it would be hard to move most existing applications to Azure. With ”code far” you can run you application”on premise” and use SQL Azure in the cloud. That’s very easy (just update the connection string) and very slow (since its usually quite some latency between the on premise application and the cloud based database). “Code far” makes a lot of sense for”remote tools” (SQLCMD, Management studio on your PC working against a SQL instance in the cloud). However I’m still to be convinced that running an on premise application against a clod based database is a good idea.

Tools and scripts RedGate’s otherwise so brilliant tools doesn’t work yet and only a very limited set of functions from SQL Management Studio works, so best bet is probably SQLCMD.

[Updated 28-01-2010] RedGate has released a private build of SQL Compare that I'm currently testing out. Wanna get you hands on the bits? Apply for it here.

Since the feature set in SQL Azure is limited there are many things that can’t be used (ROWGUID, Spatial data, index padding, ANSI NULL, ntext og image data type etc.). This means that a SQL script genererated from Management Studio needs to be “cleaned” from non-supported SQL.

Sunday, August 30, 2009 2:31:16 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Azure | S+S | SaaS

SaaS and S+S at Architect Forum

Last week I gave a session at Microsoft’s architect forum about Software-as-a-Service (SaaS) and Microsoft’s vision about Software+Services (S+S).

During the first half of the session I explained what SaaS is and how it affects both the provider and the consumer of services. I briefly covered and examplified how it affects the business model, solutions architecture and delivery model for the provider as well as how it affects the consumer of the services in terms of integration, security, compliance etc.

During the second half, I explained how service oriented architecture (SOA), SaaS, the previous sessions about “Web 2.0”, “mash-up’s” and identity federation all adds up to Microsoft’s’ S+S vision.

Finally Michel explained into which key areas of the platform he sees Microsoft are investing in order to materialize the vision.

The slides are available here: S+S Overview - Michel Baladi - MS Architect Forum.zip (3.31 MB)

Friday, September 21, 2007 7:40:38 AM (GMT Standard Time, UTC+00:00)      Comments [0]  Identity | S+S | SaaS

Team foundation Server as a Service

Like the idea and want the benefits of Software-as-a-Service (SaaS)? Want to use Microsoft Team Foundation Server for your development team but don't like the idea of installing and operating it 24*7?

Now you can get Hosted TFS at http://www.tfsnow.com/

As a small ISV start-up I allready subscribe for hosted Exchange from http://www.cohaesio.net/. A brilliant way to save me the work of installing and operating AD, Exchange, OWA, ActiveSync for my PDA etc.

For my development projects that I mainly architect & prototype but get developed abroad, I really like the idea of not operating TFS myself. However, I'm not sure I want to pay the $995/month just yet... Especially considering that a certified ISV partner gets TFS free.

Friday, August 17, 2007 9:47:08 AM (GMT Standard Time, UTC+00:00)      Comments [0]  SaaS | TFS/MSF

SHP #9: SaaS single sign-on

Scenario #6: Accessing the application with web single sign-on and federated web single sign-on

Background
On my blog at MSDN I wrote a series of blog post to share my experiences and observations from a series of SaaS engagements with hosters and ISVs during the SaaS incubation effort that I led in Microsoft’s Innovation Center in Copenhagen. The last two scenarios where never finished before I left Microsoft to start my own business. I promised to deliver the last two, so here is one of them….

Why & who?
Until now, this series of blog posts have been talking about building, hosting, delivering and monetizing SaaS solutions. The last piece is consuming the SaaS solution. In earlier posts I described the initial steps in consuming a SaaS solution – subscribing to it. This post focus on authentication and authorization while consuming SaaS applications. My old colleague and participant on the SHP incubation initiative, Kevin Sangwell from Microsoft Europe, have addressed many aspects of enterprise SaaS consumption architecture such as (integration, composition, SLA, monitoring, governance, regulatory compliance etc) in his talks.

Most (all?) existing SaaS applications require you to type in the user name and password of the first user/administrator of the SaaS application. This person can then create more user accounts, roles, permissions. This is known as “delegated administration” and allows each tenant to manage their own accounts, groups, roles etc. This is all nice and good as long as this application is the only one. The problem is that these users already have user accounts in his organization, already are assigned to groups and roles and – more importantly – are part of an identity and access lifecycle strategy and process.

Enterprises typically have processes and/or systems in place to create an account for new employees, assign to relevant groups/roles to give access to relevant systems. If the new employee is in sales and the CRM application is provided as a service, the CRM application need to either integrate with the enterprise systems or duplicate account and group/role information. If the enterprise has 100 internal applications and one new smart must-have SaaS application, it may not seem so bad, but what if the enterprise has 5 or 10 SaaS applications?

It’s easy to see that creating and maintain the user, password, group/role membership etc 5 or 10 times quickly gets cumbersome, but what is worse is termination of employment and regulatory compliance. What happens when the employment is terminated? How can the enterprise de-provision the user from 5-10 different SaaS applications hosted by different providers? And what about reporting? How can the enterprise create a report of what a given employee has access to?

How?
One solution to this problem is to duplicate and synchronize the accounts, groups and roles between the enterprise and each SaaS provider. If all SaaS provides exposed this information through some service API, a directory replication or meta-directory product could help solving the issue. However, I firmly believe that federation is the best solution to this issue. Federation is based on open standards specification such as WS-Federation which is co-written by Microsoft, IBM, BEA, Novell, VeriSign etc, under standardization through OASIS and 20+ of the major organizations and platform companies have stated they will support it. Microsoft supports the first part of federation (browser based a.k.a. “passive profile”) through Active Directory Federation Services – ADFS. ADFS is available in Windows Server 2003 R2. Support for SmartClient a.k.a. “active profile” is planned for Windows Server “Longhorn”.

Without federation a user at the consuming enterprise (application tenant) would authenticate against a local directory (such as Active Directory – AD) when starting the client machine up in the morning. After authentication, the user typically has single sign-on to all internal systems. Through this authentication and local group membership, a user in sales may read internal documents about products/pricing, access inventory status, have mail & IM communications with colleagues to plan an upcoming campaign. However, as soon as the employee tries to access customer & order information from the SaaS CRM application a new logon is required. Then, when the employee wants to write a news nugget about the campaign and update product/price information  on the corporate web site through the SaaS CMS application, a third logon is required. Now imagine an employee in HR, using HR as a Service, trying to terminate the employment of this sales person – how many logons will the employee in HR have and how do the account copies of the sales person get de-provisioned?

Image 1: Conceptual sequence diagram for one consumer of three applications on same or different SHPs without federation – no SSO!

With federation, the accounts only exist once in an account domain. In the enterprise scenario this could be in SaaS consumers AD. The resources, in this case the SaaS application, exist in a resource domain at the SaaS provider. Both the SaaS consumer and the SaaS provider expose a federation server. These servers could be Windows Server 2003 R2 servers with ADFS or any other sever OS/platform that implements the open federation standards. Once the federation servers are in place, a “federation trust” is established between the consumer and provider. This is a one-time set-up that involves describing resources, describing claims and exchange of certificate public keys. This process can be automated through the APIs described in the AFDS SDK.

Once the federation trust is established, the consumer can issue a claim, sign the claim with its private key and pass the claim to the provider. Claims can be confusing in the beginning, but it’s a beautiful concept. The consumer can claim anything and the provider of the resource would accept the claim since it’s signed by someone the provider trust. If the provider is as an analyst web site that have signed an agreement that all the consumer employees can access their reports, the claim from the consumer could simply be that the user indeed is an a employee. The provider wouldn’t need any name, user name, group membership or password to accept the claim and give access to the analyst reports. In order to greet the user and track who is downloading reports, a user name would be useful, but I bring up this example to show that a claim doesn’t have to include a name and definitely not a password.  

In the example of CRM as a service, the claim could be that the user calling the CRM SaaS application is named John Doe, is an employee of the consumer, is a sales manager and is allowed to approve orders up to $50k. Then when John tries to use the HR as Service, his federation server could pass a claim to the HR a service provider that John is a full time employee in the Danish subsidiary. Finally when John tries to use update the corporate web site through the CMS a Service, his federation server could pass a claim to the CMS a service provider that John member is a content editor.

The SaaS application provider can use the claim to authenticate the user as well as to provide access to certain functions of the application based on the content of the claim.

 

Image 2: Conceptual sequence (in reality there’s a lot of redirects going on and there are federation servers involved) diagram for SSO between a consumer and two applications on different SHPs that both trust the consumer’s claims

So what happens when John loses his password? A normal password reset of his local account is enough – no SaaS providers need to know this ever happened.

What happens with the SaaS applications when the consumer decides to implement SmartCard logon? Nothing – the claim sent to the SaaS providers are unchanged.

What happens when John leaves the company to go to a competitor and need to be locked out of the on-premise intranet and extranet applications and all SaaS application from all SaaS providers – simply delete or disable his local account which stops the federation sever from issuing anymore claims about John.

So from the enterprise perspective I think the benefit of federation is clear. It should also be clear for SaaS providers that thinking of identity as islands in each application is not acceptable for the consumers and SaaS providers must provide support for federation in the SaaS Hosting Platform – SHP.

During the SHP Proof of Concept we configured federation using ADFS, included support for the SaaS provider to describe support for federation in the platform manifest, provided support for trust setup in the application tenant provisioning sequence, provided support for the ISV to describe the support for federation in the application manifest and finally we made the SiteCore CMS application “claims aware”. See Lars Nielsen’s (solutions architect at Sitecore) blog post about this here.

Ok, enough about the enterprise SaaS consumers. They have their IT department, their internal directory and could set up federation trust with their SaaS providers. But what about the small business SaaS consumers and the private SaaS consumers? A carpenter working alone or a 5 person company signing up for three applications from different ISVs sold through a single aggregator/store-front in a small business plan would not be willing or capable to set up federation. However, the consumer would not expect to create three accounts for each user and log on three times to access the applications. For these scenarios, federation and ADFS also support a configuration (Federated Web SSO) where the SaaS provider has both the account and the resource domain as in the illustration below:

 

Image 3: Conceptual sequence (in reality there’s a lot of redirects going on and there are federation servers involved) diagram for SSO between two applications on the same SHP where the SHP provides the directory

See the ADFS design guide for details and more information.

Monday, May 28, 2007 9:00:20 PM (GMT Standard Time, UTC+00:00)      Comments [0]  Identity | SaaS

Architect Forum: Software-as-a-Service

This week Kevin Sangwell from Microsoft Europe and myself co-delivered the Microsoft Software-as-a-Service (SaaS) Architect Forum in Copenhagen.

The forum was a full day of presentations and discussions on SaaS covering four main topics:

• Business and technical overview of SaaS
• Architectural implications of building a SaaS solution
• Operational implications of hosting and operating a SaaS solution
• Implications for an enterprise when consuming SaaS applications.

Prior to starting Baladi Software, I worked as an architect and technical lead of the Microsoft Innovation Centre and was one of the driving forces behind helping Microsoft partners build, host and operate SaaS solutions as well as producing architectural guidance for SaaS software vendors and hosting providers. The experience from this work was shared with Microsoft Denmark’s customers and partners through this forum.

My slides are attached and Kevin's deck can be found on his blog

Michel Baladi on SaaS at MS Denmark Architect Forum 2007.zip (1,57 MB)
Friday, May 11, 2007 6:22:32 PM (GMT Standard Time, UTC+00:00)      Comments [0]  SaaS

I'm back!

Finally my new blog is up. It's have had a hectic time starting up a new SaaS ISV business and my consulting business. More on that later...

This blog will take over where my old blog at MSDN left off. I still need to shine it up a bit, but expect to see some fresh SaaS content soon...

I promised to finish the SaaS hosting platform series and I will deliver on that promise - stay tuned...

Michel Baladi
michel at baladisoftware dot net

Friday, May 11, 2007 5:44:39 PM (GMT Standard Time, UTC+00:00)      Comments [0]  SaaS | Misc