Showing posts from 2010

Know Your Data

Knowing your data might be the most important quality of the new database administrator (DBA). Just like a marketing person needs to know their customers, a CEO needs to know their market; a DBA needs to truly understand their data. This article is about how knowing your data becomes even more important with cloud databases like SQL Azure Database , and NoSQL style data stores like Cassandra and Windows Azure Storage . SQL Server Using a scale up approach for your on-premise SQL Server database, the SQL Server DBA needed: A good working understanding behind the relationships of the data, i.e. how tables related to each other. This allowed the DBA to develop foreign key to primary key relationships, one of the mantras of a relational database. Understanding how data was being inserted and updated into the table allowed the DBA to optimize their queries for locking, partitions for performance, and transaction logs for growth. Understanding how data was queried from th

Scale Out Your SQL Azure Database

  Scaling up an on-premise SQL Server is the concept of adding more computing resources to your existing hardware, like memory, processing power or storage. The purpose is to allow your SQL Server to handle more queries, gain performance or store more data. When you do decided to go to the cloud you should realize that SQL Azure doesn’t scale up like SQL Server – it scales out. Scaling out is adding additional machines with the same data and dividing the computation between them. This article will discuss the differences between scaling up and scaling out and how you need to plan for scale out on SQL Azure to gain performance. Due to hardware and licensing costs of SQL Server, it is much easier to add resources to existing hardware, than it is to deploy an additional replicated SQL Server on new hardware to gain performance.   The problem with scaling out (adding more machines) in an on-premise SQL Server installation is that this adds a degree of complexity to the design that

WP7 Windows Phone 7 Resources for Developers

This is a catchall for any kind of Windows Phone 7 resource for developers. If you have a favorite resource you would like added to the list, add it to the comments and I will update the list. MSFT Blogs Windows Phone Developer Blog Jesse Liberty, Silverlight Mike Ormond, UK David Anson Chris Koenig Jaime Rodriguez Jeff Wilcox Pete Brown Scott Guthrie Shawn Oster James Conard John Papa MSFT Sites Windows Phone developer resources (main landing page) Silverlight for Windows Phone XNA Game Studio and XNA Framework Windows Phone Developer Tools and Device Unlock User Experience and User Interface Application and Execution Model Input, Touch and Gestures Launchers and Choosers Security

Have we forgotten the sport of development?

There is a long standing pattern with sport teams of all types: The Team Members The Team Captain The Team Coach The Team Manager This pattern is well established, and it is well accepted that one bad apple in the above can change a champion team into a loser. Development should learn from this pattern. The following appear to be appropriate mappings by functions and roles served: Team Captain –> Dev Lead. The best member with leadership ability. The person that the team members have confidence in and respects. Someone good in the sport (development) Team Coach –> Architect. A coach plans out and teaches the plays. He makes the call on what should happen on the field. Team Manager –> The Development Manager. He is responsible for getting resources (including new people),  dealing with the sponsors etc. My general observation is that most development teams are crippling themselves by trying to save money by overloading positions. The conseque

Running Multiple Threads on Windows Azure

The default worker role created by Visual Studio 2010 generates code that only leverages a single thread in your compute instance. Using that generated code, if you make synchronous network requests to SQL Azure or to a web service (for example via REST), your dedicated core for the instance becomes underutilized while it waits for the response from the network. One technique is to use the asynchronous functions in ADO.NET and the HTTPWebRequest classes to offload the work to the background worker. For more information about asynchronous calls read: Asynchronous Programming Design Patterns . Another technique that I will cover in this blog post is how to start up multiple threads, each for a dedicated task, for this purpose I have coded a multi-threaded framework to use in your worker role.   Goals of the framework: Remain true to the design of the RoleEntryPoint class, the main class called by the Windows Azure instance, so that you don’t have to redesign your code. G

How to Combine a Worker and Web Role in Windows Azure

Some of us are running really small web sites on Windows Azure, and for every web site it seems that I needed to do a little background processing. Usually, this means creating both a web role to handle the web requests and a worker role to the background processing – typically through separate Visual Studio projects. However for a small web site this can get expensive if you are not fully utilizing the resources of both instances.   This article is going to show you how to include your background processing into your web role so you can fully utilize the Windows Azure instance. The same background processing that you would typically do in a worker role.   The recommended approach for Windows Azure is always run two instances to maximize uptime – if you were to follow the recommendation you would have the web role plus the extra background work running twice. Which means you need to design your background work to run Idempotency. For more information read: Idempotency for Windo

Deleting an Email Account on the Windows Phone 7

I don’t think that it is completely obvious how to delete an email account on the Windows Phone 7, so I am writing this quick blog post to describe how to do it. Click on the Windows button to get to the start screen (with the tiles) Swipe right to get to the applications list Swipe down until you find the settings icon. Tap on Settings to enter settings. Choose email & accounts Find the email account that you want to delete. Press down on the email account you want to delete until the secondary menu appears. There will be an option to delete , tap it. {6230289B-5BEE-409e-932A-2F01FA407A92}

