Migrating Large Databases from On-Premise to SQL Azure

altRecently, I was working on a project that required a site migration from a Shared Hosting server to Windows Azure. This application has been up and running for sometime and had acquired quite a substantially sized database.

During the course of the project I ran across a few road blocks which I wasn’t expecting, due to the experience gained in my previous blog entries: Migrate a database using the SQL Azure Data Sync Tool and Scripting a database for SQL Azure (Issues explained in previous link resolved with launch of SQL Server 2008 R2). Hopefully the following tricks will help you along your data migration.

Using Import/Export in SSMS to Migrate to SQL Azure

In addition to the SQL Azure Data Sync Tool, it is possible to use the existing Import/Export Wizard in SQL Server Management Studio to migrate data to SQL Azure. There are a number of things to keep in mind while using the Import/Export Tool:

SQL Server Native Client to .NET Data Provider for SqlServer

SQL Azure doesn’t fall under the typical SQL Server Native Client 10.0 Product SKU, this means that you’ll have to use the .NET Data Provider to migrate your data. The configuration screen for the provider is very intuitive, but there are two key settings that should be changed from their default values, Asynchronous Processing (set to true) and Connection Timeout (increase to 1500).

SQL-Azure-SMSS-IE-Tool

Without changing the Timeout value the data migration would error out after creating the fist few sets of rows. Making this an Asynchronous process was beneficial when exporting multiple tables at a time.

Work-around for SSIS Type: (Type unknown …) Error

There is a chance when you go to run the migration that you will encounter an error as described in Wayne Berry’s [@WayneBerry] blog post entitled “SSIS Error to SQL Azure with varbinary(max)” on the SQL Azure Blog.

As Wayne explains in his post, there are a number of XML files which contain data mapping information used by the Import/Export Wizard in order to map the data from the source database to the proper data type in the destination database.

Database Seeded Identity Insert Issue

I’m not sure why this happened, but when using the Import/Export even with Identity Insert on, the ID [Identity] Column was not Inserting the correct values. To get around this I used the ROW_NUMBER to generate new Identities and rebuilt the foreign key tables.

There is a lot of chatter on the Forums and other blog posts that say that BCP with the –E switch is the most effective way to do exact copying (with Identity Columns).

For more information:

Cost Effective Approach

A good thing to keep in mind while preparing your database for migration is that transactions as well as data transfer costs are applied to Queries to (and from) SQL Azure. With this in mind it would be best to set up a scenario where you would test your data migration to ensure the data migration would be performed in the least number of attempts as possible.

Happy Clouding!

This post also appears on SyntaxC4's Blog

VS 2008 at The Movies, Feb 7, 2008 Toronto Paramount

Posters_Codefather Our designer is having a field-day with this "at the Movies" theme for our upcoming review of Visual Studio 2008 being held Feb 7th from 8:30am-12:00pm @ the Paramount in Toronto. Grab a copy of this movie poster before it gets "whacked" by the lawyers.

Hope to see you there. Check out all the details after this link.

Visual Studio, SQL Server, and Windows Server 2008 Launch Events in Toronto

clip_image001

On February 27 in Toronto, MS Canada is hosting the official launch of the above mentioned products. The event will be all day long and in addition to a keynote from COO Kevin Turner, there will be some great breakout tracks running in parallel for IT Professionals, Developers, IT Managers, and Architects.

The event will be held at the Direct Energy Centre downtown. Of course ObjectSharp will have a booth there with some great offers for both our Training and Professional Services along with some awesome prize raffles so please stop by.

Also make sure to stop by the expert's area where several MVP's and speakers will be able to answer your individual questions including many of the MVP's from ObjectSharp.

You can also register for this event here along with all of the other cities and their events happening across Canada.

And don't forget, we're also doing a 1/2 day briefing for developers & architects on VS 2008 at the Paramount in Toronto on February 7th. You can view the details here.

Toronto Architect Forum, this Thursday

This coming Thursday, Microsoft is hosting the annual Toronto Architect Forum at their offices in Mississauga. The target audience is architects that are *not* in the financial service industry. Here's the agenda:

8:00 - 8:30 am Breakfast and Registration
8:30 - 9:00 am Welcome by Mark Relph
9:00 - 9:30 am Architectural Agility as Business Value, Dave Remmer
9:30 - 10:30 am Office Business Applications, Mike Walker
10:30 - 10:45 am Break
10:45 - 12:00 pm Visual Studio 2008 “All Up”, Adam Gallant
12:00 - 1:00 pm Networking lunch
1:00 - 2:15 pm Architectural Implications of LINQ, Barry Gervin
2:15 - 2:30 pm Break
2:30 - 3:00 pm Project Experiences using AJAX, Amalan Ponnampalam
3:00 - 4:15 pm How to be an Effective Architect, Mohammad Akif
4:15 - 4:30 pm Wrap-up and Prize Draw

As you can, I've secured the ever so popular "right after lunch" time slot. I don't know if there are detailed abstracts online for each session, but here is mine:

