Contents tagged with SQL Server

  • Azure: New DocumentDB NoSQL Service, New Search Service, New SQL AlwaysOn VM Template, and more

    Today we released a major set of updates to Microsoft Azure. Today’s updates include:

    • DocumentDB: Preview of a New NoSQL Document Service for Azure
    • Search: Preview of a New Search-as-a-Service offering for Azure
    • Virtual Machines: Portal support for SQL Server AlwaysOn + community-driven VMs
    • Web Sites: Support for Web Jobs and Web Site processes in the Preview Portal
    • Azure Insights: General Availability of Microsoft Azure Monitoring Services Management Library
    • API Management: Support for API Management REST APIs

    All of these improvements are now available to use immediately (note that some features are still in preview).  Below are more details about them:

    DocumentDB: Announcing a New NoSQL Document Service for Azure

    I’m excited to announce the preview of our new DocumentDB service - a NoSQL document database service designed for scalable and high performance modern applications.  DocumentDB is delivered as a fully managed service (meaning you don’t have to manage any infrastructure or VMs yourself) with an enterprise grade SLA.

    As a NoSQL store, DocumentDB is truly schema-free. It allows you to store and query any JSON document, regardless of schema. The service provides built-in automatic indexing support – which means you can write JSON documents to the store and immediately query them using a familiar document oriented SQL query grammar. You can optionally extend the query grammar to perform service side evaluation of user defined functions (UDFs) written in server-side JavaScript as well. 

    DocumentDB is designed to linearly scale to meet the needs of your application. The DocumentDB service is purchased in capacity units, each offering a reservation of high performance storage and dedicated performance throughput. Capacity units can be easily added or removed via the Azure portal or REST based management API based on your scale needs. This allows you to elastically scale databases in fine grained increments with predictable performance and no application downtime simply by increasing or decreasing capacity units.

    Over the last year, we have used DocumentDB internally within Microsoft for several high-profile services.  We now have DocumentDB databases that are each 100s of TBs in size, each processing millions of complex DocumentDB queries per day, with predictable performance of low single digit ms latency.  DocumentDB provides a great way to scale applications and solutions like this to an incredible size.

    DocumentDB also enables you to tune performance further by customizing the index policies and consistency levels you want for a particular application or scenario, making it an incredibly flexible and powerful data service for your applications.   For queries and read operations, DocumentDB offers four distinct consistency levels - Strong, Bounded Staleness, Session, and Eventual. These consistency levels allow you to make sound tradeoffs between consistency and performance. Each consistency level is backed by a predictable performance level ensuring you can achieve reliable results for your application.

    DocumentDB has made a significant bet on ubiquitous formats like JSON, HTTP and REST – which makes it easy to start taking advantage of from any Web or Mobile applications.  With today’s release we are also distributing .NET, Node.js, JavaScript and Python SDKs.  The service can also be accessed through RESTful HTTP interfaces and is simple to manage through the Azure preview portal.

    Provisioning a DocumentDB account

    To get started with DocumentDB you provision a new database account. To do this, use the new Azure Preview Portal (http://portal.azure.com), click the Azure gallery and select the Data, storage, cache + backup category, and locate the DocumentDB gallery item.

    image

    Once you select the DocumentDB item, choose the Create command to bring up the Create blade for it.

  • Azure: Virtual Machine, Machine Learning, IoT Event Ingestion, Mobile, SQL, Redis, SDK Improvements

    This past month we’ve released a number of great enhancements to Microsoft Azure.  These include:

    • Virtual Machines: Preview Portal Support as well as SharePoint Farm Creation
    • Machine Learning: Public preview of the new Azure Machine Learning service
    • Event Hub: Public preview of new Azure Event Ingestion Service
    • Mobile Services: General Availability of .NET support, SignalR support
    • Notification Hubs: Price Reductions and New Features
    • SQL Database: New Geo-Restore, Geo-Replication and Auditing support
    • Redis Cache: Larger Cache Sizes
    • Storage: Support for Zone Redundant Storage
    • SDK: Tons of great VS and SDK improvements

    All of these improvements are now available to use immediately (note that some features are still in preview).  Below are more details about them:

    Virtual Machines: Support in the new Azure Preview portal

    We previewed the new Azure Preview Portal at the //Build conference earlier this year.  It brings together all of your Azure resources in a single management portal, and makes it easy to build cloud applications on the Azure platform using our new Azure Resource Manager (which enables you to manage multiple Azure resources as a single application).  The initial preview of the portal supported Web Sites, SQL Databases, Storage, and Visual Studio Online resources.

    This past month we’ve extended the preview portal to also now support Virtual Machines.  You can create standalone VMs using the portal, or group multiple VMs (and PaaS services) together into a Resource Group and manage them as a single logical entity. You can use the preview portal to get deep insights into billing and monitoring of these resources, and customize the portal to view the data however you want.  If you are an existing Azure customer you can start using the new portal today: http://portal.azure.com.

    Below is a screen-shot of the new portal in action.  The service dashboard showing service/region health can be seen in the top-left of the portal, along with billing data about my subscriptions – both make it really easy for you to see the health and usage of your services in Azure.  In the screen-shot below I have a single VM running named “scottguvstest” – and clicking the tile for it displays a “blade” of additional details about it to the right – including integrated performance monitoring usage data:

    image

    The initial “blade” for a VM provides a summary view of common metrics about it.  You can click any of the titles to get even more detailed information as well. 

    For example, below I’ve clicked the CPU monitoring title in my VM, which brought up a Metric blade with even more details about CPU utilization over the last few days.  I’ve then clicked the “Add Alert” command within it to setup an automatic alert that will trigger (and send an email to me) any time the CPU of the VM goes above 95%:

  • Azure: 99.95% SQL Database SLA, 500 GB DB Size, Improved Performance Self-Service Restore, and Business Continuity

    Earlier this month at the Build conference, we announced a number of great new improvements coming to SQL Databases on Azure including: an improved 99.95% SLA, support for databases up to 500GB in size, self-service restore capability, and new Active Geo Replication support.  This 3 minute video shows a segment of my keynote where I walked through the new capabilities:

    image

    Last week we made these new capabilities available in preview form, and also introduced new SQL Database service tiers that make it easy to take advantage of them.

  • Windows Azure July Updates: SQL Database, Traffic Manager, AutoScale, Virtual Machines

    This morning we released some great updates to Windows Azure. These new enhancements include:

    • SQL Databases: Support for Automated SQL Export and a New Premium Tier SQL Database option
    • Traffic Manager: New support for managing Windows Azure Traffic Manager in the HTML Portal
    • AutoScale: Support for Windows Azure Mobile Services, AutoScale rules for Service Bus Queue Depth, Alerts on AutoScale actions
    • Virtual Machines: Updates to the IaaS management experiences in the Management Portal

    All of these improvements are now available to use immediately (note: some are still in preview).  Below are more details about them.

  • Entity Framework 6: Alpha2 Now Available

    The Entity Framework team recently announced the 2nd alpha release of EF6.   The alpha 2 package is available for download from NuGet. Since this is a pre-release package make sure to select “Include Prereleases” in the NuGet package manager, or execute the following from the package manager console to install it:

  • LINQ to SQL (Part 8 - Executing Custom SQL Expressions)

    Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

    Below are the first seven parts in this series:

    In my last two posts (Part 6 and Part 7) I demonstrated how you can optionally use database stored procedures (SPROCs) to query, insert, update and delete data using a LINQ to SQL data model. 

    One of the questions a few people have asked me since doing these posts has been "what if I want total control over the SQL expressions used by LINQ to SQL - but I don't want to use SPROCs to-do it?"  Today's blog post will cover that - and discuss how you can use custom SQL expressions that you provide to populate your LINQ to SQL data model classes, as well as perform insert, updates, and deletes.

    Using LINQ Query Expressions with LINQ to SQL

    For the purposes of this blog post, let's assume we've used the LINQ to SQL ORM designer in VS 2008 to define a set of data model classes like below for the Northwind database (note: read Part 2 of this series to learn how to use the LINQ to SQL ORM designer to do this):

    In Part 3 of this blog series I covered how you can use the new LINQ language support in VB and C# to query the above data model classes and return back a set of objects that represent the rows/columns in the database. 

    For example, we could add a "GetProductsByCategory" helper method to the DataContext class of our data model that uses a LINQ query to return back Product objects from the database:

    VB:

    C#:

    Once we've defined our encapsulated LINQ helper method, we can then write code like below that uses it to retrieve the products, and iterate over the results:

    VB:

     

    When the LINQ expression within our "GetProductsByCategory" method is evaluated, the LINQ to SQL ORM will automatically execute dynamic SQL to retrieve the Product data and populate the Product objects.  You can use the LINQ to SQL Debug Visualizer to see in the debugger how this LINQ expression is ultimately evaluated.

    Using Custom SQL Queries with LINQ to SQL

    In our sample above we didn't have to write any SQL code to query the database and retrieve back strongly-typed Product objects.  Instead, the LINQ to SQL ORM automatically translated the LINQ expression to SQL for us and evaluated it against the database. 

    But what if we wanted total control over the SQL that is run against our database, and don't want LINQ to SQL to-do it for us in this scenario?  One way to accomplish this would be to use a SPROC like I discussed in Part 6 and Part 7 of this series.  The other approach is to use the "ExecuteQuery" helper method on the DataContext base class and use a custom SQL expression that we provide.

    Using the ExecuteQuery Method

    The ExecuteQuery method takes a SQL query expression as an argument, along with a set of parameter values that we can use to optionally substitute values into the query.  Using it we can execute any raw SQL we want against the database (including custom JOINs across multiple tables).

    What makes the ExecuteQuery method really useful is that it allows you to specify how you want the return values of your SQL expression to be typed.  You can do this either by passing a type-object as a parameter to the method, or by using a generic-based version of the method. 

    For example, we could change the GetProductsByCategory() helper method we created earlier - using a LINQ expression - to instead use the ExecuteQuery method to execute our own raw SQL expression against the database and return "Product" objects as a result:

    VB:

    C#:

    We can then call the GetProductsByCategory() helper method using the exact same code as before:

    But unlike before it will be our custom SQL expression that will run against the database - and not dynamic SQL executed in response to using a LINQ query expression.

    Custom SQL Expressions and Object Tracking for Updates

    By default when you retrieve a data model object using LINQ to SQL, it will track all changes and updates you make to it.  If you call the "SubmitChanges()" method on the DataContext class, it will then transactionally persist all of the updates back to the database.  I cover this in more depth in Part 4 of this LINQ to SQL series.

    One of the cool features of the ExecuteQuery() method is that it can fully participate in this object tracking and update model.  For example, we could write the code below to retrieve all products from a specific category and discount their prices by 10%:

    Because we typed the return value of our ExecuteQuery call in the GetProductsByCategory method to be of type "Product", LINQ to SQL knows to track the Product objects we returned from it.  When we call "SubmitChanges()" on the context object they will be persisted back to the database.

    Custom SQL Expressions with Custom Classes

    The ExecuteQuery() method allows you to specify any class as the return type of a SQL query.  The class does not have to be created using the LINQ to SQL ORM designer, or implement any custom interface - you can pass in any plain old class to it.

    For example, I could define a new ProductSummary class that has a subset of Product properties like below (notice the use of the new C# Automatic Properties feature):

    We could then create a GetProductSummariesByCategory() helper method on our NorthwindDataContext that returns results based on it.  Notice how our SQL statement below requests just the subset of product values we need - the ExecuteQuery method then handles automatically setting these on the ProductSummay objects it returns:

    We can then invoke this helper method and iterate over its results using the code below:

    Custom SQL Expressions for Inserts/Updates/Deletes

    In addition to using custom SQL expressions for queries, we can also execute them to perform custom Insert/Update/Delete logic.

    We can accomplish this by creating the appropriate partial Insert/Update/Delete method for the entity we want to change in a partial class on our DataContext.  We can then use the ExecuteCommand method on the DataContext base class to write the SQL we want to execute.  For example, to override the Delete behavior for Product classes we could define this DeleteProduct partial method:

    And now if we write the below code to remove a specific Product instance from our database, LINQ to SQL will call the DeleteProduct method - which will cause our custom SQL to execute in place of the default dynamic SQL that LINQ to SQL would otherwise use:

    Summary

    The LINQ to SQL ORM automatically generates and executes dynamic SQL to perform queries, updates, inserts and deletes against a database.

    For advanced scenarios, or cases where you want total control over the SQL query/command executed, you also have the ability to customize the ORM to use either SPROCs, or your own custom SQL Expressions, instead.  This provides you with a great deal of flexibility when building and extending your data access layer.

    In future blog posts in this series I'll cover some remaining LINQ to SQL concepts including: Single Table Inheritance, Deferred/Eager Loading, Optimistic Concurrency, and handling Multi-Tier scenarios. 

    Hope this helps,

    Scott

  • LINQ to SQL (Part 6 - Retrieving Data Using Stored Procedures)

    Over the last few weeks I've been writing a series of blog posts that cover LINQ to SQL.  LINQ to SQL is a built-in O/RM (object relational mapper) that ships in the .NET Framework 3.5 release, and which enables you to model relational databases using .NET classes.  You can use LINQ expressions to query the database with them, as well as update/insert/delete data.

    Below are the first five parts of my LINQ to SQL series:

    In these previous LINQ to SQL blog posts I demonstrated how you could use LINQ query expressions to programmatically retrieve data from a database.

    In today's blog post I'll cover how you can also use database stored procedures (SPROCs) and user defined functions (UDFs) with your LINQ to SQL data model.  Today's blog post will specifically cover how to call SPROCs to query and retrieve data from the database.  In my next blog post in this series I'll then show how you can optionally also use SPROCs to update/insert/delete data from the database.

    To SPROC or not to SPROC?  That is the question....

    The question of whether to use Dynamic SQL generated by an ORM or instead use Stored Procedures when building a data layer is a topic that generates endless (very passionate) debate amongst developers, architects and DBAs.  A lot of people much smarter than me have written on this topic, so I won't rehash the arguments for and against each side here.

    The LINQ to SQL ORM that ships in .NET 3.5 is pretty flexible, and can be used to create data model classes whose object model can be independent of the underlying database schema, and which can encapsulate business logic and validation rules that work regardless of whether the data model is populated/persisted via dynamic SQL or via SPROCs.

    In my LINQ to SQL Part 3: Querying our Database post I discussed how you can write LINQ query expressions against a LINQ to SQL data model using code like below:

    When you write LINQ query expressions like this the LINQ to SQL ORM will execute the necessary dynamic SQL for you to retrieve Product objects that matches your query.

    As you'll learn in this post, you can also optionally map SPROCs in the database to your LINQ to SQL DataContext class, which allows you to alternatively retrieve the same Product objects by calling a stored procedure instead:

     

    This ability to use both dynamic SQL and SPROCs with a clean data model layer is pretty powerful, and provides a great deal of flexibility when working on projects.

    The Steps to Map and Call a SPROC using LINQ to SQL

    In my Part 2: Defining our Data Model Classes tutorial I discussed how to use the LINQ to SQL ORM designer to create a LINQ to SQL class model like below:

    Notice above how there are two panes on the LINQ to SQL ORM designer surface.  The left pane enables us to define data model classes that map to our database.  The right method pane allows us to optionally map SPROCs (and UDFs) to our LINQ to SQL DataContext object, which we can then use in-place of dynamic SQL to populate the data model objects.

    How to Map a SPROC to a LINQ to SQL DataContext

    To map SPROCs to our DataContext class, let's first go to the VS 2008 Server Explorer window and look at the SPROCs within our database:

    We can double click any of the SPROCs above to open and edit them.  For example, below is the "CustOrderHist" SPROC in Northwind:

    To map the above SPROC to our LINQ to SQL DataContext, we can drag/drop it from the Server Explorer onto our LINQ to SQL ORM designer.  This will automatically create a new method on our LINQ to SQL DataContext class like below:

    By default the method name created on the DataContext class will be the same as the SPROC name, and the return type of the method will be an automatically created type that follows the "[SprocName]Result" naming pattern. For example: the SPROC above would return a sequence of "CustOrderHistResult" objects.  We could optionally change the name of the method by selecting it in the designer and then use the property grid to rename it.

    How to Call our Newly Mapped SPROC

    Once we've done the steps above to map a SPROC onto our DataContext class, it is easy to use it to programmatically retrieve data.  All we need to-do is call the new method we mapped on our DataContext class to get back a sequence of strongly typed results from the SPROC:

    Calling the SPROC in VB:

    Calling the Sproc in C#:

    In addition to programming looping over the result like in the code samples above, I could also obviously bind the results to any UI control to display them.  For example, the below code databinds the result of our SPROC to a <asp:gridview> control:

    Which then displays the product history of our customer on a page like so:

    Mapping the Return Type of SPROC Methods to Data Model Classes

    In the "CustOrderHist" SPROC example above the stored procedure returned a sequence of product history results containing two columns of data: the ProductName of the product, and the Total Number of orders the customer has made for that product.  The LINQ to SQL designer automatically defined a new "CustOrderHistResult" class to represent this result.

    We could alternatively choose to have the return result of a SPROC map to an existing data model class we have already defined in the LINQ to SQL designer (for example: an existing Product or Order entity class). 

    For example, assume we have a "GetProductsByCategory" SPROC in our database that returns product information like so:

    Like before we can create a "GetProductsByCategory" method on our DataContext that calls this SPROC by dragging it onto our LINQ to SQL designer.  Rather than just dropping the SPROC anywhere on the designer, though, we'll instead drop the SPROC on top of the existing "Product" class in our data model designer:

    This gesture of dropping the SPROC onto the Product class tells the LINQ to SQL designer to have the "GetProductsByCategory" method return a sequence of "Product" objects as a return result:

     

    One of the cool things about having our SPROC return "Product" objects like above is that LINQ to SQL will automatically track the changes made to the returned Product objects just like it would Product objects returned via LINQ queries.  When we call the "SubmitChanges()" method on our DataContext, the changes we have made to these objects will automatically be saved back to the database.

    For example, we could write the code below to retrieve (using a SPROC) and change the price of all products within a specific Category to be 90% of their current value:

    When we call SubmitChanges() at the end it will transactionally update all of the product prices.  To understand more about how change tracking and the SubmitChanges() method work, as well as about how Validation Business Logic can be added to data model entities, please read my LINQ to SQL Part 4: Updating our Database tutorial. 

    In my next blog post in this LINQ to SQL series I'll also cover how you can replace the dynamic insert/update/delete SQL generated by the ORM with custom SPROCs that handle the database updates instead.  The nice thing is that the code above wouldn't change at all if I configured my DataContext to use SPROCs for updates - it would purely be a mapping layer change and the code written against my data model would be oblivious to it. 

    Handling SPROC Output Parameters

    LINQ to SQL maps "out" parameters in SPROCs as reference parameters (ref keyword), and for value types declares the parameter as nullable.

    For example, consider the below "GetCustomerDetails" SPROC which takes a CustomerID as an input parameter, and which returns the company name as an output parameter in addition to its order history as a query result:

    If we drag the above SPROC onto our "Order" class in the LINQ to SQL designer, we could then write the below code to call it:

    VB:

    C#:

    Notice in the code above how the SPROC helper method returns back a sequence of Order objects - but also then returns the CompanyName as an output parameter to the helper method.

    Handling Multiple Result Shapes from SPROCs

    When a stored procedure can return multiple result shapes, the return type of the SPROC method on the DataContext cannot be strongly typed to a single class shape.  For example, consider the SPROC below which returns either a product result or an order result depending on the input parameter:

    LINQ to SQL supports the ability to create SPROC helper methods that can return either a Product or Order shape by adding a partial "NorthwindDataContext" class to the project that defines a method (which in this case we'll call "VariablesShapeSample") that invokes the SPROC and returns an IMultipleResult object like so:

    VB:

    C#:

    Once this method is added into our project we can then call it and convert the result to be either a Product or Order sequence when we are using it:

    VB:

    C#:

    Supporting User Defined Functions (UDFs)

    In addition to SPROCS, LINQ to SQL also supports both scalar-valued and table-valued user defined functions (UDFs), as well as the in-line counterpart to both.  Once added to your DataContext as a method, you can use these UDF functions within your LINQ queries.

    For example, consider a simple scalar user defined function called "MyUpperFunction":

    We can drag/drop it from the Visual Studio Server Explorer onto our LINQ to SQL Designer to add it as a method on our DataContext:

    We can then use this UDF function inline within our LINQ expressions when writing queries against our LINQ to SQL data model (notice it is being used within the "where" clause below):

    VB:

    C#:

    If you use the LINQ to SQL Debug Visualizer that I blogged about here, you can see how LINQ to SQL transforms the above expression queries into raw SQL that execute the UDF inside the database at runtime:

    Summary

    LINQ to SQL supports the ability to call Stored Procedures and UDFs within the database and nicely integrate them into our data model.  In this blog post I demonstrated how you can use SPROCs to easily retrieve data and populate our data model classes.  In my next blog post in this series I'll cover how you can also use SPROCs to override the update/insert/delete logic when you SubmitChanges() on your DataContext to persist back to the database.

    Hope this helps,

    Scott

  • LINQ to SQL Debug Visualizer

    Probably the biggest programming model improvement being made in .NET 3.5 is the work being done to make querying data a first class programming concept.  We call this overall querying programming model "LINQ", which stands for .NET Language Integrated Query.  Developers can use LINQ with any data source, and built-in libraries are included with .NET 3.5 that enable LINQ support against Objects, XML, and Databases.

    Earlier this summer I started writing a multi-part blog series that discusses the built-in LINQ to SQL provider in .NET 3.5.  LINQ to SQL is an ORM (object relational mapping) implementation that allows you to model a relational database using .NET classes.  You can then query the database using LINQ, as well as update/insert/delete data from it.  LINQ to SQL fully supports transactions, views, and stored procedures.  It also provides an easy way to integrate data validation and business logic rules into your data model.