Optimize your SQL statements

by community-syndication | Sep 19, 2012 | BizTalk Community Blogs via Syndication

It is always good practise to optimize your SQL query statements for a better performance. Just making sure your query does what it needs to, isn’t enough. You should consider the performance impact of your code and try to optimize it for the best performance.

Below is a short list of steps to take when optimizing your query statements.
More details on the steps can be found in this blog.

1. Check if you’re missing any required table joins.
   This could result in a Cartesian join.
2. Check if you’re missing any required WHERE clause to prevent retrieving too much data.
3. Check if statistics are being created & updated automatically.
4. Check if the statistics are up-to-date.
   The DBCC SHOW_STATISTICS command can be used to view the last updated date-time, total table rows and the number of rows sampled.
   Outdated statistics can be renewed using  the sp_updatestats stored procedure, or By using the FULLSCAN option to update all statistics of a table.
5. Check for any missing table or Index scans by using the execution plans.
   you can use these DMV’s to check for missing indexes: sys.dm_db_missing_index_details, sys.dm_db_missing_index_group_stats, sys.dm_db_missing_index_groups.
6. Check for RID Lookups, also by using the execution plans.
   These cannot always be eliminated, but by making use of covering indexes, RID Lookups can be reduced.
7. Check for any sort operator, again by using the execution plans.
   There are 3 options:
   • Modify the underlining tables to create a CLUSTERED index on the required sort columns. It could be worth trying out creating the CLUSTERED index on another column which is not the Primary Key.
   • Create an Indexed view on the underlining tables and sort the view by creating a CLUSTERED Index.
   • Create a NON CLUSTERED Index on the specified columns and Include all other columns which will be returned.
8. Check for excessive index fragmentation.
   The DMV sys.dm_db_index_physical_stats can be used for this matter.
9. Check table locks.
   To prevent locking problems, follow these guidelines:
   • Keep transactions as short as possible.
   • Review the transaction isolation level, and consider minimizing locking contention, thus increasing concurrency by changing to ‘Read Committed using row versioning’ or ‘Snapshot’.
   • Specify table hints such as READUNCOMMITTED or READPAST on the select statements. Although both of these table hints do increase concurrency, both have disadvantages such as ‘dirty reads’ when using the READUNCOMMITTED or returning an incomplete data set when using the READPAST and therefore they may not be acceptable to use in all circumstances.

Source: http://www.sqlservercentral.com/articles/Performance+Tuning/70647/

Speeding Up Database Access

by community-syndication | Sep 19, 2012 | BizTalk Community Blogs via Syndication

I found a complete serie on speeding up acces to your SQL Database.

So far, there are only 4 parts published of an 8 part series of articles on speeding up access to a SQL Server Database.
Here’s an overview of the content of all 8 parts (including links of already published parts):

• Part 1 Pinpointing missing indexes and expensive queries
  • Find expensive queries (with trace log)
  • Locate missing indexes with the query optimizer (or with Tuning Advisor)
  • Find unused indexes
• Part 2 Pinpointing other bottlenecks
  • Find Locks and Latches (via counters in perfmon)
  • Execution plan reusability (using counters or dynamic management views (DMV))
  • Fragmentation of data and indexes
  • Memory check (using counters to verify if there’s a lack of memory)
  • Disk usage (locate possible disk bottlenecks using counters)
  • CPU (use counters to see if the CPU is stressed)
• Part 3 Fixing missing indexes
  • Indexes explained (clustered and nonclustered indexes)
  • Index usage (when / when not)
  • Maintaining indexes
• Part 4 Fixing expensive queries
  • Cache aggregation queries
  • Keep records short
  • Considering Denormalization
  • Be careful with triggers
  • Use table variables for small temporary result sets
  • Use Full Text Search instead of LIKE
  • Replacing cursors with set based code
  • Minimise traffic from SQL Server to Web Server
  • Object Naming
  • Use SET NOCOUNT ON
  • Use FILESTREAM for values over 1MB
  • Avoid functions on columns in WHERE clauses
  • Use UNION ALL instead of UNION
  • Use EXISTS instead of COUNT to find existence of records
  • Combine SELECT and UPDATE