LINQ: Architectural Implications

Support for Language Integrated Query in the .NET 3.5 Framework promises to simplify and unify querying operations across object collections, relational data, DataSets and XML. The opportunity to simplify or even eliminate the notion  of a data access layer is one many architects are considering. During this session we will quickly introduce the capabilities of LINQ, LINQ to SQL and the upcoming Entity Framework, and then discuss how this may affect the design of our data access logic moving forward.

Registration is still open - here.

Update - the event is for Architects not in the financial services industry (my mistake).

Ignoring Static Analysis Warnings, Centrally

via Soma

Back in the days of fxCop, (before we had to pay for code analysis in Team Developer) if you didn't like an error/warning, you could have your request to ignore said message in an external central file.

With the advent of Visual Studio Team Editions for Developers 2005, suppressions were stored as attributes in front of blocks of code. Look on the bright side we were told - now you could see your suppressions inline with your code, versioned alongside, etc. But we lost some things with this as well. Now if you were doing major code sweeps adding suppressions, you would be touching lots of files, creating some unnecessary code churn.

Another scenario may involve a given developer doing a sweep for localization, or security, compliance, etc. In fxCop, different developers could have different settings & suppression files.

Well, Visual Studio 2008 to the rescue. Code Analysis will now give us back this capability.

One of the little pet projects I'm working on is to take a code analysis pass, and cross reference that with a change set. The goal is to generate a report of obvious warnings related to the code I'm churning. We can't be perfect here as sometimes code analysis will return an error to a line of code you didn't change, but it is indirectly caused by that. The only way to truly get a clean report would be to do a code analysis before & after the checkin - that might be too much. But this would definitely be a great report for a build - to compare code analysis passes on previous builds.

Migrating ASP.NET 1.x sites to ASP.NET 2.0

One of the questions we got during the Q&A of the Ottawa VS Launch yesterday was around problems in migrating ASP.NET applications from 2002/2003 to 2005.

The Web Platform Team has put together a nice step-by-step guide that covers some best practices to ensure a successful migration effort which should take you “the better part of a day” according to them. http://msdn.microsoft.com/asp.net/default.aspx?pull=/library/en-us/dnaspp/html/webprojectsvs05.asp

Hello 2.0

This past week we saw the final bits of SQL Server 2005 and Visual Studio 2005 get shipped up to MSDN Subscriber Downloads. Next week we'll see the official launch of these same products to the rest of the world, ushered in with a rolling thunder of launch events and parties stretching into the rest of the month and beyond. Microsoft does a great job of fostering community with events like this.

Technically there is a lot to like about the updates to the platform and I share most of Joel's top picks. I've been building applications, consulting and teaching developers on this platform for 4 years now and it feels quite legacy, if not common place, to me now. However, in many peoples' eyes, this becomes a critical moment in time: .NET is no longer a 1.0 product. Of course I'm speaking about groups who are not developing anything significant in .NET today, and with this maturity milestone, allows them into this “new“ world.

We've been watching the adoption and market maturity of .NET closely for the past few years, and a bit to my surprise I'm starting to see a lot of groups come to .NET for the very first time with 2.0.

This coming Tuesday I have the great pleasure of being involved in the ushering in of this new era at the Toronto launch where we are expecting between 3,000 and 4,000 developers and IT professionals come together. Early statistics are showing that somewhere between 35-50% of these folks are new to .NET. Similar events are taking place all over the world during this week and stretching out into December and beyond. For Canada, Toronto is just the first stop in a long list of cities from coast to coast. Personally, I'll be presenting at Toronto, Ottawa, Vancouver, Montreal, Quebec City and Halifax.

The overwhelming registration statics tics in all cities tells me two things: Firstly that .NET 2.0 is going to be adopted very quickly. Secondly, and more importantly, is that the software development industry in Canada is vibrantly growing and that indeed....Software Matters!

Consider two things:

  • Software costs a lot of money to design, build, test and deploy. Much more than it should.
  • Software projects fail at an alarming rate. Failure can be defined as any of the following: Late, Over Budget, Under Functionality, Buggy, Doesn't meet requirements.

Yet despite these two glaring issues, the business value of software is so compelling, that people are willing to keep investing in building software at increasing rates.

And then there is Visual Studio and SQL Server 2005:

  • One of ASP.NET 2.0's design goals was to reduce the number of lines of code in a typical application by over 50%.
  • SQL Server 2005 has been enhanced to be more reliable and secure, while at the same time bringing the 4GL productivity associated with C#, VB.NET and the .NET Framework into the database engine itself.
  • Visual Studio Team System 2005 was built from the ground up to help project's stay on track by integrating developers, architects, testers, project managers and other stakeholders into a common extensible repository known as Team Foundation Server.

Coincidence? I hope not ;)

Microsoft LINQ Resources for September 20th

