Microsoft's free SQL Server Data Tools ease the burden on database administrators when versioning databases. Here's how they can help you with this sometimes tricky task.
By Keith Schreiner
I have read and been told
many times that it is a “best practice” to version control my database.
To do so, I’ve tried past tools from Microsoft, RedGate, and others, but
none have really caught on for me. I’ve also disliked the “messy”
process of having to manage multiple SQL scripts when upgrading a
production database. But Microsoft’s latest (free) tool, SQL Server Data Tools (SSDT), has restored my faith that versioning my database should not be a dreaded chore.
Define
SSDT
adds a lot of functionality to Visual Studio for working with SQL
Server databases. The main things it adds are a “SQL Server Database
Project” and something called a DACPAC (Data-Tier Application Package). A
DACPAC is a single deployment file that contains your entire database
schema and some related SQL files (like look-up data), basically,
everything to deploy a new version of your database in one file. It is
similar to a BACPAK, which is a DACPAC plus all of the data in every
table (like a standard database backup). But before we talk more about
DACPAC’s let’s discuss what SSDT adds with its SQL Server Database
Project.
Develop
In Visual Studio, after creating
a new SQL Server Database Project, you have an option to import a
database, which nicely creates SQL scripts of all your database objects
(tables, views, stored procedures, and more). The next step that I do is
to create a “Data” folder and add a post-deployment SQL script to
populate the look-up data and optionally create some test data.
In
a database project you can only have one post-deployment and one
pre-deployment file, so it is a best-practice to use the SQLCMD syntax
to include other files in your main file to help with organization and
maintenance. As you are creating these SQL scripts, or editing the SQL
for the tables or stored procedures, you will start to notice all of the
other things SSDT adds to Visual Studio: SQL IntelliSense, SQL code
navigation, a SQL Server Object Explorer, debugging of SP’s, unit tests
for SP’s, a visual Table Designer, a Schema Compare tool, a Data Compare
tool, and more. All of these features make database development and
maintenance feel natural in Visual Studio.
Deploy
After making your database changes, the next step is to deploy them, and SSDT provides several ways. The first way is “Publish”, which works great if you have a direct connection to the database from your Visual Studio machine (like to your local or “Dev” database). Right-click the database project and select “Publish…” which opens a dialog to save a profile. Once a profile is saved as part of your project, you can just double-click it to directly publish your database project to the database or to just generate a script of the changes that it would run.The second way is to use a DACPAC, which we talked about above, when developers do not have access to the target database (like for Production). To create a DACPAC, just right-click the database project and select “Snapshot Project”, which then creates a DACPAC in your project’s “Snapshots” folder. Then to deploy this DACPAC you have several options. If you have a DBA that always applies database updates, you can give him or her the DACPAC to apply using SSMS (SQL Server Management Studio), which has a built-in “Upgrade Data-tier Application” task; it uses a wizard to help apply the changes (and allows all changes to be reviewed for problems, like possible data loss, at every step).
Real-life sample
On the first project I used SSDT (a new MVC web site), I encountered three issues with SSDT. Here is how I solved them and greatly simplified database development. First, I only wanted to auto-deploy the DACPAC on the website’s Application_Start when the database had actually changed. So I added a piece of logic to check for a new version number in the latest DACPAC before applying it. The version number of the DACPAC is set in the Database Project properties -> Project Settings -> Properties dialog, and the current database version is stored in the system table, msdb.dbo.sysdac_instances_internal. Having this version number not only made startup faster, it made others aware of a “database version”.
Conclusion
In the past, making a change to a database felt like a long, complicated process of making a schema change (to hopefully the correct database version), manually creating a change script, reminding the deployment person about the database change, and crossing my fingers that everything went correctly. With SSDT all of this uncertainty has disappeared. SSDT is a great tool that makes it easy to create, deploy, and version your SQL Server database updates. To learn more, download the sample code, which demonstrates how to auto-deploy and version a SQL Server database.Notes:
- SSDT Website: http://msdn.microsoft.com/en-us/data/tools
- SSDT Installed In: C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\
- SSDT Version Used: July 2013 with Visual Studio 2012
- VSCommands Extension: http://vscommands.squaredinfinity.com/
0 comments:
Post a Comment
Appreciate your concern ...