Monthly Archives: August 2024

I was privileged to host yet another T-SQL Tuesday, for the month of August, 2024. My topic was on Managing database code. I was worried about getting responses, given that most of the community is no longer on X/Twitter and my invite didn’t seem to get much attention. I was wrong. People seem to keep track of this by other means and the response was excellent. Summary as below.

Kevin Chant (b/l) talks of his experience and preferences around repos and CI/CD pipelines using Azure Devops/Github for  SQL Server, SQL Pools and Microsoft Fabric Data Warehouses. He talks about using Projects for doing state based migration and migration based deployments, where repo structure can be quite different from a standard one. His post has a lot of links to technologies he has used and includes an invite to engage with him, for those who want to learn more.

Long time Data Platform MVP, MCM and friend from down under – Rob Farley (b|l) differentiates between ‘where the database code lives’ and ‘where do you keep your database code’ . From a consultant’s perspective, I can totally understand the difference between those two questions. It was revealing to me, that as a full-time employee for six years, that difference did not even occur to me because we inherently own what we manage. He talks of doing whatever fits in with the client’s existing processes – while recommending a script-based repository if they don’t have one. He also seems to use the method as I do personally – Powershell based SMO, to script out objects.

Well known conference speaker/DEI Champion and Data Platform MVP Deepthi Goguri (b|l) talks of a simple version control process using RedGate source control and GIT, that she uses where she works.

Tom (b) talks of 3 approaches to version scripts – a state-based approach, a migration-based approach and a hybrid approach. They talk of the common aversion to consider database code as repo worthy as well.

Data Platform MVP, Conference speaker and WIT champion Deborah Melkin (b|t) writes about many types of scripts and their importance to workflow for database developers. She emphasizes the importance of having code in source control, regardless of how one chooses to do it. She also has a session on ‘Database deployments demystified’ for this. I found the recording here and recommend it.

Andy Levy (b|l) blogs about usual Visual Studio database projects along with powershell to generate scripts and keep up the SQL Server based repo he manages. He also speaks of managing a MySQL repo with just scripts needed for changes, and having large swaths of code that is not in the repo.

Data Platform MVP Eitan Blumin(b|l) blogs about the advantages of GIT over TFS/VSS, and his favorite tool for CI/CD, Azure Devops. He also explains the advantages of SSDT over other tools in detail.

Data Platform MVP, speaker and author Richard Swinbank(b|l) talks of basic principles behind code management (resilience, repeatability), and the process he uses to manage code.

Jeff Mlaker(b) talks of the pitfalls of not using source control for database code, with some interesting personal stories including one on merging different versions of code when no source control existed.

Lastly, long time Data Platform MVP, DevOps Advocate and currently owner of the T-SQL Tuesday blog series, Steve Jones(b|l), talks of the importance of getting code into repo and the tools he uses to accomplish the same.

My own humble two cents on this topic is here.

This is my T-SQL Tuesday #5. It is a great way to understand multiple perspectives and experiences around a topic, get more blog traffic and gain more contacts to add to your community. Contact Steve if you wish to host one.

Thank you to everyone who responded here. Your responses enriched my knowledge of this subject and helped me find more community. #sqlfamily #sqlwinners.

This is my own contribution to the T-SQL Tuesday I am hosting – on managing database code.

I am from the older generation – where the farthest we went with managing code was use VSS/TFS to get the latest version and deploy it. In some cases, I’ve managed it. If we wanted to search for existence of an object or its usage, we simply searched the database – using some custom tools/dmvs or 3rd party tools like RedGate’s SQL Search. History of who changed what – well that came from their tickets or if it was code – from code comment history. With the advent of GIT, all that changed. I did not adapt to GIT very quickly. I went, kicking and screaming. I hated the clunky interface. I hated not knowing what the command actually did, but still had to use it. I did not see the point of learning what I thought to be over engineered version control. But over time, I’ve learned its value. I can find almost any code that has been committed. I can see who changed what, and when, very easily. I can search for code usage and objects, really quickly. Granted, the interface is still clunky and merge conflicts aren’t fun when they happen, but the advantages override all that, significantly. There is one place where we can easily see all the code related to a database. We can evaluate how complex it is and even use it for legal reasons as code base of the company.

As of now, I use GIT for source control extensively. At work all our database objects are scripted out and have their repo. When we make a change, the new objects are auto scripted with the changes.

I am a fan of SMO, for this purpose and have scripts I’ve developed myself. Granted, SMO only works for SQL Server. I am learning more on similar tools for Postgres. I am often frustrated that Redshift has none. I strongly advocate using GIT and putting all your database objects into a repo using whatever means you can get. (Or develop your own means). It is worth a lot more than you think.

I am excited to host the T-SQL Tuesday blog party for August 2024. I’ve done this many times, but I always remember when I was new to the community and how much it helped me gain recognition. Thanks to Adam Machanic for starting this initiative and to Steve Jones for keeping it going.

This month’s topic is ‘Managing Database Code’. I initially called it ‘How do you manage your database repo?’, but Steve pointed out that many people in the database community might not know what a ‘repo’ is. So, we simplified it to ‘Managing Database Code’. Managing code has been a big part of my work as a database engineer for the past six years.

A repo, for those who are not aware, is just short for ‘repository‘, a place to store stuff. In this context, a place where ‘keep’ code. It is version control for code. I remember the days before repos were common. We used tools like VSS/TFS and searched through tickets and comments to track changes. We used DMV-based queries to find objects. Now, with repos, everything is much easier. I can’t imagine working without one.

I’m curious about how others manage their database code. Here are some questions to consider:

1. Where do you keep your database code? Is it in a GIT-based repo, or just in the database the old-fashioned way?
2. If it is in a repo, how is it created? Do you script out individual scripts and add them, or do you use tools to script out the entire database, like SSMS scripter, SMO-based scripts, or third-party tools? This could apply to any relational database, not just SQL Server.
3. How do you keep your repo up to date when code changes? Depending on what your shop does, you may or may not have a well evolved CI/CD pipeline. If you do, explain how it works. If you don’t, that’s fine – we just want to know where the code resides, if it is outside the database.
4. If you don’t have a repo, why not? How do you manage things like searching code or finding who made changes and when?

I look forward to reading your responses. The rules

• Publish your post on Tuesday 13th of August , time zone does not matter.
• Include the T-SQL Tuesday logo at the top of your post and link it back to this invitation post.
  • A comment letting me know you have a contribution is ideal but if that’s not doable, just send me an email!)
• If you’re on X/Twitter, share your post using #tsql2sday.

If you want to know more, check out the T-SQL Tuesday Rules of Engagement.

Thank you!