This year I was able to attend the PASS Data Community Summit for the first time, and it was also the first time I had ever attended a professional conference in my career. Not only did I learn more than I expected about new and existing databases and tools, but I was also able to meet many interesting new people that I never would have been able to connect with otherwise. Each of the people I chatted with at various points throughout the conference had a unique story to share and was more than happy to introduce themselves, share their experience, and offer me help going forward if I need it. Going in as someone who’s never been to such an event before, I was relieved to find that I was fully welcomed and not made to feel like an outsider.
While I really enjoyed meeting so many new people from all around the world (I can’t believe how far some people traveled!), the best part of the conference was learning about many new and useful topics. These are the most interesting highlight items I learned across the three days of the conference:
Red-Gate Test Data Manager
I think this was the most exciting thing I learned while at the conference because it would be the most beneficial for my job and company. Currently, my organization has a homegrown tool that pulls down a subset of test data (from our QA environment) to each developer’s local database. While this tool is useful and has been mostly sufficient for us since it was created, it’s becoming harder to maintain and less useful to developers over time. The main issue we have with our custom tool is that it’s set to only pull down 500,000 records for any given table, with no regard (for the most part) for getting useful data for testing. Often, when trying to test a complicated or totally new process, the data subset either isn’t enough records or the records aren’t what is needed for testing.
That is why the Test Data Manager (TDM) from Red-Gate is so interesting to me. It solves all of the problems we currently have with our custom tool plus more we hadn’t considered yet. It allows you to create a clone in less than a minute from production data (my organization’s tool currently takes a long time to pull down all of the data and it pulls from our QA environment which isn’t as good of a testing set). TDM also allows you to mask any Personal Identifiable Information (PII) while keeping that data relevant, related, and useful after it has been masked (our custom tool isn’t that smart). Additionally, you can select a premade subset of data for any given table (small or large) or you can specify your own custom type of subset (e.g. only pull customer data for 2022 customers), and the software will ensure the data stays related and meaningful as it creates the subset of data.
Unit testing for databases is possible
You might think this is obvious, but the fact that unit testing can be added to database code now is crazy and exciting to me. I figured that it would be possible to write unit tests for stored procedures since that’s just code, but there’s a whole world of possibilities for other ways to unit test databases using tools already created and perfected by others. The two main tools that were discussed in regards to database unit testing were tSQLt, an open-source testing framework, and Red-Gate’s SQL Test which utilizes that framework while also giving additional capabilities for your team. While I haven’t had a chance to try to use either of these tools yet, I look forward to exploring the world of unit testing. I would love to be able to have a way to automatically test database code changes, since currently, all testing of database-related code changes, whether it’s SQL or SSIS, must be done manually by someone else on my team (and testing resources have been scarce lately).
PostgreSQL (PGSQL) is similar but also really different from Microsoft SQL Server (MSSQL)
Over the course of the Summit, I went to many different sessions related to PostgreSQL, some of them more helpful than others. What I learned is that there are multiple different tools in the AWS platform that could be useful to others to help in their transition from MSSQL to PGSQL, but those tools might not be as useful to my company. We are considering a full transition to PGSQL in the coming years for cost savings, which means that we will want to completely move our current application systems from one engine to the other.
One of the AWS services I learned about that didn’t seem like it would be as helpful to us is BabelFish, which is a service that allows you to create an instance of PGSQL in AWS and still use your existing TSQL queries to interact with the database. This service seems like it would be very helpful for anyone who is looking to migrate a single database instance, or maybe up to a few, to PGSQL without having to rewrite their entire codebase, since it lets you keep working with your same application code. BabelFish probably won’t be helpful to my team in our migration efforts since we do want to do the full switch, so there would be no need to spin up BabelFish instances that we would later have to migrate fully anyway (especially since we’re looking at moving dozens of databases).
The other AWS services that were discussed for migration from MSSQL to PGSQL, Schema Conversion Tool (SCT) and Database Migration Service (DMS), seem like they would be closer to what my organization needs for our transition to Postgres. These tools allow you to convert most of your existing TSQL codebase to Postgres without any work on your part. However, AWS doesn’t claim that the converted code is going to be aligned with PGSQL best practices or will be performant, so that could be a challenge we’ll have to deal with later. The first half of the migration process with these tools is SCT which does the conversion of tables, schemas, functions, procedures, etc. from TSQL to PL/PGSQL Then the second half of the toolset, the DMS, would help with moving the data from one server to another. If I was going to guess how my organization is going to manage the transition from one engine to another, I would say that we’ll likely use these two tools to get us started, recognizing that they won’t get us 100% converted and we’ll have to do some manual conversions ourselves as well.
The last thing I learned about how Postgres differs from Microsoft SQL Server is that the indexing system is much more customizable and specific in PGSQL than in MSSQL, and that means I will need to learn a lot about indexes in the former. MSSQL only has a few different index types you can use, clustered, nonclustered, and columnstore, while PGSQL has at least 5 or 6 different kinds that are built for very specific use cases. While it may be a bit confusing to me, a career-long Microsoft database developer, to figure out the different types of Postgres indexes available when we start the transition, I’m going to focus on what an interesting learning opportunity it is. This will be the first large database engine change I will have in my career, and learning a new system will make me a better developer and a more lucrative job candidate in the future.
AI is being added to tools many of us use daily, to help us work more efficiently so we can do the more important and interesting work
This is one of my final takeaways from the conference, but I don’t think it will be quite as useful or relevant for my current role as the other things I listed above. A large part of the sessions at the Summit this year were focused on how AI is going to start changing how we work, and that we are going to need to adapt and learn how to use it to benefit ourselves instead of being left in the technological dust by refusing to use it. AI certainly isn’t going to be stealing our jobs in the next couple of years, but it will change how we work so we should prepare for that.
For myself, I plan on using AI tools currently available (mainly ChatGPT) to try to help me be more efficient in my role, probably with non-coding tasks such as writing emails and documentation. AI can help you work much more efficiently so you can spend most of your time on what is most important to you and your company instead of the mundane daily tasks that hog your time. That is the most exciting takeaway from the AI discussions I attended at PASS.
In the future, when other new AI tools, such as Red-Gate’s SQL Prompt+ (which was announced at the conference) are released, I also intend to play around with those to see how they might increase my work efficiency. The demo that the Red-Gate team gave for how SQL Prompt+ can be used is to highlight a query in a stored procedure and ask the tool for recommendations on how to improve the performance of the query. While I guarantee the tool is going to make a lot of bad suggestions that a human can easily tell won’t improve anything, I also think that it may provide a good starting point for investigating underperforming queries that you may not have thought of yourself. That is why I go back to my point of AI not taking our coding jobs in the near future but it becoming a great resource for making your job easier.
The concept of AI in general is a bit frightening (as a lover of sci-fi and horror books and movies), but it’s not likely to take over the world anytime soon. However, it may trickle into your workplace in the next few years so it’s better to confront change head-on and figure out how you can use these new tools to your advantage.
Summary
I hope that in the future, I’ll be able to attend more of the PASS Summit events since this year’s conference proved to be such a fun and relatively easy way to learn a lot of new information relevant to my career. Whether you’re newer in your career like me or an old hat in the trade, I really would recommend attending the next PASS Data Community Summit if you’re able to. You never know what you’ll learn that could improve your work life and make you a better developer.