How to add objects to master.dacpac?

Sometimes you have objects, which you placed inside master, in order to share them between databases. This raises the question how to get a database project to resolve references to these objects? Additionally you may need to rely on the global name resolution capabilities of master, so that you do not have to prefix the object with a database name. This only applies for user defined procedures, but there are causes where you need this. Lets see how we can make this work.

For this little experiment we assume you have a database project (Database1.sqlproj), which contains a procedure that references objects in master; one which is declared inside system master.dacpac and one that we added as a shared procedure, named “dbo.sp_version”. Our contrived test object inside our database project looks like this:

create proc dbo.mytestproc
as
exec dbo.sp_who
exec dbo.sp_version

In order to resolve sp_who you need to add a database reference to master, using the “Add Database Reference” dialog like this:

image

This resolves the sp_who reference, but since sp_version is not defined in the system level master.dacpac you will see a warning like this:

Warning 1 SQL71502: Procedure: [dbo].[mytestproc] has an unresolved reference to object [dbo].[sp_version].

Your first inclination should be to use a database reference, which allows you to augment a namespace by adding additional objects.

To setup this up you need to perform the following steps::

  1. Add a second database project to your solution, lets name it MasterExt.sqlproj.
  2. Add the procedure with the name dbo.sp_version to the MasterExt project
    create proc dbo.sp_version
    as
    select	1 as major,
    		1 as minor,
    		1 as build,
    		1 as patch
    
  3. Next we need to add a reference from the Database1 project to the MasterExt project or to the MasterExt.dacpac, which ever you prefer. If these are truly objects which you are sharing amongst many databases, I personally prefer the dacpac file reference approach, as it allows to centrally control the shared objects inside a project project and while I do not have to share the source representation. Besides that file based database references are slightly faster and more efficient as the do not have to be compiled when you rebuild the solution. Adding the database reference to your project looks like this:
    image
    Make sure you choose: “Different database, same server” as the database location and set the database name to master. Also make sure to remove the database variable name, this needs to be blank, in order to trigger a literal database name reference (I know very obvious).
  4. Rebuild your solution
  5. You will still see the warning

    Warning    1    SQL71502: Procedure: [dbo].[mytestproc] has an unresolved reference to object [dbo].[sp_version]. 

  6. The reason you still get the warning is due to global name resolution, more about this in a bit. Lets first check if the reference works, by changing the call in to an explicit database referenced call by changing
    exec dbo.sp_version into exec master.dbo.sp_version
    create proc dbo.mytestproc
    as
    exec dbo.sp_who
    exec master.dbo.sp_version
  7. Rebuild the solution
  8. Now the warning is gone!

Why is that? As suggested earlier, global name resolution comes in to play. In order to get to the answer we have to crack open the master.dacpac file. A dacpac file is a so called Open Packaging Convention or OPC file for short, see http://en.wikipedia.org/wiki/Open_Packaging_Conventions for details. Bottom-line, OPC files are ZIP files with some specific structure and content. In order to digest a *.dacpac file, we can rename the file extension from *.dacpac to *.zip and we extract its content.

Lets crack open the system provided master.dacpac file which lives in:

%ProgramFiles(x86)%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SQLSchemas\master.dacpac

First we will create a directory we can work in and make a copy of the system provided master.dacpac file by performing the following steps:

md c:\dac
cd /d c:\dac
copy “\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\SQLDB\Extensions\SqlServer\110\SQLSchemas\master.dacpac”
ren master.dac master.zip

Next expand the master.zip file using your favorite ZIP utility, if you do not have one you can use the build in ZIP extraction functionality in Windows Explorer. I am using WinRAR to expand in to the c:\dac\master directory.

Now lets check the content of the directory:

dir c:\dac\master

The result looks like this:

09/13/2012 03:17 PM 175       [Content_Types].xml
09/13/2012 03:17 PM 6,230,663 model.xml
09/13/2012 03:17 PM 777       Origin.xml

The [Content_Types].xml file is a standard OPC file which defines the content types used inside the instance of the OPC file, in this case the only content type used will be XML.

<?xml version="1.0" encoding="utf-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">   <Default Extension="xml" ContentType="text/xml" />
</Types>

The model.xml file contains the actual model payload which describes the objects inside a database and the origin.xml describes where the model information is coming from in terms of version and dates. It also contains a checksum of the model.xml, to make sure the model.xml has not been tempered with.