• Part 5 Fixing locking issues
• Part 6 Fixing execution plan reuse
• Part 7 Fixing fragmentation
• Part 8 Fixing memory, disk and CPU issues

I’ll update this post when new parts are published.

Migrating TailspinSpyworks Web Forms to Windows Azure | Migration de l’appli Web Forms TailspinSpyworks vers Windows Azure

by community-syndication | Sep 18, 2012 | BizTalk Community Blogs via Syndication

()

()

    <connectionStrings>
    <add name="ApplicationServices" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
    <add name="CommerceEntities" connectionString="metadata=res://*/Data_Access.EDM_Commerce.csdl|res://*/Data_Access.EDM_Commerce.ssdl|res://*/Data_Access.EDM_Commerce.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Commerce.mdf;Integrated Security=True;User Instance=True;MultipleActiveResultSets=True&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

  <connectionStrings>
    <add name="ApplicationServices" connectionString="Data Source=.;Initial Catalog=ASPNETDB-TAILSPINSPYWORKS.MDF;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False" providerName="System.Data.SqlClient" />
    <add name="CommerceEntities" connectionString="metadata=res://*/Data_Access.EDM_Commerce.csdl|res://*/Data_Access.EDM_Commerce.ssdl|res://*/Data_Access.EDM_Commerce.msl;provider=System.Data.SqlClient;provider connection string=&quot;Data Source=.;Initial Catalog=COMMERCE-TAILSPINSPYWORKS.MDF;Integrated Security=True;Connect Timeout=15;Encrypt=False;TrustServerCertificate=False&quot;" providerName="System.Data.EntityClient" />
  </connectionStrings>

namespace TailspinSpyworks
{
    public partial class Error : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            Label_ErrorFrom.Text = Request["Err"].ToString();
            // there might be no Inner Exception. 
            //Label_ErrorMessage.Text = Request["InnerErr"].ToString();
            // string.Format will apply ToString() only to non null arguments
            Label_ErrorMessage.Text = string.Format("{0}", Request["InnerErr"]);
        }
    }
}

        protected void FormView_Product_PageIndexChanging(object sender, FormViewPageEventArgs e)
        {
            // this event was recreated but is not handled
        }

Next>

Publish

Connect

Next>

Next>

Next>

--~Changing index [dbo].[Orders].PK_Orders to a clustered index.  You may want to pick a different index to cluster on.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Orders]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Orders](
    [OrderID] [int] IDENTITY(1,1) NOT NULL,
    [CustomerName] [nvarchar](256) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Orders_OrderDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_Orders_ShipDate]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[Orders] ADD  CONSTRAINT [DF_Orders_ShipDate]  DEFAULT (getdate()) FOR [ShipDate]
