This article introduces SQL Server Data Tools, SQL Server Data Tools Project, and SQL Server Express LocalDB, which are now available in SQL Server 2012.
Overview of new development tools
New tools and projects have been released to help developers of systems using SQL Server develop efficiently. The new tools provided are SQL Server Data Tools and SQL Server Express Local DB. A database project is provided along with SQL Server Data Tools.
Target audience
- Application design and developers using SQL Server
- SQL Server database administrator
- Interested in new features in SQL Server 2012
Required environment
Visual Studio 2010 SP1
Not required when using SQL Server Data Tools alone. However, in the environment where Visual Studio 2010 is installed, it cannot be used alone and will be used on Visual Studio.
.NET Framework 4.0
If .NET Framework 4.0 is not installed, it will be installed automatically when you install SQL Server Data Tools.
Glossary
I will explain the outline and notation of the tools to be covered this time.
SQL Server Data Tools (abbreviation: SSDT)
SQL Server Data Tools is a development support tool for application developers who use SQL Server. The goal is to complete the database-related tasks that developers performed in SQL Server Management Studio, such as creating and modifying tables, in Visual Studio. Visual Studio 2010 integrates with Visual Studio by installing SQL Server Data Tools. It will be included from the beginning in the next version of Visual Studio 11.
With SQL Server Data Tools, you can work with SQL Server 2005, 2008, 2008 R2, 2012 and SQL Azure. SQL Server Management Studio does not provide an operation screen for SQL Azure, and you need to issue and operate T-SQL. With SQL Server Data Tools, SQL Azure can also use operation screens such as designers.
This time, I will not explain the specific operation method, but if you use the schema comparison function of SQL Server Data Tools, you can compare the differences between the production database, SQL Server database project, and SQL Server database project snapshot (Note 1). Can be done (Table 1).
| feature | explanation |
| Supported versions | SQL Server 2005, SQL Server 2008, SQL Server 2008 R2, SQL Server 2012, SQL Azure |
| Designer function | Creating / modifying database objects such as tables, indexes, and triggers by operating the screen |
| Data extraction function | SQL statement creation function for duplicating data |
| Schema comparison | Difference comparison of database, SQL Server database project, SQL Server database project snapshot |
| SQL generation to fill in the differences | SQL statement generation function to eliminate the difference |
| SQL Server database project creation function | Create SQL Server database project, create snapshot |
| dacpac, bacpac creation function | Schema backup function, schema and data backup function |
In the MSDN Library, it is referred to as "SQL Server Data Tool". The software name after installation is "SQL Server Data Tools". In this article, it is referred to as SQL Server Data Tools.
A SQL Server database project snapshot is a file that backs up a point-in-time SQL Server database project. When you take a SQL Server database project snapshot, it is backed up as a dacpac format file. The dacpac file is a zip-compressed file group that expresses the database structure (schema) in XML format. This file will become the mainstream backup method for SQL Server in the future.
SQL Server database project
A project template that is included when you install SQL Server Data Tools.
If you are a developer with restricted or forbidden access to your production database, using a SQL Server database project will make your development efforts easier. Developers can implement schema changes by adding, modifying, and deleting objects in SQL Server database projects. You can use schema comparison to see if there is a difference between a SQL Server database project and a production database. The SQL Server database project is attached to LocalDB and can be operated and applied via LocalDB.
Figure 1 is a conceptual diagram when developing an application using a SQL Server database project. The database administrator creates a SQL Server database project from the production DB. The developer makes changes to the SQL Server database project and runs it in LocalDB. Then reflect the changes from the database project to production.
Since a database project is a project file, it can be added to source code control along with the program code. This allows you to control the source by combining the database schema and program code.
In the MSDN library, it is referred to as "database project" or "SSDT-DP (SQL Server Database Tools --Database Project)". In this article, it is referred to as a SQL Server database project.
SQL Server 2012 Express LocalDB
There are two products available in SQL Server 2012 Express Edition. The traditional SQL Server 2012 Express and SQL Server 2012 Express Local DB. There were two needs for SQL Server Express in the background of the two types of products.
The first is a free edition of SQL Server. SQL Server Express is 100% install, manage, and programmed with other editions of SQL Server. SQL Server Express can be used for learning, training, and small product databases (with a DB capacity of less than 10GB). Upgrading from SQL Server Express to another edition is as simple as registering your license key and does not require installation.
Second, SQL Server Express is a SQL Server edition that you can use to develop applications for SQL Server. It is expected to be very small, simple, requires no configuration or administration, and can be run by non-administrator users, while maintaining 100% compatibility with other SQL Servers.
SQL Server Express has been tuned to take on both roles. However, it was difficult to achieve small size and simplicity while maintaining compatibility with SQL Server. So we changed our approach to provide a simple SQL Server 2012 Express LocalDB that is compatible with SQL Server at the API level, which is the SQL Express version for developers.
On the MSDN library, it is referred to as "SQL Server 2012 Express LocalDB" or "local database". In this article, it is referred to as LocalDB.
.............................................................