Lessons Learned with MS SQL Server


I am using DreamFactory in Azure to connect to a Microsoft SQL Server 2012 that is also running in Azure. I learned a few things today that I thought I would share.

First, your Azure SQL Endpoint has both an external and internal port number. Plug the external port number in after the server name in the connection string. You did create a SQL endpoint for your Azure SQL Server, right?
For example: SQLDb1.cloudapp.net:1433

Second, spaces in your database, table, or field names will drive you crazy. You should rework your schema so there are no spaces in the names. This will make your life easier. Quotes and bracket may work in the MS world to compensate for spaces but not (that I could make work) in DF.

Third (and this is a guess) is the Dreamfactory will grab the schema when you first build the service. I was dynamically changing the schema using SSMS on my server while testing things in the Live API. It drove me crazy until I turned on SS Profiler and watched what queries were actually hitting the server. After I adjusted the names I deleted the service and created a new one and everything worked as I expected.

Dreamfactory SQL server on Azure
Failed to launch service