Within this series (well just two posts) I want to cover some of the available configuration options of the Azure API SQL Server connector. The connector simply enables you to connect with a SQL Server (as well as on-premises instances) or Azure SQL Database and perform various actions. This includes; executing stored Procedures, Poll data (triggers) and the option to retrieve, create, update and delete database table entries without having to write SQL.
SQL Server Connector API App – Some knowledge before we start
Now, lots can be said regarding the SQL Server Connector API App and perhaps not all that positive. Therefore I would like to quickly cover some of the drawbacks / pitfalls and some scenarios where it makes perfect sense to use the connector.
Pros and cons
The cons: As the name implies, the SQL Server Connector is a direct connection with your database. As you can imagine, this has several disadvantages compared with building a Web API Service / API App. First of all you won’t be able to tightly control the SQL statements executed on your database. The SQL Server Connector will generate SQL statements dynamically when specifying the Tables option (will be covered in great detail later). In addition it’s also possible to provide a predefined SQL statement for polling operations which also affects your database security setup. Therefore it’s important to have a security strategy in mind with matches with the operations of your SQL Server Connectors.
One other disadvantage is the lack of having a middle tier. This isn’t always an issue and highly depends on the amount of logic required within your scenario. In some cases it’s might be even better to encapsulate the logic within a separate Azure API APP (micro service). Just keep in mind that building lots of logic within the Logic App itself by using the Logic App Workflow Definition Language isn’t the ideal way to solve a complex problem however that’s my opinion.
More information regarding the Logic App Workflow Definition Language can be found here: https://msdn.microsoft.com/en-US/library/azure/dn948512.aspx
Another disadvantage, which might be there only temporarily, is the absence of having an option to alter the configuration once created. This is likely related to the fact that the metadata needs to be generated and might break existing Logic Apps. Changing the connection string in the future might result into some hairy situations…
The pros: There are also some use cases where the SQL Server Connector fits perfectly. When it comes to small or legacy applications and the additional logic is simple, using this connector might be a great time saver.
I’ve also used the SQL Server Connector within a monitoring and alerting scenario. I was pretty satisfied on how fast we were able to setup some ad-hoc monitoring and include our learnings within a more structured approach later on. We definitely want to avoid keeping track a bulk load of Connectors.
SQL Server Connector – Configuration samples
I will cover some common samples and pitfalls within Part 2.
Recent Comments