Transcript Database projects

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
Manage database objects in source control
Validation at design time, not deploy
Allow easy creation of databases in a
consistent state
Version comparison
Feature
Visual Studio 2010 Professional
Visual Studio 2010
Premium and Ultimate
Schema Compare

Data Compare

Database Unit Tests
Execute Only

Transact-SQL Refactoring
Execute Only

Transact-SQL Static Code Analysis
Execute Only

Data Generation
Execute Only

Team Foundation Server Build
Integration


Command line deploy (VSDBCMD)


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 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 newcolumn int null
end
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 projects
THE PROJECT
IS THE TRUTH
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
• Can import whole database only into blank
project
• Can import script into existing database
project
demos
Build
• Validates all objects
• Doesn’t need a database connection
• Creates compiled dbschema file
Schema compare
• Gui method of comparing project and target
server
• Not available in professional
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
Deploy
Database
project
Database
Dbschema file
Schema
model
Schema
model
Compare
Database deployment
Deployment options
Sql file
demo
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
Demo
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
•
•
•
•
Code snippets
Intellisense
Integrated debugger
Extensibility
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
• Change data capture (change tracking
supported in 2010)
• Reference data
• Circular cross database references
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
• http://vsdatabaseguide.codeplex.com/
• http://social.msdn.microsoft.com/Forums/enUS/vstsdb/threads