I have been working on a project for the last four years that has a database that has been continually evolving. We have several customers whose data has been migrated through the many iterations of the database. We can handle the majority of database refactoring without any change scripts. The database has > 250 tables with ~3000 fields.
The basic premise is two small apps, one that generates a XML based description of the database and the other that creates new databases and upgrades existing ones. At the moment the XML script file that we create is a hybrid of pure XML data and small chunks of TSQL script. An upgrade program builds a new empty database using SQL-DMO and the XML script. Initially we do not add any constraints, indexes or triggers to the new database. Then we use INSERT INTO newTable (FieldList) SELECT (FieldList) FROM oldDBVersion..oldTable to migrate the data from the old database to the new one. A transform XML document is used to manipulate the fieldlists in the INSERT SQL to handle special cases like field renames, table renames and column data type changes that will not convert implicitly. After moving the data over, the constraints, indexes and triggers are added. The obvious drawback to this approach is that you end up with two complete copies of the database after the upgrade, therefore if the database is huge, this is probably not the best solution for you. The advantage is that taking a backup before doing the upgrade is not critical as the source database is not touched. All of this to say, you can manage databases changes in a semi-automatic manner. My developers know they can add/remove fields and tables without any concern, they just have to let me know if they rename columns/tables that contain production data or when they need production data moved to different columns/tables. Darrel Miller > -----Original Message----- > From: Unmoderated discussion of advanced .NET topics. > [mailto:[EMAIL PROTECTED] On Behalf Of > Gary Davidson > Sent: Wednesday, April 21, 2004 4:37 PM > To: [EMAIL PROTECTED] > Subject: Re: [ADVANCED-DOTNET] OT Continous Integration > > I have to say I agree with the comments so far and to add I > have used ORM since "InfoModeler Q" and have all Terry's > books on the subject. > > In a new db design I would always start with ORM but after > serveral iterations I find this not to be practical because > of developer skill levels and turnover. So then I have to > hire a DBA who does nothing but script change integration. > > For source code I have a source control system that is well > integrated in the IDE and I can run reports showing who > changed what & when and who has code still checked out. I can > also automate the build process from the source control system > > The db is a different story. A developer opens enterprise mgr > and makes a quick change and then "forgets" to notify anyone > of the change script. We don't find out until the build or > someone happens to do a get latest and steps on that code. > Then it turn in to a finger pointing discussion with the > developers wanting some magic "process" to fix it. They blame > lack of process and I say they aren't following the process we have. > > I guess it is the human element I am trying to factor out. I > am trying to automate the process for db development builds > and find the integration errors sooner. > > Maybe "That dog just won't hunt". > > Gary Davidson > --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.668 / Virus Database: 430 - Release Date: 24-4-2004 =================================== This list is hosted by DevelopMentor® http://www.develop.com Some .NET courses you may be interested in: NEW! Guerrilla ASP.NET, 17 May 2004, in Los Angeles http://www.develop.com/courses/gaspdotnetls View archives and manage your subscription(s) at http://discuss.develop.com