<?xml version="1.0" encoding="utf-8"?>
<DacOrigin xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">   <PackageProperties>     <Version>3.0.0.0</Version>     <ContainsExportedData>false</ContainsExportedData>   </PackageProperties>   <Operation>     <Identity>0cb584bd-a5db-4740-8cba-3bf24dbf9aff</Identity>     <Start>2012-09-13T15:17:48.3677799-07:00</Start>     <End>2012-09-13T15:17:48.5408231-07:00</End>     <ProductName />     <ProductVersion>0.0</ProductVersion>     <ProductSchema>http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02</ProductSchema>   </Operation>   <Checksums>     <Checksum Uri="/model.xml">EED727619D660CBB007A092429C44C627CCEFE26738B47E453F8A93CF35906F0</Checksum>   </Checksums>
</DacOrigin>

Now that we have exposed the model, lets open it up in our favorite XML editor, I will be using Notepad++. Once we have opened up the model.xml, lets search for sp_who, so we can compare this with sp_version which resides in the MasterExt.dacpac file.

Model definition of sp_who

<Element Type="SqlProcedure" Name="[sys].[sp_who]">       <Annotation Type="AliasedToDbo" />       <Annotation Type="GloballyScoped" />       <Relationship Name="Parameters">         <Entry>           <Element Type="SqlSubroutineParameter" Name="[sys].[sp_who].[@loginame]">             <Relationship Name="Type">               <Entry>                 <Element Type="SqlTypeSpecifier">                   <Relationship Name="Type">                     <Entry>                       <References ExternalSource="BuiltIns" Name="[sys].[sysname]" />                     </Entry>                   </Relationship>                 </Element>               </Entry>             </Relationship>           </Element>         </Entry>       </Relationship>       <Relationship Name="Schema">         <Entry>           <References ExternalSource="BuiltIns" Name="[sys]" />         </Entry>       </Relationship>     </Element>

If we repeat the same steps used to extract the information from master.dacpac for our own MasterExt.dacpac, we can retrieve the mode definition for sp_version, so we can compare them.

<Element Type="SqlProcedure" Name="[dbo].[sp_version]">
	<Property Name="BodyScript">
		<Value><![CDATA[
select	1 as major,
		1 as minor,
		1 as build,
		1 as patch]]></Value>
	</Property>
	<Property Name="IsAnsiNullsOn" Value="True" />
	<Relationship Name="Schema">
		<Entry>
			<References ExternalSource="BuiltIns" Name="[dbo]" />
		</Entry>
	</Relationship>
	<Annotation Type="SysCommentsObjectAnnotation">
		<Property Name="Length" Value="94" />
		<Property Name="StartLine" Value="1" />
		<Property Name="StartColumn" Value="1" />
		<Property Name="HeaderContents" Value="create proc dbo.sp_version&#xD;&#xA;as" />
	</Annotation>
</Element>

If you compare the two model elements, two things are very different. The master.dacpac fragment does not contain BodyScript element. Since the master.dacpac reflects the shape of objects always deployment by SQL Server, you only need to be able to bind to the shapes and you do not need it to deploy them, therefore the actual body scripts are omitted.

The second part which is different is that there are two annotations in the master.dacpac <Annotation Type=”AliasedToDbo” /> and <Annotation Type=”GloballyScoped” /> which do not show up in the MasterExt.dacpac or in any other dacpac for that matter. These two annotations reflect the special aspects of the master database regarding name resolution. GloballyScoped refers to the fact that an object can be access without having to specify a database when you are not in the particular database context where the object itself resides. AliasesToDbo refers to the fact that certain objects show up in the sys and dbo namespace, but it you look in the internal system catalog, you will only find the objects in the sys schema.

Since the way we call sp_version relies on the global namespace, in order to make this work, we would have to annotate the sp_version element with the GloballyScoped annotation. There is no way to do this inside the project, so we have to edit the model.xml of the MasterExt.dacpac.

The update model element for sp_version now looks like this:

<Element Type="SqlProcedure" Name="[dbo].[sp_version]">
	<Annotation Type="GloballyScoped" />
	<Property Name="BodyScript">
		<Value><![CDATA[
select	1 as major,
		1 as minor,
		1 as build,
		1 as patch]]></Value>
	</Property>
	<Property Name="IsAnsiNullsOn" Value="True" />
	<Relationship Name="Schema">
		<Entry>
			<References ExternalSource="BuiltIns" Name="[dbo]" />
		</Entry>
	</Relationship>
	<Annotation Type="SysCommentsObjectAnnotation">
		<Property Name="Length" Value="94" />
		<Property Name="StartLine" Value="1" />
		<Property Name="StartColumn" Value="1" />
		<Property Name="HeaderContents" Value="create proc dbo.sp_version&#xD;&#xA;as" />
	</Annotation>
