Creating users in a SQL Azure database

... is not as straight forward as you would think. It has some slightly different requirements for syntax than what you'd normally use to create users in a local or on-premise SQL Server instance.

You can see the tutorial page on MSDN here.

Debugging database deployments with MSDeploy and SqlPackage

I recently had problems deploying a database to Azure, and thanks to this article, I now know why. The best part of this experience is that I've learned to debug this in a much easier manner than relying on the unhelpful error message:

*** An error occurred during deployment plan generation. Deployment cannot continue.
Failed to import target model my-database-name. Detailed message Value cannot be null.
Parameter name: conn
Value cannot be null.
Parameter name: conn

The short of it is that:

1) you need to enable a log trace with these commands for dbDacFx and SSDT:

logman create trace -n DacFxDebug -p "Microsoft-SQLServerDataTools" 0x800 -o "%LOCALAPPDATA%\DacFxDebug.etl" -ets

logman create trace -n SSDTDebug -p "Microsoft-SQLServerDataToolsVS" 0x800 -o "%LOCALAPPDATA%\SSDTDebug.etl" -ets

2) Go execute the command that's causing you grief
3) Execute the following commands to stop the logging trace:

logman stop DacFxDebug -ets

logman stop SSDTDebug -ets

4) Open 'eventvwr' in the Run dialog available from the Start Menu
5) Go to the right side 'Actions' pane, and click on 'Open Saved Log'.
6) When prompted to convert the log, click 'No'.

The logs will display under the 'Saved Logs' folder in the left hand navigation tree, and you'll be able to inspect the errors that occurred. Good luck with deployment!

Using the MySQL EXPLAIN statement

Recently I had been having trouble with queries on a certain table in a system that I've been maintaining. I had gone through just about every excuse for why queries on the table could be performing so slowly : the machine was slow (DB running on a VM), the webserver was slow (also running on a VM), I wasn't using the native libraries (webserver was Tomcat), I had other processes running in the background (I didn't). Then I ran across a tip on a forum suggesting usage of the MySQL EXPLAIN statement. I had known all about it for the longest time, but it never occurred to me to actually use it (I think I'm that good at writing queries, turns out : I'm wrong). After using the EXPLAIN statement, I found out that the query processor was using a suboptimal query plan which utilized an index I had added with the intent of improving performance (the index had a fairly high arity, so choosing to use it was sketchy at best in the first place). Most DBMSs should have a similar functionality built in. I think that'll be the first place I go in future.