Sql-server – Visual Studio Database Project

database-designsql servervisual studio

I work in a company that is deeply ingrained in .Net programming. I am responsible for managing a couple SQL Servers, with about a dozen databases in total. Our programming manager recently demonstrated the Visual Studio Database Projects for my team and I am looking to know if anyone is using it and if so the pros/cons. Right now I have to manually promote changes from test to production via SSMS which VS would eliminate. I also have a large collection of SQL scripts on our SAN that are fairly unorganized and it would be nice to include them in a common solution.

My apologies beforehand I know this is probably not the best place for this question since I am looking for opinions rather than a technical answer…

Best Answer

I would suggest using SQL Server Data Tools (SSDT), which is essentially the latest iteration of the Visual Studio Database Projects. I use this at places that do not already have a way to source control their databases since it easily integrates with TFS, which most Visual Studio shops use for source control.

A couple pros I have for using it are:

  • Source controlling database schema
  • Able to test changes locally before publishing to server
  • Easier workflow when changing project and generating change script to server
  • Peer reviews are simpler as the other person just needs to open the project
  • Table design has most items (indexes, constraints, foreign keys, primary keys, etc.) right in designer instead of opening multiple areas
  • Latest version has everything in one install (database schema, SSIS, SSRS, and SSAS).

A few cons for using it would be:

  • Change script may be as simple as you would like (sometimes a change creates a new table and copies the data into this table, which could take a long time depending on row count)
  • Generates change script using SQLCMD syntax so be aware of that
  • Default settings detect changes that may be irrelevant so sometimes best to not check for those (permissions, role memberships, and users are examples)
  • Referencing external sources may not be intuitive (i.e. table in another database)
  • This only supports SQL Server (the older Visual Studio Database Projects supported more than SQL Server).
  • No schema design tool

Having said all that, I still use it and recommend it. I generally try to let developers use just that tool instead of SQL Server Management Studio (SSMS) because I look at SSMS as a DBA tool and SSDT as a developer tool.

Related Question