</Element>

Now that we updated the model.xml, lets put it back together inside a .dacpac, by zipping up the content and renaming the file extension from .zip to .dacpac.

After we done this we can rebuild the project, this assumes the database reference to MasterExt.dacpac is still present and points to the right file location!

Once we rebuild we will get the another error “The reference to <filename.dacpac> is not not support”.
image

The dacpac file is “not supported” because the checksum inside the orgin.xml no longer corresponds with the actual checksum of the model.xml payload. In order to address this we have to calculate the checksum of the model.xml. Turns out the checksum is a simple SHA256 based hash.  I created a small utility, named dacchksum.exe to help you update the signature. The usage is very simple:

c:\dac>dacchksum.exe /i:MasterExt\MasterExt.dacpac
Model checksum
Stored:   :[E4DB0BCE0396B68C1E2E195D9FB036A84A4A03B08F6DE21F71D645E36CD21DE8]
Calculated:[7400DD4579AE4F548C074BB771D0F1E356160F84C360AB91F4DA9AF6921AAC63]

This tells you the persisted checksum and the calculated one based on the model.xml payload are different. Adding the /Update or/u flag will update the persisted checksum with the updated value.

c:\dac>dacchksum.exe /i:MasterExt\MasterExt.dacpac /u /v
Starting:  [2/10/2013 12:18:58 PM]
Input:     [c:\dac\MasterExt\MasterExt.dacpac]
Update:    [True]
Model checksum
Stored:   :[E4DB0BCE0396B68C1E2E195D9FB036A84A4A03B08F6DE21F71D645E36CD21DE8]
Calculated:[7400DD4579AE4F548C074BB771D0F1E356160F84C360AB91F4DA9AF6921AAC63]
Updating checksum
Finished:  [2/10/2013 12:18:58 PM]

Now rebuild your project again, at the warning goes away, success!

We are now able to add a database reference, which adds objects to the global namespace of the master database. As we saw during out experiment, before it was impossible to either use a project reference or edit the master.dacpac directly, so this unsupported hack provides a solution that is isolated and cannot impact the rest of the system. In production you would add steps to updated the MasterExt.dacpac as post build steps inside the MasterExt.sqlproj.

I hope this is useful, enjoy,
@DataDude

Posted in DAC | Tagged | Leave a comment

DACFX Sept 2012 Updates Break Headless Build

Last updated: Oct 4nd 2012, 09:00hr

