10

I have an existing database with everything in schema dbo. I have an SSDT project with objects I am adding to it with schema foo

I have a table that looks like this in the project:

CREATE table foo.a (
    id INT NOT NULL
        CONSTRAINT [PK_foo_a] PRIMARY KEY CLUSTERED
        CONSTRAINT [FK_foo_a] FOREIGN KEY REFERENCES [dbo].[a],
    desc NVARCHAR(50) NOT NULL
)

It depends on dbo.a. dbo.a has many columns that are foreign keys to other columns. Someone else (who maintains the default schema) might change dbo.a.

I'd like to simple store dbo.a as:

CREATE table dbo.a (
    id INT NOT NULL
        CONSTRAINT [PK_a] PRIMARY KEY CLUSTERED
)

So it gets built internally, but not deployed. Is that possible?

  • Do you not use Schema Compare for your deploys? You can simply uncheck that table from the list when changes are detected. – Dave Aug 13 '15 at 19:24
  • I'm doing a schema compare at the momnent, but no from my laptop to the dev server I'd like to do an actual publish deploy. – Justin Dearing Aug 13 '15 at 19:53
11

You can use the AgileSqlClub SqlPackage Deployment Filter.

Brief instructions reproduced from the original article by Ed Elliott:

  1. Download the filter from agilesqlclub.codeplex.com
  2. Put the DLL into the same folder as sqlpackage.exe
  3. Add these command line parameters to your deployment:

    /p:AdditionalDeploymentContributors=AgileSqlClub.DeploymentFilterContributor /p:AdditionalDeploymentContributorArguments="SqlPackageFilter=IgnoreSchema(BLAH)"

    This will neither deploy, drop or alter anything in the BLAH schema.

See the original article for complete details.

  • When I deploy from devenv.exe via the foo.publish.xml files it doesn't spawn sqlpackage.exe. Is there a way I can make it do that? – Justin Dearing Aug 13 '15 at 21:44
  • What I do is not ever use devenv to deploy changes, instead build the project and use a script to drive sqlpackage.exe - this way you can do stuff like use deployment contributors easily and also test the same script you can use in your other environments in your ci process! – Ed Elliott Aug 17 '15 at 12:03
  • This was a massive help for me, I needed to deploy only a particular schema,. Note for others in future, the ignoreschema method can take in regex IgnoreSchema(^(?!\b(?i)BLAH\b).*) to ignore everything but the BLAH schema – andyb952 Aug 20 at 8:08
3

Only managing part of a database with SSDT is difficult. If there were no dependencies then you could simply disallow it to drop objects and not include it in the project. Since there are dependencies, but you don't want to manage them, I think that you will have to trick the system.

One method comes to mind, I don't have the ability to test them right now and I have only used a "similar" method to this, not this one exactly.

Option #1:

  1. Create a new database project with dbo.a in it.

  2. Reference the table with 3-part naming. For the first part of the name, use a SQLCMD variable. E.g. [$(DatabaseName)].dbo.a.

  3. Never deploy your foo database.

  4. Via the publish files or cli interface, make $(DatabaseName) equal the same name as your real database.

  • Going to try this – Justin Dearing Aug 13 '15 at 18:20
  • @JustinDearing Use the deployment filter method, it is exactly fits your use case (I.e. Keep all tables in source control and just ignore some on deployment) – Ed Elliott Aug 13 '15 at 19:05
0

A "clean" built-in way to do this (as of 2016) is using a saved Schema Compare file. You can:

  1. Perform your Schema Compare (from your database project -> a target server) and uncheck any objects that you want to exclude for updating.
  2. Save your Schema Compare to a *.scmp file
  3. You can copy and paste the .scmp file to create versions for different environments if needed and simply edit in XML editor to change source (to point to build output dacpac file), connection, etc. or even manually add/modify items in the section.
  4. Configure your dacpac deployment/publish command parameters to use the appropriate .scmp file for the publish. e.g. SqlPackage.exe /Action:publish /SourceFile:c:\project\schema_compare.scmp

More information here: https://devblogs.microsoft.com/ssdt/schema-compare-in-sqlpackage-and-the-data-tier-application-framework-dacfx/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.