• Introduction
• What options are there that could be used for development work?
• What do developers want?
• How different are the editions
• Does the choice affect the tools that are used?
• LocalDB
  • Benefits of LocalDb for development
  • Negatives of LocalDb for development
• What other editions are there?
  • Express Edition
  • Developer Edition
  • Web, Standard, and Enterprise Editions
  • Azure SQL Database
• Containerized version of any edition
• Conclusion

Introduction

As a developer, I need a SQL Server database against which I can develop and test code. There are several options to choose from, and various issues that you might need to bear in mind. In this article we will look at the choices, and the decisions you need to make. The LocalDb edition of SQL Server was created to be the obvious edition for developers; Does that idea make practical sense and what alternative advantages come the other editions for developers?

Development work will usually require a number of server instances. Should these be shared on one server, or distributed on a number of servers or Virtual machines? Should all development servers be of the same edition? Is it wise to rely on an internet connection to base all your development servers in the cloud, or is there a place for the traditional ‘SQL Server on the laptop’.

Before SQL Server 2005, it wasn’t hard to choose the edition that you’d use to develop with. Other than the product itself, there was just a free developer edition. It then got more complicated, and continues to do so, with some interesting permutations coming for developers with the introduction of containers to Windows 2016 and SQL Server 2016.

What options are there that could be used for development work?

Currently there is:

• Express Edition
• Developer Edition
• Web Edition
• Standard Edition
• Enterprise Edition
• LocalDb
• Azure database + Amazon RDS
• Containerized version of any edition

There is also an edition called Compact Edition which has been deprecated but is still available. It has some similarities to SQL Server but has a very small footprint and runs in-process. Compact Edition runs a SQL dialect, but it isn’t an edition of SQL Server. It is really not an appropriate development instance for developing and testing code that will be deployed to SQL Server.

What do developers want?

1. Productivity
   I am most productive as a database developer when I have my own instance on my own machine that I can stop, start, drop and create at will. When the database or instance is on a shared server then I find that development is slower – either other people change things that I am working on or do other things to interrupt, what should be, a fast develop, deploy and test cycle.
2. Simplicity
   A developer should be free to spend their time writing, testing and debugging code and any time spent managing resources like SQL Server databases means that they have less time to do the things that are actually useful, and provide value to the business.
3. Accuracy
   In an ideal world every developer would have a fast, local, exact replica of production with data that is as close as possible to production data. With this, they can test that their changes will behave the same way in production as they do in their development environment. There must be countless applications that work super-fast on a developer machine but slower when deployed in a 3-tier architecture with lots of latency and network calls between the services. It is also important to verify that the code you write for one database will actually run on another database. If you had SQL Server 2008 R2 in production, there would be no point developing with SQL 2016 using in-memory tables.

How different are the editions?

When developing and testing a database application, it is often important to know that, when we are writing code and we can see how it acts in development, we will get a similar behaviour in production. The editions are defined by the features that are installed and enabled; the code in the core engine is the same whether you use one of the free editions or the enterprise edition. The problem comes when you use, say, an enterprise-only feature in development but have only a Standard-edition instance in Production. The full comparison of these editions is published by Microsoft here ‘Features Supported by the Editions of SQL Server 2016’

Does the choice affect the tools that are used?

All of the instances behave the same in terms of the core engine and language; if, therefore, you have a tool that can manage Enterprise edition then it can, in principle, also manage every other edition. Not all tools in the past kept to this principle: There was once a version of SQL Management studio for SQL Express that was restricted to not being able to manage the full edition’s features. Now SQL Server Management Studio is backwards-compatible to previous versions and sideways-compatible with all editions. It is currently available as a free download so you can manage any instance without requiring a full license.

The second limitation is that, in order to use LocalDb, you need to change the SQL client library and so older version of SQLOLEDB and .Net are unable to connect – if you have an application that was written in .net that is earlier than version 4 then it may not connect.

What choice of editions do we have to develop on?

LocalDb

Unless you have a specific requirement that LocalDb cannot meet, then it must be the first choice for developers. Lets look at what it is and then what benefits it offers.

