Here are three scenarios, if you work with SQL Server either as a provider of database environments (DBA) or a consumer of database environments for your application (developer) then you will likely see yourself in one of these descriptions. If you don’t please 100% find some way to tell me (email, comment below etc.)

Prod FTW’ers – There is only one place where the developers can develop. DBA’s (if you have one) complain every so often about someone using “sa” on production, whoever is using the “sa” account, keeps leaving open transactions in SSMS blocking all the users. Sometimes developers delete the wrong thing, it happens less now that they generally use a tool that warns if the where clause is missing from an update or a delete. Hopefully, not too many identify with this scenario today (past sins have already been forgiven).

DBA Heavy – The DBA’s manage production and also manage the developer instances. Managing the instances involves restoring production copies of any data, deploying any sql scripts for developers and backing up the dev databases. The developers have to ask the DBAs to regularly do things like kill connections or do things like clearing the proc cache. The DBA isn’t as responsive as they would like to be because their priority is, rightly so, production. The developers find it frustrating that the DBA’s don’t respond quick enough to their requirements, and also other developers keep doing things like leaving open transactions or changing stored procedures.

Dev paradise – The DBA’s manage production and some production mirror boxes for performance testing. The developers manage their own instances, which are on their own machines. The developers either install SQL Server or they use local DB, in some cases, they might use SQL Server in a container – however, the developers do it, they get an instance of SQL Server and have an anonymised, cut-down dataset that they can use to do their development on.

The first scenario is a non-starter, don’t ever do your development on production. I hope everyone understands this, if you develop on production, you risk losing all your data – if that is an acceptable risk, go for it, otherwise, stay away from prod.

The second scenario is where lots of us come from, whoever looks after the prod side of things also look after the development instances. This is fraught with pain and conflict, the DBA’s don’t have time to manage it, and the developers don’t learn how to manage their own instances so don’t get the opportunity to learn how to manage SQL Server as part of their standard delivery. If the developers had to fix things like log files getting full, then they would more likely be aware of how to make substantial updates in production.

The last scenario is ideal, developers have their own datasets on their own machines and can develop anywhere without annoying anyone else. If the developer breaks their local instance, then they can fix it quickly enough.

The standard blocker to this I hear is that the developers need to have all of the data on their dev instances or they can not develop the application. This is fundamentally untrue, there is not an application that cannot be developed with test data. Having test data causes you to have to think about the possible data paths in the database. If you don’t understand how your data can look, then I would argue that you don’t know the database well enough to develop against it.

But what about performance?

The second complaint is “if the developers don’t have all the data, they will write really slow queries”. Yes, they might, you need to have a perf suite you run before deployments or staged deployments so you can see the queries running on a production like workload, you don’t get that with just the correct data volumes there are things like locks to consider. Development and performance testing are different and if you have a piece of work that is genuinely performance sensitive and you can only develop on a full dataset, do it as a one-off then go back to your local dev instances.

The only way, for me, to develop against a SQL Server database is locally or, in the case of things like Azure SQL Db, a private cloud instance, but I would probably still try to develop locally.

What are the options?

So the options are:

• Develop on production
• Develop on a shared dev instance provided and managed by the DBA’s
• Install SQL Server locally
• Use LocalDB
• Use a container

What is the best option?

My preferred is to use LocalDb, it is super fast to start a new instance (seconds), and I can literally start a new instance, deploy some code, run some tests and delete the instance in seconds – very exciting. LocalDB does have some restrictions like you can’t use things like Hekaton, but if you just use core functionality it is hard to beat

My next option is a container. Similar to LocalDB it is fast to start a new one, it is a bit more complicated in that you are running in a virtual environment, so you need to do things like share folders if you want to files into or out of the instance. If I needed some functionality that wasn’t in LocalDb I would use a container.

The least favourite of the top options is installing SQL locally. The reason is that I used to end up with three or four instances and they would all be running, one wouldn’t always start, and it just got really messy. It also takes ages to install, a bit of a pain to be honest.

The last feasible option is a shared dev instance, probably managed by the DBA’s but having a shared instance is a pain for development – you are always getting in the way of other developers, and it really is not very productive.

Do everything you can to be able to do your development on a local instance and figure out how to get just enough data so that you can be productive, any time you spend making this process efficient is time you will get back many times during development.