MyAdviceWriter ...

Access Control Services is one of the many services that are part of the Windows Azure Platform which handles an authentication conversation for you, allowing you an easy way to integrate cloud identity from providers like Yahoo, Google, Facebook and Live ID with your application. You need an active Azure Subscription (click here to try the 90 day Free Trial), add an ACS service namespace, then add a Secure Token Service (STS) reference to your  application (you’ll need the Windows Identity SDK and tools for Visual Studio to add the Federation Utility that does that last part – download here).

If you’ve seen an error that says ‘Unable to find assembly Microsoft.IdentityModel’ and wondered what’s the deal? In the documentation for working with a Secure Token Service (STS) you might have seen that this assembly is not part of the default deployment of your Windows Azure package, and that you’ll need to add the reference to your project and set the deployment behavior to deploy a local copy with the application…all good, right?

So you follow the instructions and still get the error. One reason for this is that you might be seeing an error caused by calling methods from the serviceRuntime which changes the appdomain of your webrole. In that case you should consider adding a Startup Task to load the WIF assembly in the Global Assembly Cache.

The basic logic is that in the startup of the web role you’ll need to inject a startup task to run the gacutil executable to do the installation. I created a startup folder in my web project to contain the scripts and files necessary, including a copy of gacutil.exe, a configuration file for it, and a batch file “IdentityGac.cmd” to contain the script commands needed.

I copied the gactuil.exe and config files from my C:\Program Files (x86)\Microsoft SDKs\Windows\v7.0A\Bin\x64 directory, and put them in the Startup folder. Next I created a cmd file (in notepad) with the commands to install the assembly into the GAC. I found a post that discusses how to create a script to do the installation on http://blogs.infosupport.com/adding-assemblies-to-the-gac-in-windows-azure/ where it installs the Windows Identity files and then sets the windows update service to on demand, installs the SDK, then runs gacutil.

    @echo off
    sc config wuauserv start= demand
    wusa.exe "%~dp0Windows6.1-KB974405-x64.msu" /quiet /norestart
    sc config wuauserv start= disabled
    e:\approot\bin\startup\gacutil /if e:\Approot\bin\Microsoft.IdentityModel.dll

I add these files to the Startup folder, and then open the properties for the 3 files and set the build action to none and “Copy to Output Directory” to Copy Always. This ensures that these files will be present in the deployed instance for my startup task. Next we edit the startup tasks in the Service Definition file, adding a section for <Startup>, and a <Task… to run our script file.

<?xml version="1.0" encoding="utf-8"?>
<ServiceDefinition name="MplsKickSite.Azure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition">
  <WebRole name="MplsKickSite" vmsize="ExtraSmall">
    <Sites>
      <Site name="Web">
        <Bindings>
          <Binding name="Endpoint1" endpointName="Endpoint1" />
        </Bindings>
      </Site>
    </Sites>
    <Endpoints>
      <InputEndpoint name="Endpoint1" protocol="http" port="80" />
    </Endpoints>
    <Startup>
      <Task commandLine="Startup\IdentityGac.cmd" executionContext="elevated" taskType="simple">
      </Task>
    </Startup>
    <Imports>
      <Import moduleName="Diagnostics" />
      <Import moduleName="RemoteAccess" />
      <Import moduleName="RemoteForwarder" />
    </Imports>
  </WebRole>
</ServiceDefinition>

In Steve Marx’s post on Startup Task Tips & Tricks he suggests that when debugging a new startup task to set the taskType to background instead of simple so that it won’t prevent Remote Desktop from getting configured. If you’re interested to download the Startup folder I used I’ve uploaded a zip archive file with the files here.

Happy Coding!

Today I showed a work around for when you are showing ACS on a site that is running without setting up the SSL piece.

<system.web>
  <!--Hack to get past request token on non-SSL site—>
  <httpRuntime requestValidationMode="2.0" />
  <pages validateRequest="false" controlRenderingCompatibilityVersion="4.0" 
         clientIDMode="AutoID"></pages>

          ...

