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 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: Neil.Hambly@hotmail.co.uk Neils Blog @ http://sqlblogcasts.com/blogs/NeilHambly

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) Q&A

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 • 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 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 .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 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) • 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

DacPAC & UCP’S 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 DBA

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 • Data-tier Application Tutorials http://msdn.microsoft.com/en-us/library/ee210554(SQL.105).aspx • Data-tier Applications in SQL Server 2008 R2 whitepaper http://msdn.microsoft.com/en-us/library/ff381683.aspx

The Endwell… (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 • Neil.Hambly@hotmail.co.uk • Thank You • Please complete feedback • Saturday Session Feedback @ www.sqlbits.com/saturdayfeedback

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