Aboutopenbve Data Publishing Studio

By: Nai Biao Zhou Updated: 2018-05-11 Comments (2) Related: More >Database Administration

SQL Server Data Tools (SSDT) SQL Projects offer excellent functionality to relieve developers of the mundane, manual tasks involved with maintaining databases. If your company licenses Visual Studio 2010 or 2012 Professional or greater, you have the ability to create Visual Studio SSDT SQL Projects. Discovering data contexts when trying to publish windows 10.0 visual studio 2017 version 15.4 Daniel Ruzza reported Nov 15, 2017 at 03:23 PM.


Problem

With a Microsoft Visual Studio Database Project, we can use version control softwareto manage changes to databases and we may face these problems:

  1. The database project deployment failed, and the error message said“The schema update is terminating because data loss might occur”.
  2. A database object, for example a table, was removed in the project,but the object was still in the target database after a successful deployment.
Solution

When publishing a database project to the target database server using MicrosoftVisual Studio, we can solve these problems through the “Advanced Publish Setting”window. When deploying the dacpac file by using command “SqlPackage.exe”,we can solve these two problems with adding specific parameters.

The solution was tested with Microsoft Visual Studio Community 2017 on Windows10 Home 10.0 <X64>. The DBMS is Microsoft SQL Server 2017 Enterprise EvaluationEdition (64-bit). The sample data was retrieved from theAdventureWorks sample databases.

Add NOT NULL Columns to a Table

We have a staging table “[dbo].[Stage_Special_Offer]” in the databaseproject “DWH_ETL_STORE”. The following screenshot presented the tablestructure. The table has some data.

We added a new column “Offer_Description” with data type “NCARCHAR (50) NOTNULL”. Then we published the project by using the database project publishingwizard. We received this error message:

We found this comment in the “DWH_ETL_Store.publish.sql”:

/* The column [dbo].[Stage_Special_Offer].[Offer_Description] on table [dbo].[Stage_Special_Offer]must be added, but the column has no default value and does not allow NULL values.If the table contains data, the ALTER script will not work. To avoid this issue,you must either: add a default value to the column, mark it as allowing NULLvalues, or enable the generation of smart-defaults as a deployment option. */

Some developers may add a default value to the column. I do not think this isa preferable solution for a table in a data warehouse. A not NULL column usuallywas needed on the basis of business requirements. Adding a default value seems tobypass the requirement unless a business requirement asks to do this. In addition,ETL developers could not find a data integrity error immediately if some bugs inthe ETL process added a NULL value to the not NULL column.

Publish Table Changes by Enabling Smart Defaults with GUI

A preferable solution for a data warehouse table is to enable the generationof smart defaults as a deployment option, and then the ETL process ensures the dataintegrity and validation.

Here is the process to publish a database project with the generation of smartdefaults as a deployment option enabled.

Step 1

Right click the project name in the “Solution Explorer” window andselect “Publish” from the pop-up menu. Configure the “Target Database Settings”as follows, then click on the Advanced button.

Step 2

On the Advanced Publish Settings window, check the “Generate smart defaults,when applicable” checkbox. Then click the “OK” button. This gets us back to the above screen where we can use the Save Profile Asif we want to save these settings for next time.

Step 3

Then click the “Publish” button.

The following screenshot shows the data in the table after the database projectwas published successfully. The new column “Offer_Description” was addedwith empty values.

Publish Table Changes by Enabling Smart Defaults using Command Line

If we want to do this from the command line, we can use this command line syntaxto deploy the project with the command “SqlPackage.exe”.

'C:Program Files (x86)Microsoft SQL Server140DACbinSqlPackage.exe'/Action:Publish /SourceFile:'DWH_ETL_STORE.dacpac' /TargetConnectionString:'DataSource=IDEA-PC;Integrated Security=True;Initial Catalog=DWH_ETL_STORE;'/p:GenerateSmartDefaults=True

This shows the project was deployed successfully with these confirmation messages.

Alter Not NULL Columns in a Table

Sometimes we might receive the same “data loss” error when we changea name of a not NULL column. This might not always happen, it depends on how wechange the column name. If we change the name in the “T-SQL” panel, the error occurs.

To publish the project successfully, we need to change the not NULL column namein the “Design” panel. Note that other versions of Microsoft Visual Studio may providea “Rename” menu item in the “Refactor”context menu.

When we changed the column name in the “Design” panel, a “refactorlog”file “DWH_ETL_STORE.refactorlog”, was generated with these XML elements:

With this “DWH_ETL_STORE.refactorlog”, the publish scripts used “sp_rename”to rename the column, thus no data loss occurred.

A system table “[dbo].[__RefactorLog]”was created in the target database to trace the database refactoring.

Aboutopenbve

Drop Objects In Target But Not In Source

We might find tables deleted from the database project are still in the targetdatabase. The database deployment could add new tables, but the deployment did notremove unused tables. We can solve this through the “Advanced Publish Settings”.

