Data-tier application with SQL Server 2012

Data-tier application in SQL Server defines the schema and objects that are required to support an application.It is really very simple.

There are two ways you can implement a DAC:

• Using Microsoft Visual Studio 2010 with a SQL Server Data-tier application project type
• Using the extraction utility in SQL Server's Extract Data-tier wizard.

In Microsoft Visual Studio 2015 Community there are no templates to do this although you can import one, if it exists in the community samples.

Here is how you extract using the SQL Server Management Studio in SQL Server 2012.

Connect to SQL Server on your computer. Here is the Object Explorer of a named instance of SQL Server 2012.

Dac_01

We now create a DAC using the Northwind database.
Right click Northwind to open the menu and the sub-menu as shown.

Dac_02

Click on Extract Data-Tier Application... to display the Introduction screen of the Wizard.

Dac_03

The above screen pretty well describes the actions we take. It has the three steps:

• Set the DAC properties

• Review object dependency and validation results

• Build the DAC Package

Click Next to display the Set Properties page of the wizard. when you choose the databse the application name gets the database name by default and if it iss the first time the version is also set.

Dac_04

It appears that I have already created a file with that name and I will extract another with a different name NrthWnd.

Dac_05

Click Next. Displays the validation and summary page of the wizard.

Dac_07

Click Next. after a bit of creating and saving animation the process either succeeds or fails.

Dac_08

Click finish (after you get to see the Success of the operationb)  and the DAC  page is saved to the location indicated.

Dac_09

DAC files can be unpacked with programs shown.

 Dac_10

SQL Server Data Tools to go with SQL Server 2014

On the heels of the new release date for SQL Server 2014 RTM, SQL Server Data Tools' (SSDT) availability was announced two days ago. It is fully compatible with SQL Server 2014.

Apart from bug fixes in the previous version, the following are the enhancements:

• Static code analysis extensibility
• Filter capabilities in the editable data grid
• Saving data compare settings to file (.dcmp)
• Additional TSQL Editor connection actions

With this all database tools are fully integrated with the following editions,

• Microsoft Visual Studio 2013 Express for web
• Microsoft Visual Studio 2013 for Windows Desktop
• Professional
• Premium
• Ultimate

These will be pushed via VS Updates. You could also bring it in using Tools | Extensions and Updates menu.

Visual Studio 2013 with SQL Server tools can be downloaded from here:
http://www.visualstudio.com/downloads/download-visual-studio-vs

Note that both SSDT VS 2010 and SSDT VS 2012 are available stand alone.
Review what's new in this update here:
http://blogs.msdn.com/b/ssdt/archive/2014/03/25/sql-server-data-tools-for-sql-server-2014-is-available.aspx

Review the posts for earlier version:
http://hodentek.blogspot.com/2012/12/sql-server-data-tools-gets-updated.html

http://hodentek.blogspot.com/2013/02/about-ssdt-and-vs-2012.html

Follow link for more:
http://msdn.microsoft.com/en-us/data/hh297027

Can you deploy a Data-tier component extracted from SQL Server 2014 CTP1 to SQL Server 2012?

This should not be possible according to documentation here  and quoted below unless the DAC Framework version are the same.

'A DAC package or export file built with one version of the DAC Framework cannot be processed by an earlier version of the DAC Framework. For example, a DAC package extracted using the SQL Server 2012 client tools cannot be deployed using the SQL Server 2008 R2 client tools.'

This post is about a little experiment to test the validity of the above statement. In an earlier post in HodentekMSSS.blogspot.com here a .dac extracted from SQL Server 2012 was deployed to CTP1:

http://hodentekmsss.blogspot.com/2013/12/deploying-dac-to-sql-server-2014.html

The above post was about a DAC package extracted from SQL Server 2012 Express on Windows 7 Platform (x64) and deployed to SQL Server 2014 CTP1 on Windows 8 Preview platform.

In this post the database created using the above procedure was extracted and deployed back to SQL Server 2012 Express on the other partition. This was also successful in violation of the previously quoted procedure.

The reason was the DAC Framework versions were found to be the same on both servers using the DacUnpack utility.

The DacUnpack utility unpacks the .dac files. Here are the two files opened in Safari browser.

Northwind.dacpack's Origin.xml


3.0.0.0 false 2.0.0.0 1.0.0.0 4e266c18-42ec-4e0c-b3ce-5894ab3f1cd1 2013-12-09T15:29:51.5237832-10:00 0001-01-01T00:00:00.0000000 Microsoft.SqlServer.Dac, Version=10.3.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a 11.0.2902.0 http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02 

Microsoft SQL Server 2012 - 11.0.2218.0 (X64) Jun 12 2012 13:05:25 Copyright (c) Microsoft Corporation Express Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) 

8 103 1 9 13 27 1 1 13 91 100 11 1 19


 16  C3CCC630725BF4F1785C54AEC314B1D687A323EDAD15DA1235BA5CFD6FC5865A
 
NorthwindCTP1.dacpack's Origin.xml (obtained after unpacking)