END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[OrderDetails]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[OrderDetails](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [OrderID] [int] NULL,
    [ProductID] [int] NULL,
    [Quantity] [int] NULL,
    [UnitCost] [money] NULL,
 CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
--~Changing index [dbo].[Categories].PK_Categories to a clustered index.  You may want to pick a different index to cluster on.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Categories]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Categories](
    [CategoryID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED 
(
    [CategoryID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
--~Changing index [dbo].[Products].PK_Products to a clustered index.  You may want to pick a different index to cluster on.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Products]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Products](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [CategoryID] [int] NOT NULL,
    [ModelNumber] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ModelName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [ProductImage] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [UnitCost] [money] NOT NULL,
    [Description] [nvarchar](3800) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SelectPurchasedWithProducts]') AND type in (N'P', N'PC'))
BEGIN
EXEC dbo.sp_executesql @statement = N'CREATE PROCEDURE dbo.SelectPurchasedWithProducts
 @ProductID int
AS
    SELECT  TOP 5 
    OrderDetails.ProductID,
    Products.ModelName,
    SUM(OrderDetails.Quantity) as TotalNum

FROM    
    OrderDetails
  INNER JOIN Products ON OrderDetails.ProductID = Products.ProductID

WHERE   OrderID IN 
(
    /* This inner query should retrieve all orders that have contained the productID */
    SELECT DISTINCT OrderID 
    FROM OrderDetails
    WHERE ProductID = @ProductID
)
AND OrderDetails.ProductID != @ProductID 

GROUP BY OrderDetails.ProductID, Products.ModelName 

ORDER BY TotalNum DESC
RETURN

' 
END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Reviews]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[Reviews](
    [ReviewID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [CustomerName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [CustomerEmail] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Rating] [int] NOT NULL,
    [Comments] [nvarchar](3850) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [PK_Reviews] PRIMARY KEY CLUSTERED 
(
    [ReviewID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END
GO
--~Changing index [dbo].[ShoppingCart].PK_ShoppingCart to a clustered index.  You may want to pick a different index to cluster on.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ShoppingCart]') AND type in (N'U'))
BEGIN
CREATE TABLE [dbo].[ShoppingCart](
    [RecordID] [int] IDENTITY(1,1) NOT NULL,
    [CartID] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [Quantity] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
 CONSTRAINT [PK_ShoppingCart] PRIMARY KEY CLUSTERED 
(
    [RecordID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF)
)
END

IF NOT EXISTS (SELECT * FROM sys.indexes WHERE object_id = OBJECT_ID(N'[dbo].[ShoppingCart]') AND name = N'IX_ShoppingCart')
CREATE NONCLUSTERED INDEX [IX_ShoppingCart] ON [dbo].[ShoppingCart] 
(
    [CartID] ASC,
    [ProductID] ASC
)WITH (STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF)
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ShoppingCart_Quantity]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ShoppingCart] ADD  CONSTRAINT [DF_ShoppingCart_Quantity]  DEFAULT ((1)) FOR [Quantity]
END
GO
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DF_ShoppingCart_DateCreated]') AND type = 'D')
BEGIN
ALTER TABLE [dbo].[ShoppingCart] ADD  CONSTRAINT [DF_ShoppingCart_DateCreated]  DEFAULT (getdate()) FOR [DateCreated]
END
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[VewOrderDetails]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.VewOrderDetails
AS
SELECT        dbo.Products.ProductID, dbo.Products.ModelNumber, dbo.Products.ModelName, dbo.OrderDetails.Quantity, dbo.OrderDetails.UnitCost, 
                         dbo.OrderDetails.OrderID
FROM            dbo.OrderDetails INNER JOIN
                         dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductID
' 
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ViewAlsoPurchased]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.ViewAlsoPurchased
AS
SELECT        TOP (5) dbo.OrderDetails.ProductID, dbo.Products.ModelName, SUM(dbo.OrderDetails.Quantity) AS TotalNumPurchased, dbo.OrderDetails.OrderID, 
                         dbo.Products.ProductID AS Products_ProductID
FROM            dbo.OrderDetails INNER JOIN
                         dbo.Products ON dbo.OrderDetails.ProductID = dbo.Products.ProductID
WHERE        (dbo.OrderDetails.OrderID IN
                             (SELECT DISTINCT OrderID
                               FROM            dbo.OrderDetails AS OrderDetailsSelected))
GROUP BY dbo.OrderDetails.ProductID, dbo.Products.ModelName, dbo.OrderDetails.OrderID, dbo.Products.ProductID
ORDER BY TotalNumPurchased DESC
' 
GO
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
IF NOT EXISTS (SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'[dbo].[ViewCart]'))
EXEC dbo.sp_executesql @statement = N'CREATE VIEW dbo.ViewCart
AS
SELECT        TOP (100) PERCENT dbo.Products.ProductID, dbo.Products.ModelNumber, dbo.Products.ModelName, dbo.Products.UnitCost, dbo.ShoppingCart.Quantity, 
                         dbo.ShoppingCart.CartID
FROM            dbo.Products INNER JOIN
                         dbo.ShoppingCart ON dbo.Products.ProductID = dbo.ShoppingCart.ProductID AND dbo.Products.ProductID = dbo.ShoppingCart.ProductID
