Tuesday, October 1, 2013

Deploy DACPAC in SQLServer2008 R2 using VSTS2012

I recently hit a problem where we were trying to build DACPAC using VSTS2012, to be deployed in SQL Server 2008 R2.I found this worth sharing.To deploy DAC following approach can be leveraged 

DAC Depolyment Approach

1. Using SSMS
2. Using SqlPackage

Please note the DACPAC referred here is built using VSTS 2012.

Using SSMS


When trying to import DACPAC using SSMS of 2008 R2 it refused to run throwing below serialization exception.



Quite anticipated as the DACPAC was built using new version of DAC version.

So workaround is to connect to SQL Server 2008 R2 using SSMS from SQL Server 2012 and import the data tier application. It will work perfectly as below snapshot says-



Using SQLPackage.exe MSDN

I tried to follow the steps from http://technet.microsoft.com/en-us/library/ee210569%28v=sql.105%29.aspx but it didn't worked for me. Though I found out that it worked with VSTS 2010 DACPAC package.I decided to switch to SqlDeploy. You can find SqlDeploy.exe @ C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\  location, if Sql Server 2012 is deployed. 


Workaround found

  1. I found the link http://sqlblog.com/blogs/jamie_thomson/archive/2012/05/30/redistribution-of-sqlpackage-exe-ssdt.aspx, Which provided me option to install 4-5 msi  in  the deployment machine but that again requires quite an effort especially when this need to deployed in multiple environments.
  2. Download ISO from http://download.microsoft.com/download/3/4/5/3458A46A-5593-40B2-BA56-81A713D21B81/SSDT_11.1.30914.0_EN.iso and deploy in the machine which handles deployment. Same reason wastage of effort for small tool.My problem is we had all the database instances running in SQL Server 2008 R2 and installing SSDT tool  though free was a major amount of effort in all the environments dev, test, integration, pre-production,etc.

    Since above two workaround didnt worked for me, I decided to find one myself and came up with one.

Wrap SqlDeploy Utility in deployment package


So I decided to wrap SQLDeploy utility in my deployment package itself.This requires to find the all the dependencies which is required by this utility. After some research I was able to find the set of dependent assemblies and files which can be independently taken to any environment and executed in standalone. It worked fine in most of the environment.Most of the files you can find from any SQL server 2012 deployed machine(C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\) with the exception for Microsoft.SqlServer.TransactSql.ScriptDom.dll, which you can find in C:\Program Files (x86)\Microsoft SQL Server\110\SDK\Assemblies folder.


SqlDeploy Command Example


SqlPackage /Action:Publish  /Sf:Northwind.dacpac  /TargetDatabaseName:Testdac  /TargetServerName:MyServer /Variables:INSERTDATA=0 /Variables:IPADDRESS=127.0.0.1 /Variables:DATABASE_CONNECTIONSTRING="Data Source=.;initial catalog=Engine;integrated security=True;MultipleActiveResultSets=True;"

Please let me know if you are able to import DACPAC using the above files.





4 comments:

  1. Your solution to copy all the needed files manually works great. I like this Approach. Tanks for sharing!

    ReplyDelete
  2. Thank you for sharing the solution. Copying the files worked for me.

    ReplyDelete
  3. Thank you for sharing the solution. Copying the files worked for me.

    ReplyDelete
  4. I had the same idea but missed the ScriptDom assembly. Added it and it worked. Thanks!

    ReplyDelete