SQL Projects for Database Development by Drew Skwiers-Koballa || SQL Server Virtual Conference

0:00
Thank you. This is going to be a blast
0:06
I am Drew Squires-Kabala, Program Manager at Microsoft, and those two links there are the documentation for SQL projects
0:17
as well as a code repository that has examples kind of across this whole presentation
0:26
There's a lot of content when you talk about development with Microsoft SQL, whether it's SQL Server or Azure SQL
0:36
So full disclosure, this last item, we don't have time to cover for it
0:42
But if you are a developer and you're intrigued by developing with SQL Server containers, check out that repository because there's some really cool stuff you can do
0:56
We're going to focus our time on SQL projects and how that works within Azure and H2U
1:03
Before I can dive into SQL projects, though, I need to kind of talk for a moment about why do SQL projects matter? Why can't you just write SQL code and call it a day
1:17
And that comes down to the artifacts that we need, the master files that are required for deploying database changes
1:30
As a developer, as many of you are, you've got a kind of a familiar development pathway
1:38
You write the code, whether it's in VS Code or Visual Studio or a completely different IDE
1:46
and then that code is compiled, it's validated, you're likely writing tests, there are code reviews
1:54
and then finally, that code is deployed. But this process repeats for different features and bug fixes
2:02
They do happen. But it's fairly universal. Things get interesting when you talk about databases
2:12
The complication with databases is that instead of just being code, instead of just being create table statements
2:24
create stored procedure, you have data. That database that's a part of your application
2:32
has a state associated with it. And so data tier application artifacts
2:40
the two primary ones, a DACPAC and a backpack, help us kind of keep that under control
2:49
The DACPAC is just the schema, just those objects. So if you have a really large database with a bunch of data in it
2:59
the DACPAC itself is going to be quite a bit smaller because it's just the schema
3:04
It could be thousands of tables still, but the data is not included
3:11
The DACPAC, however, is a snapshot of the entire database, including the data
3:18
So let's tie this back into that development process, that pathway. Declarative database development takes those data tier application artifacts and integrates them by having us develop for databases by focusing on what do we want the database to look like
3:43
What's that end state? So you've already created a table. You do not have to worry about writing an alter table statement
3:54
It's just you write the create table statement for the table that you want
4:00
This is also called state-based development because it's just a final state
4:05
The tooling that you use has to manage the change or the migration during deployment
4:12
So you develop with T-SQL or with a table designer that spits out that T-SQL for you
4:21
Then the tooling builds it into a DACPAC file. So not unlike you would build and compile a project in .NET, that DACPAC has built
4:36
And then finally, the tools that you use then take that DACPAC and update the database's state to match that schema
4:49
And the DACPAC file is what we need. At a really fundamental level, there's a command line tool that works with these data tier artifacts, and that's SQL package
5:06
It's cross-platform and available whether or not you're on Windows or Linux or macOS
5:14
So this is a really popular tool to use in DevOps pipelines
5:19
because it allows us to work with data tier application files. And so I was talking about the publish operation
5:27
It's that incremental change that updates an existing database or creates a new one with the schema from the DAC pack
5:36
that you've created in your development. And that could be to a SQL server
5:41
whether it's on-prem or in the cloud or to Azure SQL in one of those databases
5:49
But SQL package is quite versatile and very functional and that has operations for backpacks and DAC packs
5:59
So if you are needing to orchestrate data application movement, then that's when you've got the tool for SQL package in terms of scripting out
6:13
So the syntax for SQL package is straightforward, but it's great to understand the one variable you want to watch for is the action, because that determines whether or not you're doing extract or publish or import or export or one of the others
6:37
And then from there, there are different parameters and properties, lots of them, that allow you to fine-tune that process
6:47
I see a couple of questions in the chat. Is there active updates to SQL Package
6:55
Absolutely. SQL Package gets updated roughly quarterly. There is active development to it
7:05
I would say you can expect to see an update to it within a month, a month and a half now
7:13
And then a question from Paul on deploying file stream and memory optimized structures
7:21
Paul I do not know that off the top of my head but I am making a mental note and I going to see if I can find that out and reach out and get back to you So we got SQL patches
7:35
that allows us to work with these data tier artifacts. Over the last, I would say, year and a half, two years
7:48
there's been more frequent updates to it. There was a period of time where there was a lot of work happening under the covers
7:58
And what that work was, was bringing it to .NET Core so that it could be cross-platform
8:06
And why do we care that it's cross-platform? Because the fundamentals within SQL Package work with database projects
8:15
which we've known and loved in SQL Server data tools in Visual Studio for years now
8:24
If you're using Visual Studio 2019, it's the data platform workload. And that gives you this declarative database lifecycle for your database applications
8:38
Back in September of 2020, we added the SQL Database Projects extension
8:45
to Azure Data Studio. This means that if you are on Windows or Mac OS or Linux
8:52
you can use Azure Data Studio, free, open source, and work with SQL projects
9:00
It's going to use the .NET Core SDK to handle the build operations right there in the UI
9:06
and as well as that .NET Core SQL package. So we have that now available
9:13
and slideshows are fun and all, but I want to go ahead and dive right into working with database projects
9:22
in Azure Data Studio. We're going to take a look at an example where you can create projects
9:27
either from scratch or you can grab an existing database and work with that
9:32
So we'll do that. We're going to make some changes to our database
9:37
We're going to be able to build it, look at what the deployment process looks like
9:42
We're going to use some functionality from database projects known as post-deployment scripts
9:50
A post-deployment script gives you the opportunity to run additional T-SQL following the original deployment to do things like modifying additional permissions or inserting some static data
10:06
There are two other extensions in Azure Data Studio that are really complementary to database projects
10:15
The first is the Data Tier Application Wizard that gives you a UI on top of SQL Package
10:21
And the second is Schema Compare, which allows you to look at the diff, the file differences between databases, between DAC packs, between a DAC pack and a database
10:36
And we'll take a look at that as well. So let's have some fun
10:42
I am switching over into Azure Data Studio. and I have a database
10:52
It's an AdventureWorks 2019 in Azure SQL. Since I've installed the SQL Database Projects extension
11:01
I have the Projects pane. But even from within the Object Explorer
11:07
I can grab a database and say, I need to create a project from the database
11:12
and it's going to give me the options of the server and the database
11:17
but it's also asking me where I want to put it. These are going to be all the scripts that define the schema
11:25
the tables, the procedures of this database. So I've got a folder for this
11:31
and you can have it pre-organize the files for you. I am going to just use straight-up object type and hit create
11:40
So Azure Data Studio is running across in the background to grab the scripts for all the tables and the views, and it's going to pop us into the projects pane to basically open up this SQL project
11:59
I missed a really important point when I was talking about Visual Studio earlier
12:05
The current SQL projects in Visual Studio, you're already working in them
12:11
That's fantastic, but you want to give Azure Data Studio a try
12:15
They're compatible. So if you are collaborating with others and using different applications
12:22
there's cross-compatibility there as well. We have our project. We've got the tables and the functions
12:32
So for example, we've got a customer table here. This is the customer table definition from the database that I just grabbed out
12:43
Now, we've got our scripts. That's great. But let's take a look at some of the functionality of the what now
12:53
So if you are ready to build your changes, let's say we want a DACPAC from this, we hit build
13:03
When we're ready to publish this out, let's say that we don't have a pipeline that publishes our DACPAC for us
13:11
We've got permissions. We can do that directly from here, as well as a tie-in directly to Schema Compare
13:17
You can be adding additional objects, including post and pre-deployment scripts, as well as some ways to get a little bit under the covers
13:29
Changing the target platform, for example, allows us to say, I've exported a database from SQL Server 2014, but I'm ready to deploy this onto SQL Server 2019
13:44
or perhaps you've exported it from 2014 and you'd like to use some of the functionality added in SQL Server 2019
13:54
When you do the build process, it's going to validate the code that you've used against that engine
14:08
So if you're using things that are only available in SQL Server 2019
14:12
you're going to need to make sure that your target platform matches that feature set
14:19
For those of you that I mentioned already work with SQL projects quite a bit
14:27
you do have direct access to edit the SQL project file if you want to get really hands-on
14:36
So the schema compare is the most complimentary other extension and database projects will prompt you to work with it
14:49
The other one is the SQL server backpack extension And this gives you a nice wizard interface on top of SQL Package
15:07
So if you're not a fan of command line or for some simple operations
15:11
you'd like to be able to go through the wizard, I do highly recommend this extension
15:19
But let's work with our project here. I am going to add a column to our customer table just for our rewards account ID
15:33
I'll make that. I've saved the file, but this has effectively added a column
15:46
When I build, it's going to go ahead and use the .NET SDK here
15:55
and it's going to build a DACPAC. When I stored the project on my local machine
16:03
it's going to build and place the DACPAC in a similar folder in there
16:09
so if you're interested in manually grabbing that DACPAC. it has built successfully and the deck pack is available in the folder there
16:20
but everything that we're doing within Azure Days Do does not require us to manually grab that
16:27
I could do a schema compare now let's say I don't remember all of the changes that I've made
16:34
and I want to see kind of the difference between the current project, that DACPAC
16:43
and a live database. So we'll select a target of a database
16:50
and I'll use the database that I pulled this project out of
16:55
Before I hit Compare, I do want to make sure that I highlight these options
17:04
lot of changes, potentially, when you're doing a schema compare. So it's nice to be able to filter
17:11
out or exclude specific objects, or whether you're looking at other options. There's some good ones
17:22
down here. Whitespace. Whitespace changes, if you want to be able to ignore those, you can make sure
17:28
that that's checked. But when I hit compare, that's fantastic. We are live
17:42
I'm going to give it another go. One second. So comparing to a database, I hit compare
18:04
Okay, that's failing. That's okay. when I do that schema comparison
18:36
it gives us this view where you can see the difference between things that have changed
18:43
In this example, a secondary phone number column has been added in the project, which is on the left
18:55
and on the right, where the live database is, that secondary phone number is not there
19:02
If you use source control frequently, this is not a new concept to you
19:09
so let's take a look at uh not only were we able to kind of just modify this schema
19:22
flat out but i mentioned earlier those post-deployment scripts we can add all kinds of
19:30
new tables but i wanted to focus on a post-deployment script because it allows us to
19:38
kind of break out of the mold as we need. So I'm going to say
19:44
add test customer is my post-deployment script name. And this adds us
19:54
an extra SQL file into our project. And if I grab some text
20:01
real quick, I have an insert statement that allows us to add a customer row
20:12
with my name and a sample password. So if I save this
20:20
and let's go ahead and publish it. So I'm going to publish to AdventureWorks
20:31
Before I do that, I want to look at one thing. If I select top 1000 from the customer table
21:02
Looks like my database disconnected
21:16
That's okay
21:30
We have databases aplenty. Let's take a look at a different server
21:44
When in doubt, reboot. Except for not all the way when you're in the middle of a presentation
21:59
I have reopened Azure Data Studio, and so it's reconnecting to a couple databases
22:07
It's going to remember the workspace that I had open. You can combine multiple SQL projects into a single workspace in Azure Data Studio
22:21
If you familiar with Visual Studio you can combine multiple projects into a solution That same idea applies to the workspace concept
22:35
in Azure Data Studio. So when I closed out of impatient irritation and reopened
22:42
it remembered that workspace, reopened the workspace, and reloaded that project for us
22:50
Looks like my database is back. So we're going to go ahead and connect and run this query
23:00
What I wanted to point out is that we don't have a column
23:05
for rewards account ID in the table. So we're missing that element of the schema
23:20
that's in our project. Let's also check to see if I'm in here already
23:31
I am. I am no longer
23:47
We're going to publish our project. When we publish our project, we should expect that the changes that we've made to the schema
24:02
namely, we should have a rewards account ID column, we should expect to see this
24:10
as well as we have a post-deployment script that should run and do some changes so we can even have it
24:19
insert one for me and then two for louie back there who's sleeping we'll save that
24:31
so i'm gonna publish our project we're gonna check out the result of that before i hit publish
24:43
I mentioned earlier that we have to let the tools handle that differential between the states of the database
24:54
There's an element of trust there, and there's also the expression trust but verify
25:00
That's what generate script is great for. The ability to generate the scripts, whether in Azure Data Studio or through SQL package
25:12
there are options to do this. We can spit out the scripts that will be applied to publish
25:21
So we can inspect those manually and make sure we're okay with the changes. You can assess the
25:28
risk of the changes. You can decide what kind of impact they might have on production downtime
25:36
if it's going to be seconds or minutes, that kind of thing. If I scroll all the way at the bottom
25:42
We can see our post-deployment scripts. I trust. I've mostly verified
25:52
I'm going to go through with the publish. So we are publishing the project right now
26:08
And there's our deployment operation. It should take a few seconds. I'm checking for a few comments
26:20
Cool. So we've got our deck pack deployed. When I run select top 100 from sales.customer
26:32
I should see a rewards account ID. It's empty. We just changed the schema
26:40
We didn't do that. I check for the data added by our post-deployment script
26:50
It's there as well. So this gives us kind of the full picture of what happened after the deployment
26:58
as well as during the deployment where we got that rewards account ID
27:02
So from this SQL project, you have the ability to work with, whether it's a create procedure, to write procedures
27:18
you don't need to worry about create or alter. You write create and the publish process handles
27:30
determining whether or not that procedure is already there. Let me make sure I'm not missing anything
27:42
So what we have done with our project is that we were able to create a new project by extracting that from a database
27:58
We were able to edit it. We did some basic editing, but I'll go back in a minute and show a few tips that I have for kind of working and editing in Azure Data Studio
28:10
You're able to build the project right from within Azure Data Studio, and then you're able to publish that project
28:20
Some frequent questions that I get come around. Can I build from a command line
28:31
SQL package does the extract and publish operation, but what about build
28:36
That's a very reasonable question. And for that, if you go to aka.ms..
28:58
Let me grab the full... aka.ms slash Azure Data Studio SQL projects
29:19
This is the full documentation, but the nugget that I often lead people to
29:27
is being able to build a project from the command line. After you've built in Azure Data Studio once
29:35
Some files are placed in your user directory that can be used to then build projects from the command line
29:48
So that's one item that I wanted to make sure that you knew about
29:55
as a frequent question. thing about editing in Azure Data Studio that comes up pretty frequently is I was doing some
30:09
I will say, low intensity changes. I was adding a column. I wasn't creating a new table from
30:19
scratch, that kind of thing. But if I say, want to add a table, and we're going to call this
30:27
test table, it gives me a really basic framework. Let's say I want to create an index
30:39
and the syntax for that escapes me. There are snippets included in Azure Data Studio
30:47
So if you start typing SQL, IntelliSense is going to suggest for you
30:53
some of those snippets, whether they're createIndex or use a cursor. So if I do SQL index, I'm going to do createIndex
31:11
and this will allow me to define an index a little bit easier
31:16
than trying to remember the syntax completely from scratch. So that gives us kind of an overview of editing in Azure Data Studio
31:33
Azure Data Studio is a great application for kind of a broad set of functionality with SQL Server and Azure SQL
31:47
It's gaining new functionality at the time. I swear every single time I see folks' presentations
31:55
I see things that we've shipped that I missed because there's so much being added all the time
32:00
But for a lot of people, especially developers, they're working in VS Code in their projects
32:07
and there is development functionality in VS Code. SQL projects in this form are not there yet
32:18
but because Bob Ward talked about SQL containers a bit earlier, I did, since we have a minute
32:27
wanted to take a quick detour over to VS Code and talk about container-based development
32:36
SQL database projects in Azure Data Studio are continuing to get new functionality
32:41
So please, if you have feature requests, drop them on the repo or vote on the ones that are already there
32:51
Same thing goes for VS Code and the MS SQL extension for VS Code
32:57
It's used for connecting and querying database servers. It has a lot of functionality like the query history
33:06
I mentioned that I'm taking this detour because of MS SQL containers, especially as a tool for development
33:18
I'm not going to go to bat for them as production tools in this session
33:25
Bob's got that covered. But as a development tool, I want to show you something pretty cool
33:31
There's something called remote container-based development in VS Code. What that means is that VS Code sets up a container for itself on your local desktop on your Docker desktop
33:49
called the App Container. And it connects to that and it runs it process in that
33:55
What we can do because of MS SQL containers is really quickly set up SQL server environments
34:05
for our development instead of either having to be on a Windows box
34:10
and or installing a SQL server locally, we can create that as a part of that development environment
34:20
So it becomes a lot more reusable and faster to spin up and spin down
34:26
You're no longer worrying about managing containers independently. It becomes a part of developing in VS Code
34:36
On top of that, as you automate these development environments, tools like SQL Package can be used
34:46
So bearing with me for a moment, let's say you have a development environment
34:52
You're trying to build a .NET Core App. You're C Sharp developers, many of you, not a far-fetched prospect
35:01
If you already have a DACPAC built and you put it as a part of your development environment
35:09
SQL package can be used to do that DACPAC employment as the dev environment spins up
35:16
That's the prospect that's in the repository for SQL development that I'll show you in a minute
35:24
The other side of this coin is that remote development in VS Code is the GitHub Codespaces environment
35:36
So yes, what I'm telling you is that not even on your local machine, but in the browser
35:45
you can spin up that remote development environment with a SQL server in there with you
35:53
It's kind of like that moment when they're like, they're in the house with you on horror films
35:57
Yeah, the SQL server is right there with you. So as you look at a dev container setup
36:05
you would have a part of a Docker Compose add an additional image
36:11
So that's the SQL server latest. And you can set those environment variables
36:18
that we saw earlier, including accepting the EULA and setting an SA password
36:24
I know that's the worst SA password in the world. Bear with me
36:30
The other part about automating your development environment that's so important is that you can set up VS Code
36:40
to be ready for you. And I'm going to demo this here in a second
36:45
But you can say, I want these extensions. I need the C Sharp extension
36:50
and I need the MS SQL extension. So I can build my .NET Core app
36:56
and I can run queries against my development server. You're set. You probably have other extensions that you want
37:04
and you can add them to that array there. You can set up the MS SQL extension
37:10
going into your container. So I can say I want my local host container
37:18
this is the SA password I sent and it ready to go So this instance of VS Code it has that Docker Compose that not only has the app file
37:47
but the database file. This is already completely set up for me
37:54
That seems too easy. I know Bob said that he challenged someone
38:01
to get set up with SQL Server development on a Mac in five minutes
38:06
and they did it in three. Let's see how we do. I'm in VS Code
38:14
the only thing that I need to make sure that I have
38:20
besides Docker on my computer, is the remote containers extension. I am going to add development container files
38:37
I have to open a folder first. Let's open a folder. I've got a folder, it's opening, and then it's going to ask me what dev container do
39:01
I want to add. I searched for SQL. There's a C-sharp.net and MS SQL definition. Huh
39:14
Look at that. It's going to bring in a kind of bare bones setup for us
39:21
You can learn more about it at the link right here. But VS Code says, wait a minute, I see a dev container definition
39:30
Do you want me to spin up that container and reopen? So I'm going to hit reopen
39:37
VS Code is going to handle the Docker pull for the SQL server
39:43
It's going to reopen itself in that app environment with .NET Core
39:51
so I can do my app development. VS Code is going to install the MS SQL extension for me
39:59
because it knows that it's this really amazing tool. and then we'll be able to use the MS SQL extension
40:07
to connect to our container. So we have two complementary applications. We've got Azure Data Studio
40:16
where we can do kind of focused SQL project development. We can build those SQL projects into our DAC pack
40:26
our data tier application files. And then we also have VS Code
40:31
which is complementary, a development environment where we have got some really essential SQL tools
40:40
that we can tie right into modern development concepts like containers. So VS Code is starting the container and it's opening the remote
40:54
And while I give it a moment to do that, This repo right here github slash daisy squared slash SQL development tools has info about database projects in Azure Data Studio And it has information about the dev containers in VS Code
41:19
We definitely don't have time to go this far down the road, but I can tell you that if you fork
41:28
that SQL Development Tools repository and you open in Codespaces, you can get that same VS Code Development Environment
41:43
right in your browser. So this is the Codespaces version, whereas my dev container
41:56
is installing the extensions, opening the remote
42:07
connecting. Still got my dev container definition. I'm in the remote now
42:18
It's installed. The MS SQL extension is going to grab the tools service
42:25
and then it's going to have that connection pre-populated for us. When we look at database development
42:41
I started at the beginning and I said, there's kind of a hitch to database development
42:46
We have to keep in mind that there's a database state, but with the proper tools
42:52
you can still use the same development cycles that you use for building C-sharp applications
43:00
So I do suggest that you check out the Database Projects extension in Azure Data Studio
43:07
especially if you're using SSDT, because they can be really complementary experiences
43:13
especially if you're collaborating with other people, different people may be using different
43:18
tools. And then with container-based development, you get a really reusable environment. So in this
43:27
example, we've got this MS SQL container where we can create databases. And in the example in that
43:37
GitHub repo, there is a script that runs as the container launches
43:44
that looks for DACPAC files and then publishes them in the container
43:56
when the container spins up. So instead of hopping into this empty environment
44:04
you can throw a DACPAC in there and hop into your app's database
44:10
and get right to work with your C-sharp code or your Node.js code or whatever you truly care about
44:23
I have some follow-up to do with Paul on a question about memory-optimized objects
44:32
You all are welcome to reach out to me either via email or on Twitter
44:37
And here are some resources to help you go after some of the cool tools that we looked at. Thanks for joining me today. That's all I have