With the Sept 2012 update of SSDT and DAC, the accompanying SSDT Build Utilities  (SSDTBuildUtilities.msi) breaks the headless build server integration as described in this earlier blog post (http://sqlproj.com/index.php/2012/03/headless-msbuild-support-for-ssdt-sqlproj-projects/).

Early reports suggested that this problem is platform specific, but that is not the case. The problem is caused by a missing assembly file, the file is question is currently not part of the SSDTBuildUtilities.msi, and therefor causes the MSBuild task to fail. The problem does not manifest itself if you have the SQL Server Data Tools Sept 2012 update (SSDT.msi) installed on the build server, but since this defeats the purpose of a headless build server, it is therefore not a viable workaround.

The workaround is to manually copy the missing file over to the build server.

You need to copy the:

  • %ProgramFiles(x86)%\Microsoft SQL Server\110\DAC\Bin\Microsoft.Data.Tools.Contracts.10.dll

and/or

  • %ProgramFiles(x86)%\Microsoft SQL Server\110\DAC\Bin\Microsoft.Data.Tools.Contracts.11.dll

To the same directory location on your build server.

Which assembly or assemblies you have to copy depends on the which Microsoft.Data.Tools.Schema.Tasks.Sql.??.dll assemblies are installed on your build server. This is depends on which SSDTBuildUtilities.msi version you used to install; the VS2010 and/or VS2012 hosted SSDT edition. To determine which assemblies you need to copy, simply run the following DIR command:

dir “%ProgramFiles(x86)%\Microsoft SQL Server\110\DAC\bin\Microsoft.Data.Tools.Schema.Tasks.Sql.*.dll”

image

In my example it shows both versions, as I have both the VS2010 and VS2012 SSDTBuildUtilities.msi installed, so I have to copy both.

NOTE: Please do NOT copy and rename Microsoft.Data.Tools.Schema.Tasks.Sql.10.dll to Microsoft.Data.Tools.Schema.Tasks.Sql.11.dll as this will lead to problems down the road when we have to update these files.

I will update this blog post once there is a better and or final solution to this problem.

Posted in DAC, MSBUILD | Tagged , | 1 Comment

SqlPackage.exe on Windows XP

Note: Using SqlPackage.exe and the DAC framework on Windows XP or Windows Server 2003 (R2) is UNSUPPORTED, please use at your own risk!

As noted on the DACFX REDIST download page, the officially supported operating systems platforms for DACFX are: Windows 7 Service Pack 1, Windows Server 2008 R2 SP1, Windows Server 2008 Service Pack 2, Windows Vista Service Pack 2. This list does not include Windows XP, Windows Server 2003 and Windows Server 2003 R2. Before September 2012, the DACFX redist installer (DACFramework.msi) would block installing on Windows XP or Windows Server 2003 (R2) platforms. This block has been removed starting with the Microsoft® SQL Server® 2012 Data-Tier Application Framework (September 2012) release. Please note that this does not imply that Windows XP or Windows Server 2003 (R2) are now supported platforms!

This post documents to crusade to make using SqlPackage.exe possible on Windows XP.

Installing pre-requisites

I started with a clean Windows XP (x32) SP3 installation, fully updated with the latest security patches and a virus checker installed.

WinXP

First we need to install some pre-requisites for DACFX:

Normally these pre-requisites are validated and when not present installed as part of the SQL Server Data Tools (SSDT) installation process.

Note: If you did not have .NET 4.0 installed before, make sure to run Windows Update to download and install all the latest security updates.

Installing DACFX

Next we can install the DACFX redist and its two dependent components SQLScriptDom and the SQL CLR types assembly, like described on the DACFX redist download page.

  • DACFramework.msi
  • SQLDOM.MSI
  • SQLSysClrTypes.msi

Getting it to Work

Now that we installed all the binaries that we would normally need when running on a supported platform, let’s start our crusade to get it to work.

In order to make testing a little simpler and contained, I installed a local SQL Server Express 2008 R2 edition, but you can also use a remote SQL Server instance to test with. On this SQL Server instance I installed our good-old friend the Northwind database.

The first attempt is to extract a DACPAC from the Northwind database which resides on the local SQL Server Express instance, using the /Action:Extract option on SqlPackage.exe

“%ProgramFiles%\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe”
/a:extract
/ssn:localhost\sqlexpress
/sdn:Northwind
/tf:c:\temp\nw.dacpac

WinXP-DAC-EXTRACT

This attempt fails to initialize the ESE database which is used as the model store. The ESE database is present on Windows XP, however the settings used to initialize are not compatible, hence the failure.

The work around is to use an in-memory model store instead, which can be enabled using /p:Storage=Memory option.

“%ProgramFiles%\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe”
/a:extract
/ssn:localhost\sqlexpress
/sdn:Northwind
/tf:c:\temp\nw.dacpac
/p:storage=memory

As shown below, this attempt also failed, but with a different failure.

WinXP-DAC-EXTRACT2

Our third attempt will be to publish an existing DACPAC, this required me to copy a DACPAC from a different machine, I am using the same Northwind database to create a DACPAC named, nw.dacpac, which I will use to deploy to a new database named foodb.

“%ProgramFiles%\Microsoft SQL Server\110\DAC\bin\SqlPackage.exe”
/a:publish
/sf:c:\temp\nw.dacpac
/tsn:localhost\sqlexpress
/tdn:foodb

This attempt also fails, but now an exception dialog is presented, which means details which hopefully will lead to a valuable clue.

WinXP-ExceptionDlg

WinXP-DAC-EXTRACT3

Thankfully there is a clue: “Unhandled Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. —> System.PlatformNotSupportedException: The specified cryptographic algorithm is not supported on this platform.”

So, it seems the second and third failure are identical, both resulting in a TargetInvocationException when constructing a SHA256CryptoServiceProvider. Studying the documentation for System.Security.Cryptography.SHA256CryptoServiceProvider on MSDN, it becomes apparent that the supported platforms do not include Windows XP, but only: Windows 7, Windows Vista SP1 or later, Windows Server 2008 (Server Core not supported), Windows Server 2008 R2 (Server Core supported with SP1 or later), Windows Server 2003 SP2.

Search to the Rescue

At first I surrendered thinking this was never going to work, but than I started to wonder how this could work on Windows Server 2003 SP2? These two operating system versions are highly related. Searching around for a while I stumbled in to two relevant postings:

Turns out the workaround is to add a Registry key to register for the cryptographic provider, which actually exists but under a different name then the .NET code is looking for. There exists an entry for a provider named: “Microsoft Enhanced RSA and AES Cryptographic Provider (Prototype)”, while the code looks for “Microsoft Enhanced RSA and AES Cryptographic Provider”, so without the word “(Prototype)”.

The following script will add the required Registry entries:

Windows Registry Editor Version 5.00
[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Cryptography\Defaults\Provider\Microsoft Enhanced RSA and AES Cryptographic Provider]
“Image Path”=”rsaenh.dll”
“Type”=dword:00000018
“SigInFile”=dword:00000000

Let’s check what the workaround in the Registry using RegEdit.exe.

WinXP-RegEdit

Last Attempt

Now that we added the workaround, lets re-run attempts #2 and #3.

Attempt #2 – Extracting a DACPAC

WinXP-DAC-EXTRACT4

Attempt #3 – Publishing a DACPAC

WinXP-DAC-EXTRACT5

Both the /Action:Extract and /Action:Publish operations now succeeded!

Windows XP is now able to use the DACFX command line environment, but please remember: This post describes how to make an unsupported platform work, this that not make it supported and please use at your own risk!

The test scripts, including the Registry script needed to add the cryptographic provider are available for download: SqlPackageWinXPTests.zip

I hope you found this post is useful,
Twitt @DataDude

Posted in DAC, SqlPackage | Tagged , , | Leave a comment

Schema View for SQL Server Data Tools

Today the team released the first version of the SQL Server Data Tools – Power Tools, which adds the ability to navigate the schema of your project inside the SQL Server Object Explorer.

Blog post detailing the release: http://blogs.msdn.com/b/ssdt/archive/2012/04/02/first-release-of-ssdt-power-tools.aspx

SSDT Power Tools installation location: http://visualstudiogallery.msdn.microsoft.com/9b0228c6-15d1-44de-9279-66dde12bf861?SRC=Featured

Please check it out,
@DataDude

Posted in POWER TOOLS | Leave a comment

SQL Connections Spring 2012

SQL Connections Spring 2012

Next week, at the SQL Server Connections conference, I will be presenting “Database Development with SQL Server Data Tools”, a 60 minute overview how you can leverage SQL Server Data Tools (SSDT) for your daily database development and deployment tasks.

When: Tuesday March 27th 2012, 10:00-11:00AM

Abstract:

SMS01: Database Development with SQL Server Data Tools
Gert Drapers
SQL Server Data Tools (SSDT) transforms traditional database development by introducing a declarative, model-based experience that supports both online and offline (project-based) database development. This session introduces SSDT and shows through a series of demos how you can develop, debug, refactor, and maintain SQL Server and SQL Azure databases from within a single integrated environment

Drop by if you are there!

@DataDude

Posted in CONFERENCE, SQL Server Connections | Leave a comment

Headless MSBuild Support for SSDT (*.sqlproj) Projects

 

Update: breaking change: http://sqlproj.com/index.php/2012/10/dacfx-sept-2012-updates-break-headless-build/

This article describes how to install the required components to build and publish SQL Server Data Tools projects (*.sqlproj) using MSBuild without installing the full SQL Server Data Tool hosted inside the Visual Studio IDE.

In order to acquire the binaries needed you have to create an Administrative install of SSDT, which is described in detail in this previously published article.

NOTE: You could download all but one components from the SQL Server 2012 Feature Pack download page, however there currently is no separate download of the SSDTBuildUtilities.msi available.

Before we start, let’s setup a small test environment first. In this example I will start with a Windows Server 2008 installation, which has .NET 4.0 installed. I created a sample project on another machine using the IDE which I want to build and publish from my server. The sample project is available online: nw-sqlproj.zip, for this example I extracted the project in to the “c:\projects\nw-sqlproj” directory.

Second step is that we have to be able to call MSBuild.exe from the command prompt. In my example I am using a 32-bit Windows Server 2008 installation, after starting a command window using cmd.exe, I can invoke MSBuild.exe using:

%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe

Normally I create a command file, named msbuild.cmd which I place in %WINDIR%, which looks like this:

@echo off

if (%PROCESSOR_ARCHITECTURE%)==(AMD64) (
    %WINDIR%\Microsoft.NET\Framework64\v4.0.30319\MSBuild.exe %*
) else (
    %WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe %*
)

Let’s start a command window and see what happens when we try to build our test project!

image

The result is clear, and expected at this point. The build fails, since the project system references the SSDT build task via the Microsoft.Data.Tools.Schema.SqlTasks.targets file.

Below the MSBuild output as text:

C:\Projects\nw-sqlproj>%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 3:31:19 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (default targets).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (default targets).
C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj(90,3): error MSB4019: The imported project "C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default targets) -- FAILED.

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default targets)
 -- FAILED.