ORDER BY dbo.Products.ModelName, dbo.Products.ModelNumber
' 
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Order_OrderDetails]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_Order_OrderDetails] FOREIGN KEY([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Order_OrderDetails]') AND parent_object_id = OBJECT_ID(N'[dbo].[OrderDetails]'))
ALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_Order_OrderDetails]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Products_Categories]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products]  WITH CHECK ADD  CONSTRAINT [FK_Products_Categories] FOREIGN KEY([CategoryID])
REFERENCES [dbo].[Categories] ([CategoryID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Products_Categories]') AND parent_object_id = OBJECT_ID(N'[dbo].[Products]'))
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Categories]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reviews_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reviews]'))
ALTER TABLE [dbo].[Reviews]  WITH NOCHECK ADD  CONSTRAINT [FK_Reviews_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
NOT FOR REPLICATION 
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reviews_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reviews]'))
ALTER TABLE [dbo].[Reviews] CHECK CONSTRAINT [FK_Reviews_Products]
GO
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShoppingCart_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShoppingCart]'))
ALTER TABLE [dbo].[ShoppingCart]  WITH CHECK ADD  CONSTRAINT [FK_ShoppingCart_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
GO
IF  EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ShoppingCart_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[ShoppingCart]'))
ALTER TABLE [dbo].[ShoppingCart] CHECK CONSTRAINT [FK_ShoppingCart_Products]
GO
-- BCPArgs:2:[dbo].[Orders] in "c:\SQLAzureMW\BCPData\dbo.Orders.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:13:[dbo].[OrderDetails] in "c:\SQLAzureMW\BCPData\dbo.OrderDetails.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:7:[dbo].[Categories] in "c:\SQLAzureMW\BCPData\dbo.Categories.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:41:[dbo].[Products] in "c:\SQLAzureMW\BCPData\dbo.Products.dat" -E -n -b 10000 -a 16384
GO
-- BCPArgs:6:[dbo].[ShoppingCart] in "c:\SQLAzureMW\BCPData\dbo.ShoppingCart.dat" -E -n -b 10000 -a 16384
GO

18/09/2012 16:18:34 --> Error #: 40514 -- 'NOT FOR REPLICATION' is not supported in this version of SQL Server.
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_Reviews_Products]') AND parent_object_id = OBJECT_ID(N'[dbo].[Reviews]'))
ALTER TABLE [dbo].[Reviews]  WITH NOCHECK ADD  CONSTRAINT [FK_Reviews_Products] FOREIGN KEY([ProductID])
REFERENCES [dbo].[Products] ([ProductID])
NOT FOR REPLICATION 

Exit

http://tailspinspyworks.azurewebsites.net/

Benjamin

Blog Post by: Benjamin GUINEBERTIERE

Pre-allocate space and define auto-growth settings for BizTalk Server databases

by community-syndication | Sep 18, 2012 | BizTalk Community Blogs via Syndication

Today I want to talk to you about two small optimizations often forgotten but with performance implications. Some of these implications may be noticed more in the early days of our BizTalk environment when the databases are small or almost empty and often need to increase, others can have impact later when databases are large […]
Blog Post by: Sandro Pereira

Consuming JSON endpoint with BizTalk 2010 R2 REST WCF-WebHttp adapter

by community-syndication | Sep 18, 2012 | BizTalk Community Blogs via Syndication

In the previous article we looked at some of the basic GET operations we can perform using the new REST (WCF-WebHttp) adapter that ships with BizTalk Server 2010 R2. All our examples are based on XML data formats. One of the readers asked the question in the comments, how we can consume a REST end […]

The post Consuming JSON endpoint with BizTalk 2010 R2 REST WCF-WebHttp adapter appeared first on BizTalk360 Blog.

Blog Post by: Saravana Kumar

Windows Azure SQL Data Sync Walkthrough

by community-syndication | Sep 18, 2012 | BizTalk Community Blogs via Syndication

