Best Practices of continuous Integration with SQL Server project or local mdf file in project

Question

Today I maintain project that has really messy DB that need a lot of refactor and publish on clients machines.

I know that I could add a SQL Server Database project that contains just scripts of the database and creates a .dacpac file that allows me to change clients databases automatically.

Also I know that I could just add an .mdf file to the App_Data or even to Solution_Data folder and have my database there. I suppose that localDb that already exists allows me to startup my solution without SQL Server

And atlast i know that Entity Framework exist with it's own migrations. But i don't want to use it, besouse i can't add and change indexes with it's migrations and i don't have anought flexibility when i need to describe difficult migrations scenarios.

My goals:

1. Generate migration scripts to clients DB's automaticaly.
2. Make my solution self-contained, that any new Programmer that came to project don't even need to install SQL Server on his machine.
3. Be able to update local (development) base in 1-2 clicks.
4. Be able to move back in history of db changes (I have TFS server)
5. Be able to have clean (only with dictionaries or lookup tables) db in solution with up to date DB scheme.
6. Additionally i want to be able to update my DB model (EF or .dbml) automatically or very easy way.

So what I what to ask:

• What's a strengths and weaknesses of using this 2 approaches if I want to achive my goals?

• Can be that I should use sort of combination of this tools?

• Or don't I know about other existing tool from MS?

• Is there a way to update my DAL model from this DB?

Answer 1

What's a strengths and weaknesses of using this 2 approaches if I want to achive my goals?

Using a database project allows you to version control all of the database objects. You can publish to various database instances and roll out changes incrementally, rather than having to drop and recreate the database, thus preserving data. These changes can be in the form of a dacpac, a SQL script, or done right through the VS interface. You gain a lot of control over deployments using pre- and post-deployment scripts and publishing profiles. Developers will be required to install SQL Server (the developer/express edition is usually good enough).

LocalDB is a little easier to work with -- you can make your changes directly in the database without having to publish. LocalDB doesn't have a built-in publish process for pushing changes to other instances. No SQL Server installation required.

Use a database project if you need version control for your database objects, if you have multiple users concurrently making changes, or if you have multiple applications that use the same database. Use LocalDB if none of those conditions apply or for small apps that require their own standalone database.

Can be that I should use sort of combination of this tools?

Yes. According to Kevin's comment below, "If the Database Project is set as your startup project, hitting F5 will automatically deploy it to LocalDB. You don't even need a publish profile in this case."

Or don't I know about other existing tool from MS?

Entity Framework's Code First approach comes close.

Is there a way to update my DAL model from this DB?

Entity Framework's POCO generator works well unless you make changes to your DAL classes, then those changes get lost the next time you run the generator.

There is a new tool called SqlSharpener which can generate classes from the SQL files in a database project. I have not used it so I cannot vouch for it but it looks promising.