If you’re anything like me, managing and developing a SQL Server database has always been synonymous with working inside SQL Server Management Studio. It provides most of the essential elements of an integrated development environment– syntax highlighting, debugging tools, object browser – and it comes free with every installation of SQL Server, so it’s a no-brainer for most BI professionals. Recently, though, I’m becoming a huge proponent of SQL Server Data Tools, Microsoft’s set of BI plugins that run inside Visual Studio. Its features have evolved over the years and today I’ve found it’s possible, except for a very limited number of tasks, to completely switch my workflow over to SSDT full-time. Here are three reasons you should too.Reason #1: Integrated Source Control
Any team development activity requires source control, and databases are no exception. Unfortunately, only about 58% of database developers use any source control system at all, according to a Red Gate DevOps survey. This is unfortunate, since database objects are just as vulnerable to development conflicts as any other type of source code, and often require point-in-time recovery. Fortunately, Visual Studio is designed to run with Team Foundation Server (TFS), one of the best centralized source control platforms around. Of course, there are multiple third-party source control plugins available for SSMS, but none have tight integration the way that Visual Studio and TFS do. A typical workflow involving a central TFS server and three developers is illustrated below.
How many times have you run into this scenario: developer A is actively working in a shared dev database. He or she changes the name of a column. Developer B is writing a stored procedure that refers to the old column name. Suddenly, the stored proc breaks, and they have no idea why. It worked just fine a minute ago! Developer B then spends the next 10 minutes hunting down the issue, only to discover it was a result of A’s work. Wouldn’t it be nice if there was a way for both A and B to make changes to dev without the risk of overwriting each other’s work? Visual Studio is designed exactly for this. You’ll notice that when you install SSDT, you’re given the option of installing SQL Server Express on your local machine. Usually this will be given the name ProjectsV13 (or something similar, depending on your version of VS). This local instance is meant to be used as your own personal debugging database, and you can deploy objects to it and query it without breaking the central development database. Any conflicts can be resolved at the project level in Visual Studio before the final deployment to dev.
A DAC is a data-tier application, which is basically a file containing the shell of a database, without the data. It gives databases portability, upgradeability, and are a godsend to database administrators (DBAs) everywhere. Prior to this construct, developers would often need to keep track of all the changes they made to a database since the last deployment and manually script out the ALTER statements to give to the DBA as part of a change management process. Now, instead of manually scripting the changes, the developers can build their project in Visual Studio, generate a .dacpac file (which happens automatically during a build), and hand that DACPAC file over to the DBA. The DBA can then generate a change script automatically by having Visual Studio compare the contents of the DACPAC to the existing database. This is done by right-clicking the database project and choosing Deploy in Visual Studio.
If you’re involved in any kind of Microsoft-based BI development project, SQL Server Data Tools should be in your toolkit. It’s introduced tons of powerful features that enable developers focus more on the development of their code, and less on the management of their code. Going from SSMS to Visual Studio requires a bit of a learning curve and a habit change, but I’m confident that almost all developers can increase their long-term productivity by making the switch.
To learn more about creating elevating your current Data Warehouse or BI solutions, click here for CCG's Analytics Solutions or contact a consultant at (813) 265-3239.
CCG understood our project needs very well, they are very responsive and we could not ask for anything more. The solution they provided fit perfectly with our expectations and business goals.GOP Data TrustChief Data Officer
I cannot overstate the delight we experienced from the outcome of our project. I would not only recommend CCG to any company, but question why they would engage with anyone but CCG.PgiDirector of Customer Success
Working with CCG is like working with extended team members. Consultants become an integral part of the work bringing expertise for cutting edge design and development.Hillsborough County Public SchoolsChief Information and Technology Officer
CCG's team is positive and eager. They are a great big bunch of wonderful people trying to make a difference.Hillsborough County Public SchoolsDepartment Manager
I knew CCG's technical expertise and dedication to quality results would be invaluable to our project success based on our past partnerships. We could not have implemented in the short timeframe like we did without their assistance. CCG is #1 on my speed dial for successful project implementation.InCommDirector, Financial Information Systems
It was evident from the onset of negotiations through the implementation that CCG took their role in the partnership to heart and we believe it has been instrumental in our success.Interval InternationalDirector of Marketing
CCG works very hard to understand and align with our needs. It truly feels as though we are on the same team!Fortune 500 HomebuilderBI Manager
CCG came to our company in a time of much change. Their team partnered with ours, continually delivering with professionalism and efficiency. We would not be where we are today without the expertise CCG brought to the project.PSCU Financial ServicesSenior Program Manager
CCG has a good industry knowledge, we are very happy that we chose to work with CCG. They have been a great help strategically and are helping us make important decisions.Minneapolis Public SchoolsHuman Capital Coordinator
Other Vendors use the word Partnership, but CCG actually means what they say. I can’t thank them enough for their professionalism and willingness to work with us as a true Partner, not just another vendor.PODSCIO
Our CCG Consultants are total rock stars: very thorough with a solid knowledge of the financial services industry. As a bonus, they are very easy to get along with – a great fit for our team.Raymond James Financial ServicesSenior Manager of Enterprise Data
CCG's team are all amazing. Thank you, CCG, for all that you do to make us great and keep our credit unions moving forward!PSCU Financial ServicesVP Enterprise Analytics & BI
Other Vendors use the word Partnership, but CCG actually means what they say. I can’t thank them enough for their professionalism and willingness to work with us as a true Partner, not just another vendor.PODSChief Information Officer
CCG's Team is very professional and responsive. They are making our job very easy.Rollins, Inc.Senior BI Analyst
CCG did an excellent job! Their team was very flexible. They gave us everything we asked for and then some.Rooms To GoSenior BI Architect
I'm amazed at the talent at CCG, not just the skillset - they're really good people. We've already referred them once and will do so again!Ruth's Chris Hospitality GroupCIO
CCG did a great job! We're extremely impressed with what was built in a short time. CCG has delivered ahead of time and with best practices, it's been a pleasure to work with them.VologyVP of Analytics
2502 N. Rocky Point Drive, #650, Tampa, FL 33607
Phone: 813.968.3238 | Fax: 813.200.1357
8000 Avalon Blvd. Suite #100, Alpharetta, GA
Phone: 404.328.7298 | Fax: 813.200.1357