CODEWITHSUNDEEP
sql-serversql-server-data-toolsdacpacbacpacsqlproj
bwerks
bwerks

Reputation: 9001

Export Bacpac from 2008 R2 fails on Azure validation rules

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.

Upvotes: 0

Views: 966

Answers (2)

Steven Green
Steven Green

Reputation: 3517

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.

Upvotes: 2

Ed Elliott
Ed Elliott

Reputation: 6856

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

Upvotes: 1

Related Questions