Database projects in visual studio 2010
This presentation is the property of its rightful owner.
Sponsored Links
1 / 24

Database projects in visual studio 2010 PowerPoint PPT Presentation


  • 62 Views
  • Uploaded on
  • Presentation posted in: General

Database projects in visual studio 2010. Anthony Brown [email protected] http://www.sqlblogcasts.com/blogs/antxxxx. Agenda. What are they? How they work What else you can do Limitations. What are they for. Offline development of database objects

Download Presentation

Database projects in visual studio 2010

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


Database projects in visual studio 2010

Database projects in visual studio 2010

Anthony Brown

[email protected]

http://www.sqlblogcasts.com/blogs/antxxxx


Agenda

Agenda

  • What are they?

  • How they work

  • What else you can do

  • Limitations


What are they for

What are they for

  • Offline development of database objects

  • Manage database objects in source control

  • Validation at design time, not deploy

  • Allow easy creation of databases in a consistent state


Version comparison

Version comparison


Before database projects

Before database projects

  • Created a change script which was run against all environments

  • Had to maintain change script for each change and run in specific order

  • Had to know state of target server to work out which scripts to run, or create complex scripts


Before database projects1

Before database projects

if not exists (select null from sys.tables where name = 'ErrorLog' and schema_id = schema_id('dbo'))

begin

CREATE TABLE [dbo].[ErrorLog] (

[ErrorLogID] INT IDENTITY (1, 1) NOT NULL,

[ErrorTime] DATETIME NOT NULL,

[UserName] [sysname] NOT NULL,

[ErrorNumber] INT NOT NULL,

[ErrorSeverity] INT NULL,

[ErrorState] INT NULL,

[ErrorProcedure] NVARCHAR (126) NULL,

[ErrorLine] INT NULL,

[ErrorMessage] NVARCHAR (4000) NOT NULL

);

end

go

if not exists (select null from sys.columns where name = 'newcolumn' and OBJECT_NAME(object_id) = 'ErrorLog')

begin

alter table ErrorLog

add newcolumnint null

end


With database projects

With database projects

  • Change script generated at deploy time based on compiled project and state of target database

  • Do not need to know state of target database before deploy


With database projects1

With database projects

THE PROJECT IS THE TRUTH


Structure

Structure

  • All database objects are stored in a project

  • All objects are defined fully

  • Server projects for server level objects (logins, endpoints etc)

  • Database projects for database level objects (tables, stored procedures, users etc)


Import schema

Import schema

  • Can import whole database only into blank project

  • Can import script into existing database project


Demos

demos


Build

Build

  • Validates all objects

  • Doesn’t need a database connection

  • Creates compiled dbschema file


Schema compare

Schema compare

  • Gui method of comparing project and target server

  • Not available in professional


Deploy

Deploy

  • Used to generate sql file that will make the target database the same as the project

  • Optionally runs the sql file against the target database

  • Can be run from

    • Visual studio

    • Msbuild/team build

    • Vsdbcmd

    • API


Deploy1

Deploy


Database projects in visual studio 2010

demo


Permissions

Permissions

  • Managed in an xml file

  • Validated to ensure object and user/role exists in project

  • Might need to define login for user in server project

  • Not very user friendly 

  • Can import from a script


Database projects in visual studio 2010

Demo


What else they can do

What else they can do

  • References (dbschema, xsd, clr)

  • Static code analysis

  • Database unit tests & data generation (execute only in professional)

  • Refactor (execute only in professional)

  • Extensibility


New in 2010

New in 2010

  • Code snippets

  • Intellisense

  • Integrated debugger

  • Extensibility


Not supported 1

Not supported - 1

  • Sql agent jobs

  • Replication

  • Replicated tables schema modification (can workaround by setting VerifyDeployment=false)

  • For replication on stored procedures


Not supported 2

Not supported - 2

  • Change data capture (change tracking supported in 2010)

  • Reference data

  • Circular cross database references


Not supported 3

Not supported - 3

  • Session/global temporary table

  • With check/with no check on constraints

  • Non sql server linked servers

  • Create resource pool

  • Create workload group

  • Create full text stoplist

  • Reporting services

  • Integration services

  • Analysis services

  • Table – lock escalation

  • Index options – maxdop, drop_existing, sort_in_tempdb


Further reading

Further reading

  • http://vsdatabaseguide.codeplex.com/

  • http://social.msdn.microsoft.com/Forums/en-US/vstsdb/threads


  • Login