Should developers be allowed to use LocalDB vs a "development" instance?

Question

Much like the vein of the question that was posted here previously around "Should developers be able to query production databases?" I wanted to get your thoughts on another particularly annoying topic!

Many companies prevent developers from install SQL Server Express and the like on development machines, instead promoting the use of centralised development SQL Servers.

Specifically this is done to ensure:

• Patch level consistency between development servers and production
  
• Ability to prove and validate any patches on the above
  
• Data security; only data on the development servers gets used for development
  
• Recoverability; data is recoverable and still backed up
  
• Collation differences that can cause problems when moved to production

To me all these arguments are particularly invalid, with perhaps the exception of the patching ones; but if a database on a local machine is purely used for development activities, and not testing, then the patching would be proven out when an application progressed through Test / UAT etc to Production.

Collation does not appear to be a valid reason, as if this was a concern for the database it should be set when it's created anyway. Only SharePoint and SCCM have issues around this as far as I know ;)

Now, assuming it's ONLY for development, and the database will not be "moved" to production and the only movements would be:

• Scripts that created the database being generated for deployment to production
• Backups from "production" third party systems being restored and truncated where appropriate for validation and development

Can anyone see any issues? Am I missing something?

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

Answer 1

Yes. All developers should have a local instance of SQL Server AND a shared SQL server instance. They exist for different purposes. Both are needed.

---

Perhaps your current environment looks like this?

Above, several developers are authoring changes and deploying them into a communal SQL Developer database. This is bad. Microsoft MVP Troy Hunt documents some of the pain-points of this antiquated approach, here. The primary ones are ...

1. Inability to appropriately source control. BIG!
2. Inability to performance tune without server-level rights.
3. Inability to experiment without impacting others.

This pattern causes developer conflict. One symptom of the conflict is database sprawl. Many database are created as developers hunt for a safe, isolated space to do their work. There are several reasons organization cling to this pattern. The first is that they haven't invested in a proper source control system. Another is that they've simply inherited this design pattern and can't be bothered to change it. Microsoft has been steadily marching away from this pattern and into something more like this ...

In the diagram above, each developer uses Visual Studio to write and save his database changes into a local instance of SQL Server. Those local changes are then synced into an appropriate source control system. Here each developer can design and experiment in an environment where his changes won't impact anyone else until checked-in. And at that time conflicts will be resolved.

The local database used above could be LocalDB, Express, or Developer editions. Simple-Talk does a great job weighing the pros and cons of each here. There is a reason Microsoft has authored so many choices for local development databases: LocalDB, Express, Developer.. we should be using them!

If you need to choose, it's hard not to argue that every developer have a local version SQL Server Developer edition installed. It's completely free and has feature-parity with Enterprise edition. It is going to allow your entire team explore and design within the entire Microsoft BI stack (SQL Server, SSIS, SSRS, and SSAS) in a safe way.

We can still keep the communal database, but it should not be for development, it should be for testing. It's a server where system-level settings are in sync with production. Hardware, OS, patches, etc..

Answer 2

To me all these arguments are particularly invalid

Ok. But they aren't to the rest of us. Why?

Patch level consistency between development servers and production

the patching would be proven out when an application progressed through Test

Patching can fix stability and data corruption issues which would otherwise plague developers. It should be done on development machines regardless.

Data security; only data on the development servers gets used for development

It's useful to separate "what should be" from "what is". Developers end up with sensitive (not necessarily PII, but not free either) data on their databases. It happens.

Recoverability; data is recoverable and still backed up

Super important.

Collation differences that can cause problems when moved to production

Only SharePoint and SCCM have issues around this

Anything which uses a temp table will have this issue. It's extremely common. You never notice it because most people go for a standard collation in the first place.

assuming it's ONLY for development, and the database will not be "moved" to production

Why would we assume that? Things often go from development to production. How else do you get production populated for the very first time? Pure scripts? Not necessarily when the application has been in pre-production for some time.

I guess one of the biggest concerns would be the ability for local db instances coming out of date, but thats a software management issue, not a DBA one IMO.

