{"id":1483,"date":"2022-11-07T07:00:01","date_gmt":"2022-11-07T15:00:01","guid":{"rendered":"https:\/\/devblogs.microsoft.com\/azure-sql\/?p=1483"},"modified":"2022-11-04T14:10:50","modified_gmt":"2022-11-04T21:10:50","slug":"azure-sql-database-change-management","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/azure-sql\/azure-sql-database-change-management\/","title":{"rendered":"Connecting the Dots of Azure SQL CICD Part 2: Local Development"},"content":{"rendered":"

Join us for part two of our Azure SQL database change management (CICD) series. This post covers local development environments with Visual Studio Code.<\/p>\n

Prerequisites Check<\/h4>\n

For Database Projects to deploy correctly, we need to have the .NET Core SDK<\/strong>\u00a0installed.<\/p>\n

You can get it here<\/a>\u00a0with a direct link for windows x64 here<\/a>.<\/p>\n

You can also check if you have it installed by typing the following command at a windows terminal prompt:<\/p>\n

dotnet --list-sdks<\/code><\/pre>\n
\"Image<\/a><\/p>\n
After it’s installed, restart Visual Studio Code to uptake the new .NET path.<\/p>\n
 <\/p>\n
Creating a table<\/h2>\n
When we left off in part 1<\/a>, we had finished setting up our environment for Azure SQL Database change management, and had created a Database SQL project called Employee App<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
Now, right click<\/strong> on the Employee App node in the Database Projects pane and select Add Table.<\/strong><\/p>\n
\"Image<\/a><\/p>\n
In the New Table name<\/strong> box on the top of VS Code, enter employees<\/strong> as the table name. Then press Enter<\/strong>.<\/p>\n
\"Image<\/a><\/p>\n
We now have a simple create table script with a single column in our Database Project.<\/p>\n
\"Image<\/a><\/p>\n
To speed things along, we can use some pre-created code for the employees table. The point was to show you how to add a table via the menu.<\/p>\n
Replace the code with the following:<\/code><\/code><\/p>\n
\n
\n
CREATE TABLE [dbo].[employees]\r\n(\r\n    [emp_id] INT NOT NULL PRIMARY KEY IDENTITY,\r\n    [first_name] NVARCHAR(100) NOT NULL,\r\n    [last_name] NVARCHAR(200) NOT NULL,\r\n    [address] NVARCHAR(200) NOT NULL\r\n)<\/code><\/pre>\n
Nothing too complicated but it has a bit more to it than a single column.<\/span><\/pre>\n<\/div>\n
<\/div>\n
Shop Local<\/h2>\n
We have our table, now to deploy it into an actual Azure SQL database before we commit our code. Before we start this process, make sure that Docker is up and running.<\/div>\n
<\/div>\n
Here is where it gets even more awesome; right click<\/strong> the Employee App project and select Publish<\/strong>.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n<\/div>\n
<\/div>\n
\n
The first question of the Publish Wizard is where do you want to publish the project. Select Publish to a new Azure Database emulator.<\/strong><\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n<\/div>\n
<\/div>\n
Next is the Azure SQL server port number. We can leave as the default of 1433 and just press enter.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
The next two questions are what password do you want to use for the sql admin user (will be created as the sa user). Enter the password you want to use<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
then enter it again to confirm and match<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
The wizard is then going to ask you about what docker image to pull. If you are on windows on an x86\/x64 based CPU, select the Full<\/strong> image<\/strong>. If you are on windows on ARM or just happen to be on a Mac, select the Lite image. For all the posts going forward, we are going to use the Full image.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Now, accept<\/strong> the license agreement<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
And select the latest Default<\/strong> as the image tag<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Select Don’t use profile<\/strong> for the publish profile<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
And finally, select Employee App<\/strong> as the database name we will create<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Visual Studio Code will now pull the Docker image, start it up<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
and deploy your project to it all within minutes.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
\n
You now have a full instance of SQL running in Docker locally that you can use for development. It took just a couple of minutes to deploy and couldn’t be easier.<\/p>\n<\/div>\n
<\/div>\n
Make a Connection<\/h2>\n
Back in Visual Studio Code, after the deployment, select the SQL Server Connection extension if not already selected so that we can connect to our Azure SQL Database local instance<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Click + Add Connection<\/strong> and with the connection wizard, enter localhost<\/strong> for the server name.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Enter Employee App<\/strong> for the database name<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
Select SQL Login<\/strong> for Authentication Type<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
Enter sa<\/strong> as the user name<\/div>\n
<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
And enter the password you used when you created the Docker image during the publish process<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Select Yes to save the password in the connection profile<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
Last you can provide a connection profile name if you choose or just press Enter<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
In the Connection panel<\/strong>, expand the Tables<\/strong> node and you can see the employees<\/strong> table<\/strong> right there, in the database, created for you via the project.<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
<\/div>\n
<\/div>\n
Code Review Time!<\/h2>\n
Now that we have our code all set and ready to go back into the repository, we can use git to push the changes back into our GitHub repository. From a terminal windows\/command prompt, enter the AzureCICD directory and add the project files to our GitHub commit<\/div>\n
\n
git add .<\/code><\/pre>\n<\/div>\n
now we create a commit<\/div>\n
\n
git commit -m \"first commit\"<\/code><\/pre>\n<\/div>\n
Author identity unknown<\/strong><\/div>\n
\n
If you run into an Author identity unknown error, just run the following commands replacing you@example.com\u00a0 with the appropriate value you used with GitHub<\/p>\n
git config --global user.email \"you@example.com\"\r\n<\/code><\/pre>\n<\/div>\n
next, we push the changes back into the repository<\/div>\n
\n
git push<\/code><\/pre>\n
Looking at the repository on GitHub, we can see our committed files:<\/p>\n<\/div>\n
<\/div>\n
\n
\"Image<\/a><\/p>\n<\/div>\n
\n
Azure SQL CICD to be Continued in Part 3<\/h2>\n
What’s left? Testing, testing, and more testing. In the next post, we will see how we can use GitHub actions and tSQLt to test our code as we commit it so that we have clean, bug free pull requests!<\/p>\n<\/div>\n
<\/div>\n","protected":false},"excerpt":{"rendered":"
Join us for part two of our Azure SQL database change management (CICD) series. This post covers local development environments with Visual Studio Code. Prerequisites Check For Database Projects to deploy correctly, we need to have the .NET Core SDK\u00a0installed. You can get it here\u00a0with a direct link for windows x64 here. You can also […]<\/p>\n","protected":false},"author":95874,"featured_media":81,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[532,1,484,92,533],"tags":[534,95,465,469,448,30,449,93,531,94,530],"class_list":["post-1483","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-azure-data-studio","category-azure-sql","category-containers","category-devops","category-visual-studio-code","tag-azure-data-studio","tag-azure-devops","tag-azuresql","tag-azuresqldb","tag-ci-cd","tag-developers","tag-development","tag-devops","tag-docker","tag-github","tag-visual-studio-code"],"acf":[],"blog_post_summary":"
Join us for part two of our Azure SQL database change management (CICD) series. This post covers local development environments with Visual Studio Code. Prerequisites Check For Database Projects to deploy correctly, we need to have the .NET Core SDK\u00a0installed. You can get it here\u00a0with a direct link for windows x64 here. You can also […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1483","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/users\/95874"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/comments?post=1483"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/posts\/1483\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media\/81"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/media?parent=1483"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/categories?post=1483"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/azure-sql\/wp-json\/wp\/v2\/tags?post=1483"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}