Understanding the SQL Server LocalDB Approach to Designing/Deploying Visual Studio Projects

Other SQL Server Topics
1

Hi,

I’m a VB.NET programmer with some questions about SQL Server LocalDB. I switched careers about 15 years ago and stopped programming for about a decade. Then about 5 years ago I resumed programming. Until recently I’ve been developing small projects with Visual Studio 2010/2015 and Access. Then last month I installed SQL Server 2016 Express and upsized a very small Access project as a test. That went well, although the client-server design was overkill. Now I’d like to upsize a much larger Access project. SQL Server’s LocalDB approach looks promising because for the foreseeable future this project will continue as a stand-alone system. If I can figure out how to deploy a LocalDB project, I’ll probably port all my apps to SQL Server and stop using Access.

One caveat: I’d rather not incorporate the database into the VS project. I’ve always set up and deployed my Access database files (.ACCDB/.MDB) separately from their corresponding VS applications. I’d like to continue using that approach with SQL Server LocalDB if possible. With that in mind, here are my questions:

  1. Is deployment of a LocalDB database conceptually similar to deployment of an Access database insofar as copying a database file(s) to a PC, deploying the app alongside it, and connecting? Can I simply connect to the SQL Server .MDF as I would connect to an Access .ACCDB/.MDB? (I realize the connections strings are different.)
  2. Which SQL Server data file(s) must I deploy for a LocalDB installation?
  3. Must I install/register any supporting DLLs on the target PC?

Thanks!

2

AFAIK, LocalDB is great for development and testing but is not meant for deployed applications. You might be better off with SQL Server Express.

1 Like
3

Okay … I was hoping that LocalDB required only a few files—perhaps installed via a simple “copy” rather than a full-blown install of Express. I must have misunderstood the design/purpose of LocalDB.

I can’t make up my mind now. Most of my projects are small. So on the one hand, Access seems like a good choice because everything’s in a single file. The downsides are all the typical Access limitations. On the other hand, SQL Server Express seems like a good choice because it’s so much more capable. The downside is the installation of a relatively major piece of software on the user’s PC.

I was hoping LocalDB was somewhere in the middle—the best of both worlds: a simple installation combined with most of the power of SQL Server Express.

Any further advice would be greatly appreciated. I have a lot to learn about SQL Server. And I’d still be interested to find out which specific files are required for a LocalDB install.

Thanks!

4

That's kinda true of SQL Server too. SQL Server has a Data and Log file, but if you take a backup, and then Restore that on the Client end that's a single (deployable) file. (Its not true of Scheduled Jobs and external things like SSIS processes)

Sure, you have to install SQL Server / SQL Express, and that's a bunch of EXE, DLL and stuff - but so is an install of Access ...

That's just the data though. If you are also building an application then you also need to deploy that. If you do that all in Access technically you could have a single file, but IME that is a bad idea because it makes it very difficult to deploy APP changes, so it seems to be more common to have two Access (Database) files - one with all the APP stuff, and the other with the actual Client Data, and then you create LINKS from the APP Access file to the Data Access file. Once you get to that point then creating LINKS to a SQL Server database is not really any different to creating LINKS to A.N.Other Access Database file. (You say VB.NET so perhaps you are not doing Forms and stuff in Access anyway, in which case the separation between Code and Database is even cleaner - the database could be "anything [compatible]")

The difference, to my way of thinking, is that SQL Server (and SQL Express) is bullet-proof. If you run in FULL Recovery Model then you can restore to point-in-time. If your database is trashed (e.g. Disk Controller failure or Memory error that writes garbage to the Database) IME the chances are very high that corruption does not occur to both Data file AND Log file, and thus it is possible to restore the Database from one or the other - i.e. with no loss of data.

Its pretty trivial to restore a SQL Backup (to a TEMP database name) and "copy across" some data from TEMP to LIVE DB, or alternatively to COMPARE the two. For example, if someone deletes a bunch of Customer records by mistake or commits a Fraud / malicious damage. I expect these things are possible in Access, just IME (which is somewhat limited) they are not trivial to achieve.