Community Thoughts

  • Werner Moise has been “reflecting” on LINQ and the more time he spends, the more he's realizing “how well thought out and practical it is”. He has some excellent points.
  • Dinesh wants to know how you feel about attributed-based O/R mapping vs. external XML Files. He also talks about the connected vs. disconnected nature of DLinq. Also, here is the code from his DLinq demo at PDC.
  • Jomo Fisher shows how to create a custom aggregate function in LINQ using extension methods. Sweet, but it don't work for DLinq, but maybe then you want to look at User Defined Types and Aggregates in Yukon/SQL Server 2005. Oh man, this is going to take some architectural distillation.
  • Jon Galloway is scared by code maintainability problems introduced by DLinq. Jon and I share a common PowerBuilder background, where you could put SQL right inside your PowerScript (not unlike Progress either). This is not really a DLinq problem, but a code separation, high cohesion-loose coupling problem. The answer back in the PowerBuilder day was to put all your data access in datawindows. The .NET answer today would be componentized DataAdapters or DataReaders, but we still see the bad practice of people intermingling data access code in other areas of their application. But perhaps maybe DLinq does make this a slippery slope. Maybe we should all just stick to TableAdapters - yikes!

Resources

Videos

Audio

Newsgroups:

Articles

What am I missing? Drop me a line on this blog. I'm heading out next week to a VB Software Design Review and the MVP Summit in Redmond, just because there isn't enough new technology in my life these days.

More on LINQ, XLinq and perspectives.

On XLinq

A lot of the questions about XLinq are around how it will sit beside/replace XQuery (and other XML query/transform techniques). Back when I first started doing some .NET 2.0 training in February, I was a bit miffed that XQuery had not made it into the .NET Framework. Now don't get me wrong, it's not the most elegant thing in the world, but the excuse that “it's not a standard YET, so we can't put it in the framework“ seemed a little insincere given that the SQL team had managed to jump over that hurdle. But given LINQ/XLinq, maybe this now makes sense. We'll have to get our feet wet and see how LINQ evolves over time into a shipping product.

  • Michale Rys gives his thoughts on the relation between XLinq and XQuery. Interestingly he points out that XLinq uses about 30% less memory than the DOM.
  • Mike Champion's thoughts also comparing XLinq to the DOM.
  • Soumitra Sengupta also adds his 2 cents on how XLinq is positioned with the rest of the XML processing technologies.
  • Dave Remy gives some also talks about how XLinq's implementation was from the ground up, but inspired by lessons learned on the back of the DOM.

Other perspectives floating around....

  • On an unrelated note, M. David Peterson has an interesting post from one of his colleagues, with specific mention of comparisons between LINQ, and Haskell.
  • Kent Tegels also shares his first impressions.
  • Paul Wilson has a critical review of DLinq. He rightly questions the “sqlserver only“ aspect of DLinq. I'm hopeful that this is only a temporal condition. Paul also mentions, among other things, poor support for stored procedures in DLinq, but at this point, I'm not convinced that is all too important. I have to think about that some more.
  • Werner Moise has some in depth thoughts.

Some Articles...

  • Infoworld has an interview with Anders.
  • Scott Swigart has an article on VB 9 in Dr. Dobb's with interviews with Paul Vick, Amanda Silver, Erik Meijer, Rob Copeland, Alan Griver, and Jay Roxe.

And if you are at PDC and want to get more information, Don Box is hosting a LINQ Panel discussion tomorrow and he is looking for your questions in his blog comments.

What is the world saying about Microsoft's C# and VB LINQ Project

I have to say that the LINQ syntax in VB hits much closer to the mark than C#. More on that later. What is everybody else saying out there about LINQ?

  • Erik Meijer says that VB has become his “programming language of choice.“
  • Sam Gentile “LINQ is freaking cool“, but he's feeling the beta pain because the VB and C# tech previews work with Beta 2 of 2005 and he just installed the VSTS release candidate.
  • Rédo believes LINQ “will represent a tectonic shift in the way that VB programmers will work with data“.
  • Somasegar believes that LINQ is a signifiicant developer productivity enhancement and he wants to hear your feedback.
  • Sean Chase points out some interesting ideas using lambda expressions with LINQ.
  • Frans Bouma compares DLinq to O/R mappers and points out the negative side of attribute based mapping for use in cross db platform support. There is a good follow up discussion in the comments on that post.
  • Marius Gheorghe likes the LINQ idea, but not so much the implementatio and seems to agree with Frans.
  • OrangeVolt hopes that Sun will adapt this for Java.
  • Ben Galbraith gives his comments on LINQ from a Java perspective. In particular, he's happy to see the type inference feature added to C# and wishes Java could do the same. The comments also contain some interesting discussion on Java and .NET.
  • Over at the SPS Weblog, LINQ is inspiring a Visual FoxPro Object-Oriented SQL. Don't get crazy, he's only got 1 hour of development under his belt.

There is also an interview with Anders Hejlsberg and Paul Vick worth reading. Why does Paul look so much happier than Anders in these photos? No doubt it is the VB syntax simplicity :)