240 likes | 370 Views
Database projects in visual studio 2010. Anthony Brown anthony@found-it.net 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
E N D
Database projects in visual studio 2010 Anthony Brown anthony@found-it.net 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
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 newcolumnint 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
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
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
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/en-US/vstsdb/threads