Linking SQL database on Azure to Visio

Microsoft Visio has many data sources available for Link Data to Shapes, and one of those is SQL Server. Microsoft Azure is increasingly becoming a low-cost, and always available, choice for hosting all sorts of data, and applications, so I thought I would document how easy it is to setup a SQL database on Azure for use with Visio.

Creating the SQL database on Azure

First, you need register for an Azure subscription, and then create a SQL database on a server in a suitable location.

In my case, I have created a database called VisioFM on a server in North Europe, and I have highlighted the server name and port that I will use later in Visio.

Of course, I need to setup a username and password in order to create any tables, and to access any data.

I created three columns in my simple table which I named Floors.

Then I entered some data for the ground and first floor (or if you are American first and second floor ).

So, there it is, a simple database with one table, no views, and no stored procedures.

Linking the SQL database on Azure to a Visio document

I then create a new Visio document, selected Link Data to Shapes. and then chose a Microsoft SQL Server database.

I entered the Azure SQL server name and port from above, and entered the user name and password.

The Data Connection Wizard then allowed me to select the database on that server, and the table within it.

I set the properties of the data connection. 

I could then save this Office Data Connection (odc) file for future use.

Finally, the table appeared in my External Data window in Visio, and so I could use it to connect rows of data to shapes.

Conclusion

This scenario is useful when you want to link to a remotely available SQL database. You can setup multiple logins to the tables, views or stored procedures, with different levels of permissions.

Tables and views are available to select from the data connection wizard, but links to stored procedures have to be coded.

The data is refreshable in Visio, however, if you are hosting your Visio diagrams in SharePoint, and want them to be refreshable within the Visio Web Access control, then you will want to create the Azure SQL database as an External Type using Business Connectivity Services (see http://www.pointbeyond.com/2012/12/31/surfacing-data-from-sql-azure-in-sharepoint-2013-online-office-365 )