Sorry to have missed meeting you at DjangoCon, Meet, but I'll add my
findings here to the record.
By way of background, I work at The Wharton School, where we're a
Python/Django (on RHEL) and SQL Server shop. I was responsible for
implementing a working configuration for Django, starting with version 1.6,
including building Vagrant boxes for developers which had to be 'vagrant up
plug-and-play' ready to go. We've had pretty good success with the stack
we're now using, but hit quite a few pain points and found many points
where things can very much be improved.
First, drivers at the Linux level.
- We've had our best luck with FreeTDS and unixODBC for reliability.
We've been using it successfully throughout the process.
- We found the MS provided driver more performant, specifically when
dealing with large numbers of inserts.
- The MS driver also offered features like bulk-loading of data,
which while not Django specific, would have still been a big win for our
team using Python without Django.
- FreeTDS + unixODBC runs under any Linux distro we tested
(RHEL/CentOS, Ubuntu, Debian) and Solaris.
- As mentioned, we found the MSODBC Driver for RedHat
(http://www.microsoft.com/en-us/download/details.aspx?id=36437) to be more
performant, however, ran into quite a few issues starting with Django 1.7.
- There seem to be issues with multi-threading. Starting with Django
1.7, we had to run Django's runserver with the '--nothreading' option.
- We ran into further issues with SQLRowCount returns from the
driver. These rendered it unusable.
- Only supports RedHat, but also works on CentOS. We'd prefer one
that at least also works on Ubuntu/Debian (a definite must for the Django
community).
- The driver is closed-source.
- A note about FreeTDS: while FreeTDS 0.95 support TDS Version 7.3, as
does pyodbc, we had to stick with TDS Version 7.2, as any stack we tried
mapped to incorrect column types when switching to 7.3, which supports new
SQL Server 2008 column types. I haven't had the time to dig deeper, map
correctly, and issue a P.R. on this one. Additionally, TDS version numbers
are very confusing to users (see my Stack Overflow responses, heh). At one
point in time, FreeTDS has named the new TDS version "8.0" before Microsoft
made an official declaration; Microsoft then called it "7.2". While often
this will not affect configuration, now that 7.3 is support in FreeTDS
0.95, if "8.0" is entered in configuration, it is now default to "7.3"
which causes issues, and the configuration in Django, odbc.ini, and
freetds.conf must all explicitly state "7.2".
Regardless of the driver chosen (FreeTDS+unixODBC or MSODBC), we ended up
having to use pyodbc instead of pymssql:
- We have a test suite performing table creates / destroys, basic CRUD
operations, stored procedure execution, and more against both pyodbc and
pymssql.
- pymssql outperforms pyodbc significantly against SQL Server,
especially on SELECT and INSERT operations.
- pymssql on Linux offers no stable Django options, as noted throughout
this thread.
- pyodbc offers several options.
- we initially started using django-pyodbc (lionheart on GitHub),
which worked but required quite a few tweaks to the settings.
- we moved to django-pyodbc-azure, which we found a much easier
install / Django DATABASES {} configuration, and is kept up to date in a
timely fashion.
To summarize, here's what we now use:
- FreeTDS 0.91 - 0.95 (dependent on RedHat/CentOS version)
- unixODBC (dependent on RedHat/CentOS version)
- pyodbc
- django-pyodbc-azure
It works well for us, but we've had to make compromises, and the promise of
better performance we've seen in certain scenarios is tempting. If I were
building a wish list, here's what I'd like to see, for performance and
ease-of-installation:
- Native driver to replace FreeTDS + unixODBC for SQL Server connections
that is supported and runs on more than just RedHat/CentOS, preferably open
source!
- Easy, prompt free option for install: I had to hack to install to
avoid having to respond to interactive prompts in Vagrantfiles, Chef
recipes, etc.
- Eventual inclusion in EPEL, etc, for yum or apt-get installs.
- Python package (to replace pyodbc with one that supports SQL Server
functionality and performance)
- Django-Python package (to replicate django-pyodbc-azure's mappings to
the pyodbc replacement)
- Support bcp, SSIS, DATE data type, FILTERED and SPATIAL (GeoDjango,
anyone?) index types, easy Stored Procedure calls, OUTPUT variables, and
more I can't remember off the top of my head.
These would also be big wins for users of other languages / frameworks,
such as PHP and Ruby web frameworks, Flask, etc, who use SQL Server.
So there's a quick brain dump, sorry for the wall of text, but we've done a
lot of research in this area. Please let me know how I can help - and thank
you to the efforts of everyone who's done some amazing work to keep Django
plugged into SQL Server.
Regards,
Tim A.
On Wednesday, September 2, 2015 at 10:15:10 PM UTC-4, Meet Bhagdev wrote:
>
>
>
> On Wednesday, September 2, 2015 at 3:05:23 PM UTC-7, Ramiro Morales wrote:
>>
>> Hi all,
>>
>> On Fri, Aug 21, 2015 at 2:39 PM, Meet Bhagdev <meetb...@gmail.com>
>> wrote:
>> > Hi Django Committers,
>> >
>> >
>> >
>> > My name is Meet Bhagdev, I work in the Database Systems engineering
>> team at
>> > Microsoft in Seattle, WA. My focus is the APIs used to connect to and
>> use
>> > Azure SQL Database and SQL Server (MSSQL). Example APIs are ODBC, JDBC,
>> > ADO.NET, etc.
>>
>> (sorry for possibly repeating things folks have already posted to this
>> thread)
>>
>> Meet: Is this list of APIs the final and full one?
>>
>>
>
> *This is definitely not a final and full one. We have actually started
> endorsing pymssql and FreeTDS to our Linux and Mac customers who want to
> use Azure SQL. *
>
> * We have also created documentation that lets customers use pymssql
> on *
>
> * 1.
> Windows:
> https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/
>
> <https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/>*
>
> * 2.
> Linux:
> https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-ubuntu-linux/
>
> <https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-ubuntu-linux/>*
>
> * 3.
> Mac:
> https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-mac-osx/
>
> <https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-mac-osx/>*
>
>
> * Also a big thank you from everyone on our team here for working on
> FreeTDS and pymssql. I definitely makes our customers lives easier*
>
>
>> Because there is work being done on a different stack, the one formed by
>> :
>>
>> * FreeTDS (http://www.freetds.org/ GPL licensed) which implements the
>> wire-level TDS protocol.
>> * pymssql (http://pymssql.org LGPL licensed) -- Python bindings for
>> FreeTDS which implements the Python DB-API 2.0. I'm part of the team
>> maintaining it
>> * django-pymssql (https://github.com/aaugustin/django-pymssql ,
>> MIT-licensed) which was created by Aymeric Augustin and depends on
>> pymssql plus ...
>> * django-mssql (https://django-mssql.readthedocs.org/en/latest/ ,
>> MIT-licensed) which was created by Michael Manfre
>>
>> *I am currently exploring django-pymssql, django-mssql, and
> django-pyodbc-azure to see which adapter(s) can we adopt to provide Django
> and MSSQL/Azure SQL support. Do have any opinions as to which one(s) should
> we contribute to eventually? *
>
>
>> Michael, Aymeric and me are Django development team members.
>>
>> Personally, I've been working on stabilizing the lower layers by
>> following a "yak shaving" non-strategy:
>>
>> We (the pymssql team) realised the official pymssql Windows binaries
>> (in particular the FreeTDS libraries) we published when releasing
>> pymssql 2.1.1 don't link in a SSL implementation and so they aren't
>> usable to actually connect to Azure even if the pymssql code iself has
>> such ability.
>>
>
> *I looked around and it seems like this problem was fixed on this page
> by Christoph Gohlke from University of California, Irvine:
> http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql
> <http://www.lfd.uci.edu/~gohlke/pythonlibs/#pymssql>. I downloaded pymssql
> from here and it seemed like it links the SSL implementations. I also
> documented the procedure here
> <https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/>:
>
> https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/
>
> <https://azure.microsoft.com/en-us/documentation/articles/sql-database-develop-python-simple-windows/>*
>
>>
>> This, plus the fact that the manual process of creating the actual
>> matrix of Windows deliverables is a bit tedious led me to try using
>> the AppVeyor.com hosted Windows CI platform (free for open source
>> projects) to test and actually build the binaries. The work in
>> progress on this can be seen at
>> https://github.com/ramiro/pymssql/tree/appveyor and
>> https://ci.appveyor.com/project/ramiro/pymssql
>>
>> (When working on pymssql 2.1.1 at some point I created a free Azure
>> account with my credit card, tested (on Linux) the pymssql
>> implementation of connection changes needed to get it to work against
>> Azure's SQL Server and cancelled it before it started billing.)
>>
>> *Do you still need Azure credits to run Azure SQL for free, I can try to
> get some free credits for you and the FreeTDS and pymssql team depending on
> the need. *
>
>
>> So, this led me to start contributing to FreeTDS so to get it back to
>> build cleanly on Windows (work partially included with the 0.95
>> release back in June) and to also get it built/tested using
>> AppVeyor.com using the experience gained with pymssql. This has been
>> already merged in the current FreeTDS development code and allows the
>> maintainer and contributors to work without access to Windows/SQL
>> Server licenses (my case).
>>
>> - https://ci.appveyor.com/project/freetds/freetds
>> -
>> https://github.com/FreeTDS/freetds/commit/3db5caa48f281f3558d4031cb5a0f0d8e8eef28c
>>
>>
>
> * This is great to hear. Getting it on Appveyor definitely makes it easy
> to build on Windows. *
>
>
>> I know MS dropped support of the DB-Library (and hence the API it
>> provides, of which FreeTDS is a open source implementation) back in
>> the SQL Server 2005 times. That's why I ask if this stack has any
>> chance of getting some support from Microsoft.
>>
>> *We are very much looking at providing support(as you can tell) from our
> documentation mentioned above. We are currently in the prioritization stage
> and would love to discuss some of the things you need support for. As for
> now, we feel Django is a good place to start, as there is customer demand
> and we are falling behind. Would have a document which highlights the todo
> feature list where you need help on? I can pass it on to my team to get
> some responses so that we can get the ball rolling.*
>
>
>> Personal motivation for this is simply to get Django (running on
>> Linux) + SQL Server to be a viable choice, even when I currently have
>> no actual need of this. I was one of the two developers behind the
>> original django-pyodbc project which had reached "almost full Django
>> test suite passing" status back in the Django 1.0 & 1.1 (2008-2009)
>> times, but abandoned it when discoverd (by logging the traffic with
>> SQL Servr tools) that the combination of pyodbc + Linux ODBC stack
>> meant the queries were sent twice to the DB server, see
>> https://code.google.com/p/django-pyodbc/issues/detail?id=16 . This is
>> also why I started considering a FreeTDS-based solution a better
>> technical choice.
>>
>
>
> *That is a great find. I actually was not aware of that. The good news for
> the Linux side of the story here is that we are releasing a new Linux ODBC
> driver in the next 2 months. It would be great if we can test this out and
> see how it goes.*
>
>>
>> I'm posting a message to the FreeTDS mailing list later today pointing
>> to Meet's post which opened this thread.
>>
>
> *I look forward to the response :) Please let me know if you have any
> questions*
>
>>
>> Regards,
>>
>> --
>> Ramiro Morales
>> @ramiromorales
>>
>
--
You received this message because you are subscribed to the Google Groups
"Django developers (Contributions to Django itself)" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to django-developers+unsubscr...@googlegroups.com.
To post to this group, send email to django-developers@googlegroups.com.
Visit this group at http://groups.google.com/group/django-developers.
To view this discussion on the web visit
https://groups.google.com/d/msgid/django-developers/5b8413d8-0eaa-435e-a76b-71a9c82d36d5%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.
