D ata tier applications application and multi server management
1 / 18

D ata-Tier Applications {Application and Multi-Server Management} - PowerPoint PPT Presentation

  • Uploaded on

D ata-Tier Applications {Application and Multi-Server Management}. An overview & introduction to using DACPACs. Neil Hambly. York Oct 2010. Neil Hambly MS SQL Server Databases (12+ Yrs). Permanent Roles MDSL (London) July 2010 – Present DB Architect

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about ' D ata-Tier Applications {Application and Multi-Server Management}' - declan-harding

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
D ata tier applications application and multi server management

Data-Tier Applications{Application and Multi-Server Management}

An overview & introduction to using DACPACs

Neil Hambly

York Oct 2010

Neil hambly ms sql server databases 12 yrs
Neil HamblyMS SQL Server Databases (12+ Yrs)

Permanent Roles

MDSL (London) July 2010 – Present

DB Architect

Editions: SQL 2008 (R2), SQL 2005

Key Features: Mirroring, Service Broker, SSRS,SSAS

iProfile (London) Oct 2004 – June 2010

DB Architect | DBA | DB Developer (Database Lead )

Editions: SQL 2008 (R2), SQL 2005, SQL 2000

Key Features: Clustering, Service Broker, Replication, SSRS

Accenture (Dublin) June 1999– Oct 2004

DBA | DB Developer (Global BI - Team Leader)

Editions: SQL 2000, SQL 7, SQL6.5

Key Features: Replication, DTS, OLAP

Contractor Roles

BBC 14 Months (DB Support )

ABNAMRO 16 Months (DB Developer )

Editions: SQL7, SQL6.5

UK SQL User Group (London) Organiser / Presenter

Twitter: @Neil_Hambly

Email: [email protected]

Neils Blog @


Agenda overview
Agenda Overview

Tools to develop Data Tier Applications

Data Application Components

Where do we put the DAC

What’s in the Data Tier Application

Comparing DACs with Database projects

Limitations of object supported in V1

DAC and UCP’s

Demo’s (SSMS & Visual Studio 2010)


Tools to develop data tier applications
Tools to Develop Data Tier Applications

You will need the following items

SQL Server 2008 R2

  • DAC Projects in Pro+ SKUs

Data tier application components
Data-Tier Application Components

  • Single file filename.dacpac

  • Combines logical & Physical objects along with deployment policies

  • Simpler deployment options

    • Install / Uninstall /Upgrade

    • Repair (in future release)

Where do we put the dac
Where do we put the DAC

Data-tier applications are deployed to a database instance (or multiple instances)

But otherwise are the same as other user Databases

SQL Server 2008 R2

What s in the data tier application
What’s in the Data-Tier Application

.dacpacs are a zip file, containing multiple XML files

Database object definitions

Server-selection policies

Application properties (versions, App name…)

Instance-level objects (logins, users...)

pre & post-deployment scripts

Current version has limitations of objects supported

Dac v s database projects
DAC vs Database Projects?

Before .dacpacs development was done in various different methods

Sometimes this is a database project, below is a quick comparison of the differences where a database project or DAC are the most suitable method

Database projects and Data-tier Application projects can operate side by side

Dac v1 limitations objects not supported
DAC V1 Limitations (Objects Not Supported)

  • Objects marked for deprecation

    (Including defaults, rules and numbered stored procedures)

  • CLR objects and data types

    (Spatial, Geography, Geometry,

    Hierarchy ID data types, SQL assemblies,

    CLR stored procedures and functions)

  • User-defined aggregates and

    user-defined (CLR) types

  • Partition schemes and partition functions

  • XML schema collections, XML indexes and spatial indexes

  • Service broker objects

  • Filestream columns

  • Symmetric keys, asymmetric keys, certificates

  • DDL triggers

  • Application roles

  • Full-text catalog objects

  • Extended stored procedures

  • Encrypted objects

    (Encrypted stored procedures, views, functions, and triggers)

  • Objects containing cross-database dependencies and linked server references

  • Extended properties

  • Synonyms


SQL Server Management Studio

Utility Explorer content has useful Videos to help you with UCP’s

Install the .dacpac & monitor it’s usage via a UCP


Demo Time

  • Several demo’s now will follow in SSMS &

  • Visual Studio 2010 to illustrate the concepts

  • Demo 1 SSMS Extracting a Database as .dacpac

  • Demo 2 Visual Studio 2010 - DACPAC project

  • Demo 3 SSMS Installing / Upgrading DAC databases

  • Latest News - (SQL Server 2008 SP2) Released 29th Sept 2010

  • SQL Server 2008 R2 Application and Multi-Server Management Compatibility with SQL Server 2008.

    • SQL Server 2008 Instance Management.

    • With SP2 applied, an instance of the SQL Server 2008 Database Engine can be enrolled with a SQL Server 2008 R2 Utility Control Point as a managed instance of SQL Server.

    • Data-tier Application (DAC) Support.

    • Instances of the SQL Server 2008 Database Engine support all DAC operations delivered in SQL Server 2008 R2 after SP2 has been applied.

    • You can deploy, upgrade, register, extract, and delete DACs.

    • SP2 does not upgrade the SQL Server 2008 client tools to support DACs.

    • You must use the SQL Server 2008 R2 client tools, such as SQL Server Management Studio, to perform DAC operations.

Action steps give useful insight to how a DAC upgrade is performed

Creating a new version of the Database and renaming previous versions

Resources performed

  • Data-tier Application Tutorials


  • Data-tier Applications in SQL Server 2008 R2 whitepaper


The end well until next time
The End performedwell… (until next time)

  • Please ask your questions clearly and loudly.

  • If you don’t get your questions answered now

  • PLEASE ask me after the session, or emails to

  • [email protected]

  • Thank You

  • Please complete feedback

  • Saturday Session Feedback @ www.sqlbits.com/saturdayfeedback