Hi. I’m still not Brent.
My name’s Alex, and I care about databases, DevOps and data protection. Last year I delivered a session on GroupBy all about how to do Database DevOps right, and back in May I wrote a blog post for BOU about how to screw it up.
Now, dear reader, I’m going to review and compare the two most popular tools to help you put it all together: Microsoft SSDT/Visual Studio Database Projects and Redgate SQL Source Control. I hope this post will help you to decide which is more likely to suit you and your team.
But first…
Why SSDT or SQL Source Control and not something else?
Good question.
Firstly, there are two styles of database source control and deployment: Model (aka “state”) and Migrations. I’ve written about the relative pros and cons here but the tl;dr is that neither are perfect, both hold value, and generally you do need to pick one or the other.
To avoid getting tangled up in higher level debates about whether to adopt a migrations-based or a model-based source control solution – or even a hybrid, this blog post is specifically scoped to model-based source control solutions.
So what about alternative model-based solutions?
I believe it’s important to use widely used and supported tools wherever possible. SSDT and SQL Source Control are the two most widely used tools in Europe and North America for model-based source control and deployment of SQL Server databases. Other model-based tools certainly exist (see ApexSQL source control, DBmaestro and dbForge Source Control for example) but none are anywhere near as widely used or trusted for SQL Server databases as either SSDT or Redgate SQL Source Control.
So let’s get stuck in.
Ease of use
Implementing database source control or continuous integration is a big change for your team and it will be hard on your developers and you will move more slowly to begin with. Choosing a tool that is easy to adopt will make your life much easier and lead to a far greater chance of success.
Redgate SQL Source Control plugs right into SQL Server Management Studio (SSMS), whereas SSDT lives in Visual Studio (VS).
This is enormous. If your developers and DBAs prefer to work in SSMS, SQL Source Control may feel very natural to them, but Visual Studio is full of new concepts: Solutions files? Project files? MSBuild? The list goes on. This makes the learning curve much more difficult for people who are used to working in SSMS.
It’s not entirely one-sided however. The SSDT table designer is amazing, combining the SSMS table designer and a query window and allowing you to use whichever you prefer.
On balance, SQL Source Control is much easier and more natural tool for most database folks.
Winner: Redgate
Refactoring
When deployments scripts are generated by schema comparison tools, which do not look at or understand your data, there are some scenarios which can cause the software problems. For example:
- Renaming a column or table
- Splitting a table
- Adding a new NOT NULL column
Both SSDT and Redgate SQL Source Control have features to help you in these scenarios, but the SSDT features are better.
Redgate’s “migration scripts” feature is hard to understand and has a tendency to break in nasty ways and cripple your performance. In contrast SSDT gives you both pre- and post-deploy scripts and the refactor log. SSDT pre- and post-deploy scripts are a pain to maintain, but ultimately, they work more reliably.
In fairness, Redgate have recently released their own pre- and post-deploy script feature, so they aren’t far behind – but the Redgate scripts do not support SQLCMD variable syntax like the SSDT version does and they do not have anything like the refactor log.
Winner: SSDT
Filters
Perhaps you aren’t interested in versioning objects prefixed with “test_”. Or perhaps you want to filter out some other objects that are supposed to exist in your dev environment, but not production, such as the tSQLt unit testing framework?
This is a big win for Redgate – SQL Source Control filters are, frankly, awesome. Whether you want to filter out something small (like the IsSqlCloneDatabase extended property added by SQL Clone) or whether you want to set up your own naming conventions, these things work a treat. You can even use them to do quite complicated stuff to manage multiple databases with several known variations, but I’d generally discourage going down that road if you can.
In contrast, while SSDT does include some filtering functionality in Visual Studio’s schema compare tool, it’s not as easy to source control your filters and to use them when publishing DACPACs to production environments. What you can do you need to do with PowerShell and the Schema Compare API, as Gavin Campbell clearly explains here. I’d also like to put in an honorary shout out to Ed Elliot’s filtering deployment contributor which makes the process a little easier, as long as your database folks are comfortable playing with C#, APIs and DLLs etc.
Another option SSDT provides, which is lacking in Redgate SQL Source Control, is to create partial projects with database references. To demonstrate how this works, read Ken Ross’ post on adding tSQLt to an SSDT project. While I prefer the way Redgate handles tSQLt for you, others like the way the SSDT database reference approach separates the tests from the rest of your code.
To summarise: The Redgate approach to filtering is generally easier to manage, especially if your team includes folks who aren’t .NET developers.
Winner: Redgate
Static data
Redgate SQL Source Control makes adding static data to source control super easy – just right-click and select the tables you want and voila.
However, there are a few issues with it.
- All static data tables require a primary key (although, to be fair, they should have one anyway!)
- The scripts generated are not easily ‘diff-able’ because changes to the data do not appear in-line.
- Column level or row level versioning, (e.g. to support seed data), is not supported. It’s all or nothing.
- Adding lots of static data can have a negative impact on performance.
In SSDT static data is handled using post deploy scripts and MERGE statements. Your post deploy scripts can be a pain to manage and the MERGE scripts can be annoying to write by hand, but these pains can be minimised by separating out the data scripts into multiple scripts/stored procedures that are simply executed by a single coordinating post deploy script, and by using sp_generate_merge to create your merge scripts.
If you can get over the pain, SSDT allows you to create data scripts that are “diff-able” and with far greater flexibility, for example to support seed data.
All that being said, since you can now add a post-deploy script in Redgate SQL Source Control, this means Redgate can support both options.
Winner: Redgate
Money
Redgate costs money. SSDT is free with Visual Studio.
You can buy SQL Source Control as a standalone product, but you also want the automated deployment bits and that increases the cost because each team member will need a licence for the full SQL Toolbelt.
At the end of the day, free is always going to be more attractive than not free, so if this was the only issue, clearly we would all use SSDT. The decision you need to make is whether the benefits listed above are worth the additional expense.
Winner: SSDT
More detail
There is a lot more to say so this post risks getting very long. The first draft was over four times longer than this version! Hence, I’ve created a much more detailed whitepaper to partner this blog post. You can download it from the DLM Consultants website: www.dlmconsultants.com/redgate-vs-ssdt
The full whitepaper covers all the issues listed above in much more detail, as well as also discussing:
- Shared development databases
- Security
- Deployment
- Support
- Complex projects
The full whitepaper has been peer-reviewed by Brent Ozar, a long list of SSDT and Redgate power users, as well as representatives from both Redgate and the Microsoft MVP program. I hope you find it useful.
Summary
To re-cap, here are our winners for each category:
- Ease of use: Redgate
- Refactoring: SSDT
- Filters: Redgate
- Static data: Redgate
- Money: SSDT
So which is better? Which would I recommend? Well, like most things, it depends. If there was a simple answer you probably wouldn’t be asking the question.
If you have read all the above and anything has jumped out at you as being particularly important to you and your team, great. Otherwise, if you are still undecided, the proof of the pudding will be in the eating. SSDT comes free with Visual Studio and Redgate offer a free trial – so download both and give them a try.
When I went to visit Farm Credit Mid-America, we set up three parallel proof of concepts (PoCs) and our cross-functional team voted (unanimously) for their favourite. As a result, implementation was much easier for them:
Whichever you choose, both products are an excellent investment of your time and/or money.
Good luck!
Want to learn more?
I hope this post has helped you to figure out whether SSDT/Visual Studio Database Projects or Redgate SQL Source Control are likely to be a better fit for you and your organisation.
If you would like my support to build your own proof of concept, get in touch through the website.
17 Comments. Leave new
This post (and equally, the links) is fantastically good. Thank you!
One more plus for SSDT, from my experience. If your developers use Domain Driven Design, or otherwise get their hands into SQL stuff, it’s great to have everyone using Visual Studio to store and check in changes.
That’s very true. Thanks for the input Jonathan.
I have to strongly disagree with the white paper that SQL Source Control is superior when it comes to deployments and CI/CD pipelines – this has always been a massive win for SSDT, and almost entirely worth the trade off of some of the missing features (what I wouldn’t give for better static data management in SSDT). The ability to validate and create a build artifact using the underlying API and common build tools (MSBuild) without having to actually connect to a database instance to deploy it for validation and without a really expensive licensing outlay is a huge benefit. The fact that SQL Source carried all that deployment baggage with it made it a complete non-starter.
Hi Steve,
Thanks for your feedback.
I’ve revised this a little bit in the whitepaper. You are right to call me out on the build stuff being nicer in SSDT. I respectfully stand by the Redgate SQL Change Automation being superior to SSDT/DacFx for deployment, but I have to admit there are a lot of folks who love that API.
I did already mention that SSDT is free and Redgate is not both in the blog and the whitepaper.
Alex
Don’t believe I have tried Redgate Source Control before but I did tried SSDT. I built a database project for my ERP system which has thousands of SQL objects and it crawls every time you open the project because of validation. Other than that I have no issue using the tool and its FREE. I currently use ApexSQL Source Control in SSMS for my ERP project.
Thanks for your comment. How do you find the ApexSQL tool? I’ve not had much experience with it so would be interested to hear about your experiences.
It wasn’t too bad. The integration with SSMS is pretty clean and have an intuitive user interface. They support most of the major version control out there – TFS, Git, Subversion, etc. It works with multiple developers as well. I manage to check-in 14k + SQL objects with it in about a minute. It works from what I used it for. I have not tried the deployment piece as I’d like to use Redgate SQL Compare for production deployment.
We used Redgate SourceControl in the pre-implemantation phase of our main tool for about 2 years, but stopped to used it before going life (in autumn 2016).
– It is a nice tool to quickly see the differences between your local database and the checked-in state
– it is good for small databases
– it became terrible slow for bigger databases, particulary when you use migration scripts (we have ~ 1,500 objects and I have to wait ~1 min for each compare)
– to make it more painful, they decided (whyever) to refresh the commit / get latest page every time you switch to it from another window (wait again, just because you checked something in another script). The existing user voice to change this stupid behavior is ignored since years.
– it automatical changes the the database every time automatical, you click on another database in the object explorer -> wait again
– the created deployment scripts works well, as long it are only minor changes (or only changes to e.g. stored procedures)
– when the changes are a little bit more complicated (e.g. add new columns + MS_Description + change triggers + FKs + indexes + some depending procedures) they tend to fail, because SC fails to bring the deployment script in the correct order, so it was unusable without massive manual work
– we had a worst case scenario, where the script (whyever) dropped about 20 (important) tables, just because it wanted to recreate another table
– when you change the formula for a computed column it recreates the whole table (create a empty copy with the new column definition; inserts all data into it; dropping the initial table; renaming the copy – and now imaging this with a multi million row table and lots of FKs and other dependencies) , instead of only dropping / recreating the computed column
– “no product is bug free and some bugs will not be fixed” is a popular “solution” for their support after a ticket is open a while.
For this reason we decide for another solution:
– we are creating the deployment scripts manual (update0001*.sql, update0002*.sql …)
– we have a batch file, which execute all updates that are not executed yet (traced in a table in the DB) to the database to deploy changes
– we have a separate source control database (mostly empty)
– a batch job checks every 5 minutes, if there was a new script checked into source control (the manual update scripts)
– if yes, it will be executed vs. the SourceControl database
– after this it will use the command line version of Redgate SQL Compare to script the database to a file system folder and commits every change to the database repository in our SVN
Hi Thomas,
I totally get your pain. SQL Source Control does struggle to do all it needs to do while embedded in the 32 bit SSMS and limited to 2GB of RAM. And many people have issues with the auto refresh. Fortunately, Redgate have been working on the performance stuff and they recently fixed the auto refresh issue. Whether or not this would go far enough to fix your problems I can’t answer:
https://redgate.uservoice.com/forums/39019-sql-source-control/suggestions/462220-don-t-refresh-the-commit-get-latest-automatically
Some of the other issues you raise are common to all “model-based” source control solutions, including both SQL Source Control and SSDT. The “Refactoring” section in this blog explains some of the features that SSDT and Redgate provide to help deal with those scenarios and sides with SSDT as the better solution. Fundamentally, however, if this is a regular problem it probably is wise to move to a “migrations-based” approach – as you have done.
One word of caution – and I realise this may be a little late for you but it may be relevant to other readers: Before coding the migrations tool yourself, I would encourage you to check out some of the open source tools that are available, like DbUp or Flyway. With respect, these have probably had more thought and time sunk into them than a home-grown solution and they mostly have decent documentation and a community of folks who can help you. In contrast, I’ve witnessed many home grown deployment solutions that are poorly maintained and documented and they can create their own problems.
If you are looking for a migrations tool that includes many of the bits you liked in SQL Source Control (and you are happy to pay for it) you should also consider Redgate’s SQL Change Automation Visual Studio Extension (previously known as ReadyRoll). This will generate your scripts for you and run them in sequence, but if you want to edit a script, because it drops those 20 important tables for example, you can.
If you are interested, I’ve written more on the pros and cons of the migrations-driven approach vs the model-driven approach here:
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
Best wishes.
We considered to use ReadRoll (now Redgate’s SQL Change Automation Visual Studio Extension), but since it uses Visual Studio and we not (our app is a web app with a Delphi sevice in the background), it was not really a solution for us (it would, if they will integrate it into SSMS).
Thanks for the hint relating the auto-refresh (although I think, that manually editing a configuration XML file is not really a solution – they should really consider to pay a developer for 15 minutes to add this into the option dialog…)
[…] Comparison Review: Microsoft SSDT vs Redgate SQL Source Control Interested in DevOps for MSSQL databases? Check this comparison prepared by Alex (T) and published by Brent (T). […]
Anyone use ER/Studio? I’m primarily looking for quality deployment script generation. Is there a better tool than SSDT for this?
ER/Studio isn’t something I’d normally use for script generation for production. It can work, but you’d be better off overall with Red-Gate, DBUp, SSDT, or similar tools that are designed for releases. ER/Studio is great for generating diagrams and working out new code, but I’d usually then push the changes over to a local DB and pull those into the DB code tool for releases to prod. For one thing, I often found that I had to dig a bit to name things properly in ER/Studio and didn’t always catch indexes as easily as I could type them.
The tools often have some sanity checks as well that will only push the changes if they don’t already exist. The ER/Studio scripts will do that for whatever you’re comparing against, but that may not be true for other systems down the line.
I appreciate this is an old blog post but when comparing the tools did you evaluate how to manage changes from multiple developers from their feature branches to the master branch with a SSDT project?
As each developers branch would make changes to the common .sqlproj and refactorlog files this results in merge conflicts.
This is a good observation. You are correct, the .sqlproj file in database projects would result in more merge conflicts. Hopefully most would be auto-resolvable.
This said, regardless of whether you use SSDT or Redgate, I advocate for the use of trunk-based development, where folks make a concerted effort to keep the delta between main and any source control branch to a minimum.
In my opinion, if either the delta between main and production, or the delta between main and any other source control branches, is ever large (more than a day or two worth of development), you need to rethink your branching and broader project management strategy.
Fantastic post!