3.0.0.0 false 2.0.0.0 c1c8859f-2548-4873-a550-b322e63d5ef4 2013-12-26T10:09:30.4691845-10:00 0001-01-01T00:00:00.0000000 Microsoft.SqlServer.Dac, Version=10.3.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a 10.3.0.0 http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02 
Microsoft SQL Server 2014 (CTP1) - 11.0.9120.5 (X64) Jun 10 2013 20:09:10 Copyright (c) Microsoft Corporation Enterprise Evaluation Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor) 
8 103 1 9 13 27 1 1 13 9 1 100 111 19
 16  87A4AC4BF297030FAFDEA917D02EF357DFDC8F92CED835DA64DF47683B851272

 

SQL Azure is getting beefed up

SQL Azure Updates have been regularly paced and surely enriching the user experience from the day it entered production not too long ago.

The May 2011 Service Update enriches even further. The following are the recent improvements.
Read full story here,

http://blogs.msdn.com/b/windowsazure/archive/2011/05/16/sql-azure-may-2011-update.aspx

   1. SQL Azure Management REST API – a web API for managing SQL Azure servers.

REST API are great to work with and it makes automating SQL Azure managment a lot easier than through the portal. Now the guys who click will be replaced by guys who code as this is a programmatic approach. REST API is an open protocol, well documented and there is not too much to learn.

   2. Multiple servers per subscription – create multiple SQL Azure servers per subscription.

The servers could be anywhere North America or Singapore (is it?).  Makes distributing servers across the globe for truly golbal enterprises.

   3. JDBC Driver – updated database driver for Java applications to access SQL Server and SQL Azure.

DotNet is great but don't under estimate the power of JAVA. The JDBC driver 3.0, a Type 4 driver can handle both SQL Azure and SQL Server and what is more it is free. Now on premises JAVA Apps can capture the data from the Azure clouds.

   4. DAC Framework 1.1 – making it easier to deploy databases and in-place upgrades on SQL Azure.

Here is completely free article on DAC if you want to sample the experience.
http://hodentek.blogspot.com/2010/02/working-with-data-gotten-lot-easier.html

DAC is a thing I personally love and the DAC Framework 1.1 takes it a little further. DAC makes it so easy to  import/export data it is truly amazing.

By the way, my book on SQL Azure was recently published and you may avail yourself of a free copy by sending an email to me, mysorian@gmail.com with the subject line, SQL AZURE. Your named will be entered into a raffle and the winner gets a copy of my book on SQL Azure(1st Winner hardcopy and 2nd Winner an eBook)

http://hodentek.blogspot.com/2010/12/my-sql-azure-book-is-published.html

Are you ready for SQL Server 2008 SP2?

The Service Pack 2 is ready for downloading from this site here:
To download the SQL Server 2008 SP2 release please select from the links below:
 •SQL Server 2008 SP2: http://go.microsoft.com/fwlink/?LinkId=196550
 •SQL Server 2008 SP2 Express: http://go.microsoft.com/fwlink/?LinkId=196551
 •SQL Server 2008 SP2 Feature Packs: http://go.microsoft.com/fwlink/?LinkId=202815
Note


 This is a service pack for the 2008 server and not the 2008 R2.

I have only Express editions and this executable (SQLServer2008SP2-KB2285068-x86-ENU.exe, 284MB ) from the above link applied the service pack to the two SQL Express servers on my notebook computer (Aspire 4810TZ-4011) with Windows 7 Ultimate.

I ran Select @@version on each of them and got these responses:

Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition with Advanced Services on Windows NT 6.1 <X86> (Build 7600: )
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)   Sep 16 2010 20:09:22   Copyright (c) 1988-2008 Microsoft Corporation  Express Edition on Windows NT 6.1 <X86> (Build 7600: )[I call this the Vanilla]
The first of these has Reporting Services server.

So what does this service pack accomplish?
* Reporting Services in Sharepoint Integrated Mode. It also helped SQL Server 2008 R2 to integrate with Sharepoint 2007 Products.
* SQL Server 2008 R2 Application and Multiserver Management Compatibility with SQL Server 2008
    *SQL Server 2008 Instance Management. You can enroll SQL Server 2008 with SQL Server 2008 R2 Utility Control Point as a managed instance.
    *Data-Tier Application(DAC) support. With the SP2 applied I can do all the DAC Operations just like in SQL Server 2008 R2. However I am using the      SSMS tool that was installed with SQL Server 2008 R2.
     Here is an example of the DACs that you can do with SQLExpress (vanilla).

You want to jump start on DAC, the coolest feature of SQL Server, then make sure you read these articles:
http://hodentekmsss.blogspot.com/2010/07/sql-server-2008-r2-and-data-tier.html
or
here
http://hodentek.blogspot.com/2010/02/working-with-data-gotten-lot-easier.html

Have some fun!

My articles on Data Access Pages

Dynamic content is at the heart of successful presentation of enterprise information on the Internet. Without the ability to present data with user interaction, Internet based commerce will be ineffective. It is possible to create dynamic web pages with data from the enterprise by using several technologies, including MS Access. Why MS Access in these days and times? Well, many businesses still use them.

Data Access Pages, a client side technology, depends on DynamicHTML. It is DHTML data binding with ADO that makes Data Access Pages possible. If you are using MS Access and attempting to place your data on the web do yourself a favor by reviewing the following articles:

Data Access Pages: What they are and how to create one ?

Using Microsoft Script Editor for Data Access Pages

Scripting a Data Access Page: a Simple Example

Creating Data Access Pages from Scratch

Creating Data Access Pages with Charts using Office Web Components

 [compare these charts to those created by MS Chart Control in ASP.NET 3.5]