asked
Database Restructuring SQL 2005 Database with VS 2010
Has anyone been successfull with using Visual Studio 2010 to redesign and create a new database? Do you have lessons learned to share? I have developed an application from a database that was poorly contructed. I would like to create a new design with the correct relationships and primary keys. Doing this will allow the data to display in my Silverlight application correctly.
I have been using Visual Studio to create and maintain databases for about 4 years now. I started using VS when Data Dude, aka DB Pro, aka Team System Database Edition, aka whatever it's called now, first came out. I've used it in 2005, 2008 & 2010. It works extremely well, but you have to do two things to be really successful with it. First, establish and document your development & deployment process so everyone involved knows what's happening with it. Second, enforce discipline. Everything goes into source control, builds are only from source control, database only development will be over-written by sourece control deployments, etc. Do these things and you should be successful. Part of what you're going to want to use are the Code Analysis. This will do all kinds of checks against the database, looking for missing pk's, poorly structured tsql, stuff like that. It's built right into the tool. For a lot more information on this, you can search through my blog, [
scarydba.com][1], where there is some stuff. Also, I published [a couple][2] [of articles][3] on the main SQL Server Central site, and finally, a book was released, just last month, from Red Gate, where I outline development and deployment best practices over two chapters, including how to use Visual Studio, [Red Gate's Guide To Team Development][4]. In case you can't tell, I really got into using Visual Studio and working on development and deployment processes. Let me know if you have more questions. [1]:
http://scarydba.com [2]:
http://www.sqlservercentral.com/articles/visual+studio/65746/ [3]:
http://www.sqlservercentral.com/articles/DBPro/61617/ [4]:
http://www.sqlservercentral.com/articles/books/71472/
Not junk at all - you've written some great stuff in that book. It is good to see that this sort of structure is creeping into DB development. I have thought it strange that "normal" devs had all these things down for such a long time and it never really got passed onto db devs - I know they don't talk to each other, but that is really silly!
Now there we're in agreement. It's actually criminal that db development isn't using the processes & tools that have been built out over decades for the rest of the development. That's been something I've pushed for years within my organization. I was pleased as hell when Red Gate gave me a nice big platform to scream it out to everyone else.
Not up to you to promote my junk. I'll handle it. Thanks though.
I would say that the tool used to do the design of a database has little bearing on the outcome of that design. You can have the best design tool but still come up with a poor database if you don't understand the design principals of databases. I work with VS2008 at present along with SSMS - but they are only tools to translate what is in my head into the objects in a database. I don't use visual design tools when designing a databse (maybe that is not a good idea, I don't know though), I have the design in my head, move it around a bit, create the objects, run a little with the design, mould it a little more (design specs like to be changed after the fact!) and come up with something resembling sanity. I imagine most people will work along similar lines (or I am a freak in design workflow terms). VS2010 has some nice improvements for database projects - I don't know if the changes that were showcased at the PASS Summit for SSMS are part of VS2010 (but I think they will be), these will help further in terms of refactoring and so on. I would suggest maybe going down the root of a private dev database/instance where you play around with your design ideas. When you are happy you can then solidify that - depending upon your environment at work this may be promoting the design to a shared dev box / database. After that it would be QAS/Testing then Production. I hope that gives you some sort of help.