LocalDb is a cut down version of SQL Server that was specifically designed to be light-weight and easy to start and stop quickly. This means a compromise so there are some restrictions for it, but it really is a great choice for most developers because you can quickly create, use and destroy instances that are specific to one particular user.

LocalDb has some additional benefits: It shares the same binaries for each instance so you do not have to use up lots of disk space for each instance that you need. This means that it is really simple to start one up if you find that you need a new instance in the heat of a development session.

Creating a new LocalDb is simple: You just type the following at the command shell:

This will create a new instance that you can use called “instance name”, on my machine it takes about 4 seconds to create a new instance and then about another 2 seconds to start the instance. Installing a new instance of any of the other types of SQL Server is measured in minutes to hours.

If you have multiple versions of LocalDb installed, you can create any of those versions really easily, just by executing this code in a command shell:

You will get a list like:

Then if you need a 2012 instance you would type:

You would then get a new SQL Server 2012 instance you could connect to.

Connecting to LocalDb is a little bit different than connecting to a standard instance: Instead of connecting to the machine name and either passing in a port or an instance name you pass in the word “(localdb)” and then the instance name so if the instance is called “test-instance” you would pass this as your server name:

To find out which instances you have you can use:

This will list the LocalDb instances, to examine one further pass in the instance name to the last command such as:

The output will be

• the name
• version
• which user it belongs to
• whether it was started or not
• the last start time

If it is started, it also gives the path of the named pipe it is running on. If you have a client that does not understand the (localdb) servername then perhaps you can connect over named pipes to the instance.

LocalDb is great for developing against because it is so fast to create and start instances, if you often work on different projects you can stop and start instances and have lots of different instances available without the overhead of having to maintain them and store them on disk. They obviously have the same down-sides of Express in that they are cut down versions of SQL Server. There is, for example, no full text indexing, which can stop them from being able to be used in some applications.

Benefits of LocalDb for development:

• Fast development, it is simple to create new instances for development and testing.
• Shares binaries between all instances of the same version so you do not take up lots of disk space or have to maintain many different versions

Negatives of LocalDb for development:

• Does not include some fundamental features such as SQL Agent
• does not support FILESTREAM
• cannot be a merge replication subscriber,
• only allows local queues for Service Broker.
• always runs under the users security context;

What other editions are there?

Express Edition

The Express edition is a free version of SQL Server that is intended for small databases with a low number of users. In the 2016 version Express is limited to four cores, 1GB of ram per instance and a maximum database size of 10 GB. Although it is a reasonable size it is not large enough for most production database applications, but it is useful for development work if your development instance does not require many resources and is small, and if you are not developing any functionality that depends on Standard or Enterprise-only features..

Benefits of Express for development:

• Performance problems will show up more conspicuously in development
• There are no license complications for development work
• It is suitable for both individual and shared development.
• Network services can be configured just as with the full editions, so you can connect with tcp from remote machines and can be used by the SQL Browser service to allow connections using instance name
• It can be freely downloaded and installed

Negatives of Express for development:

• Does not include the full feature set of one of the production versions
• Requires a full standard installation to create a new instance, this takes time to install and upgrade and quite a lot of disk space per instance
• Should not be used for load or performance testing

Developer Edition

Developer edition is a fully-functional Enterprise edition of SQL Server which has all limitations removed. This is great if you are developing an application that uses Enterprise edition, but if you are developing an application that uses Standard edition then you may find that you get better performance than you would in production or you end up using a feature that is enterprise only. It can be a costly mistake to develop code, only to find that it cannot be run when you deploy it.

If you work on a single database application and will be deploying to an existing server that is using the Enterprise edition, then you cannot go far wrong if you use the development edition of SQL Server.

Licensing for SQL Server 2000 meant that developer edition was free as long as the use was for development and testing rather than as a production database. If you had decided to use developer edition for production, then it would have been priced as the enterprise edition. When SQL 2005 as released, a change to the licensing meant that there was a small fee of $50 per developer to be able to use the developer edition. This meant that quite a lot of people were unable to use the development version especially in teams where developers only used SQL rarely or where they did not have MSDN subscriptions which supplied developers with a license for developer edition. This was especially noticeable in non .net teams that happened to use SQL Server.

