July, 2011

  • Canadian Solution Developers' Blog

    SQL Azure Federations (Because you have a lot of data)

    • 2 Comments

    SQL-Azure_rgbLast week I explained SQL Azure Essentials for the Database Developer where I covered some of the differences between working in SQL Azure instead of an on-premise database from the perspective of a SQL Server database developer. Today I will continue in that vein and explain one of the upcoming features for SQL Azure: Federations.

    When you first create a SQL Azure database, you are asked if you want to create a Web or a Business Database. A web database can have a maximum size of 1GB or 5 GB.  A Business database can be up to 50 GB. But in this day and age 50 GB is not that big. There must be a way to use SQL Azure for databases larger than 50 GB! One of the great benefits of cloud computing is the ability to handle peak loads and peak volumes by increasing your usage as needed. We can do that with Windows Azure using the pay as you go model, but how do we get that same sort of flexibility for our database in SQL Azure? The answer is federations! With federations you can expand and contract the number of nodes that service the database. Your application can scale to have 10 to 100s of SQL Azure databases and provide you with data storage beyond the limits of a single SQL Azure database.

    The concept is very similar to partitioned tables. When you set up a partitioned table in SQL Server, you choose one of the columns on the table to use to partition the data into different data files. For example you could partition an order table based on country id. All orders with country ids of 1 or 2 go into partition A, all orders with country ids between 3 and 9 go into partition B, all orders with country ids from 10 to 20 go into partition C and so on.

    You would end up with something like this

    OrderId CustomerId OrderTotal OrderDate CountryId Country Partition
    100 402 500.00 1-Jan-2010 1 Canada A
    101 251 300.25 1-Jan-2010 1 Canada A
    102 406 199.99 1-Jan-2010 5 England B
    103 406 50.00 1-Jan-2010 5 England B
    104 397 75.00 1-Jan-2010 6 France B
    105 216 50.00 1-Jan-2010 2 USA A
    106 411 19.99 1-Jan-2010 10 China C
    107 203 315.00 2-Jan-2010 2 USA B
    108 87 100.00 2-Jan-2010 10 China C

    We usually partition a table in a on-premise SQL Server database to speed up queries (since they may only need to search through one partition and data file depending on the query), or to provide more backup options since you can back up one partition at a time. We can also merge two partitions together or split a single partition into two partitions using the SPLIT and MERGE statements.

    In SQL Azure, you create one or more Federations in your database. A federation represents all the data being partitioned and contains one or more federation members or shards (The federation member is like a partition in a partitioned table.) The federation members are stored across different nodes.

    So if we had a table called Orders in SQL Azure and we wanted to federate that table we would do the following

    1. Create a federation in our SQL Azure database and specify we want to be able to divide up our records based on a range of integer values

    CREATE FEDERATION order_federation (c_id RANGE BIGINT)

    2. Create the tables we want to partition in our federation. For each table we must specify which column contains the integer values we should use to divide up the records across federation members

    USE FEDERATION order_federation

    CREATE TABLE orders (orderid INT NOT NULL, country_id BIGINT NOT NULL, …) FEDERATED ON (c_id = country_id)

    3. Split our federation into federation members

    ALTER FEDERATION order_federation SPLIT AT (c_id=3)

    ALTER FEDERATION order_federation SPLIT AT (c_id=10)

    This will split all records with a country id of 1 or 2 into one federation member, and all records with a country id of 3 to 9 into a second federation member and all records with a country id of 10 or higher into a third federation. You can continue to use split commands to create as many federation members as you want.

    All the data is still available as you split and merge federation members!

    Now what’s truly powerful about this concept on SQL Azure is that the federation scales out across multiple nodes which gives your virtually unlimited scalability in your application. You can build applications that scale from 10s to 100s of SQL Azure databases. You can also change this on demand as needed because you can repartition without downtime.

    Federation Technology will be available in the final quarter of calendar year 2011 in all SQL Azure geographies.

    Todays My 5

    5 Places to learn more about SQL Azure federations

    1. SQL Azure Federations: Building Scalable, Elastic, and Multi-tenant Database Solutions
    2. Cihan Biyikoglu’s Blog post Building Scalable Database Solution with SQL Azure – Introducing Federation in SQL Azure
    3. Recording from PDC Building Scale-Out Database Solutions on SQL Azure
    4. Recording from TechEd 2011 Building Scalable Database Solutions Using Microsoft SQL Azure Database Federations
    5. Building Scalable Database Solutions Using SQL Azure – Scale-out techniques such as Sharding or Horizontal Partitioning
  • Canadian Solution Developers' Blog

    Free Training on .NET Framework 4 and Visual Studio 2010 on YOUR schedule

    • 1 Comments

    VisualStudioBoxThe Visual Studio Team has released a new Visual Studio 2010 and .NET Framework 4 Training Course. You can complete it online, or if you prefer you can download the training kit and work offline. For those who don’t have the training budget to take an in person course, these training kits are a fantastic resource! This course is designed for people who have already been playing with .NET but want to get up and running with the new features in .NET 4 and Visual Studio 2010.

    The course includes presentations, hands-on labs, and demos. Whether you develop on windows or web, with VB or C#, there is material here to interest you. You will get a chance to look at C# 4, Parallel Extensions, Silverlight 4, ASP.NET 4, enhancements to the data platform, WCF, and Workflow updates, the Application Lifecycle Management tools in Visual Studio, Windows Azure, and more!

    You do need Visual Studio 2010 in order to complete the labs. If you don’t have it yet you can download a full trial version.

    Since we are in the planning stages for TechDays Canada 2011, I thought I would use TechDays as the inspiration for this week’s My 5.

    5 Great Sessions from TechDays 2010 you can watch to learn more about .NET Framework 4 and Visual Studio 2010

    1. Getting Your Return on Investment with Microsoft .NET Framework 4 – this is an overview session of the new features in .NET Framework 4 across the different technologies
    2. Microsoft Visual Studio 2010 Tips and Tricks – For anyone who works with Visual Studio, pick up a few tricks to impress your boss and co-workers.
    3. New IDE and Language Features in Microsoft Visual Studio 2010 Using Visual Basic and C# – this session is focused on the new features in the programming languages themselves.
    4. Web Load Testing with Microsoft Visual Studio 2010 – Learn how to use VS2010 to make it easier to do your web load testing
    5. Microsoft Visual Studio 2010 for Web Development – improve your web deployment process
  • Canadian Solution Developers' Blog

    How to Get Consensus in a Meeting with the “Fist of Five”

    • 1 Comments

    FistHave you ever been in a meeting and asked “Is everyone okay with this plan?” only to be answered with silence. You prompt again “any concerns or questions"?” again nothing. Finally you announce that you are going to assume silence means consent and move on to the next topic. But here is the big question: Is silence consent? 

    In order to answer that question, think about what happens after the meeting where you assumed silence was consent. After the meeting, did one of the team members turn to another and start pointing out the flaws in the plan? In the coming days and weeks did anyone keep bringing up that same topic again because of additional concerns? In the worst case scenarios when you go ahead with the plan and it does not work. Is there someone who stands up at the Follow Up meeting and says “I knew this plan would never work”? Those are all signs that the team did not reach a consensus.

    The Merriam Webster defines consensus as “general agreement” and “group solidarity in sentiment and belief”. It’s that second definition group solidarity you want to reach with your team.  Group solidarity means we all agree to support this decision or plan going forward. That means we won’t walk out of the room and start telling everyone this is a bad idea, and we don’t think it will work. It means that even if it fails you will stand up and say you agreed it was worth trying.

    Consensus is not a group vote where majority rules. It is the entire group agreeing to support a decision or idea. There is a very simple technique you can use check for consensus: The Fist of Five.

    When you are ready to ask “Is everyone okay with this plan”, each team member responds by raising their fist with one to five fingers.

    • Five fingers – You think this is a very good plan and you fully support it
    • Four Fingers – You support this plan, it’s not perfect, but you strongly support it
    • Three fingers – This plan may not be your first choice, you have some concerns about it, but you understand the arguments presented in the meeting and you agree that given the current circumstances it is a reasonable plan moving forward and you support it
    • Two fingers – There is an issue you feel must be resolved before you can support the plan, further discussion or follow up is required before you can support it
    • One finger – You do not support this proposal, you do not think it will work, it is going to take some serious convincing to get you to change your mind

    If everyone is showing 3 or more fingers then you have consensus. If anyone is showing less than 3 fingers ask them to explain their concern and develop a plan to address and follow up on that concern.

    It’s simple, and it works. In fact we had a team meeting this week where we were putting this technique to good use as we discussed how to work with user communities, developers and IT Professionals across Canada. Besides what could be better than a meeting technique that sounds like the title of a Kung Fu movie!

    All those in favour?

    FistFive

  • Canadian Solution Developers' Blog

    SQL Azure Essentials for the Database Developer

    • 0 Comments

     

    I admit it, I am a SQL geek. I really appreciate a well designed database. I believe a good index strategy is a thing of beauty and a well written stored procedure is something to show off to your friends and co-workers. What I, personally, do not enjoy, is all the administrative stuff that goes with it. Backup & recovery, clustering, installation are all important but, it’s just not my thing. I am first and foremost a developer. That’s why I love SQL Azure. I can jump right in to the fun stuff: designing my tables, writing stored procedures and writing code to connect to my awesome new database, and I don’t have to deal with planning for redundancy in case of disk failures, and keeping up with security patches.

    There are lots of great videos out there to explain the basics: What is SQL AzureCreating a SQL Azure Database. In fact there is an entire training kit to help you out when you have some time to sit down and learn. I’ll be providing a few posts over the coming weeks to talk about SQL Azure features and tools for database developers. What I’d like to do today is jump right in and talk about some very specific things an experienced database developer should be aware of when working with SQL Azure.

    You can connect to SQL Azure using ANY client with a supported connection library such as ADO.NET or ODBC

    This could include an application written in Java or PHP. Connecting to SQL Azure with OLEDB is NOT supported right now. SQL Azure supports tabular data stream (TDS) version 7.3 or later. There is a JDBC driver you can download to connect to SQL Azure. Brian Swan has also written a post on how to get started with PHP and SQL Azure. .NET Framework Data Provider for SQLServer (System.Data.SqlClient) from .NET Framework 3.5 Service Pack 1 or later can be used to connect to SQL Azure and the Entity Framework from .NET Framework 3.5 Service Pack 1 or later can also be used with SQL Azure.

    You can use SQL Server Management Studio (SSMS) to connect to SQL Azure

    In many introduction videos for SQL Azure they spend all their time using the SQL Azure tools. That is great for the small companies or folks building a database for their photography company who may not have a SQL Server installation. But for those of us who do have SQL Server Management Studio, you can use it to manage your database in SQL Azure. When you create the server in SQL Azure, you will be given a Fully Qualified DNS Name. Use that as your Server name when you connect in SSMS. For those of you in the habit of using Server Explorer in Visual Studio to work with the database, Visual Studio 2010 allows you to connect to a SQL Azure database through Server Explorer.

    imageimage

     

    The System databases have changed

    • Your tempdb is hiding – Surprise, no tempdb listed under system databases. That doesn’t mean it’s not there. You are running on a server managed by someone else, so you don’t manage tempdb. Your session can use up to 5 GB of tempdb space, if a session uses more than 5GB of space in tempdb it will be terminated with error code 40551.
    • The master database has changed – When you work with SQL Azure, there are some system views that you simply do not need because they provide information about aspects of the database you no longer manage. For example there is no sys.backup_devices view because you don’t need to do backups (if you are really paranoid about data loss, and I know some of us are, there are ways to make copies of your data). On the other hand there are additional system views to help you manage aspects you only need to think about in the cloud. For example sys.firewall_rules is only available in SQL Azure because you define firewall rules for each SQL Azure server but you wouldn’t do that for a particular instance of SQL Server on premise.
    • SQL Server Agent is NOT supported – Did you notice msdb is not listed in the system databases. There are 3rd party tools and community projects that address this issue. Check out SQL Azure Agent on Codeplex to see an example of how to create similar functionality. You can also run SQL Server Agent on your on-premise database and connect to a SQL Azure database.

    SystemDatabases

    You don’t know which server you will connect to when you execute a query

    When you create a database in SQL Azure there are actually 3 copies made of the database on different servers. This helps provide higher availability, failover and load balancing. Most of the time it doesn’t matter as long as we can request a connection to the database and read and write to our tables. However this architecture does have some ramifications:

    • No  4 part names for queries –  Since when you execute a query you do not know which server it will use, 4 part queries that specify the server name are not allowed.
    • No USE command or cross database queries – When you create two databases there is no guarantee that those two databases will be stored on the same physical server. That is why the USE command and cross database queries are not supported.

    Every database table must have a clustered index

    You can create a table without a clustered index, but you won’t be able to insert data into the table until you create the clustered index. This has never affected my database design because I always have a clustered index on my tables to speed up searches.

    Some Features are not currently supported

    • Integrated Security – SQL Server authentication is used for SQL Azure, which makes sense given you are managing the database but not the server.
    • No Full Text Searches – For now at least, full text searches are not supported by SQL Azure. If this is an issue for you, there is an interesting article in the TechNet Wiki on a .NET implementation of a full text search engine that can connect to SQL Azure.
    • CLR is not supported – You have access to .NET through Windows Azure, but you can’t use the .NET to define your own types and functions, but you can still create your own functions and types with T-SQL.

    You can connect to SQL Azure from your Business Intelligence Solutions

    • SQL Server Analysis Services - Starting with SQL Server 2008 R2 you can use SQL Azure as a data source when running SQL Server Analysis Services on-premise.
    • SQL Server Reporting Services – Starting with SQL Server 2008 R2, you can use SQL Azure as a data source when running SQL Server Reporting Services on-premise.
    • SQL Server Integration Services – You can use the ADO.NET Source and Destination components to connect to SQL Azure, and in SQL Server 2008 R2 there was a “Use Bulk Insert” option added to the Destination to improve SQL Azure performance.

    Today’s My 5 of course has to relate to SQL Azure!

    5 Steps to get started with SQL Azure

    1. Create a trial account and login
    2. Create a new SQL Azure server – choose Database | Create a new SQL Azure Server and choose your region (for Canada North Central US is the closest)
    3. Specify an Administrator account and password and don’t forget it! – some account names such as admin, administrator, and sa are not allowed as administrator account names
    4. Specify the firewall rules – these are the IP Addresses that are allowed to access your Database Server, I recommend selecting the “Allow other Windows Azure services to access this server” so you can use Windows Azure services to connect to your database.
    5. Create a Database and start playing – You can either create the database using T-SQL from SSMS, or using the Create New Database in the Windows azure Platform tool which gives you a wizard to create the database.

    Now you know the ins and outs, go try it out and come back next week to learn more about life as a database developer in SQL Azure

  • Canadian Solution Developers' Blog

    By The Way There Is No Documentation…

    • 0 Comments

    Have you ever been asked to make changes or fixes to someone else’s code? Do any of these scenarios sound familiar? A developer leaves the team and now you have to continue with their code. A consulting company develops an application and then your company decides to do the maintenance themselves to save money and now you have to maintain an application built by another team. An application developed years ago now needs updating with new functionality but all the people who originally worked on that application are now elsewhere in the company.

    Yup, that’s life as a developer. To be honest I prefer to just build a new application from scratch then try to decipher someone else’s code and ad or fix their functionality. It is not because the other programmers haven’t produced good code, it’s just the extra hurdle of learning how someone else has built an application. To help with this we traditionally add comments to our code and we create documentation that explains the application. Then we starting running short on time, and the users ask for last minute changes that are have to be included. So we put off updating the documentation and focus on heads down coding to meet our deadlines. After the application goes live, there are changes and fixes to the application as well. We open tickets and create change requests and if you add comments or records to keep track of which modules were updated and why. But is the original application documentation kept up to date? All too frequently the answer is no.

    So where does that leave a developer who inherits someone else’s code and needs to add an enhancement or bug fix. Well if you have Visual Studio Ultimate 2010, there is hope. When you open Visual Studio Ultimate you see a brand new menu option called Architecture. So when you open up that code in Visual Studio and you find yourself wondering how this application is designed, and you don’t want to spend hours opening up the different classes to see what is called where. Check it out!

    Here’s how it works, you open up the Solution containing the code you need to update. You choose Architecture | Generate Dependency Graph | By Assembly.

    ArchitectureMenuZoomIn

    Figure 1 Generating a Dependency Graph

    This will generate a graph that shows you each of your assemblies and assemblies are dependant on each other. This by itself is very useful when you are trying to determine the risk and potential impact of making a change to a particular assembly. It will also help you estimate the effort that will be required to test a change to a particular assembly because you start to get a sense of where the dependencies lie.

    DependencyGraphAssemblyZoomedIn

     

    Figure 2 Sample Dependency Graph By Assembly

    As you can see in the dependency graph shown in Figure 2 the Registration project references the Courses assembly. So I know I need to retest parts of the Registration project if I make any changes to the code in the courses assembly.

    If you want more information about the classes within the assemblies you can expand each assembly as shown in Figure 3

    ZoomInExpandedAssemblyDependency

     

    Figure 3 Expanded Assembly Dependency Graph

    Unfortunately this view will not show you exactly which class within the assembly references the other classes. If you want that you need to generate a dependency graph by class. Choose Architecture | Generate Dependency Graph | By Class and you get a diagram that shows you each of your classes and the classes they reference in their code.

    ClassDependencyGraphZoomIn

    Figure 4 Class Dependency Graph

    You can see in Figure 4, now we have an up to date view of all our classes and their dependencies. This can make it a lot easier to figure out where to find the code we need to update for a fix, or to determine the impact and effort that will be involved in a particular change.

    So there you have it, there are other reverse engineering tools out there, but life is simpler if you don’t have to keep launching separate applications to do everything, so make the most out of the tool you have. Visual Studio Rocks!

    Today’s My 5 –

    5 ways to learn more about Visual Studio 2010 Ultimate

    1. Download a free trial and play with it.
    2. Read the product description (it does wayyyy more than just let you write code!)
    3. Look at the Feature Comparison Chart so you can see what is available in each version of Visual Studio.
    4. Check out a video on the Lab Management feature, a little known gem in Visual Studio Ultimate.
    5. Check out Modelling projects, UML right in Visual Studio!

    And please share your own documentation success or horror stories from your life as a developer on LinkedIn or by commenting on this blog! I am sure I am not the only one to inherit code with out of date or non existent documentation Smile

  • Canadian Solution Developers' Blog

    The Microsoft Cloud Like You’ve Never Seen It Before

    • 0 Comments

    By now, you’ve heard a lot about the “Microsoft Cloud”. We’ve talked a lot about what is Windows Azure, the problems it solves, the business opportunities it presents, and even more so, about its capabilities, functions, APIs, and developer tools. But there’s one aspect of Windows Azure that has always been a mystery to most – what’s underneath the hood of Windows Azure?

    For this week’s Windows Azure Wednesday (#AzureWednesday), I thought I would share something that is completely not related to development but has crossed the mind of every developer at least once. You’ve probably asked yourself, “When I deploy an application to Windows Azure, where does it go?” or perhaps “What do Windows Azure data centres look like?”.

    The following video should be able to answer those questions for you as it takes you on a tour of a few of Microsoft’s data centres and shows you how Microsoft’s Global Foundation Services Team, the team that designs, builds, operates, and secures Microsoft’s Cloud infrastructure, delivers all of the things that Windows Azure promises its users:

    High Availability Reliability Smart Scalability
    High Efficiency Sustainability Secure and Trusted Cloud
     
    Microsoft GFS Datacenter Tour
     
     
    Dive Deeper
     
    The video in-and-of-itself is fascinating, but if, like me, you’re now intrigued even more. Dileep Bhandarkar, a distinguished engineer at Global Foundation Services dives deeper into the details of the data center. Read >>

    Share Your Thoughts

    What did you think of the video? Does the tour and the transparency that Microsoft is providing make you feel more comfortable about deploying your applications and data to the Cloud? Share your thoughts >>

Page 1 of 1 (6 items)