0

I'm exploring the SSDT .NET class offering in Microsoft.SqlServer.Dac.dll using Powershell, and I'm trying to work a database into a source-control-based SDLC as a proof of concept. The first thing I tried was to export a bacpac so that we could propagate the database into our test environments, and eventually production.

So far I've had no trouble exporting a dacpac using DacServices.Extract. However, when I attempt to export the same database using DacServices.ExportBacpac, a huge amount of errors are reported, similar to:

Error validating element [dbo].[GetNewCustomers]: Deprecated feature 'Table hint without WITH' is not supported on SQL Azure.

I'm aware of what the error is reporting (using (NOLOCK) hints instead of WITH (NOLOCK)). However, I've got the Sql Server 2012 toolset installed on my workstation and the database I'm working with is Sql Server 2008 R2, so I'm not concerned with Azure's support of that syntax.

  • Is there a way to tell the Dac framework that I'm not using Azure when I extract a bacpac?
  • Am I going about this the wrong way? Should I be trying to work the data into scripts housed in my sqlproj instead?
bwerks
  • 8,651
  • 14
  • 68
  • 100

2 Answers2

2

Bacpac files are intended for use in Azure SQL DB migration/archiving scenarios, so the export mechanism always enforces the requirement that the contents of a bacpac file can be imported into an Azure SQL Database. To avoid this constraint, you can extract a dacpac file with all table data, which behaves very similarly to a bacpac file.

Alternately, the latest versions of DACFx support the Azure SQL Database V12 surface area, which is significantly expanded relative to the previous version. You'll find that the specific error you're encountering no longer prevents export from completing, because the expanded surface area of Azure SQL Database supports that syntax.

Steven Green
  • 3,387
  • 14
  • 17
  • This differentiation between bacpacs and dacpacs is helpful, thanks! Unfortunately my organization is still using Sql Server 2012 tooling so using the latest DacFx stuff is (I believe) off the table. – bwerks Jan 13 '16 at 23:11
1

What are you trying to achieve? If you want to use SSDT the best way to get started would be to create a new project and import from your database (or restore a backup somewhere and import from that)

This should get you started:

https://the.agilesql.club/Blogs/Ed-Elliott/HOWTO-Get-An-Existing-Schema-Into-SSDT

And

https://the.agilesql.club/taxonomy/term/34

You do have some control over the version that is used but it is only whether it is azure version 11 or 12.

Ed

Ed Elliott
  • 6,666
  • 17
  • 32
  • Your blog is invaluable! Capturing a legacy database and bringing it under source control exactly the goal I'm trying to achieve, and this addresses it directly and in detail. This series of posts deserves to be placed alongside the MSDN documentation. – bwerks Jan 13 '16 at 20:49
  • Thanks feel free to ping me if you get stuck, my contact details are on the about me page :) – Ed Elliott Jan 13 '16 at 21:47