I’ve just completed a basic implementation using Windows Azure SQL Data Sync that I will be using in a demo for an upcoming course. This is a basic walkthrough that you should be able to follow if you want to get to grips with the new SQL Data Sync functionality in Windows Azure.

The walkthrough is based on the SQL Data Sync August 2012 – Service Update, changes in future versions are possible.

Scenario

The scenario for this walkthrough is shown in the diagram below.

Adventure Works has an existing on-premise SQL database that contains its business information. They plan to create an on-line store application hosted in Windows Azure. In order to keep the product information in the on-line store database up to date, SQL data sync will be used to coordinate the synchronization between the on-premise database and the product database hosted in SQL Azure.

Existing Implementation

The existing implementation consists of a slightly modified version of the Adventureworks2012 database for Windows Azure. The sample database can be downloaded here: http://msftdbprodsamples.codeplex.com/releases/view/37304.

The database has been modified to replace the custom defined data types, which are currently not supported in Data Sync, with regular SQL data types. The modified code to create the Product table is shown below.

With the changes made to AdventureWorks2012ForSQLAzure_Schema.sql the CreateAdventureWorksForSQLAzure.cmd is run specifying local server and credentials to create and populate the database on the local instance of SQL Server.

A very WPF basic line-of-business (LOB) application is created in WPF to allow product data to be modified. The XMLT for the min window is shown here:

The code-behind here:

The application uses an entity framework model to connect to the on-premise Adventureworks2012 database. A screenshot of the application is shown below.

Creating Windows Azure SQL Databases

The first step in creating the cloud-based online store is to create the databases that will be used for the online store and the data synchronization. The following two 1 GB web databases are created using the Windows Azure Portal.

%u00b7 SyncHubDb – A database to act as the hub database for SQL Data Sync.

%u00b7 WebStoreDb – A database for the online web store application.

Both the databases are creates as the 1G Web databases.

Creating a Sync Group

With the SQL Databases created in Windows Azure, the next task is to use SQL Data Sync to define a sync group to synchronize the produce data.

The first step is provision a SQL Data Sync server in an appropriate region. This is currently done using the old Azure portal. Click on the Data Sync button, and then click Provision, select the account, and specify a region, its best to use the same region where your Windows Azure SQL Databases are located.

Once the Data Sync server has been provisioned, a new sync group can be created. With Sync Groups selected, click Create.

Enter the name for the sync group, and then click on the icon for the Sync Hub database.

Enter the database details for the Sync Hub database and click Add.

All data synchronization that is processed by this sync group will pass through the sync hub database, which must be hosted in Windows Azure SQL Database.

Adding an On-Premise Database

In order to use SQL Data Sync with an on-premise database you will need to install and configure Microsoft SQL Data Sync Agent on your local SQL server and register the database with the service.

Click on the icon to add an on-premise database and select Add a new SQL database to the sync group, you can also specify the Sync Direction here, I left the default of Bi-Directional selected.

If you don’t have an agent configured, select Install a new Agent.

Download and install the agent, and then enter a name and generate an agent key.

Start Microsoft SQL Data Sync Agent from the start menu and set the agent key that was generated in the portal. This will open a management console for the on-premise service.

Click on Register, and then select the appropriate database on the local SQL server, and click Save.

You should see that the database is reachable.

Back in the portal, click Next, then Get Database List, select the database, and click Finish.

The on-premise database will now be connected to the Data Sync server in Windows Azure.

Configuring the Sync Schedule

The schedule for data synchronization can now be configured, along with the conflict resolution options, the time interval must be between 5 minutes and one month. I selected a 5 minute interval and Client Wins. The short time interval is good for demo purposes.

Setting Client Wins for conflict resolution means that the on-premise database will win if there are any conflicts with the data synchronization.

Defining the Data Set

The next step is to define the data that will be synchronized by the sync group. In this scenario, only the Product table in the Production schema will be synchronized. To do this, select the AdventureWorks2012 database, and then select the Products table, ensuring all columns are selected. Note that some of the tables contain data types that do not meet the schema requirements for synchronization; this is why the data types in the Product table were modified.

Now the data set has been selected, the sync group can be deployed. Click the Deploy button to do this.

