Posts

Using Django with SQL Server LocalDB

How to use Django with SQL Server LocalDB

LocalDB is a version of Microsoft SQL Server Express Edition designed for developers. LocalDB is:

  • easy to install
  • requires no management
  • is compatible with other editions of SQL Server

LocalDB is also lightweight. The setup routine is just 45MB. LocalDB instances will start automatically upon the first connection request and stop automatically shortly after the last connection is closed.

Databases hosted on LocalDB are limited to 10 GB in size.

Follow these steps to install LocalDB, create a database, and connect Django.

Note: This post has been tested with Microsoft SQL Server 2017, Django 1.11.x and 2.x. The pyodbc and django-pyodbc-azure packages will be used to connect Django to SQL Server. The version of django-pyodbc-azure must match your version of Django.

Installing LocalDB

  1. Download SQL Server 2017 Express from https://www.microsoft.com/en-us/sql-server/sql-server-downloads.
  2. Run the setup routine and click Download Media.
  3. Choose LocalDB, specify a download location, and click Download.
  4. Click Close button to close the setup routine.
  5. Run the SqlLocalDB.msi setup routine.
  6. Click Next button.
  7. Accept the license agreement and click Next button.
  8. Click Install button.
  9. Click Finish button.
  10. Download most recent Cumulative Update for SQL Server 2017 (CU11 as of this post) from https://www.microsoft.com/en-us/download/details.aspx?id=56128 and run the setup routine.
  11. Accept the license agreement and click Next button.
  12. Click Next button on Features page.
  13. Click Next button on Check Files In Use page.
  14. Click Update button on Ready to update page.
  15. Click close button on Complete page.

Note: There is a bug in versions prior to CU6 that prevent databases from being created in the default location with LocalDB.

  1. Download Microsoft ODBC Driver 13.1 for SQL Server from https://www.microsoft.com/en-us/download/details.aspx?id=53339 and run the setup routine.

    Note: This is required for the Command Line Utilities for SQL Server
  2. Click Next button on Welcome page.
  3. Accept the license agreement and click Next button.
  4. Click Next button on Feature Selection page.
  5. Click Install button.
  6. Click Finish button.
  7. Download Microsoft Command Line Utilities 14.0 for SQL Server from https://www.microsoft.com/en-us/download/details.aspx?id=53591 and run the setup routine.
  8. Click Next button on Welcome page.
  9. Accept license agreement and click Next button.
  10. Click Install button.
  11. Click Finish button.

Creating database

LocalDB supports multiple instances. We recommend created a named instance for each Django project.

  1. Open command prompt as Administrator
  2. Create a named instance for your project (replace “Project” with the name of your project) 
    sqllocaldb create ProjectLocalDB
  3. Start the new instance 
    sqllocaldb start ProjectLocalDB
  4. Verify the instance is running 
    sqllocaldb info ProjectLocalDB
  5. Connect to the named instance 
    sqlcmd -S (LocalDB)\ProjectLocalDB
  6. Create login for new instance 
    create login my_user with password='my_password'
go
  7. Assign login to the admin role 
    sp_addsrvrolemember my_user, sysadmin
go
  8. Create user 
    create user my_user
go
  9. Create database 
    create database my_db
go
exit

Configure Django to connect to LocalDB database

  1. Install Django dependencies for connecting to SQL Server 
    pip install pyodbc
pip install django-pyodbc-azure

    Note: The version of django-pyodbc-azure must match your version of Django.

  2. Update DATABASES settings in your Django application 
    DATABASES = {
    'default': {
        'ENGINE': 'sql_server.pyodbc',
        'HOST': '(LocalDB)\ProjectLocalDB',
        'PORT': '',
        'NAME': 'my_db',
        'USER': 'my_user',
        'PASSWORD': 'my_password',
        'OPTIONS': {
            'driver': 'ODBC Driver 13 for SQL Server',
        },
    },
}

Note: Django will connect to a SQL Server database with ODBC Driver 11, 13, and 17, but version 13 is required for the sqlcmd utility included in Command Line Utilities 14.

 

/by

MS Access Databases: Make the Move?

Once upon a time…

MS Access databases were everywhere!

In fact, Microsoft Access databases are still used in lots of small-to-medium businesses because the applications developed years ago still work.

The obvious question then is…

If your MS Access application still works,
why would you change anything?

Because old technology – even if it’s technically FREE – isn’t really free. The costs are just hidden. We covered this topic in our article about the Care and Feeding of Custom Software and also where we talked about Microsoft end of support for Windows 7 and Windows Server 2008.

In a nutshell:

  • You’re missing out on modern efficiency gains.
  • You may be creating risks – especially in the areas of security, upgradability, and supportability

MS Access has many great features.

We’ve built many applications in Microsoft Access over the years. We liked MS Access because:

  • It’s inexpensive (the license is FREE) and requires no special hardware to run.
  • It has a graphical interface, which makes it easy to use.
  • It’s an easy all-in-one simple solution, including extra features like a built-in form creator and report writer.

On the downside… despite Microsoft’s upgrades to the product, MS Access is unsophisticated and unwieldy compared to other options available today.

  • It’s not very scalable. It’s doesn’t work well with more than about 10 concurrent users and can’t handle high-volumes of data or complex algorithms.
  • Data security can be problematic. Role-based security features have been removed from the application.
  • It’s not web-friendly.
  • Most software developers don’t want to work with MS Access databases.
  • It’s difficult to connect to other small business software solutions and SaaS offerings. There’s no API connectivity.

Microsoft Access has been around for more than 25 years now. It’s had a great run. But in our opinion, it just can’t compete with newly developed web-centric solutions.

Before switching to another custom software application, the first step is to see whether a small business software application already exists that would meet your needs. When one doesn’t exist, we’ve had several clients move off of MS Access to Django.

Their typical reaction is “Why didn’t we switch sooner?!”

MS Access Migration Examples

Client 1: Needed better software integration between QuickBooks & CRM

This client had created a customer relationship management (CRM) system in MS Access. All their financial information was in QuickBooks. The problem was that they couldn’t get a full picture of the client. When the client called in to request additional services, employees couldn’t see that they had a long overdue unpaid bill. We built a custom application for them that gave them real-time access to customer history, without requiring extra QuickBooks licenses or logins.

Client 2: Needed to run on a non-Microsoft platform, specifically Linux & Apple

This client had Linux-based thin clients in their shops, and wanted employees to be able to quickly look up information through iPads. Moving from MS Access to Django made it possible so they could login to their application from any web browser.

Client 3: Needed mobile integration with a cloud-based small business software solution (SaaS API Integration)

In this case, the sales team would touch down in a city and want to be able to find the best clients within a radius of their current location. Their MS Access database was only accessible from the home office. Printing lists and maps was ridiculously inefficient. With the new system, they can create route maps to better plan their sales day. They can also integrate with Microsoft Word and Office 365.

Other clients have moved off of Microsoft Access because they have too much data or too many users.  One of the best reasons for working with Eclipse Consulting is that we don’t prescribe THE answer. We ask the right questions to help you get the best answer for YOUR business.

technology business

/by