Kendra Little

7 December 2019

Dev team chat: Adding static data to SQL Change Automation in SSMS

A great part of working at Redgate is that I get to know folks in our software development teams. They’re smart, fun people, and I love getting a view into how they build our solutions.

This post is the first in a series where I’ll interview members of our Versioning and Automation team over a cup of coffee (or tea). I hope to give you glimpse into what our users care about, how we work, and share what we think about when we are building our products.

For this article, I spoke with:

• Maya Malakova, Technical Lead, Versioning & Automation
• Robert Farrell, Redgate’s new Business Lead – but playing the role of Product Manager for the purpose of our call
• Jon Boardman, Product Designer

Background info

SQL Change Automation gives you a migrations-first approach to authoring database changes. We have a handy comparison chart which explains the differences between Redgate’s state-first and migrations-first approaches.

SQL Change Automation has long had a plugin for developers to author changes in Visual Studio – at first under the name ReadyRoll. This year, Redgate released a plugin which allows teams to also work in SQL Change Automation in SQL Server Management Studio (SSMS).

This SSMS plugin is a big feature because many teams have developers who prefer to work in Visual Studio, while Database Administrators (DBAs) prefer to work in SSMS: now they can collaborate on the same project in the development environment of their choice.

What changed in this release?

Maya: We added a feature to manage static data in the SQL Change Automation Plugin in SSMS.

This allows developers to control the data for a table in source control, in addition to the schema. The most common uses for this are things like tables which list country names and country codes, zip codes, things like that. By including the data for the table along with the schema in a version control system, reference data can be automatically deployed to all environments in development, test, and production, and automatically updated whenever the reference data changes.

Note from Kendra: Maya wrote a technical summary showing how to use this feature for the Product Learning section of the Redgate Hub.

How many users care about static data?

Maya: We’ve heard from users that they really care about static data. 20% of the users who gave us feedback about the SSMS plugin said that it was a big enough deal that if the plugin didn’t support static data, they would prefer to continue working in Visual Studio – although it wasn’t their preferred tool overall.

We found that this is because switching back and forth between Visual Studio and SSMS breaks the flow of work for the user. There were possible workarounds to make static data work in SSMS without a real feature, but they were cumbersome and not nearly as good as our feature in Visual Studio.

Robert: We always use examples of zip codes and country codes, but there are many other potential uses of this, depending on the workflow and what kinds of data people want to seed. That’s part of why more people care about static data than you might initially think.

Wow, 20% is more than I would have thought. Why did we release static data in a minor version, instead of a major version?

Robert: We chose to release the SSMS plugin without static data initially to get people using it. This was a strategic decision for a couple of reasons.

It’s lower risk to release a Minimum Viable Product (MVP) first – we had people using it, but we wanted more feedback. So we released the 4.0 version. Even if people didn’t want to commit to it because of the lack of static data, that response itself was useful information.

Maya: We knew it was useful without it as well, because of the number of users we had using  our Early Access Program before the official release.

What’s your approach when adding a feature like this into the SSMS plugin, given that the Visual Studio plugin already has an existing workflow?

Maya: We took an independent approach with this feature and did some user experience (UX) exploration, which led to us adding in some extra features in the SSMS plugin. We’ve had initial feedback that these extra  features are useful.

They include warning about adding tables with high rowcounts, a new filter field to search by table name, warning early if requirements are not met to add a table as static data (tables must have a primary key to be added). We also thought through some new UX improvements about how to add and remove the tables.

We also made the decision to make the static data feature overall more discoverable in SSMS because of that extra feedback we had from the users about its importance. It’s on a tab and is easy to find in SSMS for a new user, whereas it’s on a context menu right now in Visual Studio.

Jon: To do UX design, we have sketching sessions, we look at feedback and discuss among the team.

Robert: We decided to build the best UX we could, rather than trying to make it the same user experience as Visual Studio. We don’t want to build something completely different – the one in Visual Studio is good—but the idea is to forge ahead instead of building something identical.

Maya: One extra note. We work closely with our designers and we listen to users a lot, but it’s also good to take feedback from our development team. We’re also building tools for developers, so they have good insights about user experience as well.

Jon: The rowcount feature Maya mentioned is a good example of this – this is an idea for a feature that wouldn’t come just from a designer.

Robert: We’re also happy that we turned this around pretty quickly after the 4.0 release. This released in less than 3 months from the 4.0 release. So although we decided to release an MVP, we were able to add this robust feature quickly.

Is there more to come with this feature?

Maya: Yes, we’re considering adding more features to static data configuration in response to user requests. But of course we’re balancing this with other feature requests that we’ve heard as well. Either way, there will be more to talk about soon.

Thanks to Maya, Robert, and Jon for the interview.

If you have questions or are curious about how our development teams work at Redgate, I’d love to hear about them! Send them to me at kendra.little@redgate.com.