Note that shutting off validation of requests is not recommended, as it opens exploits against your site. For a better way of approaching this, Sandrino Di Mattia does a great job of explaining the right way of how to deploy and use the access control service in an Azure deployed solution.

http://fabriccontroller.net/blog/a-few-tips-to-get-up-and-running-with-theazure-appfabric-access-control-service

Make sure you add a reference to Microsoft.IdentityModel v3.5.0.0 and then set the properties of the assembly to copy local with the deploy.

On another track we looked at how to explore the claims that are included as part of the returned identity. In the Azure event I also showed a page that shows the contents of the user identity and display information about the claims contained in the token returned from ACS. I created a page in a secure location, then added a simple data grid to the page (called GridView1 and then in the page load I pull the identity information from the authenticated user data. The code for the PageLoad is below:

        protected void Page_Load(object sender, EventArgs e)
        {

            try
            {
                // Cast the Thread.CurrentPrincipal
                IClaimsPrincipal icp = Thread.CurrentPrincipal as IClaimsPrincipal;

                // Access IClaimsIdentity which contains claims
                IClaimsIdentity claimsIdentity = (IClaimsIdentity)icp.Identity;

                // icp.Identity.Name;
                // txtAuthName.Text = claimsIdentity.Name;
                
                txtAuthType.Text = claimsIdentity.AuthenticationType;
                txtIsAuthenticated.Text = claimsIdentity.IsAuthenticated.ToString();


                var myClaims = from c in claimsIdentity.Claims
                               select new { c.ClaimType, c.Value };


                GridView1.DataSource = myClaims;
                GridView1.DataBind();


                // Enable secret content for administrators
                if (Thread.CurrentPrincipal.IsInRole("Administrator"))
                {
                    this.secretContent.Visible = true;
                }
            }
            catch (Exception ex)
            {
                txtAuthType.Text = ex.Message;
            }
        }

Enjoy!

I had a question after a Windows Azure Camp about what ports need to be opened and enabled at my work environment to enable working with Windows Azure. While the services work with REST there are a couple services that will benefit from adjusting the firewall to allow traffic between on-premise and the cloud. I found settings for Service Bus and SQL Server, and the settings are below…

Service Bus

-Minimal: Enable outbound http on port 80 and 443, authenticated against proxy server if any

-Optimal: Allow outbound on port 9350 to 9353, can limit to well known IP range

- 9350 unsecured TCP one-way client

- 9351 Secured TCB one-way (all listeners, secured clients)

- 9352 Secured TCP Rendezvous (all except one way)

- 9353 Direct Connect Probing Protocol (TCB listeners with direct connect)

SQL on-Premise via Windows Azure Connect

-In SSMS - Enable Remote Connections on SQL Server properties window

-In SQL Server Configuration Manager

- Disable or stop SQL Server Browser

- Enable TCP/IP in the SQL Server Network Configuration | Protocols for server

- Edit TCP/IP protocol properties and set TCP Dynamic Ports to Blank, and then specify TCP Port to 1433

- Restart SQL Service

-In Windows Firewall add the following rules

- Inbound Port 1433 (TCP) Allow the connection

- Apply to all profiles (Domain, Private and Public)

- Name the rule something significant

Enjoy!

One of the great things about Windows Azure is that it is a platform that is for the most part agnostic about what frameworks and languages you want to use. While I tend to do most of my demos in C# there is no reason we couldn’t use VB, PHP, Node.js, Java or any other language. In fact the developer story for the Microsoft Cloud is that if it runs on Windows it can run in Windows Azure. All you need is to configure the machine appropriately (more on that in another post). You can learn more about the tools by checking out the Tools section of the Windows Azure site.

For this CloudTip I got a request for how to do the encryption of the web.config but this time in VB. The logic is about the same, although I found that in VB I had to add a line to the configuration to save the new settings. The code for this in vb.net (adding to the global.asax file in the "Session_Start" subroutine…

Sub Session_Start(ByVal sender As Object, ByVal e As EventArgs)

    ' Code that runs when a new session is started 

    EncryptSection("appSettings")

End Sub

Private Sub EncryptSection(ByVal sSection As String)

    Dim config = System.Web.Configuration.WebConfigurationManager.OpenWebConfiguration(Context.Request.ApplicationPath)

    Dim configSection As ConfigurationSection = config.GetSection(sSection)

    If configSection.SectionInformation.IsProtected = False Then

        configSection.SectionInformation.ProtectSection("DataProtectionConfigurationProvider")

        config.Save()

    End If

End Sub

If you have questions about Cloud that would make a great Cloud Tip topic send them to me at my email or comment on the post.

Thanks!

In Windows Azure and especially with SQL Azure we need to store passwords to access things. I wanted to show how you can encrypt the web.config file by adding code to the global.asax file. The cool part of this is that using this technique you can secure application specific settings like connection strings and other data in the unlikely event that someone is able to get a copy of the configuration file (like by copying it to a thumb drive from the host machine or something similar).

The basic logic is to create a variable that points to a configuration section, then checking that the section is protected (i.e. encrypted). If it isn't, then call the ProtectSection method to encrypt the contents. The server uses the local DPAPI (Data Protection API) to encrypt the configuration section with a machine specific key, so only that machine can decrypt the contents. The code to add to the global.asax.cs file in the Application Start event for this is:

protected void Session_Start(object sender, EventArgs e) 
{ 
    EncryptSection("appSettings"); 
} 
     
private void EncryptSection(string sSection)
{
    Configuration config = System.Web.Configuration
                             .WebConfigurationManager
                             .OpenWebConfiguration
                             (Context.Request.ApplicationPath);

    ConfigurationSection configSection =
        config.GetSection(sSection);

    if (!configSection.SectionInformation.IsProtected)
    {
        configSection.SectionInformation.ProtectSection
        ("DataProtectionConfigurationProvider");
        config.Save();
    }
}

Happy Coding!

Suppose you have an existing application that you want to migrate to the cloud. There are many things to look at, from the data to the architecture to considering how you plan to scale beyond one instance. For simple web sites which don’t use a lot of data or stateful information the migration to the cloud can be pretty easy with the installation of the Visual Studio Tools for Windows Azure.

The current release of the tools (as of March 2012) is v1.6 which adds a command to the context menu when you right click on a web project. The SDK is installed using the Web Platform Installer (aka WebPI) which checks for previous versions of the tools and any other prerequisites and the consolidates the install into a single step.

After installing the tools when you open a Web Project, whether ASP.NET Web Forms, MVC, or similar, right clicking the project file gives you the command to add an Azure Deployment Project. You could achieve something similar by simply adding a Cloud Project to your solution and then right clicking on the “Roles” folder and adding the existing site to the project.

This adds a Cloud project to the solution and then adds the current Web project as a standard Web Role in the deployment. The key assets added include a Service Definition file and a couple Service Configuration files (one for running locally and one for running in the cloud).

The Service Definition file includes markup which describes how the Cloud Service will be deployed, including the public ports that are enrolled in the load balancer, any startup tasks and plugins that you want to run, as well as any other custom configuration that should be part of the deployment.

For example the Service Definition we created for the Kick Start event in Minneapolis looked like this:

<?xml version="1.0" encoding="utf-8"?>
<ServiceDefinition name="MplsKickSite.Azure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceDefinition">
  <WebRole name="MplsKickSite" vmsize="Small">
    <Sites>
      <Site name="Web">
        <Bindings>
          <Binding name="Endpoint1" endpointName="Endpoint1" />
        </Bindings>
      </Site>
    </Sites>
    <Endpoints>
      <InputEndpoint name="Endpoint1" protocol="http" port="80" />
    </Endpoints>
    <Imports>
      <Import moduleName="Diagnostics" />
    </Imports>
  </WebRole>
</ServiceDefinition>

In this code we’ve got a single web role being deployed that will run on port 80. It is running a small instance (1 dedicated core), and will have remote desktop enabled. The Service Configuration contains additional settings including the OS Family for the instance (1 = Windows Server 2008 SP2 and 2 = Server 2008 R2) and number of instances to run (in this case 3).

<?xml version="1.0" encoding="utf-8"?>
<ServiceConfiguration serviceName="MplsKickSite.Azure" xmlns="http://schemas.microsoft.com/ServiceHosting/2008/10/ServiceConfiguration" 
    osFamily="2" osVersion="*">
  <Role name="MplsKickSite">
    <Instances count="3" />
    <ConfigurationSettings>
      <Setting name="Microsoft.WindowsAzure.Plugins.Diagnostics.ConnectionString" value="UseDevelopmentStorage=true" />
      <Setting name="Microsoft.WindowsAzure.Plugins.RemoteAccess.Enabled" value="true" />
      <Setting name="Microsoft.WindowsAzure.Plugins.RemoteAccess.AccountUsername" value="...
      <Setting name="Microsoft.WindowsAzure.Plugins.RemoteAccess.AccountEncryptedPassword" value="...
      <Setting name="Microsoft.WindowsAzure.Plugins.RemoteAccess.AccountExpiration" value="...
      <Setting name="Microsoft.WindowsAzure.Plugins.RemoteForwarder.Enabled" value="true" />
      <Setting name="QueueName" value="createthumbnail" />
      <Setting name="myStorageAcct" value="UseDevelopmentStorage=true" />
    </ConfigurationSettings>
    <Certificates>
      <Certificate name="Microsoft.WindowsAzure.Plugins.RemoteAccess.PasswordEncryption" thumbprint="...
    </Certificates>
  </Role>
 </ServiceConfiguration>

Pressing F5 will run the Cloud Service in the local emulator (assuming we’re running Visual Studio as Administrator so it can communication cross process to the running services…an error message tells you if you’re not). The nice thing about this approach is it supports attaching to the local debugger and using the rich set of features such as breakpoints, local and watch variables, and all the rest of the goodness we’ve come to expect in Visual Studio debugging.

Once we’re satisfied that the application works and we are ready to go to the Cloud, you can right click on the Azure Deployment project and either click “Package” to create a deployment package which you can manually upload the the Windows Azure Management portal, or you can select Publish to automate it directly from Visual Studio. This runs a wizard with 3 basic steps to collect the necessary information and complete the deployment. You select the subscription you want to use, name the hosted service, optionally enable remote desktop and web deploy, and then click complete.

Note that if you enable Web Deploy, because this is only supported for a single instance development environment where you require the ability to publish changes to a web site quickly, the publishing process will change your configured number of instances to 1 despite having set it to 3 in the configuration files. That’s because if you have more than 1 instance deployed the load balancer will distribute the incoming web requests round robin, and if you change one of the instances with a web deploy you’ll have an inconsistent state. You can always change the number of instances from the management portal, but you should be aware of what you’re doing.

What happens next is the deployment process takes place. This includes validating that the hosted service is provisioned for you, uploading certificates if needed, then uploading your deployment package and configuration files to Windows Azure storage, and then deploying and starting your instances. It will also add the service deployment to the Server Explorer window (CTRL + ALT + S). From there you can see the status of your service instances, download the profile or intellitrace files if you’ve enabled that option (under advanced features during the publish).

For more information on how to do this, or if you’d like to see a webcast on how this is done, check out the first webcast in the Cloud Computing Soup to Nuts series (http://bit.ly/s2nCloud).

Enjoy!

Yesterday in Minneapolis we delivered the first Windows Azure Kick Start event of the series we’re running this spring to help developers learn and understand how to use the new tools and techniques for building cloud based applications. As part of that event we wrote a lot of code as a demonstration of how this stuff comes together and I’ve uploaded it to www.benkotips.com in case you’re interested in the download. The solution includes several projects including:

• MplsKickSite - An existing ASP.NET web site that we migrated to Windows Azure, implementing the RoleEntryPoint interface and adding references to the Microsoft.WindowsAzure.StorageClient for working with storage, and Microsoft.WindowsAzure.ServiceRuntime to give us instance information. We also added cloud identity to the site using Access Control Services to secure a data entry page with data hosted in a SQL Azure database
• WPFUpload which is a Windows application which includes logic to support drag and drop to upload files into Windows Azure Blob Storage, and if they are images to add work to a queue to create thumbnail images
• UploadDataLib which is a class library that implements Windows Azure Table Storage logic for working with the images uploaded by WPFUpload and the ThumbMaker worker role projects.
• ThumbMaker which is a worker role class that demonstrated working with Tables and Queues and the System.Drawing library to create thumbnail images and brand them with a custom logo message
• PhoneApp1 which demonstrates how to use a Windows Phone Silverlight user control to handle the login conversation with ACS
• NugetPhone which is a second example of ACS in use with devices, except that instead of spending 10 minutes to write the code like we did with PhoneApp1 we use the Nuget package manager to include a package (Install-Package Phone.Identity.AccessControl.BasePage) to perform the authentication for us and make the 4 code changes to implement the logic…2 minutes to demo

The decks and recordings of similar sessions are available on the Soup to Nuts Cloud Computing page of my site (http://bit.ly/s2nCloud). You can download the solution and project files and run every thing with the Compute and Storage Emulator, but you’ll need an active subscription to deploy to the cloud. If you don’t have Azure benefits you can activate from an MSDN Subscription you can always give the 90 day Free Trial a try.

Enjoy!

There are many ways to learn a new technology. Some of us prefer to read books, others like videos or screencasts, still others will choose to go to a training style event. In any case you need to have a reason to want to learn, whether it’s a new project, something to put on the resume or just the challenge because it sounds cool. For me I learn best when I’ve got a real project that will stretch my knowledge to apply it in a new way. It also helps to have a deadline.

I’ve been working for the last several years now for Microsoft in a role that allows me to help people explore what’s new and possible with the new releases of technology coming out at a rapid pace from client and web technologies like ASP.NET and Phone to user interface techniques like Silverlight and Ajax, to server and cloud platforms like SQL Server and Azure. The job has forced me to be abreast of how the technologies work, what you can do with them, and understanding how to explain the reasons for why and how they might fit into a project.

In this post I’d like to provide a quick tour of where you can find content and events on Cloud Computing that should help you get started and find answers along the way.

First on my list are the webcasts we’ve created that are 1 hour long sessions on the various aspects of a given topic. For Cloud Computing and Windows Azure I’ve got a list of several on my web site (www.benkotips.com) including a 27 part companion series we called “Windows Azure Boot Camp”. The first 10 webcasts in this series cover what you would see at a boot camp event (www.windowsazurebootcamp.com).  This spring we started a new series called “Cloud Computing Soup to Nuts” which is a developer focused get started with Windows Azure and the related services. We’ve recorded 6 webcasts as part of that series and will be adding more as we go forward. We just added 3 more for April including:

4/3 : Part 7 - Get Started with Windows Azure Caching Services with Brian Hitney (http://bit.ly/btlod-77)
How can you get the most performance and scalability from platform as a service? In this webcast, we take a look at caching and how you can integrate it in your application. Caching provides a distributed, in-memory application cache service for Windows Azure that provides performance by reducing the work needed to return a requested page.

4/10 : Part 8 - Get Started with SQL Azure Reporting Services with Mike Benkovich (http://bit.ly/btlod-78)
Microsoft SQL Azure Reporting lets you easily build reporting capabilities into your Windows Azure application. The reports can be accessed easily from the Windows Azure portal, through a web browser, or directly from applications. With the cloud at your service, there's no need to manage or maintain your own reporting infrastructure.  Join us as we dive into SQL Azure Reporting and the tools that are available to design connected reports that operate against disparate data sources. We look at what's provided from Windows Azure to support reporting and the available deployment options. We also see how to use this technology to build scalable reporting applications

4/17 : Part 9 – Get Started working with Service Bus with Jim O’Neil (http://bit.ly/btlod-79)
No man is an island, and no cloud application stands alone! Now that you've conquered the core services of web roles, worker roles, storage, and Microsoft SQL Azure, it's time to learn how to bridge applications within the cloud and between the cloud and on premises. This is where the Service Bus comes in—providing connectivity for Windows Communication Foundation and other endpoints even behind firewalls. With both relay and brokered messaging capabilities, you can provide application-to-application communication as well as durable, asynchronous publication/subscription semantics. Come to this webcast ready to participate from your own computer to see how this technology all comes together in real time.

If you’re looking for a conversational 30 minute show that covers Cloud topics I suggest checking out Cloud Cover on Channel9. This show features Azure experts including Ryan Dunn, Steve Marx, Wade Wegner, David Aiken and others who work closely with the product teams at Microsoft to learn how to use the latest releases.

Live events are a moving target depending on when you read this post, but we try to keep a list of upcoming Microsoft Events for developers on http://msdnevents.com. As we schedule them we add the events to this hub and you can find them by date and by location with a map of upcoming events. Another place to check is the demo page I’ve created on BenkoTips which shows not only upcoming events (aggregated from Community Megaphone, if you add it there it should show up on the map) but also User Group locations and links to their sites. That’s on http://benkotips.com/ug, then use the pan and zoom to focus the map on your city. Pins get added with the links. If your User Group data is out of date, send me an email & we’ll get it fixed.

We’ve got a series scheduled to run in thru May 2012 for Cloud Computing called Kick Start, which are a 1 day focused event that takes you thru the content from Soup to Nuts. The current schedule includes:

• 3/30 - Microsoft Edina Office
• 4/3 - Microsoft Independence Office
• 4/5 - Microsoft Columbus Office
• 4/10 - Microsoft Overland Park Office
• 4/12 - Microsoft Omaha Office
• 4/13 - Microsoft Mason Office
• 4/19 - Microsoft Southfield Office
• 4/25 - Microsoft Houston Office
• 5/1 - Microsoft Creve Coeur Office
• 5/1 - Microsoft Downers Grove Office
• 5/2 - Microsoft Franklin Office
• 5/3 - Microsoft Chicago Office
• 5/8 - Microsoft Edina Office

As to books I’d suggest checking out Sriram Krishnan’s book Programming Windows Azure: Programming the Microsoft Cloud, or Brian Prince’s book Azure in Action. If it’s SQL Azure that you’re after then Scott Klein has a great book called Pro SQL Azure (Expert’s Voice in .NET). I am also partial to the Patterns and Practices team’s book on Moving Applications to the Cloud on the Microsoft Azure Platform.

Finally you need an active Azure Subscription to get started. You can activate a 90 Free Trial by going to http://aka.ms/AzureTrialMB and get the tools at http://aka.ms/AzureMB.

The scenario is I’m migrating an application to the cloud. I’ve got a database connection defined in my web.config file which uses an on-premise SQL Server database and what I’d like to do is to move it to a Web Role on Windows Azure and use a SQL Azure database. The basic process is to add a Windows Azure Deployment Project to the solution that contains my web application. Next I move my database to SQL Azure (using the SQL Azure Migration Wizard), and then I change the connection string to point to the cloud database. Except that after I’ve deployed the project I may need to change where the database lives.

Since that information is stored in Web.config all I need to do is redeploy a new web.config file to the Web Role and we’re good to go. Unfortunately that probably means an upgrade to the application or a VIP swap, which isn’t all bad, but I know that I can make changes to the ServiceConfiguration file and propagate the changes to the running instances…no down time. But how do I get a change in the Service Configuration into the web.config?

I have posted before how you can encrypt information stored in the web.config file during a Session_Start event by adding code to the Global.asax file to examine a section and then call protect. If I can add some code to determine whether I’m running in Azure, and if I am to read the setting from the ServiceConfiguration file then we should be good.  Like the example of encrypting settings an approach that works well is to add code to the Session_Start event. For my example I’ve created a setting in the Windows Azure Role for dbConnectionString and set it to the value I’d like to use.

Next I make sure I add a reference to the Microsoft.WindowsAzure.ServiceRuntime namespace added to the web project so I can access information about the role. If I don’t have one already I add a global.asax file to my web project. This would be where I can add code for the events that fire periodically throughout the lifecycle of my app. I choose to use the Session_Start because if I’ve made changes to my ServiceDefinition file they will get applied the next time someone browses to my site. 

        public string dbConnectionString { get; set; }

        void Session_Start(object sender, EventArgs e)

        {

            // Are we running in Azure?

            if (RoleEnvironment.IsAvailable == true)

            {

                dbConnectionString = RoleEnvironment.GetConfigurationSettingValue("dbConnectionString");

                // Do we have a value for the alternate dbConnectionString in the ServiceConfiguraiton file?

                if (dbConnectionString != null)

                {

                    Configuration myConfig = WebConfigurationManager.OpenWebConfiguration("~");

                    ConnectionStringsSection mySection = myConfig.GetSection("connectionStrings") as ConnectionStringsSection;

                    if (mySection != null)

                    {

                        if (mySection.ConnectionStrings["myDBConnectionString"].ConnectionString != dbConnectionString)

                        {

                            mySection.ConnectionStrings["myDBConnectionString"].ConnectionString = dbConnectionString;

                            myConfig.Save(ConfigurationSaveMode.Modified, true);

                        }

                    }

                }

            }

        }

I check to see whether the value in the Service Configuration file is different than the value in my web.config, and if it is then make the change and save it. Initially when I tested the code I got a “the configuration is read only” error, but by adding the option for the Save method it works. Now I am able to update the database connection string from the Service Configuration File and have it propagate to my web.config of the running application.

Credit goes where credit is due, and Bing pointed me to a few posts on the subject, including StackOverflow and DotNetCurry.

Enjoy!

Sometimes I get questions about how to get better performance from a database. In working with SQL Server over the years and now SQL Azure this is not an uncommon question. In SQL 2008 and beyond the tools include a Tuning Wizard, which is great, but it relies on capturing a realistic sample of the database activity which you can get with SQL Profiler. Just go to the tool and run it, saving the captured trace to a table in SQL so you can look at it later and do some analytics.

Here’s some thoughts and ideas, for what they’re worth. First thing I would look at is to take a profile sample of the application running, which captures the queries and the statistics around which tables are being used and can be fed into the tuning utility to suggest indexes and keys. The second thing I would look at is whether a permanent working table would work better than a Temp table. The advantage is you have index capabilities, but the downside is truncating it and loading it when you need it.

Do you have flexibility with the schema to look at ways to pre-populate the data you need for the report during normal runtime of your system? For example if you are doing validations and transformations could these be scheduled to run periodically or even as the data transactions occur so that the work doesn’t have to be done ad-hoc to generate the report?

As to the query syntax I’ve found the “NOT EXISTS” clause to give better performance than the IN or NOT IN because of the way the optimizer creates and executes the plan.

Finally if you have complex queries are you generating them on the fly or can you create functions/stored procedures where the execution plan is pre-compiled?