A Smart Overloading of a Stored Procedure Call by Mining COALesce.

Often you have a table where different columns needs to be updated by different calls.  Traditionally, you wrote a stored procedure for every variation which can result in a mountain of code (increases your LOC/hr but drives maintenance cost up more!)   IF you can eliminate NULL as being a valid value (use ‘’ instead?) then you can reduce everything to a single stored procedure as illustrated below.   Create proc harvest.P_UpsertEvent @ID uniqueidentifier = null , @ParentID uniqueidentifier = null , @ConsolidationSequenceNo bigint = null , @Level smallint = null , @EventFulId varchar ( 50 ) = null , @VenueID uniqueidentifier = null , @Title nvarchar ( 120 ) = null , @Text nvarchar ( max ) = null , @Html nvarchar ( max ) = null , @StartAt datetime = null , @EndAt datetime = null , @ExtendedXml xml = null AS If Exists (

Sql Upgrade Strategies

In my prior post I described how you can identify the different between a prior production database and a developer’s evolution of the database. You could make the developer responsible for developing the scripts but that is not always the best use of such a resource (do you make the developer responsible for writing all of the automated unit tests for their code and thus do not need testing resources? The appropriate best use of a developer issue is similar). Whether the developer’s does it, the database developer does it or a contracted resource does it – you end up with a batch of TSQL for each ‘upgrade’ (there may be dozens of developer’s upgrades in a product release cycle). This post looks at the management issue.   We have the TSQL to go from Prod –> Dev We want to apply it to some other Prod system   The basic pattern is a series of update files and a console application that reads the TSQL in , configuration information and then apply the information. The fi

Startups and Sql Server Databases

Often a startup tosses out a database to get a web site up or product out of the door. This is reasonable when there is neither the time(-to-market) nor the money (for SQL Data Modelers and developers).   For a web site there is usually one of two patterns of evolution: Use the production database for development. There’s no problem of matching database to next generation code base. Side-effects can be horrible from miscoding or security breaches. Use a clone of the production database and then evolve it with the next generation code. For a product database, 2. above is the typical option. We will consider this type of option alone in this post. The second pattern we need to look at is a non-issue for a one-man development shop, but arises with there are multiple developers: A single shared development database Any problems impacts the entire development team Each developer has their own development database Each developer

Finding out what changed between two versions of a SQL Server Database

This is just a quick note showing how you can compare the structure of two databases  easily. These queries will spell out what (and their types) -- One way Select name,type from MyDataBase_dev.sys.objects EXCEPT Select name,type from MyDataBase.sys.objects -- reverse direction Select name,type from MyDataBase.sys.objects EXCEPT Select name,type from MyDataBase_dev.sys.objects The next item to check is usually columns, which follows a similar pattern:   Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns Select Table_Name,Column_name from MyDataBase_Dev.Information_Schema.Columns EXCEPT Select Table_Name,Column_name from MyDataBase.Information_Schema.Columns Many developers do not know about the very useful EXCEPT operator which excludes from one SELECT all matches in another SELECT. You can do this for the rest of the system catalogs (ju

Writing Web Pages for a Client That You Can’t Test

Every time I get a C# exception on my ASP.NET web site running in Windows Azure, I have my web site code send me an email; this email includes the call stack, path information, and user agent string. I can use these email to find the problems with the code that caused the error. For a long time I have been ignoring the email (about 50 a day) and letting them stack up – being too busy with other projects. Lately, I have resolved to clean up all the code, so that I get less email and reduce the bugs in the web site. I thought this would lead to a better user experience for people visiting the site.   My typical process is to read the call stack, look at the path information and try to reproduce the issue to gain insight into how I have to fix it. Basically, a debugging process that I have in my toolkit no matter what the environment I am programming. To get started I make sure that I am running the same browser that the user was using, be that Firefox, Internet Explorer or Google C

The sales pitch to developers …

Last night I attended the local Linux Group meeting with a presentation on  a MS Access/OO.Base to Drupal presentation described as:   “Most people think of drupal as a website framework system. However, it can also serve effectively as a replacement to the forms/reports/tables system utilized by access and base. (as well as many other things, but we won't discuss that formally tonight) No prior knowledge of Drupal is required, and for those who do understand drupal, we will go through using cck and views, as well as a few other modules to develop a replacement for a small access database.” Since this is a Windows Blog, it appears to be off target – however since it’s a emotionally-detached example to illustrate what is also seen with Windows stuff. As a FYI, I grew up on CP/M and ran Mark Williams Unix ( Coherent ) in the mid 1980’s, we have three Linux boxes in the house.   The first  thing that I struck me during the talk was that the speaker was laying smoke (as