When it comes to automating the deployment of an application, I always see customers struggling when it comes to the database. Updating a web application with Web Deploy or even with a simple XCopy isn’t that hard. But a database with schema changes is not a simple XCopy.
Fortunately, Microsoft released a great tool for dealing with database updates. Unfortunately, this tool isn’t widely used yet. This blog series will help you in getting started with SQL Server Data Tools and setting up continuous deployment of your database with Release Management.
-
Part 1 Meet SSDT
Part 1: Meet SSDT
With Visual Studio 2012 Microsoft released a big update to what was previously known as the Database Project. Starting with VS2012, SQL Server Data Tools overhauled the database project and gives you all you need to bring your database into source control and enable continuous deployment scenarios.
The newest version at the time of writing is SSDT July 2014. If you are running Visual Studio 2013, you will automatically get notified of any new updates in your IDE. If you’re running 2012 you can download the update here.
Now let’s start with a simple web application and add a database project to it.
Adding SSDT to your application
The sample application is a simple website that uses Entity Framework Database First to access a table called People.
You can download the source code for the final application here or you can create the project with the following easy steps:
- Create a database SSDTSample on your LocalDB instance. Add the following table:
-- Creating table 'PersonSet'
CREATE TABLE \[dbo\].\[PersonSet\] (
\[Id\] int IDENTITY(1,1) NOT NULL,
\[FirstName\] nvarchar(max) NULL
);
GO
-- Creating primary key on \[Id\] in table 'PersonSet'
ALTER TABLE \[dbo\].\[PersonSet\]
ADD CONSTRAINT \[PK\_PersonSet\]
PRIMARY KEY CLUSTERED (\[Id\] ASC);
GO
- Create a new ASP.NET Web project for MVC named GettingStartedWithSSDT and add this project to source control. In this sample we will use TFVC in a Team Project called _SSDTTestProject (_for more information on creating a Team Project see Create a team project)
- Add an Entity Framework Database First model that points to your SSDTSample database
- Add a MVC5 Controller with views, using Entity Framework
- Run your application and navigate to /People
And that’s it for your sample application. You now have a web application that points to a database on your LocalDB. Because you’ve scaffolded the controller, you can now view, create, edit and delete items from your database through your web application.
Add a database project
The following step is to add a Database project to your solution. If you have SSDT installed you can find this project template in the SQL Server folder.
By default, this will create an empty project that you can then use to develop your database from scratch or you can start with an existing database.
In this case, you already have a database so you can choose to import the database in your project.
All you have to do now is select the database you want to import. You can leave the other settings at their default values.
After the import process finishes the database project contains the files that describe all your tables, relationships, stored procedures and views. If you build this project you will see that the output folder now contains three files:
- MyDatabase.dacpac
- MyDatabase.dll
- MyDatabase.pdb
The most interesting one is the dacpac file. This a complete self-containing description of your database. This is the file that you can deploy to update an existing database.
Manually publishing your database
Now that you have your database project you can use this project to create new or update existing databases. This is very easy to do from Visual Studio. Just right click your project and choose publish.
In this dialog you specify the connection to your server and the name of the database you want to update. If you use a database name that doesn’t exist, the database will be created for you. Now just hit Publish to create your database!
After a couple of seconds you will see the following log:
If you now open SQL Server Management Studio (or the SQL Server Object Explorer in Visual Studio) you will see that the database is created with your table in it.
If you get an error regarding your SQL Server version make sure that the version defined in your Database projects properties matches the version of the SQL Server that you are trying to deploy to.
Making a change to your database
Now let’s say you want to add a column LastName to your database. You can use the Database Project, make your changes in the project and then update your local database. Or if you’re trying to make as less changes to your process as possible, you can add the column through SQL Server Management studio and then update your database project.
Let’s say you’ve chosen to use SQL Server Management studio and added a column LastName nvarchar(max) NULL to your table. Now you want to update your database project so it knows about the schema change. Normally, you would force your developers to create scripts for every change they make. But not with SSDT. SSDT can easily pickup the changes you’ve made and keep a record of those changes.
To do this, right click your project in the solution explorer and choose Schema Compare.
This will load a new Schema Compare. If you set the source to your database and the target to your project, you can now run a compare operation to see if there where any changes.
As you can see, the compare process found the change where you added a column called LastName. On the left it shows the current state of your database, on the right the state of your database project.
By hitting the Update button you can write the new found changes to your database project.
Updating a database
The previously deployed MyDatabaseDeploy database does not yet have the newly added column. However, this can be easily done by publishing your database project to the now already existing MyDatabaseDeploy.
All you have to do is right click your database project and select Publish. Now target the MyDatabaseDeploy Database and click Publish. This will automatically apply any changes made in your Database project to your target database.
If there is a change on data loss (such as dropping or truncating a column), you need to explicitly allow this. If you’re still not sure on what’s happening you can let the Publish wizard create a set of scripts that you can manually inspect and adapt to your situation.
That’s it for SSDT. Next time we’ll look at using Release Management to automate the deployment of a dacpac to your servers.
Feedback? Questions? Comments? Please let me know!