How to use SQL Server Database Project

Question

I am running SQL Server 2012 and VS 2010 with SSDT (SQL Server Data Tools) installed. My dev DB uses stored procs, functions, CLR objects, etc. It has a snapshot of prod data of about 500GB.

I created SQL Server Database Project and then imported the database. This created all tables, views, procs and functions files under schema names. Great stuff -- now I can do a version control just like in other VS projects, create deployments, etc. So far, so good.

But, I am confused as to what my development process should be for changing/adding procs/tables under SQL Server Database Project. It appears that any changes I make are applied to some LocalDb/Projects database and NOT to my dev database.

Am I suppose to author all my objects in that LocalDb, then Build and deploy to my dev database via Publish? I am worried about my existing tables in the dev DB since if the publish process drops and recreates tables, I will loose my prod data snapshot.

What is the right development process to follow in SQL Server Database Project?

Imprecision

You can configure to which DB that DBPROJ deploys to. It will warn you if it can't make a 'breaking' change, i.e. one which would require dropping of a table and loss of data. You also have the option to generate a change script, instead of directly changing DDL - this gives you the option of viewing the changes. – Maple

Is nobody using SSDT to develop stored procedures / views / funcitons? Because I have got to believe there is a better answer than re-publish and "try it." Maybe Unit Tests are what Microsoft Intended us to use? What are other users doing? – Blayze

Answer 1

Think of the source database (in your case, your database project) as being the "to be" state after deployment. When a deployment is initiated, the executable (SqlPackage.exe) compares the source with the target and generates a difference/delta script to make the target look like the source. This is why we no longer have to specify CREATE or ALTER; the tool figures it out. To answer your question about ongoing development, you can develop either way. You can develop in the project files and publish them to a common Dev database (say, if you're on a team), or you can develop in the database with tools like SQL Server Management Studio (SSMS) and synchronize with the project files with a schema compare (I use the latter technique because I like SSMS).

For deployment, you'll have to have SSDT installed on the machine from which you execute the deployment (SSDT ships with SQL Server 2012 and later; I don't know about SQL Server 2008). You can create scripts to simplify deployment. You'll essentially call SqlPackage.exe (it lives in x:\Program Files (x86)\Microsoft SQL Server\nnn\DAC\bin) with an action and a source. I use Publish Profiles as well to take care of most command properties. So an example deployment might look like this:

SqlPackage.exe /Action:Publish /SourceFile:MyDatabase.dacpac /Profile:MyProfile.publish.xml

For more information: SQL Server Data Tools Documentation http://msdn.microsoft.com/en-us/library/hh272686(v=vs.103).aspx

SqlPackage.exe Documentation http://msdn.microsoft.com/en-us/library/hh550080(v=vs.103).aspx

Phenolphthalein

I guess the part I'm missing is the "Develop in the database with tool like SSMS" - what does that look like when using a DB Project? I'm used to having my script files with a IF EXISTS...DROP... at the top and GO ... EXEC SprocName @params ... at the bottom. So I can edit the sproc, hit F5, and see the new results. But if I save that file to DB project, it will complain because it ONLY wants the CREATE statement. I don't want to manually set that up each time I make a little change to sproc. What does the process of making/testing a change to a sproc/view look like for you? – Blayze

One of the first things to learn about SSDT, as with any tool, is its view of the world. As I said in my post, SSDT sees the /SourceFile parameter as the state that the target database must be in at the end of the deployment. And to get there, it generates a delta script, based on comparing the target to the source, to make that happen. If your stored procedure does not yet exist in the target, it generates a CREATE PROCEDURE statement to create it; otherwise, it generates an ALTER PROCEDURE statement to update it. – Phenolphthalein

By "Develop in the database with tool like SSMS" I mean to use SSMS to write your stored procedures as we've traditionally done. Since you're working directly with with the database objects, you'll need to use CREATE/ALTER accordingly. When you're done, go back to the Database project and use a Schema Compare to synchronize the new or updated stored procedure with code in the Database project. – Phenolphthalein

Thanks Craig - I'll give you the bounty because I think this is going to lead to my final solution. I'll post it as an answer if it ends up working well. – Blayze

Answer 2

1. Make changes inside the VS DB project.

2. Deploy changes to localDB to test

3. Publish the database to your production server. I prefer to use Schema Compare to do this manually, but you can also publish the project via the right click --> publish menu (which will also create a publishing profile), or using command line arguments. The publish process won't drop and create tables (unless you tell it to drop & recreate the entire db).

Alternatively, in the project settings you can change the connection string to point to your production server (as pointed out in the comment). However, I recommend against this, as it will then attempt to publish to the production server every time you run a local build (F5).

Leak