Deploying Database Changes from Visual Studio to SQL Server 2012

Hello Readers,

Today we will demonstrate how to deploy a database using SQL Server Data Tools (SSDT), available in Visual Studio Shell. SSDT is a power tool where we can crease databases and database objects- and also perform database schema compares. Here I will show you how to connect Visual Studio to SQL Server and deploy (publish) a database.

SQL Server

When you login to SQL Server Management Studio, in the Object Explorer to the left, you can navigate through the databases. Here we have the famous AdventureWorks database already loaded. We aim to modify a column in the Person.Address table.

Figure 1. Object Explorer with AdventureWorks

Right-clicking the AddressLine1 column and select Properties to see the column properties. We will increase the length from 65 to 70, as an example.

Figure 2. AddressLine1 Properties

Data Tools in Visual Studio

Now that we have located the target column, we open Visual Studio to use the SQL Server Data Tools. First we need to create a new Project and a new connection to the AdventureWorks database in SQL Server.

Figure 3. Visual Studio New Project

Make sure you have selected the SQL Server Template to your left, and the SQL Server Database Project should appear in the middle dialogue box. Name your database and click OK. 

To connect to the SQL Server and database, right-click the project name and select Import   -> Database. Select New Connection and type in the server information and choose the database name from the drop down list towards the bottom of the Connection Properties window. You can click the Test Connection button at the bottom left, to see if you have the correct server typed in, and valid database selected.

Figure 4. Adding a New Connection

Also, change the Folder Structure in the Import Settings to Object Type.

Figure 5. Finishing Importing AdventureWorks

After you click Start, Visual Studio will begin to import AdventureWorks. After it is finished, in the Solution Explorer to the right, navigate to the Tables folder and select the first table, Address.sql. This table contains the column, AddressLine1, whose length we want to modify.

Figure 6. AddressLine1 Column in Address.sql Table

Simply click the Data Type nvarchar(65) and change it to nvarchar(70), and save all.

Deploying AdventureWorks

Since we have finished with our changes, we now need to deploy those changes into SQL Server. Right-click the AdventureWorks Project Folder in the Solution Explorer and select Publish. Re-enter the connection details and hit Publish. 

Figure 7. Publishing AdventureWorks

The middle highlighted box is the Publishing Options, and the bottom box shows the Output Dialogue Box. Note that it says the "Build: 1 succeeded".

Figure 8. Data Tools Output- Publishing

Turning back to SQL Server, in the Object Explorer, click the Refresh button with the circular arrows. Then navigate to the Person.Address Table and right-click the AddressLine1 column. Observe the change in length! It is now 70 characters long.

Figure 9. Checking AddressLine1 Length 70

Fantastic! We were able to connect to the AdventureWorks database in SQL Server, and change the length of a column in a table using SQL Server Data Tools in Visual Studio. Then we deployed those changes back into SQL Server and observed the changes. This is just a taste of what SQL Server is capable of when combined with SQL Server Data Tools- Visual Studio.

Stay tuned for more SQL posts!


Thanks for reading,

Wayne
@beyondvalence
LinkedIn