The SQL Server Data Tools includes a special Visual Studio project type, known as the data project. his provides a nice way to define a database schema within a project. By doing this, you get the benefits of the SSDT tools, for deploying the database to your servers. Plus, you get to use the Visual Studio Source Control tools to check-in the schema code alongside your other projects source code.
- Anyone who has worked on database development can tell stories about how painful a process it can be at times. To be clear, I'm not talking about using a database from your application, no, the pain points I'm referring to are the ones that affect the development team. And two big ones are the deployment and versioning of the actual database and its objects. Here's a story of a software development company I worked for back in 2005. We wrote small web applications for a major US government agency. Our apps were written in ASP.NET, and we used SQL Server for our database.
We were a small team, and didn't have a dedicated database administrator. So Eric, one of our team members, was designated as the DB guy, because he liked working with database schemas and stored procedures. When we start a new project, Eric would open SQL Server Management Studio, and create a database. Then he'd add the tables, views, and stored procedures to the database. We had some automation tooling that helped build our standards to our procedures, which reduced the time Eric spent on that task. Once Eric had the first prototype ready, he deployed to our development SQL Server where the team could use it during development.
At different stages in the development cycle, the database would be copied to a test server, a staging server, a QA server, and finally to our production server. This meant that we had to find some way to version the database. So that we knew our development database was at version 2.5, while we had version 2.3 on the other servers. It was a lot of work to keep these databases in sync, and avoid deploying the wrong version to a server. And on top of that, we needed to have sanitized data in the development and test databases, but the real data on the production servers.
We adopted a versioning system that worked for us. In fact, the steps we took were commonplace in other development shops at the time. But it was a home-grown system, and somewhat clunky. After the first database prototype was ready, Eric would create a script file containing all the SQL for defining the database. This style was zipped and stored with the version number. All we needed to change the database schema, which happened frequently at the beginning of the development process, Eric would modify the database, deploy the changes to the development server, and save another version of the ZIP file. Versioning the database was interesting.
We had to have version that matched our various milestones, so that when we rolled back to an old version of the application for a bug fix in the application code, we could have the older, matching version of the database on to the bug servers. So we'd have the application code in source control, and the data schema in that ZIP file. And we'd add the ZIP file to the source control too, so that it was affiliated with the changed set. Then, when we'd do a new version, we'd store the affiliated files together again. Anyway, I think you get the point. It was a lot of work to keep the databases and app code in sync. We didn't get much help from the existing Microsoft Data Tools in that era.
That brings me to the topic for this video. The SQL Server Data Tools , Data Project. This project type helps relieve some of the pain points from the database development. The Data Project is a special type of Visual Studio project. Here are some of the benefits you might acquire when using it. All of the database items are represented as files in the project. That means one file for each table, one table for each view, and one file for each stored procedure. The files are plain, text files that contain the SQL that defines that object. Data projects are part of the SQL Server Data Tools, and that means they use the same tools you've seen in the earlier chapters in this course.
Therefore when working with tables, you see the Table Designer. When editing stored procedures, you work in the dedicated SQL editor. Also, the Schema Compare tool is available, which makes sense, you can compare the updated schema in the data project with the development database. Then you can push the changes to the database when needed. In addition, there are other tools available. For example, there are database specific settings in the project that affect the global settings in the database, and deployment options. Then there is a Refactor tool, which simplifies renaming database items. It searches through the entire project, and determines what database items are affected by the name change.
Then you can see the changes, and approve them when needed. Finally, there's the availability of source control for the project and its files. Versioning and checking of the project files is exactly the same as for any other project file type. Let's look at this from Eric's perspective. How does it help him? We consider the database project to be the one true version of the database. It's the master schema for all the databases. All of our databases in our system are ultimately defined by what's in the data project. Updates are sent to the databases by deploying from the data project.
The data project is just one of the application projects. For example, you might have a web service, an ASP.NET MVC application, and the data project in one solution. When you open the solution, you have everything in one place. Plus the data project is kept in the same version control system as the rest of the solution. Therefore when you check out an old version of an application for a bug fix, you'll also get the data schema for that old version. Overall, data projects are nice resolution to the ancient dilemma of working with databases from the developer side of the house.
- 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