Build FAILED.

"C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default target) (1) ->
"C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default target) (2) ->
  C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj(90,3): error MSB4019: The imported project "C:\Program Files\MSBuild\Microsoft\VisualStudio\v10.0\SSDT\Microsoft.Data.Tools.Schema.SqlTasks.targets" was not found. Confirm that the path in the <Import> declaration is correct, and that the file exists on disk.

    0 Warning(s)
    1 Error(s)

Time Elapsed 00:00:00.04

C:\Projects\nw-sqlproj>

In order to get the MSBuild task and dependent components installed, you need to perform the following five steps:

  1. Install the Microsoft® SQL Server® 2012 Data-Tier Application FrameworkX86 Package(dacframework.msi)X64 Package (dacframework.msi)
  2. Install the Microsoft® SQL Server® 2012 Transact-SQL ScriptDomX86 Package(SQLDOM.MSI)X64 Package (SQLDOM.MSI)
  3. Install the Microsoft® SQL Server® 2012 Transact-SQL Compiler ServiceX86 Package(SQLLS.MSI)X64 Package (SQLLS.MSI)
  4. Install the Microsoft® System CLR Types for Microsoft® SQL Server® 2012X86 Package(SQLSysClrTypes.msi)X64 Package (SQLSysClrTypes.msi)
  5. Install the SQL Server Data Tools Build Utilities from the Administrative install point.\ssdt\x86\SSDTBuildUtilities.msi