Check the checkbox “Drop objects in target but not in source”in the “Advanced Publish Settings” window as shown in the screenshot.This will remove objects that were deleted from the database project.

Here is the command line syntax to deploy the project with the command “SqlPackage.exe”.

'C:Program Files (x86)Microsoft SQL Server140DACbinSqlPackage.exe'/Action:Publish /SourceFile:'DWH_ETL_STORE.dacpac' /TargetConnectionString:'DataSource=IDEA-PC;Integrated Security=True;Initial Catalog=DWH_ETL_STORE;'/p:GenerateSmartDefaults=True /p:DropObjectsNotInSource=True /p:UnmodifiableObjectWarnings=False

References

[1] AdventureWorks sample databases.Retrieved March 2, 2018https://msdn.microsoft.com/en-us/library/ms124825(v=sql.100).aspx/.

[2] Walkthrough: Apply DatabaseRefactoring Techniques. Retrieved April 7, 2018https://msdn.microsoft.com/en-us/library/dd193272(v=vs.100).aspx/.

Next Steps
  • In practice, we usually use scripts to deploy a database project to targetservers automatically. This document aboutSqlPackage.exe is very helpful to construct the automation scripts. MicrosoftVisual Studio also provides a function to compare the schema between the databaseproject and the target database. We can use this function to ensure all of thenot NULL column name changes were captured in the “refactorlog”file. This file should be stored under the version control system.
  • Check out these related tips:

Last Updated: 2018-05-11



About the author
Nai Biao Zhou is a Senior Software Developer with 20+ years of experience in software development, specializing in Data Warehousing, Business Intelligence, Data Mining and solution architecture design.
View all my tips




Here is the information on how to Disable Meta Data Publishing while WCF hosting.

Metadata publishing for this service is currently disabled.

When you see this error message on the client machine web browser, it means the web.config file is not configured to expose the meta data information.

If you want to simulate this error, then you have to do the following two things:

  1. Remove the endpoint configuration for the contract IMetadataExchange
  1. Set <serviceMetadatahttpGetEnabled='false'/> under behaviorsserviceBehaviorsbehavior section

This is a Windows© Communication Foundation service.
Metadata publishing for this service is currently disabled.
If you have access to the service, you can enable metadata publishing by completing the following steps to modify your web or application configuration file:
1. Create the following service behavior configuration, or add the <serviceMetadata> element to an existing service behavior configuration:

<behaviors>

<serviceBehaviors>

<behavior name='MyServiceTypeBehaviors' >

<serviceMetadata httpGetEnabled='true' />

</behavior>

</serviceBehaviors>

</behaviors>

2. Add the behavior configuration to the service:

<service name='MyNamespace.MyServiceType' behaviorConfiguration='MyServiceTypeBehaviors' >

Note: the service name must match the configuration name for the service implementation.
3. Add the following endpoint to your service configuration:

<endpoint contract='IMetadataExchange' binding='mexHttpBinding' address='mex' />

Note: your service must have an http base address to add this endpoint.
The following is an example service configuration file with metadata publishing enabled:

<configuration>

<system.serviceModel>

<services>

<!-- Note: the service name must match the configuration name for the service implementation. -->

<service name='MyNamespace.MyServiceType' behaviorConfiguration='MyServiceTypeBehaviors' >

<!-- Add the following endpoint. -->

<!-- Note: your service must have an http base address to add this endpoint. -->

<endpoint contract='IMetadataExchange' binding='mexHttpBinding' address='mex' />

</service>

</services>

<behaviors>

<serviceBehaviors>

<behavior name='MyServiceTypeBehaviors' >

<!-- Add the following element to your service behavior configuration. -->

<serviceMetadata httpGetEnabled='true' />

</behavior>

</serviceBehaviors>

</behaviors>

</system.serviceModel>

</configuration>

For more information on publishing metadata please see the following documentation: http://go.microsoft.com/fwlink/?LinkId=65455.

A sample web.config that does not expose the meta data is given below:

<system.serviceModel>

<services>

<servicename='Wcf_Test_Service.TestService'behaviorConfiguration='MyServiceTypeBehaviors'>

<endpointaddress='

binding='basicHttpBinding'

contract='Wcf_Test_Service.ITestService' >

</endpoint>

</service>

</services>

<behaviors>

<serviceBehaviors>

<behaviorname='MyServiceTypeBehaviors'>

<serviceMetadatahttpGetEnabled='false'/>

<serviceDebugincludeExceptionDetailInFaults='true'/>

Aboutopenbve Data Publishing Studio Design

</behavior>

</serviceBehaviors>

</behaviors>

<serviceHostingEnvironmentmultipleSiteBindingsEnabled='true'>

Aboutopenbve Data Publishing Studio Tutorial

<serviceActivations>

<addrelativeAddress='TestService.svc'service='Wcf_Test_Service.TestService'/>

</serviceActivations>

</serviceHostingEnvironment>

</system.serviceModel>