3

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...

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.