Now we are ready to try again!

image

And we can now successfully build our project.

Below the MSBuild output as text:

C:\Projects\nw-sqlproj>%WINDIR%\Microsoft.NET\Framework\v4.0.30319\MSBuild.exe
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 3:52:23 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (default targets).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (default targets).
GenerateSqlTargetFrameworkMoniker:
Skipping target "GenerateSqlTargetFrameworkMoniker" because all output files are up-to-date with respect to the input files.
CoreCompile:
Skipping target "CoreCompile" because all output files are up-to-date with respect to the input files.
SqlBuild:
Skipping target "SqlBuild" because all output files are up-to-date with respect
 to the input files.
CopyFilesToOutputDirectory:
  nw-sqlproj -> C:\Projects\nw-sqlproj\bin\Debug\nw_sqlproj.dll
SqlPrepareForRun:
  nw-sqlproj -> C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.dacpac
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (default targ
ets).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (default targets)
.

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:00.40

C:\Projects\nw-sqlproj>

Now that we can build lets publish our project, using MSBuild.

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.publish.xml

This will publish the project to the server specified in the publish profile, which is an MSBuild structure XML file.

nw-sqlproj.publish.xml

<?xml version="1.0" encoding="utf-8"?>
<Project ToolsVersion="4.0" xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
  <PropertyGroup>
    <IncludeCompositeObjects>True</IncludeCompositeObjects>
    <TargetDatabaseName>NorthwindTest</TargetDatabaseName>
    <DeployScriptFileName>nw-sqlproj.sql</DeployScriptFileName>
    <TargetConnectionString>Data Source=localhost;Integrated Security=True;Pooling=False</TargetConnectionString>
    <ProfileVersionNumber>1</ProfileVersionNumber>
  </PropertyGroup>
</Project>

MSBuild output of the Deploy operation:

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.pblish.xml
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 5:37:52 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (Publish target(s)).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw -sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (Publish target(s)).
SqlPublish:
  Deployment script generated to:
  C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.publish.sql

  Creating NorthwindTest...
  Creating [dbo].[Categories]...
  Creating [dbo].[Categories].[CategoryName]...
  Creating [dbo].[CustomerCustomerDemo]...
  Creating [dbo].[CustomerDemographics]...
  Creating [dbo].[Customers]...
  Creating [dbo].[Customers].[City]...
  Creating [dbo].[Customers].[CompanyName]...
  Creating [dbo].[Customers].[PostalCode]...
  Creating [dbo].[Customers].[Region]...
  Creating [dbo].[Employees]...
  Creating [dbo].[Employees].[LastName]...
  Creating [dbo].[Employees].[PostalCode]...
  Creating [dbo].[EmployeeTerritories]...
  Creating [dbo].[Order Details]...
  Creating [dbo].[Order Details].[OrderID]...
  Creating [dbo].[Order Details].[OrdersOrder_Details]...
  Creating [dbo].[Order Details].[ProductID]...
  Creating [dbo].[Order Details].[ProductsOrder_Details]...
  ...
  ...<lines deleted for clarity>

  Update complete.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (Publish target(s)).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (Publish target(s)).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:06.43