According to the SQL Server 2016 licensing guide, “SQL Server Developer Edition may not be used in a production environment or with product data” – assuming “product data” means “production data” then it seems that it is not possible to backup and restore a production database to test against unless you license the server as a production server. The guide also states that developer edition can be used for free if you join the “Dev Essentials” program – if you need a different edition such as Enterprise or Standard to develop/test against, then you can access them using a “Visual Studio” subscription.

Benefits of Developer edition for development:

• Developer edition is a fully fledged Enterprise edition so if you are testing and it works on Developer then the code should behave the same as your production Enterprise server.

Negatives of Developer edition for development:

• Only appropriate for developing a database for Enterprise Edition. Because some of the features and behaviour of Developer edition are missing on Web or Standard Edition, Your production system will have fewer features than your development environment which is never a good idea.
• It uses quite a lot of disk space per instance
• Licensing is not clear, so requires a check with your Microsoft licensing representative

Web, Standard and Enterprise editions

These versions all require a license to be used in production. You can only buy the web edition through application hosting providers. It is often safest to develop against the edition of SQL Server you will deploy to, certainly for any performance or scalability tests if not for every-day development. As discussed with the Development edition, the licensing is a little unclear and you may potentially have to pay for a license to use one of these editions in a dev or test environment.

The web edition is not generally available and you normally have to buy licenses for it through a hosting provider which adds some additional complexity.

Benefits of production editions for development:

• You will be developing and testing in an environment that is similar to production, which is nearly always a good thing for developers.

Negatives of production editions for development:

• Requires a full standard installation to create a new instance: This takes time to install and upgrade, and quite a lot of disk space per instance
• Licensing is not clear and requires checking with your Microsoft licensing representative

Azure SQL Database

Using cloud providers to host databases for you can make real sense when developing and testing but they do have some downsides. Aside from the price that you need to pay to use them you also have to understand that the latency between your machine and the database will typically be significantly higher than a local or even LAN local instance. You will also need a persistent connection to the database so those evening commutes finishing off a piece of work on the train is unlikely.

Using these for pure development work where you are constantly deploying changes and running scripts is probably not ideal but they can really come into their own for testing especially as part of automated test suites that run in hosted providers such as Appveyor or VSTS.

Having the ability to create a new instance, deploy your code and run your tests is great for a continuous integration (ci) process so that you can run multiple builds concurrently without either having to maintain a specific instance or database for each build – once you move passed having a single build as part of your ci process this simple approach to deploying databases really does help.

Benefits of Azure SQL Database for development:

• Simplicity in setting up new databases, especially as this can be automated
• No maintenance of your development instances so no patching, no backups etc.

Negatives of Azure SQL Database for development:

• More suited to testing than development as a persistent connection is required and will be slower than developing locally
• You have to pay per minute of database use

Containerized version of any edition

Windows 2016 and SQL Server 2016 both have support for running SQL Server in a container. Th-s gives us all the flexibility that we get with LocalDb and Azure+AWS for starting and stopping instances quickly. We should also see the benefits of not needing to patch each instance individually as long as we use common images for our instances.

Windows 2016 has now been released, so we can now potentially create and throw away instances of SQL Server (certainly SQL Server 2016) pretty simply if we have Windows 10 or Windows 2016. If we are tied to an earlier version of Windows or version of SQL Server then it might not be as simple to create an image that works.

Once we get widespread adoption of containers in Windows it should mean that we can create new instances and start and stop instances without the lengthy installation process. At this stage, it seems that it will be slower to start and stop instances than is the case with LocalDb.

If you test your databases in a cloud-like environment such as the Microsoft VSTS service, you may also find that starting a LocalDb instance is feasible as it is already installed. By contrast, downloading the SQL Server 2016 database image and then starting it might take up too many of your build minutes.

Conclusion

When developing code for SQL Server databases we, as developers, have three main requirements:

• we to be productive as quick as possible;
• we want our environment to be as simple as possible;
• We don’t particularly want to rely on good internet connectivity;
• we want to use a test version that simulates the real world from a functionality point of view (not necessarily performance for day to day development)

LocalDb is typically the answer for most SQL Server developers and, unless you have a specific requirement that means you are unable to use it, I would highly recommend it.