You simply need to issue a statement about what you do and don't support and why.

LocalDB is a core part of SSDT now and unavoidable. However it's not remotely accessible and doesn't have a scheduling component (Express has similar issues). Hence it's not generally supported by DBAs in regards to backups, maintenance, and integrity checks.

But consolidating to centralised development servers still makes sense. And now that Developer Edition is free, it's even easier to justify having more of them.

Answer 3

Conceptually speaking, you are on the right track. For an organization that has a mature development process / Software Development Life Cycle (SDLC) that includes source code control, continuous integration (CI), automated testing, and an IT group that knows how to manage various environments and workstations to keep them in synch regarding software and patch-levels, and disciplined developers that a) follow the process, b) work together, and c) work with IT, then having 50 (or however many) development DBs can work:

• Yes, consistent software and patch-levels can be maintained over any number of workstations.
• Yes, any "issues" can surface in automated testing and/or QA / UAT environments.
• Many dev DBs are not as easy to back up, but not impossible either. If using Roaming Profiles, then the data files being in each Windows User's "Local Settings" folder might be easier to back up. Or at the very least it can be scripted by IT to grab files from all dev workstations, similar to how they would ensure that all are properly patched.

BUT, as with most everything else, it really comes down to the context of the situation.

One benefit of having separate development DBs is that various projects can be worked on simultaneously without impacting each other. (Of course, this might be the only benefit.)

HOWEVER, there are various issues to consider:

• What is the size of the team, and how many people are working on any particular project? The more people you have working on a project, the more you need a shared / centralized development server so that everybody gets all of the changes.

• Collation-wise, SQL Server Express LocalDB does have one particularly annoying nuance / restriction / limitation:

  The instance collation for LocalDB is set to SQL_Latin1_General_CP1_CI_AS and cannot be changed. Database-level, column-level, and expression-level collations are supported normally. Contained databases follow the metadata and tempdb collations rules defined by Contained Database Collations.

  Instance-level Collation affects not only tempdb (i.e. db-scoped object-name resolution, and default collation for temporary tables but not table variables), but also local variable names cursor names / parameter names, and goto label names. Hence, if your Production servers have an instance-level Collation of something other than SQL_Latin1_General_CP1_CI_AS, then LocalDB isn't a good choice.

• Are you using Enterprise Edition features? If it is only a matter of doing ONLINE Index Rebuilds, then that probably doesn't matter. But if you are using something like Table Partitioning, then LocalDB isn't a good choice.

• Perhaps the biggest issue is the overall increased scope of potential issues arising from any environmental disparities (OS-level, software-patch-level, instance configuration, DB configuration, etc) that could lead to a bug. While we have already accepted (in a general sense) that automated testing / QA / UAT would find these issues, that is not guaranteed! Given that humans make mistakes and that humans need to come up with all of the tests that would check all code-paths and all variations of data (types, size, etc), it is highly likely that any number of scenarios will not be tested and that a bug could make it through and not be noticed until a customer finds it in Production. This happens anyway, but the chances increase when using LocalDB so that each developer can have their own private DB.

What it really comes down to is: what is the compelling reason for using it in a team environment? Across various jobs, I have always worked in groups where there were App Devs and Database Engineers, and while the App Devs sometimes mocked up a stored procedure just to get it done faster (not enough of us DBEs), the DBEs did most of the DB programming, including checking (i.e. fixing) any code that the App Devs wrote. Using LocalDB would have made it much more difficult to work in a group. And while using SQL Server Express (or even Developer Edition) to have personal instances on each dev workstation that was accessible remotely -- making it easier to collaborate -- there was never really any need to have that level of isolation since it was rare to have DB changes for one project negatively impact another.

Of course, those of us who were Database Engineers (DBEs) did have local installs of Express Edition and/or Developer Edition. But, that was to do testing that required more control over instance-level configuration / security, etc than could be allowed for on a shared server. And I used the local instance(s) occasionally, but not terribly often. But for my personal projects, I love LocalDB and use it quite frequently.