Upgrade your SQL projects with confidence with DacpacVerify | Data Exposed

Key Takeaways

You've been hearing a lot about SDK style SQL projects, but how can you easily go from the original SQL projects to SDK style and safely? Learn all about DACP pack verify this week on Data [Music] Exposed. Hi, I'm Anna Hoffman and welcome to this episode of Data Exposed. Today I'm joined by Drew, a member of the SQL Tools and Experiences team. Drew, thanks so much for coming back on Data Exposed. Thanks for having me back. Always a pleasure. And you always bring us some great developer topics and today is no exception. We're going to be talking about this new thing called Daktac Verify, which I know literally nothing about. So, I'm excited to learn. I'm sure our viewers are as well. Um, but maybe it would be good if we backed up, gave a little context. Uh, for folks who've been using SQL projects maybe for a very long time, there's this new thing or relatively new thing called SDK style SQL projects. Can you tell us a little bit about what it is and why someone would consider moving, especially if they were using the old one or maybe they're not even using it today? Yeah. So, it's it's either if you know the original SQL projects or you're new to them. It's kind of good for both scenarios because if you're used to the original SQL projects, there is this migration pathway that we're going to talk about today. Um, and a lot of the fundamentals are the same uh in terms of, you know, development in Visual Studio and being able to integrate with uh DevOps pipelines. However, if you're new to SQL projects, this is also a great time because it's simplifying the project file and adding a number of new features that integrate really well with modern paradigms like package references. So, we have uh kind of the the great moment for both both sides of developers. Now, what SQL projects actually provide is that development process integration. So, you're going to be doing a build of your project. So it kind of gives you a a nice development process around your database. So obviously if you're using the original SQL projects, you're going to want to start thinking about upgrading to SDK style of SQL projects because it gives you crossplatform compatibility or package references both for database objects as well as code analysis rules. And then you just kind of kind of snowball from there of there's this future for SQL projects that gets really exciting. Awesome. Cool. That sounds great. Okay. So now let's assume I I'm using SQL projects today, the original uh and I want to kind of upgrade to this new version SDK style. Uh are there risks in doing this? Yeah. Yes. So I you know I'm always super candid Anna and so the the risks in doing your SQL project conversion the first one is that if you're developing in Visual Studio the Visual Studio support for SDK style SQL projects is a separate component. So, as your team is migrating to the new one, some people are going to have to update their Visual Studio install. It's not just a risk. It's it's something just to be aware of as you're upgrading your environment. The the risk for the overall project conversion is just that you do have to modify your project file and something could go wrong. You could u mess up a setting or drop an object. But we've done some work around SQL projects both to make them a little bit easier to make sure your files are included as well as adding uh tooling on the end of it to make sure that the settings all match. So when you build a SQL project, the database settings get incorporated in it. Whether it's query store settings or compatibility mode, all of these things are in that DACP pack file. So for the the build process when those get built into the Dakp pack, we can compare your original SQL project DACP pack, what was that project doing in the first place to after the conversion to make sure I got it right and kind of mitigate the risk as much as you can. Amazing. Awesome. It's almost like using SQL Projects tech to do upgrades. I love it. Um, okay. So I think that's where maybe DACOG verify comes in. Um if Okay, great. Awesome. So tell it tell us what it is, how does it work and and what does it look like? Yep. So Dacpack verify is another net tool. So the SQL package, the CLI has been available as a net tool for a year or two now, which makes it a lot easier to install kind of on any platform and then just kind of invoke it or use it from the command line without any additional steps. So Microsoft.dacpackverify is also available in that same way. I've installed it on my machine here and I've also got the SQL projects docs open to this handy how-to guide in the SQL project section that walks me through doing the SQL project conversion. I have this open because there's a couple steps. First one, creating a backup. So, I create a backup of my SQL project file. I've got my AdventureWorks.sql project.back back up. Then the second step is to build the deck pack to get that original. And I've done that as well and set it aside in a deck packs to compare folder just so that I have that for later. This is the original based on the original SQL projects and I did that out of Visual Studio. Now we get into the uh I'll call them gently scary steps where I'm going to be modifying the SQL project file. I'm going to add one line and remove a bunch of others. And if we look at the original SQL project file, we can see a little bit of why why it can feel so easy to go overboard because there's a lot of extra fi lines here that can get removed. One of the huge differences between the original SQL projects and SDK style SQL projects is all of the files, all of the SQL files are included by default. So I don't need all these lines. All these lines are doing are begging for merge conflicts. I don't need them. So I can delete those out. And when I do, let me drag this so we can do a sideby-side comparison. Never mind. We've got our much much shorter on the right shorter SDK style SQL project file because I've removed a bunch of the includes because these files are already included. There's some imports that are already handled by my SDK and then some defaults from these build conditions. All of these steps are in this how-to article. I'm not doing any magic sauce by kind of coming with this prepared. So, I've removed the properties folder. I've done some of the optional steps because for effect we have this much shorter SDK style SQL project file. Yeah. Wow. I'm going to run a build on my new SDK style project. So this is just net build and it's put the DAC pack in bin debug adventure works original. And when I run dakpack verify, we made this tool as straightforward as possible in that you don't have to memorize too much fancy syntax. All I need is the paths to the two DAC packs that I want to compare. Let me collapse down some of these folders. So the original one is in bin debug. So I'll right click on it. Copy path. Lovely VS Code trick right there. I can paste this one in. And then for the second one, it's under DP packs to compare. So I'm grabbing the original one now and pasting that in as well. And what DACA pack verify is going to do is compare these two DAC packs and make sure that I didn't make any mistakes. And it turns out that the DAC packs are different. I've made some kind of mistake in how I modified my SQL project file and it's letting me know that the following SQL cmd variables are different API endpoint default year and environment. If I look at my new SDK/SSQL project, I don't have any SQL CMD variables. So, I've definitely messed that up. SQL CMD variables are these cool things in both the original and SDK style SQL projects that allow you to set a variable that doesn't get set or resolved until the deployment happens. It looks in like in my uh delete happy state I removed these lines and didn't bring them back in. So I'm going to go ahead and transfer them over into my SDK style SQL project. when you have uh the the SQL project upgrade, it's not a mass change in the syntax or the properties on the the project. It's just there's a lot more defaults and simplifications, but you can still be including a lot of these items. So, with my SDK style SQL project now having these three SQL cmd variables, really quick, we're going to go back and run net build again. This is going to update my new DAC pack. And then really quick, I'm going to run DACAP pack verify again to make sure that I do indeed have the DAC packs the same and they are now equal. Wow, I love it. It's very easy, very straightforward, and it helps uh folks who are human because sometimes you make mistakes. And again, we were talking about the risk. you really want to make sure you minimize your risk, especially before you make this your your go-to thing. Um, so awesome. I love this. Any any final like tips or tricks for folks as they get started? Yeah, I I think the one thing that I want to mention is that if you are using SQL projects and you don't have SQL cmd variables and you don't have a bunch of custom settings, you just have a bunch of files, you could consider starting from scratch on a new SDK style SQL project and just copying the files into the folder with it. You don't need to edit the SQL project as much. That said, um do do consider doing the project conversion. just back up your SQL project file before you run the conversion and um you can always go back um you can always go back to the original SQL project as needed. Yeah. No, I love that piece of advice. Cool. Awesome. Okay. Um and this is available for everyone to try out. Yes. Yes, it is. It's available for everyone uh right on the Microsoft.dacpackverify.org page. Um, so whether you're on Windows, Linux, or Mac OS, you can go ahead and grab this.NET tool and start working on converting SQL projects. Awesome. Cool. Thanks so much, Drew. I learned a lot as always. Um, I'm sure our viewers did as well. Uh, to our viewers, we'll put a link in the description for you to learn more and go get this. Uh, if you like this episode, go ahead, give it a like. Let us know in the comments what you think of SDK Style and the new DAP pack verify. And we hope to see you next time on Data Exposed. [Music]