SQL Projects in SSMS and Visual Studio

All right, I think we're rolling right into the 1:30 time slot. Thanks, folks, for coming in after lunch. I know you probably are like, "Hmm, lunch coma, coffee. Lunch coma, coffee." Uh it's it's a struggle. I totally get it. Um do not worry, I won't take it personally. Um as the slide uh lets you know, I'm a product manager at Microsoft. So, I'm used to talking to folks across different audiences to let them know kind of what we're working on, but also to get your feedback. So, as we wrap up this session afterwards, if you have follow-up questions or you just have thoughts on the topic that we talk about today that you really want to make sure that I know um as it influences our road map and what we work on, I'm more than happy to hear it. Um you can call my babies ugly. That's okay. Um I my true goal is to make you as effective as possible as a developer. So, things that stand in that way, not so great, and I'm I'm happy to hear about it. I've been at Microsoft for about 6 years, and this is one of the areas that I focus on. SQL projects as a whole and making it easier to have your database evolve with your application. This is a exciting and challenging space to be in given that little AI thing you might have heard about this week or before this week, obviously, but you've been hearing more and more about this week and how it changes development and the pace of development. And your database is sitting over there being like, "Change me. I dare you." Like, it gets a little bit more sticky. And so, with SQL projects and what we talk about today, we're going to talk about different junctures where this tooling can provide help. Some of the things that we're going to step through are manual, but then the other thing that we'll talk about is how do we layer this in with how we're currently approaching using AI to make development better, faster, safer, more effective, all of those different operations to it. I do want to give a shout-out to the great folks at VS Live. These session surveys really do influence what they select and what they bring to these conferences. So, to make sure that you're return next year or 2 years or whenever you get to come back has more meaningful content for you, make sure you're filling out these surveys. That said, they are also super fantastic in that app will be the decks, including the one for this session. I believe it just got uploaded. So, if you looked for it earlier this week, that is on me. I was late. If you look for it now, it should be there. Um if not, we'll sort it out. But, this very long abstract, we're going to be talking about Visual Studio and SSMS, some differences, some similarities, where they're headed. I am going to touch on both GitHub and Azure DevOps, but I'm also going to look at it from the CLI level that they use, so that if you're like, "Well, I'm not actually using either of those. I guess I'll just head out." There's still fundamentals in this that you'll be able to take to really any automation platform. Um and now now our database is going to get kind of these kind of crazy software development life cycle things that the rest of your application stack does, like code analysis and unit tests, kind of that peace of mind that you have that lets you sleep at night. All right. The other thing that I want to do address from the beginning is if you're using any of the Microsoft SQL family, so if you've already gotten into like SQL database and fabric, great. If you, which I say already because it GA'd relatively recently. Um if you're using one of the Azure SQL options or if you're using SQL Server and really any of the ones in support, not just 2025, SQL projects will work for you. You You can integrate all of those with and actually SQL projects will help make sure that you don't accidentally pick up some uh capabilities that are outside of the specific SQL that you use. And I already mentioned that there's a CLI available for SQL projects, but overall I wanted to give you a big picture before we zoom in and start talking about specifics. Is there's overall an ecosystem of interaction capabilities or tools that you can use to leverage what we talk about today. And this goes across like gooey thick tools like SSMS, VS Code, Visual Studio, and Rider. Then you can also use some of the pre-built integrations into GitHub and Azure DevOps pipelines. There's a SQL package CLI, and for those of you that like to get really in-depth with the stuff that you use, the capabilities that are built into everything on the left of this slide are all based on public .NET libraries. So you can grab the DAC FX library or the script DOM the the parser library and use those and build your own custom components. Um we don't have the time today to nerd out about how cool that is, but um if you hit me up on LinkedIn and ever want to like chat about that, I love it. Um there's some very very interesting stuff you can do with those libraries. Great. So I just told you kind of like all the ways that you can touch and feel and work with us. We'll look at specific examples, demo through it um live demo labs, please bear with me. But before we get there, I do need to give you the overview of what what are we doing with SQL projects? Where are we taking them? What can they do for us? In basic .NET development at a really really really high level, you have some kind of C# project. You build it and you get an executable or a library, some kind of build artifact that then is a a bit more portable as long as you're, you know, putting it on the same architecture, you can continue to use it. When it's time to do that, you you're deploying it by copying it over to an app host somewhere. It could be running it locally, you could be shipping it to a to a server or into Azure. This is kind of this repeated cycle. You change your project, you build it, you deploy it. A couple weeks later, somebody's like, "Will you add?" And you're like, "Sure, I'll make that change." You do that. There's a bug that somebody else made. There's a bug. You fix it, you build it, you ship it. Repeat, repeat, repeat. What about our database? You could be artisanally handcrafting scripts to make that change associated with that feature. You could be using EF Core, you could have other database change mechanisms in place. But some way they need to fit into this build and deploy life cycle. Because if you're building your C# project, you know that the references between the classes all match up. You can't have a reference to a class that doesn't exist and hasn't been defined. The the These are the things that you get as a guarantee as a part of a build. For our database development, SQL projects introduce that kind of peace of mind in terms of how do I move forward with my database. This is not the only way to use SQL projects. There's a lot of flexibility and scenarios that we'll talk about. But overall, the process is that we're able to build the SQL project. It is in fact also just a .NET build. But the artifact that we're getting out of it is a compiled database model. It's not It's not a It's not It's not like an actual database file, and it's actually not just a SQL script. It's not just create table, create table, create table, create stored procedure. You can only run that once. That's helpful in certain scenarios, but not always the thing you need. This compiled database model secret It's a zip file with a different file extension. Um which is nice because you can crack it open and look at it, but this DACPAC has inside of it a bunch of XML and it's that serialized relationship of all the objects that are in the SQL project. Where this is powerful is the rest of that tooling that we just looked at in terms of that ecosystem can understand a DACPAC and when you say here is my DACPAC, here's my database model, and here's a database make them make sense. Tell me, how do I make this database match this DACPAC? You could sit there all day and be like, "Hmm, there's 5,000 tables and three of them have changes. I'll find it eventually." Um but what the tooling will do for you is make that comparison and then figure out what the change is that needs to happen between them to give you that deployment script. Also, more specifics that we'll get to, but at this point you've got the concept of I have .NET projects or TypeScript projects or some other language, which is also totally permitted, um where you are building and deploying and now your database is able to follow that same kind of process. Which is kind of cool. Because now you also get the rest of the concepts for development applied to your database. You can actually do database changes in parallel. Which isn't something we thought about maybe quite as much until now we have agents where you have like three tabs open and one's working on one feature and the other's working on another feature, the other one's working on some refactor because you had that great idea at 6:00 a.m. this morning. You have all these parallel changes going on. If they each have their own database and you're like, "Oh, it's not going to be a huge big deal to combine all these changes down the road. That's that kind of branching and merging that you have capable as part of SQL projects. I mentioned that you're building to that compiled database model. And it's going to check to make sure that your code is correct. I'm not just talking about like the syntax is correct. I'm talking about the relationships are correct. You're referencing a foreign key to a table that actually exists. The stored procedure isn't referencing something that doesn't exist. And now because your database is code, you can commit it. You have that timeline. Somebody just added a table with absolutely no primary keys. Most of you are like, oh god. Oh god. Um it's not generally a good idea, but now you may be able to catch that a little bit easier. And then finally, like I mentioned, when you have that database model, that DAC pack, and you go to deploy, you're actually able to find out what is the deployment plan and approve it before it just eats itself off into production. All right. Very, very, very fair question. I have EF Core migrations already set up. I know most of you came into this talk because you're like, I love databases. I will write SQL code all day long. So, you're on this count. But, for if you're using EF Core migrations, there's still a lot of value here. I'll start with just that whole production thing that I talked about. Like, everyone has a development environment. It's just most of us are lucky that it's not also production. Um when you're applying your migrations in production, you can use the EF database update capability. It can be safe for certain scenarios, but depending on what is going on in that database, you might need more controls. You can also kind of hand craft those SQL scripts based on your understanding of the changes. But SQL projects can come into this situation and even benefit you earlier in the development life cycle. Cuz EF Core is fantastic for rapidly iterating and generating these database models very quickly. But at the same time, you might not have as much of an understanding as you would if you had code for your database. One of the classic EF Core examples is a university app with students and instructors and things like that. And when you define your student database model, um some of you may already start to see the kind of anti-patterns developing here. But the the the spoiler is that a lot of these are going to end up being varchar max columns. Cuz we didn't set a length on them. But if I start iterating on this prototype and I don't catch that till it's much further down the road, that can be harder to to catch on to. And so that's why it's always good not just to ask like, is this database valid? Like my migrations will actually run on a SQL instance. So that might be something you're already doing with EF Core. You have a side a sidecar container in a CI pipeline. And then that's running the migrations. So you you you have validated that. That's awesome. But now where it gets us is if we already have a database stood up in a pipeline, let's start thinking about how can I interact with that database as code to make sure that it is good code? Are they following common sense, best practices? So using that container, we're going to start automating code analysis on top of it. We do need SQL package the CLI to do this. It's a .NET tool. So you probably already have .NET in your environment. You install the SQL package CLI. If you do not have the .NET SDK in a build environment for whatever reason, there is a self-contained executable. But just try to grab the .NET tool. I'm moving quickly cuz I want to just demo this and and show you kind of how cool it is. But the fundamental operations that we need to understand for SQL package before we dive into that demo is that it can extract and publish with a database. So SQL package is a tool that can understand a SQL project, so that all of those database objects, a DAC pack database model, or a database itself. And it can transfer between them. So if I have a database, I can extract it out to a SQL project or to a DAC pack. In terms of being able to understand, read, and look at a database model, we want that SQL project. And then conversely, it can publish. It can figure out what that change is and apply it to a database. We're just going to use extract right now. We trust that EF Core, that database update, will handle the the deployments for us. We don't need to even change that. We just want to use SQL projects to help us make sure we're writing good database code. So I have I have AdventureWorks, which is a classic Microsoft sample in that it is a shining example of every best practice in the world, so I will never find any faults in it. And I'm just going to grab my connection string real quick. Uh I'm going to use the command line here because the the fundamental reminder is not that you have to type everything out manually, but that you can automate this. Like every PR that comes in can run your migrations to a container, SQL Server's high-quality container service in that pipeline, get you that database, and then we'll just decompile it really quickly. And look, it even remembers that I ran this earlier. So, I will Yes, great question. Thank you. My action is going to be extract. And I've said I want you to use the target folder AdventureWorks. Basically just saying this is where I want you to put it. Passing in my connection string and saying my extract target is a SQL project. By default, SQL package is going to extract to a DAC pack, to that compiled model. Great for running deployments, not so great for getting good code insights. So, as I run this, it's going to connect to the server, it's going to query the this the system views and find out what all those objects are and reverse engineer them out to files. So, if I go into boo The nice thing about the extraction to these specific objects, the default organization is going to kind of match what you're used to seeing if you will connect to object explorer, where you have schema and then object type. That's the default organization. We're a little bit opinionated about that. So, it's giving me the success message that it extracted to AdventureWorks. Open. And this is just the plain old boring Windows Explorer view. But, these are individual SQL files for all of the objects. What if I build my SQL project? We're looking at kind of that whole development cycle, but instead of just building it to get that build artifact, what I want to know is if I run SQL code analysis If I set that on, we can set this on all the time in the project file, but that project file is kind of very plain vanilla right now, so I'll just set it as a command line override. And it's compiling and checking this this database model, which is fine. We're not really worried about that. We knew the deployment worked. But what about what about the quality of this code? Oh, look at that. We're apparently using at at identity in one of our stored procedures, um and then there's an object name that has special characters in it. Ooh, spicy. Um the the at at identity is an anti-pattern that we would look out for. We could ask Copilot, "Hey, what's so not great about this?" We could ask our database professional, wherever they may sit. Um This is something that's kind of nice because honestly, it means that we could get the wrong identity if there's a lot of traffic on this server. So if we're reusing that ID number, it could be the wrong one, which is actually super bad. Um it's a good thing we caught it. These are the kinds of quick checks, like literally two commands, SQL package extract and then .NET build with code analysis, that now we're starting to get insights on top of our database. While that is interesting, the even like richer part about code analysis and how easy all of that was is that it's extensible. You can write your own custom code analysis rules, which used to make people go, "Oh, that sounds difficult." But frankly, it's not anymore. These are the perfect like low-hanging fruit where AI can help you make sure that your software development life cycle fits whatever your organization needs. How many times have you told your co-worker that they need to stop naming things X? How many times have you been worried about like how the capitalization is or nobody uses foreign keys on anything? Whatever that is, people use max length columns. Like these are the kinds of things that we can introduce as parts of our early checks. You will no longer have to remind them of these things. The CI will just fail. So, we can definitely run code analysis, use the default rules, gets you off the ground and running, and then introduce your custom code analysis rules over time. With the most recent SQL projects advancements, these can be used as package references. So, you know, like copying the DLLs around. If you know what I'm talking about, I'm terribly sorry. If you don't, good news, it's just normal. You publish your custom code analysis rules as a a library to a package feed, and you can consume them and enrich your CI. So, at the end of the day, I've set up my CI to run the migrations on a service container. I run SQL package extract. I add whatever packages for the custom code analysis rules I need, and then I run that build. You can even kind of uh parse up the build to get a nice little output. In the deck that I've shared, anytime I have like a prototype type demo, I want to make it as easy as possible for you to take that home and add it to at least one of your ongoing uh projects. So, I have code links to gists, however you pronounce that, terribly sorry, um of the of of the code for that pipeline example. This particular one is a GitHub flavored example. I'll go back and forth cuz I try to be a equal opportunist. So, I've talked a bit about SQL projects, given some kind of more ephemeral examples, but I want to get really specific about what is in a SQL project, what are some of the capabilities that make it a little bit more flexible than just a party trick. First and foremost, there's actually a lot of stuff that goes into a SQL project. It's almost a little overwhelming if you're thinking, "Oh, well, I just create some tables, maybe a view or a stored procedure if I'm uh hard up for a good time, and then like that's that's it. That's all that goes in my database, right?" We can put a lot of stuff in a SQL project to accommodate different scenarios. But, like we saw in that extract example from AdventureWorks, you are getting all of the database objects into the SQL project. So, either starting from scratch or starting from an existing database, you're going to get the views, the stored procedures, the types. You can even put permissions in there. So, it's not just the tables, it's really any user object. Indexes, foreign keys, like anything. Um on top of that, a lot of the database-level settings can also be included in the database project. So, if you're using change tracking and you need to control that, you might want to be able to change the cleanup time across different environments, that kind of thing. Those can also be set. They're set on the project file. So, they're they're kind of declarative in that way. And then there are the start of what becomes a lot of flexibility in terms of pre- and post-deployment scripts. These scripts can be added to do things like static data management or a little bit of data cleanup or modification as a part of that deployment plan. So, it gives you the opportunity to kind of break the mold outside of the SQL project if you're finding it's not doing just quite enough for your exact environment. When this renders into one of the SQL project's tools, it's going to come up in a solution explorer type view. And then when you build from that environment, you get a DAC pack. Like we all caught onto that earlier, so we're Now, the other thing that we get with SQL projects and with the DAC effects APIs overall is schema comparison. Just like out of the box, if I need visibility frankly, you don't even have to use SQL projects to get value for schema comparison. So, even if you leave here today and you're like that was a lot. No, thank you. Um you can still use schema compare between two databases. And like have great visibility at the object level to what has changed. Again, you will not hurt my feelings. I want these things to be useful for you. So, with schema comparison, you can compare really fundamentally between database branches. Like if we think about it in terms of I have code between two different branches and I need to know the difference, schema compare gives you that same visibility. You can compare databases, SQL projects, and even DAC packs. So, if someone's like, "I made these database suggestions and I've compiled it up and dropped it over to you. Will you please let me know what you think?" Okay. Goodbye. Um you can pull that open in schema compare to see the differences. So, potentially there are new objects that are being added, you'll see those. Updates or modifications to existing objects, it'll tie them out. As well as deletions. So, you get that kind of difference view. It really does look just like um a get difference for the most part. All right. I am going to skip back into live demos just to drive around SSMS for a moment. Show you what's going on in there. Great. This is the main build from like 2 days ago, so hopefully it's still working. One one quick I'll preempt questions for later a little bit. Um, I will talk about some of the differences between the different apps. I'm going to demo live out of this SSMS instance as long as it keeps working. Um, but I have some recorded demos throughout the remainder of the presentation that are also going to switch between SSMS and VS Code, I can show you some stuff in Visual Studio. Um, I mentioned earlier I'm trying to be an equal opportunist. SSMS went into public preview back in March. Um, still in public preview in SSMS. There are a couple major conferences this fall. Because along the way once you have the model comparison and the differences are figured out, this is all going to happen seamlessly behind the scenes without your interruption. You can say, "Do I just want the deployment plan? Do I want the script that it would take to make these match? Or do I just want you to go and make this happen?" So, there are actually three different deployment methods. In SSMS and VS Code, we really quickly saw that there's that publish menu option. I I glossed over the generate script option. And when you're using SQL package, you can also generate a deploy report, which is an XML file, which might tell you a little bit about how long this technology has been around. It's not JSON or YAML. But, what it is great for is for automated interpretation of deployments, if you'd look to get really really fancy. But, fundamentally, directly publishing means that the script that's been generated will be applied. The you could get the deployment script. You could run it yourself. Or you could use it to review. But that brings me to talking a little bit about best practices because honestly, there being these options doesn't do you any good if you don't have a framework to think about like, how do I deal with them? So yeah, the first one is I work on this. I understand how reliable it is and it is really, really robust, but I still encourage you to not just publish yellow in prod. Automate on that deploy report, generate the script output and read it. Pass it to an AI agent. If you're like, I'm not sure I'm comfortable and I can't get somebody with the database expertise to look at it, leverage the available tools to kind of build these gates in. That can give you an idea how of how to evaluate is this deployment going to complete in milliseconds or could it take a couple minutes? If if you have like flexibility to bring your app down for 5 minutes, great. Maybe not so much of a concern. If you're on 911 call center, different story, right? So there's there's going to be different environments that you have to understand and that's okay. I've talked a lot about the SQL package CLI, but I also want to call out that in GitHub and in Azure DevOps, there are pre-packaged tasks that wrap around SQL package and a couple other steps to make getting the the script, the deploy report, or going ahead and publishing a little bit easier to set up. There's nothing wrong with these pre-packaged tasks. I focus on SQL package because it gives you more robust options, but absolutely those two tasks have a huge advantage that I'll get to after we talk about gated pipelines with approvals. Cuz like I mentioned, we're not going to YOLO into production. However, we also want to keep things structured. So, in GitHub and in Azure DevOps, there are things basically like approvals. Uh in GitHub, they're called environments, where you can say this section of the pipeline requires this to be checked off. So, we've kind of set up a development process potentially, where changes are proposed, pull request, a CI pipeline validates the changes. Maybe using SQL code analysis, what a great idea. Uh and then somebody says, "Okay, this isn't so bad. We'll merge it into main." And now we're starting to think, "How do we bring these changes into our staging environments, our early environments, or sets up a new shared development environment based on them?" So, we'll use SQL package or some kind of uh development pipeline task to get that deployment plan. And we'll stop. And we'll either review that deployment script or have something parse that deploy report. You can publish these as artifacts to download when you're ready for them. Pop it open, check out, "Hey, I'm altering the student table. I'm going to make the first name column only 50 characters. Great. No longer varchar max. Fantastic." Now we can approve that deployment. And now publish can run. It'll run what it generated, not that exact script. It'll re- re-pull it up, which is great for safety if you have other people that have the habit of touching the database. Oh, we follow security best practices. Okay. So, the Speaking of security best practices, one of the advantages of those pre-packaged tasks for GitHub and Azure DevOps is they'll help us navigate network security. Now, hosted automation environments are public. They have public network access. So, your database is probably more restricted than that. It could be on a prem, completely firewalled. It could be in an a VNet, in a in a cloud environment. So, you may not have immediate direct connectivity from place to place. So, tightest security, you may potentially even already have this set up for any app deployment reason, is self-hosted pipeline runners. So, like when you set up GitHub Actions and you're like, "Just use Ubuntu latest." Fire something up in the shared infrastructure. But, you can specify use one of my self-hosted pools after you've set those up. The reason that this works is the runners that you self-host actually pull out to the pipelines that they're assigned to for changes. It doesn't have to be anything open coming in. However, a close second is using a shared environment and allowing them to set up just-in-time firewall rules reaching into an Azure SQL database. So, this is a specific option to if you're using Azure SQL database, which is going to be protected, you're not going to check the box for allow all Azure as enticing as that option is. You will leave that unchecked, and so you require a just-in-time firewall rule. So, what this means is that just for a temporary time period, you can have a managed identity on that pipeline get access to that database, and then immediately remove it back out. You can manually write this. Nothing wrong with that. Conveniently, there's a sample on the next slide that also has a link in the deck if you want to grab that to spin this up yourself, or you can use one of the pre-built tasks. Um you can do a very similar thing with Azure SQL Managed Instance, but it's definitely more complex. So, creating that firewall rule using with Azure PowerShell gives you the ability to add it. And because we name it specifically in that pipeline, you can immediately remove it. So, this is a 2-minute long open firewall kind of thing. But, like I mentioned, best best case scenario is that fully self-hosted runner into your isolated environment. And it's only going to be calling out to the the pipelines that it's assigned to. However, it does mean that you now also own a new free puppy. Um this this VM or these container instances or container apps you now also have to care and feed for. But, it gives you that trade-off of now you have automated deployments, you have the ability to dynamically get those deployment scripts, all because they have access to your database. Why Why are we doing all of this like checking code earlier, making deployments easier? Because we want to make sure that the software we build works correctly. One of the ways that we make sure our software works correctly is we put unit tests on them. And like we looked at, you know, creating a quick branching environment for a junk to development, obviously the hot topic. But, ultimately, the joke I made earlier about how everybody has dev environments, but hopefully they're not in prod, is very true. Like it doesn't hurt to have more early environments that you can test on. It's acceptable to mock out some of your database calls, but it's ideal to also have some true database validating unit tests. There's a couple of ways to approach this. Um one of which, for those of us that really like SQL, writing SQL code, is to use a framework like t-sql-t that doesn't require setting up a database and it fully integrating into that ecosystem. To- totally great. Like you would absolutely also be able to use SQL projects to set that up. The flip side to that is maybe you want to expand on the unit test suite you're already writing for your your .NET apps. So you could be using xUnit to expand on that. If I didn't pronounce it correctly, my bad. I read a lot. Um the the prototype that I'm going to share in a recorded demo here involves using the test containers project. Cuz a lot of times as we're running through unit tests, we're spinning up quick environments. And what it's able to do is manage the creation of the SQL container, use that SQL project to quickly deploy it out, run those tests, and then destroy the whole thing. And it's it's a very relatively lightweight to have validated at least some functionality against a database. And so they uh there's a couple there's two files related to that linked linked in the deck. And I'll show them both here in a moment. So the the the container that's getting spun up is the SQL Server 2020 two or 2025 container. You could also use the Azure SQL um development container that we're releasing literally right now. It's currently in a private preview. In this instance, I actually have data API builder holding hosting up REST APIs for me. So I have a number of different unit tests that are validating that this web API is working correctly. However, the crux of this is that I have a fixture using test containers that is initializing that container for me and it can run .NET build. Make sure that the SQL code it's being added candid can run and then it's running SQL package publish. This is not groundbreaking stuff for you. You've been in this talk for like 45 something minutes. You know about publish already. I haven't I have honestly I haven't introduced anything new at this point other than just we're plugging a few things together. You know how to use containers. You know how to build and publish SQL projects and we can integrate that with app unit test frameworks you already have access to. These These containers for the .NET app and for the SQL instance are being immediately spun up for us and honestly the tests all passed checking all of those endpoints and it's going to tear down those containers on us in literally the span of 43 seconds. Usually I speed up my demos but this one was one that I felt like I'd be cheating if I sped it up because it was able to just do all that so quickly. So when you have your databases code, you have your application already as code unit tests are another one of those things where AI can definitely help you write more comprehensive unit tests and there's a trade-off to I have now written 500 unit tests. I'm very confident that my feature is fully covered and my unit tests take 5 hours. So there's a balance there but having more comprehensive coverage that reaches into our database because we have integrated with leveraging a database definition to deploy that environment. That's hard to beat. There are the test The test container framework is a nice example of like if you don't already have uh development environments being stood up but if you already have development environments being stood up now you can build right on top of that as well. Okay. So we have 20 minutes to start talking about some good and tough questions and like I mentioned before, I'm super happy to chat with folks after this as well. The first great question is what's the deal with Visual Studio SQL projects? The title of the talk is SSMS and Visual Studio like First and foremost, those two products like SSMS and Visual Studio are shipped side by side now. You might have noticed that SSMS is starting to look a little bit more like Visual Studio. They're getting to get to be in sync on a number of things, which is great. Because some of the things that we talked about today are specific to the newer style of SQL projects. The original SQL projects are the ones that have been in Visual Studio for a little while. A little over a decade. Thank you. This is This has all of the fundamentals we talked about with like the the build. It's .NET Framework only. The DAC Pack file is created and it's the exact same DAC Pack file that you get for the SDK style SQL projects that we saw in SSMS. You can still do database references that we'll get to in a moment and there's still code analysis. You don't have package references for code analysis in the original SQL projects. Um and the SQL project file itself is a lot longer so as you're rapidly iterating, you're way more at risk for merge conflicts on that project file. So there is there is definitely a reason to be shifting towards the the SDK style SQL projects. The challenge is that we have a little bit of a landscape right now that we're working on. SSMS that I mentioned just came out into public preview earlier this year is those newer style projects only. So everything that we saw is being built with full functionality focused on the new style of projects because that is where we are headed. In SSMS, the one really important thing to remember is that if you have SQL CLR capabilities built in, I'm sure there's at least one of you in the room, that you will need to use SSMS and not VS code because you do have to do a .NET Framework build. Otherwise, you don't care. The .NET Standard, the .NET 10 build for SQL projects will do everything else. And the functionality between SSMS and VS code is really close, which should tell you a lot about where we're headed with Visual Studio, and I'll get there in just a second. The difference for VS code and the cool things that it has with SQL projects is that it's going to give you ongoing IntelliSense. So, if you're somebody that's still really into typing out code or making menu modifications, it might be a little bit more interactive for you. And then the refactoring capability was recently, last couple of weeks, added into VS code. This refactoring capability gives you the ability to use SQL projects throughout a multi-year life cycle of a database. You probably have a database in your environment that is like a child to you at this point, that it's been around for so long. And every so often you're like, "Maybe we should revamp some of this stuff, move some of these tables around, rename something or two." That's where refactoring comes in because it's able to do those operations without moving the data, just renames those objects. VS code covers both the original SQL projects and the new ones. The goal is that there is a bridge here so that everything can come over to the new SDK style SQL projects, and that includes future work in Visual Studio. Right now, SQL projects in Visual Studio, fantastic. There's a ton of rich functionality. Some of it gets used a ton, some of it barely gets used at all, but it definitely helps out developers. There's additional functionality on top of VS code, including data comparison. So, if you have really meaningful reference data that you need to keep an eye on the differences between, the data comparison UI might be helpful for you. If you are still manually creating tables, the table designer in Visual Studio SQL projects would definitely be helpful. It's also .NET Framework only for these SQL projects. I'm calling this out here because depending on how your CI/CD environments are set up, you will always need a Windows agent to build the SQL project that's Visual Studio. Maybe you're like, "Okay, great." But, if that is a problem, you need to know that ahead of time. So, the intention here for Visual Studio is that Visual Studio will get those SDK style SQL projects. I don't have a date. I know. It sucks. I'll be honest, not my plan A. But, we're still working on SQL projects. They still have a pretty cool life ahead of them. We have new features coming out for SQL projects as well this fall, including database versioning and a pre-plan script being added. Um you can definitely join on our GitHub for DAC effects to dive into the SDK for SQL projects as well. Go ahead with the question. Sweet. Thank you. All right. Other kind of tricky situations like your database is probably complicated. We've talked about SQL projects for an hour, almost exactly. That is not a long time to deal with SQL projects. Those of you that know SQL projects know it in your bones because the more you get to know it, you find other things under the covers. And so, if your database is complicated, there's a few extra things that you might want to know about, but the number one thing that usually makes people go, "Oh, that's how we would do that." is database references. SQL Server users that have cross database queries, this is probably where you're going to end up. If you have circular references, it gets really messy. There are ways to do stubs and synonyms. It's going to be an investment. Make sure that it is worth your while based on the benefits we talked about today. Or if not, again, not going to hurt my feelings, but circular references can definitely be tricky. If it's just regular, "Hey, I need to call out to another database for a couple of views." Totally fine. Database references give you the ability to reference either another project package unit for the same database, like I want to add another 100 objects, or for another database on the same server. If you are so lucky as to have to deal with linked servers, you can even do different servers and different databases through these database references. The most basic kind of these references is by referencing another project. It'll also get built whenever you build that project, which means that your build time will increase. It's kind of like .NET project references. Um one less costly way, especially if you have a core of a database that never changes and another part that's really under active development, is to do a DAC pack reference, where that part's already been compiled. That would reduce your build time a bit. With the newer project style, those DAC packs can be packaged into package references and shipped onto a package feed. Um you would do that by doing .NET pack. The the thing that you'll see with a little bit of shift between these project types as well is the system database references. So, let's say you have sp_whoisactive or other kinds of diagnostic um stored procedures within your database that become part of your project, you would need that system package reference or that system database reference in that project. So, if you get those warnings that sys.whatever can't be found, you need to add that that system database reference. All right. I have addressed it a little bit in the discussion today, but overall from a theme, like we're at a conference in the year 2026. There's going to be discussion of AI. AI has either completely taken over your job or you're still working through how does it influence it? That's totally fine. No matter where you are in between or on either ends, like changes to how you do your development process are a good thing. I would encourage you, like don't just like shift the toil along. As you're working with AI and you're like, oh, it can do this better for me. It can investigate a bug better for me. It can dig through and find where all of this is referenced. That's great. Use the SQL projects, like we showed in that one example, to understand what an agent is doing. Your example will probably be way better than mine because I made all of that up and it was really simplistic. But then you can also, if you get more advanced with using AI agents, start combining together multiple change sets. If you have two terminal tabs open, they're both going, they can work in their own environments and you don't have to worry about merging them in together later. One thing that I have heard as a pretty common theme as organizations start to adopt AI, is they get absolutely crushed by the number of PRs. Yay, more code to read. Um actually, you want to be able to evaluate code faster. That code analysis functionality can help you there. Get more confidence in the code that's being read. Um you don't want to just sign off on everything without question. I touched really briefly on how SQL projects can help people that are more focused on databases be a little bit more involved in the development process. Um I don't want to I don't want to gloss over that too much. Like that is a really important part of what database DevOps does enable for us. Because everyone has limits to their own knowledge. We're all doing our best. But everyone has limits. And so there are certain things that I go to a colleague for. There are certain things you go to a colleague for. And if databases and dealing with that is one of those things, or maybe you are that expert, people come to you. You can make sure that more people can be involved. Prior to SQL projects landing in SSMS, one of the things we were hearing is, "Hey, we're using SQL projects, but we can't get DBA so-and-so or this other development team to also use it because they use VS Code, they use SSMS, whatever. And so we're trying to and working towards bringing all of the SQL projects tools onto the same page so that you can have multiple teams involved in your database development process. All right. So wrapping up so we can take questions and chat and chill. Just recapping. SQL projects, whatever Microsoft SQL you're using, you're going to get that kind of consistent build, publish, code analysis, CI pipelines. You can do deployment across pretty much any environment. Frankly, you could be deploying to different versions of SQL Server and checking them along the way. This means that you can do these things locally and understand what's going to happen in CI/CD. But the validation that happens in CI also means that once you get to do those deployments to whatever platform, you got a lot more confidence in them. I I have a request from you. The databases group at Microsoft does want to make sure that we are factoring in what benefits you as developers as much as possible in our roadmap. So, if you are interested in sharing your thoughts, finding out what's in the pipe, influencing what's get considered in the pipe, um we would love to have you join our developer voice program. Um if you scan that QR code, it should be a nice quick little form. Uh we do host uh like ask me anything type environments, we host webinars where we're working on thing, what do you think? Um today you heard about SQL formatting, I mentioned it really briefly. That's a great example of something that Yul Lai and I met with uh developer voice for about an hour a couple weeks ago to dive into the whole roadmap for that and all the secrets that you would know if you were in developer voice. There's way more cooler things than SQL formatter just in developer voice, but that is the example that is top of mind for me. So, please do join developer voice if you're interested in it. The resources that I want to drop to you in addition to um the links throughout the deck. Again, this deck has already been uploaded, but they are pretty simple. The first one, that's just a documentation link. The real you could just find it on MS Learn, but there is a centralized documentation on SQL projects and it is tabbed like within the docs based on SSMS, VS Code, and Visual Studio because again, there are subtle differences, some places it's much bigger and as moved to bring them closer together, you'll see that converge quite a bit more. The second one is our SQL projects roadmap. Uh we've been doing this for a couple of quarters where we're looking at what's coming up next, so that is out there in public. One of the things about that roadmap page is it also has links to file feedback on all of the different things. So, for those of you that are really eager to see SQL projects in Visual Studio or you use it in VS Code and you're like, well, that was pain riddled, you'll know where to tell me about it. Or if you are liking what you're seeing and you have a new feature idea for SQL projects, that would be also a great place to put it. The last link is for those of you that don't get enough learning this week and you want to go back home and really dive into what you could do with SQL projects and database DevOps. It's a four-part workshop using GitHub to set up different pipelines for deploying a database. There's one for reverse engineering out of out of a live database. There's the gated example. There's a couple other things that we didn't even talk about today. So, there's nice pipeline examples there as well. All right. I've got a couple recent blog posts that might be of interest just based on what you may or may not be into. But, we have about 5 minutes to take questions. So, I am here to chat. If anybody wants to raise their hand and be brave, go for it. Go ahead. >> So, you have been working on uh functionality for SSMS. >> So, the good question was we're working on functionality across each of these. What's What's our goal for feature parity across them? I would say the goal is close, but not perfect. And I It's probably a very poor way to put it, but the goal is close, but right for the people that are using each tool. As one of the reasons why I'm here saying, "Sorry, Visual Studio users." is because it's really important for me to hear from folks in Visual Studio, what's the most important thing that I can get you the soonest, and where do you What things do you need? Um for SSMS, we're focusing both on active development as well as DBAs who are in SSMS. For VS Code, it is much more developer focused. The things like unit tests are coming in faster to VS Code than they may to other environments. Does that mostly answer your question? Okay. Good. Great question. So, in SSMS 22, I showed the Solution Explorer and then in a brief demo, I also uh opened up the source control panel as well for Git. The The question then went on to like, is this GitHub or is this Azure DevOps? This can be any remote provider. Um the Azure DevOps, GitHub, I'm pretty sure TFS has been removed. Um but at the end of the day, a Git remote is a Git remote um and you can set them through the command line as well. Yeah, absolutely. Um I will I will tell you that I see SQL projects consumption across GitHub, Azure DevOps, GitLab, a lot a lot of different environments. Absolutely, yeah. Good question. Awesome. Good. What is the best advantage of using SSMS? That's Wow, that's a good question. I would say the best advantage for using SSMS, if you're use working in a team where you're going to have people that are already using SSMS. They're like, I'm a hardcore DBA, I'm performance tuning, things like that. You're going to be speaking the same language as them immediately. The next advantage for using SSMS is that it has a very very deep database toolkit. Especially if you're using um SQL Server instead of like Azure SQL Database, where you do a lot more configuration potentially than uh a managed platform, then having that rich tool set and SSMS all in the same place is is good. The last thing that I'll call out, like in terms of SQL projects specifically, is if you're working in Visual Studio, you only get .NET Framework build on those projects. So, if you go to use CI/CD pipelines on top of those projects, they have to be Windows runners. There's a minor performance difference. In the interest of transparency, there's a minor performance difference in my personal experience between using Linux-based runners and Windows runners. Depending on whether that impacts your environment, honestly, it is the is a very big it depends. Yeah. Good question. It's a great question today. Go. >> Will these I mean features be split >> So, fun part about SQL projects in Visual Studio, amazing trivia question, you can use the SQL projects functionality in Visual Studio under the community license in a team and company of any size. And that is not I do not have plans to change that. Um I can't make future licensing promises, but SQL projects are SQL projects, so when the new SQL projects come into Visual Studio, they would fall under that same thing. Um so, yeah. Obvious caveat, I can't make like future promises, but the the community license thing is always important to know. >> Frank, have I heard of that or uh But you go to something different you want to come like automated deployments. >> Yes. >> I actually like it better after the fact, babe, which like brought one of my favorite ones. >> So, the nice thing about deploy reports is that it does The deploy report, when you select the deploy report output option, it stops before it does anything else. Yep. Sorry, I I wasn't clear on that. So, when when you select deploy report or script or publish options, it's it That is the end of that operation. So, deploy report, that was it. Script, it figures out what the deployment plan is, figures out what the script is, doesn't run it, and just gives it back to you. Um and then in this workshop, boop. 3.3 is taking a deploy report, PowerShell, parsing it up, figuring out what's going on, looking for warnings, things like that. It is available 100% public um at the URL akams sequel devops workshop. Yep. Which is linked to the deck. Totally. Cool. I will be vacating the podium for the next presenter. Thank you all so much. I hope you have a great rest of your day.