Django + pyodbc + MS LocalDB ghost instance

Question

I'm trying to set up a Python project with a LocalDB instance. Because of business restrictions, I'm tied to a MS SQL Server 2014 backend to use with Django 1.8 and I'm trying to connect to it with pyodbc drivers. I want to use a LocalDB instance instead of SQL server for development, but I'm running into some problems. I got the connection working with the following settings.

Django settings:

DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'NAME': 'my_db',
        'HOST': '(localdb)\\hands-on',
        'Trusted_Connection': 'yes',
        'PORT': '',
        'OPTIONS': {'driver': 'SQL Server Native Client 11.0',
                    'Integrated Security': 'true',
                    },
        'TEST': {
            'NAME': 'test_my_db',
        },
    }
}

I created this instance with my SqlLocalDB.exe:

Name:               hands-on
Version:            12.0.2000.8
Shared name:
Owner:              COMPANY\MY_ACCOUNT
Auto-create:        No
State:              Stopped
Last start time:    27-12-2016 16:53:55
Instance pipe name:

I created my_db in the instance with SSMS and the connection is made. Presto!

However, there is a bit of an issue. When I start the Django application, it automatically starts the LocalDB instance (I can see a sqlservr.exe running in Task Manager under my user account). But when I run SqlLocalDB.exe info hands-on, the output is

State:              Stopped

When I open SSMS and connect to (LocalDB)\hands-on the connection is made just fine but in my Task Manager I can now see a second sqlservr.exe running, to which it turns out I'm connected now. So Django / pyodbc and SMSS / SqlLocalDB.exe somehow seem to be interacting with two separate instances of the same LocalDB instance which both are quite happy to report is called hands-on.

I have found a workaround which consists of deleting the LocalDB instance and its files and then recreating and starting it with SqlLocalDB.exe, and only then connecting to it with my Django app. Then all software does appear to interact with the same instance and only one sqlservr.exe process is running. Sadly this workaround has the big drawback of not working anymore as soon as you make a mistake and run Django before SqlLocalDB.exe start hands-on. The ghost instance is then recreated and from that moment on Django keeps connecting to it, even when you kill the instance, start it with SqlLocalDB and then reconnect with Django; the ghost instance will show up again. So this workaround is very brittle and I can hardly go and recommend it to my colleagues.

Does anybody have a clue what is going on and how to solve the problem? Apart from not attempting to combine Django and SQL server, I mean :p

Answer 1

I never managed to find out what the problem was, but in the end I decided to work around the problem by running a little script at login that pops up a LocalDB instance before any work can be done, and sets its auto-shutdown timer to high value so that it doesn't shut itself down unnoticed. Here's the bat script for Windows:

"C:\Program Files\Microsoft SQL Server\120\Tools\Binn\SqlLocalDB.exe" s MSSQLLocalDB

@ECHO OFF

ECHO. > temp.sql
ECHO sp_configure 'show advanced options', 1; >> temp.sql
ECHO GO >> temp.sql
ECHO RECONFIGURE >> temp.sql
ECHO GO >> temp.sql
ECHO sp_configure 'user instance timeout', 65000; >> temp.sql
ECHO GO >> temp.sql
ECHO RECONFIGURE >> temp.sql
ECHO GO >> temp.sql
ECHO EXIT >> temp.sql

ECHO.
"C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn\SQLCMD.exe" -S (localdb)\MSSQLLocalDB -E -i temp.sql
del temp.sql