The Restore To Point In Time is much more likely to be used in DEV than in PRODUCTION - in DEV we are forever trying something and then going "Woops! Shouldn't have done that". So we just restore to 5 minutes ago and try again :slight_smile: (I presume that's not possible in Access, unless you thought to take a backup BEFORE making that "Woops"? :slight_smile: )

So actually the real gain might be in your DEV environment, as much as in the Client's PRODUCTION environment?

1 Like
5

SQL Server Express seems to be the recommended way to go. Clearly, LocalDB is not recommended here. Many thanks to you and gbritton for clarifying that. I just didn’t have the experience to make that call myself.

I must admit I’m a little surprised. I was headed in the wrong direction with LocalDB, and I needed a paradigm shift. (I’m so used to working with Access that I tend to think of it as “simple” in every regard compared to SQL Server. In the school system where I work, MS Office is everywhere, while SQL Server isn’t used at all at the local or district levels.) Over the past month, I’d gotten the impression that LocalDB was a sort of middle ground between Access and SQL Server, and therefore it would be just right for my single-user projects.

But I see what you mean about comparing installations of Access and SQL Server. Perhaps there’s not much difference in terms of overall footprint between a clean Access install (including any necessary supporting Office and/or Windows files) and a clean SQL Server install.

My apps’ forms and reports are developed with Visual Studio, so yes, the VS component of each app requires a relatively extensive install. By comparison, the Access files contain nothing but data, so I can literally just copy them to the user’s hard drive. I was thinking LocalDB might offer similar ease of deployment.

The “bullet proof” aspect of SQL Server is one of many reasons why I want to upsize my Access projects. With Access, in order to restore data after a “Woops” I must first remember to make backup copies of specific tables or the entire database. There are ways to automate that, but SQL Server’s inherently fault-tolerant design is superior.

Okay, I’m going continue upsizing my Access databases to SQL Server Express and forget about LocalDB unless I hear advice to the contrary. Again, thanks!:slight_smile:

6

if I may make a recommendation but first a quick question. nothing to do with sql server :slight_smile: I come from the world of small client size applications using ADP (Access Data Projects). It came with its nightmares but it was pretty solid. Almost 3 tiered :slight_smile: Front end SQL server on back end.
Are you deploying the client app on each user machine ? Is it an .exe or is it a web application with web servers and all? If you are making the leap from Access to SQL server might want to also think about making application level changes as well if you are able to and if you have the budget. I did that and moved on to an angular/node.js web application with rest api and all and what a huge stress reduction from the whole client side app nightmare.

1 Like
7

Just before you do that!! ...

... I expect there are blogs detailing any Gothcas that you may want to consider, I recommend you Google for some Good Info, but the one I would raise is Backups.

Unless things have changed (we only use the heavy Iron versions of SQL Server, not Express) there is no SQL Agent in SQL Express. SQL Agent contains a scheduler which, basically, just runs one/many SQL statement "steps" on a timed schedule, and has the ability to EMail etc. on Failure (or Success), keeps a log of the output from the tasks, and so on. The Log History makes it pretty easy to debug things that go wrong etc. - i.e. right-tool-for-right-job. There is also a Maintenance Wizard and whilst the Pros here (myself included) thrown scorn on it at least you can say "Construct a backup for all DBs every night" and "Rebuild all indexes at the weekend" and so on without having to write a line of code.

Building a backup schedule using Windows Task Scheduler, by comparison, would require both some Grunt and some Knowledge.

There are cracking 3rd party backup solutions - of those I favour Minion Backup for "small shops" because you can just plug it in, and it will "just work" out of the box - fiddle with options only when you decide you want to, your data will be safe in the meantime. But I have no idea how easily that can be scheduled WITHOUT using SQL Agent. There may also be other tasks? that you need to schedule that you will miss having SQL Agent available

Either way, I suggest:

Set the Recovery Model of ALL your databases to FULL. IF you can reconstitute a database, entirely, from external data then setting it to SIMPLE is OK.

For ANY database that is in FULL Recovery Model backup the log every 10 minutes, or more frequently. Absolutely no sense having the ability to restore to point-in-time and then only taking a LOG backup once a day! Also, in FULL mode you need to backup the log often, otherwise it will grow - possibly to rediculously large size.

Then take a FULL backup overnight.

Backup to local disk (buy more space if you need it). Backing up to a remote location complicates things and risks backups not happening (and you not realising they haven't).

Keep a weeks worth of backups online (moving them to somewhere else, with more space, after a day or so is fine). That way you can restore / investigate without having to find a backup tape, wait for the tape machine to be free, wait for the tape to restore, then discover that wasn't the backup that you needed after all!

(We keep Sunday's Backups online for 4 weeks, daily backups for a week, we actually only do DIFF backups on weekedays, FULL on Sundays, to save space).

Oh ... use COMPRESSION on all your backups. Absolutely no reason not to, and its a single "switch" in the settings, so no need to configure backups to be compressed etc., just throw the "all backups compressed switch".

We haven't written a Client APP in years. Everything is web pages now, the whole deployment thing is a joy by comparison to all the hassle we used to have of DLL-Hell et al ...

Amen!

1 Like
8

I don’t have anything against Access; it’s been “solid” for me, too. I’m sure I could continue to use it successfully. However, it has some limitations that will work against me when I take things to the next level. Besides, I really want to update my SQL Server skills. So I figured it was a good time to upsize.

Yes, I’m deploying EXEs on individual PCs via Visual Studio Installer/Setup Projects. The apps are mostly small ones that ease various aspects of teaching. And while they certainly have potential as multi-user systems, I’d like to upsize them to SQL Server before taking that step.

Moving to web applications is a more distant goal; SQL Server is my next priority. At least I have some ancient background with SQL Server (version 6.5). But I can progress only so fast. I’m still a teacher by day; programming is a “serious hobby” that may eventually become a second job. The school district is interested in a couple of my apps, and we’ll see where that leads.

But first things first; It would be nice to settle on a version of SQL Server so I can devote the coming summer to upsizing all my apps. When I heard about LocaDB, I stopped by to find out more about it and discovered that I should just make the leap to SQL Server Express.

Thanks for the long-range advice; it all helps. :slight_smile:

9

Thanks for the ideas about backups. So much good advice … I appreciate you taking time to detail those things. I’m saving this information so I can look into it this summer! :slight_smile: