Recently I was asked what tools database professionals use and I wanted to clarify a little bit on the topic so here goes. Previously we used to have a number of different tools when working with the database. If you look at just SQL 2000 we had Query Analyzer and Enterprise manager one for the Developer and another for the DB. That evolved into SSMS which combined everything and put all the stuff the developer and DBA needed into a single customized version of Visual Studio called SSMS or SQL Server management studio. However because BI tools were still evolving at that time we ended up with another UI just for BI Projects called BIDS (Business Intelligence Development studio). This worked well initially since teams were broken up into DBA-DEVELOPER, MSBI DEVELOPER and .NET DEVELOPER. So we ended up with three different job roles and therefore three different tools. .NET Developers continued to code in C#, Vb.NET and the occasional database projects. The DBA had everything he needed in SSMS such as PBM, CMS, SQL Agent etc. and the MSBI had SSRS, SSIS and SSAS in BIDS. But SSMS and BIDS are actually just customized versions of Visual Studio so it was inevitable at some point they would merge into a single framework where all coding can happen be it a web page , stored procedure or a SSIS package. Hence we arrive at the latest version which is SSMS for the DBA, SSDT for the MSBI Developer and Visual Studio for the database developer.
While these are still three different products they are at their core the same thing. A UI which contains a common toolset for what most programmers need along with very specific projects types that can be imported as needed. The challenge at this point is MS releases new versions of Visual studio frequently and along with that different editions. A project that’s created in one version needs to be upgraded before it can be opened in a new version. Often the upgrade is irreversible and now you maintain two different versions of the same code. One that opens fine in BIDS and another that’s been upgraded to open in VS 2012. At some point the hope is these teething troubles will be overcome and projects will open seamlessly between different types and we will achieve better integration of code across the different projects but until then we all we can do is wait and watch.
Here is another diagram just from the tools perspective