The Sync Group will be provisioned and the first data synchronization will run. After a few seconds the status of the two databases should transition to good.

Opening the SyncHubDb in the Windows Azure portal shows the tables that have been created. The Production.Product table is present, with the 504 rows of product data. There are also a number of tables that are used by SyncFramework to manage the synchronization process.

Selecting data from the Production.Product table shows that the product data is now present in the SyncHubDb database. This was synchronized from the on-premise database when the Sync Group was provisioned. The synchronization will run every 5 minutes to synchronize any changes between the Product tables in both databases.

Adding a Windows Azure SQL Database

The next step is to add the WebStoreDb database to the sync group. This is done in a similar way to adding the SyncHubDb database. Click on the Add Windows Azure SQL Database icon and specify the WebStoreDb database details. With this database the data synchronization will be one directional, data will be synchronized from the hub to the WebStoreDb database, but not from the WebStoreDb database to the sync hub.

Deploy the Sync Group to save the changes, after a while the provisioning and synchronization will complete, and the topology will be as follows.

Clicking on the Log Viewer icon will show logs from the synchronization process. This can be used to verify that synchronization is taking place, and to diagnose any errors with the synchronization process.

Examining the WebStoreDb database shows that the product data has successfully been synchronized. The synchronization will run using the 5 minute schedule to ensure that any changes in the on-premise database are updated in the on-line store.

Creating the Online Store Application

The on-line store application is a simple ASP.net web forms application that uses entity framework to data-bind to the products table in the WebStoreDb database.

The default page has been modified to use a Repeater control to display product information.

The code behind file will use entity framework to load the product data and data-bind it to the Repeater.

The connection string is modified to use the WebStoreDb database hosted in Windows Azure SQL Database.

The application is deployed as a Windows Azure Web Site, and tested. The default page successfully displays product information.

Testing Data Synchronization & Scheduling

In order to test that the data synchronization and scheduling are working correctly, the WPF LOB application will be used to make some changes to the products data. In this test the first three products are put on special offer, and the changes updated in the on-premise database.

After a few minutes the changes have been successfully synchronized with the WbStoreDb database, and the special offer products are viewable in the online store website.

The log files can be viewed to see the results of the synchronization operations.

From the Adventureworks2012 database to the SyncHubDb database:

From the SyncHubDb database to the WebStoreDb database:

Conclusions

I’ve only taken a quick look at Windows Azure SQL Data Sync and it seems fairly intuitive to get up and running with it and get basic synchronization up and running. I’ve always been a believer that companies will “Extend to the Cloud” rather than “Move to the Cloud”, and will focus on creating hybrid applications, with some services hosted in the cloud, whilst maintaining existing on-premise applications. Windows Azure SQL Data Sync, like Windows Azure Service Bus, is a great technology for bridging the gap between on-premise applications and applications hosted in the cloud.

SP2010: Good Morning SharePoint – error 2613 I think.

by community-syndication | Sep 17, 2012 | BizTalk Community Blogs via Syndication

Here’s something I go this morning which I think is a SharePoint error:

Blog Post by: Mick Badran

Everything’s Amazing and Nobody’s Happy

by community-syndication | Sep 17, 2012 | BizTalk Community Blogs via Syndication

Scott Hanselman wrote an interesting post called Everything’s Broken and Nobody’s Upset this weekend, and it reminded me of the classic, profound Louis CK bit called Everything’s Amazing and Nobody’s Happy. While Scott’s post was reasonable, I’m an optimist and instead thought of a few aspects of technology awesomeness in life that are super cool […]
Blog Post by: Richard Seroter

When to Stop/Restart a Host Instance

by community-syndication | Sep 16, 2012 | BizTalk Community Blogs via Syndication

Further to a question I recently answered on StackOverflow, I wanted to expand a little on when it is necessary to restart a BizTalk Host Instance, and when this is overkill. Host Instance restart required Any change to an underlying datatype, map or orchestration will require a restart of the corresponding Host Instance. This is […]
Blog Post by: Brett