Forums (Obsolete)

Member

fraser - 8/26/2013 2:55:08 PM

Working with BACPAC on Azure

Hi all,

We are hosting a bunch of sites on an Azure Virtual Machine. We are using SQL Azure as our database.

The sites work just fine and we back up our database to the BACPAC format.

When I try to restore a BACPAC to my local SQL Server 2012 (SP1), I get an error when it's trying to restore the data to the CMS_Tree table:

TITLE: Microsoft SQL Server Management Studio
------------------------------

Data plan execution failed with message One or more errors occurred. (Microsoft.SqlServer.Dac)

------------------------------
ADDITIONAL INFORMATION:

One or more errors occurred. (mscorlib)

------------------------------

A severe error occurred on the current command. The results, if any, should be discarded. (Microsoft SQL Server, Error: 0)

Has anyone encountered this error? or know of a way to deal with BACPACs successfully?


Kentico Support	kentico_jurajo - 8/27/2013 7:11:54 AM RE:Working with BACPAC on Azure Hello, I do not see any connection to Kentico. Please, submit posts related to Kentico usage only. Anyway, I did a quick Google and found these two articles, have you tried following them? article 1 Stackoverflow article Best regards, Juraj Ondrus


Member	fraser - 8/27/2013 8:30:32 AM RE:Working with BACPAC on Azure Hi, sorry, I should have mentioned that I use this exact same procedure with non-Kentico databases and I do not have any issues. It only happens when I try to restore a Kentico database.


Kentico Support	kentico_jurajo - 8/28/2013 1:49:13 AM RE:Working with BACPAC on Azure Hi, This is strange. The DB is the same as any other, we are not doing anything special. Are there any more details about the error? Have you tried following the suggestions in the articles? Is the bacpac file OK? Best regards, Juraj Ondrus

Member

fraser - 8/29/2013 9:05:34 AM

RE:Working with BACPAC on Azure

I looked and could not find any more detail on the error. I have tried with multiple different Kentico databases and it always gets stuck on CMS_Tree table.

For the restoration, I have tried both the SQL Management Studio UI as well as the command prompt and neither work.

I could send you a copy of the BACPAC if that would help.

If using BACPAC will not work with Kentico, can you suggest some other method we can use to back-up our SQL Azure databases??

Thanks!

-Fraser

Kentico Support

kentico_filipl - 9/5/2013 5:46:13 AM

RE:Working with BACPAC on Azure

Hi Fraser,

Could you try to export and import your database and follow steps mentioned in the official Azure documentation - How to: Import and Export a Database (Windows Azure SQL Database). I tried it and everything went smoothly from exporting the database of my Kentico CMS project deployed on Azure to importing it back. There was no error and it did not get stuck during this process.

Please let me know about the outcome of this operation.

Best regards,
Filip Ligac


Certified Developer 9	Jim Garwacki - 9/11/2013 3:38:03 PM RE:Working with BACPAC on Azure I'm having the same exact problem. Importing an Azure bacpac backup file into local SQL (tried 2008 R2 and 2012) and it fails in the same place CMS_Tree. It sits on CMS_Tree for a long time and eventually fails. Using Kentico 7.0.52. Let me know if you need any more information from me. Thanks, Jim Garwacki

Kentico Support

kentico_filipl - 9/12/2013 6:01:21 AM

RE:Working with BACPAC on Azure

Hi Jim,

Does it throw any error with more details for CMS_Tree database table after it fails? When I tried to import a bacpac database backup from my Azure Kentico project using SQL Management Studio 2012, it was finished successfully without any errors as you can see in the screenshot. I used the following guide - Windows Azure Storage - BACPAC Import database - Using SQL server (2012) Management Studio. Could you try it and let me know about the outcome?

Screenshot

Best regards,
Filip Ligac

Member

thanos.panousis-indice - 9/18/2013 6:08:19 AM

RE:Working with BACPAC on Azure

I am experiencing the same problems with importing a bacpac from azure.

The error I am getting for the CMS_tree table is

Program Location:

at Microsoft.SqlServer.Dac.Deployment.PlanExecutor.Execute(DeploymentPlan plan, SqlConnectionStringBuilder connectionString, Boolean isAzureTarget, LoggingContext loggingContext, CancellationToken cancelToken)
at Microsoft.SqlServer.Dac.DeployOperation.UpdateDatabaseData(LoggingContext loggingContext, CancellationToken token)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<>c__DisplayClass12.<CreatePlanExecutionOperation>b__f()
at Microsoft.Data.Tools.Schema.Sql.Dac.OperationLogger.Capture(Action action)
at Microsoft.SqlServer.Dac.DeployOperation.<>c__DisplayClass10.<CreatePlanExecutionOperation>b__e(Object operation, CancellationToken token)
at Microsoft.SqlServer.Dac.Operation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.ReportMessageOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.CompositeOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.DeployOperation.Microsoft.SqlServer.Dac.IOperation.Run(OperationContext context)
at Microsoft.SqlServer.Dac.OperationExtension.Execute(IOperation operation, DacLoggingContext loggingContext, CancellationToken cancellationToken)
at Microsoft.SqlServer.Dac.DacServices.InternalDeploy(IPackageSource packageSource, Boolean isDacpac, String targetDatabaseName, DacAzureDatabaseSpecification creationDefaults, DacDeployOptions options, CancellationToken cancellationToken, DacLoggingContext loggingContext)
at Microsoft.SqlServer.Dac.DacServices.ImportBacpac(BacPackage package, String targetDatabaseName, DacAzureDatabaseSpecification creationDefaults, Nullable`1 cancellationToken)
at Microsoft.SqlServer.Management.Dac.DacWizard.ImportDatabase.DoWork()
at Microsoft.SqlServer.Management.TaskForms.SimpleWorkItem.Run()


Kentico Support	kentico_filipl - 9/18/2013 8:15:52 AM RE:Working with BACPAC on Azure Hi Thanos, Would it be possible to send us an e-mail to support@kentico.com with the bacpac file attached? I still have not encountered such an issue while exporting/importing database. Best regards, Filip Ligac


Member	Pete - 9/19/2013 9:04:13 PM RE:Working with BACPAC on Azure I have the same exact issue here with the same exact error message. BACPAC import fails. Kentico 7.0.48.


Kentico Support	kentico_filipl - 9/20/2013 5:54:14 AM RE:Working with BACPAC on Azure Hello Pete, You can also send us an e-mail to support@kentico.com and attach your bacpac file. We have not received any bacpacs so far so we cannot find the cause of the issue since I have never come across such an issue before. Best regards, Filip Ligac


Member	Pete - 10/3/2013 10:19:38 PM RE:Working with BACPAC on Azure Is there any update on this? I believe I had gotten you much more information via email and verified that there is some issue with some specific version(s) of the Microsoft Tool Chain (SQL Server Management Studio) and importing the Kentico database from a BACPAC.

Certified Developer 9

Jim Garwacki - 10/3/2013 10:39:44 PM

RE:Working with BACPAC on Azure

Pete,

I sent Filip a copy of my BACPAC file that was causing issues and they were able to duplicate the problem. He informed me that they were in contact with the Microsoft Azure group to see if it was a known bug or not.

We've since abandoned Azure SQL and went with an Azure SQL VM, because we weren't seeing the performance we needed for our site. We may look at it again for other projects in the future.

Thanks,

Jim Garwacki


Certified Developer 9	Jim Garwacki - 9/18/2013 10:14:12 AM RE:Working with BACPAC on Azure The only error that I saw was the same one mentioned in the first post. I can provide a bacpac file if you would like. We ultimately (for performance reasons) gave up on Azure SQL and web roles. We'll probably stay with Azure, but move to a VM instead. Thanks, Jim Garwacki


Kentico Support	kentico_filipl - 9/20/2013 1:32:10 AM RE:Working with BACPAC on Azure Hello Jim, It would be probably best if you could send us the bacpac file so that we could reproduce the issue. Thank you. Best regards, Filip Ligac


Member	markcoatsworth - 10/8/2013 11:10:17 AM RE:Working with BACPAC on Azure I'm experiencing the exact same problem with Kentico BACPAC files from SQL Azure. Has there been any progress on this? This is a significant problem because now we cannot get our data out of SQL Azure -- would appreciate any updates on this! Mark

Kentico Support

kentico_filipl - 10/10/2013 6:43:46 AM

RE:Working with BACPAC on Azure

Hi Mark,

Currently we are trying to solve this issue with Windows Azure support directly since we were finally able to reproduce the behaviour on one of our databases. Right now we are waiting for their statement to find out what we can do to to resolve or eliminate it.

I will update this forum thread once I get more information from them.

Best regards,
Filip Ligac

Member

markcoatsworth - 11/4/2013 1:53:54 PM

RE:Working with BACPAC on Azure

Hi Filip,

Thanks for following up. I hate to bother you, but has there been any progress in the last few weeks?

We've run into a bad situation where a client needs to upgrade their site from Kentico 5.5R2 to Kentico 7. Unfortunately the upgrade utility does not work with SQL Azure -- it throws lots of "not supported in this version of SQL Server" errors. However we also can't get our data out of SQL Azure.

So we're basically stuck until this problem gets resolved.

If you have any updates we would appreciate them! Thanks,

Mark

Kentico Support

kentico_filipl - 11/7/2013 2:31:29 AM

RE:Working with BACPAC on Azure

Hi Mark,

Unfortunately we are still waiting for a possible fix from Azure support. They have not given us a final announcement how we could resolve this issue. In their last update they have just informed us the case had been escalated to their product group which was a week ago.

I am continuosly trying to get some more information from their side but so far I had not received any approximate date for releasing a fix for the issue.

Best regards,
Filip Ligac

Kentico Support

kentico_filipl - 11/8/2013 1:45:01 AM

RE:Working with BACPAC on Azure

I have just received a confirmation that a fix will be a part of SQL Server 2012 Service Pack 1 Cumulative Update 8 which is scheduled for release on 20/01/2014.

However, I understand it is a really long time to wait for a fix so I have also asked them for a temporary fix or a workaround for this issue.

Best regards,
Filip Ligac


Member	markcoatsworth - 11/11/2013 11:46:20 AM RE:Working with BACPAC on Azure Hi Filip, Thanks for following up! Appreciate your help with this. If you do get any information about a fix or workaround, please let us know. Otherwise I guess we'll wait for the cumulative update. January is indeed a long time away, but at least it's good to know they're working on it. Thanks again, Mark

Kentico Support

kentico_filipl - 11/12/2013 2:18:34 AM

RE:Working with BACPAC on Azure

Hi Mark,

Yesterday I received an update from Azure support about a fix which should be released till the end of the month which is great news. Of course the date of the fix release cannot be guaranteed because of testing which needs to be done and could also prolong the release date. However, I hope they will be able to provide us with a solution long before Service Pack is released.

Best regards,
Filip Ligac

Kentico Support

kentico_filipl - 12/5/2013 3:15:11 AM

RE:Working with BACPAC on Azure

Hi everyone,

I am glad to inform all of you that we finally received a working fix from Microsoft which enables you to import bacpac to your local SQL server. If I forget to send the fix to some of you who have experienced problems with import, please send us an e-mail to support@kentico.com to get it.

Please note that the fix is not fully tested yet. Official fix will be released in SQL Server 2012 Service Pack 1 Cumulative Update 8 which will be released on 20/01/2014.

P.S.: After installing the fix, you also need to modify the registry record (regedit.exe) HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\SQLDB\Database\QueryTimeoutSeconds (11.0 as well) and set the value from default 60 seconds to 0. The fix is working only for SQL server versions 11 and higher.

// Update: SQL Server 2012 Service Pack 1 Cumulative Update #11 can be downloaded from here - https://support.microsoft.com/en-us/kb/2908007

Best regards,
Filip Ligac

Member

markcoatsworth - 1/8/2014 4:10:40 PM

RE:Working with BACPAC on Azure

Hi Filip, thanks so much, this is great news!

I've been busy on another project but finally got a chance to try the fix and import my SQL Azure bacpac files. Everything worked properly. Had a few problems early on, but once I upgraded to SQL Server 2012 SP1 Cumulative Update 7 the fix worked great.

Thanks again for all your help with this, is very much appreciated.

Mark