Posts Tagged ‘Azure’

How to add an FAQ bot to your website

Hosting an event? Attendees always have questions, you can bury them on your website or you can add a chat bot for quick Q&A based on your existing FAQ!

In this post I will show you the steps to

  • Create and test a FAQ knowledge base
  • Create and deploy a bot that connects to your knowledge base
  • Get a hyperlink so your website can launch a chat with the bot

This tutorial uses Azure so you need an Azure subscription to continue. If you are a student you can sign up for free Azure without a credit card at . If you are not a student you can sign up for a free Azure trial.

Create and test a FAQ knowledge base

Azure has a tool called QnA Maker which allows you to create a knowledge base automatically from an existing set of FAQ.

Visit the QnA Maker site and select Create a knowledge base from the top menu.


Select Create a QnA service.


You will be redirected to the Azure portal. Sign in using the account associated with your Azure subscription.

TIP: If you are redirected to a different Azure page after signing in, just go back to the QnA Maker page and select Create a QnA Service again to bring back the blade for creating a new QnA Service.


  1. Service Name
  2. Select the Management pricing tier (TIP: the ones that start with “F” are free, you can switch to a paid tier later when you need to support more calls per minute)
  3. Select your pricing tier (TIP: the ones that start with “F” are free, you can switch to a paid tier later when you need more storage)
  4. You can change the App name if you wish, it defaults to the name of your service. The name must be unique across all Azure users since it will determine the URL of your service.
  5. You can change the other values if you wish or leave them at their default values then select Create.


After a minute or two your service will be created. Go to  Notifications in the top right corner and select Go to Resource.


Now that you have a QnA Service you can return to the QnA Maker site and create your knowledge base. Refresh the page so the site picks up your recently created QnA Service.

Select your Azure directory, subscription name and services using the drop down lists.

Then enter a name for your Knowledge base


Provide the URL for one or more web pages with your FAQ or upload one or more files containing your FAQ.

Select Create your KB to create your knowledge base.


You can add additional questions by selecting + Add QnA pair


Type in a question the way you think a user might ask it and the suggested response.

TIP: Use the + symbol for any question to add different phrases that might be used to ask the same question.NewAnAPair

Once you have the questions and answers you want, select Save and train from the top menu to save and train your QnA service model.

SaveAndTrain Select Test to test your trained service. Enter a question and see what comes back. Note you do not have to type in the questions exactly as they appear in the FAQ, that’s because the service is using LUIS (Language Understanding ) to figure out the intent of each question.


When you are finished testing select Test again to collapse the testing window. You can go back and forth adding new questions and phrasing then retraining the model as often as you wish until you feel the most common questions from users will find a suitable response.

When you are satisfied with your responses select PUBLISH from the top menu and select Publish to publish your service and get an endpoint you can use to call the service.


Once it is deployed you will see the screen below. You can Edit then retrain and republish your service as often as you want.

You will need some of the details in the HTTP request shown on this screen to create a bot that connects to your service.


Congratulations you have successfully deployed a QnA service. Your next step is to create a Bot and connect it to your service.

Create and deploy a bot that connects to your knowledge base

Return to the Azure portal

Select Create a resource and search for Web App Bot


Select Web App Bot from the list of search results then select Create


Enter a Bot name and a pricing tier

Select your Bot template:

  • Change the SDK version to SDK V3
  • Choose either C# or Node.js as your SDK language
  • Choose the Question and Answer template


While you are waiting go back to the QnAMaker and select My Knowledge bases

Select your knowledge base

Go the Settings tab

Scroll down to the Deployment details and find the

  • KnowledgeBaseID – This value will go in the QnAKnowledgeBaseID field
  • EndPointHostName – This value will go in the QnAEndpointHostName field
  • EndPointKey – This value will go in the QnAAuthKey field


Once your Web app bot is created, select it in the portal and choose Application Settings

Scroll down until you see the Application settings

Select Show Values to see the individual application settings and enter the values you looked up for

  • QnAAuthKey
  • QnAEndpointHostName
  • QnAKnowledgebaseId

Don’t forget to select Save after you enter the values


You can test to ensure your Bot is connected to the knowledge base by selecting Test in Web Chat. Type in a few questions, you should get the same responses you saw when you tested in QnAMaker. If you do not get an answer, you can try republishing the QnA Service in QnA Maker and double check the app settings are copied correctly.


Get a hyperlink so your website can launch a chat with the bot

Now that you have a bot, you need a way to call the Bot.  There are many different ways to call a bot. You can set up channels to call the Bot from Skype or Facebook. Some platforms are quite a bit of work because you need authentication keys from the applications and you have to go back and forth connecting the application to your service passing keys back and forth.

For many of us, the ability to call the chat bot from a website is sufficient. Let me show how to get a hyperlink you can use to launch or an iframe to embed the bot into your website.

Open your bot in Azure and select Channels 

Select Get bot embed codes from the Channels blade


On the pop up page select Click here to open the Web Chat configuration page


Select Show to display your secret key.

If you want to embed a chat window inside your website copy everything in the Embed code to your webpage. Replace the text YOUR_SECRET_HERE with the secret key from the text box above.

If you want to open the chat window in a separate window, just create a hyperlink that points to the URL https://webchat.botframework/embed/YOURBOTNAME?s=YOUR_SECRET_HERE that appears inside the embed code. Replace YOUR_SECRET_HERE with the secret key from the text box above


If you open the hyperlink you will get a new tab with the chat window


If you embed the code in an iframe it will appear within your web page. You will probably want to set the height and width properties of the iframe.


<h1>Look a chat bot</h1>

I added this to my web page

neat eh?


Congratulations! You have just created and deployed a chat bot that will answer frequently asked questions using natural language and artificial intelligence!

Find out more about




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

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
    This post is also on the Canadian Solution Developer blog

SQL Azure Essentials for the Database Developer

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.



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.


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

This post is also featured on the Canadian Solution Developer Blog