Where is my database anyway?
Tuesday 10, March 2015 | Post link
Entity Framework changes quite a few things about application development the foremost being not having to worry about the database at the start of development cycle. If you use Visual Studio to create ASP.NET MVC applications for learning purpose, all database related stuff just works out of the box - as if by magic.
Traditionally when developing any application requiring a database the first line of code to run would probably be:
As per Sql Server blogs SQL Server local db is a more developer friendly version of SQL Server Express. Its mainly used to test against local data since it accepts only local connections using named-pipe.
It doesn't run as service and is started on demand. You can check this; run a sample web application which makes use of a database connection like the one given above, you'll see the sqlserv.exe process in Task Manager:
When an application tries to connect to a LocalDb database, if the local db executable is not already running it is starts as a child process of the application and shares the same security context. Most examples use Windows authentication but it's not necessary you can just as well use SQL authentication by creating the sql login in LocalDb.
You can connect to localdb using Sql Server Management Studio which comes with Sql Express 2012 or any other version of Sql Server 2012 which has management studio as part of the install.
The next question is where are the database files stored? The string 'DataDirectory' is an AppDomain property which is read and substituted with the value set for it when connection to the database is being made. For a web application its set to the App_Data folder - how convenient. The master database is stored in the user's AppData local folder e.g. C:\Users\Siddharth\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances. You can attach your application's database datafile to a full-fledged SQL Server instance so migrating from locadb to another version should not be a problem at all.
Sql localdb runs the same sqlservr.exe as other version of Sql Server like Express or Developer or even Enterprise Edition. From programming point of view, you can do most of the things you can with a 'real' SQL Server like run all T-SQL queries, stored procedures and more. There are limitation which are detailed in the following blog (http://www.sqlcoffee.com/SQLServer2012_0004.htm). For learning purposes, localdb seems to fit the bill.
Traditionally when developing any application requiring a database the first line of code to run would probably be:
CREATE DATABASE MyTestWebAppEntity Framework creates the database and tables if you're using code-first approach. However it would be nice to know where exactly the database files are and where the server is. Let's start from the web.config file which ideally should have the first clue as to the whereabouts of the database engine. The connection string section of it reads:
<connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\v11.0; AttachDbFilename=|DataDirectory|\aspnet-Checkboxes-20150215043057.mdf;Initial Catalog=aspnet-Checkboxes-20150215043057;Integrated Security=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
LocalDB - is this SQL Express? or SQL Server or some other thing?
As per Sql Server blogs SQL Server local db is a more developer friendly version of SQL Server Express. Its mainly used to test against local data since it accepts only local connections using named-pipe.
It doesn't run as service and is started on demand. You can check this; run a sample web application which makes use of a database connection like the one given above, you'll see the sqlserv.exe process in Task Manager:
When an application tries to connect to a LocalDb database, if the local db executable is not already running it is starts as a child process of the application and shares the same security context. Most examples use Windows authentication but it's not necessary you can just as well use SQL authentication by creating the sql login in LocalDb.
You can connect to localdb using Sql Server Management Studio which comes with Sql Express 2012 or any other version of Sql Server 2012 which has management studio as part of the install.
The next question is where are the database files stored? The string 'DataDirectory' is an AppDomain property which is read and substituted with the value set for it when connection to the database is being made. For a web application its set to the App_Data folder - how convenient. The master database is stored in the user's AppData local folder e.g. C:\Users\Siddharth\AppData\Local\Microsoft\Microsoft SQL Server Local DB\Instances. You can attach your application's database datafile to a full-fledged SQL Server instance so migrating from locadb to another version should not be a problem at all.
Sql localdb runs the same sqlservr.exe as other version of Sql Server like Express or Developer or even Enterprise Edition. From programming point of view, you can do most of the things you can with a 'real' SQL Server like run all T-SQL queries, stored procedures and more. There are limitation which are detailed in the following blog (http://www.sqlcoffee.com/SQLServer2012_0004.htm). For learning purposes, localdb seems to fit the bill.
Categories:
Sql Server (2)
TSQL (5)
Comments