C:\Projects\nw-sqlproj>

And if we would publish it again, by default it will only perform an incremental update!

C:\Projects\nw-sqlproj>msbuild /t:Publish /p:SqlPublishProfilePath=nw-sqlproj.publish.xml
Microsoft (R) Build Engine Version 4.0.30319.1
[Microsoft .NET Framework, Version 4.0.30319.239]
Copyright (C) Microsoft Corporation 2007. All rights reserved.

Build started 3/7/2012 5:41:23 PM.
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" on node 1 (Publish target(s)).
ValidateSolutionConfiguration:
  Building solution configuration "Debug|Any CPU".
Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (1) is building "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (2) on node 1 (Publish target(s)).
SqlPublish:
  Deployment script generated to:
  C:\Projects\nw-sqlproj\bin\Debug\nw-sqlproj.publish.sql

  Update complete.
Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sqlproj" (Publish target(s)).

Done Building Project "C:\Projects\nw-sqlproj\nw-sqlproj.sln" (Publish target(s)).

Build succeeded.
    0 Warning(s)
    0 Error(s)

Time Elapsed 00:00:11.50

C:\Projects\nw-sqlproj>

We are done, we enabled build and publishing from MSBuild, without installing the SQL Server Data Tools IDE inside the Visual Studio shell.

I hope this helps you getting started with SQL Server Data Tools (SSDT)

@DataDude

Posted in MSBUILD, SETUP, SSDT | Tagged , , | 3 Comments

Creating an Administrative Install for SSDT

Last update March 6 2012

There are many occasions where users will not be able to install SSDT directly from the web; company regulations, restricted bandwidth or simply because you are in a DMZ. For all these situations the “administrative install” capabilities of SSDT are there to provide the solution.

The administrative install enables you to create a completely independent installation point, which you can place on a flash drive or publish to a file share. Users can then install SSDT from this alternative location instead of having to download it from the web. This is also an ideal solution for engineers or consultants who want to make certain that they have their complete tool set with them at all times or if you want to burn an ISO image.

Lets walk through the steps required to create an administrative install.

  1. Create a local directory in which you are going to gather all the files. For this example we will create a directory named C:\SSDTAdminInstall.
  2. Next download the SSDT installer (SSDTSetup.exe) and save it in the C:\SSDTAdminInstall directory. Note that the SSDTSetup.exe file is language specific!

    Portuguese (Brazil)

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×416

    Chinese(PRC)

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×804

    English(United States)

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×409

    French

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0x40c

    German

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×407

    Italian

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×410

    Japanese

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×411

    Korean

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×412

    Russian

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×419

    Spanish

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0x40a

    Chinese(Taiwan)

    http://go.microsoft.com/fwlink/?LinkID=241405&clcid=0×404

  3. Start an elevated command shell and set the directory context to the C:\SSDTAdminInstall directory
  4. You can always execute “SSDTSetup.exe /?” to get the help screenimage
  5. Next we need to download all the files needed to create the independent and isolated administrative setup. We do this by executing: “SSDTSetup.exe /layout C:\SSDTAdminInstall”image
  6. This step launches a user interface which allows you to monitor the download progress.image
  7. When the download step is finished, the following files will reside in the C:\SSDTAdminInstall directory.

    C:\SSDTAdminInstall\SSDTSetup.exe

    C:\SSDTAdminInstall\payload\dotNetFx40_Full_x86_x64.exe

    C:\SSDTAdminInstall\payload\NDP40-KB2544514-x86-x64.exe

    C:\SSDTAdminInstall\payload\x64\DACFramework.msi

    C:\SSDTAdminInstall\payload\x64\NDP40-KB2468871-v2-x64.exe

    C:\SSDTAdminInstall\payload\x64\SharedManagementObjects.msi

    C:\SSDTAdminInstall\payload\x64\SQLDom.msi

    C:\SSDTAdminInstall\payload\x64\SqlLocalDB.msi

    C:\SSDTAdminInstall\payload\x64\sqlls.msi

    C:\SSDTAdminInstall\payload\x64\SQLSysClrTypes.msi

    C:\SSDTAdminInstall\payload\x64\TSqlLanguageService.msi

    C:\SSDTAdminInstall\payload\x86\DACFramework.msi

    C:\SSDTAdminInstall\payload\x86\NDP40-KB2468871-v2-x86.exe

    C:\SSDTAdminInstall\payload\x86\SharedManagementObjects.msi

    C:\SSDTAdminInstall\payload\x86\SQLDom.msi

    C:\SSDTAdminInstall\payload\x86\SqlLocalDB.msi

    C:\SSDTAdminInstall\payload\x86\sqlls.msi

    C:\SSDTAdminInstall\payload\x86\SQLSysClrTypes.msi

    C:\SSDTAdminInstall\payload\x86\SSDTDBSvcExternals.msi

    C:\SSDTAdminInstall\payload\x86\TSqlLanguageService.msi

    C:\SSDTAdminInstall\payload\x86\VS10sp1-KB983509.exe

    C:\SSDTAdminInstall\payload\x86\VSIntShell.exe

    C:\SSDTAdminInstall\ssdt\x86\DbSqlPackageProvider.msi

    C:\SSDTAdminInstall\ssdt\x86\SSDT.msi

    C:\SSDTAdminInstall\ssdt\x86\SSDTBuildUtilities.msi

  8. The administrative install is now ready to be used. You can start install SSDT by invoking C:\SSDTAdminInstall\SSDTSetup.exe.

