Jaa

SQL Server To Windows Azure helper library - A SQL Server open source CodePlex project

  • Artikkeli

Hi all,

since my last SQLRally presentation in Amsterdam people kept asking for a working build of the SQLCLR Windows Azure REST API  DLL. Many snippets of it can be found in this blog; however most of you don't really care about the implementation boilerplate. On the contrary, some of you might actually like to contribute to this library. In order to please everyone I've created - with the help of a fellow PFE Matteo Teruzzi - a GIT enabled open source project hosted on CodePlex. You can find it here (https://sqlservertoazure.codeplex.com/).

For those unfamiliar with CodePlex, just know that you can:

  1. Download a precompiled DLL to import in your Database (easy). You can also download a sample script to test the library in a matter of minutes.
  2. Download the source code to build your own copy (I'll explain in a moment why you should do that).
  3. Just browse come class straight from your browser.
  4. Post comments!
  5. Create issue tasks for the community to correct.
  6. Start a discussion for a feature to implement.

Right now the project needs - badly - a proper documentation (as it is now there's just an empty page) and a bunch of willful testers.

Please note that this is a pet project so I cannot guarantee any support. The full license is here (https://sqlservertoazure.codeplex.com/license); it's a the standard Mozilla Public License Version 2.0.

As I said the library can be used in its compiled form. I, however, encourage you to build you own version.

First of all you should replace the dummy snk file with your own. This is fairly straightforward, all you have to do is to right click every library and change the strong name key file:

If you don't have a strong name key file you can generate one using the sn.exe utility that ships with the Microsoft SDK. The syntax is just:

 sn -k <output_file_name.snk>

Second if you want to use the [Azure.Embedded] schema you need to customize the Connections.xml file embedded into the library:

As you can see in the image above there are two account already configured (a dummy one and the simulator). All you have to do is to add a Connection section for each account you want to be able to address without specifying all the parameters in the T-SQL (as I've done in my SQLRally presentation). For example, if you have an Azure Storage account called myazurestorageaccount and one of your shared keys is Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw== you can add a section like this one:

 <Connection>
  <LogicalName>azurestorage</LogicalName>
  <AccountName>myazurestorageaccount</AccountName>
  <SharedKey>Eby8vdM02xNOcqFlqUwJPLlmEtlCDXJ1OUzFT50uSRZ6IFsuFq2UVErCz4I6tq/K1SZFPTOtr/KBHBeksoGMGw==</SharedKey>
  <UseHTTPS>True</UseHTTPS>
</Connection>

Recompile your library, import it into SQL Server and you should be able to call the [Azure.Embedded] schema functions/SPs specifying azurestorage as logical connection name.

Note: you can find all those parameters in the Windows Azure management portal (https://manage.windowsazure.com) clicking the "Manage Access Keys button" in the lower bar:

Third you might want to expose only a few selected methods/SPs instead of all of them. In order to do so all you have to do is to comment the relevant method in ITPCfSQL.Azure.CLR and recompile.

Happy coding,

Francesco Cogno