Udostępnij za pośrednictwem

New Advanced Publish Options to Specify Object Types to Exclude or Not Drop

  • Artykuł

Our team has been hard at work these past few months, and we're thrilled to announce that the most frequently requested improvement to SQL Server Data Tools is here. We plan to release an update of SQL Server Data Tools in the coming weeks, and in that update you'll find new options that provide greater flexibility and control over database publishing.

We've re-organized the Advanced Publish Settings dialog and added new options on the Drop and Ignore tabs. On the Drop tab, selecting Drop objects in target but not in source now enables additional options for selecting object types, like Users, that will not be dropped. The need for this frequently arises with TSQL user objects, as they are often jointly managed by the developer and the database administrator. Until now, selecting Drop objects in target but not in source always caused any target-only user objects to be dropped during publish, but the new Do not drop users option leaves the choice up to you.

Do not drop users can be combined with do not drop permissions and do not drop role membership to ensure that settings for user objects that exist only in the target database are not modified.

Note that the new "do not drop" options are just concerned with target-only objects. Selecting Do not drop users, for example, will not prevent users that are defined in your database project from being created or altered – it only prevents users that aren't defined from being dropped. Sometimes, though, you just want a certain object type to be completely ignored when publishing a database, so in addition to the "do not drop" options, we've added options for excluding object types from publish.

On the Ignore tab, the new options in the Excluded Object Types section allow you to choose to prevent certain object types from being published. Excluded object types aren't evaluated during publish and new, modified, or deleted objects of an excluded type aren't created, changed or dropped in the target database. (Note, though, that it is sometimes necessary to modify an excluded object when one of its dependencies has been changed or removed.)

We've also modified SqlPackage.exe and the Data-Tier Application Framework (DacFX) to accept these new options.

We'd like to thank the user community for the clear and consistent feedback you have provided about the importance of these options, and we encourage you to tell us what we should be doing next by:

  • Submitting feedback and voting at Connect
  • Posting on our Forum
  • Submitting a comment below