Enjoy,
-GertD

Posted in SETUP, SSDT | Tagged | Leave a comment

SSDT setup from SQL Server 2012

Last update March 6 2012

This post will walk you through the installation experience when installing the SQL Server Data Tools (SSDT) as part of the SQL Server 2012 setup.

Installing SSDT via the SQL Server setup is targeted at those users who do not have an existing Visual Studio 2010 environment installed on their machine. If you do have an existing Visual Studio 2010 environment installed on your machine there is a more expedient way and you also have to worry about meeting some prerequisites which are not required if you do not have Visual Studio 2010 installed. I will describe this process in a different post.

This posts assumes you already downloaded the SQL Server 2012 media and started the SQL Server setup. The following screenshots provide a visual and chronological overview of the steps that lead up to the point where you make the decision to install SSDT in the feature selection section.

image image

image image

image image

image image

image

As part of the feature selection you can choose the “SQL Server Data Tools” option, as shown below

image

If you just want to install the SSDT development environment this is the only thing you have to select.

Selecting this option will result in the installation of the Visual Studio 2010 SP1 integrated shell. When the installation is finished you will find an “SQL Server Data Tools” menu entry under the “Microsoft SQL Server 2012″ menu. You will notice there also exists a Microsoft Visual Studio 2010 menu entry. Both links will launch the same shell, the SQL Server Data Tools link simply added as an additional convenience for SQL Server users.

SQL2012 Feature Installation 23 SQL2012 Feature Installation 6

The “SQL Server Data Tools” will launch the Visual Studio 2010 shell. If you choose File\New Project the Visual Studio New Project dialog will open and show you the installed project templates. When selecting the “SQL Server” templates, you will find there is a template named: “SQL Server Data Tools – Database Projects (Web Install)”.

SQL2012 Feature Installation 10

When you create a new project using this template or open an existing database (*.sqlproj) project, the so called stub project will get invoked and initiate the last leg of the installation process. This is the stage where the actual SSDT bits will get installed via the Web Platform Installer (WebPI) feed. The remaining part of the installation is about 40MB since you already have installed the Visual Studio 2010 shell, the Visual Studio 2010 SP1 updates for the integrated shell and the other SQL Server components which SSDT depends on.

image

If you do not have the Web Platform Installer (version 3.0) installed, you will first land on the following web page, which will initiate the installation of the WebPI installer.

SQL2012 Feature Installation 12

Depending on the version of the operating system and browser which you are using you will be prompted to confirm the execution of SSDT.exe.

SQL2012 Feature Installation 13

Once the WebPI binaries are installed you will see the following dialog inside WebPI. If you have the WebPI binaries installed you will immediately advance to this dialog.

SQL2012 Feature Installation 14

After you selected the “Install” option you will get one more final conformation page. after which the installer will download the remaining binaries and install them. When the installation is finished you will get the summary dialog, after which you will get returned the the WebPI overview page.

SQL2012 Feature Installation 15 SQL2012 Feature Installation 17

SQL2012 Feature Installation 18 SQL2012 Feature Installation 19

Since you initiated this process from within the Visual Studio shell, you will have to close the shell and restart it (just the shell that is). You will instantly notice that SSDT is install since you first will get asked to choose a profile.

SQL2012 Feature Installation 21

Don’t worry you can always change your profile setting later. Once you picked a profile you are ready to start using SSDT.

SQL2012 Feature Installation 22

I hope you will enjoy your new database development environment,
-GertD

Posted in SETUP, SQL2012, SSDT | Tagged , | Leave a comment