Flex2SQL Version 12 New Feature: MS SQL 2012 LocalDB's

One of the new features introduced in Microsoft's SQL Server 2012 Express is a new edition called LocalDB.  This new edition is intended primarily for developers or single user installations.  It is available (along with the Express edition installers). The installer for LocalDB is significantly smaller than the Standard or Express installation.

When you run the LocalDB installer a local instance will be setup with the name "(localdb)v11.0".  One of the differences from the express edition (and in fact a primary feature of LocalDB) is that the instance doesn't actually start a process or service on your PC.  So there are no resources used by a LocalDB instance while it is sitting idle.  Once you connect to the instance and attach or create a database a process will be started that lives until a few minutes after the last connection to it is closed.

Additionally, LocalDB's allow you to attach to an MDF (the actual SQL Database file) on the fly.  This means that you can distribute your database as standard files (by detaching them from your LocalDB or Express server), copy them, and then just attach to them on another computer using the LocalDB installation on that computer.  This make installations exceptionally easy.  It also allows for demo workspaces that use Mertech's drivers to be distributed without need a bunch of "foot work" to get them up and running.

The key to making this all work is knowing how to attach to a database file from a local instance in DF/VDF.  This is where things are a bit different than the guides you'll find online for attaching from C# or VB.NET.  After you've installed your LocalDB instance (just run the installer) you need to attach the database to the instance.  From DF/VDF you can do that with a few simple commands:
 

Login "(localdb)v11.0" "" "" "SQL_DRV"
Send Ignore_Error of Error_Object_Id 25000
SQL_SET_STMT to "EXEC sp_attach_db @dbname = N'MTSample', @filename1 = N'C:Mertec~1DataMTSample.mdf', @filename2 = N'C:Mertec~1DataMTSample_Log.ldf'"
SQL_PREPARE_STMT
SQL_EXECUTE_STMT
Send Trap_Error of Error_Object_Id 25000
SET_DATABASE_NAME to "MTSample" 

Let's go through this one line at a time. The first line connects to our freshly installed local instance. Note that we don't pass a username or password. Although you can setup usernames/passwords for LocalDBs, by default they are setup to use Trusted Connections. The next line is to ignore the possible error we'll have trying to attach to our database (more on this later). The next three lines are to attach to our database. You'll note that on the SQL_SET_STMT line I pass the database name along with two filenames.

The first is the actual database, while the second is the transaction log file. These two files, the MDF and LDF are a matched pair for each database. The database only needs to be attached to the first time, but since LocalDB's can be attached/detached at anytime it is good practice to bake in the code to attach to your database. This is why we needed the ignore_error before. If the database is already in an attached state, this code will generate an error. The error can be ignored though, so that's what we do. After this we call trap_error so that if there are any other SQL errors we'll know about them. The last line gets us wired up to the database we've just attached to.

Although technically the attach code only needs to be run the first time, the whole idea of LocalDBs is that they are significantly more dynamic in nature. You can easily go into the SQL Server Management Studio and detach the database to move to another computer. This also makes it easy to detach from one database and attach to another. You could easily create a dialog that allows you to select a database to load, detaching from one and attaching to another. This would allow you to have test databases that are actually separate databases that all share the same database name. You'd just attach to test database files which can be named whatever you like, even if they contain a database that has the same name as your "live" database files. LocalDB's also makes it very easy to send databases to other developers or to get a database from a customer.