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.

more ▼

asked Dec 02, 2010 at 07:15 PM in Default

avatar image

1.4k 100 114 121

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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, where there is some stuff. Also, I published a couple of articles 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.

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.

more ▼

answered Dec 03, 2010 at 05:04 AM

avatar image

Grant Fritchey ♦♦
137k 20 46 81

Sorry - forgot to mention your book / chapters - I'm in the process of reading it now (really good). Would give more than +1 if possible

Dec 03, 2010 at 05:28 AM WilliamD

Not up to you to promote my junk. I'll handle it. Thanks though.

Dec 03, 2010 at 06:39 AM Grant Fritchey ♦♦

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!

Dec 03, 2010 at 06:47 AM WilliamD

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.

Dec 03, 2010 at 06:51 AM Grant Fritchey ♦♦

pre-sactly. Keep on shouting whilst on that soapbox! You are one of the people that could scare some db devs into doing the right thing! ;0)

Dec 03, 2010 at 06:59 AM WilliamD
(comments are locked)
10|1200 characters needed characters left

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.

more ▼

answered Dec 03, 2010 at 02:52 AM

avatar image

26.2k 18 37 48

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Dec 02, 2010 at 07:15 PM

Seen: 1922 times

Last Updated: Dec 03, 2010 at 02:52 AM

Copyright 2018 Redgate Software. Privacy Policy