Comments

  • Anonymous
    March 04, 2015
    The comment has been removed

  • Anonymous
    March 04, 2015
    The comment has been removed

  • Anonymous
    March 11, 2015
    Does the change to publish options mean the old ones are deprecated? I'm hesitant to upgrade my local install because I don't want to change the existing publish profiles into something the version used by our servers can't handle. What will happen if you have an option that isn't recognized by a lower version of SSDT?

  • Anonymous
    March 16, 2015
    Hi Peter, To take a concrete example, if you enable the option Do Not Drop Users in your publish profile, then save that profile and use it with an older version of SSDT or SqlPackage, the older version will simply ignore the Do Not Drop Users setting. It should be safe to install locally and start testing out the new feature.

    • Anonymous
      September 15, 2016
      its not working with older (12.0) sqlpackage.exe.

  • Anonymous
    March 16, 2015
    I am facing another issue after updating this SSDT. Now my generated publish script is creating script for table schema which is identical in source and destination. More specific, it is dropping existing constraints like Defaults and Indexes, creating new table with prefix tmp_ms, copying data to new table created, dropping the existing table and finally renaming the new table with the old name. Previously I notices this with tables having schema changes in source  and destination. Because of this, it is taking too much time to deploy my changes to server as it is touching all tables in the database. Is there any option i need to manually select to handling this behavior. Thanks, Gigil K G

  • Anonymous
    March 17, 2015
    The comment has been removed

  • Anonymous
    March 17, 2015
    Steven, Thanks. This is what I wanted to be sure about. I'd hate to mess up the publish process if somehow we got new options put in place that our deploy servers weren't able to handle.

  • Anonymous
    March 26, 2015
    Kudos team!  This solved most of what we created a custom DeploymentPlanModifier to do except schema drops.  I don't see that option and still see the DROP SCHEMA statements in the generated script for users created outside of the DB project by DBAs.  Am I missing an option or is this not available yet?

  • Anonymous
    March 26, 2015
    Do you know when the MSDN page will be updated?

  • Anonymous
    March 26, 2015
    There is a bug in this feature.  While it is no longer dropping users, or the roles that they are members of, it is dropping their permissions (i.e. CONNECT).

  • Anonymous
    March 27, 2015
    Hi William, Permissions are considered to be a separate object type from the users, so the do not drop permissions setting controls whether permissions are dropped. If you're finding that the do not drop permissions setting is not working in your case, could you open a new Connect issue so that we can track the problem and keep you updated when we resolve it? Thanks!

  • Anonymous
    March 27, 2015
    For the love all that is holy can we PLEASE get IgnoreColumnOrder back into SSDT? You're missing out on a ton of adoption with people who have legacy databases that have been around for years (10+) with multiple installs and column orders different in all of them depending on when initially stood up. To be able to use the DAC paradigm we need to transition, sometimes slowly, starting off with tools like sqlpackage and using it for schema deltas. I much rather would be able to use a sqlproj natively than redgate. Where do we make requests that get listened to? Should it be on the SQL connect site? There are quite a few threads on this issue and none seem to be listened to. Here's the most "active" one with plenty of upvotes: social.msdn.microsoft.com/.../schema-comparisons-ignore-column-order

  • Anonymous
    March 27, 2015
    Mark - Thanks! Unfortunately we were not able to address schemas in this release. Chris - The request for an update to the MSDN documentation is in progress. My apologies -- it's taking a bit longer than we anticipated. Rich - Thank you for the feedback. I agree with your sentiment. I'm not able to provide forward-looking guidance regarding significant features, but I can assure you that this feedback has been heard. As for providing feedback, raising issues and/or voting at Connect is a good way to give us feedback.

    • Anonymous
      June 07, 2017
      @Steven: Has schemas been addressed yet? When I create a publish script I still see that it tries to drop schema if it is not in the project, even if tables are not being dropped, and the schema it tries to drop has tables in it.Will this be fixed?

  • Anonymous
    March 29, 2015
    The comment has been removed

  • Anonymous
    March 30, 2015
    The comment has been removed

  • Anonymous
    March 30, 2015
    Very nice improvements, thanks! Keep up the good work :)

  • Anonymous
    April 01, 2015
    Great Improvements. One request I would like to see and I am not sure if this is entirely possible. I am trying to source control DDL triggers (not Server Triggers or Database Triggers).  I would like to see the ability to have the sqlpackage.exe program have an option to exclude Tables but allow delta changes on triggers. As of now, it seems its an all or nothing thing for Tables.

  • Anonymous
    April 07, 2015
    Glad to see this, correlates 95% with the SchemaCompare options, and is roughly twice as fast when compared to SchemaCompare invoked from msbuild (not to mention the console spam when using msbuild). I am unfortunately missing one of the omitted options: "Database Options, Permissions and Extended Properties". This causes a lot of CONNECT permission grant/revokes to appear in generated scripts for database users, effectively negating usefulness of /p:ExcludeUserType=Users when permission comparison is enabled.

  • Anonymous
    April 15, 2015
    The comment has been removed

  • Anonymous
    April 22, 2015
    Is anyone else having issues with these new settings? I have ExcludePartitionSchemes and IgnorePartitionSchemes set to True, and it is still scripting the creation of the partition scheme. I thought maybe it was because a table is referencing it (even though "Ignore object placement on partition scheme" is also set to true), but even if I comment out the partition scheme placement in the table definition, it still scripts out the new partition scheme on publish. What gives? Note: I am doing this because I have a sliding window partition scenario and do not want to maintain changing filegroups/schemes/functions in the project. So I need to set everything up dynamically via pre-deployment script and do not want the project to create any partitioning objects, but would still like to have them as part of the project to show dependencies. For now, I guess I will need to remove them.

  • Anonymous
    April 24, 2015
    One thing that I noted and someone else may be experiencing this problem, when you install the new version of SQL Server Data Tools and Data-Tier Application Framework Update for February 2015 My scripts were pointing to this location C:Program Files (x86)Microsoft SQL Server110DACbin This version does NOT support the ExcludeObjectTypes parameter, nor the new exclude parameters such as ExcludeLogins I had to update my files to point here C:Program Files (x86)Microsoft SQL Server120DACbin

  • Anonymous
    April 27, 2015
    After updating SSDT I'm having problems with SCRIPT action when using "DoNotDropObjectType" argument. Here is original command: sqlpackage /Action:Script /OutputPath:"dac/UpdateScripts/OdysseyCompare.sql" /SourceFile:"dac/source/OdysseySuite.dacpac" /TargetFile:"dac/target/OdysseySuite.dacpac" /TargetDatabaseName:"Odyssey" /v:OdysseyMarket=OdysseyMarket /v:ServiceLog=ServiceLog /p:AllowIncompatiblePlatform=True /p:BlockOnPossibleDataLoss=False /p:BlockWhenDriftDetected=False /p:IgnoreNotForReplication=True /p:IgnorePermissions=True /p:IgnoreRoleMembership=True /p:GenerateSmartDefaults=True /p:DoNotAlterReplicatedObjects=False /p:DropObjectsNotInSource=True /p:DoNotDropObjectType=Users Error message I get: *** 'DoNotDropObjectType' is not a valid argument for the 'Script' action.

  • Anonymous
    April 29, 2015
    @Steven: "Permissions are considered to be a separate object type from the users..." Here's the issue.  We don't want anything touched on users that are not defined in the project.  Whereas users that are defined, we want them to be fully managed by the project.  By selecting the "Do not drop permissions" setting, we completely lose this management for defined users, which is not what we want.  There should be overlap where if the user is not being dropped, then neither should anything attached to that user be dropped.  Otherwise, this option is pretty useless.

  • Anonymous
    April 29, 2015
    The CONNECT permission should not be dropped connect.microsoft.com/.../1290459

  • Anonymous
    April 30, 2015
    @William you seem to get the permissions logic, maybe you can answer this: My DB Solution defines 1 user, I'd like that user to be added/modified by SSDT but I'd like SSDT to leave any other users and permissions on the DB untouched.  I need to allow clients to add users and permissions on top of ours. Or, is it perhaps possible to add and update but not drop users and permissions.  I know there's an option to prevent dropping on all object types but that's too broad. thanks, Martin.

  • Anonymous
    May 01, 2015
    The comment has been removed

  • Anonymous
    May 01, 2015
    Disregard above comment. I found that the sqlpackage.exe location I needed to be using was here: C:Program Files (x86)Microsoft Visual Studio 12.0Common7IDEExtensionsMicrosoftSQLDBDAC120

  • Anonymous
    May 11, 2015
    What is the purpose of the Advanced Deployment Settings on the Debug tab in Project Settings? I had hoped that it would act as a default settings for all future publishes, something we would desperately like to see, but the options set there do not seem to accomplish anything useful.

  • Anonymous
    May 11, 2015
    Charles - The settings in the project properties Debug tab are used when a database project is deployed using F5 (Debug).

  • Anonymous
    May 12, 2015
    For below error make sure you are using right version of SQLPackage.exe.. Error message: *** 'DoNotDropObjectTypes' is not a valid argument for the 'Script' action. Grab it from C:Program Files (x86)Microsoft Visual Studio 12.0Common7IDEExtensionsMicrosoftSQLDBDAC120 Instead of C:Program Files (x86)Microsoft SQL Server120DACbin(This is not updated for some reason)

  • Anonymous
    May 13, 2015
    @Martin H.: Unfortunately, as I stated to Steven Green, these changes/extensions are pretty useless in their current state.  What I ended up doing was check the boxes to ignore undefined users and roles that they may be in, to prevent it from deleting users that are not defined in the project.  Unfortunately, the system as-it-is still removes the permissions for users, so I had to create a rather large Post-Deployment script with hard coded information for all of the users that a server/workstation may be using.  Until they have the foresight to fix their blunder, there is no other alternative.

  • Anonymous
    May 19, 2015
    @William:  Is completely ignoring all users and permissions an option?  We might be able to handle our users/permissions manually, once setup they don't usually need updating.  thanks for the earlier reply.

  • Anonymous
    May 20, 2015
    We currently deploy the DacFramework.msi to the target server as a deployment pre-requisite, such that we know SqlPackage.exe is there, and then our deployment scripts call out to it. However when installing via DacFramework.msi - it installs SqlPackage.exe here: C:Program Files (x86)Microsoft SQL Server120DACbin This is an older release it seems, than the version installed through VS here: C:Program Files (x86)Microsoft Visual Studio 12.0Common7IDEExtensionsMicrosoftSQLDBDAC120 Can we expect an updated DacFramework.msi please? We need SqlPackage.exe to be in a distributable form so that it can easily be deployed to target servers for automated deployment scenarios..

  • Anonymous
    June 16, 2015
    Hi, We are using a Sql Server project and loving the experience all round. However we have found an issue that is making it impossible for us to keep using this solution. We deploy tables, stored procedures and various other items, which we and our clients consider a starting point. We support them adding in their own columns which is then used at a later stage. User being able to add their own columns is a mission critical step in our operations. However after trying all settings, when we redeploy a new dac, all user defined table columns are dropped. This is a real world scenario and surely there must be some way how we can achieve this. Please let me know if anything comes to mind.

  • Anonymous
    July 06, 2015
    The comment has been removed

  • Anonymous
    July 27, 2015
    I am unable to find equivalent setting in the Microsoft.SqlServer.Dac,DacDeployOptions.  Am I just missing them?

  • Anonymous
    July 27, 2015
    Sorry should have included what setting I'm able to find.  In the UI's Advanced Publish Settings Ignore tab in the Excluded Object Types section. "Exclude Database roles", "Exclude Logins", "Exclude Permissions", "Exclude role membership", "Exclude users" I am unable to find equivalent setting in the Microsoft.SqlServer.Dac,DacDeployOptions.  

  • Anonymous
    July 29, 2015
    I'm absolutely wondering the same thing about getting a new version of the DAC framework that can be deployed to a machine that does not have Visual Studio on it.  This has already been asked but not answered.  In setting up our integration testing SQL server and production SQL Server boxes this is the missing link for continuous integration and continuous deployment.  Does anyone have a link to a new install that we can use to get the latest sqlpackage on a non visual studio machine?

  • Anonymous
    August 04, 2015
    How would you go about ignoring certain schemas? My database is being used by me and other groups we segregate responsibilities via schema. I dont want to drop any of their objects but i do want to drop objects in mine.

  • Anonymous
    November 18, 2015
    The comment has been removed

  • Anonymous
    November 23, 2015
    Still no ignore column order after all these times?  I'm not sure who is in charge up there, but you do understand that the tools is pretty much useless without it in any reasonable scale project, regardless of how many other "features" you piled on.  Right?

  • Anonymous
    November 25, 2015
    Question 1:  If / when will SQL Server Management Studio's "Upgrade Data-Tier Application" wizard support ALL (or any) of the options available using SqlPackage.exe, or Visual Studio Publish SSDT utilities.  This has been neglected for some time now.  Why are there disconnects between functionality across all areas which support DACPAC?   Question 2: Is there any way to deploy a DACPAC using C# code?  Other than calling SqlPackage.exe Thanks.

  • Anonymous
    February 05, 2016
    The comment has been removed

  • Anonymous
    April 21, 2016
    Can i call a Advanced Publish Settings dialog from outside Visual Studio... like a portable app?

  • Anonymous
    May 03, 2016
    Hey guys, for those trying to use this option with DacServices.Deploy() - you can get to it through reflection on DacDeployOptions:FieldInfo field = typeof(DacDeployOptions).GetField("_options", BindingFlags.Instance | BindingFlags.NonPublic);// Microsoft.Data.Tools.Schema.Sql.Deployment.SqlDeploymentOptionsobject advancedOptions = field.GetValue(this.options);Type advancedOptionsType = advancedOptions.GetType();MethodInfo method = advancedOptionsType.GetMethod("AddExcludedObjectTypeForSchemaCompare");// Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable - anything that inherits from ModelElementType sqlTableType = advancedOptionsType.Assembly.GetType("Microsoft.Data.Tools.Schema.Sql.SchemaModel.SqlTable");method.Invoke(advancedOptions, new object[] { sqlTableType });

  • Anonymous
    June 16, 2016
    Still if I define these in Debug tab in VS project, and choose not to drop e.g., users, and then upgrade .dacpac using SSMS, it drops the users.

    • Anonymous
      September 26, 2016
      Hey Jussi,did you end up resolving this? I am experiencing the same issue.

  • Anonymous
    August 08, 2016
    Wow! It is most important in my life because it is useful in my life without This thing is my life is boaring , But our site is this technique easily way fulfill.

  • Anonymous
    January 19, 2017
    The comment has been removed

  • Anonymous
    August 23, 2017
    What if we want to ignore publishing a specific object i.e a specific table may be.. Is it possible? I don't want to exclude all the objects of a specific type.Thanks,Shilpa

    • Anonymous
      November 06, 2017
      Hi Shilpa, to do this you can use Schema Compare (do a comparison, filter your object and save the result to a .scmp file). This is now supported for use through SqlPackage too. There are also community extensions to filter by schema object name - see https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Filter-Dacpac-Deployments for an example