Livestream Starting Soon

SQL Projects for Database Development by Drew Skwiers-Koballa || SQL Server Virtual Conference

CSharpCorner

Java Essentials - Access specifiers in java

"Should I Apply for Financial Aid?" - Livestream Clips

Gemini 2.5 Navigates, Searches & Clicks Your Browser – Automatically

"Will I Get Accepted to College?" - Livestream Clip

P1-M622-16DR Mini PLC First Program: Start-Stop Motor Circuit!

Build a Smart Java Calculator with AI in NetBeans | Java Swing Full Tutorial

Build a React.js EPUB Reader & Viewer Offline Web App in Browser Using TypeScript

Complete online examination system project Java Swing + MySQL | Free Source Code!

Problem 2 | Essential SQL

shane hummus: best google certificates for high-paying jobs nobody is talking about

shane hummus: best youtube upload settings for 2025: algorithm & seo tips!

The BEST Proposed Flags That Countries Didn't Use

Geoguessr 1v1 BUT Zi8gzag Has To Click The Top of The Country

Maps that are Insightful

This Island SHOULD Be Swedish - Why Isn't It?

Livestream Starting Soon

Up next in 10

SQL Projects for Database Development by Drew Skwiers-Koballa || SQL Server Virtual Conference

CSharpCorner