Publishing

FIRST: We’re using SSDTDatabase Project and we introduced an error in an earlier post; resolve this error, delete or use the correct column name in the view, to continue.

Typically, debugging is for a developer to examine, study, learn, and, obviously, develop and debug the database in an environment separate and independent from other developers. This target debug database is the one we saw in the Database Project Properties | Debug | Target Connection String setting.

Tip: The debug database may reside on a networked SQL Instance.

When publishing, the target database is now shared and accessible by other developers and applications on a SQL Server instance. During this exercise we’ll publish to two instances simulating test and production environments: one on a local network and the other on Azure.

1. Successfully Build the Database
   1. Check for Errors
   2. Build the Database
2. Create a Publish Profile
   1. Resolve Mismatched SQL Server Versions
3. Publish from the Output Script
   1. Review of the Script Elements
   2. Publish from the Script
4. Publish to Second SQL Server

Successfully Build the Database

Publishing a database requires no errors. To check and validate no errors exist well 1) check the Error List pane, and 2) verify by building the database.

Check for Errors

Most of the time you’ll be resolving object errors (DDL Errors) as the database is developed. It is good practice to review the Error List.

1. Open Error List pane: View > Error List
2. Look to see no errors exist
3. Also review any Warnings or Messages…
4. Prefect, no errors!

Build the Database

Now we’ll build the database, review the output, and also recheck the Error List:

1. Right click the project SSDTDatabase
2. Select Build
3. The Output window will open automatically and show the build steps

4. A successful build returns a positive message and no errors are listed in the error list:

========= Build: 1 succeeded or up-to-date, 0 failed, 0 skipped =========

Create a Publish Profile

Once the database successfully builds, it is ready for and can be published. We’ll begin with some organizational tasks followed by creating and publishing the database on a server.

1. If necessary, create a New Folder in the SSDTDatabase project and name it Deployment
2. Right click the project SSDTDatabase
3. Select Publish…

4. A new window opens with an empty database connection
5. Click Edit…

6. Select the Browse tab
7. Expand Network
8. Select your SQL Server – Mine is named WINSQL
9. If your SQL Servers do not broadcast their availability…
   • Manually enter the connection details
   • Get SQL DBAs to show the servers
   • Change jobs
10. Enter the Authentication method and enter any credentials details
    • The user must have CREATE DATABASE privileges
11. Leave Database Name: set to <default>
12. Verify the credentials by clicking Test Connection
13. Click OK
14. Click OK and return to the Publish Database window

15. In the publish database window, the Database name has been removed
16. Since the project name (SSDTDatabase) is the default, we will use it for the Database name
17. For Database name: enter SSDTDatabase

18. Click Save Profile As… this will save time when Advanced settings are changed
19. Navigate to your Deployment folder in the project
    • It should be nested under the Solution (SSDT Training) > Project (SSDTDatabase) > Folder (Deployment)
20. Change the name to SSDTDatabase.Network.publish.xml
21. Click Save
22. The new new publish settings will appear in the Solution Explorer
23. Click Generate Script

Expert Tip: publish scripts have the compound extension .publish.xml

Resolve Mismatched SQL Server Versions

You may get an error in Data Tools Operations pane (my emphasis because the server versions may be different in your environment):

A project which specifies SQL Server 2016 as the target platform cannot be published to Microsoft Azure SQL Database v12.

1. Set the Project Properties > Project Settings > Target Platform to your SQL Server instance
2. On my network I’m using the developer edition of SQL Sever 2017.

Tip: Deployment to different SQL Servers is easier when they are the same version.

Tip: The IDE panes can be arranged to your preference.

Tip: Missing IDE panes can be found under Menu > View or Menu > View > Other Windows

1. Double click SSDTDatabase.Network.publish.xml
2. Verify the settings are correct
3. Click Generate Script

Publish from the Output Script

Review of the Script Elements

When clicking Generate Script is a success, a script is created.. We’ll note the most important elements:

① Current Database – [master] is ok
② SQLCMD mode – The script must be run in SQLCMD mode
③ :setvar DatabaseName “SSDTDatabase”
④ Verify SQLCMD mode is enabled
⑤ Drop the database if it exists
⑥ Create the database.. and further more DDL commands.
⑦ SQL Server Connection

Items ①, ②, and ⑦ show the SQL Server, Database connection, and SQLCMD mode. SQLCMD mode isverified on in section ④.

SQLCMD mode allows variables, they are set in item ③. The variables are then used by other statements in the script.

Section ⑤ checks to see if the database exists referencing the variable $(DatabaseName); if the database exists, then drop the database. We should probably change this behavior in the publish xml.

Section ⑥ creates the database specifying the file locations and collation.

Publish from the Script

Publish scripts contain commands to create, drop, and alter objects; add and remove properties; and insert and delete data. The generated scripts do their best to not lose any data and/or copy data as table structures change. Sometimes, developer intervention is necessary with large or complex refactoring steps.

To publish the database from the script:

1. Review the script for surprises
2. To run the script and deploy the database click Execute Script (ctrl-shift-e) in the top left
3. Review the Messages (SSDT splits the code pane into T-SQL and Messages tabs) for any publish errors
4. Verify the database, objects, and data in a query tool of your choice. I am using Azure Data Studio in this example.

When writing Script.PostDeployment.sql, we included a line beginning with :r; the deployment script did not contain that line; SSDT replaced the line with the code from the referenced file:

Publish to Second SQL Server

This part is easy!

1. Right click the project SSDTDatabase
2. Click Publish…
3. Click Edit…
4. Select a different SQL Server, I’m selecting an empty database in Azure
5. Click OK

6. Click Save Profile As…
7. Since this SQL Server is in Azure, name the profile SSDTDatabase.Azure.publish.xml
8. The new new publish settings will appear in the Solution Explorer
9. Click Generate Script
10. Review the script
11. Execute the script (ctrl-shift-e)
12. Review results and the database in the new sql server

So after debugging, when the database project is ready for publishing, I can double click one of the .publish.xml files and make the changes on the target server.