TO THE CLOUD WITH SQL SERVER DATA TOOLS

Gert Drapers (@DataDude)Principal Group Program ManagerSQL Server Data Platform

CONNECTED DEVELOPMENT

PROJECT BASED DEVELOPMENT

SCHEMA DEPLOYMENT

INTRODUCING SQL SERVER DATA TOOLS

CONNECTED DEVELOPMENT

PROJECT BASED DEVELOPMENT

SCHEMA DEPLOYMENT

DeclarativeStandard Formats

Multi-Targeting DAC

4

DEPLOY DATABASE TO SQL AZURE

SQL AZURE VS. SQL SERVER, SOME OF THE DIFFERENCES…Features not available include…

• Common Language Runtime (CLR)

• Filegroup management• Full-text Search• Linked Servers• Distributed Transactions• Change Tracking • Service Broker

Unsupported syntax includes…• USE DATABASE• ON PRIMARY (Filegroups)• Tables must have a clustered

index • Extended Properties• NOT FOR REPLICATION• ROWGUIDCOL• Various Index options (WITH)• Windows logins• Various XML features • Encrypted objects• UDTs (CLR)

For more information see: http://msdn.microsoft.com/en-us/library/ee336253.aspx http://

msdn.microsoft.com/en-us/library/windowsazure/ee336267.aspx

• Federations

Features only on SQL Azure…

6

RETARGET TO SQL AZURE

SQL AZURE MIGRATION PROCESS

Assess

• Assess database schema for compatibility• Assess application requirements

Update

• Update schema as required• Update applications to match revised schema

Deploy

• Deploy schema, or• Update database and deploy schema + data• Deploy applications and test

MIGRATION TO SQL AZURE WITH SSDT

• Conversion approaches• Import database schema into project and prune, or• Schema Compare into empty project excluding unsupported objects

• Validation• Set project target to SQL Azure and build• Fix validation errors and rebuild to confirm

• Deploy Schema• Publish schema to SQL Azure (direct, via scripts or dacpac)

• Deploy Schema + Data• Reset target and update original database to make it SQL Azure

compliant• Use (SSMS) Deploy to SQL Azure… (uses bacpac)

FILTERING OUT UNSUPPORTED SQL AZURE OBJECTS

APPLICATION-SCOPED OBJECTS

• Aggregates• Application Roles• Assemblies• Asymmetric keys• Broker Priorities• Certificates• Contracts• Defaults• Extended Properties• Filegroups• Full Text Catalogs• Full Text Indexes• Full Text Stoplists

NON-APPLICATION SCOPED• All…

Use Schema Compare options to exclude these from comparison

• Message Types• Partition Functions• Partition Schemes• Queues• Remote Service Bindings• Rules• Sequences• Services• Symmetric Keys• User Defined Types (CLR)• XML Indexes• XML Schema Collections• Users (if Windows users…)

10

DAC SCENARIOS

SQL SERVER DATA TOOLS (SSDT)

Developer-focused toolset for authoring, building and publishing DACPACs

Experiences Enabled• Connected Imperative and Declarative Development• Project Based Development• Application Lifecycle & Tools

DACPAC verbs• Author DACPAC declaratively and build package from source• Compare DACPAC to project, database, and other DACPACs• Import DACPAC into project• Publish DACPAC or Project to database

SQL SERVER MANAGEMENT STUDIO (SSMS)

DBA-focused tools for deploying and extracting DACPACs; importing and exporting BACPACs

Experiences Enabled• Administration and Monitoring• Connected Imperative Development• Configuration and Task Wizards

DACPAC Verbs Supported• Deploy Data Tier Application• Register/Unregister Data Tier Application• Upgrade Data Tier Application

BACPAC Verbs Supported• Import and Export Data Tier Application• Migrate Database to SQL Azure

SQL AZURE MANAGEMENT PORTAL (SAMP)

Subscriber and Operator oriented toolset for managing SQL Azure databases through DACfx Verbs

Experiences Enabled• Connected Development• Portal Based Tools• Application Lifecycle support

BACPAC Verbs supports (Via Blob Storage)• Export Data Tier Application• Import Data Tier Application

DACPAC verbs supported• Deploy Data Tier Application• Upgrade Data Tier Application• Extract Data Tier Application

14

DAC LIFECYCLE

Develop (SSDT)• Import Database/

DACPAC• Author DAC• Compare and

Synchronize Source

• Compile Database Project to DACPAC

• Publish DACPAC

Deploy (SSMS)• Deploy Package

• New Databases• Upgrade Databases

Manage (SSMS and SAMP)• Export and Extract

Packages• Register Database as

DAC

Developer

DBA DBA/Admin

15

SCHEMA DEPLOYMENT USING DACFX

RESOURCES

SQL Server Data Tools Online Installationhttp://msdn.microsoft.com/data/tools Get It

Team Bloghttp://blogs.msdn.com/b/ssdt/

MSDN Forumhttp://social.msdn.microsoft.com/Forums/en-US/ssdt/threads

ArticlesMSDN Magazine Sept 2011 The "Juneau" Database Project

17