There's a special version of SQL Server called SQL Server Express LocalDB. It was added to the list of SQL Server editions in version 2012. It was created especially for developers. This tutorial looks at LocalDB and explains why it was created and what problems is solves for developers.
- [Voiceover] There's a special version of SQL Server called SQL Server Express LocalDB. It was added to the list of SQL Server editions in version 2012. Under the covers, it's really SQL Server Express with all the benefits and restrictions that apply to Express editions. It was created specifically for developers to solve issues with having database servers on developer computers. Mainly, it attempts to diminish problems from installing a full version of SQL Server on a development computer and it fixes problems that arose when developers attempted to use the SQL Server Express edition for development.
Let's look at why we need a developer specific version of SQL Server. As you may recall, the SQL Server Standard Edition runs on a server computer. From my developer computer, I can connect to that SQL instance and use it while developing the application. In most scenarios, there are multiple copies of the database available during the development cycle. For example, a production server where the real data is stored for our our company application and there could be a test server, a staging server and a dev server and more. You could develop against a production server but that is so risky that it's forbidden in most organizations.
So an alternative that's common is to have a separate development server that has SQL Server installed and is used during the development process. You connect Visual Studio to the dev server and everything works great but there are some issues with this. For one, you have to provision a server and that server is only available when your dev computer is connected to the network. To get around this issue, some development shops add a copy of SQL Server to the developers' computers. I see this implementation in small development shops or small dev teams within an organization but anyway, with the local copy, you'll always have a copy of SQL Server available then if you need to prototype some data structures or are working on a laptop off network, you can still use the database.
This is not an ideal solution however. I could list a bunch of reasons why this is true. Here's a couple. You have a production capable database server running on your laptop. Database servers are resource intensive which impacts the computer performance for other tasks like running Visual Studio. They run as a background process so they are running 24 hours a day and you are running a real database server from the laptop. There could be security risks because that server is listing to known access ports waiting for client data connections and the database service is often configured with a high privileged user account.
To get around these issues, Microsoft created a lightweight edition called SQL Server Express. This is a free edition that is lighter in resource usage. Plus, it runs in a limited security context and has other benefits. It's a good solution but it still has issues. Most of these are centered around developer complaints about database context and database installation troubles. Microsoft took the Express concept and tweaked it and released the LocalDB edition. It's the latest attempt to help developers work with SQL Server on their computer.
When you install Visual Studio 2012 or later, LocalDB is installed. It's also installed when you install SQL Server 2012 Express or later. It's only installed one time on a computer. This is important because in older versions of SQL Express, each app that installed SQL caused another instance to be installed but equally important, there can be multiple applications installed on a computer that use LocalDB. When each app connects to LocalDB, a dedicated LocalDB process is created for that app. That brings me to another great benefit.
LocalDB runs on demand meaning the LocalDB process is started and stopped automatically when needed. What does this mean in practice? When you start a computer, there is no LocalDB process running therefore there are no resources dedicated to servicing a database server while you go about your daily work routine. When you run an application that uses LocalDB and a connection is made to the database, the LocalDB process is started on the computer. That's the on-demand part. When the last connection from the application is closed, a few minutes pass, then the LocalDB process ends.
This on-demand feature works for deployed application. For example, when you create an app with LocalDB and install it on the end user computer. Plus, it also works in Visual Studio when you are coding the application. For example, when you access the LocalDB file and a Visual Studio IDE tool like SQL Server Object Explorer, the LocalDB process is automatically started and stopped just like in the production release and it works when you debug an application. There are more benefits. The LocalDB process is designed to run under a low privilege user account.
Also, the developer can use the attached DB file name property for the database connection string. That means that you control the location of the database file. It doesn't have to go in some centralized SQL Server location. It can be in your Application Install folder and it also means we are just working with a database file, not configuring and using a database server. Alright, that sums up the reasons why we have LocalDB and some of the benefits it provides to the developer. I'll use it for most of the code examples in the course.
The rest of the chapter shows how to discover what LocalDB versions you have available on your computer and what applications are using LocalDB on your computer.
- Exploring integration with Azure data sources and other Microsoft databases
- Working with SQL Server Express LocalDB
- Connecting to databases with Server Explorer
- Working with database tables
- Manipulating tables with the SQL Server Object Explorer
- Creating queries and custom views
- Creating stored procedures
- Comparing databases
- Building a data project in Visual Studio
- Refactoring a database object
- Deploying the database
- Connecting to Azure databases
- Deploying data projects to Azure
- Integrating Visual Studio with Entity Framework
Skill Level Beginner
Q: This course was updated on 08/23/2017. What changed?
A: The following topics were updated: installing the SSDT features and why LocalDB is essential for developers.