J
j_folberth
About
This article will talk through how one can import an existing database that lives on a SQL Server either on prem or in Azure onto a local machine. This import will create a .sqlproj. Per the documentation a sql Project is:
A Management Studio project is a collection of logically related scripts and files that can be saved together for database administration and development.
Once we have this then we will have the ability to make and test changes locally, place the contents under source control, and leverage Azure DevOps for CI/CD deployment. One misnomer that I will call out is that we will do this all without leveraging a Visual Studio License, which historically has been a prerequisite for managing SQL Databases.
Pre Reqs
In order to follow along with this process, one will need to have a combination of access and software installed on their computer. I will be walking through using Visual Studio Code; however, the process is quite similar if leveraging Azure Data Studio.
- Visual Studio Code with the SQL Database Projects Extension OR Azure Data Studio
- Via network connectivity/firewall ability to establish a connection to the desired SQL Database
- Ability to login to the database and read all schema objects. This can be achieved via SQL Login or Entra ID. We will use Entra ID for our purposes.
Importing the Database
Now we will go step by step how to effectively download and recreate the database schema files on our local machine. This process will entail Creating the Connection Profile, Downloading the Database, and Confirming Your Local Copy.
Creating the Connection Profile
The first step is to launch Visual Studio Code (VS Code), or Azure Data Studio. Once we have this launched VS Code, we will leverage the Command Pallet in VS Code by the shortcut Ctrl+Shift+P and type in "Database Projects"
This will provide a list of actions one can perform in VS Code leveraging the Database Projects Extension. In our case we want to select "Database Projects: Create Project From Database"
After selecting this you will see an option to "Create Connection Profile" --it is possible if you have previously used Data Studio or VS Code to create a Connection Profile to the desired database in the past. If so, then feel free to skip this section.
The next prompt will ask you for the connection string. We will use just the server address to keep it basic. I am using a SQL PaaS Database that contains just the sample schema/data for Adventure Works.
We will then be prompted for an optional parameter to include the name of the database we'd like to download. I HIGHLY recommend scoping your project import to a single database. The reason behind this is, typically, individual databases are maintained, operated, and scoped for a single purpose. This means in the grand scheme of source control management and CI/CD we would want to scope our projects according to not only lifecycle but purpose as well.
In my case the database name is "sqlmoveme"
We will then be prompted to how to authenticate to the SQL DB. I will be using "Microsoft Entra Id - Universal". The next screen will confirm the Entra Account we will be using to connect if leveraging SQL Login it will ask for the username and password. Odds are you will be choosing the "Add" option. When clicking this VS Code will launch a new browser session where you will log/MFA into the Entra ID Tenant.
After this step the MSSQL library will be testing a connection behind the scenes. This can be confirmed in the VS Code Output window by selecting MSSQL from the drop down and inspecting the logging:
Downloading the Database
After connecting VS Code will now prompt you to give your project a name. I would recommend naming the project either the same as your database or something similar.
After this VS Code will now ask what folder we should download and create the .sqlproj in.
I would recommend each database you are downloading have a separate folder. This will help later if we want to put our database under source control.
Once we have a location we will be asked how to import the database. This can be a bit of a dealer's choice; however, I am going to choose to import the "Schema/Object Type" option. Others are perfectly fine.
Once selecting this you will be prompted if permissions and security scripts should be included. I would advise you not to include this as it will contain the credentials for SQL Logins, which we should not be storing under source control.
The next prompt around "SDK-style project" may sound a bit confusing. This is prompting the user if they'd like to leverage Use SDK-style projects with the SQL Database Projects extension though listed in preview at the time of this writing I'd still encourage you to select this option. It will let us build the .sqlproj leveraging a 'dotnet build' build command and more importantly won't require us to have Visual Studio.
Once selected VS Code will download the database locally and the result, in the case of Adventure Works, will look like:
Confirming Your Local Copy
At this point we can see the database schemas, in our example this is just `dbo` and `SalesLT`. If expanding these folders we can see all the object types and their definitions. In this screenshot the table Address has been selected and one can see the associated SQL script:
Let's make a change and save it. I am going to add an "Address Line3" with NULLs being allowed into our table definition and save it.
Now let's right click the Address.sql file in the File Explorer and select "Reveal in File Explorer". This will launch File Explorer where we can confirm the date modified on our Address.sql has been updated.
Building the Database
Now that we confirmed we can make these changes in our local .sqlproj the next logical question is how should we deploy or product an artifact to deploy. Since we are using the SDK-style projects with the SQL Database Projects extension a `dotnet build` command entered in the VS Code terminal will produce our deployable artifact. I am going to use .dacpac for future iterations of this blog series.
Next Steps
Once we have the .dacpac file locally we can deploy this either manually through SQL Server Management Studio or Azure Data Tools or preferrable through a CI/CD process which I will cover in a future post leveraging the SqlAzureDacpacDeployment task. However; before we do that we should discuss how to push the database project we just created to source control. To be alerted when this blog is available be sure to follow me on Tech Community
Continue reading...