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

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.

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.

StackExchange Database Administrators Q#154591, answer score: 6